Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # *************************************************************
- # MYSQL ASSIGNMENT
- # NAMES:
- # Brown, Shayne
- # Logan, Justin
- # Goncalves, Caio Victor
- # *************************************************************
- DROP DATABASE IF EXISTS assignment;
- CREATE DATABASE assignment;
- # -- select the database
- USE assignment;
- # -- create products tables
- CREATE TABLE contact
- (
- CONTACT_ID INT PRIMARY KEY,
- PHONE_NUMBER VARCHAR(10),
- FIRST_NAME VARCHAR(255) NOT NULL,
- LAST_NAME VARCHAR(255) NOT NULL,
- ADDRESS VARCHAR(255),
- POSTAL_CODE VARCHAR(6),
- CITY VARCHAR(255)
- );
- CREATE TABLE employees
- (
- EMPLOYEE_ID INT PRIMARY KEY,
- SIN VARCHAR(7),
- CONTACT_ID INT,
- FOREIGN KEY(CONTACT_ID)
- REFERENCES contact (CONTACT_ID)
- );
- CREATE TABLE customer
- (
- CUSTOMER_ID INT PRIMARY KEY,
- CONTACT_ID INT,
- FOREIGN KEY (CONTACT_ID)
- REFERENCES contact (CONTACT_ID)
- );
- CREATE TABLE suppliers
- (
- SUPPLIER_ID INT PRIMARY KEY,
- CONTACT_ID INT,
- FOREIGN KEY(CONTACT_ID)
- REFERENCES contact (CONTACT_ID)
- );
- CREATE TABLE products
- (
- PRODUCT_ID INT PRIMARY KEY,
- DESCRIPTION VARCHAR(255),
- PRODUCT_NAME VARCHAR(255) NOT NULL,
- SUPPLIER_ID INT,
- FOREIGN KEY(SUPPLIER_ID)
- REFERENCES suppliers (SUPPLIER_ID)
- );
- CREATE TABLE invoices
- (
- INVOICE_ID INT PRIMARY KEY,
- PRODUCT_ID INT,
- DESCRIPTION VARCHAR(255),
- CUSTOMER_ID INT,
- FOREIGN KEY (CUSTOMER_ID)
- REFERENCES customer (CUSTOMER_ID),
- FOREIGN KEY (PRODUCT_ID)
- REFERENCES products (PRODUCT_ID)
- );
- CREATE TABLE invoice_item
- (
- PRODUCT_ID INT,
- INVOICE_ID INT,
- FOREIGN KEY(PRODUCT_ID)
- REFERENCES products (PRODUCT_ID),
- FOREIGN KEY (INVOICE_ID)
- REFERENCES invoices (INVOICE_ID)
- );
- CREATE TABLE product_types
- (
- PRODUCT_ID INT, #this will cause a problem
- DESCRIPTION VARCHAR(255),
- FOREIGN KEY (PRODUCT_ID)
- REFERENCES products (PRODUCT_ID)
- );
- CREATE TABLE catering_event
- (
- EVENT_ID INT PRIMARY KEY,
- EVENT_DATE DATE,
- INVOICE_ID INT,
- FOREIGN KEY (INVOICE_ID)
- REFERENCES invoices (INVOICE_ID)
- );
- CREATE TABLE sale
- (
- SALE_ID INT PRIMARY KEY,
- SALE_DATE DATE,
- INVOICE_ID INT,
- FOREIGN KEY (INVOICE_ID)
- REFERENCES invoices (INVOICE_ID)
- );
- CREATE TABLE delivery
- (
- DELIVERY_ID INT PRIMARY KEY,
- DELIVERY_DATE DATE,
- SALE_ID INT,
- FOREIGN KEY (SALE_ID)
- REFERENCES sale (SALE_ID)
- );
- CREATE TABLE stock
- (
- PRODUCT_ID INT,
- SALE_ID INT,
- FOREIGN KEY(PRODUCT_ID)
- REFERENCES products (PRODUCT_ID),
- FOREIGN KEY(SALE_ID)
- REFERENCES sale (SALE_ID)
- );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement