Project 1
Thisproject allows students to demonstrate their skills in the area of designingrelational databases to satisfy specific business rules and requirements. Thedeliverables for this project include an Entity Relationship Diagram anddetailed documentation describing the database design and structure.
Project 1 Details:
Inthis project you will be provided a set of specifications to create anentity-relationship diagram and design accompanying table layout using soundrelational modeling concepts and practices. The relationships between theentities and the attributes for the entities will be identified and described.This database will provide the foundation for the follow-on project. Thefollowing paragraphs provide the background and summary of the businessrequirements.
Youare a database consultant with Ace Software, Inc. and have been assigned todevelop a database for the Mom and Pop Johnson video store in town. Mom and Pophave been keeping their records of videos and DVDs purchased from distributorsand rented to customers in stacks of invoices and piles of rental forms foryears. They have finally decided to automate their record keeping with arelational database.
You sit down with Mom and Pop todiscuss their business, and watch their operation for about a week. Youdiscover quickly that a video and a DVD are both copies of a movie kept ina separate plastic case that is rented out. They have several copies of eachmovie they rent; therefore there are several videos and DVDs for each movietitle. You learn that in their inventory they have several thousand videos andDVDs, which they get wholesale from about a half dozen distributors. The videoand DVD prices to them are based on the quantity of their shipment and the pastbusiness they have done with each company.
The price of a DVD for a moviemight be different than the price of a video for the same movie, even fromthe same distributor. Each distributor provides different types ofmovies (e.g., suspense, horror, mystery, comedy, etc.). A singledistributor may provide several different types of movies in both video and DVDformat. It is possible to obtain the same movie from multipledistributors, and at different wholesale prices.
Each video and DVD has a uniqueidentification number that Mom and Pop assign in their inventory, in additionto the distributor’s serial number for the item. Each movie also has a uniqueidentification number Mom and Pop assign in addition to the title, and anymovie IDs the distributors use in their electronic catalogs. Distributorsprovide electronic catalogs to Mom and Pop and the information from thesecatalogs must be included in the database.
Mom and Pop need to record when avideo or DVD is rented, when a video or DVD is returned, and all customercharges such as late and damaged fees, failure to rewind fees, and taxes. Theyneed a report of which videos are returned late because there are standard andlate charges. On occasion there are discount prices for certain moviesor types of movies. Customers want to rent movies based on actors oractresses, running length, type of movie, rating, year released, the director,and the academy awards won (by the movie, the actors, the actresses and/or thedirectors). Customers also want to know how many videos they have rented in thelast month, year, and so forth. Mom and Pop need to keep only basicinformation on customers in their database, such as name, address, telephonenumbers, etc.
There must be no limit to thenumber of video and/or DVD copies of a movie that Mom and Pop can have in theirinventory. Video/DVD ID numbers, movie ID numbers, and distributor IDnumbers for videos, DVDs, and movies are all different. Also, each moviemust be able to have an unlimited number of actors, actresses, directors,and academy awards (i.e., Oscars). Other types of awards (e.g., GoldenGlobe, People’s Choice, etc.) are not of interest for this application. Therental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept inthe database.
Usingthis information, you should:
1.Determine and list your entities.Then describe fully the relationshipsbetween entities via pairs of sentences that indicate the two components of thetotal relationship in both directions between the entities. Relationships maybe unary, binary, or ternary with respect to entities. You should not have anymany-to-many relationships.
2.Begin the database logical design by identifying the entities, relationshipsbetween entities, and entities’ attributes as we have done in this course. Usethe 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 VideoStore Database E/R Diagram.” Your ERD should not have any many-to-many relationshipsbetween entities. Make sure that all relationships between entities areone-to-many to facilitate the construction of relational database tables.
3.Complete the logical database design and start the physical database design bycreating metadata (i.e. documentation) that describes the table(s) created from each entity and the column(s) created from each attribute in the ERD. Attributesshould be self-describing. Particularattention will be given to the proper specification of all primary key (via “PK”) and foreignkey (via “FK”) columns in the table layouts. These shouldmatch your ERD exactly. Begin these descriptions on a page labeled “ProposedDatabase Tables and Columns based on E/R Diagram.” All tables must be3rd Normal Form. Indicate any and all assumptions that were made.
4. Make sure your work is neat andlegible.
5. Followthe procedures in the course modules and the Database Development Phasesdocument for
thisproject.
Submissions:
1.You should submit a well-organized Worddocument that includes your entity list, ERD, and database design description.This document should also include all assumptions and your lesson learnedduring your project. Name you projectXXX_PROJECT1.doc(x) where XXX are your initials.
2.Submit document to LEO Project 1 areabefore 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 thecourse. In the gradebook, the total number of points will be set to 100. Theproject elements are assessed as follows:
Attributes
Value
Project Design
30 points
Project Functionality
40 points
Documentation
25 points
Documentationgrammar and spelling
5 points
Total
100 points
Reviews
There are no reviews yet.