DBST

Homework 5

Question 1: (25 Points)

The diagram below is an ERD for a public library database.

NOTE: There are two types of catalog items – Book and DVD. A customer can check out one item at a time.

Study the ERD and answer the following questions:

I. Identify all relationships and specify cardinality and business rules. For example: 1:M between Library and Branch: a library has many branches; a branch belongs to one and only one library.

II. For each entity, identify primary key and foreign key, if any. For foreign key, also specify parent entity and matching attribute in parent entity.

III. Write SQL DDL statements to create tables in Oracle and also implement primary key, foreign key, and NOT NULL constraint. Include all columns listed with the correct data type. This step is important, as in Question 2 you will run INSERT statements to populate the tables you created and then write SELECT statement to query those tables.

IV. Write SQL statement for the following scenario:

a. Add a new customer Eric Short with Customer_ID 10 and Customer_Zip 23456.

b. Save changes permanently.

c. Change customer Eric Short zip from 23456 to 20231.

d. Cancel the change made in step c, restore data to its original status prior to step c.

e. Delete customer Eric Short.

f. Save changes permanently.

For SQL code, submit both source statements and results of running your statements.

Question 2: (25 Points)

Continue Question 1 above. It is important for you to create those tables with the exact table/column name and exact column data type/length as shown in the ERD.

Run the script Homework5_ Part2_q2.sql attached and then respond the following SQL problems:

Create SQL statements for the following scenarios. Your response should include SQL statement, output and any other assumptions you have made to arrive at the solution. Please provide a single SQL query for each problem.

a. For each book in the catalog, display its title, publisher, ISBN, release date, number of pages, and whether it’s carried in any library (“Yes” or “No”). Sort results by title. Make sure there is no duplicate information.

b. For a book with ISBN 9780756404079, display it’s title, publisher, ISBN, which library (not branch) has it, and total number of copies each library has. Do not show libraries that don’t carry it. Sort results by number of copies in decending order.

c. For each DVD in the catalog, display it’s title, length, release_date, and how many times it has been checked out by all customers across all libraries. Include those that have not been checked out yet (display as 0). Sort results by title.

d. Display those customers who have checked out the same catalog item more than once. Show customer name, and for each catalog item show title, type, number of check out times, first check out date and last check out date for the item. Sort results by customer first/last name and catalog item title.

e. Show all customers who have checked more books than DVDs. Display customer name, total book checkouts and total DVD checkouts. Sort results by customer first name and last name.