zS9Z0tiB

1DROP DATABASE IF EXISTS assignment;
2CREATE DATABASE assignment;
3 
4# -- select the database
5USE assignment;
6 
7# -- create products tables
8CREATE TABLE products
9(
10    PRODUCT_ID INT PRIMARY KEY,
11    DESCRIPTION VARCHAR(255),
12    PRODUCT_NAME VARCHAR(255) NOT NULL,
13	SUPPLIER_ID INT, 
14	FOREIGN KEY(SUPPLIER_ID)
15    REFERENCES suppliers (SUPPLIER_ID)
16);
17 
18CREATE TABLE product_types
19(
20    PRODUCT_ID INT PRIMARY KEY, #this will cause a problem
21    DESCRIPTION VARCHAR(255),
22	FOREIGN KEY (PRODUCT_ID)
23	REFERENCES products (PRODUCT_ID)
24);
25 
26CREATE TABLE invoices
27(
28	INVOICE_ID INT PRIMARY KEY,
29    PRODUCT_ID INT,
30    DESCRIPTION VARCHAR(255),
31    CUSTOMER_ID INT,
32    FOREIGN KEY (CUSTOMER_ID)
33    REFERENCES customers (CUSTOMER_ID),
34	
35	FOREIGN KEY (PRODUCT_ID)
36	REFERENCES products (PRODUCT_ID)
37);
38 
39CREATE TABLE invoice_item
40(
41    PRODUCT_ID INT,
42    INVOICE_ID INT,
43    FOREIGN KEY(PRODUCT_ID)
44	REFERENCES products (PRODUCT_ID),
45	
46	FOREIGN KEY (INVOICE_ID)
47	REFERENCES invoices (INVOICE_ID)
48);
49 
50CREATE TABLE customer
51(
52    CUSTOMER_ID INT PRIMARY KEY,
53    CONTACT_ID INT,
54    FOREIGN KEY (CONTACT_ID)
55    REFERENCES contact (CONTACT_ID)
56);
57 
58CREATE TABLE employees
59(
60    EMPLOYEE_ID INT PRIMARY KEY,   
61    SIN VARCHAR(7),
62	CONTACT_ID INT,
63	FOREIGN KEY(CONTACT_ID)
64	REFERENCES contact (CONTACT_ID)
65);
66 
67CREATE TABLE contact
68(
69    CONTACT_ID INT PRIMARY KEY,
70    PHONE_NUMBER VARCHAR(10),
71    FIRST_NAME VARCHAR(255) NOT NULL,
72    LAST_NAME VARCHAR(255) NOT NULL,
73    ADDRESS VARCHAR(255),
74    POSTAL_CODE VARCHAR(6),
75    CITY VARCHAR(255)
76);
77 
78CREATE TABLE suppliers
79(
80    SUPLIER_ID INT PRIMARY KEY,
81    CONTACT_ID INT, 
82	FOREIGN KEY(CONTACT_ID)
83	REFERENCES contact (CONTACT_ID)
84);
85 
86CREATE TABLE catering_event
87(
88    EVENT_ID INT PRIMARY KEY,
89    EVENT_DATE DATE,
90    INVOICE_ID INT, 
91	FOREIGN KEY (INVOICE_ID)
92	REFERENCES invoices (INVOICE_ID)
93);
94 
95CREATE TABLE delivery
96(
97    DELIVERY_ID INT PRIMARY KEY,
98    DELIVERY_DATE DATE,
99    SALE_ID INT,
100	FOREIGN KEY (SALE_ID)
101	REFERENCES sale (SALE_ID)
102);
103 
104CREATE TABLE sale
105(
106    SALE_ID INT PRIMARY KEY,
107    SALE_DATE DATE,
108    INVOICE_ID INT,
109	FOREIGN KEY (INVOICE_ID)
110	REFERENCES invoices (INVOICE_ID)
111);
112 
113CREATE TABLE stock
114(
115    PRODUCT_ID INT,
116    SALE_ID INT,
117    FOREIGN KEY(PRODUCT_ID)
118	REFERENCES products (PRODUCT_ID),
119	
120	FOREIGN KEY(SALE_ID)
121	REFERENCES sale (SALE_ID)
122);