SIT772 Database Design Report and Implementation

Suppose you are working in Alpha Softwares as a senior database designer and you received following conceptual schema from database designer.

BOOK(Book-ID, Title, Authors, Volume, ISBN, Publisher) primary key = (Book-ID) USER(ID, First-Name, Last-Name, Email) Primary key = (ID) BORROW(BOOK-ID, ISBN, First-Name, Last-Name, Borrow-Date, Volume, Year) primary key = (Book-ID, FirstName, Last-Name) Fines(ID, Amount, BOOK-ID)

The aim of new database is to track record of books in Deakin Library borrowed by Deakin students and staff. Students and staff are described by ID, last name, first name, phone, email, A book is described by a book-id (uniquely identifies each copy of book). Before T3 commence, Deakin Library buys large number of identical text books for each unit i.e. 60 books for SIT772. A book can be further be described by book name, ISSN, Volume, title, and publisher. The system records the date of barrow.

Before, approving the database design, you noticed that junior designer has made some mistakes in conceptual and logical modeling of database.

Being Senior Designer, your task is to analyze the database to reduce the inconsistencies in the design. Your role is to use the analysis of functional dependencies and normalization to find the highest normal form valid for each one of the relational schemas listed above.

(1) List all the users order by fines. (2) List all the books order by publisher and publication year.

Note: Please, keep in mind the above design is incorrect, you are required to update the above database design. For each schema listed above, first find the functional dependencies valid in a relational schema followed by the minimal keys. In next step, find the highest normal form valid for a relational schema, and finally if a normal form found is not BCNF decompose a schema into BCNF.

Marking Guide:

5 Marks - Identification of problems in conceptual schema 10 Marks Illustration of normalization process explaining with motivations 5 Marks Final proposed conceptual schema 5 Marks Proper testing by dummy data insertion