Question

Suppose we have the following two tables (pk is in bold):

       EMP (E#, Ename, Address, D#)

       SAL_HISTORY (E#, Raise#, Salary, Raise_Date, Reason)

Assume that 90% of queries involving salary is based on the most recent salary. How could you denormalize the above tables to improve the performance.

Answer 1

Answer)
Tables are:

EMP (E#, Ename, Address, D#)

SAL_HISTORY (E#, Raise#, Salary, Raise_Date, Reason)

Based on the above table structures, we have the information that 90% of queries involving salary are based on the most recent salary which is depicted by the column Salary as the most recent Salary.

Thus for de-normalizing the above tables for improving performance, we can have the tables as:

EMP (E#, Ename, Address, D#, CurrentSalary)

SAL_HISTORY (E#, Raise#, Salary, Raise_Date, Reason)

We can add the column CurrentSalary to the table EMP, which would mean that we can access the CurrentSalary form the EMP table only and do not have to perform joins with SAL_HISTORY table for the current salary. As 90% of the queries involving salary is based on the most recent salary thus this would be the correct solution.

**Please Hit Like if you appreciate my answer. For further doubts on the question or answer please drop a comment, I'll be happy to help. Thanks for posting.**