Data Modeling, Databases, DP-900, Microsoft Certification

Describe normalization and why it is used (DP-900 Exam Prep)

 
This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. 
This topic falls under these sections:
Identify considerations for relational data on Azure (20–25%)
   --> Describe relational concepts
      --> Describe normalization and why it is used

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

Normalization is a foundational concept in relational database design. For the DP-900 exam, you are expected to understand what normalization is, why it is important, and how it improves data quality and efficiency.

What Is Normalization?

Normalization is the process of organizing data in a relational database to:

  • Reduce data redundancy (duplicate data)
  • Improve data integrity
  • Ensure logical data relationships

This is done by splitting data into multiple related tables and defining relationships between them using keys.

Why Normalization Is Used

Normalization is used to solve common data problems in poorly designed tables.

1. Reduce Data Redundancy

Without normalization, the same data may be repeated across multiple rows.

Example (Unnormalized Table):

OrderIDCustomerNameCustomerCityProduct
1JohnSeattleLaptop
2JohnSeattleMouse

Here, customer information is duplicated.

After Normalization:

Customers Table

CustomerIDNameCity
1JohnSeattle

Orders Table

OrderIDCustomerIDProduct
11Laptop
21Mouse

Now, customer data is stored once and referenced using a key.

2. Improve Data Integrity

Normalization ensures that data remains accurate and consistent.

Without normalization:

  • Updating a customer’s city requires changing multiple rows
  • Missing one update leads to inconsistent data

With normalization:

  • Data is updated in one place only
  • Consistency is maintained automatically

3. Prevent Data Anomalies

Normalization helps prevent common issues:

  • Insert anomaly: Cannot add data without unrelated data
  • Update anomaly: Inconsistent updates across rows
  • Delete anomaly: Deleting one record removes important data

Normalized designs eliminate these problems.

4. Improve Data Organization

Normalized databases:

  • Clearly separate different entities (customers, orders, products)
  • Use relationships to connect data logically
  • Make databases easier to maintain and scale

Understanding Normal Forms (Simplified for DP-900)

Normalization is often described in stages called normal forms. For DP-900, you only need a basic understanding:

First Normal Form (1NF)

  • No repeating groups or multi-valued fields
  • Each column contains atomic (single) values

Second Normal Form (2NF)

  • Meets 1NF
  • All non-key columns depend on the entire primary key

Third Normal Form (3NF)

  • Meets 2NF
  • No dependency between non-key columns

💡 DP-900 Tip:
You do NOT need to memorize formal definitions — just understand that normalization reduces redundancy and improves integrity.

Trade-Offs of Normalization

While normalization has many benefits, there are trade-offs:

Advantages

✔ Reduces duplicate data
✔ Improves consistency
✔ Simplifies updates
✔ Enhances data integrity

Disadvantages

✖ Requires more tables
✖ Queries may require joins
✖ Can slightly impact performance for complex queries

Normalization vs Denormalization

Understanding this comparison is important for the exam:

FeatureNormalizationDenormalization
Data RedundancyReducedIncreased
Data IntegrityHighLower
Query ComplexityHigher (joins required)Lower
PerformanceSlower for readsFaster for analytics
Use CaseTransactional systems (OLTP)Analytical systems (OLAP)

Where Normalization Is Used in Azure

Normalization is commonly applied in relational database services such as:

  • Azure SQL Database
  • Azure Database for PostgreSQL
  • Azure Database for MySQL

These services are typically used for transactional workloads, where data integrity is critical.

Why This Matters for DP-900

On the exam, you may be asked to:

  • Identify why normalization is used
  • Recognize normalized vs unnormalized structures
  • Understand how normalization affects data integrity
  • Distinguish normalization from denormalization

Summary — Exam-Relevant Takeaways

✔ Normalization organizes data into multiple related tables
✔ It reduces data redundancy
✔ It improves data integrity and consistency
✔ It prevents insert, update, and delete anomalies
✔ It is commonly used in transactional (OLTP) systems
✔ It may require joins when querying data

Go to the Practice Exam Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

Leave a comment