CASE STUDY: Squash League of Southern Perthshire (SLSP)
You are required to build a database system to record details for a non-profit organisation that manages the squash competition around Perthshire with the main headquarters centred in Perth (Scotland).
The main part of the system is to receive and acknowledge any new member registrations, using a friendly user interface - this allows a prospective member to enter their personal details (i.e. name, address, contact, etc.). Although the organisation is non-profit and a registered charity, it does permit new members to make a compulsory donation once they have completed the membership form. This donation itself can either be done via (BPay, Visa or cheque) with the later accepted by staff members at the various courts around Perthshire.
A newsletter is one of the main forms of communication between the administrators of the league and the players, this newsletter sets of the rules of competition, the scoring and a list of actual games that players are scheduled to play at the main centre in Perth. This newsletter is sent to all the members on a monthly basis and allows the members to send any feedback to the organisation to the company secretary who manages all the administrative functions of the organisation.
Concerning the actual games: Each singles game records two players and one result from that particular division. Players can only compete within their own particular set division. When a match has being completed a score is recorded in the following format 3 set format (e.g. 11-5, 3- 11, 11-6). The system needs to be able to record each players win and loss, the points on the ladder, matches played.
A separate part of the system will contain the competition ladder which is divided into a number of separate divisions, the five (5) divisions are based on age, with the open draw also separated by sex (i.e. an open women's competition, and a men's competition) with each division having a number of positions or rankings according to the players' proficiency and their accumulated points throughout the season.
The system will need to also identify future fixtures that is it should show any scheduled games to be played in the near future (i.e. 1 month ahead of time). It should also have an archival facility in that it will show any past fixtures for that season along with the scores.
The system will need to record the various staff members at the squash court centre, so a record is kept of who took payments from which member. This member will also act as the official and umpire for the various fixtures throughout the season. Since this is a charity organisation, the staff members are also players from the various divisions of the ladder, which naturally means that their personal details are also recorded on the system.
Task 1
Business Case
Task 1A. Explain why a relational database would be suitable information system for the organisation such as the one provided in the case study and provide (3) three reasons to support your recommendation (500 words).
Task 1B. Explain (3) three distinct advantages concerning what a database management system provides to any organisation. Note: Make these relevant to the organisation in the case study (500 words).
Requirements Definition
Read the case study carefully, and decide what are the important (i.e. key) features of the system. Construct a basic prototype that will display the following:
Task 1C. Design a set of Initial screens that can be shown to the client - this allows for the input of information to satisfy the user requirements, you should produce at a minimum (5) five forms. Submit a softcopy that has a set of screen shots that illustrate the screen designs with appropriate narrative for each screen. (100 Words)
Task 1D. Design a set of typical reports that would be appropriate for the proposed company database - this requires you to assume the role of the user/manager and list three (3) likely reports he/she might need to do their job. It must show the report, its main features, and then populate it with some likely test data. Submit a softcopy that has a set of screen shots that illustrate these proposed screen designs. (100 Words)
Note:
A suitable package to do the above task (A to D) would be Microsoft Word or any similar product.
Task 2
Development
Use database development strategy to decide the main elements of the database - this will include nominating the:
Fields (also the primary keys and foreign keys) Tables
Links and Cardinalities
Task 2A. Produce a Data Dictionary - which contains the following information: table, field type, field size and field description.
Note:
A suitable package to do Task 2B would be Microsoft Word or any similar product.
Database Design
Develop an initial prototype of this case study system, therefore in your design phase you and your team should create the following appropriate diagrams. Use the MySQL Workbench (or equivalent tool set) to create the following:
Task 2B. Propose an Entity - Relationship diagram that matches the information provided in the case study. Use the appropriate formalism and structure as explained in the theory.
Task 2C. Develop the Normalised Schema for the system you have proposed - normalise to 3rd normal form. Provide a relevant justification which shows how your ER-schema passes each of the normal forms.
Note:
A suitable package to do Task 2B would be Gliffy, EDraw, Microsoft Visio or any similar product.
Task 3
Implementation of a Relational Database
Task 3A. Generate a physical database, using the final normalised ER - Diagram to create this database. Use MySQL server, Oracle Express or an equivalent relational database to create the system. Provide a relevant screenshots of the final system that displays the entire set of tables as evidence of its creation.
Populate the Database
Task 3B. Demonstrate the entry of six (6) records into the database system that you previously created. These records might be used to display to the client that the system is operational. Provide screenshots of the final system that displays (3) three of the tables with the mock data inserted.
Task 3C. Design a comprehensive user manual that will describe how to use the database system you have developed. Screenshots of the main menu and navigation between the sub-menus along with accurate descriptions is an important feature of this section of the assignment. (750 Words)
Note: You may use previous screenshots (forms, reports and data screens) to create this manual.
Improvement
Task 3D. Reflect upon your new developed understanding of database design and provide a lessons learned log - that indicates what you found important and any improvements you wish to make for the next version of the database, and other personal reflections. (300 Words)
Note:
A suitable package to do Task 3A, Task 3B would be MySQL community server or any similar product.
A suitable package to do Task 3C, Task 3D would be Microsoft Word or any similar product.