Changes between Version 3 and Version 4 of ddlScript-with-help-of-AI.sql

Timestamp:

12/30/25 12:24:58 (16 hours ago)

Author:

235018

Comment:

--

ddlScript-with-help-of-AI.sql

@@ -1 +1 @@
 {{{#!sql
--- Delete tables if they exist 
+-- Delete tables if they exist (in reverse dependency order)
 DROP TABLE IF EXISTS category;
 DROP TABLE IF EXISTS store;
@@ -71 +71 @@
     product_code VARCHAR(8) REFERENCES product(code)
         ON DELETE CASCADE,
-    image VARCHAR NOT NULL DEFAULT 'Image NOT found!'
+    image VARCHAR NOT NULL DEFAULT 'Image NOT found!',
+    PRIMARY KEY(product_code, image)
 );
 
@@ -79 +80 @@
     product_code VARCHAR(8) REFERENCES product(code) 
         ON DELETE CASCADE,
-    color VARCHAR(50)
+    color VARCHAR(50),
+    PRIMARY KEY(product_code, color)
 );
 
@@ -86 +88 @@
 -- Create PERSONAL table
 CREATE TABLE personal (
-    ssn VARCHAR(13) PRIMARY KEY,                                     -- Format 13 digits :   0101001451001, 1209995423736,...
+    id CHAR(7) PRIMARY KEY,                                     -- Format 13 digits :   0101001451001, 1209995423736,...
     first_name VARCHAR(20) NOT NULL,
     last_name VARCHAR(20) NOT NULL,
@@ -96 +98 @@
 -- Create PERMISSIONS table
 CREATE TABLE permissions (
-    personal_SSN VARCHAR(13) PRIMARY KEY REFERENCES personal(ssn)
+    personal_ID CHAR(7) REFERENCES personal(id)
         ON DELETE CASCADE,
     type VARCHAR(100) UNIQUE NOT NULL,
-    authorisation VARCHAR(50) NOT NULL
+    authorisation VARCHAR(50) NOT NULL,
+    PRIMARY KEY(personal_ID, type)
 );
 
@@ -105 +108 @@
 -- Create BOSS table
 CREATE TABLE boss (
-    boss_SSN VARCHAR(13) PRIMARY KEY REFERENCES personal(ssn)
+    boss_ID CHAR(7) PRIMARY KEY REFERENCES personal(id)
         ON DELETE CASCADE
 );
@@ -112 +115 @@
 -- Create EMPLOYEES table
 CREATE TABLE employees (
-    employee_SSN VARCHAR(13) PRIMARY KEY REFERENCES personal(ssn)
+    employee_ID CHAR(7) PRIMARY KEY REFERENCES personal(id)
         ON DELETE CASCADE,
     date_of_hire DATE NOT NULL
@@ -153 +156 @@
 );
 
--- Should there be also a table for refund???
 
 -- Table 12
@@ -205 +207 @@
     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)
+    personal_ID CHAR(7) NOT NULL REFERENCES personal(id)
+        ON DELETE CASCADE,
+    PRIMARY KEY(request_num, personal_ID)
 );
 
@@ -243 +245 @@
 -- Create MAKES_CHANGE table
 CREATE TABLE makes_change (
-    personal_SSN VARCHAR(13) REFERENCES personal(ssn)
+    personal_ID CHAR(7) REFERENCES personal(id)
         ON DELETE CASCADE,
     change_date_time TIMESTAMP,
     product_code VARCHAR(8),
-    PRIMARY KEY(personal_SSN, change_date_time, product_code),
+    PRIMARY KEY(personal_ID, change_date_time, product_code),
     FOREIGN KEY (change_date_time, product_code) REFERENCES "change"(date_and_time, product_code)
         ON DELETE CASCADE
@@ -255 +257 @@
 -- Create WORKS_IN_STORE table
 CREATE TABLE works_in_store (
-    personal_SSN VARCHAR(13) REFERENCES personal(ssn)
+    personal_ID VARCHAR(5) REFERENCES personal(id)
         ON DELETE CASCADE,
     store_ID VARCHAR(3) REFERENCES store(store_ID)
         ON DELETE CASCADE,
-    PRIMARY KEY(personal_SSN, store_ID)
+    PRIMARY KEY(personal_ID, store_ID)
 );
 
@@ -265 +267 @@
 -- Create WORKED table
 CREATE TABLE worked (
-    personal_SSN VARCHAR(13) REFERENCES personal(ssn)
+    personal_ID CHAR(7) REFERENCES personal(id)
         ON DELETE CASCADE,                   
     report_date TIMESTAMP,
@@ -273 +275 @@
     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),
+    PRIMARY KEY (personal_ID, report_date, store_ID),
     FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
         ON DELETE CASCADE
@@ -302 +304 @@
 -- Create APPROVES table
 CREATE TABLE approves (
-    boss_SSN VARCHAR(13) REFERENCES boss(boss_SSN)
+    boss_ID CHAR(7) REFERENCES boss(boss_ID)
         ON DELETE CASCADE,
     report_date TIMESTAMP,
     store_ID VARCHAR(3),
     owner_signature VARCHAR NOT NULL, 
-    PRIMARY KEY (boss_SSN, report_date, store_ID),
+    PRIMARY KEY (boss_ID, report_date, store_ID),
     FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
         ON DELETE CASCADE
@@ -338 +340 @@
 );
 
-
 }}}