0

The given FDs are - 

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

The table is-enter image description here
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. enter image description here Where am I going wrong? Any help would be really appreciated.

  • Although there is an accepted answer with a correct decomposition, I have added an answer to address problems in your question & comments. There's also another answer regarding R2 not being in 2NF, but it doesn't properly address your "primary key conflict". – philipxy Dec 29 '16 at 22:06

3 Answers3

0

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

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • ok .. However, the book that I am using, Fundamentals of DB by Navathe, 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. So Training was multi-valued. So I created a different table R2 for that, and R2 is already in 2NF since date attribute has no FD for it, isn't it? –  Dec 25 '16 at 06:12
  • Also, are the FDs complete for the changing the table in 3NF. I feel that there should have been an FD mentioning date too. –  Dec 25 '16 at 06:14
  • a table cannot be in 3NF unless it's in 2NF and 1NF and having multiple items of course title violates those earlier normal forms – e4c5 Dec 25 '16 at 06:28
  • R1 is ok provided that an employee can belong to exactly one department. If an employee can belong to more than one you need a separate table for departments. – e4c5 Dec 25 '16 at 06:34
  • R1 (Employee#, Dept#)..... R2(Eployee, Course, Year) .... R3(Course, title) ....R4 (Dept#, Manager#) ... Are these in 3NF? –  Dec 25 '16 at 06:36
  • Not quite sure why you unaccepted the answer. I do believe your primary question was the error that was raised when you attempted to insert data and that I asnwered. No where in your question do you say we are supposed to give you the correct table structure. – e4c5 Dec 25 '16 at 06:48
  • I am really sorry! This is my second question. I was trying to mark both the answers as helpful. As it turns out, I cannot do that! Alas! –  Dec 25 '16 at 17:11
  • When @Arfat says "when I inserting the data into R2", they mean, *to give R2 the value it has above*, not to add a duplicate row. The error is not that they "already have such a record in your table". The error is that the primary key of R2 is {employee#, course#, date}, not, as they claim, "Emp# and Course#". – philipxy Dec 29 '16 at 21:32
0

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).

Renzo
  • 26,848
  • 5
  • 49
  • 61
  • Thank you so much. Also, Is it necessary for every attribute to be in either LHS or RHS side of a functional dependency? Because I feel the date should have been a part of some FD. –  Dec 25 '16 at 06:39
  • No, it is not necessary. If an attribute is not in any FD, then it **must** be part of the key in the original relation, and in fact the original relation has key Employee#, Course#, Date. – Renzo Dec 25 '16 at 06:41
  • @Arfat It is necessary--if by "a FD" you mean one that holds, rather than one that was explicitly given--because every attribute functionally determines itself. But what you need to understand is that when you are given some FDs that hold, *all the FDs in their closure hold*, per Armstrong's axioms, and you must address *all* of them. But some algorithms involve just using the FDs in some *minimal/canonical cover* for the original & (equivalently) its closure. You need to memorize definitions and algorithms exactly, from college/university textbooks/slides. Eg "primary keys" are irrelevant. – philipxy Dec 26 '16 at 07:00
  • @Arfat Eg The CK of R4 is (Employee#, Course#, Date). As CK, every subset of attributes of R4 is functionally determined by it. – philipxy Dec 26 '16 at 07:37
0
--- 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.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83