Tag: Data Normalization

Data Modeling, DP-900, Microsoft Certification

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

Practice Questions

Question 1

What is the primary purpose of normalization in a relational database?

A. Increase data duplication
B. Improve query speed for analytics
C. Reduce data redundancy and improve integrity
D. Store unstructured data

✅ Answer: C

Explanation:
Normalization reduces duplicate data and improves consistency and integrity.

Question 2

Which issue is MOST likely to occur in a non-normalized table?

A. Faster query performance
B. Data redundancy
C. Reduced storage requirements
D. Simpler queries

✅ Answer: B

Explanation:
Without normalization, the same data is often stored multiple times.

Question 3

A database stores customer details repeatedly in every order record. What problem does normalization solve in this scenario?

A. Improves indexing
B. Reduces redundancy
C. Enhances encryption
D. Increases data size

✅ Answer: B

Explanation:
Normalization separates customer data into its own table to avoid duplication.

Question 4

Which anomaly occurs when updating the same data in multiple rows leads to inconsistent values?

A. Insert anomaly
B. Delete anomaly
C. Update anomaly
D. Query anomaly

✅ Answer: C

Explanation:
Update anomalies happen when duplicate data is not consistently updated.

Question 5

Which approach is commonly used in normalized database design?

A. Storing all data in a single table
B. Splitting data into related tables
C. Removing all relationships between data
D. Using only unstructured formats

✅ Answer: B

Explanation:
Normalization divides data into multiple related tables.

Question 6

Which statement best describes First Normal Form (1NF)?

A. Data must be encrypted
B. Tables must include foreign keys
C. Each column contains atomic (single) values
D. Tables must be denormalized

✅ Answer: C

Explanation:
1NF requires that each field contains indivisible values and no repeating groups.

Question 7

What is a trade-off of normalization?

A. Increased data redundancy
B. Simpler queries with no joins
C. More complex queries due to joins
D. Reduced data integrity

✅ Answer: C

Explanation:
Normalized databases often require joins, making queries more complex.

Question 8

Which type of workload benefits MOST from normalized data?

A. Analytical workloads
B. Transactional workloads
C. Streaming workloads
D. Machine learning workloads

✅ Answer: B

Explanation:
Transactional systems prioritize data integrity, which normalization supports.

Question 9

Which action is an example of normalization?

A. Combining multiple tables into one
B. Storing duplicate data for faster reads
C. Separating customer and order data into different tables
D. Removing relationships between tables

✅ Answer: C

Explanation:
Normalization separates data into logical entities and links them via keys.

Question 10

Why might a system choose denormalization instead of normalization?

A. To improve data integrity
B. To reduce redundancy
C. To improve read performance for analytics
D. To enforce strict schemas

✅ Answer: C

Explanation:
Denormalization is often used in analytical systems to improve query performance.

✅ Quick Exam Takeaways

For DP-900, remember:

✔ Normalization = reduce redundancy + improve integrity
✔ Involves splitting data into related tables
✔ Prevents:

  • Insert anomalies
  • Update anomalies
  • Delete anomalies

✔ Used primarily in transactional (OLTP) systems
✔ Trade-off = more joins / more complex queries
✔ Opposite concept = denormalization (used in analytics)

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