Sample: Oracle Database Query

Question

The Requirements for a Small Library System Database: There are several branches in a library system, and each branch has a branch number, branch name, location (address), and phone number. The branch number is a unique identifier for each branch. Each library branch owns a large volume of copies of various books, and each copy of a book has a copy number and associated cost. Each book has a book number, title, the number of pages of the books, the name of the publisher, the year of publishing, and list of authors of the book. Each library could own zero to several copies of the same book. The database might store the authors names of each book and other information. Frequently, there are many patrons who borrow copies of books. Each time when a patron borrow a copy of a book, the system automatically generates a transaction identification associated with the borrowing date and due date of the book loan. Each patron has a patron number, patron’s name, patron’s contact information such as postal address, phone number, and email address.

Answer

TABLE CREATION CODE

{`
  1 SQL> CREATE TABLE BRANCH (
  2 BNumber number (5) NOT NULL,
  3 BName varchar2 (20) NOT NULL,
  4 Address1 varchar2 (25) NOT NULL,
  5 Address2 varchar2 (25),
  6 City varchar2 (15) NOT NULL,
  7 State char (2) NOT  NULL,
  8 Zip char (5) NOT NULL,
  9 Phone char (10) NOT  NULL,
  10 PRIMARY KEY (BNumber));
  Table created.
  `}

{`
  1 SQL> CREATE TABLE COPYOFBOOK (
  2 CNumber number (6) NOT NULL,
  3 ISBN varchar2 (13) NOT NULL,
  4 Cost number (5,2) NOT NULL,
  5 Branch number (3) NOT NULL,
  6 PRIMARY KEY (CNumber));
  Table created.
  `}

{`
  1 SQL> CREATE TABLE BOOKLOAN (
  2 LNumber number (10)NOT NULL,
  3 CNumber number (6) NOT NULL,
  4 PNumber number (7) NOT NULL,
  5 BorrowDate date DEFAULT SYSDATE,
  6 Duration number(3) DEFAULT '30' NOT NULL,
  7 Status char(1) DEFAULT 'A' NOT NULL,
  8 PRIMARY KEY (LNumber));
  Table created.
  `}

{`
  1 SQL> CREATE TABLE PATRON (;
  2 PNumber number (7) NOT NULL,
  3 FName varchar2 (15) NOT NULL,
  4 LNAME varchar2 (15) NOT NULL,
  5 Address1 varchar2 (25),
  6 Address2 varchar2 (25),
  7 City varchar2 (15) NOT NULL,
  8 State char (2) NOT NULL,
  9 Zip char (5) NOT NULL,
  10 EMail varchar2 (40),
  11 Phone char (10),
  12 PRIMARY KEY (PNumber));
  Table created.
  `}

{`
  1 SQL> CREATE TABLE BOOK (
  2 ISBN varchar2 (13) NOT NULL,
  3 Title varchar2 (60) NOT NULL,
  4 Publisher varchar2 (35) NOT NULL,
  5 Year number (4) NOT NULL,
  6 Pages number (4) NOT NULL,
  7 PRIMARY KEY (ISBN));
  Table created.
  `}

{`
  1 SQL> CREATE TABLE BOOKAUTHOR (
  2 ANumber number (5) NOT NULL,
  3 FName varchar2 (15) NOT NULL,
  4 Middle char (1),
  5 LName char (15),
  6 PRIMARY KEY (ANumber));
  Table created.
  `}

{`
  1 SQL> CREATE TABLE AUTHORLIST (
  2 ISBN varchar2 (13) NOT NULL,
  3 Author number (5) NOT NULL);
  Table created.
  `}

Constraints

{`
  ALTER TABLE COPYOFBOOK ADD CONSTRAINT FK_Branch
  FOREIGN KEY (Branch) REFERENCES BRANCH(BNumber);

  ALTER TABLE COPYOFBOOK ADD CONSTRAINT FK_ISBN
  FOREIGN KEY (ISBN) REFERENCES BOOK(ISBN);

  ALTER TABLE BOOKLOAN ADD CONSTRAINT FK_CopyofBook
  FOREIGN KEY (CNumber) REFERENCES COPYOFBOOK(CNumber);
  ALTER TABLE BOOKLOAN ADD CONSTRAINT FK_Patron

  FOREIGN KEY (PNumber) REFERENCES PATRON(PNumber);

  ALTER TABLE BOOKLOAN ADD CONSTRAINT ValidStatus
  CHECK (Status IN ('A', 'L', 'P'));

  ALTER TABLE BOOKLOAN ADD CONSTRAINT ValidDuration
  CHECK (Duration <= 90);

  ALTER TABLE AUTHORLIST ADD CONSTRAINT FK_Book
  FOREIGN KEY (ISBN) REFERENCES BOOK(ISBN);

  ALTER TABLE AUTHORLIST ADD CONSTRAINT FK_Author

  FOREIGN KEY (Author) REFERENCES BOOKAUTHOR(ANumber);
  `}

Indexing

{`
  CREATE INDEX IdxBranch ON BRANCH (City);
  CREATE INDEX IdxAuthor ON BOOKAUTHOR(LName);
  CREATE INDEX IdxLoan ON BOOKLOAN (CNumber);
  CREATE INDEX IdxList1 ON AUTHORLIST (ISBN);
  CREATE INDEX IdxList2 ON AUTHORLIST (Author);
  `}

Triggers

{`
  CREATE TRIGGER TrgValidCopy
  BEFORE
  INSERT ON BOOKLOAN
  FOR EACH ROW
  DECLARE
  cnt Number;
  BEGIN
  SELECT count(*) INTO cnt
  FROM BOOKLOAN
  WHERE Status IN ('A','P')
  AND CNumber = :new.CNumber;
  IF cnt <> 0 THEN
  raise_application_error(-20000, 'This copy is already loaned');
  END IF;
  END;
  `}

Procedure

{`
  CREATE PROCEDURE PrcUpdateLoan AS
  BEGIN
  UPDATE BOOKLOAN SET Status = 'P'
  WHERE Status = 'A' AND (BorrowDate + Duration) < SYSDATE;
  END;
  `}

Sequence Creation

{`
  CREATE SEQUENCE SeqBranches START WITH 1 INCREMENT BY 1 NOCACHE
  NOCYCLE;
  CREATE SEQUENCE SeqAuthors START WITH 1 INCREMENT BY 1 NOCACHE
  NOCYCLE;
  CREATE SEQUENCE SeqPatrons START WITH 1 INCREMENT BY 1 NOCACHE
  NOCYCLE;
  CREATE SEQUENCE SeqCopies START WITH 1 INCREMENT BY 1 NOCACHE
  NOCYCLE;
  CREATE SEQUENCE SeqLoans START WITH 1 INCREMENT BY 1 NOCACHE
  NOCYCLE;
  `}