CS 338 - Winter 2025

Assignment 3

Introduction

You have been asked by the Waterloo Recreational Sports Association (WRSA) to help them with  their database design. The WRSA has come up with a database specification and included it in the next section. You will be required to create an ER model based on the specification. Once you have created the ER model you will map it to a relational model using the steps from lecture. Please upload your answers as a single PDF file. Refer to Learn for the deadline.

Database specification

The WRSA oversees several sports. Each sport has a unique name, the number of referees required to 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 exactly one sport, and every sport has some team associated with it. Each team has a unique name. A team may choose to have one or more coaches.  However, since the WRSA is recreational, teams are not required 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 of multiple teams, but they must be a member of at least one team. Players have a name, age, phone number, and address. Coaches and players are covered by the WRSA’s insurance and each have an insurance number. Each player must have at least one emergency contact. The emergency contacts each have a name, address, relation to the player, and phone number. A player cannot be a coach and cannot have two emergency contacts with the same name. The WRSA selects several players to act as mascots. Each mascot must have a nickname. Every team plays in matches with other teams. A match is given a unique match number, a location, and a date and time. Matches are always between two diferent teams and the outcomes are not recorded. Each match is refereed by other players who are not involved in that specific match.

Part I: ER model

Create a ER model based on the database description.  Make a registrant entity that is a superclass of 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

Create a relational model based on your ER model.  Make sure to follow the six steps outlined in your lecture notes.