Notes

Name: hasan, A. K. M. Abid
ID: 13-24001-2



Create Table Phase:
-----------------------



CREATE TABLE PUBLISHER
(
Name Varchar2(50),
Address Varchar2(50),
Phone NUMBER(15),
CONSTRAINT Pub_nam_pk primary key(Name)
);






Create Table Book
(
Book_ID NUMBER(5),
Title VARCHAR2(20),
PublisherName VARCHAR(15),
Constraint bk_id_pl primary key(Book_ID),
constraint bk_pub_fk foreign key(PublisherName) references PUBLISHER(Name)
);




Create Table BOOK_AUTHORS
(
Book_ID NUMBER(5),
AuthorName VARCHAR(20),
Constraint ba_id_pl primary key(Book_ID, AuthorName),
Constraint ba_id_fk foreign key(Book_ID) references Book(Book_ID)
);




Create Table LIBRARY_BRANCH
(
BranchId NUMBER(15),
BranchName VARCHAR(30),
Address VARCHAr(30),
Constraint lb_id_pk primary key(BranchId)
);



Create Table BORROWER
(
CardNo NUMBER(30),
Name VARCHAR(30),
Address VARCHAR(30),
Phone NUMBER(30),
Constraint br_cn_pk primary key(cardNo)
);

Create table BOOK_COPIES
(
BookId NUMBER(5),
BranchId NUMBER(15),
No_Of_Copies NUMBER(30),
Constraint bc_bid_pk primary key(BookId, BranchId),
Constraint bc_brid_fk foreign key(BranchId) references LIBRARY_BRANCH(BranchId)
);


Create BOOK_LOANS
(
Book_ID NUMBER(5),
BranchId NUMBER(15),
CardNo NUMBER(30),
DateOut Date,
DueDate Date,
Constraint bl_bid_pk primary key( Book_ID, BranchId, CardNo),
Constraint bl_bid_fk foreign key(Book_ID) references Book(Book_ID),
Constraint bl_brid_fk foreign key(BranchId) references LIBRARY_BRANCH(BranchId),
Constraint bl_cn_fk foreign key(CardNo) references BORROWER(CardNo)
);


#Query 1:

SELECT No_Of_Copies
FROM BOOK_COPIES, LIBRARY_BRANCH, Book
WHERE BOOK_COPIES.BranchId=LIBRARY_BRANCH.BranchId and BOOK_COPIES.BranchId=Book.Book_ID and LIBRARY_BRANCH.BranchName='Sharpstown' and Book.Title='The Lost Tribe';


#Query 2:

SELECT No_Of_Copies
FROM ((Book NATURAL JOIN BOOK_COPIES ) NATURAL JOIN LIBRARY_BRANCH )
WHERE Title='The Lost Tribe';



#Query 3:
SELECT Name
FROM BORROWER
WHERE CardNo NOT IN (SELECT CardNo FROM BOOK_LOANS );


#Query 4:

SELECT Title, Name, Address
FROM Book, BORROWER , BOOK_LOANS , LIBRARY_BRANCH
WHERE LB.BranchName='Sharpstown' AND BranchId=BranchId AND
DueDate='today' AND CardNo=R.CardNo AND BL.BookId=B.BookId


#Query 5:



#Query 6:


SELECT Name, Address, COUNT(*)
FROM BORROWER, BOOK_LOANS
WHERE CardNo = CardNo
GROUP BY CardNo, Name, Address
HAVING COUNT(*) > 5;


#Query 7: