KTWgtZTS

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