Project Assignment 3

CSC319 - Database Technology

Due: 11/19/2001

Note: Project #3 is a continuation of Project #2.

The presentation of your design was well received and the software development project has been funded. You have been asked to create a relational database schema from the ER model presented to the video store managers.

Your tasks are:

1. Derive a relational schema from your ER model.
   Note: Use the transformation rules discussed in class.
2. Create relational tables from your schema.
   Note: Write SQL DDL for each table. You may use MySQL, SQLServer (use the Pubs database) or MSAccess.
3. Create test data for each table and populate the tables.
4. Given your test data, write SQL queries to accomplish the following:
   1. List the title of all CD's in the catalog.
   2. List the title of all movies of a particular genre in the catalog.
   3. List the name and address of all members.
   4. List the address of members who are more than 2 days late in returning an item (include the description of the item in your list).
   5. List all items in the catalog that a particular performer has performed in.
   6. List the items currently borrowed by a given member.

Submit hardcopy of the database schema, table contents (SELECT * FROM table_name), as well as SQL, and corresponding output, for each query above.