Problem I. 299. (September 2012)
I. 299. A spice company has introduced a new product line consisting of spice mixes. The name of the mixes, their recipes and data of the basic ingredients are stored in a database.
The database consists of three tables:
The relation between the tables is illustrated by the figure.
Create a database i299 and import data from the UTF-8 encoded text files with the same names as the tables. Remember to define the appropriate relations.
Create your queries answering the following questions.
[1.] List the names of those mixes that contain curcuma. (01kurkuma)
[2.] Give the ingredients of the Sichuan mix, together with their relative amounts in descending order. (02szecsuani)
[3.] Give which mix contains the largest number of components together with this number. (03legtobbfele)
[4.] The company distributes 100 free product samples of each mix, all samples weighing 100 grams. Calculate the total price of the spices needed. (04mintaar)
[5.] Due to a ship accident the ingredients Sichuan pepper and cardamom are temporarily unavailable. List the names of the affected mixes whose production should be halted. Each mix name should appear at most once. (05nemkeverheto)
[6.] Under the assumptions of the previous question, list those mixes whose production can be continued without interruption. (06keverheto)
[7.] Give those mixes that do not contain salt. In each case also list the component with the largest relative amount and its total mass in 1 kg of mix. (07sotlan)
[8.] Give a list of the common ingredients of the Mediterranean mix and ``Flekken'' mix. (Only the type of the spice matters, its amount is irrelevant.) (08mfkozos)
[9.] Give those ingredients that can be found in exactly one of the two mixes Grill and Piquant Grill. (Again, only the type of the spice matters.) (09grilldifi)
[10.] List those spice mixes that contain the most expensive spice. For each mix, also display the price of the spice in 1 kg of mix. (10draga)
The database (i299.mdb, i299.accdb, ...) together with a short documentation (i299.txt, i299.pdf, ...) - also containing the name and version number of the database application - should be submitted.
Downloadable files:
(10 pont)
Deadline expired on October 10, 2012.
Sorry, the solution is available only in Hungarian. Google translation
Két mintamegoldást is közzé teszek, Jákli Aidáét (Zalaegerszeg, Zrínyi M. Gimn.) és Kocsis 789 Mátyásét (Budapest, Berzsenyi D. Gimn.), az utóbbiban az UTF-8 kódolás problémája nem megoldott, de a lekérdezések megoldása mintaszerű. mintamegoldások.zip
Statistics:
17 students sent a solution. 10 points: Dobos-Kovács Mihály, Jákli Aida Karolina, Kocsis 789 Mátyás. 9 points: Debreceni Bálint, Fényes Balázs, Gema Barnabás, Kőrösi Nikolett, Lapincs Mátyás, Qian Lívia, Tilk Bence. 8 points: 1 student. 7 points: 3 students. 5 points: 1 student. 4 points: 1 student. 3 points: 1 student.
Problems in Information Technology of KöMaL, September 2012