ER Diagram 06: Video Store Rentals

Video Rental Store

A video rental company has a number of stores. The data kept on each store is the complete address (street address, city, state, and zip), and phone number. Each store is assigned a store ID number (e.g. S0001, "S" is for "store"), which is unique throughout the company. Each store has a staff, with one of those staff members designated as the manager. The manager is responsible for the day-to-day running of a given branch. Each branch has a stock of videos. The data kept for each video is the catalog ID number, video number, title, category, daily rental fee, cost, status, and the names of the main actors and the director. The Catalog ID number uniquely identifies a specific disk (e.g. S0001-D000001, "D" is for "disk"). The first part of the catalog ID number is the store ID number, so a given video can always be returned to the store it was rented from. The remainder of the catalog number will uniquely identify each individual video in the store. If there are multiple copies of the same video, they will have the same video number (e.g. all copies of Gone With the Wind will have the video number V12345; "V" is for "Video"), but a unique catalog ID number. (Example: S0001-D000001 is Gone With the Wind (which is video number V12345); S0001-D000002 is also Gone With the Wind (which is still video number V12345). A video is given a single category such as Action, Adult, Children, Drama, Horror, etc. The status of a video indicates whether a specific copy of a video is available for rent. Before renting a video from one of the stores, a customer must first register as a member. The data held on a member is the member ID number, first and last name, complete address, date joined, home phone, cell phone, and credit card number. Once registered, a member is free to rent up to 10 videos at a time. The data held on each rental is the rental ID number, the member ID number, the catalog number of the video rented, the rental date, due date, fee, and actual return date. Each video rented (even if rented in the same transaction) generates its own rental ID number. The rental ID number is unique throughout the company (e.g. S0001-R0000001 the Store Number at the front ensures that no two stores will ever have the same rental number; "R" is for "Rental").

 

Employees need to be able to:

         Look up customer contact information. The employee may look up the customer by name or by phone number.

         Look up information for a specific video. The employee may look up the video by name or by its catalog number or by its video number.

         Look up the rentals for a specific customer (using name or phone as a key).

         Tell a customer which movies the store has with a given actor/actress in them (e.g. all of the John Wayne movies).

         Produce reports of all videos that are currently rented.

         Produce reports of all videos that are currently overdue.

         Produce a history of the rentals for a given video (either by specific catalog ID number, or by video number).

         Produce a history of the rentals for a given customer (using name or phone as a key).

         Produce a report of income generated from rentals between any two dates.

 

Note that none of these reports require information on employees, therefore you do not need to worry about an employee entity (although a complete information system would also have this information).

 

Do the following:

         Identify each entity class.

         Identify all attributes for each entity class.

         Identify all determinants. Note that this may require you to re-think your entity classes.

         Identify a key for each entity class that will uniquely identify a single entity. Note that the key may be more than a single field. Underline the key field.

         Identify each foreign key (not all entity classes will have a foreign key). Make foreign keys italic.

         For each entity class, identify relationships between it and other entity classes.

         Draw the ER diagram using the crow's foot format used in the textbook. You only need to give the entity class name in the boxes of the diagram.

         For each entity, create three rows of sample data.