Consider the table:
STUDENTATHLETE(StudentID, StudentFName, StudentFName, Campus, Gender, CoachID, CoachFName, CoachLName, TrackMeetDate, TrackMeetLocation, RunEvent1, RunEvent2, RunEvent3, FldEvent1, FldEvent2, RunEvent1Time, RunEvent1Place, RunEvent2Time, RunEvent2Place, RunEvent3Time, RunEvent3Place)
Assume you are assisting the coaching staff by converting a spreadsheet of trackmeet data to a database, to track results for each event in the locally-hosted track meet. The coaches plan to use the database to record data for all future track meets in other locations as well. Student athletes may participate in (minimum 0, max 3) running events and (minimum 0, max 2) field events. Each event has a dedicated coach assigned to it. A coach does NOT coach multiple events. Your database should track the student winners in first through third place for each event. A snapshot of the Excel spreadsheet is provided.
1. Write a functional dependency that expresses the fact that a single Coach oversees one event.(i.e. a longjump coach, a polevault coach, a mile relay coach) A coach does NOT coach multiple events.
2. Write a functional dependency that expresses the fact that a student athlete can participate in 1, 2 or 3 running events and 1 or 2 field events. Show me a functional dependency for running events and a separate functional dependency for field events.
3. Write a functional dependency that expresses the fact that if you tell me which student and which track meet, I can tell you what running events and field events he participated in.
4. Identify one determinant of Campus.
5. Write a functional dependency that expresses the fact that for each event (running or field) first, second and third place winners and their respective times/distances will be stored.
6. Transform STUDENTATHLETE into a set of tables in BCNF and 4NF.
7. Indicate the primary keys in each table.
8. Properly format the foreign keys in each table.
9. Document all referential integrity constraints with the proper SQL "must exist" commands.
10. Assume your tables hold historic data from last year's meets. Write the appropriate SQL to confirm that the firstplace winners in the RESULTS table are also in the STUDENTATHLETE table. (You can make up your own table names. You don't have to use mine.)