Problem I. 338. (January 2014)
I. 338. The most complex event at the women's athletics program is the heptathlon. The women's heptathlon consists of the 100 m hurdles, the 200 m and the 800 m races, the high jump, the long jump, the shot put and the javelin throw. The scoring system is based on an international scoring table. To convert an athlete's performance X (measured in the appropriate units) into points (= ``pont''), one uses the following formula pont=[a.|x-b|c], where [ ] is the floor function and | | is the absolute value. The constants a, b and c have different values for each of the events and are determined by the International Association of Athletics Federations. The constants are displayed in the following table.
For example, if an athlete performs 180 cm in high jump, then she gets 978 points for this event.
The results of heptathletes at the 2012 Summer Olympics for each of the events and the data for computing their scores are available in the files versenyzo.txt, eredmeny.txt, vszam.txt and nemzet.txt. (Here ``versenyzo'' refers to the athlete names, ``eredmeny'' means ``result'', ``vszam'' is the event type, and ``nemzet'' is nationality.) The tabulator-separated and UTF-8-encoded text files contain the field names in their first lines.
1. Create a database named hetproba (= ``heptathlon''). The above data files should be imported into the database with the same names versenyzo, eredmeny, vszam and nemzet.
2. You should set the appropriate data formats and keys upon loading. You should not create new fields in the tables.
You should answer the following tasks. Only the requested data should appear for each query, and nothing else. Your solutions should be saved by using the names in parentheses.
3. By using a query, determine who won the high jump event. The result should contain the athlete's name, her result and its physical unit. (3magas)
4. Make a query to list the name (= ``név''), result (= ``eredmény'') with the physical unit, and the computed points (= ``pontszám'') of the athletes scoring more than 1000 points in long jump, according to the example. (4tavol)
5. Some countries could send more than one athlete for the heptathlon events. List the number of athletes from each country. The list should be sorted in a decreasing order according to the number of athletes. (5nemzetdb)
6. By using a query, list the names of the athletes (sorted according to their national identifiers) who have valid results in all of the 7 events. (6teljesek)
7. By using a query, list for each event the number of athletes having valid results. (7szamonkent)
8. By using a query, give the results of the Hungarian athlete. The list should contain the event name, her results with the physical units, and her points. (8magyar)
9. Determine the final results of the heptathlon games. The list should contain the athlete's name, her country identifier and her total number of points, and should be sorted in a decreasing order according to the total number of points. (9vegeredmeny)
The database (hetproba.odb, hetproba.mdb, ...) together with a short documentation (i338.txt, i338.pdf), also describing the name and version number of the database application, should be submitted in a compressed file (i338.zip).
Deadline expired on February 10, 2014.
Sorry, the solution is available only in Hungarian. Google translation
Példa megoldások: hetprobajav.pdf
Kelkó Balázs, 9. évfolyamos tanuló (Komárom, Jókai Mór Gimnázium) munkája: hetproba.accdb