Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP DATABASE IF EXISTS assignment;
- CREATE DATABASE assignment;
- USE assignment;
- CREATE TABLE contact
- (
- CONTACT_ID INT NOT NULL AUTO_INCREMENT 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 NOT NULL AUTO_INCREMENT PRIMARY KEY,
- SIN VARCHAR(7),
- CONTACT_ID INT,
- CONSTRAINT employees_fk_contact
- FOREIGN KEY(CONTACT_ID)
- REFERENCES contact (CONTACT_ID)
- );
- CREATE TABLE customer
- (
- CUSTOMER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- CONTACT_ID INT,
- CONSTRAINT customer_fk_contact
- FOREIGN KEY (CONTACT_ID)
- REFERENCES contact (CONTACT_ID)
- );
- CREATE TABLE suppliers
- (
- SUPPLIER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- CONTACT_ID INT,
- CONSTRAINT suppliers_fk_contact
- FOREIGN KEY(CONTACT_ID)
- REFERENCES contact (CONTACT_ID)
- );
- CREATE TABLE products
- (
- PRODUCT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- DESCRIPTION VARCHAR(255),
- PRODUCT_NAME VARCHAR(255) NOT NULL,
- SUPPLIER_ID INT,
- PRICE DOUBLE,
- CONSTRAINT products_fk_suppliers
- FOREIGN KEY(SUPPLIER_ID)
- REFERENCES suppliers (SUPPLIER_ID)
- );
- CREATE TABLE invoices
- (
- INVOICE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- DESCRIPTION VARCHAR(255),
- CUSTOMER_ID INT NOT NULL,
- CONSTRAINT invoices_fk_customer
- FOREIGN KEY (CUSTOMER_ID)
- REFERENCES customer (CUSTOMER_ID)
- );
- CREATE TABLE invoice_item
- (
- PRODUCT_ID INT NOT NULL,
- INVOICE_ID INT NOT NULL,
- QUANTITY INT,
- CONSTRAINT invoice_item_fk_products
- FOREIGN KEY(PRODUCT_ID)
- REFERENCES products (PRODUCT_ID),
- CONSTRAINT invoice_item_fk_invoices
- FOREIGN KEY (INVOICE_ID)
- REFERENCES invoices (INVOICE_ID)
- );
- CREATE TABLE product_types
- (
- PRODUCT_ID INT NOT NULL,
- DESCRIPTION VARCHAR(255),
- CONSTRAINT product_types_fk_products
- FOREIGN KEY (PRODUCT_ID)
- REFERENCES products (PRODUCT_ID)
- );
- CREATE TABLE catering_event
- (
- EVENT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- EVENT_DATE DATE,
- INVOICE_ID INT NOT NULL,
- CONSTRAINT catering_event_fk_invoices
- FOREIGN KEY (INVOICE_ID)
- REFERENCES invoices (INVOICE_ID)
- );
- CREATE TABLE sale
- (
- SALE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- SALE_DATE DATE,
- INVOICE_ID INT NOT NULL,
- CONSTRAINT sale_fk_invoices
- FOREIGN KEY (INVOICE_ID)
- REFERENCES invoices (INVOICE_ID),
- EMPLOYEE_ID INT NOT NULL,
- CONSTRAINT sale_fk_employees
- FOREIGN KEY (EMPLOYEE_ID)
- REFERENCES employees (EMPLOYEE_ID)
- );
- CREATE TABLE delivery
- (
- DELIVERY_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- DELIVERY_DATE DATE,
- SALE_ID INT NOT NULL,
- CONSTRAINT delivery_fk_sale
- FOREIGN KEY (SALE_ID)
- REFERENCES sale (SALE_ID)
- );
- CREATE TABLE stock
- (
- PRODUCT_ID INT NOT NULL,
- DELIVERY_ID INT NOT NULL,
- EXPIRATION DATE,
- CONSTRAINT stock_fk_products
- FOREIGN KEY(PRODUCT_ID)
- REFERENCES products (PRODUCT_ID),
- CONSTRAINT stock_fk_delivery
- FOREIGN KEY(DELIVERY_ID)
- REFERENCES delivery(DELIVERY_ID)
- );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement