JUYSSgs5

1/*DROP DATABASE IF EXISTS taskmanager;
2CREATE DATABASE taskmanager;
3*/
4USE taskmanager;
5CREATE TABLE Account
6(
7	ID int NOT NULL AUTO_INCREMENT,
8	FirstName varchar(255) NOT NULL,
9	LastName varchar(255) NOT NULL,
10	Username varchar(255) NOT NULL,
11	Password varchar(255) NOT NULL,
12	Email varchar(255) NOT NULL,
13	PRIMARY KEY (ID)
14);
15
16CREATE TABLE Projects
17(
18	userID int NOT NULL,
19	projID int NOT NULL AUTO_INCREMENT,
20	ProjectName varchar(255) NOT NULL,
21	PRIMARY KEY (projID),
22	FOREIGN KEY (userID) REFERENCES Account(ID)
23);
24
25CREATE TABLE Tasks
26(
27	projID int NOT NULL,
28	taskID int NOT NULL AUTO_INCREMENT,
29	Task varchar(255) NOT NULL,
30	Due DATE NOT NULL,
31	Priority VARCHAR(255),
32	Done BOOLEAN,
33	PRIMARY KEY (taskID),
34	FOREIGN KEY (projID) REFERENCES Projects(projID)
35);
36
37INSERT INTO Account(FirstName, LastName, Username, Password, Email) VALUES("Admin","Admin","admin","admin","admin@essex.ac.uk")
38INSERT INTO Projects(ProjectName) VALUES ("Test Project")
39INSERT INTO Tasks(Task, Due, Priority, Done) VALUES("Testing Testing 123","2008-11-11","High",TRUE)
40
41SHOW TABLES;