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.
Fig: 1
Fig: 2