CMIS Project 1

Project 1



This
project allows students to demonstrate their skills in the area of designing
relational databases to satisfy specific business rules and requirements. The
deliverables for this project include an Entity Relationship Diagram and
detailed documentation describing the database design and structure.



Project 1 Details:

In
this project you will be provided a set of specifications to create an
entity-relationship diagram 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 project. 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 describe fully the relationships
between entities via pairs of sentences that indicate the two components of the
total relationship in both directions between the entities. Relationships may
be unary, binary, or ternary with respect to entities. You should not have any
many-to-many relationships.

2.
Begin the database logical design by identifying the entities, relationships
between entities, and entities' attributes as we have done in this course. Use
the same entity/relationship diagram
(ERD) notation as used in class for entities, attributes, and relationships.
Sketch your ERD by hand or a drawing program (e.g., Visio, PPT, SQL Modeler …)
on one single 8-1/2" x 11"
page (8-1/2" x 14" maximum), labeled "Mom and Pop Johnson Video
Store Database E/R Diagram." Your ERD should not have any many-to-many relationships
between entities. Make sure that all relationships between entities are
one-to-many to facilitate the construction of relational database tables.

3.
Complete the logical database design and start the physical database design by
creating metadata (i.e. documentation) that describes the table(s) created from each entity and the column(s) created from each attribute in the ERD. Attributes
should be self-describing. 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. Begin these descriptions on a page labeled "Proposed
Database Tables and Columns based on E/R Diagram." All tables must be
3rd Normal Form. Indicate any and all assumptions that were made.

4. Make sure your work is neat and
legible.



5. Follow
the procedures in the course modules and the Database Development Phases
document for

this
project.



Submissions:

1.
You should submit a well-organized Word
document that includes your entity list, ERD, and database design description.
This document should also include all assumptions and your lesson learned
during your project. Name you project
XXX_PROJECT1.doc(x) where XXX are your initials.

2.
Submit document to LEO Project 1 area
before the due date. Please contact the instructor before the due date if you have any questions or concerns.









Grading:

This activity is awarded 25% of the total grade in the
course. In the gradebook, the total number of points will be set to 100. The
project elements are assessed as follows:




Attributes


Value




Project Design


30 points




Project Functionality


40 points




Documentation


25 points




Documentation
grammar and spelling


5 points




Total


100 points