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 dont 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 Shakespeares 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 clubs 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.)