System Analysis And Design Soccer Club Database Computer Science Essay
The aim of this project is to design and implement the database application to replace the current paper system of the Soccer Club. This is the objective of system. The committee of Soccer club wants to use this system for member registration, fee payment, creation of matches list, set up the training sessions, publishes the result of each game. The coaches want to use this system to allocation of team.
c) A Data Flow Diagram for the system including: (12 marks)
• Process descriptions
1. Register Membership, when public want to join the membership, the committee of Soccer club will provide the application from to public, and check the application from is complete or not. And verified their ID and contact address. If the information is correct. Then will collect the membership fee and create the membership card to the member. And the information of the member will keep in the ‘member’ table table.
2. Team allocation, the coach will get the playing member’s information from the ‘member’ table table. And the coach will select the compatible member into the on game list. This is use to join the game of League and Cup. And also will update the information of the team to the ‘Team’ table table. For example select the captain of the team.
3. Plan Training Session, the Soccer club have hold a number of training sessions, the training session is lead by international player. When the committee of Soccer club setting up the training sessions, they need to connect with international player for lead the session. And the international player needs to confirm to lead the sessions. The data of international player is store in ‘international player’ table table and the data of training session is store in ‘Training’ table table.
4. Create fixture lists, when the Soccer club joins the game of league competition and cup competition. The league competition and cup competition will collect other 15 teams and provide the fixture list to the Soccer club. The committee of Soccer club need to publish this fixture list to the public, let them know the home team, away team, match address, match day and time. This data will store in ‘Fixture list and result’ table. In this fixture list we also will know which team will join the league and cup. Other Soccer team’s information will store in ‘Other teams’ table table.
5. Publish result, all the result of the matches, will be provide from the league competition and cup competition. The result of Soccer club and other teams also need to collect. This data will store in the ‘result’ table table, they will publish the result to the public.
• Data store contents
1. Member Detail, This is the table of member’s information. It include the field: Member_ID, Name, Date_of_Birth, Membership_type, Phone, Address, Email, user_ID, Team_ID, On_game_list, Committee_ID
2. Team Detail, This is the table o f Soccer Cup’s Team. It include the field: Team_ID, Coach_ID, Captain_Member_ID, Vice_Captain_Member_ID, Team_Name, Member_ID
3. International Player Detail: This is the table of international player’s information, this table include the field: Player_ID, Phone, Address, Email, Salary, user_ID
4. Training Detail, this is the table of Training’s information, it include this field: Session_ID, Name, Player_ID, Player_Name, session_open_day, Address, time, total_hour, Session_Fee, Committee_ID
5. Other_Teams Detail, this is the table of other team’s information which team are join the match with Soccer club. This table include the field: Other_team_ID, Name
6. Fixture Lists and result Detail, this is table of the match list and the result of the match. This table include the field: Match_ID, Team_ID, Other_team_ID, Match_address, Date, Time, Team_score, Other_team_score
• External entity definitions
1. Member of Public, this is the people who join the Soccer club membership and they have been pay the membership fee every year. They can be playing members or simply social members.
2. Coach and International player, this is the people who responsible for training and selecting the team. And they also make the arrangements for travelling to matches at other Soccer club. And the international player is the trainer of open training session.
3. Committee, this is the people who responsible for organizing open training sessions, inviting international players to lead the sessions, advertising the training sessions and determining and collecting the fee for such sessions. And they also need to collect and publish the matches result.
4. League and cup, this is the local league competition and local cup competition, the Soccer join the match by this two competitions. They provide the matches result and the fixture lists to the Soccer club.
Task 2 – Create a data model detailing the data structure required to support the information and process requirements of the soccer club system including:
a) An Entity Relationship Diagram for the system. (6 marks)
b) Entity descriptions for all entities in the diagram. (4 marks)
1. User
2. Coach
3. Member
4. Team
5. Position
6. Committee
7. Other team
8. Training
9. Fixture List and Result
10. International Player
11. Training Student
12. Student
c) Appropriate attributes for all entities including primary and foreign keys. (5 marks)
1. Entity: User
Object Type: Relation
Description: This is a table for the User login ID and password. The user of this system will use the information of this table to login.
Attributes: User_ID, User_Login, Password, Group
Primary Key: User_ID
2. Entity: Coach
Object Type: Relation
Description: The coach’s personal information which system need are store in this table.
Attribute: Coach_ID, Name, Address, Phone, Email, User_ID
Primary Key: Coach_ID
Foreign Key: User_ID references to ‘User’ table.
3. Entity: Member
Object Type: Relation
Description: This is a table to store member’s record.
Attribute: Member_ID, Name, ID_card_number, Date_of_birth, Membership_type, Phone, Address, Email, User_ID, On_game_list
Primary Key: Member_ID
Foreign Key: User_ID references to ‘User’ table table.
Foreign Key: Committee_ID references to ‘Committee’ table
4. Entity: Team
Object Type: Relation
Description: This is a table to store Soccer club player team’s record.
Attribute: Team_ID, Coach_ID, Captain_Member_ID, Vice_Captain_Member_ID, Team_Name, Member_ID
Primary Key: Team_ID
Foreign Key: Coach_ID references to ‘Coach’ table.
Foreign Key: Member_ID references to ‘Member’ table.
5. Entity: Position
Object Type: Relation
Description: This is a table to store the position of committee’s record.
Attribute: Position_ID, Position_name
Primary Key: Position_ID
6. Entity: Committee
Object Type: Relation
Description: This is a table to store the committee record.
Attribute: Committee_ID, Team_ID, Position_ID
Primary Key: Committee_ID
Foreign Key: Team_ID reference to ‘Team’ table.
Foreign Key: Position_ID reference to ‘Position’ table.
7. Entity: Other Team
Object Type: Relation
Description: This is a table to store the record of other Soccer team which will match with Soccer Club’s team.
Attribute: Other_team_ID, Name
Primary Key: Other_team_ID
8. Entity: Training
Object Type: Relation
Description: This is a table to store the open training session’s record.
Attribute: Session_ID, Name, Player_ID, Player_Name, Session_open_day, Address, Time, Total_hour, Session_Fee, Committee_ID
Primary Key: Session_ID
Foreign Key: Player_ID references to ‘International Player’.
Foreign Key: Committee_ID references to ‘Committee’.
9. Entity: Fixture Lists and Result
Object Type: Relation
Description: This is a table to store the record of fixture Lists and the match result.
Attribute: Match_ID, Team_ID, Other_team_ID, Match_address, Date, Time, Team_score, Other_team_score
Primary Key: Match_ID
Foreign Key: Team_ID references to ‘Team’ table.
Foreign Key: Other_team_ID references to ‘Other team’ table.
10. Entity: International Player
Object Type: Relation
Description: This is a table to store the record of international player. The international player is the trainer of open training session.
Attribute: Player_ID, Phone, Address, Email, Salary, User_ID
Primary Key: Player_ID
Foreign Key: User_ID references to ‘User’ table.
11. Entity: Training Student
Object Type: Relation
Description: This is the link entity of ‘Training’ table and ‘Student’ table. Because one student can join many training session and one training session can have many students. So we need to use link entity to resolve this many to many relationship.
Attribute: Training_studnet_ID, Session_ID, ID_card_Number
Primary key: Traininig_student_ID
Foreign Key: Session_ID references to ‘Training’ table.
Foreign Key: ID_card_number references to ‘Student’ table.
12. Entity: Student
Object Type: Relation
Description: This is a table to store the record of student.
Attribute: ID_card_number, Name, Phone, Address, Email
Primary Key: ID_card_number
d) Relationships detailing optionality and degree of relationships between entities. (5 marks)
1. Relationship ID: R1
Name: ‘Coach’ table to ‘User’ table
Object Type: Relationship
Description: Each coach has only one unique User_ID
Cardinality: one to one,
‘Coach’ table is the one side. One User_ID only belong to one coach.
‘User’ table also is the one side. One coach only has one unique User_ID.
Optional: ‘Coach’ table is the optional side. A user may be not a coach.
‘User’ table is the mandatory side. A coach must be a user.
2. Relationship ID: R2
Name: ‘International Player’ table to ‘User’ table
Object Type: Relationship
Description: Each international player has one unique User_ID
Cardinality: one to one
‘International Player’ table is the one side. One User_ID only can belong to one international player.
‘User’ table also is the one side. One international player only has one User_ID.
Optional: ‘International Player’ table is the optional side. A user may be not a international player.
‘User’ table is the mandatory side. A international player must be a user.
3. Relationship ID: R3
Name: ‘Member’ table to ‘User’ table
Object Type: Relationship
Description: Each member has one unique user_ID.
Cardinality: one to one
‘Member’ table is the one side. One User_ID only belong to one member.
‘User’ table is the one side. One member only can have one User_ID.
Optional: ‘Member’ table is the optional side. A use may be not a member.
‘User’ table is the mandatory side. A member must be a user.
4. Relationship ID: R4
Name: ‘Member’ table to ‘Team’ table
Object Type: Relationship
Description: Member belongs to Ruby Club’s team.
Cardinality: many to one
‘Member’ table is the many side. One team can have many members.
‘Team’ table is the one side. One member only can belong one team.
Optional: ‘Member’ table is the optional side. A team may be not has any member.
‘Team’ table is the optional side. A member may be not belongs to any team.
5. Relationship ID: R5
Name: ‘Team’ table to ‘Coach’ table
Object Type: Relationship
Description: Each team must have a coach.
Cardinality: one to one
‘Team’ table is the one side. One coach only can belong one team.
‘Coach’ table is the one side. One team only can have one coach.
Optional: ‘Team’ table is the optional side. A coach can have any team to lead.
‘Coach’ table is the mandatory side. A team must have a coach.
6. Relationship ID: R6
Name: ‘Member’ table to ‘Committee’ table
Object Type: Relationship
Description: Some member is the committee of Soccer Club.
Cardinality: many to one
‘Member’ table is the many side. One committee position can belong to many members.
‘Committee’ table is the one side. One member only can have one committee position.
Optional: ‘Member’ table is the mandatory side. A committee position must belong to member.
‘Committee’ table is the optional side. A member may be not have a committee position.
7. Relationship ID: R7
Name: ‘Position’ table to ‘Committee’ table
Object Type: Relationship
Description: Each committee member of Soccer Club has a position.
Cardinality: one to many
‘Position’ table is the one side. One committee members only can have one position.
‘Committee’ table is the many side. One position only can belong to many committee members.
Optional: ‘Position’ table is the mandatory side. A committee must have a position.
‘Committee’ table is the optional side. A position may be not belongs to any committee member.
8. Relationship ID: R8
Name: ‘Team’ table to ‘Committee’ table
Object Type: Relationship
Description: Every committee member belongs to their team of Soccer Club.
Cardinality: one to many
‘Team’ table is the one side. One committee member only belong one team.
‘Committee’ table is the many side. One team can have many committee members.
Optional: ‘Team’ table is the mandatory side. Every committee member must belong to a team.
‘Committee’ table is the optional side. A team may be not has any committee member.
9. Relationship ID: R9
Name: ‘Team’ table to ‘Fixture List and Result’ table
Object Type: Relationship
Description: The team of Soccer Club will have match will other team.
Cardinality: one to many
‘Team’ table is the one side. One match only can join with one Soccer club team and one other team.
‘Fixture List and Result’ table is the many side. One team can has many matches to play.
Optional: ‘Team’ table is the mandatory side. The match must have Soccer club’s team.
‘Fixture List and Result’ table is optional side. A team may be not has any match.
10. Relationship ID: R10
Name: ‘Other Team’ table to ‘Fixture List and Result’ table
Object Type: Relationship
Description: Some member is the committee of Soccer Club.
Cardinality: one to many
‘Other Team’ table is the one side. One match only can join with one Soccer club team and one other team.
‘Fixture List and Result’ table is the many side. One other team can have many matches to play.
Optional: ‘Team’ table is the mandatory side. The match must have Soccer club’s team.
‘Fixture List and Result’ table is the optional side. A team may be not has any match.
11. Relationship ID:R11
Name: ‘Committee’ table to ‘Training’ table
Object Type: Relationship
Description: Committee member with responsibility for this open training session.
Cardinality: many to one
‘Committee’ table is the one side. One session will lead by one committee member.
‘Training’ table is the many side. One committee member can lead many training session.
Optional: ‘Committee’ table is the mandatory side. The training session must lead by committee member and international player.
‘Training’ table is the optional side. A committee member may be not lead any open training session.
12. Relationship ID:R12
Name: ‘International Player’ table to ‘Training’ table
Object Type: Relationship
Description: International player with responsibility for this open training session.
Cardinality: many to one
‘International Player’ table is the one side. One session will lead by one international player.
‘Training’ table is the many side. One international player can lead many training session.
Optional: ‘Committee’ table is the mandatory side. The training session must lead by committee member and international player.
‘Training’ table is the optional side. A international player may be not lead any open training session.
13. Relationship ID:R13
Name: ‘Training Student’ table to ‘Training’ table
Object Type: Relationship
Description: It is a link entity to solve the many to many relationship of ‘Training’ table and ‘Student’ table. It break it to two ‘one to many’ relationships.
Cardinality: many to one
‘Training Student’ table is the many side.
‘Training’ table is one the side.
Optional: ‘Training Student’ table is the mandatory side.
‘Training’ table is the mandatory side.
14. Relationship ID:R14
Name: ‘Training Student’ table to ‘Student’ table
Object Type: Relationship
Description: It is a link entity to solve the ‘many to many’ relationship of ‘Training’ table and ‘Student’ table. It break to two ‘one to many’ relationships.
Cardinality: many to one
‘Training Student’ table is the many side.
‘Student’ table is the one side.
Optional: ‘Training Student’ table is the mandatory side. The student must take training session, if not take the training session, only is public not student.
‘Student’ table is the mandatory side.
Task 3 – Create an Entity Life History for a training session.
First is ‘Join Member’ stage, then ‘Member Amendment’ is the second stage, this stage can be repeat. And under this stage have three options, ‘Change Membership Type’, ‘Change Personal’ table and ‘Do nothing’. Finish this stage will become to ‘Membership Overdue’ stage. This is the third stage of member. It has two options, ‘Pay membership fee’ and ‘Resign Member’. If choose ‘Pay member fee’, then will back to the ‘Member Amendment’, and if choose ‘Resign Member’, then will become the last stage of member ‘Delete Member’. This is the end of this entity.
Task 4 – Create a database design for the soccer club system including:
a) A set of tables identifying primary and foreign keys.(10 marks)
User (User_ID, password, Group)
Coach (Coach_ID, Name, Address, Phone, Email, User_ID)
Team (Team_ID, Coach_ID, Captain_Member_ID, Vice_Captain_Member_ID, Team Name)
Member (Member_ID, Name, ID_card_num,ber, Date_of_birth, Membership_Type, Phone, Address, Email, User_ID, On_game_list, Committee_ID, Team_ID)
Position (Position_ID, Position_name)
Committee (Committee_ID, Team_ID, Position_ID)
Other team (Other_team_ID, Name)
Fixture List and Result (Match_ID, Team_ID, Other_team_ID, Match_address, Date, Time, Team_score, Other_team_score)
Training (Session_ID, Session_Name, Player_ID, Session_open_day, Address, Time,
Total_hour, Session_Fee, Committee_ID)
International Player (Player_ID, Player_Name, Phone, Address, Email, Salary, User_ID)
Training Student (Training_student_ID, Session_ID, ID_card_number)
Student (ID_card_number, Name, Phone, Address, Email)
b) Data Dictionary entries for all items included in the database design. (10 marks)
Table Name: User Table Type: Entity Description: It contains user’s detail. This is a table that includes all user’s password and login name.
Data Field
Name
Format
Key
User_ID
CHAR(10)
Primary Key
User_LOGIN
VARCHAR(20)
Password
VARCHAR(15)
Group
VARCHAR(15)
Table Name: Coach Table Type: Entity Description: It contains coach’s detail. This is a table that includes the personal information of coach.
Data Field
Name
Format
Key
Coach_ID
CHAR(10)
Primary Key
Name
VARCHAR(50)
Address
VARCHAR(50)
Phone
CHAR(8)
VARCHAR(30
User_ID
CHAR(10)
Foreign Key reference ‘User’ table
Table Name: Member Table Type: Entity Description: It contains member’s detail. This is a table that includes the member’s membership type, belong to which team and their personal information.
Data Field
Name
Format
Key
Member_ID
CHAR(10)
Primary Key
Name
VARCHAR(50)
ID_card_number
CHAR(8)
Date_of_birth
DATE
Membership_type
CHAR(1)
Phone
CHAR(8)
Address
VARCHAR(50)
VARCHAR(30
User_ID
CHAR(10)
Foreign Key reference ‘User’ table.
On_game_list
CHAR(1)
Committee_ID
CHAR(10)
Foreign Key reference ‘Committee’ table.
Team_ID
CHAR(10)
Foreign Key reference ‘Team’ table.
Table Name: Team Table Type: Entity Description: It contains team’s detail. This is a table that includes the name of team, the coach of team, the captain and the vice captain of the team.
Data Field
Name
Format
Key
Team_ID
CHAR(10)
Primary Key
Coach_ID
CHAR(10)
Foreign Key reference ‘Coach’ table.
Captain_Member_ID
CHAR(10)
Vice_Captain_Member_ID
CHAR(10)
Team_Name
VARCHAR(50)
Table Name: Position Table Type: Entity Description: It contains position name and unique identifier..
Data Field
Name
Format
Key
Position_ID
CHAR(10)
Primary Key
Position_Name
VARCHAR(50)
Table Name: Committee Table Type: Entity Description: It contains committee detail. This is a table that includes the information of the committee is which position of which team.
Data Field
Name
Format
Key
Committee_ID
CHAR(10)
Primary Key
Team_ID
CHAR(10)
Foreign Key reference ‘Team’ table.
Position_ID
CHAR(10)
Foreign Key reference ‘Position’ table.
Table Name: Other Team Table Type: Entity Description: It contains other team detail. The other team is the team which will match with Soccer club’s team. This table includes their name and unique identifier.
Data Field
Name
Format
Key
Other_team_ID
CHAR(10)
Primary Key
Name
VARCHAR(50)
Table Name: Training Table Type: Entity Description: It contains open training session’s detail. It includes the date, time and the trainer.
Data Field
Name
Format
Key
Session_ID
CHAR(10)
Primary Key
Name
VARCHAR(50)
Foreign Key reference ‘Coach’ table.
Player_ID
CHAR(10)
Foreign Key reference ‘International Player’ table
Session_open_day
DATE
Address
VARCHAR(100)
Time
TIME
Total_hour
CHAR(2)
Session_Fee
DECIMAL
Committee_ID
CHAR(10)
Table Name: Fixture List and Result Table Type: Entity Description: It contains the fixture list and match result detail.
Data Field
Name
Format
Key
Match_ID
CHAR(10)
Primary Key
Team_ID
CHAR(10)
Foreign Key reference ‘Coach’ table.
Other_team_ID
CHAR(10)
Foreign Key reference ‘International Player’ table
Session_open_day
DATE
Address
VARCHAR(100)
Time
TIME
Total_hour
CHAR(2)
Session_Fee
DECIMAL
Committee_ID
CHAR(10)
Table Name: International Player Table Type: Entity Description: It contains the international player (open training session coach) detail. It includes their name, salary and personal contact.
Data Field
Name
Format
Key
Player_ID
CHAR(10)
Primary Key
Name
VARCHAR(50)
Phone
CHAR(8)
Address
VARCHAR(100)
VARCHAR(50)
Salary
DECIMAL
User_ID
CHAR(10)
Foreign Key reference ‘User’ table.
Table Name: Training Student Table Type: Entity Description: It contains link entity of ‘Training’ and ‘Student’ tables.
Data Field
Name
Format
Key
Training_student_ID
CHAR(20)
Primary Key
Session_ID
CHAR(10)
Foreign Key reference ‘Training’ table.
Student_ID
CHAR(10)
Foreign Key reference ‘Student’ table
Table Name: Student Table Type: Entity Description: It contains the open training session’s student detail. This includes the ID card, name and contact of student.
Data Field
Name
Format
Key
ID_card_number
CHAR(8)
Primary Key
Name
VARCHAR(50)
Phone
CHAR(8)
Address
VARCHAR(100)
VARCHAR(50)
Task 5 – Create a prototype user interface for the soccer club system including, as a minimum, the following functions:
a) Adding a new member. (5 marks)
b) Creating a fixture list for a team. (5 marks)
c) Recording a match result. (5 marks)
Task 6 – You are responsible for organising the initial training of the system users and producing the User Guide for the new system.
Create an outline training plan for the new system including who would be trained, how the session(s) would be organised (e.g. all together, small groups, etc.) (8 marks)
This is the training plan of the Soccer club system, the objective is want the user understand more about the system, and let them user this system smoothly.
We have three user groups need to training, ‘Public’, ‘Coach’ and ‘Committee’ are our target. The public user group we want they know how to use this system to download application form, view the fixture list and view the result of match. The coach user group we want them understand how to allocate team. And the committee user group we want them understand how to create new member, edit member detail, create fixture list, update fixture list, create match result and update match result.
We will give training session to each user group and give user guide with print screens to them. And need a computer room let them use the system at the training session.
For the public user group we need about 30 minutes to training. The coach user group we need about 60 minutes to training. And the committee user we need about 2 hours to training
Group
User
Skill
Material
Public
Member and public
Download application from
View fixture list
View match result
Public user guide
Coach
Team coach
Allocation team
Coach user guide
Committee
Committee member
Create new member
Update member detail
Create fixture list
Update fixture list
Create match result
Update match result
Committee user guide
• Create a User Guide for the system. This should not be a comprehensive system manual but a reference document that users can use as a quick guide to the tasks they need to carry out. (10 marks)
Task 7 – Create a comprehensive, professional standard report describing your system design for the soccer club. This should include the details of tasks 1 – 6 including the User Guide.
Reference
[1] Colin Bentley, 2002, “NCC Education – Managing Business Projects”, second edition, Galatea Training Services Limited, Singapore
[2] Mark Brown, 2002, “Project Management in a week”, Hodder & Stoughton, UK
[3] Sue Craig, Hadi Jassim, “People and Project Management for IT”, McGraw-Hill
[4] Dinsmore, Paul C, 1993, “The AMA Handbook of Project Management”, New York AMACOM Books, 1993, New York
[5]Lewls, James P, 1995, “Fundamentals of Project Management”, New York AMACOM Books, 1995, New York
[6] Toby Teorey, 2008, “Database design : know it all”, Morgan Kaufmann
[7] M. Vaziri and D. Jackson, Some shortcomings of OCL, the Object Constraint Language of UML, MIT, 1999.
[8] N. V. Cuong and X. Qafmolla, Meta-model Transformation with Kermeta, in 13th International Conference OBJEKTY 2008 proceedings, p. 109-116.
[9] Object Management Group, “Business process modeling notation,” February 2006,
Order Now