Changes between Version 5 and Version 6 of ddlScript.sql

Timestamp:

12/24/25 22:30:32 (6 days ago)

Author:

235018

Comment:

--

ddlScript.sql

@@ -32 +32 @@
 -- Create PRODUCT table
 CREATE TABLE product (
-    code SERIAL PRIMARY KEY,                      -- In format 3 digits for store, 5 digits for product (Total 8 digits). Ex. 00100155, 01500020,...
+    code VARCHAR(8) PRIMARY KEY,                      -- In format 3 digits for store, 5 digits for product (Total 8 digits). Ex. 00100155, 01500020,...
     price NUMERIC NOT NULL CHECK (price >= 0),
     availability INTEGER NOT NULL,
@@ -44 +44 @@
 -- Create IMAGE table
 CREATE TABLE image(
-    product_code VARCHAR PRIMARY KEY REFERENCES product(code)
+    product_code VARCHAR(8) PRIMARY KEY REFERENCES product(code)
         ON DELETE CASCADE,
     image VARCHAR NOT NULL DEFAULT 'Image NOT found!'
@@ -52 +52 @@
 -- Create COLOR table
 CREATE TABLE color (
-    product_code VARCHAR PRIMARY NULL REFERENCES product(code) 
+    product_code VARCHAR(8) PRIMARY NULL REFERENCES product(code) 
         ON DELETE CASCADE,
     color VARCHAR(50)
@@ -60 +60 @@
 -- Create STORE table
 CREATE TABLE store (
-    store_ID SERIAL PRIMARY KEY,             -- In the format of 3 digits. Example : 001, 101, 567, 996,...
+    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,
@@ -105 +105 @@
 -- 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, ...
+    client_ID INTEGER 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,
@@ -123 +123 @@
 -- Create ORDER table
 CREATE TABLE "order" (
-    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
+    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,
@@ -140 +140 @@
         ON DELETE CASCADE,
     overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0),
-    sales_trend VARCHAR NOT NULL,
-    marketing_growth VARCHAR NOT NULL,
-    owner_signature VARCHAR(30) NOT NULL,
+    sales_trend VARCHAR NOT NULL,                                                    -- graph 
+    marketing_growth VARCHAR NOT NULL,                                               -- graph
+    owner_signature VARCHAR(30) NOT NULL,                                            
     PRIMARY KEY (date, store_ID)
 );
@@ -161 +161 @@
 -- Create REQUEST table
 CREATE TABLE request (
-    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 
+    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(1000) NOT NULL,
-    notes_of_communication VARCHAR(300),
+    problem VARCHAR(300) NOT NULL,
+    notes_of_communication VARCHAR,
     costumer_satisfaction NUMERIC NOT NULL 
 );
@@ -181 +181 @@
 -- Create ANSWERS table
 CREATE TABLE answers (
-    request_num VARCHAR(10) REFERENCES request(request_num)
+    request_num VARCHAR(14) REFERENCES request(request_num)
         ON DELETE CASCADE,
     personal_SSN VARCHAR(13) NOT NULL REFERENCES personal(ssn)
@@ -191 +191 @@
 -- Create FOR_STORE table
 CREATE TABLE for_store (
-    request_num VARCHAR(10) REFERENCES request(request_num)
+    request_num VARCHAR(14) REFERENCES request(request_num)
         ON DELETE CASCADE,
     store_ID VARCHAR(3) REFERENCES store(store_ID)
@@ -201 +201 @@
 -- Create REVIEW table
 CREATE TABLE review (
-    order_num VARCHAR(10) REFERENCES "order"(order_num)
+    order_num VARCHAR(11) REFERENCES "order"(order_num)
         ON DELETE CASCADE,               -- Should the review be deleted when the order is deleted?
     comment VARCHAR(300),
@@ -248 +248 @@
     store_ID VARCHAR(3),     
     wage NUMERIC NOT NULL CHECK (wage>=62),
-    pay_method VARCHAR(20) DEFAULT 'hourly',
+    pay_method VARCHAR DEFAULT 'hourly',
     total_hours NUMERIC NOT NULL,
-    week VARCHAR(24) 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)
@@ -270 +270 @@
 -- Create INCLUDES table
 CREATE TABLE includes (
-    order_num VARCHAR(10) REFERENCES "order"(order_num)
+    order_num VARCHAR(11) REFERENCES "order"(order_num)
         ON DELETE CASCADE,
     product_code VARCHAR(8) REFERENCES product(code)
@@ -303 +303 @@
     PRIMARY KEY (report_date, store_ID)
 );
+}}};