9rmChiNc

1CREATE TABLE IF NOT EXISTS ManuOrder (
2  customerName VARCHAR(60) NOT NULL,
3  orderID MEDIUMINT NOT NULL AUTO_INCREMENT,
4  deadline DATE NOT NULL,
5  location VARCHAR(256) NOT NULL,
6  onHand BOOL NOT NULL,
7  PRIMARY KEY (orderID)
8);
9
10CREATE TABLE IF NOT EXISTS Company(
11  companyID MEDIUMINT NOT NULL AUTO_INCREMENT,
12  companyName VARCHAR(100) NOT NULL,  
13  employerEmail VARCHAR(100) NOT NULL,  
14  employerPassword VARCHAR(30) NOT NULL,  
15  PRIMARY KEY (companyID),  
16  UNIQUE (employerEmail)  
17);
18CREATE TABLE IF NOT EXISTS Product(
19  productTitle VARCHAR(256) NOT NULL,  
20  quantity INT,  
21  price FLOAT,  
22  orderID VARCHAR(50) NOT NULL,  
23  PRIMARY KEY (productTitle),  
24  FOREIGN KEY (orderID) REFERENCES ManuOrder(orderID)  
25);
26
27
28CREATE TABLE IF NOT EXISTS Part(
29  partTitle VARCHAR(256) NOT NULL, 
30  partQuantity VARCHAR(256) NOT NULL,  
31  productTitle VARCHAR(256) NOT NULL,  
32  PRIMARY KEY (partTitle),  
33  FOREIGN KEY (productTitle) REFERENCES Product(productTitle)  
34);
35
36
37CREATE TABLE IF NOT EXISTS Employee(
38  name VARCHAR(30) NOT NULL, 
39  employeeID MEDIUMINT NOT NULL AUTO_INCREMENT,  
40  jobTitle VARCHAR(256) NOT NULL,  
41  permissions VARCHAR(50) NOT NULL,  
42  email VARCHAR(100) NOT NULL,  
43  password VARCHAR(30) NOT NULL,  
44  companyID MEDIUMINT NOT NULL,
45  PRIMARY KEY (employeeID, email),  
46  FOREIGN KEY (companyID) REFERENCES Company(companyID)  
47);
48
49CREATE TABLE IF NOT EXISTS Tasks(
50  taskID MEDIUMINT NOT NULL AUTO_INCREMENT,
51  taskTitle VARCHAR(256) NOT NULL,  
52  taskDescription VARCHAR(256) NOT NULL, 
53  taskDeadline DATE NOT NULL,  
54  employeeID MEDIUMINT NOT NULL,
55
56  orderID MEDIUMINT NOT NULL,
57  partTitle VARCHAR(256) NOT NULL,  
58  PRIMARY KEY (taskID),
59  FOREIGN KEY (employeeID, email) REFERENCES Employee(employeeID),
60  FOREIGN KEY (productTitle) REFERENCES Product(productTitle),
61  FOREIGN KEY (orderID) REFERENCES Orders(orderID), 
62  FOREIGN KEY (partTitle) REFERENCES Parts(partTitle)
63);