Changes between Initial Version and Version 1 of ddlScript.sql

Timestamp:

12/20/25 22:14:24 (10 days ago)

Author:

235018

Comment:

--

ddlScript.sql

@@ +1 @@
+{{{#!sql
+-- Delete tables if they exist
+DROP TABLE IF EXISTS product CASCADE;
+DROP TABLE IF EXISTS image CASCADE;
+DROP TABLE IF EXISTS colors CASCADE;
+DROP TABLE IF EXISTS store CASCADE;
+DROP TABLE IF EXISTS personal CASCADE;
+DROP TABLE IF EXISTS permissions CASCADE;
+DROP TABLE IF EXISTS boss CASCADE;
+DROP TABLE IF EXISTS employees CASCADE;
+DROP TABLE IF EXISTS client CASCADE;
+DROP TABLE IF EXISTS delivery_address CASCADE;
+DROP TABLE IF EXISTS order CASCADE;
+DROP TABLE IF EXISTS report CASCADE;
+DROP TABLE IF EXISTS monthly_profit CASCADE;
+DROP TABLE IF EXISTS request CASCADE;
+DROP TABLE IF EXISTS makes_request CASCADE;
+DROP TABLE IF EXISTS answers CASCADE;
+DROP TABLE IF EXISTS for_store CASCADE;
+DROP TABLE IF EXISTS review CASCADE;
+DROP TABLE IF EXISTS change CASCADE;
+DROP TABLE IF EXISTS makes_change CASCADE;
+DROP TABLE IF EXISTS made_od CASCADE;
+DROP TABLE IF EXISTS works_in_store CASCADE;
+DROP TABLE IF EXISTS worked CASCADE;
+DROP TABLE IF EXISTS sells CASCADE;
+DROP TABLE IF EXISTS includes CASCADE;
+DROP TABLE IF EXISTS approves CASCADE;
+DROP TABLE IF EXISTS exchange_data CASCADE;
+
+
+-- Table 1
+-- Create PRODUCT table
+CREATE TABLE product (
+    code SERIAL UNIQUE NOT NULL PRIMARY KEY,
+    price NUMBER NOT NULL,
+    availability INTEGER NOT NULL,
+    weight NUMBER NOT NULL,
+    width_X_length_X_depth VARCHAR(20) NOT NULL,
+    aprox_production_time INTEGER NOT NULL,
+    description VARCHAR 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!'
+);
+
+
+-- Table 3
+-- Create COLOR table
+CREATE TABLE color (
+    product_code SERIAL UNIQUE NOT NULL REFERENCES product(code) 
+        ON DELETE CASCADE,
+    color VARCHAR(10)
+);
+
+
+-- Table 4
+-- Create STORE table
+CREATE TABLE store (
+    store_ID SERIAL UNIQUE NOT NULL PRIMARY KEY
+        ON DELETE CASCADE,
+    name VARCHAR(50) UNIQUE NOT NULL,
+    date_of_founding DATE NOT NULL,
+    physical_address VARCHAR(100) NOT NULL,
+    store_email VARCHAR(20) UNIQUE NOT NULL,
+    rating NUMBER NOT NULL DEFAULT '0'
+);
+
+
+-- Table 5
+-- Create PERSONAL table
+CREATE TABLE personal (
+    ssn NUMBER UNIQUE NOT NULL PRIMARY KEY,
+    first_name VARCHAR(20) NOT NULL,
+    last_name VARCHAR(20) NOT NULL,
+    email VARCHAR(20) UNIQUE NOT NULL,
+    password VARCAHR NOT NULL
+);
+
+
+-- Table 6
+-- Create PERMISSIONS table
+CREATE TABLE permissions (
+    personal_SSN NUMBER UNIQUE NOT NULL 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 NUMBER UNIQUE NOT NULL PRIMARY KEY REFERENCES personal(ssn)
+        ON DELETE CASCADE
+);
+
+
+-- Table 8
+-- Create EMPLOYEES table
+CREATE TABLE employees (
+    employee_SSN NUMBER UNIQUE NOT NULL 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 UNIQUE NOT NULL PRIMARY KEY,
+    first_name VARCHAR(50) NOT NULL,
+    last_name VARCHAR(50) NOT NULL,
+    email VARCHAR(20) UNIQUE NOT NULL,
+    password VARCHAR NOT NULL
+);
+
+
+-- Table 10
+-- 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
+);
+
+
+-- Table 11
+-- Create ORDER table
+CREATE TABLE order (
+    order_num SERIAL UNIQUE NOT NULL PRIMARY KEY,
+    client_ID SERIAL UNIQUE NOT NULL 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 VARHAR (250) NOT NULL,
+    discount NUMBER DEFAULT 0.0,
+);
+
+
+-- Table 12
+-- Create REPORT table
+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,
+    sales_trend VARCHAR(max) NOT NULL,
+    marketing_growth VARCHAR(max) NOT NULL,
+    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 REFERENCES report(date)
+        ON DELETE CASCADE,
+    store_ID SERIAL NOT NULL REFERENCES store(store_ID),
+        ON DELETE CASCADE,
+    month_and_year DATE NOT NULL,
+    profit NUMBER NOT NULL DEFAULT 0.0,
+    PRIMARY KEY(report_date, store_ID)
+);
+
+
+-- Table 14
+-- Create REQUEST table
+CREATE TABLE request (
+    
+);
+
+
+
+}}}