Database Design
Introduction
This assignment allows students to demonstrate their skills in designing relational databases to satisfy specific business rules and requirements. The deliverables for this assignment include an Entity Relationship Diagram (ERD) and the associated detailed documentation describing the database design and structure. In this assignment you will be provided with a description of an application (below) to create an ERD and design accompanying table layout using sound relational modeling concepts and practices. The relationships between the entities and the attributes for the entities will be identified and described. This database will provide the foundation for the follow–on assignment. The following paragraphs provide the background and summary of the business requirements.
Scenario
You are a database consultant with Ace Software, Inc. and have been assigned to develop a database for the Mom and Pop Johnson Video Store in old–town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database. You sit down with Mom and Pop to discuss their business and watch their operation for about a week. Mom and Pop rent legacy video/DVDs and discuss their need to move away from maintaining their rental records on paper. You discover for 1 title, they typically (but not always) offer two formats including a video (VCR) and DVD format. Both formats of a movie are kept in a separate plastic case when rented out. They have several copies of each rental movie. There are several videos and DVDs for each movie title. You learn that in their inventory, they have several thousand videos and DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to them are based on the quantity of their shipment and the past business they have done with each company. The cost of a DVD rental might be different than the price of a video rental for the same movie, even from the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery, comedy, etc.). A single distributor may provide several different types of movies in both video and DVD format. It is possible to obtain the same movie from multiple distributors, and at different wholesale prices. Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, in addition to the distributor’s serial number for the item. Each movie also has a unique identification number Mom and Pop assign in addition to the title, and any movie IDs the distributors use in their electronic catalogs. Distributors provide electronic catalogs to Mom and Pop and the information from these catalogs must be included in the database.
Mom and Pop need to record when a video or DVD is rented, when a video or DVD is returned, and all customer charges such as late and damaged fees, failure to rewind fees, and taxes. They
need a report of which videos are returned late because there are standard and late charges. On occasion there are discount prices for certain movies or types of movies. Customers want to rent movies based on actors or actresses, running length, type of movie, rating, year released, the director, and the academy awards won (by the movie, the actors, the actresses and/or the directors). Customers also want to know how many videos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basic information on customers in their database, such as name, address, telephone numbers, etc. There must be no limit to the number of video and/or DVD copies of a movie that Mom and Pop can have in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos, DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors, actresses, directors, and academy awards (i.e., Oscars). Other types of awards (e.g., Golden Globe, People’s Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.
Directions
Using this information, you should:
1. Review the course content that contains information about:
a. Review the difference between a Conceptual Model and a Logical Model (keep in mind that for this assignment, we are not addressing a Physical Model).
b. Review the readings about entities (e.g. tables), their attributes (e.g. fields), and the relationships that provide the linkage between pairs of entities (tables).
c. Review what a key attribute is for an entity, as this is the main mechanism that can associate one entity to another, as a primary way of doing this in an RDBMS.
2. For your data model:
a. First, determine a list of entities needed for the Mom and Pop database.
b. Second, determine a list of ‘preliminary’ attributes for each of your entities. These are noted as ‘preliminary’ as you may decide to later change/modify the list of attributes in each entity. Be sure to also identify a key field for each entity.
c. Third, create relationship sentence pairs between those entities that are related. You should not have any many–to–many relationships. Hint: If you do have any many–to–many relationships in your preliminary design, you will need to break these down and use an association/intersection entity.
3. Draw an ER model using an ER tool.
a. Use either ERAssistant or Visio to create an entity/relationship diagram (ERD)
showing all your entities, attributes, and relationships.
b. You will find ERAssistant and Visio in the virtual Lab Broker.
c. Keep in mind that all entities should be related to at least one other entity.
d. Your ERD should have all one–to–many relationships and must not have any many–to–many relationships.
e. Develop the ERD and save your work. This may take a few revisions before you get the model to your perfection. Shown below is an Example ERD using the ‘crows foot’ notation. Please note that the following model DOES NOT FULLY represent the Mom & Pop video store. This example shows you a model that contains (a) entities, (b) attributes, (c) key fields/attributes for each entity, and
(d) the relationships that exist between each pair of entities. For this assignment, create either an ERAssistant or Visio model that represents YOUR Mom & Pop video store. Note that the model below does not show the Foreign Keys. For your model, be sure to include the FKs in the related entity.
4. Create ‘metadata’ (e.g., data about data) that describes each entity (table) along with the attributes (field/column) in the ERD. While this information can be done in the comments section of ERAssistant or denoted in Visio, consider using a spreadsheet to identify your metadata for the database. Keep in mind that metadata is typically kept in a “data dictionary” for a given project in the organization, but we’ll simply use a spreadsheet for this assignment. Please pay particular attention to the description of all primary key (via “PK”) and foreign key (via “FK”) columns, as these support the relationships that are designed between tables. A ‘starter’ example of the meta data is shown below. EXPAND on this example to fully describe the entities, attributes, and relationships in your database. (see the readings for this week).
