Hi Guys,

I'm currently revising for an upcoming Database Management exam.

There is a question on denormalising the sample employees database. The question is as follows, the schema is also shown below:

Use the 'employees' database to de-normalise any two (or more) tables to produce a table in 2NF. you must precisely explain why the table is in 2NF and not 3NF.

Schema [no photo embed?] employees-schema

My Answer:
I would denormalise the 'salaries' table into the 'employees' table to. To normalise I would move {salary, from_date, to_date} into the employees table and remove the 'salaries' table. Note: from_date is no longer part of the primary key in 'employees'.

The 'employees' table is no longer in 3NF and is now in 2NF. This is because a 'transitive dependency' has been introduced into the table.

The transitive dependency is as follows: 'salary' depends on 'from_date'. It is transitive and not partial because 'from_date' is not a component of the primary key. In a partial dependency the determinant must be part of the primary key.

Basically for this question I need to create a transitive dependency. This schema seems a little sparse and I'm also a little thrown off by the fact that dates are part of primary keys.

If the above dependency is wrong could somebody possible point one out for me please?

Another possible solution is to denormalise 'departments' into 'dept_emp'. I could add the 'dept_name' into 'dept_emp'. But from looking at the SQL for this table shows 'dept_no' is part of the primary key.

Any guidance on this would be greatly appreciated.