DatabaseDesign

CMIS 320 Project 2

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 for this assignment

Using this information, you should:

1. Review the course content that contains information about:
• 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).
• 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).
• 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:
• First, determine a list of entities needed for the Mom and Pop database.
• 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.

• 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.
• Use either ERAssistant or Visio to create an entity/relationship diagram (ERD) showing all

your entities, attributes, and relationships.

• You will find ERAssistant and Visio in the virtual Lab Broker.
• Keep in mind that all entities should be related to at least one other entity.
• Your ERD should have all one-to-many relationships and must not have any many-to-many

relationships.
• 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 Microsoft Visio). Name the file your
last name, followed by Project 2. For example: Smith_Project_2.ERD. It is recommended
to also submit a print the ERD diagram into PDF format and submit the PDF file, if
possible.

(b) Your metadata file.

Assignment-2’s grading rubric:

Attributes Meets Does Not Meet

Entities and attributes

45 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

45 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

90 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

45 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.

Resources that you might explore:

Note: If the videos are no longer available on YouTube, do a search for other relevant content.

1. Difference between conceptual logical and physical data model:

2. ER Diagramming using ERAssistant:

3. ER Diagramming using Visio: