Re: problem whith normalisation

Jan Hidders wrote:
>
> GLE wrote:
> > I have a problem whith normalisation. Someone who can tell what I do wrong?
> >
> > Suppose you have to make tables for the following problem:
> >
> > There are employees (employeeID, employeename). Each employee speaks one
> > ore more languages. Each employee has followed zero-one or more Courses
> > (CourseID and CourseDescription)
> >
> > [...]
> >
> > 1NF --> 2NF (EmployeeName is Functional Dependend of EmployeeID)
> >
> > (1)
> > CourseID (PK)
> > CourseDescription
> >
> > (2)
> > CourseID (PK)
> > EmployeeID (PK)
> >
> > (3)
> > CourseID (PK)
> > EmployeeID (PK)
> > Language (PK)
> >
> > (4)
> > EmployeeID (PK)
> > EmployeeName
> >
> > These tables are in 3NF (and i think also in BCNF)
> >
> > If you now compare whith the correct tables:
> >
> > (1) is tblCOURSES
> > (2) is tblCOURSE_EMPLOYEE
> > (4) is tblEMPLOYEES
> > (3)?????? if this is the table where you can read which employee speaks
> > whitch languages, why is CourseID in this table???
>
> Congratulations. You have just discovered why 4NF is of more practical
> use than many database modelers would like to admit. :-) First you can
> see that table (2) is a subtable of (3), so you should omit (2). The
> resulting three tables, are indeed in BCNF. But why have we still not
> reached the "perfect" set of tables? Well, table (3) is not in 4NF
> because there is a multivalued dependency EmployeeID ->> Language
> there. And 4NF says that then EmployeeID should be candidate key, but
> it doesn. So we split off this dependency and get the two tables:
>
> (3a) /* what is left of the relation */
> CourseID (PK)
> EmployeeID (PK)
>
> (3b) /* the split off dependency */
> EmployeeID (PK)
> Language (PK)
>
> And, lo and behold, there is your perfect set of tables.

You imply that language is dependent on the employee and that there is no relation to the course. Given the original 0NF record, it might also be that language is actually dependent on the course.

The prosa at the beginning of the OP suggests the dependence you assumed. Therefore the solution is probably correct. I just wanted to point out that you can't reach this conclusion based on the data alone. Acutally, IMHO the data rather suggests the second interpretation that an employee was taught a language in a course.

Regards,
Heinz Received on Mon Sep 03 2001 - 08:43:03 CEST