Lecture 2

Question 1

Cardinality

Answer 1

The way tables are related. 3 types:

• One-to-one (or zero-to-one)
• One-to-many
• Many-to-many (M-to-N) often via a Bridge table.

Question 2

Bridge table

Answer 2

Decomposes the M-to-N table in to one-to-many relationships.

Question 3

CIF and Programming languages composed of

Answer 3

• Production (also called back-end)
• Assembly, logistics, and storage (includes Data-warehousing, is a DBMS)
• Processing, analysis and consumption (also called front-end)

Question 4

SQL basic form

Answer 4

Select [fields]
from [one or more tables]
Where [criteria]
Group by [product]
Having [criteria]

Question 5

Join

Answer 5

Generally composed of an equality comparison between foreign key and primary key of related tables.

Question 6

Different ways of creating joins (Venn Diagrams)

Answer 6

• Inner join (result is overlap)
• Left outer join (result is left)
• Right outer join (result is right)
• Full outer join (result is everything)
• Minus (result is left or right without overlap)

Question 7

Trends in the database world

Answer 7

1. From disk-based to in-memory databases (SAP HANA).
   History: CPU is relatively fast, memory is very slow. -> the further away from the CPU, the slower.
   Current/future: memory became cheaper, and larger. Therefor it is possible to store databases in-memory. However there is a technical movement -> instead of working in rows, they use columns, which makes it faster.
2. From local databases to cloud databases (Azure, Google Cloud etc.)

Question 8

Data warehouse

Answer 8

A database that is maintained separately from the organization’s operational databases for the purpose of managerial decision-making.

Question 9

Data warehousing

Answer 9

The process of constructing and using data warehouses.

Question 10

Why do we need a separate data warehouse?

Answer 10

1. Data quality: If you want to make use of the data, you want to have high data quality.
2. Historical data: The problem is time-related. Mostly, databases in the back-end do not have much historical data.
3. Data consolidation: You want to aggregate the data.

Question 11

Components of a data warehouse framework

Answer 11

1. Production
2. ETL
3. Data warehouse
4. Data marts
5. Meta data
6. BI Applications

Question 12

Production

Answer 12

Contains the different databases.

Question 13

ETL

Answer 13

Extract, Transform, Load. Within the component, you want to clean, convert, and link the data to improve the data quality.

Question 14

Data warehouse:

Answer 14

The data in this part is more ‘formed’ to support the information requirements for managers and decision-making. This data is cleaned, ordered and linked. -> Star and Snowflake modelling. This part does not have up-to-date data. There is a certain delay because of the ETL process.

Question 15

Data Marts

Answer 15

A subset of a data warehouse. There are less details, history and dimensions. It is user/group oriented.

Question 16

Dependent Data Mart

Answer 16

Dependent on the central warehouse.

Question 17

Independent Data Mart

Answer 17

Independent from the central warehouse and is directly linked to the ETL.

Question 18

Metadata

Answer 18

Data about the data (such as location, meaning, transformations etc.)

Question 19

BI applications

Answer 19

Connected with the data warehouse or data mart. Two trends:

• Integrated BI/BA-suits
• Enterprise information portals.

Question 20

What is a datawarehouse

Answer 20

• Subject oriented
• Integrated
• Time-variant
• Nonvolatile
  Collection of data in support of management’s decision-making process.

Question 21

Subject oriented

Answer 21

• Focussing on the analysis of data or decision makers not on on daily operations/transition processing.
• Providing a simple view around particular subjects by excluding data that are not useful in the decision support.

Question 22

Integrated

Answer 22

• Constructed by integrating multiple heterogenous data sources.
• Data cleaning and data integration techniques are applied.
  + Ensure consistency in naming conventions.
  + When data is moved to the data warehouse, it is converted.

Question 23

Time-variant

Answer 23

• Time horizon for the warehouse is significantly longer than that of operational systems.
  + Operational database
  + Data warehouse data: provide information from historical perspective.
• Every key structure in the data warehouse.
  + Contains an element of time.

Question 24

Non-volatile

Answer 24

• Operational updates of data do not occur in the data warehouse environment. This requires 2 operations:
  + Initial loading (SQL: Insert)
  + Access of data (SQL: Select)

Question 25

Different types of data warehouses

Answer 25

- Direct BI on source systems - Canned data warehouse (in ERP system) - Independent data marts - Bus architecture - Enterprise data warehouse - Hub & spoke - Federated data warehouse - Data lake

Question 26

Central data warehouse

Answer 26

A pure architect -> Top down -> Enterprise data warehouse approach. - Has one integrated warehouse. - Advantage is that it has a simple ETL process. - Entity-relational modelling. - Examples: Hub & Spoke and Federated data warehouse

Question 27

Federated architecture

Answer 27

Architecture is composed of data marts (no central data warehouse) -> Bottom up -> Canned data warehouse. - Start at the requirements of a single department, and after that they scale up. Data warehouse = collection of data marts. Typical feature = dimensional modelling. Cheap to build. - Dimensional modelling: consistency achieved by conformed dimensions. - Problems: + very much ETL has to be done. + low data consistency -> solution is data mart bus architecture, which links the different data marts by conformed dimensions. - Examples: Independent data marts, bus architecture, and the canned data warehouse are within this category.

Question 28

Data mart vs Enterprise data warehouse

Answer 28

No one-size fits all strategy to data-warehousing. Depends on: - Management's information needs -> when management want a central overview, the enterprise data warehouse approach is probably better. - Information interdependence between organisational units.