Problem I. 293. (April 2012)

I. 293. Radio channels are broadcast from several stations throughout the country. In Hungary, May 2010, this was the topic of the database management task of the informatics matriculation exam in English. However, that exam was based on the data collected in 2009. Radio channels and broadcasting data changed since then. In this exercise, you are going to investigate these changes. The files aregi.txt, auj.txt and telepules.txt contain the broadcasting data for 2009 and 2012, further, the location data, respectively.

[1.] Create a new database with name i293. Import the data tables into the database with names aregi, auj and telepules. These txt data files are tabulator separated files in UTF-8 encoding, with field names in the first lines. You should add a unique identifier azon to the tables aregi and auj.

[2.] After importing, you should set the appropriate data formats and keys.

Tables:

aregi (azon, frekvencia, teljesitmeny, csatorna, adohely, cim) containing data from 2009

azon   identifier of the actual element of the frequency list (counter), this is the key;

frekvencia   the broadcasting frequency in MHz (number);

teljesitmeny maximal broadcasting power in kW (number);

csatorna   name of the radio channel (text), or empty if there is currently no broadcast;

adohely   name of the settlement containing the station (text);

cim   location of the station within the settlement (text), or empty if this is the only station there auj (having the same field names and types as table aregi) containing data from 2012;

telepules (nev, megye)

nev   name of the settlement (text), this is the key (and has all the settlement names in the task);

megye  name of the settlement's county (text).

Radio stations are grouped into 3 categories according to the table. Kategória is category, Sugárzási teljesítmény is broadcasting power, helyi is local, térségi is regional, országos is national. ``0,1 kW és alatta'' is ``0.1 kW or below'', ``0,1 és 1 kW között'' is ``between 0.1 and 1 kW'', while ``1 kW és fölötte'' is ``1 kW or above''.

You should solve the following tasks. When a query is answered, no other data only the requested results should appear. Queries should be saved as indicated in the parentheses.

[3.] By using a query, list those settlements from which the channel ``MR1-Kossuth Rádió'' is broadcast in 2012. Each settlement should appear only once and in alphabetical order in the list. (3kossuth)

[4.] List those settlements from which only one channel was broadcast in 2009. (4egy)

[5.] Create a query to list those channels in 2009 that are no longer broadcast this year. (5megszunt)

[6.] By using a query, list those stations, locations and frequencies where the name of the channel changed. Your list should contain the frequency, the old and the new channel names, the settlement name and the location. (6valtas)

[7.] By using a query, list those settlements where a station was created since 2009. (7ujak)

The following exercises should be based on the current (year 2012) data.

[8.] Create a query to determine for each county the number of settlements having a station. (8megyenkent)

[9.] By using a query, give the name of the regional channel (see the table about broadcasting powers) which is broadcast from the most stations. (9nepszeru)

[10.] By using a query, list the names of those settlements that simultaneously broadcast local, regional and national channels. Each settlement should appear only once in the list. (10vegyes)

[11.] Create a query to determine for each settlement (település in the table) the number of local, regional and national channels. Channels with unknown broadcasting power or channel name should be ignored. (11sugarstat)

Your database (i293.odb, i293.mdb, ...) together with a short documentation (i293.txt, i293.pdf, ...) - also describing the name and version number of the database application - should be submitted.

(10 pont)

Deadline expired on May 10, 2012.

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

Megoldásokról:

A feladat az emelt szintű informatika érettségi adatbázis-kezelési feladatánál egy kicsit nehezebb volt. 100

Mintamegoldásként egy lekérdezés gyűjteményt: radiomego.pdf és Kucsma Levente István 9. osztályos (Eger, Dobó István Gimnázium) tanuló adatbázisát: i393.mdb közöljük.

Statistics:

8 students sent a solution.
10 points:	Kucsma Levente István.
9 points:	Antal János Benjamin, Fényes Balázs, Jákli Aida Karolina.
8 points:	1 student.
7 points:	2 students.
3 points:	1 student.

Problems in Information Technology of KöMaL, April 2012