script.sql

-- ============================================
-- LMS Database Initialization Script
-- 007 PRODUCTION FIX: Creates tables and schema
-- User drop/recreate is handled by Java setup
-- ============================================

-- This script runs as PRJ2531H after the user has been
-- dropped and recreated by the Java setup wizard (as SYSTEM).
-- It ONLY creates tables, sequences, and indexes.

--LOGIN MODULE

-- CREDENTIAL TABLE WHERE THE LOGIN CREDENTIALS OF THE USERS ARE STORED. THIS TABLE IS USED FOR AUTHENTICATION PURPOSES.

CREATE TABLE TBL_CREDENTIALS (
    USER_ID   CHAR(5) CONSTRAINT LOGIN_PK PRIMARY KEY, -- USER ID FOR LOGIN
    NAME      VARCHAR2(50) NOT NULL, -- NAME OF THE USER
    PSWD	VARCHAR2(255) NOT NULL, -- HASHED PASSWORD
    EMAIL     VARCHAR2(80) NOT NULL,    -- EMAIL OF THE USER
    PHNO      NUMBER(10) NOT NULL, -- PHONE NUMBER OF THE USER
    ROLE      VARCHAR2(15) DEFAULT 'LIBRARIAN' NOT NULL, -- ADMIN / LIBRARIAN
    STATUS    VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL,
    CONSTRAINT CHK_USER_ROLE CHECK (ROLE IN ('ADMIN', 'LIBRARIAN')),
    CONSTRAINT CHK_USER_STATUS CHECK (STATUS IN ('ACTIVE', 'INACTIVE'))
);

CREATE TABLE TBL_ID_COUNTER (
    COUNTER_KEY VARCHAR2(50) CONSTRAINT ID_COUNTER_PK PRIMARY KEY,
    NEXT_VAL    NUMBER(10) NOT NULL
);

INSERT INTO TBL_ID_COUNTER (COUNTER_KEY, NEXT_VAL) VALUES ('USER_ID', 0);

