CompTIA Data+ DA0-002 · Free study guide

Normalization, star schemas, and when denormalizing is the right answer

This supplemental guide connects two database-design ideas that can appear to contradict each other. Normalization eliminates harmful redundancy in transactional systems; dimensional modelling deliberately accepts some redundancy for analytical reads. The current V2 objective lesson is the source of truth for DA0-002 scope, while the normal forms here provide useful context.

The trade-off depends on workload. Normalization can reduce update anomalies in transactional models, while dimensional models can simplify and accelerate analytical access. Neither shape is universally optimal.

The first three normal forms

The current V2 objectives do not explicitly require the normal forms; these first three are useful database-design enrichment. Recognize what each prevents rather than treating the number as a rule for every system.

First normal form (1NF): atomic values, no repeating groups. Each cell holds a single value. The violation looks like a phone_numbers column containing 555-0100, 555-0101, or columns named item1, item2, item3. The fix is a separate row or a related table.

Second normal form (2NF): no partial dependencies. Applies when the primary key is composite. Every non-key attribute must depend on the whole key, not part of it.

A standard scenario is an order-line table keyed on (order_id, product_id), carrying product_name and product_category. Those depend on product_id alone, not on the combination — a partial dependency. The consequence is that the product's details repeat on every line that sells it and can be updated inconsistently.

Third normal form (3NF): no transitive dependencies. No non-key attribute depends on another non-key attribute. The scenario: an employee table containing department_id and department_name, where the name depends on the department, which depends on the employee. Move the department to its own table.

A serviceable summary: each attribute depends on the key, the whole key, and nothing but the key.

Why normalization exists

Not for elegance. It exists to prevent update anomalies:

• Update anomaly — a product is renamed, and because the name is stored on ten thousand order lines, some get updated and some do not. The database now disagrees with itself.
• Insertion anomaly — you cannot record a new product until someone orders it, because the only place a product name lives is an order line.
• Deletion anomaly — the last order for a product is deleted and the product's existence disappears with it.

These anomalies trace to dependencies being stored at the wrong grain or multiple business entities being forced into one table. Separating the facts by dependency greatly reduces opportunities for inconsistency.

Why analytics deliberately breaks the rule

A normalized design is often effective for transactional integrity because it reduces duplicated facts. For large analytical reads, a deeply normalized model can require many joins and be harder for consumers to navigate.

Warehouses often denormalize deliberately through dimensional models.

The star schema puts measurements in a central fact table with a declared grain, numeric measures, and foreign keys, surrounded by dimension tables holding descriptive attributes. A typical query joins the fact to a few dimensions and stops.

The grain can be one row per transaction, one row per entity and period in a periodic snapshot, or one row that accumulates milestones for a process. State the grain before interpreting or aggregating a measure.

Two advantages to remember:

1. Fewer joins, which can simplify queries and reduce join work.
2. A structure a business user can navigate, because dimensions are organised the way people describe the business.

The cost is accepted redundancy: a dimension repeats attributes rather than normalising them away. That is a trade, not a mistake.

The snowflake schema normalizes the dimensions — splitting a product dimension into product, subcategory, and category tables. It saves some storage and adds joins. Choose between them from governance, usability, maintenance, storage, and query requirements rather than assuming one is always superior.

Slowly changing dimensions

This distinction is easy to overlook.

A customer's sales region changes. What should historical reports show?

Type 1: overwrite. The old value is gone. Every historical report now attributes past sales to the new region — history has been silently restated. Correct only when the old value genuinely does not matter, such as fixing a typo.

Type 2: add a new row with effective dates and a new surrogate key. The old version stays, each fact joins to the version current when it occurred, and attribute history remains available. Type 2 is a common dimensional technique when reports must use the value in effect at the time.

Schedule does not determine type. A nightly job can overwrite a Type 1 row or insert effective-dated Type 2 versions; the change-handling behavior determines the classification.

OLTP and OLAP

The distinction underpinning all of this:

Heavy analytical queries on a production transactional database can compete with application work. When isolation, load, or history requirements demand it, use a replica, warehouse, or another reviewed analytical path; some hybrid systems can support both workloads when designed and tested for them.

Columnar formats such as Parquet belong to this story. A query touching three columns of a two-hundred-column data set can read only the relevant column blocks. Row-oriented systems may still use indexes, projections, caches, or other optimizations, so physical I/O depends on the engine and design as well as the file format.

ACID, briefly

As optional database context, ACID describes transaction guarantees under failure and concurrency:

• Atomicity — all of a transaction happens, or none of it. The funds transfer that fails halfway cannot leave money debited but never credited.
• Consistency — the database moves between valid states.
• Isolation — concurrent transactions behave according to a defined isolation level without unsafe interference. Weaker levels permit some read phenomena that stronger levels prevent.
• Durability — once committed, it survives a crash.

"Half the operation applied" indicates an atomicity problem; observing an incoherent blend of concurrent states can indicate an isolation problem.

What to drill

For enrichment, recognize 1NF, 2NF, and 3NF violations and the anomalies they reduce. For current V2 preparation, focus on fact, dimension, bridge, star, snowflake, slowly changing dimension, and schema choices. State the fact-table grain, choose Type 1 or Type 2 from history requirements, and treat ACID and physical formats as supporting context rather than assumed exam scope.