database work

System Requirements Specification A local school wants to set up a library for students. You have been asked to develop a database and basic front-end for this system, based on their requirements. The basic Conceptual Database Design of the project has been completed and the UML class diagram for the system is shown below. You will note that the only the primary key attributes are shown. In the case of BookCopy, the primary key cannot be fully resolved fully at this stage of design. Data Requirements are listed 1-4 below. 1. Each book will be identified by an ISBN (International Standard Book Number). Each book also has a title, publisher, edition, price, reader level and author. You can assume that a publisher name is unique, but many authors may have the same name. The system also stores a URL for each publisher. 2. Several copies of any book may be stored in the library. The status of each copy (e.g. available, being repaired, lost, etc.) needs to be recorded. 3. Students may borrow books and each student is assigned a student id. Each student also has a first name, last name, gender and the class to which they are assigned. 4. Every time a book is borrowed the details are logged in the system, including the date the book was borrowed. All books can be borrowed for 2 weeks. The system needs to be able to report on a borrower's history, showing the due date of any outstanding books along with the expected return date. PLEASE document any other assumptions you make about the system in your submission! Sample Queries for the Library database. The database should be capable of supporting the following query transactions: 1. List the books that were never borrowed in an academic year. For each book list the following: a. ISBN b. Title c. Author d. Publisher e. Edition f. Price g. Total number of copies 2. List all overdue books. For each book list the following: a. ISBN b. Title c. Borrower name d. Class e. Date borrowed f. Expected return date g. Number of days overdue ordered the class. 3. List the top ten readers in an academic year. For each student list the following: a. Name b. Class c. Number of books borrowed ordered the number of books borrowed in descending order. Project Deliverables Logical Database Design 1. A screenshot of a detailed E-R diagram that displays the following • Entities • Attributes • Relationships • Multiplicity of each relationship • Primary Keys Note: Many-to-many relationship should be resolved. You can use Visual Paradigm, MySQL modeller or any other modelling tool to produce this. Please ensure all elements of the diagram are clearly visible. Physical Database Design 1. SQL script to create this MySQL database. This should include DDL to create: • Tables • Primary key constraints • Foreign keys constraints • Attribute domain constraints 2. SQL scripts to populate all tables in the database with sample data. At least 3 rows required for all tables. 3. SQL queries for the sample queries 1-3 above. Implementation Develop a basic application in a general-purpose procedural language (Java or Python) that uses embedded SQL to access the library database. All code needs to be well commented. This must support the following minimum functionality. a) Create functionality to select data from at least two tables. b) Create functionality to insert data into at least two tables. c) Create functionality to update data for at least two tables. d) Create functionality to delete data from at least two tables. e) Create a front-end (GUI or command line) to allow the user to use the select, insert, update and delete functionality created above.