Download Report

CMP 420/758 Database Systems
Assignment 4 (submit answers as hard copy)
Due by May 18, 2015
Department of Mathematics and Computer Science
[Q.1] Answer the following questions for a simple library application.
The data requirements of the library application are summarized as follows: BOOK entity is
identified by BookId, it has title and multiple author names. PUBLISHER entity consists of
Name, Address, and Phone attributes. Name is the key for the PUBLISHER.
LIBRARY_BRANCH entity has BranchId as a key and Branchname attribute additionally.
BORROWER entity has BrowerId 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 borrower at a specific library branch.
You completed the following tasks for the Assignment 3:



Design the conceptual database schema using the ER diagram for this application.
Specify key attributes of each entity type and structural constraints on each relationship
type. Note any unspecified requirements, and make appropriate assumptions to make the
specification complete.
Map the conceptual schema to logical database schema. Show your logical database
tables are in 3NF.
Show all referential integrity constraints in the logical database tables.
In this Assignment 4, students should create and populate the database tables from the
normalized set of logical relations created in the Assignment 3 using ER diagramming
tool and forward engineer tool in MySQL Workbench:
 Writing SQL Script: make a file containing the SQL statements that create your
entire database schema, named myLibDDL.sql. This includes the tables with their
constraints (e.g., referential integrity constraint).
 Writing SQL Script: make a file containing INSERT statements which populate
the tables created, named myLibDML.sql. This script will contain SQL
commands to fill data in your database tables. Each table should have around 5
sample data. If needed other SQL statements, such as UPDATE and DELETE,
can be included myLibDML.sql.
 Writing Drop SQL Script: myLibDROP.sql is used to drop all the objects you
have created for your project including tables. This will be used to start from a
clean state after some insertions and deletions were made to your database. You
should be able to clean everything by this script and re-create the database state.

Write SQL queries for the following queries on the library database:
o How many copies of the book titled “Database Systems” are owned by the
library branch “Lehman”?
o For each library branch, retrieve the name and the total number of books loaned
out from the branch.
o For each book authored (co-authored) by “Robert Feinerman”, retrieve the title
and the number of copies owned by the library branch “Lehman”.
o Retrieve the names of the borrowers who did not borrow any books from library
branches.