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.
Figure 1: Example ERD
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).
Example (starter)
Meta Data
Mom & Pop Database
Entity Name Attributes PK/FK? Data Type Description
Producer ID PK integer Primary Key for Producer entity
CompanyName no varchar (255) Producer Company Name
Country no varchar (255) Producer Country
Movie ID PK int Primary key for Movie
ProducerID FK int
This is the FK that points to
Producer
Title no varchar (255) Title of Movie
Duration no int Length of Movie
Rating no char(1) Customer rating of Movie
Customer_Movie xxx
xxx
Customer xxx
xxx
Figure 2: Example (partial) meta data for the database
5. Submit a Word document that contains two parts: (a) a screenshot of the ERD
(completed in ERAssistant or Visio), AND your metadata. Name the file your last name,
followed by Project 2. For example: Smith_Project_2.ERD.
1. Difference between conceptual logical and physical data model:
https://www.youtube.com/watch?v=RJ9TpkWKyU0&t=26s
2. ER Diagramming using ERAssistant: https://www.youtube.com/watch?v=a6UCXWJZ2GE
3. ER Diagramming using Visio:
https://www.youtube.com/watch?v=597BVMtMZ1w&t=568s
