Answered step by step
Verified Expert Solution
Question
1 Approved Answer
You have been asked by the Waterloo Recreational Sports Association ( WRSA ) to help them withtheir database design. The WRSA has come up with
You have been asked by the Waterloo Recreational Sports Association WRSA to help them withtheir database design. The WRSA has come up with a database specification and included it in thenext section. You will be required to create an ER model based on the specification. Once youhave created the ER model you will map it to a relational model.Database specificationThe WRSA oversees several sports. Each sport has a unique name, the number of referees requiredto referee matches of that sport, and a list of locations where the sport can be played. For simplicity,each location is a single string. The WRSA is comprised of many teams. Each team plays exactlyone sport, and every sport has some team associated with it Each team has a unique name. A teammay choose to have one or more coaches. However, since the WRSA is recreational, teams are notrequired to have a coach. Coaches have a name, address, phone number, and certification date.Players who are registered in the WRSA are members of teams. Each player can be a member ofmultiple teams, but they must be a member of at least one team. Players have a name, age, phonenumber, and address. Coaches and players are covered by the WRSAs insurance and each havean insurance number. Each player must have at least one emergency contact. The emergencycontacts each have a name, address, relation to the player, and phone number. A player cannot bea coach and cannot have two emergency contacts with the same name. The WRSA selects severalplayers to act as mascots. Each mascot must have a nickname. Every team plays in matches withother teams. A match is given a unique match number, a location, and a date and time. Matchesare always between two different teams and the outcomes are not recorded. Each match is refereedby other players who are not involved in that specific match.Part I: ER model marksCreate an ER model based on the database description. Make a registrant entity that is a superclassof players and coaches. Give registrants unique IDs.For each entity you create, ask yourself: what are its attributes? what attributes can make up its key? is the entity weak? is the entity a subclass of another entity? what relations does it have with other entities? are any attributes multivalued?For each relation you create, ask yourself: what is its cardinality ratio? what are the participation constraints? is it an identifying relationship?Come up with at least one derived attribute and include it in your ER model.Part II: Relational model marksCreate a relational model based on your ER model.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started