DATABASE FUNDAMENTALS
DATA WAREHOUSING AND DATA MINING
|
Question
[CLICK ON ANY CHOICE TO KNOW THE RIGHT ANSWER]
|
|
|
Foreign key
|
|
|
Fact Table
|
|
|
Dimensional Table
|
|
|
Surrogate key
|
Detailed explanation-1: -A surrogate key uniquely identifies each entity in the dimension table, regardless of its natural source key. This is primarily because a surrogate key generates a simple integer value for every new entity. Surrogate keys are necessary to handle changes in dimension table attributes.
Detailed explanation-2: -There can be other possible examples of the Surrogate key, which are: System date & time stamp. Random alphanumeric string and many more.
Detailed explanation-3: -Surrogate keys are present in dimension tables as the primary key and in fact tables as foreign keys to dimensions. However, the dimension record retains the business key as an alternate-key attribute. Surrogate keys are four-byte integers and their size does not change even when production key changes in size.
Detailed explanation-4: -For example, if the data warehouse contains data about 20, 000 customers, who on average made 15 purchases, then the fact table will contain about 300, 000 (small) surrogate key values, whereas the dimension table will contain 20, 000 (large) business key values in addition to the same number of surrogate key values.