Designing relational databases to satisfy specific business rules

at
Categories
Tags

 

 

 

 

 

 

 

Introduction
Demonstrate skills in the area of designing relational databases to satisfy specific business rules and requirements. The deliverables for this assignment include an Entity Relationship 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 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 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.
Directions
Using this information, you should:
1) Determine and list your entities. Then create relationship sentence pairs between those entities that are related. You should not have any many-to-many relationships.
2) Use ERAssistant to create an entity/relationship diagram (ERD) showing all your entities, attributes, and relationships. You will find ERAssistant in the virtual Lab Broker. All entities should be related to at least one other entity. Your ERD should have all one-to-many relationships and not have any many-to-many relationships.
3) Create metadata that describes the table created from each entity and the column created from each attribute in the ERD. This can be done in the Comments fields in ERAssistant. 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.
Submit the ERAssistant file which should be a .ERD file. Name the file your last name, followed by Project 2. For example: Smith_Project_2.ERD.
Grading rubric
Attribute Meets Does Not Meet
Entities and attributes 20 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 20 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 40 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 20 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

Sample Solution

Tones of Poems The shade of verse is anything but difficult to pass judgment on somebody’s sentiments through the voice during discussion. On the off chance that individuals are cheerful, or simply fun, they for the most part make their faces giggle, however on the off chance that they are furious, their voice gets stricter and greater. Creators can utilize different procedures to communicate feelings in short stories and books, for example, outcry focuses and italics. These strategies make it simpler for perusers to comprehend what the character feels. In verse, it is hard to comprehend your condition. These sonnets have various contrasts. The circumstance of verse and adversaries is unique. In Sukhrin’s sonnet, the condition is exceptionally miserable. In affection verse, the tone isn’t just dismal yet in addition exceptionally bold. The two sections are dismal so the two tones are in acceptable concurrence with the refrain. The other party is a lady in John Suckling’s verse, and Richard Lovelace’s sonnet is a restriction war. As indicated by the clarification, Suckling’s “the reason it is so pale, glad, darling” is an impersonation of the custom of affection in the court, demonstrating the writer’s uncertainty about the convention in the embarrassment and his knight’s joy. Sacling’s verse incorporates incongruity of the subjects. It blames youngsters utilizing coat love code as he considers his lady’s aloofness. An artist ‘s beau can not make a move outside of customs with the goal that he doesn’t direct his concentration toward ludicrousness and false reverence of his conduct. Without genuine love and sex understanding, ironicly the artist calls the sweetheart “youthful delinquent”, as they will be limited by crushed convictions. There is no uncertainty that verse and its three refrains have gained comparative ground. Be that as it may, the tone of verse is very contrastive. Wonder’s verse has indisputable dissatisfaction and want for affection. This is in sharp difference to Dawn’s verse where wickedness tone is self-evident. Marvell’s poeties express a great deal of feelings; both “carpe diem” (“get on the day”) and “tempus edax” (“time devourer”) are clear in this stanza. This is in sharp difference to the next two sections that don’t have clear feelings. Think about John Donne’s “The Flea” with Andrew Marvell’s “To His Coy Mistress” and conclude which is the most appealing thing. Understanding the tone of verse is significant for the artist to comprehend what he needs to state. This is likewise significant in choosing the topic of this sonnet. How would you choose the tone? In the wake of perusing just because, you may feel the tone of this sonnet. Be that as it may, how would you realize what is directly for you? You need proof from verse that underpins your case. You took a gander at the jargon and found the proof. The word reference is the decision that the writer picked when composing verse. If it’s not too much trouble consider the decisions you made when composing. You can pick what you can say anything. How would you pick the correct words? In the event that you need to get genuine, you can pick a few words to pass on a genuine tone. Initial segment of the guidelines: Please read the accompanying sentences. As a matter of first importance I will compose the sentences. Accentuate the (word) that you think will add to making the tone for each sentence. Single sentence: frozen yogurt dissolving PPreaccttiiccaall PPooeettrryy AA GGuididdee ttoo TTomeachchinngg CChmmmmoon CCoorree TTextxtt EExextmemplaarrss ffoor PPoetetryry iinn

This question has been answered.

Get Answer
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, Welcome to Compliant Papers.
Hi, how can I help?