Changes between Version 2 and Version 3 of ddlScript.sql

Timestamp:

12/21/25 00:22:44 (10 days ago)

Author:

235018

Comment:

--

ddlScript.sql

@@ -33 +33 @@
 -- Create PRODUCT table
 CREATE TABLE product (
-    code SERIAL UNIQUE NOT NULL PRIMARY KEY,
-    price NUMBER NOT NULL,
+    code SERIAL PRIMARY KEY,
+    price NUMERIC NOT NULL CHECK (price >= 0),
     availability INTEGER NOT NULL,
-    weight NUMBER NOT NULL,
-    width_X_length_X_depth VARCHAR(20) NOT NULL,
+    weight NUMERIC NOT NULL CHECK (weight > 0),
+    width_x_length_x_depth VARCHAR(20) NOT NULL,
     aprox_production_time INTEGER NOT NULL,
-    description VARCHAR NOT NULL
-);
-
+    description VARCHAR(1000) NOT NULL
+);
 
 -- Table 2
 -- Create IMAGE table
 CREATE TABLE image(
-    product_code SERIAL UNIQUE NOT NULL REFERENCES product(code)
-        ON DELETE CASCADE,
-    image VARCHAR NOT NULL DEFAULT 'Image NOT found!'
+    product_code INTEGER PRIMARY KEY REFERENCES product(code)
+        ON DELETE CASCADE,
+    image VARCHAR(max) NOT NULL DEFAULT 'Image NOT found!'
 );
 
@@ -55 +54 @@
 -- Create COLOR table
 CREATE TABLE color (
-    product_code SERIAL UNIQUE NOT NULL REFERENCES product(code) 
-        ON DELETE CASCADE,
-    color VARCHAR(10)
+    product_code INTEGER PRIMARY KEY NULL REFERENCES product(code) 
+        ON DELETE CASCADE,
+    color VARCHAR(20)
 );
 
@@ -64 +63 @@
 -- Create STORE table
 CREATE TABLE store (
-    store_ID SERIAL UNIQUE NOT NULL PRIMARY KEY
+    store_ID SERIAL PRIMARY KEY
         ON DELETE CASCADE,
     name VARCHAR(50) UNIQUE NOT NULL,
     date_of_founding DATE NOT NULL,
-    physical_address VARCHAR(100) NOT NULL,
+    physical_address VARCHAR(100) NOT NULL,   -- ADDRESS FORMAT : st.(st-name) num.(unit-num), (city) (post-code), (country)?????
     store_email VARCHAR(20) UNIQUE NOT NULL,
-    rating NUMBER NOT NULL DEFAULT '0'
+    rating NUMERIC NOT NULL DEFAULT 0.0 CHECK (rating>=0.0) 
 );
 
@@ -77 +76 @@
 -- Create PERSONAL table
 CREATE TABLE personal (
-    ssn NUMBER UNIQUE NOT NULL PRIMARY KEY,
+    ssn VARCHAR(13) UNIQUE NOT NULL PRIMARY KEY,
     first_name VARCHAR(20) NOT NULL,
     last_name VARCHAR(20) NOT NULL,
-    email VARCHAR(20) UNIQUE NOT NULL,
+    email VARCHAR(50) UNIQUE NOT NULL,
     password VARCAHR NOT NULL
 );
@@ -88 +87 @@
 -- Create PERMISSIONS table
 CREATE TABLE permissions (
-    personal_SSN NUMBER UNIQUE NOT NULL PRIMARY KEY REFERENCES personal(ssn)
+    personal_SSN VARCHAR(13) PRIMARY KEY REFERENCES personal(ssn)
         ON DELETE CASCADE,
     type VARCHAR(100) UNIQUE NOT NULL,
-    authorisation VARCHAR (50) NOT NULL
+    authorisation VARCHAR(50) NOT NULL
 );
 
@@ -98 +97 @@
 -- Create BOSS table
 CREATE TABLE boss (
-    boss_SSN NUMBER UNIQUE NOT NULL PRIMARY KEY REFERENCES personal(ssn)
+    boss_SSN VARCHAR(13) PRIMARY KEY REFERENCES personal(ssn)
         ON DELETE CASCADE
 );
