Problem I. 368. (February 2015)

I. 368. In this task we create a database containing and comparing several CPU features from different catalogues to help our friend make his decision about buying a new computer.

Create a new database with name i368. In the file ixyz.zip (downloadable from our web page) you will find the UTF-8 encoded and semicolon-separated text files cpu.csv, ceg.csv and arlista.csv. The first line of each file contains the field names according to the following description. You should import the files and create the tables cpu, ceg and arlista, then set the appropriate types and keys.

Tables:

The table arlista connects the processor data with the store data: each product appears only once in each store, and has the same price in a given store.

When solving the following tasks, save the queries, tables and reports by using the names in parentheses. Only the required fields and given names should be used.

Based on the 3 benchmark tests we want to know which processors are the fastest. Instead of averaging the averages of these three quantities (falling into different ranges), we do the following. For each of the 3 speed data series we select the maximum, then compare every value to their corresponding maximum.

1. To perform the comparison, first create a query that determines the maximum speed values with names MBM1, MBM2 and MBM3 in the corresponding categories. (1maxbm)

2. Then make a query that contains the id's of the processors, the 3 speed values divided by the corresponding maximum values and named SBM1, SBM2, SBM3, and a performance field, being the average of the previous three quantities. (2teljes)

3. Create a query to give the processor and socket types and the performances, sorted according to decreasing performance. (3sorban)

By decreasing the processor feature size, the energy consumption generally decreases. At the same time, the energy consumption of more complex CPUs with more cores or operating at higher clock rates or with integrated graphics processing units is higher than that of simpler CPUs. Verify to what extent the above general statements are true by examining the given data.

4. Add a new logical field GP to the table cpu, and create a query modifier to determine whether there is an integrated graphics processing unit in the processor. (4gp)

5. By grouping according to the number of cores, threads and the feature size, give the number of processors without integrated graphics units and their average energy consumption in each of the above groups by using a query. (5fogyaszt)

6. Make a query to give the processor type, performance and energy consumption for CPUs having at least 0.5 GHz difference between their maximum achievable and average clock rates. (6turbo)

7. Let us consider the CPUs with performance at least 60%. Make a query to give the type of the first 3 processors with minimum energy consumption, together with the stores where one can buy them. (7kisfogy)

After receiving the above information, our friend wants to buy an AMD processor with 8 cores, and he would like to know the prices of these CPUs in the stores located in the 4th and 13th districts.

8. Make a query to list the processor types, performances and prices based on the above criteria, together with the store names, postal code and address. (8amd8)

Now our friend would like to extend his search by considering not only the two districts listed above, but also any web stores.

9. By using a query, give the name of the store where one of the chosen devices has the lowest price among the chosen processors. Also give the CPU type and price. (9web)

Finally, our friend thinks it would be worth investigating other CPUs as well, having a similar price: what if one finds a much better CPU only for a little more money?

10. Create a report to display, for the CPUs within the price range HUF 30000-40000, all the CPU types, performances, the type of graphics units, prices, and the store names where these processors are available. The report should be grouped according to processor types, and, within this, sorted according to increasing price. The report title should be ``Some more CPUs for HUF 30000-40000''. The heading above the data should contain the words ``Type, Price, Store name, Performance, Graphics''. The report size should be A4 with portrait orientation. All fields and values should be completely visible. If needed, make a query to collect the necessary data. (10többi)

Source: the processor data were taken from the January 2015 issue of the CHIP Magazine.

The database containing your solution or the text file containing the SQL queries (i368.odb, i368.accdb, i368.sql) should be submitted in a compressed file (i368.zip), also containing a short documentation (i368.txt, i368.pdf) and specifying the name and version number of the database application.

Downloadable file: ixyz.zip

(10 pont)

Deadline expired on March 10, 2015.

Statistics:

11 students sent a solution.
10 points:	Dombai Tamás, Fényes Balázs, Kelkó Balázs, Kiss 107 Ádám, Kovács 246 Benedek, Kovács Balázs Marcell, Mócsy Miklós.
9 points:	Kazal Soma.
8 points:	1 student.
6 points:	1 student.
3 points:	1 student.

Problems in Information Technology of KöMaL, February 2015