<Membership Number> – Used to identify player for which record is stored.
<Fixture Code> – Identifies which fixture record relates to.
Tries – Number of tries scored by a particular player in each fixture.
Penalties – All penalty kicks converted by a player in a fixture.
Drop Kicks – All drop kicks scored by a player in a fixture.
Disciplinary – Whether a player was disciplined in a fixture and what level of action, a yellow card for example.
Fee Owed –The amount paid will be displayed from.
TOURNAMENT holds data relating to tournaments the clubs plays in:
<Tournament Code> - Unique code which identifies each tournament.
Winner – Eventual winner of tournament.
1.5 Entity Relationship Diagram
This diagram of how data will be kept in the new system.
The Players-Fixtures data store will link Players and Fixtures together. It will store data relating to what a particular player does in each game. Each record will be identified by using a player’s membership code and the corresponding fixture code, these two foreign keys will create a composite key. Each player may have played in many fixtures and therefore have many records in Players-Fixtures. This creates a one to many relationship between, Players and Players-Fixtures. Similarly many records in Players-Fixtures may relate to one fixture and so a one to many relationship exists between Fixtures and Players-Fixtures.
The Rival Players-Fixtures data store is similar to Players-Fixtures, however it stores data about rival players as opposed to players for Wooten Bassett rugby club. Each record will be uniquely identified using the foreign keys of rival player code to determine which player, and fixture code to determine the fixture. Again a one to many relationship exists between Fixtures and Rival Players-Fixtures as one fixture may have many related records in Rival Players-Fixtures. Each Rival Player may have many records in Rival Players-Fixtures as they have played in many fixtures, a one to many relationship will exist here.
Wooten Bassett will face many different rival clubs with many different Rival Players. Therefore Rivals will store data about all rival clubs and each record will be uniquely identified by team code. Rival Players will store data will store data about all the players who play for these clubs will be identified by the rival player code. To determine which player plays for which club Rival Players will have the foreign key Team code. This means many Rival Players can play for one club, again a one to many relationship will exist here.
Each fixture will be against one of the teams detailed in Rivals. In order to identify which team is being played the foreign key, team code from Rivals will be used. One Rival may play in many fixtures creating a one to many relationship from Rivals to Fixtures.
Tournament holds data relating to competitive fixtures. Therefore one tournament will have many records in fixtures, and again a one to many relationship is present.
1.6 Data Flow Diagrams
2 Design
2.1 System Design
The system will be divided into five entities described in Analysis. These are Players, Rivals, Fixtures, Players-Fixtures and Tournament.
2.2 Attribute Design
Following are description of all the attributes to be stored in the system:
2.3 Form Design
In order for the user to interact with the system, it must be presented in a user friendly interface. Within Access this is done using forms and reports, each form should allow the user to easily understand the information and be able to perform sufficient actions such as adding or deleting data.
Following are the forms required by the system:
- frmFixtures
This form will deal with all the actions relating the handling of data with fixtures. Here the user will be able to add, alter or delete any records from the Fixtures table. For example if a new fixture has been organised then the user can use the Add Fixture button to add a fixture to the schedule. Also changes to the date for example can be made. In order to maintain the consistency of data validation is being used. The FixtureCode attribute is updated automatically as an auto number preventing the user from making mistakes. Also a TeamCode will be selected from a combo box so a user cannot enter a team that does not exist. Also only two values will be able to be entered into the Home/Away attribute, home or away. To simplify the user’s navigation of the system a combo box at the top of the form allows the user to select any existing record quickly.
- frmPlayers
The Players form works on a similar principle to the Fixtures form. There are button to add and delete Player records and there is also a combo box to allow the user to navigate through the system. For validation the training attribute uses a combo box to ensure the user enters the correct value and an input mask controls the MembershipNumber, so that data is entered in the appropriate format.
- frmRivals
Again the Rivals form works in a similar fashion to Players and Fixtures. Buttons on the right control adding and deleting functions and a combo box is used for navigation. Validation is ensured by using an auto number data type to automatically update the TeamCode attribute.
- frmTournament
The tournament form works exactly the same as the previous forms, with add and delete buttons clearly labelled and a combo box for navigation. The TournamentCode attribute does not require input as it is an auto number ensuring validation.
- frmFixture Statistics
This form is directly different form the previous forms. In this form a user can manipulate data in the Players-Fixtures entity. The user can view specific fixtures through a combo box, when a fixture is selected it will open a sub form containing all the data about the players whom played in that match. Here new data can be added about a fixture or incorrect data can be corrected. The FixtureCode and MembershipNumber attributes will be controlled by a combo box, eliminating user error and the consistency of the data.
2.4 Query Design
For the system to perform the function required of it queries are used to extract the precise data from the database.
- qryTotalDropKicks, qryTotalTries, qryTotalPenalties
These three queries all perform similar function, they will be used to determine the total amount of penalties, drop kicks and tries scored by players throughout the course of the season. Each query will hold the attributes, MembershipNumber and name to identify players. Also a calculated field that sums the total number of respective statistics. I will these queries using SQL and the sum function. The sum function will sum all the values in a given field.
SELECT tblPlayer.Name, tblPlayer.MembershipNumber, Sum([tblPlayers-Fixtures].Penalties) AS TotalPenalties
FROM tblPlayer INNER JOIN [tblPlayers-Fixtures] ON tblPlayer.MembershipNumber=[tblPlayers-Fixtures].MembershipNumber
GROUP BY tblPlayer.Name, tblPlayer.MembershipNumber;
SELECT [tblPlayers-Fixtures].MembershipNumber, tblPlayer.Name, Sum([tblPlayers-Fixtures].Tries) AS TotalTries
FROM tblPlayer INNER JOIN [tblPlayers-Fixtures] ON tblPlayer.MembershipNumber = [tblPlayers-Fixtures].MembershipNumber
GROUP BY [tblPlayers-Fixtures].MembershipNumber, tblPlayer.Name;
SELECT [tblPlayers-Fixtures].MembershipNumber, tblPlayer.Name, Sum([tblPlayers-Fixtures].DropKicks) AS TotalDropKicks
FROM tblPlayer INNER JOIN [tblPlayers-Fixtures] ON tblPlayer.MembershipNumber=[tblPlayers-Fixtures].MembershipNumber
GROUP BY [tblPlayers-Fixtures].MembershipNumber, tblPlayer.Name;
- qryTotalPoints
It also important the system is able to calculate the total amount of points scored by each player over the course of the season. This query will hold the MembershipNumber and Name attributes to identify the players, the total number of tries, drop kicks, and penalties and a calculated field of the total number of points. In order to calculate the total number of points tries must be multiplied by 5 and drop kicks and penalties by 3.
SELECT [tblPlayers-Fixtures].MembershipNumber, tblPlayer.Name, qryTotalDropKicks.TotalDropKicks, qryTotalPenalties.TotalPenalties, qryTotalTries.TotalTries, (qryTotalDropKicks!TotalDropKicks*3)+(qryTotalPenalties!TotalPenalties*3)+(qryTotalTries!TotalTries*7) AS [Total Points]
FROM (((tblPlayer INNER JOIN qryTotalDropKicks ON tblPlayer.MembershipNumber = qryTotalDropKicks.MembershipNumber) INNER JOIN qryTotalPenalties ON tblPlayer.MembershipNumber = qryTotalPenalties.MembershipNumber) INNER JOIN qryTotalTries ON tblPlayer.MembershipNumber = qryTotalTries.MembershipNumber) INNER JOIN [tblPlayers-Fixtures] ON tblPlayer.MembershipNumber = [tblPlayers-Fixtures].MembershipNumber;
- qryFees
Another important aspect of the system is to monitor the fees owed by players. The amount owed after every game is stored therefore the query will sum these figures together for each player. To do this I will again use the sum function in SQL. MembershipNumber, Name, Address1, Address2, City and Postcode will be the other attributes stored in this query.
SELECT [tblPlayers-Fixtures].MembershipNumber, tblPlayer.Name, tblPlayer.Address1, tblPlayer.Address2, tblPlayer.City, tblPlayer.Postcode, Sum([tblPlayers-Fixtures].FeeOwed) AS SumOfFeeOwed
FROM tblPlayer INNER JOIN [tblPlayers-Fixtures] ON tblPlayer.MembershipNumber = [tblPlayers-Fixtures].MembershipNumber
GROUP BY [tblPlayers-Fixtures].MembershipNumber, tblPlayer.Name, tblPlayer.Address1, tblPlayer.Address2, tblPlayer.City, tblPlayer.Postcode;
- qryDisciplinary
An important statistic stored about players are their disciplinary records. It is recorded if a player gets a yellow or red card over the course of the season. This query will extract the following attributes: MembershipNumber, Name, FixtureCode, TeamCode, Date and Disciplinary.
- qrySeason
The season query will extract all the scores of all the fixtures from the entire season. The attributes involved will be: FixtureCode, TeamCode, TournamentCode, TeamScore, RivalScore,Date, Home/Away, andWinner.
- qryOrganiserAway
This query will find all the fixture taking place between the actual day it is being used and a week later. This is used so that the system can generate letters to be sent to players informing them of upcoming fixtures. Due to this reason this query extracts a large amount of information including addresses for players and directions to the match. However this query only generates this data for away matches.
- qryOrganiserHome
This query is exactly the same as the one above however it extracts data for home match. A home match will not require the same data as an away match so less data is needed such as direction to another’s team venue.
2.5 Report Design
The reports will be used to present data from the database in an appropriate fashion.
- Leading Drop Kick Scorers, Leading Penalty Kickers, Leading Try Scorers and Leading Points Scorers
Each of these reports will present in order the leaders for each respective statistic. For example the Leading Drop Kicks reports will be grouped Player’s name but will also hold data on the player’s membership number and the number of drop kicks scored. The leading drop kicker comes first and the report will be in descending order downwards. This will be similar for the Leading Penalty Kickers and Leading Try Scorers reports. The Leading Points Scorer report will not only show membership number, name and the descending order of leading points scorers but also display the amount of drop kicks, tries, and penalties scored by that player. The source of this data will be the queries, qryTotalPoints, qryTotalDropKicks, qryTotalPenalties and qryTotalTries.
- Disciplinary Record
The Disciplinary report will present information form qryDisciplinary. It will be grouped by the player’s name and display each player’s disciplinary record for that season up until that point. This means each yellow or red card received and the match they receive it in will be shown.
- Season Overview
The Season Overview report will be based on the query, qrySeason. It will display all the matches of that season and their scores in chronological order.
2.6 Letter Design
Vital to the user’s requirements for the system was the facility to create standardised letters. There are three letters the system will provide, one that informs players of outstanding fees and another that informs players of fixture information. The outstanding fees letter will be based on the query, qryFees. The letter relating to fixture information will be based on the qryOrganiserAway and qryOrganiserHome queries.
2.7 Menu Design
In order to comply with user requirements the system must be as simple and user friendly as possible. Therefore the menu must be kept simple to avoid confusion.
The system will open in a main menu offering the user three options: Add/Delete Data, Reports and Letters. The Add/Delete Data option will give the user the option access the forms described in Form Design. The Reports option will allow the user to view and print the reports described in Report Design. Lastly the Letters option allows the user to access the standardised letters described in Letter design.
2.8 Macro Design
I have designed a macro that allows the user to preview or print a report from the reports menu. Each option in an option box is assigned to a report and when combined with clicking on either the print or preview button the corresponding action takes place. Also the last section of the macro will allow the user to exit the reports menu and enter the main menu.
Another macro to be used will be the mcrMainMenu. This macro will automatically open the main menu form as soon as the database has been opened.
Other macro will be applied to buttons, these macros will open a specified form and close the current form. For example if the Reports button in the Main Menu is selected this will open the reports form and close the main menu.
3 Testing
Testing Strategy
Test results
After conducting each test, the majority of tests performed as they were expected to. However there were a few exceptions where tests did find flaws, these were quickly fixed. Following are a few examples of successful tests and those tests that exposed errors:
2. This test was designed to ensure the reports button worked in the main menu. This is crucial as it will allow the user to navigate through the system.
Before:
After:
This test worked successfully.
- This test ensures that the macro, mcrSelectReport works correctly. The macro was designed to open a report selected from the option box in print preview mode.
Before:
After:
Again another successful test and part of an important macro is working correctly.
- This test also use the macro, mcrSelectReport but instead uses another section of the macro that prints the selected report.
Before:
After:
This test also works and demonstrates that the macro is working successfully.
- This test tests the system’s mail merge function. When the Outstanding fees button is pressed the system should merge the query, qryFees with the document Fees.doc.
Before:
After:
This test failed as the hyperlink was not working correctly. The address stored did not directly refer to the file with the correct document. When this problem was rectified the correct result occurred:
32. Validation was tested with this test. The attribute, FeeOwed had to have a value between £2.00 or below. To work correctly the system would allow the value £2.00 to inputted.
Before:
After:
The test failed as the validation rule set for asked for values less than 2. However when two is entered this is unacceptable as the validation was not set for values less than or equal to 2. This problem has been fixed and now performs correctly.
4 Appraisal
The system has been fully implemented onto the user’s system. In relation to the original objectives these have been for the most part achieved.
The system is able to generate template letters to reduce the amount of time spent writing letters. The user has found this aspect particularly pleasing.
The user is now able to easily find contact information for players and their parents. Also individual players can be monitored for their payments at matches and membership fees.
The system is able to store important information on fixtures and rival teams so that these can be easily extracted.
The user can easily view statistics on players and monitor, which players are on what training program.
Overall the user is generally pleased with the results. He is pleased to see the system has automated a lot of the tasks that were previously time consuming. He is also pleased that the interface is simple to navigate and understand. However he does feel that entering data for individual matches is taking up his time instead. In the future perhaps the system can be adapted to reduce this task by having a more user friendly method of entering data.
5 User Manual
This system has been designed to simplify the processes and procedures associated with the rugby club. This user manual has been written to allow you to come to grips with all the different elements of the system.
When the system is loaded the first screen to appear is the Main Menu (pictured below). There are four options to choose from, Add/Delete Data, Letters, Reportsand Exit. The exit button will close down the system and following the other options are described in detail.
5.1 Adding and Deleting Data
In order for the system to provide any use for the user it is crucial it allows you to manipulate data with ease and precision. When you want to change any data simply enter the main menu from here select Add/Delete Data, this will bring you to an screen where six options are presented:
-
Players- This is the option where data can be added or manipulated relating to data stored about individual players.
User the drop down menu labelled View Player you can select the record you wish to view by selecting their name. The two buttons on the side of the screen control manipulation of data. By clicking on add player, a new, blank record will appear in which enter new data. The delete player button can be used to delete individual records. Add/Delete Data button will access the Add/Delete Data menu. Be aware that data under the Membership Number attribute must fit the format of three numbers followed by the letter, E, if data is not entered in this format the system will not allow you to enter the data. Under the training attribute data is entered by selecting one of the options in the drop down list.
Below is an example of Adding new data:
-
The Second option available is Fixture Statistics. This option takes you to a screen where data can be added about player statistics for each individual player.
The Add/Delete Data button will bring you back to the Add/Delete Menu for simple navigation. The view fixture drop down menu allows you to select a particular fixture in which to add records or change records for each player for each player. By using the drop down menu under MembershipNumber you can select any player stored under Players, similarly the Disciplinary attribute has a drop down menu to select either yellow card or red card.
-
The Rivals option allows you to view all the data stored about any rival team such as addresses or telephone numbers.
On the Rivals screen a drop down menu labelled, View rival team is used to navigate through the records. The Add Rival button will open a blank record where new data can be entered. The Delete Rival button will delete the current record. Again the Add/Delete Data button will take you back to the Add/Delete Data menu. The Team Code attribute will be updated automatically. You should also be aware as the Directions attribute is limited to 255 characters.
-
The Tournament button. Here data can be added for the winner of a given tournament.
The Add/Delete Data button again will bring you back to the Add/Delete Data menu. The Add Tournament button will load a blank record that can then be inputted with new data. To delete a tournament record simply press the Delete Tournament button. No need to enter a value in the Tournament code attribute as this will be updated automatically. The value in the winner field can be entered using the drop down menu to select one of the teams stored under Rivals.
-
Fixtures will allow you to manipulate data about fixtures. For example adding a newly organised fixture or deleting one that has been cancelled.
The Add/Delete Data button will bring you back to the Add/Delete Data Menu. The Add Fixtures button will provide a blank record to enter new information and the Delete Fixture will delete the current fixture. The FixtureCode attribute is updated automatically and the Home/Away attribute has a drop down list to choose either home or away.
-
The last option is Main Menu, this option will load the main menu and close the Add/Delete Data Menu.
5.2 Reports
After pressing the reports button you will be taken to the Reports menu.
Under the option group, Select Report the various reports available are listed. These include a list of leading points and penalties scorers. When one of these options are selected there are two options available, Preview or Print. When both a report is selected and the preview button used , that report will be opened in Print Preview mode. Similarly if a report is selected and the print button pushed the corresponding report will be printed. The Main Menu button will take you back to the Main Menu.
Below is an example of the Disciplinary report:
5.2 Letters
When the letters button has been selected from the Main Menu, the Letters Menu will load. Here you can print one of three template mail merges.
-
Outstanding Fees- The outstanding fees letter is a template for all players that have not paid their match fees. The system will calculate the total amount owed and when this option is chosen a letter addressed to all those owing money will be made, to be used at your own discrepancy.
-
Fixture Information-Home- This option will allow the user to print off letters addressed to all the players informing them of any upcoming home games in the following week.
-
Fixture Information-Away- Working in a similar way to Fixture Informaion-Home this prints letters about fixtures approaching in the forthcoming week. However this option provides information about how to reach upcoming matches.
6 System Maintenance
6.1 Forms
frmMainMenu
Uses a macro, mcrMainMenu to automatically open when the database is loaded. Also uses macros, mcrOpenReports, mcrOpenLetters and mcrOpenAdd/Delete to open the forms, frmReports, frmLetters and frmAdd/Delete but also close frmMainMenu. The button Exit will close the database.
frmReports
Uses the macro, mcrSelectReport to either print a chosen report, preview a report or access frmMainMenu and close frmReports.
frmLetters
The buttons are hyperlinks to access three mail merge documents in MSWord, fees.doc, fixtureinformation-home.doc and fixtureinformation-away.doc. The macro, mcrLMainMenu is applied to the Main Menu button and will open frmMainMenu and close frmLetters.
frmAdd/Delete
The macros, mcrOpenPlayers, mcrOpenFixtures, mcrOpenStatistics, mcrOpenRivals, and mcrOpenTournament are applied to the buttons to open, frmPlayers, frmFixtures, frmStatistics frmRivals and frmTournament respectively. The macros will also close the form, frmAdd/Delete. The macro mcrAMainMenu will open the form, frmMainMenu and close the form, frmAdd/Delete, it is applied to the Main Menu button.
frmPlayers
This form has a combo box to navigate through records by choosing a player name. It has a macro applied to the button Add/Delete Data called mcrClosePlayers that opens the form, frmAdd/Delete and closes frmPlayers. The Add Player button loads a blank record and Delete Player deletes a record.
frmRivals
This form has an Add Rival button that loads a blank record and a Delete Rival button that deletes a record. The Add/Delete button has a macro called mcrCloseRivals that closes the form, frmRivals and opens the form, frmAdd/Delete. There is a combo box allowing the user to choose a team name and navigate records this way.
frmfixtures
This form has a combo box where by choosing a FixtureCode navigates through the records. The Add/Delete Data Button is controlled by the macro, mcrCloseFixtures which closes frmFixtures and opens frmAdd/Delete. The Add Fixture button loads a blank record and the Delete Fixture button deletes the current record.
frmTournament
This form has a drop down menu where a TournamentCode can be chosen. The Delete Tournament button deletes the current record and the Add Tournament loads a blank record. a macro applied The Add/Delete Button has mcrCloseStats which closes the form, frmStatistic and opens the form, frmAdd/Delete.
frmStatistics
This form has a sub form called frmPlayers-Fixtures which lists all the records matching a given FixtureCode. To view the record the combo box View Fixture allows the user to choose a precise fixture. The Add/Delete Button has a macro applied mcrCloseStats which closes the form, frmStatistic and opens the form, frmAdd/Delete.
6.2 Queries
Queries are all described in the 2.4 Design section.
6.3 Macros
Macros described under 6.1Forms.
6.4 Reports
Reports are described under the 2.5 Report Design.
Rugby Club System
1 Analysis
1.1 Introduction
1.2 Current System
1.3 Interview Summary
1.4 Data Requirements
1.5 Entity Relationship Diagram
1.6 Data Flow Diagrams
2 Design
2.1 System Design
2.2 Attribute Design
2.3 Form Design
2.4 Query Design
2.5 Report Design
2.6 Letter Design
2.7 Menu Design
2.8 Macro Design
3 Testing
3.1 Testing Strategy
Test No.
Test
Expected Result
3.2 Test results
4 Appraisal
5 User Manual
5.1 Adding and Deleting Data
5.2 Reports
5.2 Letters
6 System Maintenance
6.1 Forms
6.3 Macros
6.4 Reports