Consider the following entity relationship diagram:The Contests entity identifies dog shows competed 1 answer below »

Consider the following entity relationship diagram:The Contests entity identifies dog shows competed in by entities (dogs) from theContestants entity type. The Wins relationship type identifies which dogs won whichdog show (with the points won by the dog). The CompetesIn relationship typeidentifies which dog show the dogs compete in (and what person presented the dogduring the show).a. Write the SQL create table statements to create tables to represent the Contestsand Contestants entity types as relational tables.b. A many-to-many relationship type becomes a relational database table. Write theSQL to create a table for the CompetesIn relationship type. Be sure to specify theprimary key constraint and the foreign key constraints for this table.c. How would you represent the Wins relationship type? You may write SQL for a newtable or you may modify the SQL create statement for Contests or Contestants asappropriate. There is more that one correct choice for this problem.2. Consider the following table:RentFromUsDatabaseVIDMakeModelYearColorRCNStartDateEndDateCIDCNameCAddressCPhoneThis table tracks the rental of a small companies vehicles to the companies customers. Thistable has a row for every rental agreement between a customer and the company. The VIDattribute is a vehicle ID that is unique over all the vehicles (described by the make, model,year and color attributes). The RCN attribute is the rental constract number for a rental2/7Prof. Dave Pitts CS610AH1: Database Management Systems Spring 2015agreement with the start date and end date described by the StartDate and EndDateattributes. The RCN is unique. Finally, the CID is a customer ID that is unique for eachcustomer. It has the following functional dependencies that hold:* VID –> (Make, Model,Year, Color)* RCN –> (StartDate, EndDate)* CID –> (CName, CAddress, CPhone)* CID, RCN, VID –> (Make, Model, Year, Color, StartDate, EndDate, Cname,CAddress, CPhone)a. Identify which functional dependencies are key dependencies (a functionaldependency X–>Y such that the right hand side set of attributes Y is complete tableattributes.b. This table is not in 2NF because of particial dependencies on candidate keys.Decompose the table to produce 2NF tables (you will have

Attachments:

Don't use plagiarized sources. Get Your Custom Essay on
Consider the following entity relationship diagram:The Contests entity identifies dog shows competed 1 answer below »
For as low as $7/Page
Order Essay

2015-Spring-C….pdf

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*

*