problem whith normalisation

From: GLE <goderik.lefebvre_at_sip.be>
Date: 31 Aug 2001 14:17:53 -0700
Message-ID: <a74f4cd5.0108311317.d7f69a8_at_posting.google.com>

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)

I guess the correct tables are:

tblEMPLOYEES
EmployeeID (PK)
EmployeeName

tblCOURSES
CourseID (PK)
CourseDescription

tblCOURSE_EMPLOYEE
EmployeeID (PK)
CourseID (PK)

tblLANGUAGES_EMPLOYEE
EmployeeID (PK)
Language (PK)

Whith the rules for normalisation I don't reach this result. Where do I make a mistake???

0NF:

CourseID
CourseDescription
EmployeeID
EmployeeName
Language

0NF --> 1NF (CourseID is PK, * are repeating groups)

CourseID (PK)
CourseDescription
EmployeeID*
EmployeeName*
Language*

(In the new relation I make the combination CourseID and EmployeeID PK)

(1)

CourseID (PK)
CourseDescription

(2)

CourseID (PK)
EmployeeID (PK)
EmployeeName
Language*

(again a repeating group to be removed in a new table)

(1)

CourseID (PK)
CourseDescription

(2)

CourseID (PK)
EmployeeID (PK)
EmployeeName

(3)

CourseID (PK)
EmployeeID (PK)
Language (PK)

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???

Thanks
goderik.lefebvre_at_sip.be Received on Fri Aug 31 2001 - 23:17:53 CEST