i need to normalise the following information and explain how i got from 1NF to 2NF to 3NF
please add htp: //
the case study is as follows:
Case Study: BlueSwim Swimming Club
BlueSwim Swimming Club is a local amateur swimming club. An interview with BlueSwim's Chairman, Albert has provided the following information.
The club was established a number of years ago and Albert took over as chairman approximately 3 years ago. The club runs entirely on volunteers, who coach the swimmers, organise competitions and run a club shop selling costumes, goggles and other swimming paraphernalia at a small profit to the club. The club has approximately 200 members of all ages and abilities, but predominantly children aged eight to fifteen.
Each member pays membership fees once per year, which pays for pool use, running local galas and coaches to take the team to away matches. The club has a number of teams that compete in a variety of leagues. The information for this is kept on the Internet by each league’s committee and Albert is not interested in duplicating information that is readily available on the Internet.
When new members join, they have to complete a registration form with their personal details and it has to be signed by a parent or guardian if the member is under sixteen. This information is then transferred to a member card (example 1) and a number of spreadsheets that store information on training groups (example 2), best timings for each stroke (example 3), etc.
Every two months the club hold a ‘time trial’ where everyone swims each stroke under race conditions and their times are recorded. This information is compared with timings stored for each stroke and updated accordingly. Training groups are likely to also change as a result of this (members with similar abilities and timings swim in the same group). The teams are also likely to be updated to make sure that the best swimmers are representing the club, although swimmers cannot be demoted to a lower team during the season.
The club shop is run by Agnes and is ‘open’ during the training sessions on Sundays. There is a wide variety of stock available to buy for training sessions, such as costumes, goggles, floats, fins, hats, bags, etc. There are also items such as costumes, track suits and T-shirts that carry the club logo and are personalised with the member’s name. All stock is kept at home by Agnes and brought to Sunday sessions in case anyone wants to buy anything. A list of items sold (example 4) is typed up by Agnes after every Sunday session and at the end of each month are passed, with a summary of the sales for the month and the money, to Janis, the Treasurer. An accurate stock inventory is not available, but more stock is ordered from a number of selected suppliers if Agnes feels she is running low on any item. The total value of all stock should not exceed £3,000 at any time, but probably does….
the assignment is as follows:
Your tasks are as follows:
Part One :
1. Using a bottom up approach (normalisation), create, atomise and normalise the Shark Fin Swimming Club, using the four sample forms attached (appendix) as your starting point. Show each step of the normalisation process, justifying what you are doing at each stage. State any assumptions you make during the process.
2. Using MS Access, implement your proposed solution from the normalisation process to test the feasibility of your answer (you should validate the data fields, create the relationships in Access, and input some test data, but do not create the user interface (forms and reports)). Evaluate the feasibility/suitability of your solution (max 500 words) in terms of implementation and effectiveness for the club.
Part Two :
3. Create annotated design sketches for a prototype user interface to satisfy at least four of the user requirements identified below. Justify your design decisions making use of refereed articles or books. You can also use other Web material (e.g. material from vendors’ sites, individuals’ sites or mailing lists, tutorial material from Universities, etc) but should discuss the suitability of the source.
The users requirements for the new system are as follows : The system must be able to :
• Add new and/or update existing member details.
• Add and update time trial details and results.
• Record the attendance at a training session.
• Produce a report showing members who are in each (or a specified) training group..
• Produce a report showing any members who have joined the club in the last 3 months.
• Enter details of the sale of swimming equipment to a club member.
• Produce a report to show current stock levels of swimming equipment.
• Produce a report showing all items purchased by each member in the last year (what was bought and who bought it).
• Produce a report showing the shop sales for a day/month/year