Dear learning community,
as many of you asked for some more details on the correct answers for the closed assignments or access to the past questions if you missed them, we decided to put the solutions online in the respective week.
If there are any questions left, please do not hesitate to ask in the forum.
Best regards,
Ralf for the IMDB Teaching Team
Question Name: SIMD
Text: SIMD - Single instruction, multiple data...
Correct answer:
- describes computers that perform a single operation on multiple data items in parallel
Incorrect answers:
- is a processor optimized for single instructions
- is only required for computer graphic operations
- is an outdated programming language
General explanation: Because this question asks for the definition of an abbreviation, there is not much to explain. As the question text already hints, the correct answer is that SIMD describes computers that perform a single operation on multiple data items in parallel. It is not only required for computer graphic operations and it is not an outdated programming language, either. The answer that it is a processor optimized for single instructions does not cover the whole meaning of SIMD and additionally, SIMD describes an architecture, not a specific processor. Therefore, this answer is wrong, too.
Question Name: Message passing
Text: What are potential bottlenecks in a message passing system?
Correct answer:
- The bandwidth of the network connection that connects the distributed processors
Incorrect answers:
- The size of the shared memory storing the messages
- The number of processors is limited since each processor has to know all others
- The central processor, which coordinates all other processors
Question Name: Rare Look-Ups in a Column
Text: If a column is very rarely used to identify or look-up specific tuples...
Correct answer:
- … we probably don’t need an index on top of it, but use a full column scan
Incorrect answers:
- … an index on top of it is a "must-have"
- … we cannot use a full column scan
- ... we should delete it
General explanation: This question is basically about optimization and judgement in actual use cases. Indexes need additional memory and have to be maintained, so their benefits come at the cost of additional overhead and complexity. Therefore, they are normally only used on frequently accessed columns. A full column scan without an index is a vbit more expensive with regards to computational cost, but this is acceptable if the column is only used seldomly. So this is the correct answer. With that, the option "we cannot use a full column scan" is clearly wrong, and because we should never delete any information from our database, the last option is obviously wrong, too.
Question Name: Aggregate Function
Text: Which of the following is an aggregate function?
Correct answer:
- Average (AVG)
Incorrect answers:
- Scan (SCN)
- Lookup (LOOK)
- Group by (GROUP)
General explanation: There are no distinct operations "scan (SCN)" or "lookup (LOOK)" in SQL. The group by clause in a SQL statement is used to select the attributes which determine the groups on which the aggregates are built. So the only valid aggregate function listed here is average (AVG).
Question Name: Aggregation - HAVING
Text: The HAVING clause is used to express...
Correct answer:
- additional filter criteria based on aggregated values
Incorrect answers:
- that the aggregate function shall be computed for every distinct value (or value combination) of the specified attribute(s)
- the sort order of values in the result set
- the number of subsets the results should be split into
General explanation: The HAVING clause acts like sort of a WHERE clause on SETs. It checks for characteristics of the set as a whole and therefore has to take the results of aggregate functions into account for these checks. The other answers are wrong. The clause that determines on which groups sharing the same distinct values the specified aggregate functions should be computed is the GROUP BY clause. THe sort order is expressed by the SORT BY clause and the number of subsets into which the result is split is not determined by an additional specific clause, but it is a result of the GROUP BY clause.
Question Name: Cache Invalidation
Text: What happens to a cached aggregate in case of invalidations in the main storage? The cached aggregate is...
Correct answer:
- ... revalidated using the bit vector of the main partition
Incorrect answers:
- ... revalidated using the bit vector of the differential buffer
- ... revalidated using the bit vector of the main partition and differential buffer
- ... evicted and will not be cached again
General explanation: The goal of the aggregate cache is to reuse the previously cached aggregate and therefore the bit vector of the main partition is used to perform an efficient incremental revalidation.
Question Name: Enterprise Simulations and Aggregate Cache
Text: Why does the aggregate cache accelerate certain queries for enterprise simulations?
Correct answer:
- In each simulation, only a few parameters are changed. Therefore, all untouched queries can be answered using the aggregate cache
Incorrect answers:
- For a drill-down into a finer granularity, e.g. drill-down from year to a monthly view, cached aggregates can be reused.
- No, the aggregate cache does not accelerate queries because the data is already based on materialized views.
- The aggregate cache always returns outdated data. Therefore, the costly recalculation to include new inserts is not required, which in turn speeds up the whole process.
General explanation: Based on the fact, that in each simulation, only a few parameters are changed, many partial results stay the same. The composition of the final result therefore is significantly sped up by using the unchanged existing results. If we would need a finer granularity, the aggregate cache would most likely have little to no effect, since no intermediate result will be reusable. The answer mentioning materialized views is wrong since we do not rely on materialized views any longer, and the aggregate cache is a completely different concept. Last but not least, the aggregate cache does not return outdated data, rendering the answer mentioning a speed advantage on the basis of omitting a recalculation wrong.