Identify two possible examples of SCDs in the property sales star schema shown in Figure 32.1 and discuss the types of change each represents.

What will be an ideal response?

An example of a SCD is the position attribute in Staff. The position held by a member of staff is likely
to change over time. If it is important to know the position held by a member of staff associated by a
property sale then this is a Type 2 SCD. This means that a new Staff dimension record has to be created
to represent the member of staff with the new position and this record takes effect from this point
onwards until the next change.
A second example of a possible SCD is the propertyNo attribute in PropertyForSale. It is possible that
the business may change the coding used to uniquely identify each property from e.g. PG1 to
PGW00001. If it is not important to know the old unique identifier for a property when analysing sales
then this is a Type 1 SCD. This means that the new value for each property overwrites the old value.
Evidence of the change associated with propertyNo is lost and the old values will not be available when
querying property sales.