Re: problem whith normalisation
Date: Mon, 3 Sep 2001 18:54:59 +0200
Message-ID: <9n0cgp$5re$1_at_news.planetinternet.be>
Thanks for your help, Heinz
Heinz Huber <hhuber_at_racon-linz.at> schreef in berichtnieuws
3B932677.5C0384ED_at_racon-linz.at...
> 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 - 18:54:59 CEST