LAB 8

 
CREATE TABLE AUTHOR 
    ( 
     SSN INTEGER  NOT NULL , 
     Book_ISBN INTEGER  NOT NULL , 
     DOB DATE 
    ) 
    LOGGING 
;
 
 
 
ALTER TABLE AUTHOR 
    ADD CONSTRAINT AUTHOR_PK PRIMARY KEY ( SSN ) ;
 
 
 
CREATE TABLE BOOK 
    ( 
     ISBN INTEGER  NOT NULL , 
     Title VARCHAR2 (30 CHAR) , 
     Category VARCHAR2 (30 CHAR) , 
     Threshold INTEGER , 
     QUANTITY INTEGER , 
     Price INTEGER 
    ) 
    LOGGING 
;
 
 
 
ALTER TABLE BOOK 
    ADD CONSTRAINT BOOK_PK PRIMARY KEY ( ISBN ) ;
 
 
 
CREATE TABLE ORDERS 
    ( 
     ID INTEGER  NOT NULL , 
     Book_ISBN INTEGER  NOT NULL , 
     QUANTITY INTEGER 
    ) 
    LOGGING 
;
 
 
 
ALTER TABLE ORDERS 
    ADD CONSTRAINT ORDERS_PK PRIMARY KEY ( ID ) ;
 
 
 
CREATE TABLE Published_By 
    ( 
     ISBN INTEGER  NOT NULL , 
     Name VARCHAR2 (40 CHAR)  NOT NULL , 
     Year DATE 
    ) 
    LOGGING 
;
 
 
 
ALTER TABLE Published_By 
    ADD CONSTRAINT Published_By_PK PRIMARY KEY ( ISBN, Name ) ;
 
 
 
CREATE TABLE Publisher 
    ( 
     Name VARCHAR2 (40 CHAR)  NOT NULL , 
     Phone_Num VARCHAR2 (10 CHAR) , 
     Address VARCHAR2 (40 CHAR) 
    ) 
    LOGGING 
;
 
 
 
ALTER TABLE Publisher 
    ADD CONSTRAINT Publisher_PK PRIMARY KEY ( Name ) ;
 
 
 
 
ALTER TABLE AUTHOR 
    ADD CONSTRAINT AUTHOR_BOOK_FK FOREIGN KEY 
    ( 
     Book_ISBN
    ) 
    REFERENCES BOOK 
    ( 
     ISBN
    ) 
    NOT DEFERRABLE 
;
 
 
ALTER TABLE ORDERS 
    ADD CONSTRAINT ORDERS_BOOK_FK FOREIGN KEY 
    ( 
     Book_ISBN
    ) 
    REFERENCES BOOK 
    ( 
     ISBN
    ) 
    ON DELETE CASCADE 
    NOT DEFERRABLE 
;
 
 
ALTER TABLE Published_By 
    ADD CONSTRAINT TABLE_6_BOOK_FK FOREIGN KEY 
    ( 
     ISBN
    ) 
    REFERENCES BOOK 
    ( 
     ISBN
    ) 
    ON DELETE CASCADE 
    NOT DEFERRABLE 
;
 
 
ALTER TABLE Published_By 
    ADD CONSTRAINT TABLE_6_Publisher_FK FOREIGN KEY 
    ( 
     Name
    ) 
    REFERENCES Publisher 
    ( 
     Name
    ) 
    NOT DEFERRABLE 
;
 
CREATE SEQUENCE ORDER_SEQ 
    START WITH 1 
    INCREMENT BY 1 
    MAXVALUE 100 
    MINVALUE 1 
    CACHE 20 
;
 
CREATE OR REPLACE TRIGGER INC_QUANTITY 
    BEFORE DELETE ON ORDERS 
    FOR EACH ROW 
BEGIN
UPDATE BOOK SET BOOK.QUANTITY=BOOK.QUANTITY+:OLD.QUANTITY WHERE ISBN=:OLD.ISBN;
END; 
/
 
 
CREATE OR REPLACE TRIGGER Quantity_Check 
    BEFORE UPDATE OF QUANTITY ON BOOK 
    FOR EACH ROW 
BEGIN
IF (:NEW.QUANTITY < 0 ) THEN
raise_application_error (-20999,'QUANTITY CANT BE NEGATIVE');
END IF;
END; 
/
 
ALTER TRIGGER Quantity_Check ENABLE 
 
 
CREATE OR REPLACE TRIGGER THRESHOLD_CHECK 
    AFTER UPDATE OF QUANTITY ON BOOK 
    FOR EACH ROW 
BEGIN
IF (:NEW.QUANTITY <:NEW.THRESHOLD) THEN
INSERT INTO ORDERS VALUES (ORDER_SEQ.NEXTVAL,:NEW.THRESHOLD-:NEW.QUANTITY);
END IF;
END; 
/
 
ALTER TRIGGER THRESHOLD_CHECK ENABLE