ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam DP-203 All Questions

View all questions & answers for the DP-203 exam
Go to Exam

Exam DP-203 topic 1 question 35 discussion

Actual exam question from Microsoft's DP-203
Question #: 35
Topic #: 1
[All DP-203 Questions]

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 Hide 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

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
PallaviPatel
Highly Voted 3 years, 2 months ago
Answer correct.
upvoted 26 times
...
ajhak
Highly Voted 1 year, 11 months ago
Always denormalize when moving from database to date warehouse.
upvoted 8 times
Rob77
1 year, 11 months ago
God no! Only dimensional models, read Inmon and Lindstedt approach.
upvoted 2 times
...
...
roopansh.gupta2
Most Recent 8 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, 7 months ago
denormalizing and IDENTITY
upvoted 2 times
...
SHENOOOO
2 years, 2 months ago
Answer Is correct
upvoted 3 times
...
Deepshikha1228
2 years, 8 months ago
Answer is correct
upvoted 3 times
...
JimZhang4123
2 years, 11 months ago
'The solution must optimize read operations.' means denormalization
upvoted 6 times
...
Mahesh_mm
3 years, 3 months ago
Answers are correct
upvoted 1 times
...
PallaviPatel
3 years, 3 months ago
answer is correct
upvoted 4 times
...
moreinva43
3 years, 4 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
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago