Description
This assignment allows students to demonstrate their skills in the area of designing relational databases
to satisfy specific business rules and requirements. The deliverables for this assignment include an Entity
Relationship Diagram and 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 entityrelationship diagram (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.
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 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.
You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case
that is rented out. They have several copies of each movie they rent; therefore 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 price of a DVD for a movie might be different than the price of a video 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.
Using this information, you should:
1) Determine and list your entities. Then create relationship sentence pairs between those
entities that are related. You should not have any many-to-many relationships.
2) Create an entity/relationship diagram (ERD) showing all your entities, attributes, and
relationships. Sketch your ERD by hand or use a drawing program. Your diagram must be on a
single page. All entities should be related to at least one other entity. Your ERD should have all
one-to-many relationships and not have any many-to-many relationships.
3) Create metadata that describes the table created from each entity and the column created from
each attribute in the ERD. Particular attention will be given to the proper specification of
all primary key (via “PK”) and foreign key (via “FK”) columns in the table layouts. These should
match your ERD exactly.
Submit everything for the above steps in a single Word or PDF file to the LEO assignment link. Make
sure your work is neat and legible and your steps are in order.
Grading rubric
Attribute Meets Does Not Meet
Entities and attributes 20 points
Student identifies a large potential
set of entities and their attributes
for the application
0 points
Major problems with identification of
entities and their attributes
Relationship sentence
pairs
20 points
Student properly formats all
required relationship sentence
pairs to describe one-to-many
relationships
0 points
Major problem with development of
relationship sentences
ERD 40 points
ERD properly includes and depicts
all entities, attributes and
relationships identified
0 points
ERD is missing many items and/or has
major problems with relationships
Metadata 20 points
Spreadsheet of table specification
metadata properly includes all
tables, their columns, and all
required details for the columns
0 points
Major omissions for table
specification metadata
