DBMS Day 8

#CONCEPTS & SOME OTHER SQL STATEMENTS

#Creating CUSTOMER table

CREATE TABLE CUSTOMER (
    customerid INT,
    customername VARCHAR(20) NOT NULL,
    caddress VARCHAR(30),
    ccity CHAR(15),
    cstate CHAR(2),
    czip CHAR(6) NOT NULL,
    PRIMARY KEY (customerid),
    UNIQUE (customername)
);

#Creating ORDERS table with Primary Key and Foreign Key

CREATE TABLE ORDERS (
    orderno INT,
    orderdate DATE,
    customerid INT,
    CONSTRAINT PKORDERS
        PRIMARY KEY (orderno),
    CONSTRAINT FKORDERS
        FOREIGN KEY (customerid)
        REFERENCES CUSTOMER(customerid)
);

The PRIMARY KEY uniquely identifies each order, while the FOREIGN KEY establishes a relationship between ORDERS and CUSTOMER.

The foreign key can be defined with ON DELETE CASCADE.

CREATE TABLE ORDERS (
    orderno INT,
    orderdate DATE,
    customerid INT,
    CONSTRAINT PKORDERS
        PRIMARY KEY (orderno),
    CONSTRAINT FKORDERS
        FOREIGN KEY (customerid)
        REFERENCES CUSTOMER(customerid)
        ON DELETE CASCADE
);

When a customer row is deleted, the corresponding rows in ORDERS are also deleted.

The foreign key can also be defined using ON DELETE SET NULL.

CREATE TABLE ORDERS (
    orderno INT,
    orderdate DATE,
    customerid INT,
    CONSTRAINT PKORDERS
        PRIMARY KEY (orderno),
    CONSTRAINT FKORDERS
        FOREIGN KEY (customerid)
        REFERENCES CUSTOMER(customerid)
        ON DELETE SET NULL
);

When a referenced customer row is deleted, the corresponding customerid in ORDERS is set to NULL.

A view is a virtual table derived from one or more base tables. The view definition is stored and the data is obtained when the view is used.

#Example 1: FINISH_QTY

CREATE VIEW FINISH_QTY(PRODFINISH, TOTAL_QTY)
AS
SELECT PRODUCTFINISH, SUM(QTYONHAND)
FROM PRODUCT
GROUP BY PRODUCTFINISH;

To display the view:

SELECT *
FROM FINISH_QTY;

#Example 2: ORDERING_CUSTOMER

CREATE VIEW ORDERING_CUSTOMER(CUSTOMERID, CUSTOMERNAME)
AS
SELECT CUSTOMERID, CUSTOMERNAME
FROM CUSTOMER
WHERE CUSTOMERID IN (
    SELECT CUSTOMERID
    FROM ORDERS
);

To retrieve data using the view:

SELECT CUSTOMERID, CUSTOMERNAME, CADDRESS
FROM CUSTOMER
WHERE CUSTOMERID IN (
    SELECT CUSTOMERID
    FROM ORDERING_CUSTOMER
);

#Example 3: CUST_PROD

CREATE VIEW CUST_PROD(CUSTNAME, PRODNAME, QTYORD)
AS
SELECT DISTINCT C.CUSTOMERNAME,
                P.PRODUCTDESC,
                R.QUANTITY
FROM PRODUCT P, REQUESTS R, ORDERS O, CUSTOMER C
WHERE P.PRODUCTNO = R.PRODUCTNO
  AND R.ORDERNO = O.ORDERNO
  AND O.CUSTOMERID = C.CUSTOMERID;

A view can subsequently be used in queries as if it were a regular table.

#Dropping a View

DROP VIEW ORDERING_CUSTOMER;

An assertion can be used to specify a constraint involving multiple tables.

CREATE ASSERTION CHECK_QTY
CHECK (
    NOT EXISTS (
        SELECT *
        FROM PRODUCT P, REQUESTS R
        WHERE P.PRODUCTNO = R.PRODUCTNO
          AND QTYONHAND < QUANTITY
    )
);

The assertion ensures that the database state satisfies the specified condition.

A trigger is an event-driven action that is automatically executed when a specified event occurs.

Example:

CREATE TRIGGER QTY_VIOLATION
BEFORE INSERT OR UPDATE OF QUANTITY
ON ORDERS
FOR EACH ROW
WHEN (
    NEW.QUANTITY > (
        SELECT QTYONHAND
        FROM PRODUCT
        WHERE PRODUCTNO = NEW.PRODUCTNO
    )
)
DO_PROCEDURE(NEW.PRODUCTNO);

The trigger checks for a quantity violation before an insertion or update takes place.