Problem I. 242. (May 2010)
I. 242. In problem I. 237 you created a data table of the 185-year-old Hungarian Academy of Sciences. After processing these tables, a list of members in 2007 together with some additional data is available in the files szemely.txt, kapcsolo.txt and foglalkozas.txt. These files are tabulator separated text files in UTF-8 encoding with field names in the first list.
[1.] Create a new data base named mta. The above 3 data files should be imported into the data base using the same names szemely, kapcsolo and foglalkozas.
[2.] Upon importing, you should set the appropriate data formats and keys. No new fields should be created in the tables.
The structure of the tables is the following:
szemely (id, nev, szul, nemzetiseg, tipus, mettol)
|
kapcsolo (szemely_id, foglalkozas_id)
|
foglalkozas (id, nev)
|
Now solve the following task. When a query is answered, no other data, only the requested results should appear. Queries should be named as indicated in the parentheses.
[3.] By using a query, list in alphabetical order the names and nationality of the mathematician academicians. (3matematikusok)
[4.] Make a query that counts the number of members for each membership type. (4tipusdb)
[5.] List in a decreasing order how many academicians pursue each occupation.\ (5szakmadb)
[6.] By using a query, give the name of the youngest full member and his or her age (at the moment of executing the query). (6fiatal)
[7.] By using a query, give the name of the academician who became a full member when he or she was the youngest, give his or her age at that time and what his or her occupation is. (7koran)
[8.] By using a query, give the other occupation(s) of the geologist academicians. Each occupation should appear only once in the list. (8geo)
[9.] List those people who have the same occupation as Vilmos Csányi. (9csanyi)
[10.] Hungarian scientists have the nationality rubric blank. By using a query, fill these up with the word ,,Hungarian''. (10magyar)
The database (mta.odb, mta.mdb) together with a short documentation (i242.txt, i242.pdf) -- also containing the name and version number of the database application -- should be submitted in a compressed file (i242.zip).
(10 pont)
Deadline expired on June 10, 2010.
Sorry, the solution is available only in Hungarian. Google translation
Megoldásokról
Az érettségi követelményrendszerének megfelelő adatbázis-kezelési feladatra több, majdnem száz százalékos megoldás érkezett.
A megoldások elkészíthetők QBE-rács használatával és SQL nyelvű lekérdezések írásával is. A két fajta feleletalkotási módot egyenértékűnek tekintjük. A két módszernek más-más nehézsége, illetve előnye van.
A javítási útmutató az SQL nyelvű minta megoldásokat adja meg a könnyebb és jobb dokumentálhatóság miatt.
Mintamegoldás Kozma Bálint (Eger, Gárdonyi Géza Ciszterci Gimnázium) 11. osztályos tanuló megoldását közöljük: mta.mdb
Javítási útmutató javitasiutmutato.pdf
Statistics:
10 students sent a solution. 10 points: Balla Attila, Kozma Bálint. 9 points: Debreceni Bálint, Horváth 135 Loránd, Janosov Milán, Nánási József, Sagmeister Ádám, Szabó 928 Attila. 8 points: 1 student. 7 points: 1 student.
Problems in Information Technology of KöMaL, May 2010