Part $1.$ NHL Database $(20$ Marks$)$

For this assignment, we are going to create our own database to keep track of a hockey league.

Our database will feature NHL teams, platers, and the games they play. Download the

Ass$01.$xslx spreadsheet provided. This spreadsheet contains some data for us to initialize our

database with.

There will be $3$ relations: Team, Player, and Game. Each table must have a primary key.

Relationships between the tables must be defined, and the relationships must enforce

referential integrity.

Each team has a name, a coach, a location, and several players. Team ID is the primary key.

For each player we keep track of their first and last name, jersey, position, height in feet and

inches, weight, date of birth, and where they were born. The primary key is composite,

composed of team name and jersey number.

There are several games where each game involves exactly two teams: one team is designated

the home team and the other is the visiting team. Each game has a game ID $($primary key$)$ and

we know when the game is played and, where possible, the scores for each team $($home score,

visitor score$).$

$1.(9$ Marks$)$ Create the Player, Team, and Game tables as described above. Load the data from

the spreadsheet provided. $(3$ Marks per table$)$

a$.$ Choose datatypes that are appropriate for each field. For example, score should be

numeric with no decimals, names must be text, etc.

b$.$ All fields should be required, except scores in the Game table, and shot in the Player

table.

$2.(1$ Mark$)$ Add a validation rule and text for scores that enforce that scores cannot be

negative.

$3.(1$ Mark$)$ Add an input mask for TeamID that enforces the ID to be only $3$ letters long.

$4.(3$ Marks$)$ Create the following relationships. Enforce referential integrity. $(1$ Mark each$)$

a$.$ A team has many players $/$ a player belongs to one team

b$.$ A game has one home team $/$ a team may be the home team in many games

c$.$ A game has one visiting team $/$ a team may be the visiting team in many games

$5.(2$ Marks$)$ Add some data manually.

a$.$ Make yourself a player on a team of your choice. Pick a make your student number

your jersey number.

b$.$ Insert scores for some of the games in the initial data that did not already have

scores. Insert whatever scores you wish.

$6.(4$ Marks$)$ Design the following queries $(1$ Mark each$)$:

a$.$ List all players $($first and last name$)$ on the Winnipeg Jets who were born in USA.

b$.$ List all players $($first and last name, height, weight$)$ on the Vancouver Canucks in

order by height, shortest to tallest. In the case where players are of the same height

they must be ordered by weight.

c$.$ List the location and name of the home team for games where the Jets are the

visiting team and where the Jets have won the game. Add captions to the query

fields that read $$homeLocation$,$ and $$homeName$$ where appropriate

d$.$ List the teamId, name, and total number of goals by each team when they were the

visiting team, where the team has played at least $2$ games as the visitor $($Note: some

of the resulting rows might be $0/$have no data$).$

Submit your $.$accdb database file to Nexus before midnight on the due date. Late submissions will be

not be accepted