Designing a Data Model: Star Schema for Table1 and Table2
Question
You are assigned to create a data model for your new database following the star schema design theory.
There is a Table called Table1 which has details about customers.
You have noticed that values like email and phone numbers are changed by some of the customers using your application once in a while.
There is another table Table2 which stores the salesperson related details.
When a Salesperson is relocating to another region, the new version of salesperson details must be created.
But the previous version of details must be stored with a start date and end date.
Which of the following options is correct? (Multiple Choice)
Answers
Explanations
Click on the arrows to vote for the correct answer
A. B. C. D.Correct Answer: A and B.
Slowly Changing Dimensions (SCD) is used to manage the change in dimension members over a period of time.
Some of the dimensions may vary and may not require a previous version also to be available.
But some required historical details.
Here the question says that it is using a star schema design model.
Commonly type1 SCD and Type 2 SCD are used with a star schema.
Table1 has values that may change once in a while and can be overwritten to the current value, instead of creating a new version.
This is Type 1 SCD.
Table2 has a requirement to create a new version of the data, thus Type 2 SCD.Option A is correct: When using the Type 2 SCD, the values are overwritten, and the latest values are only reflected.
Option B is correct: When using Type2, the new version of data is created with start and end dates.
Option C is incorrect: Table1 can afford overwriting of data.
So, it is Type1 SCD, not Type2 SCD.Option D is incorrect: Table2 needs versioning, and overwriting will not work here.
So, it is TYpe2, not Type1.
In data warehousing, the star schema is a common design that organizes data into a central fact table surrounded by dimension tables. The fact table contains the business metrics and the dimension tables contain descriptive attributes for the metrics. Slowly Changing Dimensions (SCD) refer to the attributes of a dimension that change over time.
In the given scenario, Table1 contains customer details, such as email and phone numbers, that may change over time. Table2 contains salesperson-related details and tracks changes in their details when they relocate to a new region.
Option A: Table1 should use Type 1 SCD Type 1 SCD overwrites the old value with the new value. It does not keep track of historical changes. Therefore, using Type 1 SCD for Table1 is not appropriate as it would overwrite the old email and phone number with new values, which would make it difficult to track the changes over time.
Option B: Table2 should use Type 2 SCD Type 2 SCD keeps track of historical changes in a separate row and uses a start date and end date to identify when the change occurred. Therefore, using Type 2 SCD for Table2 is appropriate as it would allow for tracking of salesperson details over time.
Option C: Table1 should use Type 2 SCD Type 2 SCD is the appropriate choice for Table1 as it would allow for tracking changes in email and phone numbers by creating a new row for the updated values and marking the old row with an end date.
Option D: Table2 should use Type 1 SCD Type 1 SCD overwrites the old value with the new value, which would not be appropriate for tracking changes in salesperson details when they relocate to a new region. Therefore, Type 1 SCD should not be used for Table2.
Therefore, the correct options are B and C. Table2 should use Type 2 SCD, and Table1 should use Type 2 SCD.
