CREATE MICROSOFT ACCESS DATABASE BY FOLLOWING ALL THE STEPS The CMHA is a non-pr

Question

CREATE MICROSOFT ACCESS DATABASE BY FOLLOWING ALL THE STEPS

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

Explanation / Answer

Create Tables queries for all tables in Microsoft access

1)Table for arena:

CREATE TABLE arena(
arenaid int NOT NULL PRIMARY KEY,
    name varchar(255) NOT NULL,
    address varchar(255),
    city varchar(32),postalCode int, phone text,email varchar(255));

Table For Coach:

CREATE TABLE coach(
coachId int NOT NULL PRIMARY KEY,
lastName varchar(255) NOT NULL,
firstNach varchar(255) NOT NULL,
phone text,email varchar(255));

Table for Team:

CREATE TABLE team(
teamId int NOT NULL PRIMARY KEY,
    name varchar(255) NOT NULL,
    status varchar(25),
    designationId int , levelId int, coachId int);

Table for level

CREATE TABLE level(levelId int NOT NULL PRIMARY KEY,

name varchar(255) NOT NULL,, minAge int, maxAge int)

Table for designation:

CREATE TABLE designation(designationId int NOT NULL PRIMARY KEY, designationName

varchar(255) NOT NULL);

Table For player:

CREATE TABLE player(playerId int NOT NULL PRIMARY KEY, firstName varchar(255) NOT NULL, lastName varchar(255), dateOfBirth DATE(), teamId int);

Table For game:

CREATE TABLE game(homeTeamId int NOT NULL PRIMARY KEY, awayTeamId int, date DATE(), time TIME(), arenaId int , homeScore int, awayScore int);

1)a query of Coaches not associated with a Team

select distinct C.* from coach C left join team T on C.coachId=T.coachId
where C.coachId not in(select coachId from team);

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

select T.name,P.playerId,P.firstName,P.lastName from team T left join player P on T.teamId=P.teamId
where teamId=123;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.