$1$ Database Design $[40]$

A basketball tournament will be held in Newark and you are asked to design a database that will help keep track of different kind of information related to the tournament. Many people will be taking part of the tournament with different roles: player, audience, organizer, employee of a team, and volunteer. Some common attributes are shared by all of these people: SSN $($identifier$),$ Name, Address $[$City$,$ State, Zip$],$ Birth Date, and Phone. Each of the five groups has at least one unique attribute of its own. Players have a Position $($Forward$,$ Guard or Center$),$ members of the organizing team have a Date Joined $($when they joined the organizing team$),$ Employees of a team have a Date Hired, Volunteers have Skills, and Audience has a Reservation Date and price.

There are some additional people in the game, those who do not belong to one of these five groups $($their number is relatively small$).$ Note that the audience is defined for each match and any person can be part of the audience of a given match $($for example, a volunteer who is watching a match is part of the Audience for that match; he$/$she has to have a reservation for that match$).$

Each player belongs to one $($and only one$)$ team. A team should have at least twelve players, and should have at least two Guards, two Forwards and one Center. In each team, one of the players is assigned as the team leader. Team has Team Id and T Name $($Team Id should between $1$ and $30).$

An employee of a team has three kinds of job: coach, physician or staff. A coach has a coach certificate, and physician has a physician license. Each employee has only one kind of job. Each employee belongs to one $($and only one$)$ team. A team can have no staff. But it cannot have more than two coaches.

Each team should attend at least one match. Each match has exactly two teams, two referees and three time controllers. A Referee should be an organizer. If a volunteer has computer skills, they can be chosen to be a time controller; otherwise, the time controller should come from the organizers. A match has three attributes: Match Id $($Match Id$),$ Date Held and Gym#$.$ We need record the scores of the two teams in this match.

Each Gym has one $($and only one$)$ manager, who is a member an organizer.

Audience could order one or more seats on his$/$her reservation. A seat can be assigned to only one person for a given match. A seat has Seat$-$Id $($unique identifier$)$; Gym# and Seat#$.$ Anybody in the audience of a match needs to have a seat reserved.

Questions

$1.$ Propose an entity$-$relationship $($ER$)$ diagram to model this information. For each of the entities, feel free to define a set of attributes $[25].$

$2.$ Give a step by step mapping of the ER diagram to the relational model. Explain your decisions. List every relationship before mapping it and explain in $1$ sentence your mapping decision