This table stores information of members. The DOB field determines which team the member belongs to. For members age greater than or equal to 18, he/she will join 1st Team or 2nd Team. For member's age less than 18, he/she will join a junior team. The junior teams are for four specific age groups and consist of players under the age of 18 years, 16 years, 14 years and 12 years on 1st September of the current season. For the STATUS column, it stores either Y or N while Y means active in the current season and N means in-active.
TEAM
This table stores information of teams including captain, vice-captain and coach of the team. For the CAPTAIN and VICE_CAPTAIN fields, they store the MEMBER_ID of the captain and vice-captain and are the forgien keys refer to table MEMBER. Similiarly, the field COACH is the forgien key refer to the COACH_ID of table COACH.
The TEAM table stores teams in different seasons with the field SEASON to distingish the season which a team belongs to.
The TYPE column specifies the team belongs to either 1st Team, 2nd Team, 1st Junior Team, 2nd Junior Team, 3rd Junior Team or 4th Junior Team.
TEAM_MEMBER
It is a intermediate table of table TEAM and table MEMBER. It identifies which member joins which team. Each player can only be registered with one team in each season. It is optional with reference to table MEMBER because a member can be social member and will not join any team.
MEMBER_FEE
This table stores schedule of membership fees in different seasons.
PAYMENT
This table stores payment records of members with reference to table MEMBER_FEE.
MATCH
This table stores matches information which involve the soccer club. Each match maps to a record in table TEAM.
COACH
This table stores information of coaches. Each team has a coach who is responsible for training and selecting the team and who also makes the arrangements for travelling to matches at other soccer clubs.
TRAINING
This table stores information of each training session. The club invites international players to lead the sessions, and a fee will be paid to him. Training sessions are open to non-members only. Those non-members taking part have to pay an entrance fee.
TRAINEE
This table stores trainee (non-members who join training sessions) information.
TRAINING_TRAINEE
This is an intermediate table between table TRAINING and table TRAINEE. It records which trainee takes part to which training session. This table also records the date when a trainee pays the entrance fee of a training session.
Task 3
The Entity Life History of a training session is shown below:
Task 4
The tables designed for the soccer club system with primary and foreign keys specified are shown below:
CREATE TABLE MEMBER (
MEMBER_ID INT UNSIGNED NOT NULL,
NAME VARCHAR(255),
DOB DATE,
TEL VARCHAR(255),
EMAIL VARCHAR(255),
ADDRESS VARCHAR(255),
STATUS CHAR(1) NOT NULL DEFAULT 'Y', -- Y / N
CREATE_DATE DATETIME,
PRIMARY KEY (MEMBER_ID)
);
CREATE TABLE MEMBER_FEE (
FEE_ID INT UNSIGNED NOT NULL,
SEASON INT(4) UNSIGNED NOT NULL,
AMOUNT INT UNSIGNED,
DESCRIPTION VARCHAR(255),
PRIMARY KEY (FEE_ID),
);
CREATE TABLE PAYMENT (
PAYMENT_ID INT UNSIGNED NOT NULL,
MEMBER_ID INT UNSIGNED NOT NULL,
FEE_ID INT UNSIGNED NOT NULL,
AMOUNT INT UNSIGNED,
METHOD VARCHAR(255),
PAYMENT_DATE DATETIME,
PRIMARY KEY (PAYMENT_ID),
FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (FEE_ID) REFERENCES MEMBER_FEE
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE COACH (
COACH_ID INT UNSIGNED NOT NULL,
NAME VARCHAR(255),
DOB DATE,
TEL VARCHAR(255),
EMAIL VARCHAR(255),
ADDRESS VARCHAR(255),
STATUS CHAR(1) NOT NULL DEFAULT 'Y', -- Y / N
CREATE_DATE DATETIME,
PRIMARY KEY (COACH_ID)
);
CREATE TABLE TEAM (
TEAM_ID INT UNSIGNED NOT NULL,
TYPE VARCHAR(255) NOT NULL,
CHECK (VALUE IN ('1ST TEAM','2ND TEAM','1ST JUNIOR','2ND JUNIOR','3RD JUNIOR','4TH JUNIOR')),
SEASON INT UNSIGNED NOT NULL,
CAPTAIN INT UNSIGNED, -- MEMBER_ID OF CAPTION
VICE_CAPTAIN INT UNSIGNED, -- MEMBER_ID OF VICE CAPTION
COACH_ID INT UNSIGNED,
PRIMARY KEY (TEAM_ID),
FOREIGN KEY (CAPTAIN) REFERENCES MEMBER(MEMBER_ID)
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (VICE_CAPTAIN) REFERENCES MEMBER(MEMBER_ID)
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (COACH_ID) REFERENCES COACH
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE TEAM_MEMBER (
TEAM_ID INT UNSIGNED NOT NULL,
MEMBER_ID INT UNSIGNED NOT NULL,
PRIMARY KEY (TEAM_ID, MEMBER_ID),
FOREIGN KEY (TEAM_ID) REFERENCES TEAM
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE MATCH (
MATCH_ID INT UNSIGNED NOT NULL,
SEASON INT UNSIGNED NOT NULL,
TEAM_ID INT UNSIGNED NOT NULL,
COMPETITOR VARCHAR(255),
TYPE VARCHAR(255),
CHECK (VALUE IN ('LEAGUE','CUP')),
MY_SCORE TINYINT,
COMPETITOR_SCORE TINYINT,
LOCATION VARCHAR(255),
MATCH_DATE DATETIME,
FINISHED CHAR(1) NOT NULL DEFAULT 'Y', -- Y / N
PRIMARY KEY (MATCH_ID)
);
CREATE TABLE TRAINING (
TRAINING_ID INT UNSIGNED NOT NULL,
INTERNATIONAL_PLAYER VARCHAR(255),
PLAYER_FEE INT UNSIGNED,
TRAINEE_QUOTA INT UNSIGNED,
ENTRANCE_FEE INT UNSIGNED,
TRAINING_DATE DATETIME,
PRIMARY KEY (TRAINING_ID)
);
CREATE TABLE TRAINEE (
TRAINEE_ID INT UNSIGNED NOT NULL,
NAME VARCHAR(255),
DOB DATE,
TEL VARCHAR(255),
EMAIL VARCHAR(255),
ADDRESS VARCHAR(255),
PRIMARY KEY (TRAINEE_ID)
);
CREATE TABLE TRAINING_TRAINEE (
TRAINING_ID INT UNSIGNED NOT NULL,
TRAINEE_ID INT UNSIGNED NOT NULL,
PAYMENT_DATE DATETIME,
PRIMARY KEY (TRAINING_ID, TRAINEE_ID),
FOREIGN KEY (TRAINING_ID) REFERENCES TRAINING
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (TRAINEE_ID) REFERENCES TRAINEE
ON DELETE NO ACTION ON UPDATE CASCADE
);
Task 4 (cont'd)
The Data Dictionary entries for all items included in the database design are listed below:
MEMBER　
Field
Data Type
Description
MEMBER_ID
INT
ID of member
NAME
VARCHAR(255)
Name of member
DOB
DATE
Date of birth of member
TEL
VARCHAR(255)
Tel of member
VARCHAR(255)
Email of member
ADDRESS
VARCHAR(255)
Address of member
STATUS
CHAR(1)
Status of member (Y | N)
CREATE_DATE
DATETIME
Record create date
MEMBER_FEE　
Field
Data Type
Description
FEE_ID
INT
ID of member fee
SEASON
INT(4)
Season of league
AMOUNT
INT
Amount of fee
DESCRIPTION
VARCHAR(255)
Fee description
PAYMENT　
Field
Data Type
Description
PAYMENT_ID
INT
ID of payment
MEMBER_ID
INT
ID of member
FEE_ID
INT
ID of member fee
AMOUNT
INT
Amount of fee
METHOD
VARCHAR(255)
Payment method description
PAYMENT_DATE
DATETIME
Date of payment
COACH　
Field
Data Type
Description
COACH_ID
INT
ID of coach
NAME
VARCHAR(255)
Name of coach
DOB
DATE
Date of birth of coach
TEL
VARCHAR(255)
Tel of coach
VARCHAR(255)
Email of coach
ADDRESS
VARCHAR(255)
Address of coach
STATUS
CHAR(1)
Status of coach (Y | N)
CREATE_DATE
DATETIME
Record create date
TEAM　
Field
Data Type
Description
TEAM_ID
INT
ID of team
TYPE
VARCHAR(255)
Type of team (1st Team | 2nd Team | 1st
Junior Team | 2nd Junior Team | 3rd Junior Team | 4th Junior Team)
SEASON
INT
Season of league
CAPTAIN
INT
Member ID of team captain
VICE_CAPTAIN
INT
Member ID of vice team captain
COACH_ID
INT
ID of coach of the team
MATCH　
Field
Data Type
Description
MATCH_ID
INT
ID of match
SEASON
INT
Season of league
TEAM_ID
INT
ID of team
COMPETITOR
VARCHAR(255)
Name of Competitor
TYPE
VARCHAR(255)
Type of match (LEAGUE | CUP)
MY_SCORE
TINYINT
Score of our team
COMPETITOR_SCORE
TINYINT
Score of competitor
LOCATION
VARCHAR(255)
Match location
MATCH_DATE
DATETIME
Date of match
FINISHED
CHAR(1)
Is finished (Y | N)
TRAINING　
Field
Data Type
Description
TRAINING_ID
INT
ID of training session
INTL_PLAYER
VARCHAR(255)
Name of international player invited
PLAYER_FEE
INT
Fee pay for international player
TRAINEE_QUOTA
INT
Maximum allowable no. of trainee joining the session.
ENTRANCE_FEE
INT
Entrance fee of training session
TRAINING_DATE
DATETIME
Date of training session
TRAINEE　
Field
Data Type
Description
TRAINEE_ID
INT
ID of trainee (non-member)
NAME
VARCHAR(255)
Name of trainee
DOB
DATE
Date of birth of trainee
TEL
VARCHAR(255)
Tel of trainee
VARCHAR(255)
Email of trainee
ADDRESS
VARCHAR(255)
Address of trainee
Task 5
The following is the screenshot of the user interface for adding a new member:
Figure 5.1 User Interface for Adding New Member
For validation rules, fields with * are mandatory. That means user must enter those fields at least. If user submits the form with blank mandatory fields, the following alert will be prompted:
Figure 5.2 Alert for Filling Mandatory Fields
Moreover, the format of date and email address will also be checked. If user entered an incorrect format for date of birth or an email address with improper format, the following alert will be prompted:
Figure 5.3 Alert for Incorrect Date and Email
Furthermore, once the member information is submitted, the system should check whether that member is already existed in the system. This can maintain data integrity and avoid data redundancy.
For creating a fixture list for a team, firstly, go to the fixture list as shown below:
Figure 5.4 Fixture List
Secondly, if user wants to add a match to the fixture list, he/she can click the "Add" button. A form for adding match information will be shown as below:
Figure 5.5 User Interface for Adding New Match
For validation rules in this form, fields with * are mandatory. Moreover, if user entered a date with incorrect format, or entered a non-integer value in score fields. The following alert will be prompted:
Figure 5.3 Alert for Incorrect Date and Score
In addition, once the form is submitted. The system should check whether a same record already existed in the system. If so, the system should prompt the user.
For recording a match result (to be done by team captains), it is similar to adding a match to fixture list. Firstly, user goes to fixture list and find the match. Then he/she clicks the "Edit" button of the match. The following page will be shown:
Figure 5.4 User Interface for Recording a Match Result
The score fields are mandatory and must be filled. If not, the following alert will be prompted:
Figure 5.5 Alert for In-completed Fields
Moreover, data type of the entered value will be checked. In this case, user must enter integer for the scores. Otherwise, the following alert will be prompted:
Figure 5.6 Alert for In-correct Data Type
The reasons for applying validation rules in the forms above are:
Avoid human mistakes;
Maintain data integrity;
Avoid data redundancy.
Task 6
For the training plan for the soccer club system, firstly we should identify the user of this system. They can be classified into following groups:
Staffs who register new member and collect the membership fee;
Coaches who responsible for selecting the team and also makes the arrangements for matching with other soccer clubs;
Team captains (or vice captains) who record results of matches involving the club;
Event Secretary with responsibility for organizing open training sessions, inviting international players to lead the session and determining and collecting the fees for such sessions. The Events Secretary also records the fees paid to international players and total amount of money collected in entrance fees at each event;
Since the above user groups handle different functions of the system, we should train them separately and with different training plans.
For user group 1, the number of staff in this group should be around 2 to 4. People who register as members may walk-in to the club, so the staffs should be familiar to the system and can handle the task quickly. In the training, we should demonstrate for the staffs in a test environment and let them to try it by themselves a few times. A quick-reference guide should also provide to them for easy recall.
For user group 2 - Coaches, as there are six teams and each team has a coach, the number of user in this group is around 6. Mainly we have to train them to select the teams and create fixture list for each team. We could perform a formal lecture with projector showing the process and the system. This user group would properly enter data once per season only, so a comprehensive user manual should be provided for the users for long term.
For user group 3 - Team Captains, the number of user in this group is around 6. Their responsibilities are to enter result of each match to the system only. And new captains would be elected each season. We could consider training them together by short informal lecture.
Finally for user group 4 - Event Secretary, the number of user in this group is 1 to 2. Their job is mainly deal with open training sessions. They have to create record of training session in the system. When there are non-members registering a training session, they have to create non-members' record. Also they have to update the status of training session frequently. We could provide one to one or one to two formal discussion to introduce the system, and also provide a comprehensive user guide for their reference.
User Guide
Login
Once you enter the system, enter your username and password to login. If you do not sure your login information, contact your system administrator.
After login, you will see the sections which you can enter.
If you do not perform any action in the system for 20 minutes. You login session will be expired and you will logout automatically. In this case, login to the system again.
After you finished your work, click the "Logout" button to logout.
Member Registration
Click "Member" link from the menu. A member list will be shown.
To create a new member:
Click "Add" button.
Enter member's information in the form.
According to the age of the member, select a proper fee option.
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
To edit an existing member information:
Enter the member name or tel. no in the search box of the member list.
Click "Search" button. The member list will show the member record.
Click "Edit" button next to the member record.
Edit member's information in the form.
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
To re-new membership:
Enter the member name or tel. no in the search box of the member list.
Click "Search" button. The member list will show the member record.
Click "Edit" button next to the member record.
Click "Re-newal" button in the form.
According to the age of the member, select a proper fee option.
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
To delete a member:
Usually you should not delete an existing member, if you enter a member wrongly:
Enter the member name or tel. no in the search box of the member list.
Click "Search" button. The member list will show the member record.
Click "Edit" button next to the member record.
Click "Delete" button in the form.
The member will be deleted.
Coach Creation
Click "Coach" link from the menu. A coach list will be shown.
To create a new coach:
Click "Add" button.
Enter coach's information in the form.
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
To edit an existing coach information:
Enter the coach name or tel. no in the search box of the coach list.
Click "Search" button. The coach list will show the coach record.
Click "Edit" button next to the coach record.
Edit coach's information in the form.
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
To disable a coach:
Enter the coach name or tel. no in the search box of the coach list.
Click "Search" button. The coach list will show the coach record.
Click "Edit" button next to the coach record.
Select the status to "In-Active".
Click "Save" to submit the form.
Team Creation
Click "Team" link from the menu. A team list will be shown.
To create a team:
Click "Add" button.
Select type of the team. (1st Team / 2nd Team / 1st Junior Team /…)
Select coach of the team from the coach pull down menu. (Coach already assigned to another team will not be shown in the pull down menu. In this case, you have to remove the coach from other team first.)
Select captain and vice-captain of the team from the member pull down menu.
Select members of the team from the member pull down menu. (Members already joint another team will not be shown in the pull down menu. In this case, you have to remove the member from other team first.)
Click "Save" to submit the form.
To edit a team:
Click "Edit" button next to the team in the team list.
To remove the coach from the team, click the "Remove" checkbox next to the coach.
To remove a member from the team, click the "Remove" checkbox next to the member.
Select members of the team from the member pull down menu. (Members already joint another team will not be shown in the pull down menu. In this case, you have to remove the member from other team first.)
Click "Save" to submit the form.
Fixture List
Click "Fixture List" from the menu. A fixture list will be shown.
To add a match to the fixture list:
Click "Add" button.
Select one of the team from the team pull down menu.
Enter the rest information in the form.
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
To edit a match:
Click "Edit" button next to the match record in the fixture list.
Edit the match information.
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
Match Result
Click "Fixture List" from the menu. A fixture list will be shown.
To enter / edit result of a match:
Click "Edit" button next to the match record in the fixture list.
Enter / Edit the match result
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
Training Session
Click "Training Session" from the menu. A training list will be shown.
To create a training session:
Click "Add" button
Enter the information of the training session.
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
To edit a training session:
Click "Edit" button next to the training record in the training list.
Edit the information
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
Non-member (Trainee) Registration
Click "Trainee" from the menu. A trainee list will be shown.
To create a new trainee:
Click "Add" button.
Select a training session from the training pull down menu.
Enter trainee's information in the form.
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
To edit a trainee:
Enter the trainee name or tel. no in the search box of the trainee list.
Click "Search" button. The trainee list will show the trainee record.
Click "Edit" button next to the trainee record.
Select another training session from the training pull down menu if necessary.
Edit trainee's information in the form.
Click "Save" to submit the form.
If alert box is prompted after you submit the form, follow the instruction(s) and correct the form. Then submit the form again.
===== User Guide End =====
Task 7
In this project, we have make the following assumptions during analysing, designing and implementing the system.
Assumptions:
Member registration fee of each member must be paid once and for all. No instalment or discount are allowed.
Every user should have their own login account and have different privileges to access to different part of the system.
Entrance fee of each training session is unique.
Number of member in a team should be limited.
Number of non-member joining a training session should be limited.
Only one international player will be invited for each training season.
Each player can only be registered with one team in each season.
Only matches involving Townsville Kicks soccer club will be recorded in the system.
None of the club's teams play friendly matches.
Changes which can improve our work:
According to the scenario, each season only has six teams and members join specific teams according to their ages. There may be a case a team has too many members. We better suggest providing a member quota for each team.
In this project, we do not provide implementation plan and testing plan. It would be more complete if we provide one.
No actual data are provided. We cannot show the relationship of the entities clearly.
