CMHA - Conestoga Minor Hockey Association

MICROSOFT ACCESS DATABASE

After successfully completing this assignment, you will be able to:

Create tables within a database

Establish relationships between tables

Define fields and their properties within a table

Establish input masks and data validation for fields

Determine if a field is required to be entered

Create queries

Create forms

The CMHA is a non-profit organization that creates teams to play against each other. This takes a number of hours for a volunteer to coordinate. The volunteers were hoping that we could computerize this.

The system must be able to handle:

teams can be Active or Inactive

team designated as House, Rep, Allstar AA, Allstar AAA

teams designated according to level: 1 = Novice, 2 = Midget, 3 = Bantam, 4 = PeeWee

teams have names (Krouts, Kobra, Kombines.....)

players belong to teams

a player can only play for 1 team at any time

keep track of the game schedule

there are many arenas in Kitchener

1 coach per team

1 coach can have many teams

No two teams can play each other more than once a day

Opening switchboard form (buttons on main form which activate other forms and queries

1. Develop the tables and relationship specifications for CHMA

The following list represents possible tables supporting the project. Underlined attributes represent the primary keys.

arena(arenaId, name, address, city, postalCode, phone, eMail)

coach(coachId, lastName, firstName, phone, eMail)

team(teamId, name, status, designationId, levelId, coachId)

level(levelId, name, minAge, maxAge)

designation(designationId, designationName)

player(playerId, firstName, lastName, dateOfBirth, teamId)

game(homeTeamId, awayTeamId, date, time, arenaId, homeScore, awayScore)

2. Include the appropriate validation rules and input masks that are apparent. The following must be included, but you may add more

teams designated as House, Rep, Allstar AA, Allstar AAA

ensure that the birth date is not in the future or more than 20 years in the past

use input mask for data uniformity (names, city, postal code)

a team can not play itself

all dates should allow and display 4 digit years

teams designated according to level:

1 = Pre-Novice (ages 1 to 6)

2 = Novice (ages 7 to 8)

3 = Atom (ages 9 to 10)

4 = PeeWee (ages 11 to 12)

5 = Bantam (ages 13 to 14)

6 = Midget (ages 15 to 17)

3. Develop the following queries

a query of Coaches not associated with a Team

a query of a specific team (parameter query by TeamName), showing Team Name and Full Name of all Players

a query of Games Played. Sort this by date of Game in ascending order, showing Team Names, Game Date, Winning team name (blank if tie), Winner Score, Loser Score and the spread as a positive number.

4. Develop the following forms.

All forms should have a common background colour, no dividing lines, no record selector and a descriptive title in large text in the form header area.

a)A form for Teams that allows the user to add teams. Use a check box for Active, an option group for Level, and a combo box for Designation and for Coach (display the coaches full name).

b)A form for Players that allows the user to add new Players. The form should contain all Player data. Do not allow deletion of Players on this form. Teams would be a combo box of Active teams.

c)A form to enter each game data - include boxes for date, time, scores, combo boxes of Active teams and list box for arenas.

d)Create a switchboard Main Menu to access all Forms and Queries.

5. Add records to the database as follows

level 6 records (as listed above)

designation 4 records

team 6 records (ensure that 1 team is not active)

coach 4 records (ensure that 1 coach does not coach any teams)

player 10 records distributed amongst teams

arena 4 records

game 5 records