Project Assignment 2

CSC319 - Database Technology

Due: 7/19/2001

You are a new hire at a local consulting firm and have been assigned to a software development team. You have been asked to design and implement a database for a video store. You have been provided with preliminary requirements and have determined that the video store acts as a club where members (i.e. customers) may borrow from a catalog that contains DVD's, video tapes, and music CD's. The database is intended to keep track of items in the catalog as well as club members.

Your most recent conversation with your contact at the video store provided the following details:

• Members borrow as many DVD's, video tapes, and CD's as they wish.
• There are different types of movies (i.e. action, comedy etc.)
• A movie can exist more than once in the catalog. (i.e. several DVD copies and/or several video tape copies).
• Items may be borrowed for 1 day, 3 days (week-end) or a week.
• Videos are classified as either new releases or oldies.

You have limited time to produce a design. Your design will be presented to managers of the store. The quality of your design will determine if your firm gets the development contract.

Note that the problem definition is somewhat incomplete, so you may have to make assumptions about how information should be represented. Make any reasonable additional assumptions. Do not over complicate the design. Be sure to state your assumptions clearly. You will have one more chance to speak to your contact before the presentation and so you must be prepared to resolve any ambiguities or incomplete information at that time.

Following, are a minimum set of sample queries that your system should be able to address.

1. List members who borrowed a given movie.
2. List the telephone numbers of members who are more than 2 days late in returning an item (include the description of the item in your list).
3. List in alphabetical order all movie titles for a given actor.
4. List members that have never borrowed a DVD.
5. List the items currently borrowed by a given member.

You must create an ER diagram (use Visio or draw carefully by hand), and a database schema. Use SQL Server to implement your design (i.e. create relational tables based on your design). You do not need to create a new database. Create your tables in the pubs database. Populate your tables with sample data (make up your own data) and execute the sample queries above.

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