69CDYjWS

1-- *************************************************************
2-- MYSQL ASSIGNMENT
3-- your name here
4-- Caio Victor Gonรงalves // 
5-- *************************************************************
6
7-- create the database
8DROP DATABASE IF EXISTS assignment;
9CREATE DATABASE assignment;
10
11-- select the database
12USE assignment;
13
14-- create products tables
15CREATE TABLE products
16(
17  	PRODUCT_ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
18	DESCRIPTION VARCHAR(255),
19	PRODUCT_NAME VARCHAR(255) NOT NULL,
20	SUPPLIER UNSIGNED INT FOREIGN KEY
21);
22
23CREATE TABLE product_types
24(
25  	PRODUCT_ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
26	DESCRIPTION VARCHAR(255)
27);
28
29CREATE TABLE invoices
30(
31  	PRODUCT_ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
32	DESCRIPTION VARCHAR(255),
33	CUSTOMER INT FOREIGN KEY
34);
35
36CREATE TABLE invoice_item
37(
38  	PRODUCT UNSIGNED INT,
39	INVOICE UNSIGNED INT,
40	PRIMARY KEY(PRODUCT, INVOICE)
41);
42
43CREATE TABLE customer
44(
45  	CUSTOMER_ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
46	CONTACT FOREIGN KEY
47);
48
49CREATE TABLE employees
50(
51  	EMPLOYEE_ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
52	CONTACT_ID FOREIGN KEY,
53	SIN VARCHAR(7)
54);
55
56CREATE TABLE contact
57(
58  	CONTACT_ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
59	PHONE_NUMBER VARCHAR(10),
60	FIRST_NAME VARCHAR(255) NOT NULL,
61	LAST_NAME VARCHAR(255) NOT NULL,
62	ADDRESS VARCHAR(255),
63	POSTAL_CODE VARCHAR(6),
64	CITY VARCHAR(255)
65);
66
67CREATE TABLE suppliers
68(
69  	SUPLIER_ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
70	CONTACT FOREIGN KEY
71);
72
73CREATE TABLE catering_event
74(
75  	EVENT_ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
76	EVENT_DATE DATE,
77	INVOICE UNSIGNED INT FOREIGN KEY
78);
79
80CREATE TABLE delivery
81(
82  	DELIVERY_ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
83	DELIVERY_DATE DATE,
84	SALE_ID UNSIGNED FOREIGN KEY
85);
86
87CREATE TABLE sale
88(
89  	SALE_ID UNSIGNED INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
90	SALE_DATE DATE,
91	INVOICE UNSIGNED INT FOREIGN KEY
92);
93
94CREATE TABLE stock
95(
96  	PRODUCT UNSIGNED INT NOT NULL,
97	SALE_ID UNSIGNED INT NOT NULL,
98	PRIMARY KEY(PRODUCT, SALE_ID)
99);