Converting a relation into 3NF

Question

The given FDs are -

Epmployee#→Dept#,Manager# 
Dept#→Manager# 
course#→course_title

The table is-
I have decomposed it into 3 relations. They are in 3NF.

R1 (Employee#, Dept#)  --- Employee is PK
R2 (Employee#, Course#, course_title, date)  --- Emp# and Course# are PK
R3 (Dept#, Manager#) --- Dept# is PK

The Primary Keys are as above.
However, when I inserting the data into R2, I get a primary key conflict. It is in red. Where am I going wrong? Any help would be really appreciated.

Answer 1

A correct decomposition in your case is the following:

R1(Dept#, Manager#), with the only dependency Dept# → Manager#, so Dept# is the key
R2(Employee#, Dept#), with the only dependency Employee# → Dept#, with key Employee#
R3(Course#, Course_title), with the only dependency Course# → Course_title and key Course#
R4(Employee#, Course#, Date), with no dependency and key (Employee#, Course#, Date)

All the relations are in Boyce-Codd Normal Form (and for this reason they are also in 3NF).

Finally note that one could be tempted to coalesce the two relations R2 and R4, but this would have produced a decomposition not in 4NF (since an employee has only a department but can follow several courses).

Answer 2

The error is because you already have such a record in your table. Checkout

002  ta01 Time Management 2014

And FYI, this is not in third normal form. Course Title should not apear in R2. You should have a table called courses that stores Course Number, Course title and it's only the course number (TA01 etc ) that should appear in R2

Answer 3

--- Emp# and Course# are PK

Note PKs (primary keys) are irrelevant to normalization. CKs (candidate keys) matter.

{employee#, course#} is not a CK of R2 since it does not determine date. The only CK is {employee#, course#, date}.

They are in 3NF.

R2 isn't in 3NF or 2NF, since non-prime column course_name is partially functionally dependent on a CK, since it is functionally dependent on {course#}, a proper/smaller subset of a CK. (This would be so even if the CK were {employee#, course#} as you claimed.) Lossless decomposition leads to 3NF tables on {course#, course_title} and {employee#, course#, date}.

Course# -> Course_title

This does not make sense re the original table since those are not columns of it. It does make sense to say that a course has exactly one title. That does mean that in a table with those columns with their obvious meanings, {course#} -> {course_title}. It also means that the original table is subject to a certain constraint; it just isn't a FD (functional dependency) constraint.

it is written that if there are multi-valued attributes then I should form a new table and propagate the Primary key of the table (Employee#) into the new Table and them both the composite PK. [comment by you]

In the original table each training value is a set of records. To get rid of a set column that isn't part of a CK we can split off a table using columns of some CK to get a new table with a row for each pairing of an original table CK subrow value with a set element. Here the set elements are records. After we get rid of the set column we can get rid of the record column. To get rid of a record column we replace it by columns for its fields. Now applying that gives your R2.

Originally getting rid of table-valued columns was called "normalization" by Codd. Then by "further normalization" Codd meant lossless decomposition to higher normal forms. Sometimes getting rid of set-valued or record-valued columns is called "normalization". But it's not. It's just improving the design. Sometimes "normalization" is used to mean lossless decomposition to higher normal forms regardless of any table-valued columns.

I created a different table R2 for that, and R2 is already in 2NF [comment by you]

Every time you replace a table by others you must determine the new tables' FDs, CKs & NFs. If you losslessly decompose to a higher NF then you know that each component's highest NF is higher than the original's. But here you are doing different transformations--getting rid of set and record columns. Here R2's CK and an FD involving courses means it isn't in 2NF even though the original was. The non-FD constraint involving courses in the original relation has caused a certain FD to hold in R2.

I feel the date should have been a part of some FD. [comment by you]

Maybe you are trying to say that you would expect that a given employee could only take a given course in one year. But that is not what we have been told.