Updated 2018.03.14

Problem Description

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).

Project Requirements

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

3. Build test database and queries

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 (2% 1%).

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.

1.    Find 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 date. (4% 5%) Name this query 01GameList.

2.    Find 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.

3.    Find 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.

4.    Find 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.

5.    Find 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. (4% 5%) Name this query 05PlayerCareer.

Hand in a report that includes the following (all in a single Word document):

1. (3%) A printout of each of the above queries in SQL followed by a dump of the result of each of the queries on the test database (put the results below their corresponding query) and specify the parameter(s) that you used and display the results for that parameter value. There should be at least two tests of each query
2. A printout of the relationships in the database (Database Tools | Relationships). (1%)

Put the database file in the drop box.

4. Build forms and reports

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:

1. An example of each form and description of how the form is to be used.
2. A printout of each report.

Put the database file in the drop box.