Answered step by step
Verified Expert Solution
Link Copied!

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 (20 marks)Create 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 (20 marks)Create a relational model based on your ER model.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Students also viewed these Databases questions

Question

Choose an appropriate organizational pattern for your speech

Answered: 1 week ago

Question

Writing a Strong Conclusion

Answered: 1 week ago