Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 1 question 35 discussion

Actual exam question from Microsoft's DP-203

Question #: 35
Topic #: 1

HOTSPOT -
You have a Microsoft SQL Server database that uses a third normal form schema.
You plan to migrate the data in the database to a star schema in an Azure Synapse Analytics dedicated SQL pool.
You need to design the dimension tables. The solution must optimize read operations.
What should you include in the solution? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

Box 1: Denormalize to a second normal form
Denormalization is the process of transforming higher normal forms to lower normal forms via storing the join of higher normal form relations as a base relation.
Denormalization increases the performance in data retrieval at cost of bringing update anomalies to a database.

Box 2: New identity columns -
The collapsing relations strategy can be used in this step to collapse classification entities into component entities to obtain flat dimension tables with single-part keys that connect directly to the fact table. The single-part key is a surrogate key generated to ensure it remains unique over time.
Example:

Note: A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.
Reference:
https://www.mssqltips.com/sqlservertip/5614/explore-the-role-of-normal-forms-in-dimensional-modeling/ https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-identity

by moreinva43 at Dec. 11, 2021, 4:43 p.m.

Comments

Submit Cancel

PallaviPatel

Highly Voted 3 years, 4 months ago

Answer correct.

upvoted 26 times

...

ajhak

Highly Voted 2 years, 1 month ago

Always denormalize when moving from database to date warehouse.

upvoted 8 times

Rob77

2 years ago

God no! Only dimensional models, read Inmon and Lindstedt approach.

upvoted 2 times

...

roopansh.gupta2

Most Recent 10 months ago

Why Denormalizing to 2nd Normal Form (2NF) is CORRECT?: In a star schema, dimension tables are typically denormalized to reduce the number of joins required during query execution. This denormalization improves query performance, particularly in read-heavy workloads typical in analytics environments like Azure Synapse. Denormalizing to 2NF involves reducing the number of tables and increasing the redundancy within the dimension tables, which is beneficial for faster reads. Maintaining 3NF: While 3rd Normal Form (3NF) minimizes redundancy and ensures data integrity, it also requires more complex joins during queries, which can slow down read operations in a star schema designed for analytics.

upvoted 3 times

...

kkk5566

1 year, 9 months ago

denormalizing and IDENTITY

upvoted 2 times

...

SHENOOOO

2 years, 4 months ago

Answer Is correct

upvoted 3 times

...

Deepshikha1228

2 years, 10 months ago

Answer is correct

upvoted 3 times

...

JimZhang4123

3 years, 1 month ago

'The solution must optimize read operations.' means denormalization

upvoted 6 times

...

Mahesh_mm

3 years, 5 months ago

Answers are correct

upvoted 1 times

...

PallaviPatel

3 years, 6 months ago

answer is correct

upvoted 4 times

...

moreinva43

3 years, 6 months ago

While denormalizing does require implementing a lower level of normalization, the second normal form ONLY applies when a table has a composite primary key. https://www.geeksforgeeks.org/second-normal-form-2nf/

upvoted 1 times

...