Data Management - Important Facts

Question 1

What is a data model?

Answer 1

A simple representation of a complex real-world object or event.

Or a simple representation of a data structre and its relationships.

Question 2

What is functional dependence?

Answer 2

If attribute A determines the value of all the rows for attribute B.

Emphasis on all.

Question 3

What is full functional dependence?

Answer 3

If the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that composite key, the attribute (B) is fully functionally dependent on (A).

Question 4

What is entity integrity?

Answer 4

That a primary key attribute can not have a row with a null value.

Each row is uniquely identified by the primary key.

Question 5

What is referential integrity?

Answer 5

All values of a foreign key refer to existing values in another table.

Question 6

What is data normalization?

Answer 6

A process for evaluating and correcting table structures to minimize data redunandcies, thereby reducing the likelihood of data anomalies.

-> Dividing everything into seperate tables as much as possibile to minimize redundancies.

Question 7

How does a DSS look like from an architectual viewpoint?

Answer 7

1. Model based management system that is connected to
2. A knowledge engine
3. A graphical user interface.

Question 8

What are mandatory requirements for a datawarehouse process?

To turn operational data into information that can be used to support decision-making?

Answer 8

1. Accesibility to users that are not familiar with data
2. Intergration of data on the basis of a standard model
3. Query flexibility to maximize the obtained advantages from existing information
4. Information conciseness to allow for target-oriented and effective analyses
5. Multidimensional representation to give users a manageable view of information
6. Correctness and completeness of integrated data

Question 9

What are three features of a data warehouse?

Answer 9

1. It is subject-oriented
2. It is integrated and consistent
3. It shows its evolution over time and it is not volatile

Question 10

What are the three main differences between an operational database and a datawarehouse?

Answer 10

1. Operational data covers a short period of time, whereas a datawarehouse covers a period of time.
2. Data in operational databases can be added, removed or modified. This does not happen in a data warehouse.
3. Operational queries focus on a small number of tables and their relations. Data warehouse queries focus on huge amounts of data and queries.

Question 11

What are 5 architecture properties that are essential for a data warehouse system?

Answer 11

1. Separation
2. Scalability
3. Extensibility
4. Security
5. Administerability

Question 12

Which main five types of system are distinguished in scientific literature that also include the beforementioned layers?

Answer 12

1. Independent data marts architecture
2. Bus architecture
3. Hub-and-Spoke architecture
4. Centralized architecture
5. Federated architecture

Question 13

What are five advantages of the two-layer architecture?

Answer 13

1. Good quality information is always available, even if access to the sources is unavailable.
2. Analysis queries do not affect the management of transactions
3. Data warehouses are logically structured according to the multi dimensional model
4. OLAP system have historical and summarized data as well - so you avoid a mismatch in terms of time and level of detail
5. Data warehouses can be designed specifically to optimize performance

Question 14

What are the advantages of the added ‘reconciled data’ layer?

Answer 14

1. It creates a common reference data model for a whole enterprise.
2. Sometimes the reconciled layer can be used to better accomplish operational tasks (instead of using the operational sources)

Question 15

When does ETL happen?

Answer 15

When a data warehouse is populated for the first time. After that, it occurs every time the data warehouse is regularly updated.

Question 16

What are the main data cleansing features in ETL and what do they do?

Answer 16

1. Rectification (Rectify mistakes)

2. Homogenization (Make them similar)

Question 17

Which three types of data extraction are possible?

Answer 17

1. Static extraction
   - Making a snapshot of operational data, done when you populate the data warehouse for the first time
2. Incremental extraction
   - Used to update data warehouses. Based on a log maintained by the operational DBMS
3. Source driven extraction
   - You rewrite your operational applications to give notifications of all the data changes

Question 18

What are the three main transformation processes in the transformation phase?

Answer 18

1. Conversion and normalization that operate on both storage formats and units of measure to make data uniform.
2. Matching that associates equivalent fields in different sources
3. Selection that reduces the number of source fields and records.

Question 19

Which two ways of loading (into the data warehouse) exist?

Answer 19

1. Refresh
   Replacing al the older data, rewrite the database completely. Normally done at the beginning
2. Update
   Only add the changes in data. Normally done to update.

Question 20

How could you represent the sales cube with the relational model schema?

Answer 20

You start with the fact followed by the used dimensions into brackets and underlined, and then the measures:

SALES(store, product, date, quantity, receipts)

First three are underlined.

Question 21

How could you represent the dependency of the sales cube?

Answer 21

The primary key, built up from the dimensions determine the measures.

store, product, date -> quantity, receipts

Question 22

What is the navigation path in OLAP?

Answer 22

An analysis process for facts acording to different viewpoints and at different levels of detail.

This path is turned into a sequence of (sequential) queries, each referencing their previous queries.

Question 23

What is the main problem with ROLAP implementations?

Answer 23

Performance hits caused by join operations between large tables.

Question 24

What is an aggregate navigator?

Answer 24

Component in the multidimensional engine that selects a view from among all the alternatives to solve a specific query at the minimum access cost.

Question 25

What are the benefits of HOLAP?

Answer 25

Combines both ROLAP as MOLAP. Takes advantage from standardization and the ability to handle large data from ROLAP, but the query speed from MOLAP.

Largest amount of data stored in a relational datbase, and a multidimensional model for the most frequently used data.

Question 26

What are 7 properties that characterize quality?

Answer 26

1. Accuracy
2. Freshness
3. Completeness
4. Consistency
5. Availability
6. Traceability
7. Clearness

Question 27

Which 5 types of querying are there in a multidimensional database?

Answer 27

1. Slice-and-dice
2. Drill-down and roll-up
3. Drill-across
4. Ranking
5. Rotating

Question 28

When is a dimension hierarchy considered onto?

Answer 28

If the hierarchy is balanced

Question 29

When is a dimension hierarchy irregular?

Answer 29

If a lower level is not connected to the middle level for all data points

Question 30

When is a dimension hierarchy considered covering?

Answer 30

If no containment paths skips a level.

Question 31

When is a dimension hierarchy considered strict?

Answer 31

If no dimension value has more than one direct parent

Question 32

What is a conformed dimension?

Answer 32

In data warehousing, a conformed dimension is a dimension that has the same meaning to every fact with which it relates.

Conformed dimensions allow facts and measures to be categorized and described in the same way across multiple facts and/or data marts, ensuring consistent reporting across the enterprise.