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.
page revision: 0, last edited: 11 Aug 2008 23:23