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

Timestamp:

12/28/25 01:04:22 (3 days ago)

Author:

235018

Comment:

--

ddlScript-with-help-of-AI.sql

@@ +1 @@
+{{{#!sql
+-- Delete tables if they exist
+DROP TABLE IF EXISTS product;
+DROP TABLE IF EXISTS image;
+DROP TABLE IF EXISTS color;
+DROP TABLE IF EXISTS store;
+DROP TABLE IF EXISTS personal;
+DROP TABLE IF EXISTS permissions;
+DROP TABLE IF EXISTS boss;
+DROP TABLE IF EXISTS employees;
+DROP TABLE IF EXISTS client;
+DROP TABLE IF EXISTS delivery_address;
+DROP TABLE IF EXISTS "order";
+DROP TABLE IF EXISTS report;
+DROP TABLE IF EXISTS monthly_profit;
+DROP TABLE IF EXISTS request;
+DROP TABLE IF EXISTS makes_request;
+DROP TABLE IF EXISTS answers;
+DROP TABLE IF EXISTS for_store;
+DROP TABLE IF EXISTS review;
+DROP TABLE IF EXISTS "change";
+DROP TABLE IF EXISTS makes_change;
+--  DROP TABLE IF EXISTS made_on CASCADE;
+DROP TABLE IF EXISTS works_in_store;
+DROP TABLE IF EXISTS worked;
+DROP TABLE IF EXISTS sells;
+DROP TABLE IF EXISTS includes;
+DROP TABLE IF EXISTS approves;
+DROP TABLE IF EXISTS exchanges_data;
+
+
+-- Table 0
+-- Create CATEGORY table
+CREATE TABLE category (
+    id SERIAL PRIMARY KEY,
+    name VARCHAR(50) NOT NULL,
+    parent_category_id INTEGER REFERENCES category(id)
+);
+
+-- Table 1
+-- Create PRODUCT table
+CREATE TABLE product (
+    code VARCHAR(8) PRIMARY KEY DEFAULT -1,                      -- In format 3 digits for store, 5 digits for product (Total 8 digits). Ex. 00100155, 01500020,...
+    price DECIMAL(10,2) NOT NULL CHECK (price >= 0.0),
+    availability INTEGER NOT NULL,
+    weight DECIMAL(5,2) NOT NULL CHECK (weight > 0),    -- Shown in kg
+    width_x_length_x_depth VARCHAR(20) NOT NULL, 
+    aprox_production_time INTEGER NOT NULL,
+    description VARCHAR(500) NOT NULL,
+    cathegory_id INTEGER NOT NULL REFERENCES category(id)
+        ON DELETE SET DEFAULT
+);
+
+-- Table 2
+-- Create IMAGE table
+CREATE TABLE image(
+    product_code VARCHAR(8) 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(8) REFERENCES product(code) 
+        ON DELETE CASCADE,
+    color VARCHAR(50)
+);
+
+-- Table 4
+-- Create STORE table
+CREATE TABLE store (
+    store_ID VARCHAR(3) 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(40) UNIQUE NOT NULL CHECK (store_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
+    rating DECIMAL(2,1) NOT NULL DEFAULT 0 CHECK (rating>=0.0 AND rating<=5.0) 
+);
+
+-- Table 5
+-- Create PERSONAL table
+CREATE TABLE personal (
+    ssn VARCHAR(13) PRIMARY KEY CHECK (ssn ~ '^[0-9]{13}$'),                                     -- Format 13 digits :   0101001451001, 1209995423736,...
+    first_name VARCHAR(20) NOT NULL,
+    last_name VARCHAR(20) NOT NULL,
+    email VARCHAR(50) UNIQUE NOT NULL     CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
+    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 CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
+    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(200) NOT NULL, -- ADDRESS FORMAT : st.(st-name) num.(unit-num)
+    city VARCHAR(30) NOT NULL,
+    postcode VARCHAR(20) NOT NULL,
+    country VARCHAR(40) NOT NULL,
+    is_default BOOLEAN DEFAULT True
+);
+
+-- Table 11
+-- Create ORDER table
+CREATE TABLE "order" (
+    order_num VARCHAR(11) PRIMARY KEY,                   -- In the format: 3 digits for store ID, 3 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,                  -- Format : "credit card, **** **** **** 6750" , or "cash", or "paypal, user@gmail.com",... Contains important information that are going to be needed when issuing a refund 
+    discount DECIMAL(5,2) DEFAULT 0.0 CHECK(discount>=0.0 AND discount<= 100.00),
+    CONSTRAINT check_status (status IN ('placed order', 'being processed', 'shipping', 'delivered', 'canceled'))
+);
+
+-- Should there be also a table for refund???
+
+-- 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,                                                    -- graph 
+    marketing_growth VARCHAR NOT NULL,                                               -- graph
+    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 VARCHAR(14) PRIMARY KEY,  -- Format: 3 digits for store ID, 2 digits for month in which the request is placed, 3 digits for year, 4 digits for client, 2 digits for request number 
+    date_and_time TIMESTAMP NOT NULL,
+    problem VARCHAR(300) NOT NULL,
+    notes_of_communication VARCHAR,
+    customer_satisfaction NUMERIC NOT NULL  -- Fixed from "costumer" to "customer"
+);
+
+-- 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(11) 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(14) 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(14) 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(11) PRIMARY KEY 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 DEFAULT 'hourly',
+    total_hours NUMERIC NOT NULL,
+    week VARCHAR(23) NOT NULL,                       -- Format : 15.12.2025 - 21.12.2025
+    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(11) 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 EXCHANGES_DATA table
+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 sales (not "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)
+);
+
+-- Table 28
+-- Create REFUND table
+CREATE TABLE refund (
+    refund_id SERIAL PRIMARY KEY,
+    order_num VARCHAR(11) REFERENCES "order"(order_num)
+        ON DELETE CASCADE,
+    amount DECIMAL(10,2) NOT NULL CHECK(amount > 0.0),
+    reason VARCHAR(200),
+    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'processed'))
+);
+
+
+
+}}}