Changes between Version 1 and Version 2 of ddlScript.sql

Timestamp:

12/20/25 23:05:36 (10 days ago)

Author:

235018

Comment:

--

ddlScript.sql

@@ -176 +176 @@
 -- Create REQUEST table
 CREATE TABLE request (
-    
-);
-
-
-
-}}}
+    request_num SERIAL UNIQUE NOT NULL PRIMARY KEY,
+    date_and_time TIMESTAMP NOT NULL,
+    problem VARCHAR(1000) NOT NULL,
+    notes_of_communication VARCHAR(300),
+    costumer_satisfaction NUMBER NOT NULL 
+);
+
+
+-- Table 15
+-- 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,
+);
+
+
+-- Table 16
+-- 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,    
+);
+
+
+-- Table 17
+-- 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
+);
+
+
+-- Table 18
+-- Create REVIEW table
+CREATE TABLE review (
+    order_num SERIAL NOT NULL UNIQUE REFERENCES order(order_num)
+        ON DELETE CASCADE,
+    comment VARCHAR(300),
+    rating NUMBER NOT NULL,
+    last_mod_date TIMESTAMP NOT NULL
+);
+
+
+-- Table 19
+-- Create CHANGE table
+CREATE TABLE change (
+    date_and_time TIMESTAMP NOT NULL,
+    changes VARCHAR(max) NOT NULL
+);
+
+
+-- Table 20
+-- 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)
+);
+
+
+-- 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)
+);
+
+
+-- Table 22
+-- 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)
+        ON DELETE CASCADE,
+    PRIMARY KEY(personak_SSN, store_ID)
+);
+
+
+-- Table 23
+-- 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,
+    pay_method VARCHAR(20) DEFAULT 'hourly',
+    total_hours NUMBER NOT NULL,
+    week VARCHAR(24) NOT NULL
+);
+
+
+-- Table 24
+-- 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
+);
+
+
+-- Table 25
+-- 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,
+);
+
+
+-- Table 26
+-- 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
+);
+
+
+-- Table 27
+-- 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,
+    date TIMESTAMP NOT NULL,
+    sales VARCHAR(max) NOT NULL,
+    damages VARCHAR(max)
+);
+
+