@@ -106 +105 @@
 -- Create EMPLOYEES table
 CREATE TABLE employees (
-    employee_SSN NUMBER UNIQUE NOT NULL PRIMARY KEY REFERENCES personal(ssn)
+    employee_SSN VARCHAR(13) PRIMARY KEY REFERENCES personal(ssn)
         ON DELETE CASCADE,
     date_of_hire DATE NOT NULL
@@ -115 +114 @@
 -- Create CLIENT table
 CREATE TABLE client (
-    client_ID SERIAL UNIQUE NOT NULL PRIMARY KEY,
+    client_ID SERIAL PRIMARY KEY,
     first_name VARCHAR(50) NOT NULL,
     last_name VARCHAR(50) NOT NULL,
-    email VARCHAR(20) UNIQUE NOT NULL,
+    email VARCHAR(50) UNIQUE NOT NULL,
     password VARCHAR NOT NULL
 );
@@ -126 +125 @@
 -- Create DELIVERY_ADDRESS table
 CREATE TABLE delivery_address (
-    client_ID SERIAL UNIQUE NOT NULL PRIMARY KEY REFERENCES client(client_ID)
-        ON DELETE CASCADE,
-    address VARCHAR NOT NULL
+    client_ID INTEGER PRIMARY KEY REFERENCES client(client_ID)
+        ON DELETE CASCADE,
+    address VARCHAR(100) NOT NULL -- ADDRESS FORMAT : st.(st-name) num.(unit-num), (city) (post-code), (country)?????
 );
 
@@ -135 +134 @@
 -- Create ORDER table
 CREATE TABLE order (
-    order_num SERIAL UNIQUE NOT NULL PRIMARY KEY,
-    client_ID SERIAL UNIQUE NOT NULL REFERENCES client(client_ID),
+    order_num SERIAL PRIMARY KEY,
+    client_ID INTEGER REFERENCES client(client_ID),
         ON DELETE CASCADE,
     quantity INTEGER NOT NULL,
@@ -142 +141 @@
     last_date_mod TIMESTAMP NOT NULL,
     payment_method VARHAR (250) NOT NULL,
-    discount NUMBER DEFAULT 0.0,
+    discount NUMERIC DEFAULT 0.0,
 );
 
@@ -150 +149 @@
 CREATE TABLE report (
     date TIMESTAMP NOT NULL,
-    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
-        ON DELETE CASCADE,
-    overall_profit NUMBER NOT NULL DEFAULT 0.0,
+    store_ID INTEGER NOT NULL REFERENCES store(store_ID)
+        ON DELETE CASCADE,
+    overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0,0),
     sales_trend VARCHAR(max) NOT NULL,
     marketing_growth VARCHAR(max) NOT NULL,
@@ -163 +162 @@
 -- Create MONTHLY_PROFIT table
 CREATE TABLE monthly_profit (
-    report_date TIMESTAMP NOT NULL REFERENCES report(date)
-        ON DELETE CASCADE,
-    store_ID SERIAL NOT NULL REFERENCES store(store_ID),
-        ON DELETE CASCADE,
+    report_date TIMESTAMP NOT NULL,
+    store_ID INTEGER NOT NULL,
     month_and_year DATE NOT NULL,
     profit NUMBER NOT NULL DEFAULT 0.0,
-    PRIMARY KEY(report_date, store_ID)
+    PRIMARY KEY(report_date, store_ID),
+    FOREIGN KEY (report_date, store_id) REFERENCES report(date, store_ID) 
+        ON DELETE CASCADE
 );
 
@@ -176 +175 @@
 -- Create REQUEST table
 CREATE TABLE request (
-    request_num SERIAL UNIQUE NOT NULL PRIMARY KEY,
+    request_num SERIAL PRIMARY KEY,
     date_and_time TIMESTAMP NOT NULL,
     problem VARCHAR(1000) NOT NULL,
     notes_of_communication VARCHAR(300),
-    costumer_satisfaction NUMBER NOT NULL 
+    costumer_satisfaction NUMERIC NOT NULL 
 );
 
@@ -187 +186 @@
 -- Create MAKES_REQUEST table
 CREATE TABLE makes_request (
-    client_ID SERIAL NOT NULL REFERENCES client(client_ID)
-        ON DELETE CASCADE,
-    order_num SERIAL UNIQUE NOT NULL REFERENCES order(order_num)
-        ON DELETE CASCADE,
+    client_ID INTEGER NOT NULL REFERENCES client(client_ID)
+        ON DELETE CASCADE,
+    order_num INTEGER UNIQUE NOT NULL REFERENCES order(order_num)
+        ON DELETE CASCADE,
+    PRIMARY KEY(client_ID, order_num)
 );
 
@@ -197 +197 @@
 -- Create ANSWERS table
 CREATE TABLE answers (
-    request_num SERIAL NOT NULL REFERENCES request(request_num)
-        ON DELETE CASCADE,
-    personal_SSN VARCHAR NOT NULL REFERENCES personal(ssn)
-        ON DELETE CASCADE,    
+    request_num INTEGER 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)
 );
 
