Changes between Version 1 and Version 2 of ddlScript-with-help-of-AI.sql


Ignore:
Timestamp:
12/30/25 10:51:18 (18 hours ago)
Author:
235018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

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

    v1 v2  
    11{{{#!sql
    2 -- Delete tables if they exist
     2-- Delete tables if they exist (in reverse dependency order)
     3DROP TABLE IF EXISTS category;
     4DROP TABLE IF EXISTS store;
    35DROP TABLE IF EXISTS product;
    46DROP TABLE IF EXISTS image;
    57DROP TABLE IF EXISTS color;
    6 DROP TABLE IF EXISTS store;
    78DROP TABLE IF EXISTS personal;
    89DROP TABLE IF EXISTS permissions;
    … …  
    2829DROP TABLE IF EXISTS approves;
    2930DROP TABLE IF EXISTS exchanges_data;
    30 
     31DROP TABLE IF EXISTS refund;
    3132
    3233-- Table 0
    … …  
    3839);
    3940
     41-- Table 4
     42-- Create STORE table
     43CREATE TABLE store (
     44    store_ID VARCHAR(3) PRIMARY KEY,             -- In the format of 3 digits. Example : 001, 101, 567, 996,...
     45    name VARCHAR(50) UNIQUE NOT NULL,
     46    date_of_founding DATE NOT NULL,
     47    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,
     49    rating DECIMAL(2,1) NOT NULL DEFAULT 0 CHECK (rating>=0.0 AND rating<=5.0)
     50);
     51
     52
    4053-- Table 1
    4154-- Create PRODUCT table
    4255CREATE 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,...
     56    code VARCHAR(8) PRIMARY KEY,                      -- In format 3 digits for store, 5 digits for product (Total 8 digits). Ex. 00100155, 01500020,...
    4457    price DECIMAL(10,2) NOT NULL CHECK (price >= 0.0),
    4558    availability INTEGER NOT NULL,
    … …  
    4962    description VARCHAR(500) NOT NULL,
    5063    cathegory_id INTEGER NOT NULL REFERENCES category(id)
    51         ON DELETE SET DEFAULT
     64        ON DELETE SET DEFAULT,
     65    store_id VARCHAR(3) REFERENCES store(store_ID)
    5266);
    5367
    … …  
    6882);
    6983
    70 -- Table 4
    71 -- Create STORE table
    72 CREATE TABLE store (
    73     store_ID VARCHAR(3) PRIMARY KEY,             -- In the format of 3 digits. Example : 001, 101, 567, 996,...
    74     name VARCHAR(50) UNIQUE NOT NULL,
    75     date_of_founding DATE NOT NULL,
    76     physical_address VARCHAR(100) NOT NULL,   -- ADDRESS FORMAT : st.(st-name) num.(unit-num), (city) (post-code), (country)?????
    77     store_email VARCHAR(40) UNIQUE NOT NULL CHECK (store_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    78     rating DECIMAL(2,1) NOT NULL DEFAULT 0 CHECK (rating>=0.0 AND rating<=5.0)
    79 );
    8084
    8185-- Table 5
    8286-- Create PERSONAL table
    8387CREATE TABLE personal (
    84     ssn VARCHAR(13) PRIMARY KEY CHECK (ssn ~ '^[0-9]{13}$'),                                     -- Format 13 digits :   0101001451001, 1209995423736,...
     88    ssn VARCHAR(13) PRIMARY KEY,                                     -- Format 13 digits :   0101001451001, 1209995423736,...
    8589    first_name VARCHAR(20) NOT NULL,
    8690    last_name VARCHAR(20) NOT NULL,
    87     email VARCHAR(50) UNIQUE NOT NULL     CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
     91    email VARCHAR(50) UNIQUE NOT NULL,
    8892    password VARCHAR NOT NULL
    8993);
    … …  
    119123    first_name VARCHAR(50) NOT NULL,
    120124    last_name VARCHAR(50) NOT NULL,
    121     email VARCHAR(50) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
     125    email VARCHAR(50) UNIQUE NOT NULL,
    122126    password VARCHAR NOT NULL
    123127);
    … …  
    145149    last_date_mod TIMESTAMP NOT NULL,
    146150    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
    147     discount DECIMAL(5,2) DEFAULT 0.0 CHECK(discount>=0.0 AND discount<= 100.00),
    148     CONSTRAINT check_status (status IN ('placed order', 'being processed', 'shipping', 'delivered', 'canceled'))
     151    discount DECIMAL(5,2) DEFAULT 0.0 CHECK(discount>=0.0 AND discount<= 100.00)
     152    --CONSTRAINT check_status (status IN ('placed order', 'being processed', 'shipping', 'delivered', 'canceled'))
    149153);
    150154
    … …  
    326330-- Create REFUND table
    327331CREATE TABLE refund (
    328     refund_id SERIAL PRIMARY KEY,
     332    refund_id INTEGER PRIMARY KEY,
    329333    order_num VARCHAR(11) REFERENCES "order"(order_num)
    330334        ON DELETE CASCADE,
    … …  
    335339
    336340
    337 
    338341}}}