Lab03.sql

DROP TABLE CUSTOMER_SERVICE CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;
DROP TABLE SUPPLIER CASCADE CONSTRAINTS;
DROP TABLE PRODUCT CASCADE CONSTRAINTS;
DROP TABLE INVENTORY CASCADE CONSTRAINTS;
DROP TABLE PRODUCTION_BATCH CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER CASCADE CONSTRAINTS;
DROP TABLE ORDERS CASCADE CONSTRAINTS;
DROP TABLE DELIVERER CASCADE CONSTRAINTS;



-- Employee table
CREATE TABLE EMPLOYEE (
   E_Number      INTEGER  PRIMARY KEY,     -- Employee Number as primary key
   E_Fname       VARCHAR2(20) NOT NULL,    -- First name, required
   E_Lname       VARCHAR2(20) NOT NULL,    -- Last name, required
   E_Pnumber     VARCHAR(20),              -- Phone Number
   E_PostalCode  VARCHAR(20),              -- Postal code
   E_Role        VARCHAR(20),              -- Role within the company (e.g., Manager, worker, cashier & etc.)
   E_PayRate NUMBER(5,2) DEFAULT 16.25 CHECK (E_PayRate > 0)  -- Pay rate, default value is $16.25
);

-- Supplier table
CREATE TABLE SUPPLIER (
   SupplierID      INTEGER PRIMARY KEY,    -- Supplier ID as primary key
   SupplierName    VARCHAR2(50) NOT NULL,  -- Supplier Name
   SupplierAddress VARCHAR2(100),          -- Address
   SupplierPhone   VARCHAR2(20),           -- Phone number
   SupplierEmail   VARCHAR(50)             -- Email Address  
);


-- Customer table
CREATE TABLE CUSTOMER (
   CustomerID      INTEGER PRIMARY KEY,     -- Customer ID as primary key
   CustomerName    VARCHAR2(50) NOT NULL,   -- Customer name
   CustomerPhone   VARCHAR2(20),            -- Phone number
   CustomerEmail   VARCHAR2(50),            -- Email address
   CustomerAddress VARCHAR2(100)            -- Customer address
);

CREATE TABLE PRODUCTION_BATCH (

    BatchID INTEGER PRIMARY KEY,    -- Batch ID as primary key
    B_DateProduced DATE NOT NULL,
    B_ExpiryDate DATE NOT NULL     -- Expiry Date
    
);

-- Product table with foreign key to supplier
CREATE TABLE PRODUCT (
   P_SKU          VARCHAR(9) PRIMARY KEY,  -- Product Stock keeping unit as primary key       
   P_name         VARCHAR(50),             -- Product name
   P_cost         NUMBER(7, 2) NOT NULL,   -- Cost price
   P_Price        NUMBER(7, 2) NOT NULL,   -- Selling price
   P_receiveDate  DATE,                    -- Receive date
   P_ExpireDate   DATE,                    -- Expiry date 
   SupplierID     INTEGER,                 -- Foreign key to Supplier
   CONSTRAINT fk_supplier FOREIGN KEY (SupplierID) REFERENCES SUPPLIER(SupplierID) -- Foreign key constraint
);

CREATE TABLE ORDERS (

    OrderDetailID INTEGER PRIMARY KEY,
    P_SKU             VARCHAR2(9),          -- Foreign key to Product
    Quantity          INTEGER NOT NULL,     -- Quantity ordered
    PricePerUnit      NUMBER(7, 2),         -- Price per unit
    CONSTRAINT fk_product FOREIGN KEY (P_SKU) REFERENCES PRODUCT(P_SKU)                -- Foreign key constraint
);

-- Inventory table to track product stock
CREATE TABLE INVENTORY (
   InventoryID       INTEGER PRIMARY KEY,    -- Inventory ID as primary key
   P_SKU             VARCHAR2(9),            -- Foreign key to Product
   QuantityAvailable INTEGER NOT NULL,       -- Quantity of product available
   CONSTRAINT fk_inventory_product FOREIGN KEY (P_SKU) REFERENCES PRODUCT(P_SKU)  -- Foreign key constraint
);


CREATE TABLE DELIVERER(
    DelivererID INTEGER PRIMARY KEY,
    D_Address VARCHAR2(100),
    D_ExpectedDeliveryDate DATE

);

CREATE TABLE CUSTOMER_SERVICE(

    TicketID INTEGER PRIMARY KEY,
    CustomerID INTEGER,
    CustomerRepresentative INTEGER,
    ProductID VARCHAR2(9),
    Customer_Inquiry VARCHAR2(100),
    CONSTRAINT fk_CustomerRepresentative FOREIGN KEY (CustomerRepresentative) References Employee(E_Number),
    CONSTRAINT fk_CustomerID FOREIGN KEY (CustomerID) References Customer(CustomerID),
    CONSTRAINT fk_ProductID FOREIGN KEY (ProductID) References Product(P_SKU)


 );