1ZDZT0iV

1DROP DATABASE IF EXISTS assignment;        
2CREATE DATABASE assignment;
3 
4USE assignment;
5 
6 
7CREATE TABLE contact
8(
9    CONTACT_ID		INT				NOT	NULL	AUTO_INCREMENT	PRIMARY KEY,
10    PHONE_NUMBER	VARCHAR(10),
11    FIRST_NAME		VARCHAR(255)	NOT NULL,
12    LAST_NAME		VARCHAR(255)	NOT NULL,
13    ADDRESS			VARCHAR(255),
14    POSTAL_CODE		VARCHAR(6),
15    CITY			VARCHAR(255)
16);
17
18CREATE TABLE employees
19(
20    EMPLOYEE_ID 	INT 			NOT NULL 	AUTO_INCREMENT PRIMARY KEY,   
21    SIN 			VARCHAR(7),
22	CONTACT_ID 		INT,
23	CONSTRAINT employees_fk_contact
24		FOREIGN KEY(CONTACT_ID)
25			REFERENCES contact (CONTACT_ID)
26);
27
28CREATE TABLE customer
29(
30    CUSTOMER_ID 	INT 			NOT NULL 	AUTO_INCREMENT PRIMARY KEY,
31    CONTACT_ID 		INT,
32    CONSTRAINT customer_fk_contact
33		FOREIGN KEY (CONTACT_ID)
34    		REFERENCES contact (CONTACT_ID)
35);
36 
37CREATE TABLE suppliers
38(
39    SUPPLIER_ID 	INT 			NOT NULL 	AUTO_INCREMENT PRIMARY KEY,
40    CONTACT_ID 		INT, 
41	CONSTRAINT suppliers_fk_contact	
42		FOREIGN KEY(CONTACT_ID)
43			REFERENCES contact (CONTACT_ID)
44);
45
46CREATE TABLE products
47(
48    PRODUCT_ID 		INT 			NOT NULL 	AUTO_INCREMENT PRIMARY KEY,
49    DESCRIPTION 	VARCHAR(255),
50    PRODUCT_NAME 	VARCHAR(255) 	NOT NULL,
51	SUPPLIER_ID 	INT, 
52	PRICE			DOUBLE,
53	CONSTRAINT products_fk_suppliers	
54		FOREIGN KEY(SUPPLIER_ID)
55			REFERENCES suppliers (SUPPLIER_ID)
56);
57
58CREATE TABLE invoices 
59(
60	INVOICE_ID 		INT 			NOT NULL 	AUTO_INCREMENT PRIMARY KEY,
61    DESCRIPTION 	VARCHAR(255),
62    CUSTOMER_ID 	INT 			NOT NULL,
63    CONSTRAINT invoices_fk_customer
64		FOREIGN KEY (CUSTOMER_ID)
65    		REFERENCES customer (CUSTOMER_ID)
66);
67
68CREATE TABLE invoice_item
69(
70    PRODUCT_ID 		INT 			NOT NULL,
71    INVOICE_ID 		INT 			NOT NULL,
72	QUANTITY		INT,
73    CONSTRAINT invoice_item_fk_products
74		FOREIGN KEY(PRODUCT_ID)
75			REFERENCES products (PRODUCT_ID),
76	
77	CONSTRAINT invoice_item_fk_invoices
78		FOREIGN KEY (INVOICE_ID)
79			REFERENCES invoices (INVOICE_ID)
80);
81
82CREATE TABLE product_types
83(
84    PRODUCT_ID 		INT 			NOT NULL,
85    DESCRIPTION 	VARCHAR(255),
86	CONSTRAINT product_types_fk_products
87		FOREIGN KEY (PRODUCT_ID)
88			REFERENCES products (PRODUCT_ID)
89);
90 
91CREATE TABLE catering_event
92(
93    EVENT_ID 		INT 			NOT NULL 	AUTO_INCREMENT PRIMARY KEY,
94    EVENT_DATE 		DATE,
95    INVOICE_ID 		INT 			NOT NULL, 
96	CONSTRAINT catering_event_fk_invoices
97		FOREIGN KEY (INVOICE_ID)
98			REFERENCES invoices (INVOICE_ID)
99);
100 
101CREATE TABLE sale
102(
103    SALE_ID 		INT 			NOT NULL 	AUTO_INCREMENT PRIMARY KEY,
104    SALE_DATE 		DATE,
105    INVOICE_ID 		INT 			NOT NULL,
106	CONSTRAINT sale_fk_invoices
107		FOREIGN KEY (INVOICE_ID)
108			REFERENCES invoices (INVOICE_ID),
109	EMPLOYEE_ID		INT				NOT NULL,
110	CONSTRAINT sale_fk_employees
111		FOREIGN KEY (EMPLOYEE_ID)
112			REFERENCES employees (EMPLOYEE_ID)
113);
114
115CREATE TABLE delivery
116(
117    DELIVERY_ID 	INT 			NOT NULL 	AUTO_INCREMENT PRIMARY KEY,
118    DELIVERY_DATE 	DATE,
119    SALE_ID 		INT 			NOT NULL,
120	CONSTRAINT delivery_fk_sale
121		FOREIGN KEY (SALE_ID)
122			REFERENCES sale (SALE_ID) 
123);
124 
125CREATE TABLE stock
126(
127    PRODUCT_ID 		INT 			NOT NULL,
128	DELIVERY_ID		INT				NOT NULL,
129	EXPIRATION		DATE,
130    CONSTRAINT stock_fk_products
131		FOREIGN KEY(PRODUCT_ID)
132			REFERENCES products (PRODUCT_ID),
133	
134	CONSTRAINT stock_fk_delivery
135		FOREIGN KEY(DELIVERY_ID)
136			REFERENCES delivery(DELIVERY_ID)
137);