Data Base Project

CSCI 6622 — Databases — 14 Winter — Course Project, Due:

THE DUE DATE OF THE PROJECT WILL BE ANNOUNCED LATER: it will probably be around a week before the final exam.

The classical music database: You have to create a database for use by the employees of a classical music mail order business. The mail order business sells both cassettes and CDs. You can assume that one cassette or one CD has only one recording. For each recording we keep track of the recording number (which is unique), the composer (each recording has only a single composer), the name of the composition, and the year it was composed in. An item can be a cassette or a CD; a cassette and a CD with the same recording will have the same recording number but different item numbers (item numbers are unique). For each item we keep track of whether it is a cassette or a CD and what is its cost (the price for the cassette or the CD containing the same recording could be different), and which is the label selling that item (i.e. a recording company like SONY, HMV etc). You can assume that one item is only sold by one label, and that some recordings are only being sold only as cassettes, some only as CDs and some as both. You can assume that the names of the composers are unique i.e. no two composers can have the same name. You can assume that the names of the labels are unique and for each label we also keep track of the name of the CEO of the label, and the addresses of the label; one label might have multiple addresses. Customers can be uniquely identified by their customer id. For each customer, we also keep track of his or her last name and the zipcode. In a single order, a customer might order items from different labels.

Among the queries your database should be capable of handling are:

1. List the names of the customers who have bought a cassette with Mozart’s fifth symphony.

2. Get the names and addresses of the the labels that sell recordings by Bach.

3. Get the names of the labels that sell cassettes but not CDs.

4. Get the names of the labels that sell recordings by Vivaldi and recordings by Beethoven.

5. List the names and zipcodes of all customers who bought a recording by Tchaikovky and another recording by Bach in a single order.

6. For recordings of Beethoven’s 4th concerto, how many were sold as CDs and how many as cassettes?

7. List the names of all customers who bought Vivalidi’s Symphony in B major but did not buy any other recording.

8. Which customer spent the most money in a single order.

9. Which was the best selling recording i.e. for which the total of cassettes and CDs sold is more than that of any other recording.

10. How many customers have bought 6 or more CDs (possibly in different orders).

11. The basic information about customers (customer id, name and zipcode ) has to be entered using forms. For this query, you have to also give a printout showing what the form looks like i.e. what would it look like to the user who is using the form to enter the customer data.

12. You have to print out a report containing, for each customer, a list of all the cassettes bought by that customer.

Please turn over to the other side for further instructions.

You need to turn in a hard copy (paper copy) of the following:

1. A description of any assumptions you are making in designing this database.

2. An E.R. diagram of your database.

3. The relational schema with all primary and foreign keys similar to Elmasri Figure 5.7.

4. If you are using Access, a printout of the relationships.

5. A printout of all the tables in your relational instance. Make sure your instance has enough information content to have meaningful results for all 12 queries. In particular, none of your queries should return an empty table.

6. For each of the queries :

(a) For each query, copy the query in English from this project sheet to the top of the page of what you turn in.

(b) Show the SQL to implement the query. You may not need to do this for queries 11 and 12 if you did not use SQL.

(c) If you are using views or subqueries or intermediate tables

• explain in English what the subquery is doing. • show the SQL to create the views or subqueries or intermediate tables • show the tables for these views or subqueries or intermediate tables.

(d) Show what is the final output table (produced by the DBMS) on the query.

You also need to submit on blackboard the database PROJECT containing

1. The relational instance

2. For each of the queries 1 through 10 , the saved SQL queries, clearly labelled as query1, query2 etc.

3. If you are using views or subqueries or intermediate tables, these should also be here, clearly labelled.

4. The form and report specification for queries 11 and 12.

Notes:

• You can implement your database in either Microsoft Access or another relational DBMS of your choice.

• If a particular SQL construct is not available in the DBMS you are using, you may have to use a different SQL construct.

• Feel free to create views or to save queries (to help answer a more complex query) or to create other tables (eg: using the INTO command discussed in class) if that helps you.

• Please note that what is turned in on blackboard should exactly match what is being turned in on the hard copy. So, for example, if you have some queries on the database you are submitting on blackboard, those should be an exact match with what is being turned in on the hard copy.

• If the hard copy is submitted on time but the blackboard submission is done after the start of class on the due date, the assignment/problem will be marked late.

• If the the blackboard submission is done on time but hard copy is submitted after the start of class on the due date, the assignment/problem will be marked late.

• Email submissions will not be accepted.

2