The NCAA (National Collegiate Athletic Association) wants to develop a database to keep track of information about college basketball. Each university team belongs to only one conference (e.g., the University of Houston belongs to American Athletic Conference; the University of Mississippi belongs to the Southeastern Conference, etc.); but a few teams may not belong to any conference. A conference has several teams; no conference has fewer than six (6) teams. Each team can have a maximum of twenty (20) players and a minimum of twelve (12) players. Each player can play for only one team. Each team has from four (4) to eight (8) coaches on its coaching staff and a coach works for only one team. Lots of games are played in each university location every year; but a game between any two universities is played at a given location only one time a year. Three referees from a larger pool of referees are assigned to each game. A referee can work several games; however, some referees may not be assigned to any game. Players may play in several games, but it is possible that some players simply sit on the bench and do not play in any game. A game involves at least ten players. Player performance statistics (i.e., points scored, rebounds, assists, minutes played, and personal fouls committed) are recorded for each player for every game. Information collected about a game includes the final score, the attendance, and the date of the game. During the summer months, some of the players serve as counselors in summer youth basketball camps. These camps are identified by their unique campsite location (e.g., Mason, Bellaire, Kenwood, etc.). Each camp has at least three (3) players who serve as counselors. A player may work in a number of camps.

A player can be identified by student number only. The other attributes for a player include name, major, number of credit hours completed, and grade point average. For a coach, relevant attributes include name, title (e.g., head coach, assistant coach), salary, address, and telephone number. Attributes for a referee include name, salary, years of experience, address, telephone number, and certifications. Both coaches and referees are identified by their personal NCAA identification number. A team is identified by the name of the university. Other team attributes include current ranking, capacity of home court, and number of players. Each conference has a unique name, number of teams, and an annual budget. For the basketball camps, data is available on the campsite (i.e., location) and the number of courts.

When a referee retires, all links to the games handled by that referee should be removed. Likewise, if a game is cancelled, all links to the referees for that game should be removed. Although it is does not happen often, a university may sometimes leave the conference of which it is a member. Naturally, we want to keep the team in the database since the university could decide to join another conference at a later date. However, if a team (university) leaves the NCAA altogether, all players and coaches of that team should be removed from the database along with the team. In all other relationships that exist in the database, the default value of Restriction of Deletion should be explicitly indicated.

-List of entities and attribytes:

• PLAYER: (Student_Number, Major, Name, Number of credit hours completed, Grade point average)
• TEAM: (Name of the university, Ranking, Number of Players, Capacity of home court)
• COACHES: (Name, Certifications, Salary, Phone, Address, Personal NCAA identification number)
• REFEREE: (Name, Salary, Years of experience, Address, Telephone Number, Certifications, Personal NCAA identification number)
• CONERENCE: (Name, Number of Teams, annual budget) The Basketball Camps: (Campsite location, campsite, Number of courts.)
• GAME: (Final score, Attendance, Date of the game

-Relationship and Structural Constraints

• Belong_to: CONFERENCE (6,m) and TEAM (0,1)
• Play_in: TEAM (12,20) and PLAYER (1,1)
• Work_for: TEAM (3,7) and COACH (1,1)
• Play_in: Team(1,m) and Game(n,1)
• Assign_to: REFEREE: (0,m) and GAME (1,n)
• Play_in: PLAYER (0,m) and GAME (1,n)
• Counselors_in: PLAYER (0,m) and CAMPS (3,n)

Question:

1. Create a presentation layer ERD using the Look near notation of the entities listed above
   1. Include attributes and proper notation to indicate their optionality, key status, etc.
      1. Data type specifications are not required in the presentation layer ERD
   2. Deletion constraints should be included
   3. Multi-value attributes and m:n relationships should be present in the presentation layer ERD (i.e., do not decompose these relationships)
   4. A list of semantic integrity constraints that are not captured in the ERD
      1. Categorize rules as attribute, entity, miscellaneous, or deletion
      2. All business rules that can be captured in the ERD must be present in the ERD. Any business rule that cannot be captured in the ERD should be in a list of semantic integrity constraints. You may or may not have rules of each type.
      3. Include any assumptions you make.
      4. See page 103 in your book, and slides 40-41 from Day 6