Changes between Version 9 and Version 10 of ddlScript-with-help-of-AI.sql


Ignore:
Timestamp:
09/19/26 07:46:38 (10 days ago)
Author:
235018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddlScript-with-help-of-AI.sql

    v9 v10  
    11{{{#!sql
    2 -- Delete tables if they exist (in reverse dependency order)
     2-- Delete tables if they exist
    33DROP TABLE IF EXISTS category;
    4 DROP TABLE IF EXISTS store;
    54DROP TABLE IF EXISTS product;
    65DROP TABLE IF EXISTS image;
    76DROP TABLE IF EXISTS color;
     7DROP TABLE IF EXISTS store;
    88DROP TABLE IF EXISTS personal;
    99DROP TABLE IF EXISTS permissions;
    … …  
    2222DROP TABLE IF EXISTS "change";
    2323DROP TABLE IF EXISTS makes_change;
    24 --  DROP TABLE IF EXISTS made_on CASCADE;
    2524DROP TABLE IF EXISTS works_in_store;
    2625DROP TABLE IF EXISTS worked;
    … …  
    3635    id SERIAL PRIMARY KEY,
    3736    name VARCHAR(50) NOT NULL,
    38     parent_category_id INTEGER REFERENCES category(id)
     37    parent_category_id INTEGER REFERENCES category(id) NOT NULL
     38);
     39
     40-- Table 1
     41-- Create PRODUCT table
     42CREATE TABLE product (
     43    code VARCHAR(8) PRIMARY KEY DEFAULT -1,                      -- In format 3 digits for store, 5 digits for product (Total 8 digits). Ex. 00100155, 01500020,...
     44    price DECIMAL(10,2) NOT NULL CHECK (price >= 0.0),
     45    availability INTEGER NOT NULL,
     46    weight DECIMAL(5,2) NOT NULL CHECK (weight > 0),    -- Shown in kg
     47    width_x_length_x_depth VARCHAR(20) NOT NULL,
     48    aprox_production_time INTEGER NOT NULL,
     49    description VARCHAR(500) NOT NULL,
     50    category_id INTEGER NOT NULL REFERENCES category(id)
     51        ON DELETE SET DEFAULT
     52);
     53
     54-- Table 2
     55-- Create IMAGE table
     56CREATE TABLE image(
     57    product_code VARCHAR(8) REFERENCES product(code)
     58        ON DELETE CASCADE,
     59    image VARCHAR NOT NULL DEFAULT 'Image NOT found!'
     60);
     61
     62-- Table 3
     63-- Create COLOR table
     64CREATE TABLE color (
     65    product_code VARCHAR(8) REFERENCES product(code)
     66        ON DELETE CASCADE,
     67    color VARCHAR(50)
    3968);
    4069
    … …  
    4675    date_of_founding DATE NOT NULL,
    4776    physical_address VARCHAR(100) NOT NULL,   -- ADDRESS FORMAT : st.(st-name) num.(unit-num), (city) (post-code), (country)?????
    48     store_email VARCHAR(40) UNIQUE NOT NULL,
     77    store_email VARCHAR(40) UNIQUE NOT NULL CHECK (store_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    4978    rating DECIMAL(2,1) NOT NULL DEFAULT 0 CHECK (rating>=0.0 AND rating<=5.0)
    5079);
    51 
    52 
    53 -- Table 1
    54 -- Create PRODUCT table
    55 CREATE TABLE product (
    56     code VARCHAR(8) PRIMARY KEY,                      -- In format 3 digits for store, 5 digits for product (Total 8 digits). Ex. 00100155, 01500020,...
    57     price DECIMAL(10,2) NOT NULL CHECK (price >= 0.0),
    58     availability INTEGER NOT NULL,
    59     weight DECIMAL(5,2) NOT NULL CHECK (weight > 0),    -- Shown in kg
    60     width_x_length_x_depth VARCHAR(20) NOT NULL,
    61     aprox_production_time INTEGER NOT NULL,
    62     description VARCHAR(500) NOT NULL,
    63     cathegory_id INTEGER NOT NULL REFERENCES category(id)
    64         ON DELETE SET DEFAULT,
    65     store_id VARCHAR(3) REFERENCES store(store_ID)
    66 );
    67 
    68 -- Table 2
    69 -- Create IMAGE table
    70 CREATE TABLE image(
    71     product_code VARCHAR(8) REFERENCES product(code)
    72         ON DELETE CASCADE,
    73     image VARCHAR NOT NULL DEFAULT 'Image NOT found!',
    74     PRIMARY KEY(product_code, image)
    75 );
    76 
    77 -- Table 3
    78 -- Create COLOR table
    79 CREATE TABLE color (
    80     product_code VARCHAR(8) REFERENCES product(code)
    81         ON DELETE CASCADE,
    82     color VARCHAR(50),
    83     PRIMARY KEY(product_code, color)
    84 );
    85 
    8680
    8781-- Table 5
    8882-- Create PERSONAL table
    8983CREATE TABLE personal (
    90     id CHAR(7) PRIMARY KEY,                                     -- Format 3 digits for store, 4 digits for employee ID
     84    id VARCHAR(10) PRIMARY KEY,     -- format: storeId(3) + '001' for owner, storeId(3) + employeeNum(3) for employees)
    9185    first_name VARCHAR(20) NOT NULL,
    9286    last_name VARCHAR(20) NOT NULL,
    93     ssn CHAR(13) NOT NULL,
    94     email VARCHAR(50) UNIQUE NOT NULL,
     87    ssn VARCHAR(13) NOT NULL CHECK (ssn ~ '^[0-9]{13}$'),                                   
     88    email VARCHAR(50) UNIQUE NOT NULL     CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    9589    password VARCHAR NOT NULL
    9690);
    … …  
    9993-- Create PERMISSIONS table
    10094CREATE TABLE permissions (
    101     personal_ID CHAR(7) REFERENCES personal(id)
    102         ON DELETE CASCADE,
    103     type VARCHAR(100) UNIQUE NOT NULL,
    104     authorisation VARCHAR(50) NOT NULL,
    105     PRIMARY KEY(personal_ID, type)
     95    personal_is VARCHAR(10) PRIMARY KEY REFERENCES personal(id)
     96        ON DELETE CASCADE,
     97    type VARCHAR(50) NOT NULL,
     98    authorisation VARCHAR(50) NOT NULL
    10699);
    107100
    … …  
    109102-- Create BOSS table
    110103CREATE TABLE boss (
    111     boss_ID CHAR(7) PRIMARY KEY REFERENCES personal(id)
    112         ON DELETE CASCADE,
    113     signature VARCHAR UNIQUE NOT NULL
     104    boss_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id)
     105        ON DELETE CASCADE
    114106);
    115107
    … …  
    117109-- Create EMPLOYEES table
    118110CREATE TABLE employees (
    119     employee_ID CHAR(7) PRIMARY KEY REFERENCES personal(id)
     111    employee_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id)
    120112        ON DELETE CASCADE,
    121113    date_of_hire DATE NOT NULL
    … …  
    128120    first_name VARCHAR(50) NOT NULL,
    129121    last_name VARCHAR(50) NOT NULL,
    130     email VARCHAR(50) UNIQUE NOT NULL,
     122    email VARCHAR(50) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    131123    password VARCHAR NOT NULL
    132124);
    … …  
    150142    client_ID INTEGER REFERENCES client(client_ID)
    151143        ON DELETE CASCADE,
    152     quantity INTEGER NOT NULL,
    153144    status VARCHAR(20) NOT NULL DEFAULT 'placed order',
    154145    last_date_mod TIMESTAMP NOT NULL,
    155146    payment_method VARCHAR (250) NOT NULL,                  -- Format : "credit card, **** **** **** 6750" , or "cash", or "paypal, user@gmail.com",... Contains important information that are going to be needed when issuing a refund
    156147    discount DECIMAL(5,2) DEFAULT 0.0 CHECK(discount>=0.0 AND discount<= 100.00),
    157     delivery_address VARCHAR NOT NULL,                        -- Combined, street, city and postcode of client's chosen address. Default is the delivery address marked by client
    158148    CONSTRAINT check_status (status IN ('placed order', 'being processed', 'shipping', 'delivered', 'canceled'))
    159149);
    160150
    161 
    162151-- Table 12
     152-- Create REVIEW table
     153CREATE TABLE review (
     154    order_num VARCHAR(11) PRIMARY KEY REFERENCES "order"(order_num)
     155        ON DELETE CASCADE,               
     156    comment VARCHAR(300),
     157    rating DECIMMAL(1,1) NOT NULL CHECK(rating>=0.0 AND rating<=5.0),
     158    last_mod_date TIMESTAMP NOT NULL
     159);
     160
     161-- Table 13
     162-- Create REFUND table
     163CREATE TABLE refund (
     164    refund_id SERIAL PRIMARY KEY,
     165    order_num VARCHAR(11) PARTIAL KEY REFERENCES "order"(order_num)
     166        ON DELETE CASCADE,               
     167    reason VARCHAR(300),
     168    amount DECIMAL(5,2) NOT NULL,
     169    status VARCHAR(100) NOT NULL DEFAULT 'requested refund',
     170    CONSTRAINT check_refund_status (status IN ('requested refund', 'being revied', 'approved', 'not approved', 'processed'))
     171
     172);
     173
     174
     175-- Table 14
    163176-- Create REPORT table
    164177CREATE TABLE report (
    … …  
    167180        ON DELETE CASCADE,
    168181    overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0),
    169     sales_trend VARCHAR NOT NULL,                                                    -- graph
    170     marketing_growth VARCHAR NOT NULL,                                               -- graph
    171     owner_signature VARCHAR(30) NOT NULL,                                            
     182    sales_trend VARCHAR(100) NOT NULL,                                                    -- graph
     183    marketing_growth VARCHAR(100) NOT NULL,                                               -- graph     
     184    owner_signature VARCHAR(50) NOT NULL DEFAULT 'Not signed yet',                                 
    172185    PRIMARY KEY (date, store_ID)
    173186);
    174187
    175 -- Table 13
     188-- Table 15
    176189-- Create MONTHLY_PROFIT table
    177190CREATE TABLE monthly_profit (
    … …  
    185198);
    186199
    187 -- Table 14
     200-- Table 16
     201-- Create EXCHANGES_DATA table
     202CREATE TABLE exchanges_data (
     203    report_date TIMESTAMP NOT NULL,
     204    store_ID VARCHAR(3) NOT NULL REFERENCES report(store_ID)
     205        ON DELETE CASCADE,
     206    monthly_profit NUMERIC NOT NULL DEFAULT 0.0,
     207    date TIMESTAMP NOT NULL,
     208    sales NUMERIC NOT NULL DEFAULT 0.0,                            -- Total revenue achieved in sales                 
     209    damages NUMERIC NOT NULL DEFAULT 0.0 CHECK (damages<=0),            -- Total loss in damages/refunds
     210    PRIMARY KEY (report_date, store_ID),
     211    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
     212        ON DELETE CASCADE
     213);
     214
     215
     216
     217-- Table 17
    188218-- Create REQUEST table
    189219CREATE TABLE request (
    … …  
    192222    problem VARCHAR(300) NOT NULL,
    193223    notes_of_communication VARCHAR,
    194     customer_satisfaction NUMERIC NOT NULL  -- Fixed from "costumer" to "customer"
    195 );
    196 
    197 -- Table 15
     224    customer_satisfaction NUMERIC NOT NULL 
     225);
     226
     227-- Table 18
    198228-- Create MAKES_REQUEST table
    199229CREATE TABLE makes_request (
    200230    client_ID INTEGER NOT NULL REFERENCES client(client_ID)
    201231        ON DELETE CASCADE,
    202     request_num VARCHAR(11) UNIQUE NOT NULL REFERENCES request(request_num)
     232    order_num VARCHAR(11) UNIQUE NOT NULL REFERENCES "order"(order_num)
    203233        ON DELETE CASCADE,
    204234    PRIMARY KEY(client_ID, order_num)
    205235);
    206236
    207 -- Table 16
     237-- Table 19
    208238-- Create ANSWERS table
    209239CREATE TABLE answers (
    210240    request_num VARCHAR(14) REFERENCES request(request_num)
    211241        ON DELETE CASCADE,
    212     personal_ID CHAR(7) NOT NULL REFERENCES personal(id)
    213         ON DELETE CASCADE,
    214     PRIMARY KEY(request_num, personal_ID)
    215 );
    216 
    217 -- Table 17
     242    personal_id VARCHAR(10) NOT NULL REFERENCES personal(id)
     243        ON DELETE CASCADE,
     244    PRIMARY KEY(request_num, personal_id)
     245);
     246
     247-- Table 20
    218248-- Create FOR_STORE table
    219249CREATE TABLE for_store (
    … …  
    225255);
    226256
    227 -- Table 18
    228 -- Create REVIEW table
    229 CREATE TABLE review (
    230     order_num VARCHAR(11) PRIMARY KEY REFERENCES "order"(order_num)
    231         ON DELETE CASCADE,               -- Should the review be deleted when the order is deleted?
    232     comment VARCHAR(300),
    233     rating NUMERIC NOT NULL,
    234     last_mod_date TIMESTAMP NOT NULL
    235 );
    236 
    237 -- Table 19
     257
     258-- Table 21
    238259-- Create CHANGE table
    239260CREATE TABLE "change" (
    … …  
    245266);
    246267
    247 -- Table 20
     268-- Table 22
    248269-- Create MAKES_CHANGE table
    249270CREATE TABLE makes_change (
    250     personal_ID CHAR(7) REFERENCES personal(id)
     271    personal_id VARCHAR(10) REFERENCES personal(id)
    251272        ON DELETE CASCADE,
    252273    change_date_time TIMESTAMP,
    253274    product_code VARCHAR(8),
    254     PRIMARY KEY(personal_ID, change_date_time, product_code),
     275    PRIMARY KEY(personal_id, change_date_time, product_code),
    255276    FOREIGN KEY (change_date_time, product_code) REFERENCES "change"(date_and_time, product_code)
    256277        ON DELETE CASCADE
    257278);
    258279
    259 -- Table 22
     280-- Table 23
    260281-- Create WORKS_IN_STORE table
    261282CREATE TABLE works_in_store (
    262     personal_ID VARCHAR(5) REFERENCES personal(id)
     283    personal_id VARCHAR(10) REFERENCES personal(id)
    263284        ON DELETE CASCADE,
    264285    store_ID VARCHAR(3) REFERENCES store(store_ID)
    265286        ON DELETE CASCADE,
    266     PRIMARY KEY(personal_ID, store_ID)
    267 );
    268 
    269 -- Table 23
     287    PRIMARY KEY(personal_id, store_ID)
     288);
     289
     290-- Table 24
    270291-- Create WORKED table
    271292CREATE TABLE worked (
    272     personal_ID CHAR(7) REFERENCES personal(id)
     293    personal_id VARCHAR(10) REFERENCES personal(id)
    273294        ON DELETE CASCADE,                   
    274295    report_date TIMESTAMP,
    275296    store_ID VARCHAR(3),     
    276     wage NUMERIC NOT NULL CHECK (wage>=62),
    277     pay_method VARCHAR DEFAULT 'hourly',
     297    wage NUMERIC NOT NULL CHECK (wage>=0),
     298    pay_method VARCHAR DEFAULT 'full-time',
    278299    total_hours NUMERIC NOT NULL,
    279300    week VARCHAR(23) NOT NULL,                       -- Format : 15.12.2025 - 21.12.2025
    280     PRIMARY KEY (personal_ID, report_date, store_ID),
     301    PRIMARY KEY (personal_SSN, report_date, store_ID),
    281302    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
    282         ON DELETE CASCADE
    283 );
    284 
    285 -- Table 24
     303        ON DELETE CASCADE,
     304    CONSTRAINT check_pay_method (pay_method IN ('full_time', 'part-time', 'custom'))
     305);
     306
     307-- Table 25
    286308-- Create SELLS table
    287309CREATE TABLE sells (
    … …  
    294316);
    295317
    296 -- Table 25
     318-- Table 26
    297319-- Create INCLUDES table
    298320CREATE TABLE includes (
    … …  
    301323    product_code VARCHAR(8) REFERENCES product(code)
    302324        ON DELETE CASCADE,
     325    quantity INTEGER NOT NULL CHECK(quantity>=0),
    303326    PRIMARY KEY (order_num, product_code)
    304327);
    305328
    306 -- Table 26
     329-- Table 27
    307330-- Create APPROVES table
    308331CREATE TABLE approves (
    309     boss_ID CHAR(7) REFERENCES boss(boss_ID)
     332    boss_id VARCHAR(10) REFERENCES boss(boss_id)
    310333        ON DELETE CASCADE,
    311334    report_date TIMESTAMP,
    312335    store_ID VARCHAR(3),
    313336    owner_signature VARCHAR NOT NULL,
    314     PRIMARY KEY (boss_ID, report_date, store_ID),
     337    PRIMARY KEY (boss_SSN, report_date, store_ID),
    315338    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
    316339        ON DELETE CASCADE
    317340);
    318341
    319 -- Table 27
    320 -- Create EXCHANGES_DATA table
    321 CREATE TABLE exchanges_data (
    322     report_date TIMESTAMP NOT NULL,
    323     store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID)
    324         ON DELETE CASCADE,
    325     monthly_profit NUMERIC NOT NULL DEFAULT 0.0,
    326     date TIMESTAMP NOT NULL,
    327     sales NUMERIC NOT NULL,                                          -- Total profit achieved in sales (not "sells")                   
    328     damages NUMERIC NOT NULL DEFAULT 0.0 CHECK (damages>=0),         -- Total loss in damages/refunds
    329     FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
    330         ON DELETE CASCADE,
    331     PRIMARY KEY (report_date, store_ID)
    332 );
    333 
    334 -- Table 28
    335 -- Create REFUND table
    336 CREATE TABLE refund (
    337     refund_id INTEGER PRIMARY KEY,
    338     order_num VARCHAR(11) REFERENCES "order"(order_num)
    339         ON DELETE CASCADE,
    340     amount DECIMAL(10,2) NOT NULL CHECK(amount > 0.0),
    341     reason VARCHAR(200),
    342     status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'processed'))
    343 );
    344 
    345342
    346343