You have been hired by the National Hockey League (NHL) to create a database mod

Question

You have been hired by the National Hockey League (NHL) to create a database model of its teams, players and their injuries. The NHL is composed of 32 teams, each having a unique name. Each team plays for a certain city, but a city may have multiple teams. A team is coached by a single person and has exactly 23 players, one of whom is designated as the team captain. A player can belong to only one team, and has a name, position (left wing, right wing, center, defense or goalie), and is graded by team scouts according to skill level at their position (an integer value between 1 and 10). Due to the long season and significant body contact, players may become hurt and thus each will have a history of sustained injuries. A game is played on a certain date between two teams (designated as home and away). Each game has an integer score, which Ls the sum of the number of goals scored by that team in the game, e.g., 5 to 1. The team with the most goals wins the game, and a tie occurs if the score is equal. Teams may play each other multiple times throughout the season, but only once on a given date. For simplicity you may assume that the database is required to only store data from the current season. (a) Provide an ER diagram in Crow's foot notation for the above scenario. The diagram must be created using software such as dia (freely available for Mac, Windows and Linux). (b) Provide the CREATE TABLE statements to implement your design.

Explanation / Answer

(b) Create table statements

Create table TEAM (

Name char(30) PRIMARY KEY,

Captain char(30)

);

Create table CITY (

Name char(30) PRIMARY KEY,

Pincode integer

);

Create table PLAYER(

Name char(30) PRIMARY KEY,

Position char(15),

Grade integer

);

Create table INJURY (

IDate date PRIMARY KEY,

Description char(150)

);

Create table GAME (

GDate date PRIMARY KEY,

Score integer

);

Create table Coach(

Team char(30) PRIMARY KEY,

Name char(30)

);

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.