Problem I. 332. (November 2013)
I. 332. Data for the biggest trees in Hungary have been collected for more than 10 years in public databases (see, for example, http://www.dendromania.hu/index.php?old=foold). They contain the name of the species of the tree, its perimeter, the geographical coordinates and the year it was put in the database. Data from Somogy county are contained in the file fa.txt. The file hely.txt contains location information as the name and population of the corresponding nearby settlements. Both files can be downloaded from our webpage.
1. Create a new database named somogyifak. Import the files fa.txt and hely.txt with table names fa and hely into the database. The files are UTF-8 encoded, tabulator separated text files, with field names in their first lines. You should add a unique identifier with name id to the table fa, and set the appropriate types and keys.
The queries and the report in the solutions of the following tasks should be saved as specified by the names in parentheses. Only the requested fields should appear in the queries.
2. Make a query to display (in alphabetical order) the name of the species and the perimeter for trees with perimeter between 7 and 9 meters. (2vastag)
3. The size of forested areas and hence the number of large trees greatly vary from settlement to settlement. List by using a query the number of trees in the database for each settlement. (3feljegyzesek)
4. Create a query to determine the number of oak trees in the database for each settlement. The name of the settlement should also appear, and the list should be sorted in decreasing order according to the number of trees. If two settlements contain the same number of oaks, alphabetical order is used. (4tolgy)
5. For the tree with the largest perimeter in the database and by using a query, display its species name, perimeter, its year of recording and the name of its settlement. (5kover)
6. A certain species, ``barkócaberkenye'' (Sorbus torminalis) was chosen to be the ``Tree of the year'' in Hungary in 2000. By using a query, display the names of the species living in the same settlement as ``barkócaberkenye''. Every species should appear once in your list, but the name ``barkócaberkenye'' itself should not. (6berkenye)
7. There can be settlements with several notable trees. By using a query, display the settlements having more than 5 different tree species in their record. The name of the settlement, its population and the number of species should be included. The list should be sorted in decreasing order according to the number of species. (7gazdag)
8. By using a query, list (in alphabetical order) the settlement names simultaneously having oak and beech trees. Each settlement should appear once. (8egyszerre)
9. Create a parametric query to display the species name, perimeter and settlement name of the tree nearest to the point given on the map by longitude and latitude in minutes. Instead of spherical coordinates, the usual planar coordinates should be used (the distance between two points (x1,y1) and (x2,y2) is and differences between elevations should be ignored. The table contains a sample input (``Bemenet'') and the corresponding output (``Kimenet''); notice that the decimal separator is the ``comma''. (9kereso)
10. Create a report to display settlement names in Somogy county with huge fir trees (``Fenyők listája'' in the example). The report should contain the name of the settlement (``Település''), the name of the species (``Faj'') and the perimeter (``Körméret''). The list should be sorted according to the name of the settlements, and for each settlement, smaller trees should be listed first. The report should be made by using queries containing the appropriate fields, or by auxiliary tables. The text and the order of the fields in your report should be similar to the example; the formatting can be different. (10fenyo)
The database (somogyifak.odb, somogyifak.mdb, ...) and a short documentation (i332.txt, i332.pdf) also with the name and version number of the database application should be submitted in a compressed file (i332.zip).
Deadline expired on December 10, 2013.
Sorry, the solution is available only in Hungarian. Google translation
Megoldásokról: A feladat megfelelt az emelt szintű gyakorlati érettségi adatbázis-kezelő feladatának.
Egy lehetséges megoldás: somogyifakmego.pdf
Kovács Balázs Marcell, 11. évfolyamos tanuló (Budapest, ELTE Radnóti Miklós Gyakorló Iskola) munkája: somogyifak.odb
10 students sent a solution. 10 points: Kovács 246 Benedek, Kovács Balázs Marcell, Németh 123 Balázs. 9 points: Fényes Balázs, Radnai Bálint. 8 points: 2 students. 7 points: 1 student. 6 points: 1 student. 5 points: 1 student.