CMIS 320 project: This assignment allows students to demonstrate their skills in designing relational databases to satisfy specific business rules and requirements.

Order Instructions

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 ofrelationship

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 oftable

specification metadata properly

includes all tables, their

columns, and all required

details for the columns.

0 points

Major omissionsfortable

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: https://www.youtube.com/watch?v=a6UCXWJZ2GE

3. ER Diagramming using Visio: https://www.youtube.com/watch?v=597BVMtMZ1w&t=568s

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.