Re: problem whith normalisation

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.

Met vriendelijke groet van een bijna-Belg :-).

-- 
  Jan Hidders