CMIS 320 Project 2<br/>In this assignment you will be provided with

a description of an application (below) to create an entity- relationship 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.

Attached is the full assignment requirements/information:

 ATTACHMENT PREVIEW Download attachment

CMIS 320 Project 2This assignment allows students to demonstrate their skills in the area of designing relational databasesto satisfy specific business rules and requirements. The deliverables for this assignment include an EntityRelationship 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 entity-relationship diagram (ERD) and design accompanying table layout using sound relational modelingconcepts and practices. The relationships between the entities and the attributes for the entities will beidentified and described. This database will provide the foundation for the follow-on assignment. Thefollowing 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 forthe Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videosand DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rentalforms 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 casethat is rented out. They have several copies of each movie they rent; therefore there are several videosand DVDs for each movie title. You learn that in their inventory they have several thousand videos andDVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to themare 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 fromthe 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 DVDformat. It is possible to obtain the same movie from multiple distributors, and at different wholesaleprices.Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, inaddition to the distributor’s serial number for the item. Each movie also has a unique identification numberMom and Pop assign in addition to the title, and any movie IDs the distributors use in their electroniccatalogs. Distributors provide electronic catalogs to Mom and Pop and the information from thesecatalogs 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 allcustomer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a reportof which videos are returned late because there are standard and late charges. On occasion there arediscount prices for certain movies or 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 the directors). Customers also want to know how manyvideos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basicinformation 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 havein 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.

View the Answer

Using this information, you should:1) Determine and list yourentities. Then createrelationship sentence pairsbetween thoseentities that are related.You should not have any many-to-many relationships.2) Create anentity/relationship diagram(ERD) showing all your entities, attributes, andrelationships. Sketch your ERD by hand or use a drawing program. Your diagram must be on asingle page. All entities should be related to at least one other entity. Your ERD should have allone-to-many relationships and not have any many-to-many relationships.3) Create metadata that describes thetablecreated from each entity and thecolumncreated fromeach attribute in the ERD. Particular attention will be given to the proper specification ofall primary key (via “PK”) and foreign key (via “FK”) columns in the table layouts. These shouldmatch your ERD exactly.Submit everything for the above steps in a single Word or PDF file to the LEO assignment link. Makesure your work is neat and legible and your steps are in order.Grading rubricAttributeMeetsDoes Not MeetEntities and attributes20 pointsStudent identifies a large potentialset of entities and their attributesfor the application0 pointsMajor problems with identification ofentities and their attributesRelationship sentencepairs20 pointsStudent properly formats allrequired relationship sentencepairs to describe one-to-manyrelationships0 pointsMajor problem with development ofrelationship sentencesERD40 pointsERD properly includes and depictsall entities, attributes andrelationships identified0 pointsERD is missing many items and/or hasmajor problems with relationshipsMetadata20 pointsSpreadsheet of table specificationmetadata properly includes alltables, their columns, and allrequired details for the columns0 pointsMajor omissions for tablespecification metadata