CREATE TABLE TBL_AUDIT_LOG (
    LOG_ID              NUMBER CONSTRAINT AUDIT_PK PRIMARY KEY,
    USER_ID             VARCHAR2(20) NOT NULL,
    MODULE              VARCHAR2(30) NOT NULL,
    ACTION_DESCRIPTION  VARCHAR2(400) NOT NULL,
    LOG_TS              TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE SEQUENCE SEQ_AUDIT_LOG START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER TRG_AUDIT_LOG
BEFORE INSERT ON TBL_AUDIT_LOG
FOR EACH ROW
BEGIN
    IF :NEW.LOG_ID IS NULL THEN
        SELECT SEQ_AUDIT_LOG.NEXTVAL INTO :NEW.LOG_ID FROM DUAL;
    END IF;
END;
/

CREATE INDEX IDX_AUDIT_USER ON TBL_AUDIT_LOG(USER_ID);
CREATE INDEX IDX_AUDIT_MODULE ON TBL_AUDIT_LOG(MODULE);
CREATE INDEX IDX_AUDIT_TS ON TBL_AUDIT_LOG(LOG_TS);

--BOOK MODULE

-- BOOK INFORMATION TABLE WHERE THE DETAILS OF THE BOOKS ARE STORED. THIS TABLE IS USED TO STORE THE DETAILS OF THE BOOKS AVAILABLE IN THE LIBRARY.

-- BOOK CATALOG TABLE: ONE ROW PER (AUTHOR + TITLE + EDITION). STOCK COUNTS SHOULD REFERENCE THIS TABLE.
CREATE TABLE TBL_BOOK_CATALOG (
    AUTHOR_NAME VARCHAR2(50)  NOT NULL, -- AUTHOR NAME OF THE BOOK
    BK_TITLE    VARCHAR2(100) NOT NULL, -- TITLE OF THE BOOK
    EDITION     NUMBER(2)     NOT NULL, -- EDITION OF THE BOOK
    CONSTRAINT BOOK_CAT_PK PRIMARY KEY (AUTHOR_NAME, BK_TITLE, EDITION)
);

CREATE TABLE TBL_BOOK_INFORMATION (
    ACCESS_NO   CHAR(6) CONSTRAINT BK_INFO_PK PRIMARY KEY, -- ACCESSION NUMBER OF THE BOOK (6-digit numeric)
    AUTHOR_NAME VARCHAR2(50) NOT NULL, -- AUTHOR NAME OF THE BOOK
    BK_TITLE    VARCHAR2(100) NOT NULL, -- TITLE OF THE BOOK
    VOLUME      NUMBER(2), -- VOLUME OF THE BOOK
    EDITION	NUMBER(2) NOT NULL, -- EDITION OF THE BOOK
    PUBLISHER	VARCHAR2(40) NOT NULL, -- PUBLISHER OF THE BOOK
    PUB_PLACE   VARCHAR2(40) NOT NULL, -- PLACE OF PUBLICATION
    PUB_YEAR    NUMBER(4) NOT NULL, -- PUBLICATION YEAR OF THE BOOK
    PAGES       NUMBER(5) NOT NULL, -- NUMBER OF PAGES IN THE BOOK
    SOURCE      VARCHAR2(20) NOT NULL, -- SOURCE OF THE BOOK (PURCHASED/DONATED)
    CLASS_NO	CHAR(6), -- CLASSIFICATION NUMBER OF THE BOOK
    BOOK_NO     VARCHAR2(10) NOT NULL, -- CALL NO (AUTO: FIRST 3 LETTERS OF AUTHOR)
    U_PRICE     NUMBER(6,2) NOT NULL, -- UNIT PRICE OF THE BOOK
    B_NO        VARCHAR2(20), -- BILL NUMBER OF THE BOOK
    B_DATE      DATE, -- BILL DATE OF THE BOOK
    TAGS        VARCHAR2(500), -- SEARCHABLE TAGS (COMMAS SEPARATED)
    WITHDRAWN	DATE, -- WITHDRAWN DATE OF THE BOOK
    REMARKS     VARCHAR2(50), -- REMARKS ABOUT THE BOOK
    STATUS      VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL, -- ACTIVE / WITHDRAWN / LOST / DAMAGED
    CIRC_STATUS VARCHAR2(10) DEFAULT 'AVAILABLE' NOT NULL, -- AVAILABLE / ISSUED (for circulation)
    CONSTRAINT CHK_BOOK_STATUS CHECK (STATUS IN ('ACTIVE', 'WITHDRAWN', 'LOST', 'DAMAGED')),
    CONSTRAINT CHK_BOOK_CIRC_STATUS CHECK (CIRC_STATUS IN ('AVAILABLE', 'ISSUED')),
    CONSTRAINT FK_BOOKINFO_CATALOG FOREIGN KEY (AUTHOR_NAME, BK_TITLE, EDITION)
        REFERENCES TBL_BOOK_CATALOG (AUTHOR_NAME, BK_TITLE, EDITION)
);

CREATE INDEX IDX_BOOKINFO_TITLE ON TBL_BOOK_INFORMATION(BK_TITLE);
CREATE INDEX IDX_BOOKINFO_AUTHOR ON TBL_BOOK_INFORMATION(AUTHOR_NAME);
CREATE INDEX IDX_BOOKINFO_BILL ON TBL_BOOK_INFORMATION(B_NO);
CREATE INDEX IDX_BOOKINFO_SOURCE ON TBL_BOOK_INFORMATION(SOURCE);
CREATE INDEX IDX_BOOKINFO_YEAR ON TBL_BOOK_INFORMATION(PUB_YEAR);

-- BOOK STOCK TABLE WHERE THE DETAILS OF THE BOOK STOCK ARE STORED. THIS TABLE IS USED TO STORE THE DETAILS OF THE BOOK STOCK AVAILABLE IN THE LIBRARY.

CREATE TABLE TBL_BOOK_STOCK (
    AUTHOR_NAME VARCHAR2(50)  NOT NULL, -- AUTHOR NAME OF THE BOOK
    BK_TITLE    VARCHAR2(100) NOT NULL, -- TITLE OF THE BOOK
    EDITION     NUMBER(2)     NOT NULL, -- EDITION OF THE BOOK
    P_LOCATION  VARCHAR2(50) NOT NULL, -- PHYSICAL LOCATION OF THE BOOK IN THE LIBRARY
    QUANTITY    NUMBER(3) NOT NULL, -- QUANTITY OF THE BOOK IN THE STOCK
    AVAIL_QTY	NUMBER(3) NOT NULL, -- AVAILABLE QUANTITY OF THE BOOK IN THE STOCK
    CONSTRAINT BOOK_STOCK_PK PRIMARY KEY (AUTHOR_NAME, BK_TITLE, EDITION, P_LOCATION),
    CONSTRAINT FK_STOCK_CATALOG FOREIGN KEY (AUTHOR_NAME, BK_TITLE, EDITION)
        REFERENCES TBL_BOOK_CATALOG (AUTHOR_NAME, BK_TITLE, EDITION)
);

CREATE TABLE TBL_BOOK_ALERT_LOG (
    ALERT_ID       NUMBER PRIMARY KEY,
    ALERT_TS       DATE DEFAULT SYSDATE NOT NULL,
    ALERT_TYPE     VARCHAR2(20) NOT NULL, -- LOW_STOCK
    BK_TITLE       VARCHAR2(100) NOT NULL,
    AUTHOR_NAME    VARCHAR2(50) NOT NULL,
    EDITION        NUMBER(2) NOT NULL,
    CURRENT_QTY    NUMBER(5) NOT NULL,
    THRESHOLD_QTY  NUMBER(5) NOT NULL,
    ALERTED_TO     VARCHAR2(50) NOT NULL
);

CREATE SEQUENCE SEQ_BOOK_ALERT_LOG START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER TRG_BOOK_ALERT_LOG
BEFORE INSERT ON TBL_BOOK_ALERT_LOG
FOR EACH ROW
BEGIN
    IF :NEW.ALERT_ID IS NULL THEN
        SELECT SEQ_BOOK_ALERT_LOG.NEXTVAL INTO :NEW.ALERT_ID FROM DUAL;
    END IF;
END;
/


--STUDENT MODULE

-- STUDENT TABLE WHERE THE DETAILS OF THE STUDENTS ARE STORED. THIS TABLE IS USED TO STORE THE DETAILS OF THE STUDENTS WHO ARE MEMBERS OF THE LIBRARY.

CREATE TABLE TBL_STUDENT (
    CARD_ID   CHAR(9) CONSTRAINT STUD_PK PRIMARY KEY, -- LIBRARY CARD ID OF THE STUDENT (PPU-YYxxx)
    ROLL      NUMBER(5) NOT NULL, -- ROLL NUMBER OF THE STUDENT
    NAME      VARCHAR2(50) NOT NULL, -- NAME OF THE STUDENT
    PH_NO     NUMBER(10) NOT NULL, -- PHONE NUMBER OF THE STUDENT
    ADDR      VARCHAR2(100) NOT NULL, -- ADDRESS OF THE STUDENT
    COURSE    CHAR(3) NOT NULL, -- COURSE OF THE STUDENT
    ACAD_SESSION   CHAR(9) NOT NULL, -- ACADEMIC SESSION OF THE STUDENT
    RECEIPT_NO VARCHAR2(10) NOT NULL UNIQUE, -- RECEIPT NUMBER OF THE STUDENT (e.g., LR-26002)
    ISSUED_BY VARCHAR2(20) NOT NULL, -- NAME OF THE LIBRARIAN WHO ISSUED THE LIBRARY CARD TO THE STUDENT
    ISSUE_DATE DATE DEFAULT SYSDATE NOT NULL, -- ISSUE DATE OF THE LIBRARY CARD
    BOOK_LIMIT NUMBER(1) DEFAULT 2 NOT NULL, -- MAXIMUM NUMBER OF BOOKS THAT THE STUDENT CAN ISSUE
    FEE  NUMBER(5,2) NOT NULL, -- FEE OF THE STUDENT
    STATUS VARCHAR2(9) DEFAULT 'ACTIVE' NOT NULL, -- STATUS OF THE STUDENT (ACTIVE/INACTIVE)
    CONSTRAINT UK_STUD_ROLL_COURSE_SESS UNIQUE (ROLL, COURSE, ACAD_SESSION),
    CONSTRAINT CHK_STUD_BOOK_LIMIT CHECK (BOOK_LIMIT IN (1,2)),
    CONSTRAINT CHK_STUD_STATUS CHECK (STATUS IN ('ACTIVE','INACTIVE')),
    CONSTRAINT CHK_STUD_ROLL_5DIG CHECK (ROLL BETWEEN 10000 AND 99999),
    CONSTRAINT CHK_STUD_PHNO_10DIG CHECK (PH_NO BETWEEN 1000000000 AND 9999999999)
);


--TRANSACTIONS MODULE

-- SELLER TABLE WHERE THE DETAILS OF THE SELLERS ARE STORED. THIS TABLE IS USED TO STORE THE DETAILS OF THE SELLERS WHO SUPPLY BOOKS TO THE LIBRARY.

CREATE TABLE TBL_SELLER (
    S_ID                CHAR(10) CONSTRAINT SEL_PK PRIMARY KEY, -- SIDxxxxxxx
    COMPANY_NAME        VARCHAR2(60) NOT NULL,
    COMPANY_CONTACT_NO  VARCHAR2(10) NOT NULL,
    COMPANY_MAIL        VARCHAR2(80) NOT NULL,
    CONTACT_PERSON      VARCHAR2(60) NOT NULL,
    CONTACT_PERSON_NO   VARCHAR2(10) NOT NULL,
    CONTACT_PERSON_MAIL VARCHAR2(80) NOT NULL,
    ADDR                VARCHAR2(150) NOT NULL
);

CREATE TABLE TBL_ORDER_HEADER (
    ORDER_ID   CHAR(10) CONSTRAINT ORDER_HDR_PK PRIMARY KEY, -- ORD-YYxxxx
    S_ID       CHAR(10) NOT NULL,
    ORDER_DATE DATE DEFAULT SYSDATE NOT NULL,
    CONSTRAINT FK_ORDER_HDR_SELLER FOREIGN KEY (S_ID) REFERENCES TBL_SELLER(S_ID)
);

CREATE TABLE TBL_ORDER_DETAILS (
    ORDER_ID      CHAR(10) NOT NULL,
    BOOK_TITLE    VARCHAR2(120) NOT NULL,
    AUTHOR        VARCHAR2(100) NOT NULL,
    PUBLICATION   VARCHAR2(100) NOT NULL,
    QUANTITY      NUMBER(5) NOT NULL,
    CONSTRAINT ORDER_DTL_PK PRIMARY KEY (ORDER_ID, BOOK_TITLE, AUTHOR, PUBLICATION),
    CONSTRAINT FK_ORDER_DTL_HEADER FOREIGN KEY (ORDER_ID) REFERENCES TBL_ORDER_HEADER(ORDER_ID) ON DELETE CASCADE
);

-- BILL TABLE WHERE THE DETAILS OF THE BILLS ARE STORED. THIS TABLE IS USED TO STORE THE DETAILS OF THE BILLS GENERATED FOR THE BOOKS PURCHASED FROM THE SELLERS.

CREATE TABLE TBL_BILL (
    B_ID      CHAR(10), -- BILL ID OF THE BOOK
    S_ID      CHAR(10) NOT NULL, -- SELLER ID OF THE BOOK
    TTL       VARCHAR2(100) NOT NULL, -- TITLE OF THE BOOK
    AUTHOR    VARCHAR2(100) NOT NULL, -- AUTHOR OF THE BOOK
    QUANTITY  NUMBER(5) NOT NULL, -- QUANTITY OF THE BOOK
    U_PRICE   NUMBER(5,2) NOT NULL, -- UNIT PRICE OF THE BOOK
    B_DATE    DATE DEFAULT SYSDATE, -- BILL DATE OF THE BOOK
    TAX       NUMBER(5) NOT NULL, -- TAX ON THE BOOK
    TTL_AMUT  NUMBER(10) NOT NULL, -- TOTAL AMOUNT OF THE BILL
    GR_TTL    NUMBER(10) NOT NULL, -- GRAND TOTAL OF THE BILL
    CONSTRAINT BILL_PK PRIMARY KEY (B_ID, TTL, AUTHOR), -- PRIMARY KEY CONSTRAINT
    CONSTRAINT FK_BILL_SELLER FOREIGN KEY (S_ID) REFERENCES TBL_SELLER(S_ID) -- FOREIGN KEY CONSTRAINT
);



--CIRCULATION MODULE

-- ISSUE/RETURN TABLE FOR CIRCULATION TRANSACTIONS.

CREATE TABLE TBL_ISSUE (
    ISSUE_ID      CHAR(10) CONSTRAINT ISSUE_PK PRIMARY KEY, -- LIDYYxxxxx
    BORROWER_TYPE VARCHAR2(10) DEFAULT 'STUDENT' NOT NULL, -- STUDENT / FACULTY
    CARD_ID       CHAR(9), -- STUDENT CARD ID
    FACULTY_NAME  VARCHAR2(80), -- FACULTY BORROWER NAME
    FACULTY_CONTACT VARCHAR2(15), -- FACULTY CONTACT NO
    ACCESSION_NO  CHAR(6) NOT NULL, -- BOOK ACCESSION NO (Matches TBL_BOOK_INFORMATION.ACCESS_NO)
    ISSUE_DATE    DATE DEFAULT TRUNC(SYSDATE) NOT NULL, -- DATE OF ISSUE
    DUE_DATE      DATE NOT NULL, -- ISSUE_DATE + 7 DAYS
    RETURN_DATE   DATE, -- DATE OF RETURN
    RETURN_CONDITION VARCHAR2(10), -- GOOD / DAMAGED / LOST
    FINE          NUMBER(8,2) DEFAULT 0 NOT NULL, -- RETURN FINE
    ISSUED_BY     VARCHAR2(20) NOT NULL, -- LIBRARIAN USER ID
    STATUS        VARCHAR2(10) DEFAULT 'ISSUED' NOT NULL, -- ISSUED / RETURNED
    CONSTRAINT CHK_ISSUE_BORROWER_TYPE CHECK (BORROWER_TYPE IN ('STUDENT','FACULTY')),
    CONSTRAINT CHK_ISSUE_STATUS CHECK (STATUS IN ('ISSUED','RETURNED')),
    CONSTRAINT CHK_ISSUE_RETURN_CONDITION CHECK (RETURN_CONDITION IS NULL OR RETURN_CONDITION IN ('GOOD','DAMAGED','LOST')),
    CONSTRAINT CHK_ISSUE_BORROWER CHECK (
        (BORROWER_TYPE = 'STUDENT' AND CARD_ID IS NOT NULL AND FACULTY_NAME IS NULL AND FACULTY_CONTACT IS NULL) OR
        (BORROWER_TYPE = 'FACULTY' AND CARD_ID IS NULL AND FACULTY_NAME IS NOT NULL AND FACULTY_CONTACT IS NOT NULL)
    ),
    CONSTRAINT FK_ISSUE_STUDENT FOREIGN KEY (CARD_ID) REFERENCES TBL_STUDENT(CARD_ID),
    CONSTRAINT FK_ISSUE_BOOK FOREIGN KEY (ACCESSION_NO) REFERENCES TBL_BOOK_INFORMATION(ACCESS_NO)
);

CREATE INDEX IDX_ISSUE_CARD_STATUS ON TBL_ISSUE(CARD_ID, STATUS);
CREATE INDEX IDX_ISSUE_BORROWER_TYPE ON TBL_ISSUE(BORROWER_TYPE, STATUS);
CREATE INDEX IDX_ISSUE_FACULTY_NAME ON TBL_ISSUE(FACULTY_NAME, STATUS);
CREATE INDEX IDX_ISSUE_ACC_STATUS ON TBL_ISSUE(ACCESSION_NO, STATUS);

COMMIT;