arnob fakibaz

-- DBMS II - Quiz
-- 1.

CREATE TABLE ROOM
(
    R_ID NUMBER PRIMARY KEY,
    R_FLOOR NUMBER
);

CREATE TABLE DOCTOR
(
    D_ID NUMBER PRIMARY KEY,
    D_NAME VARCHAR2(20),
    D_SPECIALIZATION VARCHAR2(20),
    D_ROOM NUMBER,
    CONSTRAINT D_FK FOREIGN KEY(D_ROOM) REFERENCES ROOM(R_ID)
);

CREATE TABLE PATIENT
(
    P_ID NUMBER PRIMARY KEY,
    P_NAME VARCHAR2(20),
    P_AGE NUMBER,
    P_ADDRESS VARCHAR2(30),
    P_CONTACT VARCHAR2(11),
    P_SICKNESS VARCHAR2(20)
);

CREATE TABLE CONSULTATION
(
    C_ID NUMBER PRIMARY KEY,
    P_ID NUMBER,
    D_ID NUMBER,
    C_DATE DATE,
    CONSTRAINT C_FK1 FOREIGN KEY(P_ID) REFERENCES PATIENT(P_ID),
    CONSTRAINT C_FK2 FOREIGN KEY(D_ID) REFERENCES DOCTOR(D_ID)
);

-- 2.
CREATE TABLE UPDATE_LOG
(
    USERNAME VARCHAR2(20),
    IP_ADDRESS VARCHAR2(20),
    OBJECT_NAME VARCHAR2(20),
    OPERATION VARCHAR2(20),
    UPDATE_TIME DATE,
    OLD_VAL VARCHAR2(20),
    NEW_VAL VARCHAR2(20)
);

-- consider that we'll be tracking all the changes in transactions table.

CREATE OR REPLACE TRIGGER STORE_UPDATE_LOG
AFTER UPDATE ON TRANSACTIONS
FOR EACH ROW
BEGIN
  INSERT INTO UPDATE_LOG VALUES(ORA_DICT_OBJ_OWNER, ORA_CLIENT_IP_ADDRESS, ORA_DICT_OBJ_NAME, ORA_SYSEVENT, SYSDATE, :OLD.AMOUNT, :NEW.AMOUNT);
END;
/

-- 4308 final 17-18
CREATE TABLE SPECIALIZATION
(
    SP_ID NUMBER PRIMARY KEY,
    SUB_NAME VARCHAR2(20),
    SP_NAME VARCHAR2(20)
);

CREATE TABLE PUBLISHER
(
    P_ID NUMBER PRIMARY KEY,
    P_NAME VARCHAR2(20),
    P_COUNTRY VARCHAR2(20),
    REPUTATION VARCHAR2(10),
    CHECK REPUTATION IN('Excellent', 'Good', 'Bad')
);

CREATE TABLE BOOK
(
    ISBN NUMBER PRIMARY KEY,
    B_NAME VARCHAR2(30),
    P_ID NUMBER,
    B_SPECIALIZATION NUMBER,
    PURCHASE_DATE DATE,
    CONSTRAINT BOOK_FK1 FOREIGN KEY(P_ID) REFERENCES PUBLISHER(P_ID),
    CONSTRAINT BOOK_FK2 FOREIGN KEY(B_SPECIALIZATION) REFERENCES SPECIALIZATION(SP_ID)
);

CREATE TABLE BOOK_COPY
(
    C_ID NUMBER PRIMARY KEY,
    ISBN NUMBER,
    AVAILABILITY CHAR(1),
    CONSTRAINT BOOK_COPY_FK FOREIGN KEY(ISBN) REFERENCES BOOK(ISBN)
);

CREATE TABLE BORROWER
(
    BOR_ID NUMBER PRIMARY KEY,
    BOR_NAME VARCHAR2(20)
);

CREATE TABLE ISSUED
(
    I_ID NUMBER PRIMARY KEY,
    COPY_ID NUMBER,
    BOR_ID NUMBER,
    BORROW_DATE DATE,
    RETURN_DATE DATE,
    CONSTRAINT ISSUED_FK1 FOREIGN KEY(COPY_ID) REFERENCES BOOK_COPY(C_ID),
    CONSTRAINT ISSUED_FK2 FOREIGN KEY(BOR_ID) REFERENCES BORROWER(BOR_ID)
);

CREATE TABLE SHARED
(
    S_ID NUMBER PRIMARY KEY,
    MAJ_ID NUMBER,
    U2_ID NUMBER,
    U3_ID NUMBER,
    BOOK_ID NUMBER,
    CONSTRAINT SHARED_FK1 FOREIGN KEY(MAJ_ID) REFERENCES BORROWER(BOR_ID),
    CONSTRAINT SHARED_FK2 FOREIGN KEY(U2_ID) REFERENCES BORROWER(BOR_ID),
    CONSTRAINT SHARED_FK3 FOREIGN KEY(U3_ID) REFERENCES BORROWER(BOR_ID),
    CONSTRAINT SHARED_FK4 FOREIGN KEY(BOOK_ID) REFERENCES BOOK_COPY(C_ID)
);

SELECT ISBN, B_NAME, P_NAME, P_COUNTRY, PURCHASE_DATE
FROM BOOK NATURAL JOIN PUBLISHER;

SELECT ISBN AS BOOK_NO, B_NAME, P_NAME, P_COUNTRY, (SELECT COUNT(*)
            FROM BOOK_COPY
            WHERE ISBN = BOOK_NO) AS TOTAL_COUNT, (SELECT COUNT(*)
            FROM BOOK_COPY WHERE ISBN = BOOK_NO AND AVAILABILITY = 'Y') AS TOTAL_AVAILABLE
FROM BOOK NATURAL JOIN PUBLISHER;

SELECT B_NAME
FROM BOOK NATURAL JOIN BOOK_COPY NATURAL JOIN ISSUED NATURAL JOIN BORROWER
WHERE BOR_ID = 12345
AND AVAILABILITY = 'N'
UNION
SELECT B_NAME
FROM BOOK NATURAL JOIN BOOK_COPY NATURAL JOIN SHARED NATURAL JOIN BORROWER
WHERE (MAJ_ID = 12345 OR U2_ID = 12345 OR U3_ID = 12345)
AND AVAILABILITY = 'N';

SELECT B_NAME
FROM BOOK NATURAL JOIN BOOK_COPY NATURAL JOIN ISSUED NATURAL JOIN BORROWER
WHERE BOR_ID = 12345
AND (SYSDATE - BORROW_DATE) < 30
UNION
SELECT B_NAME
FROM BOOK NATURAL JOIN BOOK_COPY NATURAL JOIN SHARED NATURAL JOIN BORROWER
WHERE (MAJ_ID = 12345 OR U2_ID = 12345 OR U3_ID = 12345)
AND (SYSDATE - BORROW_DATE) < 30;