Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: problem whith normalisation

Re: problem whith normalisation

From: Jan Hidders <hidders_at_REMOVE.THIS.win.tue.nl>
Date: 1 Sep 2001 19:25:00 GMT
Message-ID: <9mrcmc$b8e$1@news.tue.nl>


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
Received on Sat Sep 01 2001 - 14:25:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US