Changes between Version 4 and Version 5 of ddlScript.sql


Ignore:
Timestamp:
12/24/25 22:23:12 (6 days ago)
Author:
235018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddlScript.sql

    v4 v5  
    1 {{{#!sql
    21-- Delete tables if they exist
    32DROP TABLE IF EXISTS product CASCADE;
    43DROP TABLE IF EXISTS image CASCADE;
    5 DROP TABLE IF EXISTS colors CASCADE;
     4DROP TABLE IF EXISTS color CASCADE;
    65DROP TABLE IF EXISTS store CASCADE;
    76DROP TABLE IF EXISTS personal CASCADE;
    … …  
    2120DROP TABLE IF EXISTS change CASCADE;
    2221DROP TABLE IF EXISTS makes_change CASCADE;
    23 DROP TABLE IF EXISTS made_od CASCADE;
     22--  DROP TABLE IF EXISTS made_on CASCADE;
    2423DROP TABLE IF EXISTS works_in_store CASCADE;
    2524DROP TABLE IF EXISTS worked CASCADE;
    … …  
    2726DROP TABLE IF EXISTS includes CASCADE;
    2827DROP TABLE IF EXISTS approves CASCADE;
    29 DROP TABLE IF EXISTS exchange_data CASCADE;
     28DROP TABLE IF EXISTS exchanges_data CASCADE;
    3029
    3130
    … …  
    3332-- Create PRODUCT table
    3433CREATE TABLE product (
    35     code SERIAL PRIMARY KEY,
     34    code SERIAL PRIMARY KEY,                      -- In format 3 digits for store, 5 digits for product (Total 8 digits). Ex. 00100155, 01500020,...
    3635    price NUMERIC NOT NULL CHECK (price >= 0),
    3736    availability INTEGER NOT NULL,
    3837    weight NUMERIC NOT NULL CHECK (weight > 0),
    39     width_x_length_x_depth VARCHAR(20) NOT NULL,
     38    width_x_length_x_depth VARCHAR(20) NOT NULL, 
    4039    aprox_production_time INTEGER NOT NULL,
    4140    description VARCHAR(1000) NOT NULL
    … …  
    4544-- Create IMAGE table
    4645CREATE TABLE image(
    47     product_code INTEGER PRIMARY KEY REFERENCES product(code)
    48         ON DELETE CASCADE,
    49     image VARCHAR(max) NOT NULL DEFAULT 'Image NOT found!'
    50 );
    51 
     46    product_code VARCHAR PRIMARY KEY REFERENCES product(code)
     47        ON DELETE CASCADE,
     48    image VARCHAR NOT NULL DEFAULT 'Image NOT found!'
     49);
    5250
    5351-- Table 3
    5452-- Create COLOR table
    5553CREATE TABLE color (
    56     product_code INTEGER PRIMARY KEY NULL REFERENCES product(code)
     54    product_code VARCHAR PRIMARY NULL REFERENCES product(code)
    5755        ON DELETE CASCADE,
    5856    color VARCHAR(50)
    5957);
    60 
    6158
    6259-- Table 4
    6360-- Create STORE table
    6461CREATE TABLE store (
    65     store_ID SERIAL PRIMARY KEY
    66         ON DELETE CASCADE,
     62    store_ID SERIAL PRIMARY KEY,             -- In the format of 3 digits. Example : 001, 101, 567, 996,...
    6763    name VARCHAR(50) UNIQUE NOT NULL,
    6864    date_of_founding DATE NOT NULL,
    … …  
    7268);
    7369
    74 
    7570-- Table 5
    7671-- Create PERSONAL table
    77 CREATE TABLE personal (     -- Should a member of the personal have a personal_id, so that one person can work at 2 different stores? Or do we make a Composite key: (personal_ssn, store_id)?
    78     ssn VARCHAR(13) PRIMARY KEY,
     72CREATE TABLE personal (
     73    ssn VARCHAR(13) PRIMARY KEY,                                     -- Format 13 digits :   0101001451001, 1209995423736,...
    7974    first_name VARCHAR(20) NOT NULL,
    8075    last_name VARCHAR(20) NOT NULL,
    8176    email VARCHAR(50) UNIQUE NOT NULL,
    82     password VARCAHR(max) NOT NULL
    83 );
    84 
     77    password VARCHAR NOT NULL
     78);
    8579
    8680-- Table 6
    … …  
    9387);
    9488
    95 
    9689-- Table 7
    9790-- Create BOSS table
    … …  
    10194);
    10295
    103 
    10496-- Table 8
    10597-- Create EMPLOYEES table
    … …  
    110102);
    111103
    112 
    113104-- Table 9
    114105-- Create CLIENT table
    115106CREATE TABLE client (
    116     client_ID SERIAL PRIMARY KEY,
     107    client_ID SERIAL PRIMARY KEY,                     -- Format, just an order of an integer, starting from 1000. EX : 1005, 1023,  1200, ...
    117108    first_name VARCHAR(50) NOT NULL,
    118109    last_name VARCHAR(50) NOT NULL,
    … …  
    121112);
    122113
    123 
    124114-- Table 10
    125115-- Create DELIVERY_ADDRESS table
    … …  
    130120);
    131121
    132 
    133122-- Table 11
    134123-- Create ORDER table
    135 CREATE TABLE order (
    136     order_num SERIAL PRIMARY KEY,
    137     client_ID INTEGER REFERENCES client(client_ID),
     124CREATE TABLE "order" (
     125    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
     126    client_ID INTEGER REFERENCES client(client_ID)
    138127        ON DELETE CASCADE,
    139128    quantity INTEGER NOT NULL,
    140129    status VARCHAR(20) NOT NULL DEFAULT 'placed order',
    141130    last_date_mod TIMESTAMP NOT NULL,
    142     payment_method VARHAR (250) NOT NULL,
    143     discount NUMERIC DEFAULT 0.0,
    144 );
    145 
     131    payment_method VARCHAR (250) NOT NULL,
     132    discount NUMERIC DEFAULT 0.0
     133);
    146134
    147135-- Table 12
    … …  
    149137CREATE TABLE report (
    150138    date TIMESTAMP NOT NULL,
    151     store_ID INTEGER NOT NULL REFERENCES store(store_ID)
    152         ON DELETE CASCADE,
    153     overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0,0),
    154     sales_trend VARCHAR(max) NOT NULL,
    155     marketing_growth VARCHAR(max) NOT NULL,
     139    store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID)
     140        ON DELETE CASCADE,
     141    overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0),
     142    sales_trend VARCHAR NOT NULL,
     143    marketing_growth VARCHAR NOT NULL,
    156144    owner_signature VARCHAR(30) NOT NULL,
    157145    PRIMARY KEY (date, store_ID)
    158146);
    159 
    160147
    161148-- Table 13
    … …  
    163150CREATE TABLE monthly_profit (
    164151    report_date TIMESTAMP NOT NULL,
    165     store_ID INTEGER NOT NULL,
     152    store_ID VARCHAR(3) NOT NULL,
    166153    month_and_year DATE NOT NULL,
    167     profit NUMBER NOT NULL DEFAULT 0.0,
     154    profit NUMERIC NOT NULL DEFAULT 0.0,
    168155    PRIMARY KEY(report_date, store_ID),
    169     FOREIGN KEY (report_date, store_id) REFERENCES report(date, store_ID)
    170         ON DELETE CASCADE
    171 );
    172 
     156    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
     157        ON DELETE CASCADE
     158);
    173159
    174160-- Table 14
    175161-- Create REQUEST table
    176162CREATE TABLE request (
    177     request_num SERIAL PRIMARY KEY,
     163    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
    178164    date_and_time TIMESTAMP NOT NULL,
    179165    problem VARCHAR(1000) NOT NULL,
    … …  
    182168);
    183169
    184 
    185170-- Table 15
    186171-- Create MAKES_REQUEST table
    … …  
    188173    client_ID INTEGER NOT NULL REFERENCES client(client_ID)
    189174        ON DELETE CASCADE,
    190     order_num INTEGER UNIQUE NOT NULL REFERENCES order(order_num)
     175    order_num VARCHAR(10) UNIQUE NOT NULL REFERENCES "order"(order_num)
    191176        ON DELETE CASCADE,
    192177    PRIMARY KEY(client_ID, order_num)
    193178);
    194 
    195179
    196180-- Table 16
    197181-- Create ANSWERS table
    198182CREATE TABLE answers (
    199     request_num INTEGER REFERENCES request(request_num)
     183    request_num VARCHAR(10) REFERENCES request(request_num)
    200184        ON DELETE CASCADE,
    201185    personal_SSN VARCHAR(13) NOT NULL REFERENCES personal(ssn)
    … …  
    203187    PRIMARY KEY(request_num, personal_SSN)
    204188);
    205 
    206189
    207190-- Table 17
    208191-- Create FOR_STORE table
    209192CREATE TABLE for_store (
    210     request_num INTEGER REFERENCES request(request_num)
    211         ON DELETE CASCADE,
    212     store_ID SERIAL REFERENCES store(store_ID)
     193    request_num VARCHAR(10) REFERENCES request(request_num)
     194        ON DELETE CASCADE,
     195    store_ID VARCHAR(3) REFERENCES store(store_ID)
    213196        ON DELETE CASCADE,
    214197    PRIMARY KEY(request_num, store_ID)
    215198);
    216 
    217199
    218200-- Table 18
    219201-- Create REVIEW table
    220202CREATE TABLE review (
    221     order_num INTEGER REFERENCES order(order_num)
    222         ON DELETE CASCADE,               -- SHould the review be deleted when the order is deleted?
     203    order_num VARCHAR(10) REFERENCES "order"(order_num)
     204        ON DELETE CASCADE,               -- Should the review be deleted when the order is deleted?
    223205    comment VARCHAR(300),
    224     rating NUMBER NOT NULL,
     206    rating NUMERIC NOT NULL,
    225207    last_mod_date TIMESTAMP NOT NULL
    226208);
    227 
    228209
    229210-- Table 19
    230211-- Create CHANGE table
    231 CREATE TABLE change (
     212CREATE TABLE "change" (
    232213    date_and_time TIMESTAMP NOT NULL,
    233     product_code INTEGER REFERENCES product(code)
    234         ON DELETE CASCADE,
    235     changes VARCHAR(max) NOT NULL,
     214    product_code VARCHAR(8) REFERENCES product(code)
     215        ON DELETE CASCADE,
     216    changes VARCHAR NOT NULL,
    236217    PRIMARY KEY (date_and_time, product_code)
    237218);
    238 
    239219
    240220-- Table 20
    241221-- Create MAKES_CHANGE table
    242222CREATE TABLE makes_change (
    243     personal_SSN NUMERIC REFERENCES personal(ssn)
     223    personal_SSN VARCHAR(13) REFERENCES personal(ssn)
    244224        ON DELETE CASCADE,
    245225    change_date_time TIMESTAMP,
    246     product_code INTEGER,
     226    product_code VARCHAR(8),
    247227    PRIMARY KEY(personal_SSN, change_date_time, product_code),
    248     FOREIGN KEY (change_date_time, product_code) REFERENCES change(change_date_time, product)
    249         ON DELETE CASCADE
    250 );
    251 
    252 
    253 -- Table 21
    254 -- Create MADE_ON table                   -- Is it needed?
    255 --CREATE TABLE made_on (
    256 --    product_code INTEGER,
    257 --    change_date_time TIMESTAMP,
    258 --    PRIMARY KEY(product_SSN, change_date_time)
    259 --    FOREIGN KEY(product_SSN, change_date_time) REFERENCES change(product_SSN, change_date_time)
    260 --        ON DELETE CASCADE
    261 --);
    262 
     228    FOREIGN KEY (change_date_time, product_code) REFERENCES "change"(date_and_time, product_code)
     229        ON DELETE CASCADE
     230);
    263231
    264232-- Table 22
    … …  
    267235    personal_SSN VARCHAR(13) REFERENCES personal(ssn)
    268236        ON DELETE CASCADE,
    269     store_ID INTEGER REFERENCES store(store_ID)
    270         ON DELETE CASCADE,
    271     PRIMARY KEY(personak_SSN, store_ID)
    272 );
    273 
     237    store_ID VARCHAR(3) REFERENCES store(store_ID)
     238        ON DELETE CASCADE,
     239    PRIMARY KEY(personal_SSN, store_ID)
     240);
    274241
    275242-- Table 23
    … …  
    279246        ON DELETE CASCADE,                   
    280247    report_date TIMESTAMP,
    281     store_ID SERIAL,     
    282     wage NUMERIC NOT NULL CHECK (wage>=62),   -- Anyone should be paid a minimum of 63MKD/hour
     248    store_ID VARCHAR(3),     
     249    wage NUMERIC NOT NULL CHECK (wage>=62),
    283250    pay_method VARCHAR(20) DEFAULT 'hourly',
    284251    total_hours NUMERIC NOT NULL,
    285252    week VARCHAR(24) NOT NULL,
    286253    PRIMARY KEY (personal_SSN, report_date, store_ID),
    287     FOREIGN KEY (report_date, store_ID) REFERENCES report(report_Date, store_ID)
    288         ON DELETE CASCADE
    289 );
    290 
     254    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
     255        ON DELETE CASCADE
     256);
    291257
    292258-- Table 24
    293259-- Create SELLS table
    294260CREATE TABLE sells (
    295     product_code INTEGER REFERENCES product(code)
    296         ON DELETE CASCADE,
    297     store_ID SERIAL REFERENCES store(store_ID)
    298         ON DELETE CASCADE,      -- Should the data be kept if store is deleted?
     261    product_code VARCHAR(8) REFERENCES product(code)
     262        ON DELETE CASCADE,
     263    store_ID VARCHAR(3) REFERENCES store(store_ID)
     264        ON DELETE CASCADE,
    299265    discount NUMERIC NOT NULL DEFAULT 0.0,
    300266    PRIMARY KEY (product_code, store_ID)
    301267);
    302 
    303268
    304269-- Table 25
    305270-- Create INCLUDES table
    306271CREATE TABLE includes (
    307     order_num INTEGER REFERENCES order(order_num)
    308         ON DELETE CASCADE,
    309     product_code INTEGER REFERENCES product(code)
     272    order_num VARCHAR(10) REFERENCES "order"(order_num)
     273        ON DELETE CASCADE,
     274    product_code VARCHAR(8) REFERENCES product(code)
    310275        ON DELETE CASCADE,
    311276    PRIMARY KEY (order_num, product_code)
    312277);
    313 
    314278
    315279-- Table 26
    316280-- Create APPROVES table
    317281CREATE TABLE approves (
    318     boss_SSN NUMERIC REFERENCES boss(boss_SSN)
     282    boss_SSN VARCHAR(13) REFERENCES boss(boss_SSN)
    319283        ON DELETE CASCADE,
    320284    report_date TIMESTAMP,
    321     store_ID INTEGER,
    322     owner_signature VARCHAR(30) NOT NULL,  -- Is it needed?
     285    store_ID VARCHAR(3),
     286    owner_signature VARCHAR NOT NULL,
    323287    PRIMARY KEY (boss_SSN, report_date, store_ID),
    324     FOREIGN KEY (report_date, store_ID) REFERENCES report(report_date, store_ID)
    325         ON DELETE CASCADE
    326 );
    327 
     288    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
     289        ON DELETE CASCADE
     290);
    328291
    329292-- Table 27
    330 -- Create EXCHANGES_DATA table
    331 CREATE TABLE exchanges_date (
    332     report_date TIMESTAMP,    -- Should the data be kept if the report is deleted?
    333     store_ID INTEGER REFERENCES store(store_ID)
     293CREATE TABLE exchanges_data (
     294    report_date TIMESTAMP NOT NULL,
     295    store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID)
    334296        ON DELETE CASCADE,
    335297    monthly_profit NUMERIC NOT NULL DEFAULT 0.0,
    336298    date TIMESTAMP NOT NULL,
    337     sales VARCHAR(max) NOT NULL,
    338     damages VARCHAR(max),
    339     PRIMARY KEY (report_date, store_ID),
    340     FOREIGN KEY (report_date, store_ID) REFERENCES report(report_date, store_ID)
    341         ON DELETE CASCADE
    342 );
    343 
     299    sales NUMERIC NOT NULL,                                          -- Total profit achieved in sells                   
     300    damages NUMERIC NOT NULL DEFAULT 0.0 CHECK (damages>=0),         -- Total loss in damages/refunds
     301    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
     302        ON DELETE CASCADE,
     303    PRIMARY KEY (report_date, store_ID)
     304);