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
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.
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.
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.
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.
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.
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.
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;
