· 6 years ago · Nov 18, 2019, 09:18 PM
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 tables
16
17CREATE TABLE contact
18(
19 CONTACT_ID INT NOT NULL AUTO_INCREMENT 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 NOT NULL AUTO_INCREMENT PRIMARY KEY,
31 SIN VARCHAR(7),
32 CONTACT_ID INT,
33 CONSTRAINT employees_fk_contact
34 FOREIGN KEY(CONTACT_ID)
35 REFERENCES contact (CONTACT_ID)
36);
37
38CREATE TABLE customer
39(
40 CUSTOMER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
41 CONTACT_ID INT,
42 CONSTRAINT customer_fk_contact
43 FOREIGN KEY (CONTACT_ID)
44 REFERENCES contact (CONTACT_ID)
45);
46
47CREATE TABLE suppliers
48(
49 SUPPLIER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
50 CONTACT_ID INT,
51 CONSTRAINT suppliers_fk_contact
52 FOREIGN KEY(CONTACT_ID)
53 REFERENCES contact (CONTACT_ID)
54);
55
56CREATE TABLE products
57(
58 PRODUCT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
59 DESCRIPTION VARCHAR(255),
60 PRODUCT_NAME VARCHAR(255) NOT NULL,
61 SUPPLIER_ID INT,
62 CONSTRAINT products_fk_suppliers
63 FOREIGN KEY(SUPPLIER_ID)
64 REFERENCES suppliers (SUPPLIER_ID)
65);
66
67CREATE TABLE invoices
68(
69 INVOICE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
70 PRODUCT_ID INT NOT NULL,
71 DESCRIPTION VARCHAR(255),
72 CUSTOMER_ID INT NOT NULL,
73 CONSTRAINT invoices_fk_customer
74 FOREIGN KEY (CUSTOMER_ID)
75 REFERENCES customer (CUSTOMER_ID),
76
77 CONSTRAINT invoices_fk_products
78 FOREIGN KEY (PRODUCT_ID)
79 REFERENCES products (PRODUCT_ID)
80);
81
82CREATE TABLE invoice_item
83(
84 PRODUCT_ID INT NOT NULL,
85 INVOICE_ID INT NOT NULL,
86 CONSTRAINT invoice_item_fk_products
87 FOREIGN KEY(PRODUCT_ID)
88 REFERENCES products (PRODUCT_ID),
89
90 CONSTRAINT invoice_item_fk_invoices
91 FOREIGN KEY (INVOICE_ID)
92 REFERENCES invoices (INVOICE_ID)
93);
94
95CREATE TABLE product_types
96(
97 PRODUCT_ID INT NOT NULL,
98 DESCRIPTION VARCHAR(255),
99 CONSTRAINT product_types_fk_products
100 FOREIGN KEY (PRODUCT_ID)
101 REFERENCES products (PRODUCT_ID)
102);
103
104CREATE TABLE catering_event
105(
106 EVENT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
107 EVENT_DATE DATE,
108 INVOICE_ID INT NOT NULL,
109 CONSTRAINT catering_event_fk_invoices
110 FOREIGN KEY (INVOICE_ID)
111 REFERENCES invoices (INVOICE_ID)
112);
113
114CREATE TABLE sale
115(
116 SALE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
117 SALE_DATE DATE,
118 INVOICE_ID INT NOT NULL,
119 CONSTRAINT sale_fk_invoices
120 FOREIGN KEY (INVOICE_ID)
121 REFERENCES invoices (INVOICE_ID)
122);
123
124CREATE TABLE delivery
125(
126 DELIVERY_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
127 DELIVERY_DATE DATE,
128 SALE_ID INT NOT NULL,
129 CONSTRAINT delivery_fk_sale
130 FOREIGN KEY (SALE_ID)
131 REFERENCES sale (SALE_ID)
132);
133
134CREATE TABLE stock
135(
136 PRODUCT_ID INT NOT NULL,
137 SALE_ID INT NOT NULL,
138 CONSTRAINT stock_fk_products
139 FOREIGN KEY(PRODUCT_ID)
140 REFERENCES products (PRODUCT_ID),
141
142 CONSTRAINT stock_fk_sale
143 FOREIGN KEY(SALE_ID)
144 REFERENCES sale (SALE_ID)
145);