@@ -207 +208 @@
 -- Create FOR_STORE table
 CREATE TABLE for_store (
-    request_num SERIAL NOT NULL REFERENCES request(request_num)
-        ON DELETE CASCADE,
-    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
-        ON DELETE CASCADE
+    request_num INTEGER REFERENCES request(request_num)
+        ON DELETE CASCADE,
+    store_ID SERIAL REFERENCES store(store_ID)
+        ON DELETE CASCADE,
+    PRIMARY KEY(request_num, store_ID)
 );
 
@@ -217 +219 @@
 -- Create REVIEW table
 CREATE TABLE review (
-    order_num SERIAL NOT NULL UNIQUE REFERENCES order(order_num)
-        ON DELETE CASCADE,
+    order_num INTEGER REFERENCES order(order_num)
+        ON DELETE CASCADE,               -- SHould the review be deleted when the order is deleted?
     comment VARCHAR(300),
     rating NUMBER NOT NULL,
@@ -229 +231 @@
 CREATE TABLE change (
     date_and_time TIMESTAMP NOT NULL,
-    changes VARCHAR(max) NOT NULL
+    product_code INTEGER REFERENCES product(code)
+        ON DELETE CASCADE,
+    changes VARCHAR(max) NOT NULL,
+    PRIMARY KEY (date_and_time, product_code)
 );
 
@@ -236 +241 @@
 -- Create MAKES_CHANGE table
 CREATE TABLE makes_change (
-    personal_SSN NUMBER NOT NULL REFERENCES personal(ssn)
-        ON DELETE CASCADE,
-    change_date_time TIMESTAMP NOT NULL,
-    PRIMARY KEY(personal_SSN, change_date_time)
+    personal_SSN NUMERIC REFERENCES personal(ssn)
+        ON DELETE CASCADE,
+    change_date_time TIMESTAMP,
+    product_code INTEGER,
+    PRIMARY KEY(personal_SSN, change_date_time, product_code),
+    FOREIGN KEY (change_date_time, product_code) REFERENCES change(change_date_time, product)
+        ON DELETE CASCADE
 );
 
 
 -- Table 21
--- Create MADE_ON table
-CREATE TABLE made_on (
-    product_code SERIAL NOT NULL REFERENCES product(code)
-        ON DELETE CASCADE,
-    change_date_time TIMESTAMP NOT NULL,
-    PRIMARY KEY(product_SSN, change_date_time)
-);
+-- Create MADE_ON table                   -- Is it needed?
+--CREATE TABLE made_on (
+--    product_code INTEGER,
+--    change_date_time TIMESTAMP,
+--    PRIMARY KEY(product_SSN, change_date_time)
+--    FOREIGN KEY(product_SSN, change_date_time) REFERENCES change(product_SSN, change_date_time)
+--        ON DELETE CASCADE
+--);
 
 
@@ -256 +265 @@
 -- Create WORKS_IN_STORE table
 CREATE TABLE works_in_store (
-    personal_SSN NUMBER NOT NULL REFERENCES personal(ssn)
-        ON DELETE CASCADE,
-    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
+    personal_SSN VARCHAR(13) REFERENCES personal(ssn)
+        ON DELETE CASCADE,
+    store_ID INTEGER REFERENCES store(store_ID)
         ON DELETE CASCADE,
     PRIMARY KEY(personak_SSN, store_ID)
@@ -267 +276 @@
 -- Create WORKED table
 CREATE TABLE worked (
-    personal_SSN NUMBER NOT NULL REFERENCES personal(ssn)
-        ON DELETE CASCADE,
-    report_date TIMESTAMP NOT NULL REFERENCES report(date)
-        ON DELETE CASCADE,
-    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
-        ON DELETE CASCADE,
-    wage NUMBER NOT NULL,
+    personal_SSN VARCHAR(13) REFERENCES personal(ssn)
+        ON DELETE CASCADE,                   
+    report_date TIMESTAMP,
+    store_ID SERIAL,     
+    wage NUMERIC NOT NULL CHECK (wage>=62),   -- Anyone should be paid a minimum of 63MKD/hour
     pay_method VARCHAR(20) DEFAULT 'hourly',
-    total_hours NUMBER NOT NULL,
-    week VARCHAR(24) NOT NULL
+    total_hours NUMERIC NOT NULL,
+    week VARCHAR(24) NOT NULL,
+    PRIMARY KEY (personal_SSN, report_date, store_ID),
+    FOREIGN KEY (report_date, store_ID) REFERENCES report(report_Date, store_ID)
+        ON DELETE CASCADE
 );
 
@@ -283 +293 @@
 -- Create SELLS table
 CREATE TABLE sells (
-    product_code SERIAL NOT NULL REFERENCES product(code)
-        ON DELETE CASCADE,
-    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
-        ON DELETE CASCADE,
-    discount NUMBER NOT NULL DEFAULT 0.0
+    product_code INTEGER REFERENCES product(code)
+        ON DELETE CASCADE,
+    store_ID SERIAL REFERENCES store(store_ID)
+        ON DELETE CASCADE,      -- Should the data be kept if store is deleted?
+    discount NUMERIC NOT NULL DEFAULT 0.0,
+    PRIMARY KEY (product_code, store_ID)
 );
 
@@ -294 +305 @@
 -- Create INCLUDES table
 CREATE TABLE includes (
-    order_num SERIAL NOT NULL REFERENCES order(order_num)
-        ON DELETE CASCADE,
-    product_code SERIAL NOT NULL REFERENCES product(code)
-        ON DELETE CASCADE,
+    order_num INTEGER REFERENCES order(order_num)
+        ON DELETE CASCADE,
+    product_code INTEGER REFERENCES product(code)
+        ON DELETE CASCADE,
+    PRIMARY KEY (order_num, product_code)
 );
 
@@ -304 +316 @@
 -- Create APPROVES table
 CREATE TABLE approves (
-    boss_SSN NUMBER NOT NULL REFERENCES boss(boss_SSN)
-        ON DELETE CASCADE,
-    report_date TIMESTAMP NOT NULL REFERENCES report(date)
-        ON DELETE CASCADE,
-    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
-        ON DELETE CASCADE,
-    owner_signature VARCHAR(30) NOT NULL
+    boss_SSN NUMERIC REFERENCES boss(boss_SSN)
+        ON DELETE CASCADE,
+    report_date TIMESTAMP,
+    store_ID INTEGER,
+    owner_signature VARCHAR(30) NOT NULL,  -- Is it needed?
+    PRIMARY KEY (boss_SSN, report_date, store_ID),
+    FOREIGN KEY (report_date, store_ID) REFERENCES report(report_date, store_ID)
+        ON DELETE CASCADE
 );
 
@@ -317 +330 @@
 -- Create EXCHANGES_DATA table
 CREATE TABLE exchanges_date (
-    report_date TIMESTAMP NOT NULL references report(report_date)
-        ON DELETE CASCADE,
-    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
-        ON DELETE CASCADE,
-    monthly_profit NUMBER NOT NULL DEFAULT 0.0,
+    report_date TIMESTAMP,    -- Should the data be kept if the report is deleted?
+    store_ID INTEGER REFERENCES store(store_ID)
+        ON DELETE CASCADE,
+    monthly_profit NUMERIC NOT NULL DEFAULT 0.0,
     date TIMESTAMP NOT NULL,
     sales VARCHAR(max) NOT NULL,
-    damages VARCHAR(max)
-);
-
-
+    damages VARCHAR(max),
+    PRIMARY KEY (report_date, store_ID),
+    FOREIGN KEY (report_date, store_ID) REFERENCES report(report_date, store_ID)
+        ON DELETE CASCADE
+);
+