library project – logical and physical database design

Faculty of Science & Information Technology School of Design, Communication and Information Technology Callaghan INFT2040 – Database Management Systems Assignment 2: Library Project – Logical and Physical Database Design Due: 7pm, Tuesday, Nov. 25, 2014 WORTH 15% of final assessment mark In this assignment, steps in the logical and physical database design are conducted (as described below), and a report is written (as specified below). Revise requirements and EER diagram in Assignment 1, based on: Either the EER Model submitted for your Assignment 1. Revise it based on feedback provided by your marker, or Alternatively, complete the partial EER model given at the end of this file, to meet the systems requirements. Map the EER model to the relational model. Document the relational schema in DBDL (Sample format is given below) ISBN (id, number, itemNo) Primary Key id Alternate Key number Foreign Key itemNo references Book(itemNo) ON UPDATE CASCADE, ON DELETE CASCADE Normalize the schema to Boyce-Codd Normal Form (if any relation is not already in BCNF). The final normalised schema must be documented in DBDL. Write an SQL script which creates the normalised library database. Write SQL statements satisfying your transaction requirements including: Input proper data for at least three rows for every table, and Implement the queries you proposed in Step 1 (i.e., in revised Assignment 1), and Implement the following queries (if they are not the exact query implemented in step b): c.1 List the details of a particular member c.2 List privileges of a particular member c.3 Four searches on an item, based on call number, keyword, author, and title, separately c.4 List present loans by a particular member c.5 List frequently loaned items for each semester c.6 Give fines report containing information about fines levied and members fined c.7 Give report of items overdue and members who have overdue items c.8 List current hold…