A College Sports Information Director wishes to create a database of basketball box scores. Such a database will allow him to retrieve any historical information that can be retrieved by looking at box scores. Each college in the database has one team represented (the database will be either for men's basketball or women's basketball, but not both). A team has a nickname and a school name. The school name will not uniquely identify a team, but each school will be assigned a 4-letter (max) abbreviation that is unique (e.g. BCU, DORD, NWC). A team has many players. Teams (and obviously players) play many games. A box score is created for each game. A box score has a player name, position, field goals made and attempted, three point field goals made and attempted, free throws made and attempted, offensive and defensive rebounds, personal fouls, assists, turnovers, blocks, steals, and minutes played for each player who saw playing time. Every time a player plays a game, there is one box score entry associated with that player for that game. So if 10 players see action for Briar Cliff against Northwestern, there will be 10 box score entries for that game. If those same players see action in a game against Dordt, there will be 10 more box score entries for that game, giving each player 2 entries in the box score. (Remember that we are keeping track of all of the box score entries for a given team over a period of many seasons.) Each player has a name, unique ID number (not the jersey number), position, home and away jersey numbers, home city and high school, height, and weight. Each player is associated with a team. A game is uniquely identified by its date, home team, and visiting team. The data fields that are kept for each game are (in addition to the ones just given): time, location, attendance, referees, and comments.
The Sports Information Director needs to be able to do the following:
· Enter new teams, players, and games.
· Enter a box score for a game. Each box score will have many rows, one for each player who participated.
· Print schedule information.
· Print a box score for a game.
· Print box score summaries (one line per team) for a series of games.
· Print stats for a single player (all of the box score entries for each game the player has played).
The project is to be done individually. The database is to be implemented using Microsoft Access. The project will be done in 4 parts:
1. Create the conceptual design (see project overview)
2. Create the relational design (see project overview)
3. Build test database and queries
4. Build forms and reports
Build a test database in Access for the relational design
created in the part 2 of the project. Ensure that there is sufficient data
in the database to provide reasonable answers to test queries (
Design in SQL, and implement using Access, the following test queries. All queries must use parameters entered by the user. The fields that are underlined and red below represent the parameters in a query. Use the names given in bold for your queries.
all of the games between two dates
(including both dates) specified by the user. Input will be the two dates. This
query could be used to print a schedule for a season. Return date, time,
location, home team name (not ID), visiting team name (not ID) in order by
4% 5%) Name this query 01GameList.
all of the box score entries for both teams for a game specified by the user. Input will be the primary key
for a game. If there are 12 players per team, this would produce 24 lines. Sort
by team (so all players from the same team are together). Note that you do
NOT have to provide totals for each team for the game as would be in a normal
box score. (
4% 5%) Name this query 02GameBoxScore.
the box score totals for a given team between two dates
specified by the user (the box total is the team totals in each box score
category for both teams in the game. If there were 30 games, this report would
produce 60 lines, possibly with blank lines between each pair). Input will be
the team ID and the two dates. Give all statistics, including player name, and sort
by date. (
4% 5%) Name this query 03GamesBoxScore.
all of the box score entries for a given player between two dates
specified by the user. If there are 10 games that the player played between
those dates, this should produce 10 lines. Input will be the player ID and the
two dates. Give all statistics, including the player name, and sort by date.
4% 5% Name this query 04PlayerBoxScore.
career totals for a given player. Input
will be the player ID. This will be a single line with all of the player's
totals in each box score category, including the player name. (
Name this query 05PlayerCareer. Hand in a
report that includes the following (all in a single Word document):
Put the database file in the drop box.
Design, and implement using Access, two of the forms needed for the Basketball Database:
1. A form to allow a user to view a player, enter a new player, and to update an existing player's information. (6%) Name this form 01Player.
2. A form to allow a user to view a box score line, enter a new box score line, and to update an existing box score line. (6%) Name this form 02BoxScore.
Design, and implement using Access, the following reports:
1. A report of a single player's stats for every game between two dates specified by the user. This will consist of one line for each game played. (6%) Name this report 01SinglePlayer.
2. A report of an entire team's stats between two dates specified by the user. This will consist of one line for each player. (6%) Name this report 02PlayerTotals.
Hand in a Word document (hard
copy) which includes the following:
Put the database file in the drop box.