DP-900: understanding and using normalization in relational databases

I will teach the skill of normalizing relational schemas (1NF–3NF) — a key aptitude in the "Dados relacionais no Azure" section of the DP-900. Knowing how to normalize helps you design efficient databases and reduce redundancy, which matters both for the exam and for real solutions in Azure SQL Database or SQL Server. Beyond theory, I explain concrete steps, performance trade-offs and practical ways to experiment in an Azure or local environment.

What you need to know

Normalization is a set of rules for organizing attributes and tables to reduce redundancy and avoid insert/update/delete anomalies. The normal forms most relevant for the Fundamentals level are:

• 1NF (First Normal Form): each column must contain atomic values (no lists or structures) and each row must be unique (primary key). For example, do not put a "Products" column with a comma-separated list — each product should be a row.
• 2NF (Second Normal Form): in addition to being in 1NF, all non-key attributes must depend fully on the primary key. This is especially important when you have composite keys (for example, OrderID + ProductID). 2NF eliminates partial dependencies that cause unnecessary repetition.
• 3NF (Third Normal Form): in addition to 2NF, there must be no transitive dependencies among non-key attributes. In other words, a non-key attribute should not depend on another non-key attribute; if that happens, move that part to a new table.

Simple example: an orders record. A non-normalized schema could have: OrderID, CustomerName, CustomerAddress, ProductID, ProductName, Quantity in a single table. This causes redundancy — if an order has 5 lines (5 products), the customer data is repeated 5 times. In real datasets (for example, 100,000 order rows) this repetition can increase storage used and complicate updates: changing the customer address would require updating many rows.

How it works (step-by-step)

Follow a practical process to normalize up to 3NF, using an Orders example:

1. Identify entities and repetitions: examine the data and separate concepts — Order, Customer, Product. Count how often each piece of information repeats (for example, if 10% of rows repeat the same CustomerName, that's a sign of redundancy).
2. Ensure 1NF: replace multivalued fields with separate rows. If the order has multiple products, each product is a row in OrderItems with OrderID repeated but a composite key (OrderID, ProductID). This transforms lists into atomic records.
3. Apply 2NF: detect composite keys. If OrderItems uses (OrderID, ProductID) as the key, attributes like ProductName or UnitPrice should not depend only on one part (ProductID). Moving them to a Products table avoids repeating name and price for each order line.
4. Apply 3NF: remove transitive dependencies. If the Customers table contains CustomerCity and CityRegion, and CityRegion depends on City, create a Cities table (CityID, CityName, Region) and reference it by key. That way, region changes cause a single modification.

-- Esquema normalizado (exemplo conceptual)
Customers(CustomerID PK, CustomerName, CustomerAddress)
Products(ProductID PK, ProductName, UnitPrice)
Orders(OrderID PK, OrderDate, CustomerID FK)
OrderItems(OrderID FK, ProductID FK, Quantity, PRIMARY KEY(OrderID, ProductID))

This design centralizes customer and product information, easing maintenance and allowing the application of constraints and transactions to maintain integrity in Azure SQL Database.

In practice — recommendations for Azure

When implementing in Azure SQL Database or in Managed Instance, consider these practical points:

• Define primary and foreign keys to enforce referential integrity; this allows the SQL engine to validate relationships automatically.
• Create indexes on join columns (for example, a non-clustered index on Orders.CustomerID) to speed up joins; without indexes, a join query can cost several hundred ms or more on large tables.
• Analyze the execution plan (Query Plan) to understand the cost of joins; if a critical read query does 5 joins and is slow, consider materializing results with indexed views or controlled denormalization.
• Balance normalization with performance requirements: in read-heavy scenarios (for example, reports in Power BI), some denormalization (duplicating a field that rarely changes) can remove 2–3 joins and speed up read queries; always document the reasons and impacts on write operations.

Common mistakes

Some typical pitfalls and how to avoid them:

• Confusing normalization with total elimination of duplication: normalizing to very advanced forms can create too many tables and many joins, penalizing reads. Evaluate the balance between writes and reads.
• Forgetting natural keys versus surrogate keys: using name or email as a natural key can break integrity if the value changes. Prefer surrogate keys (integer auto-increment IDs) for stability and join performance.
• Ignoring read/write operations: over-normalizing increases the number of write operations (more tables to update) and can induce contention. Test with representative loads (for example, 1000 inserts/sec) to measure impact.

How to practice

Practice modeling and normalization using a test environment in Azure (for example, an Azure SQL Database instance with free tier/credits) or a local server with SQL Server Developer. Suggested steps:

• Create a denormalized schema, populate with a few thousand rows (for example 10k–100k) and measure size and times for basic queries (SELECT, UPDATE).
• Normalize to 3NF according to the process above, populate the normalized tables and compare space used, query times and number of joins. Document percentage differences — in many cases you will observe reduced redundancy and easier updates, and a small increase in read cost if appropriate indexes are not present.
• Use Azure monitoring tools (Query Performance Insight, Query Store) to analyze real queries.

For exam preparation: use the OFFICIAL Microsoft Practice Assessment (free) and the official Microsoft study guide (free). These resources help you verify the measured knowledge without resorting to prohibited material.

In summary

• Normalization (1NF–3NF) organizes data to reduce redundancy and avoid data anomalies.
• 1NF requires atomic values; 2NF eliminates partial dependencies; 3NF removes transitive dependencies.
• In Azure SQL Database, combine normalization with indexes and referential integrity to achieve good performance; test with real loads and adjust if needed.
• Practice with real schemas and use Microsoft’s official free resources to prepare for the DP-900, always following good modeling and performance evaluation practices.