System Analysis And Design Soccer Club Database Computer Science Essay

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 members 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 teams 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 members 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 Cups 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 players information, this table include the field: Player_ID, Phone, Address, Email, Salary, user_ID 4. Training Detail, this is the table of Trainings 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 teams 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 coachs 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 members 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 teams 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 committees 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 Clubs 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 sessions 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 Clubs 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 clubs 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 clubs 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 users detail. This is a table that includes all users 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 coachs 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) Email VARCHAR(30 User_ID CHAR(10) Foreign Key reference User table Table Name: Member Table Type: Entity Description: It contains members detail. This is a table that includes the members 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) Email 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 teams 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 clubs 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 sessions 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) Email 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 sessions 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) Email 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,