Upon discussion with the intern you gathered that the BookID identifies a specific physical volume of book
Question:
Upon discussion with the intern you gathered that the BookID identifies a specific physical volume of book that is housed at a definite branch of SL identified by LibBranchID. A book can be either available or on loan, indicated by the BookStatus value. Whenever a book is issued (loaned) to a member, a new ID is assigned to the transaction related to that book (BookIssueID). For instance, if a member issues borrows five books in one go, five different BookIssueIDs will be generated for those five books. You took upon yourself to explain to the intern the issue at hand, and how the issue should be addressed. Complete the following tasks in that context.
1.Determine the highest normal form that the relation Abnormal_Rel is in. Then: 1. Normalize/decompose it until you get relations that are in 3NF. Use appropriate illustration to aid the understanding of your work. 2. Check if the resultant relations are in BCNF. If not, decompose them as necessary until you get all of them in BCNF.
2 Now you have a set of relation(s) in BCNF.
3 Explain in a short paragraph what non-trivial Functional Dependencies (FDs) are. Identify the non-trivial FDs on the relation Abnormal_Rel. Then identify the Candidate key(s) of Abnormal_Rel.
4 Explain the three forms of Anomalies in three short paragraphs. Determine for each anomaly if the relation Abnormal_Rel is susceptible to that anomaly. Support your determination with a small example.
Abnormal_Rel( BookID, BookTitle, Author, Publisher, PubYear, LibBranchID, BranchSuburb, BookStatus, BookPrice, MemberID, MemberName, MemberAddress, MemberExpDate, BookIssueID, DateBorrowed, DateReturned, ReturnDueDate )
Financial and Managerial Accounting
ISBN: 978-0132497978
3rd Edition
Authors: Horngren, Harrison, Oliver