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

# Problem I. 251. (November 2010)

I. 251. The files eromu.txt, kapcsolo.txt and futoanyag.txt (tabulator separated text files in UTF-8 encoding with field names in their first lines) contain data about Hungarian power plants.

[1.] Prepare a database with name i251. You should import the above 3 files into this database without changing their names.

[2.] Upon importing, you should set the appropriate data formats and keys. No new fields should be created in the tables.

Tables

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.

table

[3.] By using a query, list in alphabetical order the names of renewable energy plants (that is, fueled by biomass or water). (3megujulo)

[4.] Which power plant has the largest power? List its name and power. (4legnagyobb)

[5.] Determine by a query which power plants are fueled by the same method as Mátrai Erőmű''. (5matrai)

[6.] For each fuel type, determine the total power of the power plants supposing that only that fuel type is available. This list of fuel types and total power should be sorted into decreasing order according to power''. (6tipusonkent)

[7.] Make a query to list the number of power plants put in operation in each decade. (7szakaszok) For example, the 60s'' denote years between 1960 and 1969.

[8.] The side-effect of fuelling a power plant with brown coal is the production of a large amount of carbon dioxide, moreover, they have little efficiency, so these power plants will be abolished. To maintain the database, you should create 3 deletion queries assuming consecutive execution. You do not have to execute the deletion queries, but it not forbidden.

[$\displaystyle a.$] By using a query, delete records from table kapcsolo which had any connection to using brown coal. (8barna_1)

[$\displaystyle b.$] Delete the record of brown coal from table futoanyag (8barna_2)

[$\displaystyle c.$] Delete from table eromu all data in connection with power plants to be abolished due to the fact of having no other fuel sources. (8barna_3)

The database (i251.odb, i251.mdb) together with a short documentation (i251.txt, i251.pdf) -- also containing the name and version number of the database application -- should be submitted in a compressed file (i251.zip).

(10 pont)

Deadline expired on December 10, 2010.

Sorry, the solution is available only in Hungarian. Google translation

Megoldásokról

Az érettségi követelményrendszerénél kicsit nehezebb, de annak sajátságait magán viselő adatbázis-kezelési feladatra kevés száz százalékos megoldás érkezett.

A megoldások nem készíthetők el maradéktalanul QBE-rács használatával, hanem SQL nyelvű lekérdezések írására is szükség van. A két módszernek más-más nehézsége, illetve előnye van. A logikailag összetettebb feladatok megoldását könnyebb elkészíteni a lekérdező nyelvvel.

A legtöbb hiányosság az al-, illetve a segédlekérdezések használatánál volt.

A javítási útmutató az SQL nyelvű minta megoldásokat adja meg a könnyebb és jobb dokumentálhatóság miatt.

Javítási útmutató: eromuvekjav.pdf