Background

As an avid basketball fan (who isn't?), I'd like to create a database to keep track of teams' games

in the NBA. In every game in the NBA, there are two teams. One team is the home team, and the

other is the away team. Usually, but not always, games are played in the home team's stadium.

There are quite a few attributes to the composition of a basketball team. These include their

unique name, location, total salary, year founded, management, and history.

Games are played in stadiums. Usually, a basketball game is played in the home team's stadium,

but not always. There may be NBA exhibition games or games played outside the US. Even in

these games, one team is the home team. A stadium has several crucial attributes, including its

unique name, location, building cost, age, ownership, and capacity.

Games are played in stadiums. There are 2 NBA teams at each tournament.

[1]

DAT 560G-Fall 2023-Assignment #1

Database

Develop the E/R diagram for a database for NBA games. For now, we'll focus on only a few aspects

of the database. Later parts of this assignment will expand the database. The database should

incorporate information about the teams, stadiums, and games.

The database contains information about each team's unique name, location, and total salary

and adds two more attributes.

Information about stadiums includes the unique name, capacity, year built, and add 2 more

attributes.

Stadiums are either dedicated only to NBA games or may have a different sport played in them.

For example, many basketball stadiums also host hockey games. Only for those dedicated to the

NBA, the database includes information about the price of nachos and drinks. For those that are

dual use (and have another sport), the database maintains information about the other sport,

the capacity for the other sport, and the average ticket price for the other sport.

A game is played in a stadium and has two teams. The home team and the away team. For each

match, the database maintains information about the date of the game, teams, and each team's

score. The same pair of teams may play each other more than once./nQuestion 1: [60 points]

Design the corresponding E/ER diagram. Remember to include arrows representing cardinality

where relevant. Underline key attributes and identify foreign keys. Note: think about how to

require both a home team and an away team in the E/R diagram.

i. Also, convert the diagram to relations. In other words, show the tables used in the

database. You do not need to include data, only the structure of the tables.

ii. Suggest three queries that you could ask of this database.

iii. Suggest 1 query that requires additional attributes. Which attributes are needed to answer

this further query?

Question 2: [20 points]

Modify the database to include information for each entrance to each stadium. A stadium likely

has multiple entries. The database only maintains the name of the gate. ALSO, add the

appropriate relation.

Question 3: [20 points]

[2]

DAT 560G-Fall 2023 - Assignment #1

Modify the database to include information about the players on each team. A team has several

players. A player plays on only one team. Information about each player includes their number,

salary, and years of playing.

Also, add the proper relation.

Now include cardinality for pairs of relations that you

added.