1) Schema for the Library and its Patrons are given below. You need to use these schemas to solve
the following stated problems:
Patrons (PID, lName, fName, DOB, Sex, city)
LibraryItems (IID, itemName, Category, Value )
Borrows (PID, IID, Fee, BorrowDate, Quantity )
BooksJournals (IID, AuthorName, Publisher, PublishDate)
MediaItems (IID, StarName, Genre, Date, Length, Production)
a) Create a view named patronsBorrow which contains patrons and item information hiding the
patrons DOB and items value.
b) Restrict the domain of library items category to one of Book, Journal, Magazine, DVD,
Audio book, and name sure the domain is always not null.
c) Restrict the domain of library item’s IID Car’s to make sure that it is not one of Patron’s PID.
d) Write an assertion to ensure that a borrower who lives in Houston city borrows more book
journal items than media items.
e) Write a trigger so that after deleting a librayItem, it also deletes associated information from
bookjournal or mediaItems table.
f) Write a trigger so that before deleting a Patron’s information from the Patrons table, it checks
whether s/he has borrowed any items from the library. If yes, then don’t remove the patron
else remove entries from both patrons and borrows table.
2) Consider the schema R = (A, C, D, E, H) and the following set of functional dependencies
F={A C, AC D, E AD, E H}. Show your detailed work.
a) Which of the functional dependencies of F are nontrivial or completely nontrivial?
b) Apply Armstrong axioms to check whether AC ED and A CH can logically be implied
or not?
c) Use attribute closure to check whether A CH and E DH holds or not?
d) Check whether A is a candidate key? If not then check whether AE is a candidate key?
3) Given a relation R = {A, B, C, H, I} and suppose we decompose it to R1=(A, B, C) and R2=(A,
H, I). We have the following functional dependency of R F = { B H, CH I, A BC, I
A, B C }. Show your detailed work.
a) Check whether the decomposition is lossless join decomposition or not.
b) Check whether the decomposition is a dependency-preserving decomposition or not.
c) Give a loss-less join, dependency-preserving decomposition into BCNF or 3NF for the
schema R.
0 comments