-- Delete tables if they exist DROP TABLE IF EXISTS product CASCADE; DROP TABLE IF EXISTS image CASCADE; DROP TABLE IF EXISTS color CASCADE; DROP TABLE IF EXISTS store CASCADE; DROP TABLE IF EXISTS personal CASCADE; DROP TABLE IF EXISTS permissions CASCADE; DROP TABLE IF EXISTS boss CASCADE; DROP TABLE IF EXISTS employees CASCADE; DROP TABLE IF EXISTS client CASCADE; DROP TABLE IF EXISTS delivery_address CASCADE; DROP TABLE IF EXISTS order CASCADE; DROP TABLE IF EXISTS report CASCADE; DROP TABLE IF EXISTS monthly_profit CASCADE; DROP TABLE IF EXISTS request CASCADE; DROP TABLE IF EXISTS makes_request CASCADE; DROP TABLE IF EXISTS answers CASCADE; DROP TABLE IF EXISTS for_store CASCADE; DROP TABLE IF EXISTS review CASCADE; DROP TABLE IF EXISTS change CASCADE; DROP TABLE IF EXISTS makes_change CASCADE; -- DROP TABLE IF EXISTS made_on CASCADE; DROP TABLE IF EXISTS works_in_store CASCADE; DROP TABLE IF EXISTS worked CASCADE; DROP TABLE IF EXISTS sells CASCADE; DROP TABLE IF EXISTS includes CASCADE; DROP TABLE IF EXISTS approves CASCADE; DROP TABLE IF EXISTS exchanges_data CASCADE;

-- Table 1 -- Create PRODUCT table CREATE TABLE product (

code SERIAL PRIMARY KEY, -- In format 3 digits for store, 5 digits for product (Total 8 digits). Ex. 00100155, 01500020,... price NUMERIC NOT NULL CHECK (price >= 0), availability INTEGER NOT NULL, weight NUMERIC NOT NULL CHECK (weight > 0), width_x_length_x_depth VARCHAR(20) NOT NULL, aprox_production_time INTEGER NOT NULL, description VARCHAR(1000) NOT NULL

);

-- Table 2 -- Create IMAGE table CREATE TABLE image(

product_code VARCHAR PRIMARY KEY REFERENCES product(code)

ON DELETE CASCADE,

image VARCHAR NOT NULL DEFAULT 'Image NOT found!'

);

-- Table 3 -- Create COLOR table CREATE TABLE color (

product_code VARCHAR PRIMARY NULL REFERENCES product(code)

ON DELETE CASCADE,

color VARCHAR(50)

);

-- Table 4 -- Create STORE table CREATE TABLE store (

store_ID SERIAL PRIMARY KEY, -- In the format of 3 digits. Example : 001, 101, 567, 996,... name VARCHAR(50) UNIQUE NOT NULL, date_of_founding DATE NOT NULL, physical_address VARCHAR(100) NOT NULL, -- ADDRESS FORMAT : st.(st-name) num.(unit-num), (city) (post-code), (country)????? store_email VARCHAR(20) UNIQUE NOT NULL, rating NUMERIC NOT NULL DEFAULT 0 CHECK (rating>=0.0)

);

-- Table 5 -- Create PERSONAL table CREATE TABLE personal (

ssn VARCHAR(13) PRIMARY KEY, -- Format 13 digits : 0101001451001, 1209995423736,... first_name VARCHAR(20) NOT NULL, last_name VARCHAR(20) NOT NULL, email VARCHAR(50) UNIQUE NOT NULL, password VARCHAR NOT NULL

);

-- Table 6 -- Create PERMISSIONS table CREATE TABLE permissions (

personal_SSN VARCHAR(13) PRIMARY KEY REFERENCES personal(ssn)

ON DELETE CASCADE,

type VARCHAR(100) UNIQUE NOT NULL, authorisation VARCHAR(50) NOT NULL

);

-- Table 7 -- Create BOSS table CREATE TABLE boss (

boss_SSN VARCHAR(13) PRIMARY KEY REFERENCES personal(ssn)

ON DELETE CASCADE

);

-- Table 8 -- Create EMPLOYEES table CREATE TABLE employees (

employee_SSN VARCHAR(13) PRIMARY KEY REFERENCES personal(ssn)

ON DELETE CASCADE,

date_of_hire DATE NOT NULL

);

-- Table 9 -- Create CLIENT table CREATE TABLE client (

client_ID SERIAL PRIMARY KEY, -- Format, just an order of an integer, starting from 1000. EX : 1005, 1023, 1200, ... first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(50) UNIQUE NOT NULL, password VARCHAR NOT NULL

);

-- Table 10 -- Create DELIVERY_ADDRESS table CREATE TABLE delivery_address (

client_ID INTEGER PRIMARY KEY REFERENCES client(client_ID)

ON DELETE CASCADE,

address VARCHAR(100) NOT NULL -- ADDRESS FORMAT : st.(st-name) num.(unit-num), (city) (post-code), (country)?????

);

-- Table 11 -- Create ORDER table CREATE TABLE "order" (

order_num SERIAL PRIMARY KEY, -- In the format: 3 digits for store ID, 2 digits for year when order is placed, 5 digits for the number of order for that store in that year client_ID INTEGER REFERENCES client(client_ID)

ON DELETE CASCADE,

quantity INTEGER NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'placed order', last_date_mod TIMESTAMP NOT NULL, payment_method VARCHAR (250) NOT NULL, discount NUMERIC DEFAULT 0.0

);

-- Table 12 -- Create REPORT table CREATE TABLE report (

date TIMESTAMP NOT NULL, store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID)

ON DELETE CASCADE,

overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0), sales_trend VARCHAR NOT NULL, marketing_growth VARCHAR NOT NULL, owner_signature VARCHAR(30) NOT NULL, PRIMARY KEY (date, store_ID)

);

-- Table 13 -- Create MONTHLY_PROFIT table CREATE TABLE monthly_profit (

report_date TIMESTAMP NOT NULL, store_ID VARCHAR(3) NOT NULL, month_and_year DATE NOT NULL, profit NUMERIC NOT NULL DEFAULT 0.0, PRIMARY KEY(report_date, store_ID), FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)

ON DELETE CASCADE

);

-- Table 14 -- Create REQUEST table CREATE TABLE request (

request_num SERIAL PRIMARY KEY, -- Format: 3 digits for store ID, 2 digits for month in which the request is placed, 2 digits for year, 3 digits for request number date_and_time TIMESTAMP NOT NULL, problem VARCHAR(1000) NOT NULL, notes_of_communication VARCHAR(300), costumer_satisfaction NUMERIC NOT NULL

);

-- Table 15 -- Create MAKES_REQUEST table CREATE TABLE makes_request (

client_ID INTEGER NOT NULL REFERENCES client(client_ID)

ON DELETE CASCADE,

order_num VARCHAR(10) UNIQUE NOT NULL REFERENCES "order"(order_num)

ON DELETE CASCADE,

PRIMARY KEY(client_ID, order_num)

);

-- Table 16 -- Create ANSWERS table CREATE TABLE answers (

request_num VARCHAR(10) REFERENCES request(request_num)

ON DELETE CASCADE,

personal_SSN VARCHAR(13) NOT NULL REFERENCES personal(ssn)

ON DELETE CASCADE,

PRIMARY KEY(request_num, personal_SSN)

);

-- Table 17 -- Create FOR_STORE table CREATE TABLE for_store (

request_num VARCHAR(10) REFERENCES request(request_num)

ON DELETE CASCADE,

store_ID VARCHAR(3) REFERENCES store(store_ID)

ON DELETE CASCADE,

PRIMARY KEY(request_num, store_ID)

);

-- Table 18 -- Create REVIEW table CREATE TABLE review (

order_num VARCHAR(10) REFERENCES "order"(order_num)

ON DELETE CASCADE, -- Should the review be deleted when the order is deleted?

comment VARCHAR(300), rating NUMERIC NOT NULL, last_mod_date TIMESTAMP NOT NULL

);

-- Table 19 -- Create CHANGE table CREATE TABLE "change" (

date_and_time TIMESTAMP NOT NULL, product_code VARCHAR(8) REFERENCES product(code)

ON DELETE CASCADE,

changes VARCHAR NOT NULL, PRIMARY KEY (date_and_time, product_code)

);

-- Table 20 -- Create MAKES_CHANGE table CREATE TABLE makes_change (

personal_SSN VARCHAR(13) REFERENCES personal(ssn)

ON DELETE CASCADE,

change_date_time TIMESTAMP, product_code VARCHAR(8), PRIMARY KEY(personal_SSN, change_date_time, product_code), FOREIGN KEY (change_date_time, product_code) REFERENCES "change"(date_and_time, product_code)

ON DELETE CASCADE

);

-- Table 22 -- Create WORKS_IN_STORE table CREATE TABLE works_in_store (

personal_SSN VARCHAR(13) REFERENCES personal(ssn)

ON DELETE CASCADE,

store_ID VARCHAR(3) REFERENCES store(store_ID)

ON DELETE CASCADE,

PRIMARY KEY(personal_SSN, store_ID)

);

-- Table 23 -- Create WORKED table CREATE TABLE worked (

personal_SSN VARCHAR(13) REFERENCES personal(ssn)

ON DELETE CASCADE,

report_date TIMESTAMP, store_ID VARCHAR(3), wage NUMERIC NOT NULL CHECK (wage>=62), pay_method VARCHAR(20) DEFAULT 'hourly', total_hours NUMERIC NOT NULL, week VARCHAR(24) NOT NULL, PRIMARY KEY (personal_SSN, report_date, store_ID), FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)

ON DELETE CASCADE

);

-- Table 24 -- Create SELLS table CREATE TABLE sells (

product_code VARCHAR(8) REFERENCES product(code)

ON DELETE CASCADE,

store_ID VARCHAR(3) REFERENCES store(store_ID)

ON DELETE CASCADE,

discount NUMERIC NOT NULL DEFAULT 0.0, PRIMARY KEY (product_code, store_ID)

);

-- Table 25 -- Create INCLUDES table CREATE TABLE includes (

order_num VARCHAR(10) REFERENCES "order"(order_num)

ON DELETE CASCADE,

product_code VARCHAR(8) REFERENCES product(code)

ON DELETE CASCADE,

PRIMARY KEY (order_num, product_code)

);

-- Table 26 -- Create APPROVES table CREATE TABLE approves (

boss_SSN VARCHAR(13) REFERENCES boss(boss_SSN)

ON DELETE CASCADE,

report_date TIMESTAMP, store_ID VARCHAR(3), owner_signature VARCHAR NOT NULL, PRIMARY KEY (boss_SSN, report_date, store_ID), FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)

ON DELETE CASCADE

);

-- Table 27 CREATE TABLE exchanges_data (

report_date TIMESTAMP NOT NULL, store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID)

ON DELETE CASCADE,

monthly_profit NUMERIC NOT NULL DEFAULT 0.0, date TIMESTAMP NOT NULL, sales NUMERIC NOT NULL, -- Total profit achieved in sells damages NUMERIC NOT NULL DEFAULT 0.0 CHECK (damages>=0), -- Total loss in damages/refunds FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)

ON DELETE CASCADE,

PRIMARY KEY (report_date, store_ID)

);