Can anybody help me with QUESTION 5 of this DATABASE ASSIGNMENT !!!!
CMP 420758 Database Systems Assignment 1 (submit answers as hard copy) Due by March 27, 2018 Department of Computer Science, Lehman College lo n conuider nhe olowing data reguirermrntoa iandbase for keping rack of litrary members' borrowing books from a few library branches The data requirements of the library application are summarized as follows BOOK entity is identified by Bookld, it has title, publisher name, and multiple author names. nity consists of Name, Adress, and Phone attributes. Name is the key for the PUBLISHER. Note: Phone number is unique. LIBRARY_BRANCH entity has Branchld as a key and other attributes such as Library Banch Name, Branch Address, Branch Phone Number. Note: Phone number is unique. BORROWER (or Library Member) entity has MemberCID as key and additionally has name, address, phone attributes. Each LIBRARY_BRANCH has one or more copies of the same book. In such a case, noOfCopies attribute needs to be maintained by the relationship. A book is published by only one publisher. A book can be loaned to a library member at a specific library branch. When a library member checks out a book, checking out date and time and due date and time must be stored in the database. 1. e schemausing the ER diagram for this library database Design the conceptu application. Specif referential integrity constraints on each relationship type. Note: Make appropriate assumptions to make the library data requirements in your universe of discourse (UoD). Derive logical database schema by the mapping rules in chapter 9 (mapping rule 1 to mapping rule 9). Draw ER diagram including tables and their relationships. Each table consists of columns (attributes) with proper data types. Relationship should follow referential integrity constraints. Use MySQL forward engineer tool, generate DDL SQL statements for creating librarydb Before resetting values for the variables SQL_MODE, FOREIGN_KEY_CHECKS, and UNIQUE_CHECKS, populate database tables by insert statements. Note: populate each table with at least 3 tuples Write the following SQL queries attirtbutes of cach entity type and structural constraints and 2. 3. 4. 5. a. Retrieve library branch name, book ID, and number of copies of each boolk b. Retrieve book title, number of copies of the book, and library branch name where checked out by library members. book title is " a book title" and library branch name is 'a branch name' (e.g. where clause may be as follows: book title is 'Database Systems' and branch c. Retrieve library branch name and number of books checked out by members d. Retrieve the library member's name who checked out at least one book (Hint: use e. Retrieve the library member's name who did not check out any book (Hint: use name is 'Yonkers'). from each library branch. exists clause exists clause)
