DDL.sql

SET ECHO ON

/*DROP TRIGGERS*/

DROP TRIGGER TRG_CUSTOMER;
DROP TRIGGER TRG_PRODUCT;
DROP TRIGGER TRG_ARTICLE;
DROP TRIGGER TRG_ORDERS;
DROP TRIGGER TRG_WISHLIST;
DROP TRIGGER TRG_PREVIOUSLY_USED;


/* DROP SEQUENCES */

DROP SEQUENCE SEQ_CUSTOMER_CUSTOMER_ID;
DROP SEQUENCE SEQ_PRODUCT_PRODUCT_ID;
DROP SEQUENCE SEQ_ARTICLE_ARTICLE_ID;
DROP SEQUENCE SEQ_ORDERS_ORDER_ID;

/* Drop Views */

 DROP VIEW CUSTOMER_VIEW;
 DROP VIEW PRODUCT_VIEW;
 DROP VIEW ARTICLE_VIEW;
 DROP VIEW ORDERS_VIEW;
 DROP VIEW WISHLIST_VIEW;

/*DROP INDICES */

DROP INDEX CST_FName_IDX;
DROP INDEX CST_LName_IDX;
DROP INDEX PRD_PName_IDX;
DROP INDEX PRD_BRAND_IDX;
DROP INDEX ART_AName_IDX;
DROP INDEX ORD_CUST_FK_IDX;
DROP INDEX ORD_PROD_FK_IDX;

/* Drop Tables */

DROP TABLE CUSTOMER CASCADE CONSTRAINTS;
DROP TABLE PRODUCT CASCADE CONSTRAINTS;
DROP TABLE ARTICLE CASCADE CONSTRAINTS;
DROP TABLE ORDERS CASCADE CONSTRAINTS;
DROP TABLE WISHLIST CASCADE CONSTRAINTS;
DROP TABLE ORDER_ITEMS CASCADE CONSTRAINTS;

/* Creating Table 'CUSTOMER' */

CREATE TABLE CUSTOMER (
CUSTOMER_ID INT CONSTRAINT CST_PK PRIMARY KEY,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(100) NOT NULL,
DOB DATE NOT NULL,
CDATE_JOINED DATE NOT NULL,
CDATE_MODIFIED DATE NOT NULL
);

/* Creating Table 'PRODUCT' */

CREATE TABLE PRODUCT (
PRODUCT_ID INT CONSTRAINT PRD_PK PRIMARY KEY,
PRODUCT_NAME VARCHAR(100) NOT NULL,
PRODUCT_TYPE VARCHAR(50) NOT NULL,
BRAND VARCHAR(50) NOT NULL,
VALIDITY DATE NOT NULL,
ARTICLE_ID INT,
PREVIOUS_COUNT INT NOT NULL,
DATE_LAUNCHED DATE NOT NULL,
PDATE_MODIFIED DATE NOT NULL
);

/* Creating Table 'ARTICLE' */

CREATE TABLE ARTICLE (
ARTICLE_ID INT CONSTRAINT ART_PK PRIMARY KEY,
ARTICLE_NAME VARCHAR(100) NOT NULL,
ARTICLE_DESCRIPTION VARCHAR(1000) NOT NULL,
POSTING_DATE DATE NOT NULL,
ADATE_MODIFIED DATE NOT NULL
);

/* Creating Table 'ORDERS' */

CREATE TABLE ORDERS(
ORDER_ID INT CONSTRAINT ORD_PK PRIMARY KEY,
CUSTOMER_ID INT NOT NULL,
ORDER_DATE DATE NOT NULL,
PAYMENT_TYPE VARCHAR(50) NOT NULL,
ORDER_TOTAL INT,
ODATE_MODIFIED DATE NOT NULL
);

/* Creating Table 'ORDERS_PRODUCT' */

CREATE TABLE ORDER_ITEMS(
ORDER_ID INT NOT NULL,
PRODUCT_ID INT NOT NULL,
QUANTITY INT NOT NULL,
DISCOUNT INT NOT NULL,
DELIVERY_MODE VARCHAR(20) NOT NULL,
WEIGHT_LB NUMERIC(10,3) NOT NULL
);

/* Creating Table 'SAVED_PRODUCTS' */

CREATE TABLE WISHLIST(
PRODUCT_ID INT,
CUSTOMER_ID INT,
MODIFIED_DATE DATE NOT NULL,
PREFERENCES INT NOT NULL,
SAVED_DATE DATE NOT NULL
);

/*Indexing*/

-- Indexing For table CUSTOMER:
CREATE INDEX CST_FName_IDX ON CUSTOMER (FIRST_NAME);
CREATE INDEX CST_LName_IDX ON CUSTOMER (LAST_NAME);

-- Indexing For table PRODUCT:
CREATE INDEX PRD_PName_IDX ON PRODUCT (PRODUCT_NAME);
CREATE INDEX PRD_BRAND_IDX ON PRODUCT (BRAND);

