Out September 18, Due September 27
The system you are asked to implement is intended to be general so that it can be customized easily to used by any store rather than being custom designed for one particular store. We will assume that a generic video rental store maintains information about movies, cards, and distributors of movies. A movie has an ID number (generated by the store), a title, release date, rating and price category; for example, it is common for newly released movies to be more expensive. Additionally, information is maintained about the principle actors in the movie, the director, the producer, and other important attributes.
A card has an ID number (again, generated by the store), category, owner name, owner address, owner phone, owner credit card (optional), and amount credited/debited to the account. Additionally, information may be maintained about other names on the card (e.g. additional family members) and their rating (e.g. whether the person is only allowed to rent G, PG or PG13 movies).
A distributor has a name, address and phone number. Copies of movies are purchased from distributors on a certain date for a certain price. Once they have been tagged with a copy number, a copy can be rented to a card; the rental date must be maintained.
When a movie is returned, the rental is recorded in a rental history, which maintains the copy rented, renting card, rental date, return date, amount paid and the payment form. The rental history is used for two purposes: to give employees a basis to work from when they are asked what movie the card member should see; and to maintain accounts (obviously, only a portion of the history will be maintained on line; the rest will be archived).
To make this rental system applicable to a wide range of stores, you should allow different classes of cards, each class having a different rental terms. You should design this part to be as flexible as possible; one suggestion is to specify for each class a base rate for the first two days and another rate for each additional day. However, you should be creative in designing this and draw on your own experience with various rental stores. An individual store should also be able to specify the maximum number of movies that can be rented simultaneously, and the maximum amount of time a movie can be held for before the card is ``frozen''.
Your first assignment is to design a structure for this database. You should hand in a description of the database, a description of any assumptions you have made about the data, and an ER diagram. Be sure to show all information possible in the ER diagram (e.g., keys, cardinality, participation, etc.). If you wish to use an extended ER notation, describe the particular notation you are using.