Answer to Problem 3.2

This table is in 1NF. The primary identifier is [course, teacher, text]. There are two partial key dependencies, teacher —> hire date and text —> copyright, which are violations of 2NF. In 3NF the relations are:

teacher —> hire date
text —> copyright
course, teacher, text

In the third relation we have two MVDs: course —» teacher | text. Fixing these violations of 4NF we are left with

teacher —> hire date
text —> copyright
course —» teacher
course —» text

These tables are what results from this analysis.

Solution to the course 4NF problem
Teacher table
Teacher Hire date
Moore 1992
Ravishankar 1986
Walls 1993
Course table
Course Teacher
BIT340 Moore
BIT340 Ravishankar
BIT301 Moore
BIT301 Walls
Book table
Text Copyright
340 Coursepack 2007
Access 2007
301 Coursepack
Excel 2007
being digital 2005
Course text table
Course Text
BIT340 340 Coursepack
BIT340 Access
BIT301 301 Coursepack
BIT301 Excel
BIT301 being digital

Return to 4th normal form.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License