Mathematical and Physical Journal
for High Schools
Issued by the MATFUND Foundation
Already signed up?
New to KöMaL?

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)

id Identifier of the member of the Academy (number), this is the key;
nev Name of the academician (text);
szul Year of birth of the academician (number);
nemzetiseg His or her nationality -- can be multiple (text);
tipus Type of membership -- external, honorary, corresponding or full (text);
mettol Starting year of the latest membership type (number);

kapcsolo (szemely_id, foglalkozas_id)

szemely_id Personal identifier (number), key;
foglalkozas_id Identifier of the occupation (number), key;

foglalkozas (id, nev)

id Identifier of the occupation (number), this is the key;
nev Name of the occupation (text)

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