Class Activity 2 - Creating New Tables (Week 2)

Example SQL Code

USE EBIS3003;

DROP TABLE IF EXISTS projects_kick;
DROP TABLE IF EXISTS creaotrs_kick;


CREATE TABLE creators_kick (
	id INT NOT NULL,
	user_id VARCHAR(255) NOT NULL,
	password VARCHAR(255) NOT NULL,
	num_projects INT NULL,
	address VARCHAR(255) NULL,
	bio LONGTEXT NULL,

	CONSTRAINT creators_key PRIMARY KEY(id)
);

CREATE TABLE projects_kick (
	id INT NOT NULL,
	creator_id INT NOT NULL,
	timestamp VARCHAR(255) NOT NULL,
	total_amount DECIMAL(10,3) NULL,
	completion INT NOT NULL,
	remaining_amount DECIMAL(10,3) NULL,

	CONSTRAINT projects_key PRIMARY KEY(id),
	CONSTRAINT projects_f_key FOREIGN KEY(creator_id) REFERENCES creators_kick(id) 
);

There are a few things to note:

1. Transit to EBIS3003 database.

2. DROP TABLE in case you made mistake before.

3. VARCHAR v. VARCHAR2 v. CHAR.

   • The numbers are 'bytes,' not # of characters.

   • Difference between standard SQL v. MySQL (or other variations).

   • https://www.w3schools.com/sql/sql_datatypes.asp

4. CONSTRAINT can be omitted in MySQL.

5. Use of NULL.

6. INT and DECIMAL are different!

7. The order of creating tables matter when you have constraints.

8. The name of foreign key can be different from its original name in the referenced table.

A Simple Data Model

powered by https://dbdiagram.io/