Question

1. Which of the following best describes a dimension table in a star schema:

1. Most attributes will likely be text
2. Most attributes will likely be numeric
3. The primary key should be a single integer surrogate key
4. Both a. and c.

2. Which of the following best describes a fact table in a star schema:

1. Most attributes will likely be text
2. Most attributes will likely be numeric
3. The primary key should be a single integer surrogate key
4. Both a. and c.

3. Which of the following best describes an advantage(s) of a snowflake schema:

1. It slows down queries by forcing more joins between dimension tables
2. It allows facts to be stored at different granularities of a dimension in a multi-star schema (e.g. day and month granularities of a date dimension)
3. Facts that are at the lowest level of the dimension can be more easily summarized at higher levels of the dimension than they can with a pure star schema by precomputing aggregates
4. Both b. and c.

Transactions and Recovery:

4. Which of the following best describes the atomicity property in regards to transactions:

1. Either all actions in a transaction are executed or no actions are done
2. The DBMS changes from one consistent state to another consistent state when a transaction completes successfully
3. A transaction executes with the same outcome, without any impact from other transactions executing concurrently
4. The DBMS guarantees that changes are made persistently upon successful completion of a transaction

5. Which of the following statements are true:

1. Verifying transaction consistency is the job of the semantic data controller
2. Managing consistency of transactions operating in parallel is the job of the concurrency controller
3. Both a. and b.
4. Neither a. nor b.

6. Which of the following statements best describes a phantom read:

1. A select from one user calculating a summary or aggregate function (e.g. avg, max, sum, etc.) reads some rows from a table. At the same time another user is updating some of those same rows to contain different values. The first user reads some of the rows before the update changes them and reads some rows after the update changes them.
2. One user reads the same row more than once. Between the reads, an update from another user modifies the row.
3. One user reads a set of rows from a table. Another user inserts a row that matches the where condition read by the first user. The first user reads the same set of rows again getting the new row in addition to the rows that the user read the first time.
4. Two users update the same columns of the same row of the same table at the same time. One update will be overwritten by the other.

Answer 1

1.

Which of the following best describes a dimension table in a star schema

The primary key should be a single integer surrogate key.

So option (c) is correct.

2.

Which of the following best describes a fact table in a star schema

Most attributes will likely be numeric and the primary key should be a single integer surrogate key.

So option (d) is correct.

3.

Which of the following best describes an advantage(s) of a snowflake schema

It slows down queries by forcing more joins between dimension tables.

So option (a) is correct.

4.

Which of the following best describes the atomicity property in regards to transactions

Either all actions in a transaction are executed or no actions are done.

So option (a) is correct.

5.

Which of the following statements are true

Verifying transaction consistency is the job of the semantic data controller and managing consistency of transactions operating in parallel is the job of the concurrency controller.

So option (c) is correct.

6.

Which of the following statements best describes a phantom read

One user reads a set of rows from a table. Another user inserts a row that matches the where condition read by the first user. The first user reads the same set of rows again getting the new row in addition to the rows that the user read the first time.

So option (c) is correct.