Case Study
The organiser of the Garden City Harriers Running Club requires you to design and implement a web-based database application to assist with the running of the club. The website will be called “The Garden City Harriers Running Club” (GCH). The first task is to design the back-end database. The organiser would like initially to hold information about athletes with their name, address, email, date of birth, and phone number etc. The members will run in various races throughout the country and the club would like to record the results for each member. The club would also like to record details of the members’ next_of_kin (up to two next-of-kin details would be useful).
Every year there is an awards ceremony where members are awarded prizes.
Some of the members only run in races (running races) and other members are triathletes and run in triathlons. (A Triathlon combines three sports in one race: swimming, cycling and running.). Some members do both Triathlons and run in Races. The club puts on and organises two or three of its own races in the year. (These are only 5 Kilometre (5K) running events not triathlons.) The club does not have the facilities to put on a triathlon. The club would also like to keep details of the local volunteers who offer to marshal these races which the club puts on and organises itself – for this they only need a contact list; we don’t necessarily need to allocate the marshals to races at the moment although the club would like to do this in future. (The marshals direct the runners round the course during the race and have various points where they stand around the course. They also help clean up at the end and take down the signage.) These 5K club races attract runners from other clubs round the country. It is also a fund-raising opportunity for the club and they charge a race entry fee (members have to pay too!). For these races the club needs to keep an entry list of the external runners and their clubs and their dates of birth and its own club runners who enter the race, in order to be able to publish results.
However the majority of the races and results stored will be the results for runners who run in external races. It is important to get these results recorded correctly in the club database as some members can be chosen to run with elite runners in large events – for example the London marathon – depending on their results throughout the year. The club administrators enter the results from weekly races into the club database. They do this by trawling through the races on the race calendar every week and they also rely on members to tell them about the more obscure races.
There are many different race distances. The most popular being the marathon, half marathon, 10K and 5K races. Races will have a name and a distance recorded. Most races run year after year at approximately the same time of year and will have a race name. For example “The Stratford Half Marathon” for an annual half marathon which runs in Stratford each year on the Sunday before the anniversary of Shakespeare’s birthday. Or “The Suicide Six” – a six mile multi-terrain race which runs every year in Dudley. Most races are on a Sunday, but there are some evening races and occasional races on a Saturday.
Athletes can run in many races throughout the year. When athletes enter an event they will always be given a race number. Results are recorded by the chip worn on the shoe or wetsuit and they also have to wear the race number on their shirts. The race number varies from race to race.
For the Triathlon races there are several times to be recorded.These are:- the swim time, the cycle time, the run time, transition time between the swim and the cycle (usually called T1,) the transition time between the cycle and the run (usually called T2) and the overall time which is all of these added together.Triathlons vary in the distances they have for each sport.A sprint Triathlon is usually a 750 metre swim, a 14 mile cycle and a 5K run. An Olympic Triathlon is usually a 1.5K swim, approximately 28 mile cycle and a 10K run. However in many Triathlons the cycle distance often varies depending on the terrain and what is available locally. However the events always finish with a standard 5K, 10K, half-marathon (13.1 miles) or marathon run (26.2 miles.)
The club needs to be able to report the personal best times for club members in races they have run (a personal best (PB) is the shortest time they have run the distance in). Personal best times are only stored for standard distances. These standard distances are 5k, 10K, half marathon and full marathon. This means that the club needs to keep results for all members for the duration of their membership until they leave the club. This allows the club to award prizes at the annual dinner and prize giving evening in December each year. Prizes are awarded for a) the most improved runner b) the best performance for a newcomer c) the Triathlete who took the longest times to get changed in Transition points (this is a joke award called the Vanity Trophy).
As regards the club’s own races, prizes are awarded for each race. Prizes are awarded on the day. These will be 1st overall, 2nd overall and 3rd overall, 1st female veteran over 40, 1st female veteran over 50, 1st female veteran over 60, first female veteran over 70, 1st male veteran over 40, 1st male veteran over 50, 1st male veteran over 60, 1st male veteran over 70.
And, above all, running and tri sports are all about achieving personal best times for the competitors. Race results need to be recorded in fractions of a second!
Example Member table fields: member_no (primary key), first_name, last_name, gender, email address, address line one etc, date_of_birth, main phone number, joining date.
Choose appropriate data types and insert some rows (at least 20) into the members table. Also please insert the following row into the members table.
member_no – 1, first_name Marie, last_name Byrne, email b.m.byrne@herts.ac.uk date of birth 15.06.65. Insert suitable information into the other fields. (This row is used for testing your tables on-line by the lecturer.)
Example Race Table Fields: race_no, race_name, race_date,
race_distance, (Primary Key - race name and date)
Sample data for the race table. Add further rows as necessary.
Race Name Date Distance
Forest 10K 12 Feb 2019 10k
Stratford Half 25 April 2019 Half Marathon
Nottingham Half 5 Sep 2019 Half Marathon
London Marathon 18 April 2019 Marathon
Dudley Action Heart 5K 5 Feb 2019 5 K
Stratford Half 26th April 2020 Half Marathon
Part One
Draw an enhanced Entity Relationship Model for the Garden City Harriers case study described in previous pages. Show the structural constraints using the minimum and maximum number notation used in the module. Explain any assumptions you have made (if any).
Submit: The model (on one or two pages (maximum) with assumptions if necessary). The enhanced ER model can be hand-drawn or completed with a tool. No extra marks are awarded for using a drawing tool. (Please retain all iterations of your design – marked version one, two, etc. as during the marking of the assessment some students may be asked to hand-in their versions.)
Part Two
Using the data model mapping algorithm used in the module map your extended Entity Relationship model to a set of relations and include examples of constraints. (Show the final set of relations/tables only there is no need to show the steps.)
Submit: The final set of tables/relations (written out in the accepted way). This should be a maximun of two pages.
Part Three
In order to test that the tables/relations you have produced will meet the requirements of the club, answer the following in Structured Query Language (SQL). You will need to create the tables in the university installation of the Oracle DBMS and populate the tables with some sample data.
Produce a list of club members ordered by the date they joined the club (the members who joined more recently appearing first).
How many marshals does the club have?
When the club ran its “Festive Five” on 15th December 2019 it charged £8 per entry. How much did it make on entry fees that day?
Who came first overall in the Festive Five on the 15th December 2019?
Who was the first female runner in the 40 - 49 category for the above race?
Who has the fastest time recorded for a 10k among the members, (their PB)?
James Black joined the club in 2012. What are his times for 10 kilometre races since he has been a club runner, and which races did he achieve them in?
Produce a report showing the Triathlons which club members have competed in this year. Show the times achieved including the Transition Times and the overall Race time. We would like members’ full names in the report.
Produce a list of members (including their names) and the External Runs (not triathlons) they have competed in this year up to today’s date with their results in fractions of a second. It would be sensible to group this by Race name and then ordered by time achieved within each race – obviously starting with the winner!
Who should the club award “The Vanity Trophy” to this year?
Submit:
For each query submit the SQL required to produce the result based on your tables from Part Two. and the results of the query. This should take the form of a screen shot of your results from the Oracle database. In the mark scheme above it is one mark for the query and the remaining marks for the results. (If you have problems producing results submit the text of your SQL query based on your tables.)