Chapter 2 - Normalization

Question 1

Normalization

Answer 1

RAOC

• The process of organizing tables in a relational database
• Eliminates data redundancy
• Decreases record locking
• Increases efficiency in concurrency
• Reduces data anomalies

Question 2

Characteristics of a Normalized Database

Answer 2

DEIL

1. detailed data
2. data efficiently
3. data integrity
4. Results in a lot of tables
5. Excellent for managing our day to day activities

Question 3

Drawbacks of a Normalized Database

Answer 3

• Inefficient for data extraction, poor data analysis (Because it is stored in the most detailed format)
• stored in multiple databases more data is added queries become even more inefficient

Question 4

Dimensional Modeling

Answer 4

data warehouse databases are designed using the Dimensional Model while Relational databases (OLTP) are designed using the Entity Relationship (ER) model

Question 5

star schema

Answer 5

1. A single central table called a fact table
2. Fact table is surrounded by multiple tables called dimensions
3. A fact table represents a multi dimensional hypercube

Question 6

Shared or Conformed dimensions

Answer 6

Dimensions with connections to multiple fact tables are called shared or conformed
dimensions

Question 7

Snowflake vs Star schema

Answer 7

If you imagine multiple dimensions brought to 3NF with a central fact table you
end up with a Snowflake schema. If you take a Star schema and normalize it, you end up with a
Snowflake schema.

Question 8

Hybrid Schema

Answer 8

A hybrid schema occurs when you normalize only part of the design to accommodate shared
dimensions.

Question 9

Star vs Snowflake vs Hybrid

Answer 9

• Use the Star schema if you can
• Use the Hybrid schema if you need to share a dimension with multiple dimensions
• Only use the Snowflake schema for quick Proof of Concept projects
o Because the design is closest to your source database that is already in 3NF and will
therefore require less time to construct and will be easier to load data

Question 10

Granularity

Answer 10

the level of detail that data is stored in fact tables /dimensions.
The lower the level of detail, the larger the data amount in the fact table.

Question 11

Dimensionality

Answer 11

The dimensional granularity of fact table is dependent on the number of the dimensions connected to
the fact table.