Changes between Version 7 and Version 8 of ddlScript.sql


Ignore:
Timestamp:
12/27/25 21:20:16 (3 days ago)
Author:
235018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddlScript.sql

    v7 v8  
    11{{{#!sql
    22-- Delete tables if they exist
    3 DROP TABLE IF EXISTS product CASCADE;
    4 DROP TABLE IF EXISTS image CASCADE;
    5 DROP TABLE IF EXISTS color CASCADE;
    6 DROP TABLE IF EXISTS store CASCADE;
    7 DROP TABLE IF EXISTS personal CASCADE;
    8 DROP TABLE IF EXISTS permissions CASCADE;
    9 DROP TABLE IF EXISTS boss CASCADE;
    10 DROP TABLE IF EXISTS employees CASCADE;
    11 DROP TABLE IF EXISTS client CASCADE;
    12 DROP TABLE IF EXISTS delivery_address CASCADE;
    13 DROP TABLE IF EXISTS order CASCADE;
    14 DROP TABLE IF EXISTS report CASCADE;
    15 DROP TABLE IF EXISTS monthly_profit CASCADE;
    16 DROP TABLE IF EXISTS request CASCADE;
    17 DROP TABLE IF EXISTS makes_request CASCADE;
    18 DROP TABLE IF EXISTS answers CASCADE;
    19 DROP TABLE IF EXISTS for_store CASCADE;
    20 DROP TABLE IF EXISTS review CASCADE;
    21 DROP TABLE IF EXISTS change CASCADE;
    22 DROP TABLE IF EXISTS makes_change CASCADE;
     3DROP TABLE IF EXISTS product;
     4DROP TABLE IF EXISTS image;
     5DROP TABLE IF EXISTS color;
     6DROP TABLE IF EXISTS store;
     7DROP TABLE IF EXISTS personal;
     8DROP TABLE IF EXISTS permissions;
     9DROP TABLE IF EXISTS boss;
     10DROP TABLE IF EXISTS employees;
     11DROP TABLE IF EXISTS client;
     12DROP TABLE IF EXISTS delivery_address;
     13DROP TABLE IF EXISTS "order";
     14DROP TABLE IF EXISTS report;
     15DROP TABLE IF EXISTS monthly_profit;
     16DROP TABLE IF EXISTS request;
     17DROP TABLE IF EXISTS makes_request;
     18DROP TABLE IF EXISTS answers;
     19DROP TABLE IF EXISTS for_store;
     20DROP TABLE IF EXISTS review;
     21DROP TABLE IF EXISTS "change";
     22DROP TABLE IF EXISTS makes_change;
    2323--  DROP TABLE IF EXISTS made_on CASCADE;
    24 DROP TABLE IF EXISTS works_in_store CASCADE;
    25 DROP TABLE IF EXISTS worked CASCADE;
    26 DROP TABLE IF EXISTS sells CASCADE;
    27 DROP TABLE IF EXISTS includes CASCADE;
    28 DROP TABLE IF EXISTS approves CASCADE;
    29 DROP TABLE IF EXISTS exchanges_data CASCADE;
     24DROP TABLE IF EXISTS works_in_store;
     25DROP TABLE IF EXISTS worked;
     26DROP TABLE IF EXISTS sells;
     27DROP TABLE IF EXISTS includes;
     28DROP TABLE IF EXISTS approves;
     29DROP TABLE IF EXISTS exchanges_data;
    3030
    3131
    … …  
    5353-- Create COLOR table
    5454CREATE TABLE color (
    55     product_code VARCHAR(8) PRIMARY NULL REFERENCES product(code)
     55    product_code VARCHAR(8) REFERENCES product(code)
    5656        ON DELETE CASCADE,
    5757    color VARCHAR(50)
    … …  
    6666    physical_address VARCHAR(100) NOT NULL,   -- ADDRESS FORMAT : st.(st-name) num.(unit-num), (city) (post-code), (country)?????
    6767    store_email VARCHAR(20) UNIQUE NOT NULL,
    68     rating NUMERIC NOT NULL DEFAULT 0 CHECK (rating>=0.0)
     68    rating NUMERIC NOT NULL DEFAULT 0 CHECK (rating>=0.0 AND rating<=5.0)
    6969);
    7070
    … …  
    106106-- Create CLIENT table
    107107CREATE TABLE client (
    108     client_ID INTEGER PRIMARY KEY,                     -- Format, just an order of an integer, starting from 1000. EX : 1005, 1023,  1200, ...
     108    client_ID SERIAL PRIMARY KEY,                     -- Format, just an order of an integer, starting from 1000. EX : 1005, 1023,  1200, ...
    109109    first_name VARCHAR(50) NOT NULL,
    110110    last_name VARCHAR(50) NOT NULL,
    … …  
    174174    client_ID INTEGER NOT NULL REFERENCES client(client_ID)
    175175        ON DELETE CASCADE,
    176     order_num VARCHAR(10) UNIQUE NOT NULL REFERENCES "order"(order_num)
     176    order_num VARCHAR(11) UNIQUE NOT NULL REFERENCES "order"(order_num)
    177177        ON DELETE CASCADE,
    178178    PRIMARY KEY(client_ID, order_num)
    … …  
    202202-- Create REVIEW table
    203203CREATE TABLE review (
    204     order_num VARCHAR(11) REFERENCES "order"(order_num)
     204    order_num VARCHAR(11) PRIMARY KEY REFERENCES "order"(order_num)
    205205        ON DELETE CASCADE,               -- Should the review be deleted when the order is deleted?
    206206    comment VARCHAR(300),
    … …  
    304304    PRIMARY KEY (report_date, store_ID)
    305305);
     306
    306307}}};