--  Indexing For table ARTICLE:
CREATE INDEX ART_AName_IDX ON ARTICLE (ARTICLE_NAME);

-- Indexing For table ORDERS:
CREATE INDEX ORD_CUST_FK_IDX ON ORDERS (CUSTOMER_ID);

-- Indexing For table ORDERS_PRODUCT:
CREATE INDEX OP_PK_IDX ON ORDER_ITEMS (ORDER_ID, PRODUCT_ID);
CREATE INDEX ORD_PROD_FK_IDX ON ORDER_ITEMS (PRODUCT_ID);

-- Indexing For table SAVED_PRODUCTS:
CREATE INDEX SA_PK_IDX ON WISHLIST (PRODUCT_ID, CUSTOMER_ID);


/*CONSTRAINTS*/

PROMPT Creating Foreign Keys on 'ORDERS'

ALTER TABLE ORDERS ADD CONSTRAINT
ORD_CUST_FK FOREIGN KEY
(CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID);

PROMPT Creating Primary Key on 'WISHLIST'

ALTER TABLE WISHLIST
ADD CONSTRAINT WL_PK PRIMARY KEY 
(PRODUCT_ID, CUSTOMER_ID);

PROMPT Creating Foreign Keys on 'WISHLIST'

ALTER TABLE WISHLIST
ADD CONSTRAINT WL_PROD_FK FOREIGN KEY (PRODUCT_ID)
REFERENCES PRODUCT(PRODUCT_ID)
ADD CONSTRAINT WL_CUST_FK FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER(CUSTOMER_ID);

PROMPT Creating Primary Key on 'ORDER_ITEMS'

ALTER TABLE ORDER_ITEMS
ADD CONSTRAINT OI_PK PRIMARY KEY
(ORDER_ID, PRODUCT_ID);

PROMPT Creating Foreign Keys on 'ORDER_ITEMS'

ALTER TABLE ORDER_ITEMS
ADD CONSTRAINT OI_ORD_FK FOREIGN KEY (ORDER_ID)
REFERENCES ORDERS(ORDER_ID)
ADD CONSTRAINT OI_PROD_FK FOREIGN KEY (PRODUCT_ID)
REFERENCES PRODUCT(PRODUCT_ID);

PROMPT Creating Foreign Key on 'PRODUCT'

ALTER TABLE PRODUCT
ADD CONSTRAINT ART_PRD_FK FOREIGN KEY
(ARTICLE_ID) REFERENCES ARTICLE(ARTICLE_ID);


/*VIEWS*/

CREATE VIEW CUSTOMER_VIEW AS
SELECT CUSTOMER_ID, FIRST_NAME, LAST_NAME, ADDRESS, DOB, CDATE_JOINED, CDATE_MODIFIED 
FROM CUSTOMER;

CREATE VIEW PRODUCT_VIEW AS
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE, BRAND, VALIDITY, ARTICLE_ID, PREVIOUS_COUNT, DATE_LAUNCHED, PDATE_MODIFIED
FROM PRODUCT;

CREATE VIEW ARTICLE_VIEW AS
SELECT ARTICLE_ID, ARTICLE_NAME, ARTICLE_DESCRIPTION, POSTING_DATE, ADATE_MODIFIED
FROM ARTICLE;

CREATE VIEW ORDERS_VIEW AS
SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, PAYMENT_TYPE, ORDER_TOTAL, ODATE_MODIFIED 
FROM ORDERS;

CREATE VIEW WISHLIST_VIEW AS
SELECT PRODUCT_ID, CUSTOMER_ID, MODIFIED_DATE, PREFERENCES, SAVED_DATE
FROM WISHLIST;

--CREATING SEQUENCES

--PROMPT CREATE SEQUENCE FOR CUSTOMER
CREATE SEQUENCE SEQ_CUSTOMER_CUSTOMER_ID
    INCREMENT BY 2
    START WITH 202350
    NOMAXVALUE
    MINVALUE 202350
    NOCACHE;
--CREATE SEQUENCE FOR PRODUCT
CREATE SEQUENCE SEQ_PRODUCT_PRODUCT_ID
    INCREMENT BY 1
    START WITH 1000
    NOMAXVALUE
    MINVALUE 1000
    NOCACHE;

--CREATE SEQUENCE FOR ARTICLE
CREATE SEQUENCE SEQ_ARTICLE_ARTICLE_ID
    INCREMENT BY 1
    START WITH 100
    NOMAXVALUE
    MINVALUE 100
    NOCACHE;

--CREATE SEQUENCE FOR ORDERS
CREATE SEQUENCE SEQ_ORDERS_ORDER_ID
    INCREMENT BY 11
    START WITH 1111
    NOMAXVALUE
    MINVALUE 1111
    NOCACHE;

