Reference no: EM132095263
Please drawing the ER diagram to show the relationship.
The Grand Bay Family Fun organization (GBFF) wants a system to help it manage a series of cutting-edge road-rally events. In traditional road rallies, all teams solve the same clues, in the same order.
Traditionally, there may be a 5 minute gap between one team's departure and the next team's. GBFF will let teams start with only a 30 second gap between teams, because the order in which teams solve clues has been randomly scrambled for each team.
Interviewing Fred from GBFF, the following facts are discovered. Not all are relevant for the system being designed, and the ERD you are preparing should omit irrelevant things.
1. GBFF will only ever hold one road-rally event on a given date.
2. Fred's shoes are size 8, and he will wear them to any road rally he helps organize.
3. Teams at a road-rally event are numbered 1, 2, 3, etc., but there is no globally unique identifier for a team. (The team numbered 1 in last week's road rally is likely to be unrelated to the team numbered 1 in this week's rally.)
4. A road-rally event will have between 4 and 20 teams.
5. Teams are made up of people who are team members, and a team can have 2-6 members. A team drives a particular car during a road rally. This car may have been used by a different group of people in an earlier rally.
6. One member of each team will receive text messages for the team, containing the clues.
7. Over time, a person may have competed in several rallies and may have belonged to a (possibly different) top-3 team several times. GBFF tracks these things.
8. Cars are identified by their license plates (province and number).
9. All teams in the event solve the same clues (although not in the same order). However, everyone gets the same final clue, which sends them to the restaurant where the winners are computed.
10. Fred is responsible for making good clues and questions and specifying the desired answers. Fred can re-use clues in several road rallies, but he likes to make sure that he doesn't ask a question that was used too recently. Thus, for every question/clue, Fred needs to know when it was last asked. Our system needs to manage the clues, questions, and answers to help Fred.
11. A given clue/question may have several different correct answers.
12. Cellphone texts are used to deliver clues to teams. A clue always first directs the team to a location. It either asks them a question about that location, or it specifies that they take some action to prove that they were at the location (buy something, take a photo, etc). If a question is asked, they only are given the next clue after a correct answer has been given. If they have to do something, they can get their next clue as soon as they indicate that they have completed the required activity. (And proof will be required at the restaurant.)
13. If an incorrect answer is given, the team is allowed to try again, any number of times.
14. Our system is going to be responsible for deciding on the random order of the clues given to each team, and it is responsible for sending the textmessage clues and checking the text-message answers received.
15. If teams are really stuck about a clue or a question, there is a number that they can call for help. This number is potentially different for each event, though often it might be Fred's phone.
16. The times at which each clue is delivered and each answer is sent are important. If the time gap between a clue and its answer (or the request for the next clue, in the case of an activity) is too small, we know the team has been speeding. It will be disqualified (although it will be allowed to finish the rally). If the gap is too large, a time penalty is assessed. Teams are permitted 2 minutes more than the driving time (as determined by Google Maps) between the location where the clue was given and the location where the answer is given. Longer time gaps cost 1 penalty point per minute.
17. Similarly, the Google Maps driving time between the restaurant and the location for the final clue is compared against the team's actual time. Time penalties or disqualification can result.
18. The location where a clue is delivered is either the starting location (for the first clue), or the location where the previous answer must have been given. We assume that teams don't move away from a location until they correctly answer the question (or do the requested activity) and thus get the next clue.
19. Calling the "I'm stuck" number costs a team 10 penalty points per call. The system being constructed should compute the number of penalty points per team. Someone from GBFF (probably Fred) will use this penalty, and whether the proofs of activities are deemed suitable - which is determined manually - to decide upon the top 3 teams. Exactly how the top 3 teams is determined is outside the scope of this system. It merely needs to track the penalty points.
20. All teams are to arrive at the restaurant by a certain time. Any who are still missing and who have not called the "I'm stuck" number to say they will be late will be assumed to be having mechanical trouble. Their license plate numbers will be given to the police by Fred, who has the police's phone number memorized. Fred has an easy time remembering phone numbers and other numeric facts. Usually Nancy at the police station would receive the call, and Fred likes Nancy.
Please using the ERD to complete database management, and also could use multivalued attributes and many-to-many relationships where they make sense.