2.1 Raw data The data used to build the tables for this assignment are based on the 2023 Walt Disney World Marathon's Military members participation. The results are based on the PDFs in the files. However, the data from the PDFs have been updated and are described below. Date: 2/21/2023 Time: 5:29:41 P Dw Place Name MEN-MILITARY 1 JOSEPH CRAIG 2 GARRETT GROVE Date: 2/21/2023 Time: 5:29:41 P DN Place Name File Preview WARATHON WEDNESDA WOMEN-MILITARY 1 ABIGAIL BAHLAU 2 JESSICA ANDERSON 3 CHRISTINE KENDZIOR 2023 Walt Disney World Marathon Race Num Age 30012 26 State Far 11 78 29 73 2023 Walt Disney World Marathon Hace Num Age Place Gender Clock Net Place Time Time 101 20 193 20406 33 473 41 383 11 2:48.50 2:48:48 Space Force 64 3:20:34 3:18:01 Air Force Place Gender Clock Place Time Matary Branch 35 51 94 Net Mitary Time Dranch 3.34.25 3.34.00 Navy 3.43.34 3.41.22 Coast Guard 3:51:31 3:49.16 Army The raw data in the input file, wdwM-military-23.csv, has CSV data separated with a semi- colon (;) in the following order with the corresponding data types. One participant's data will be on each row of data in the input file. NB: The first row of the CSV contains the name or description for every field in all the following rows. label name place gender first name last name bib # age place gender place clock time net time branch distance Table 2.1: Input data:Raw data field names & their data types data type integer character character character character integer integer integer comments HH:MM:SS Official start of the race aka gun time HH:MM:SS Time that the sensor recognizes racer crossing the start line character Army, Navy, Marines, Air Force, Space Force, Coast Guard USA, USN, USMC, USAF, USSF, USCG marathon, half marathon, 5K, 10K, 17.75K, etc. character Notes: 1. The data types are recognizable for most languages. However, there are SQL Specific data types appropriate to the types listed above. Make sure to use the appropriate SQL data types. This is especially true for time. 2. The data fields, branch and distance must be in another appropriately named data table. • branch could be represented using the following acronyms: USAF, USN, USMC, USCG, USSĘ, USA. The branch could be the primary key, and would contain the full name in the record. (This would minimize table sizes of the user data. As a side note there are over 30,000 participants in this race. Hint: This database may be used in future topics and assignments.) • distance could be represented in a table containing the acronym or nickname, the distance in both metric and English numerical units of measure. For example, the marathon is 26.2 miles or 42.195 kilometers. This will facilitate calculations of minutes per mile or kilometer. 3. Make sure to create the database for this assignment to be used for your NID MySQL account. 2.2 Tables Given the input text file contains all the individual competitors user data, it should be organized to optimize the overall table size and to enable quick queries and calculations. The structures for the tables should follow the following outline: 1. Competitor specific data, i.e. place, gender, first name, last name, bib #, age on race day, overall place, gender place, clock time, net time, branch, & distance 2. A table containing the race distance name, metric & English distances. 3. A table containing the military branch acronym and full name, i.e., USN corresponding to US Navy, USA for US Army, etc. It is required that an Entity-Relationship Diagram be submitted as part of the assignment. (It might be helpful to pencil a quick diagram showing the ERD and then use the MySQL EEF tools to generate a more polished ERD.) This database may be used in future topics and assignments. 2.2.1 Relationships There are only two relationships required for this database, as discussed previously, they are described below. The data fields, branch and distance must be in another appropriately named data table. • branch could be represented using the following acronyms: USAF, USN, USMC, USCG, USSF, USA. The branch could be the primary key, and would contain the full name in the record. (This would minimize table sizes.) • distance could be represented in a table containing the acronym or nickname, the distance in both metric and English numerical units of measure. For example, the marathon is 26.2 miles or 42.195 kilometers. This will facilitate calculations of minutes per mile or kilometer. 2.3 Exercises 2.3.1 Queries The following queries need to be written. 1. The 10 fastest females in this race, based on net time. 2. The 10 fastest males in this race, based on net time. 3. The 5 fastest in each gender for each of the military branches. (If there aren't 5 females (or males) in the USCG, then only report the ones in the database.) 4. The 10 runners, regardless of gender or branch, who had the longest wait from the beginning based on a calculation of the difference between clock time to net time. Sorted from longest to shortest time. 5. The top 10, regardless of gender, based on their average minute per mile. The minuter per mile can be calculated in SQL with a bit of research. 3 Documenting the steps Each of the five queries outline above can be run from the MySQL command line. And a screenshot of the results will be sufficient proof of query completion. 3.1 Screenshots 1. Make sure to take a screenshot of the final results of each of the queries, from 1 thru 5. 2. Save the screenshot in a file named YourNID-QryX where QryX is the query number. Submit the screen shots from each step identified above as part of your assignment. The following queries need to be screenshot. 1. The 10 fastest females in this race, based on net time. 2. The 10 fastest males in this race, based on net time. 3. The 5 fastest in each gender for each of the military branches. (For example, if there aren't 5 females (or males) in the USCG, then only report the ones in the database.) 4. The 10 runners, regardless of gender or branch, who had the longest wait from the beginning based on a calculation of the difference between clock time to net time. Sorted from longest to shortest time. 5. The top 10, regardless of gender, based on their average minute per mile. The minuter per mile can be calculated in SQL with a bit of research. 4 Submission instructions You must submit this assignment in Webcourses as file uploads. Note that all submissions will be via Webcourses. Be sure to include the five query result screenshots and the MySQL EER screenshot of the ERD. It is preferred to ZIP your submissions. 5 Grading Scoring will be based on the following rubric:/n

Fig: 1