---- Business purpose: The TRG_CUSTOMER trigger automatically assigns a sequential Customer id to a newly-inserted row in the Customer table, as well as setting the join date to the current system date.  If the record is being inserted or updated, appropriate values are assigned to the CDATE_MODIFIED field.
CREATE OR REPLACE TRIGGER TRG_CUSTOMER
BEFORE INSERT OR UPDATE ON CUSTOMER
FOR EACH ROW
BEGIN
        IF INSERTING THEN
            IF :NEW.CUSTOMER_ID IS NULL THEN
                :NEW.CUSTOMER_ID := SEQ_CUSTOMER_CUSTOMER_ID.NEXTVAL;
            END IF;
            IF :NEW.CDATE_JOINED IS NULL THEN
                :NEW.CDATE_JOINED := SYSDATE;
            END IF;
        END IF;
        IF INSERTING OR UPDATING THEN
            :NEW.CDATE_MODIFIED := SYSDATE;
        END IF;
END;
/


-- Business purpose: The TRG_PRODUCT trigger automatically assigns a sequential Product id to a newly-inserted row in the Product table, as well as setting the launched date to the current system date. If the record is being inserted or updated, appropriate values are assigned to the PDATE_MODIFIED field.
CREATE OR REPLACE TRIGGER TRG_PRODUCT
BEFORE INSERT OR UPDATE ON PRODUCT
FOR EACH ROW
BEGIN
        IF INSERTING THEN
            IF :NEW.PRODUCT_ID IS NULL THEN
                :NEW.PRODUCT_ID := SEQ_PRODUCT_PRODUCT_ID.NEXTVAL;
            END IF;
            IF :NEW.DATE_LAUNCHED IS NULL THEN
                :NEW.DATE_LAUNCHED := SYSDATE;
            END IF;
        END IF;
        IF INSERTING OR UPDATING THEN
            :NEW.PDATE_MODIFIED := SYSDATE;
        END IF;
END; 
/


-- Business purpose: The TRG_ARTICLE trigger automatically assigns a sequential Article id to a newly-inserted row in the Article table, as well as setting the article launch date to the current system date.  If the record is being inserted or updated, appropriate values are assigned to the ADATE_MODIFIED field.
CREATE OR REPLACE TRIGGER TRG_ARTICLE
BEFORE INSERT OR UPDATE ON ARTICLE
FOR EACH ROW
BEGIN
        IF INSERTING THEN
            IF :NEW.ARTICLE_ID IS NULL THEN
                :NEW.ARTICLE_ID := SEQ_ARTICLE_ARTICLE_ID.NEXTVAL;
            END IF;
            IF :NEW.POSTING_DATE IS NULL THEN
                :NEW.POSTING_DATE := SYSDATE;
            END IF;
        END IF;
        IF INSERTING OR UPDATING THEN
            :NEW.ADATE_MODIFIED := SYSDATE;
        END IF;
END;
/


-- Business purpose: The TRG_ORDERS trigger automatically assigns a sequential Order id to a newly-inserted row in the Orders table, as well as setting the Order date to the current system date.  If the record is being inserted or updated, appropriate values are assigned to the ADATE_MODIFIED field.
CREATE OR REPLACE TRIGGER TRG_ORDERS
BEFORE INSERT OR UPDATE ON ORDERS
FOR EACH ROW
BEGIN
        IF INSERTING THEN
            IF :NEW.ORDER_ID IS NULL THEN
                :NEW.ORDER_ID := SEQ_ORDERS_ORDER_ID.NEXTVAL;
            END IF;
            IF :NEW.ORDER_DATE IS NULL THEN
                :NEW.ORDER_DATE := SYSDATE;
            END IF;
        END IF;
        IF INSERTING OR UPDATING THEN
            :NEW.ODATE_MODIFIED := SYSDATE;
        END IF;
END; 
/


-- Business purpose: The TRG_WISHLIST trigger sets the Saved date to the current system date. If the record is being inserted or updated, appropriate values are assigned to the MODIFIED_DATE field.
CREATE OR REPLACE TRIGGER TRG_WISHLIST
BEFORE INSERT OR UPDATE ON WISHLIST
FOR EACH ROW
BEGIN
        IF INSERTING THEN
            IF :NEW.SAVED_DATE IS NULL THEN
                :NEW.SAVED_DATE := SYSDATE;
            END IF;
        END IF;
        IF INSERTING OR UPDATING THEN
            :NEW.MODIFIED_DATE := SYSDATE;
        END IF;
END; 
/

-- Business purpose: The TRG_PREVIOUSLY_USED trigger updates the PREVIOUS_COUNT based on records added to orders_id.
CREATE OR REPLACE TRIGGER TRG_PREVIOUSLY_USED
BEFORE INSERT ON ORDER_ITEMS
FOR EACH ROW
BEGIN
        IF INSERTING THEN
            UPDATE PRODUCT
            SET PREVIOUS_COUNT = PREVIOUS_COUNT + :NEW.QUANTITY
            WHERE PRODUCT_ID = :NEW.PRODUCT_ID;
        END IF;
END;
/