I'm currently studying for my exams and the trouble I am having is how to decompose a relation R with given functional dependencies into 2NF then 3NF.

For example for the following R and functional dependencies:

R = {A, B, C, D, E, F, G, H, I, J}

Functional dependencies F = ( {A, B} -> {C}, {A} -> {D, E}, {B} -> {F}, {F} -> {G, H}, {D} -> {I,J} }

I know firstly you have to work out the closure to find the key for R, which I have done and the key is {A,B} and now this is where I get stuck. My text book doesn't give any examples on how one can solve this except for definitions of 2NF and 3NF.

Example on how I can do this will be greatly appreciated.

  • 1
    I was just in the process of answering the question that you just deleted. You need an outer join in case any departments have 0 professors and your GROUP BY needs the department id in there. – Martin Smith Dec 27 '12 at 20:47
  • @MartinSmith It turns out I needed to have Distinct for the Count function and it worked. So it was: SELECT DNO, DName, Count(DISTINCT Prof_SSN) FROM Professor, Department, Work_Department WHERE DNO=Dept_number GROUP BY Dname – orange Dec 27 '12 at 21:29
  • 1
    Don't know what DBMS you are running that in but I'm guessing MySQL? Other DBMSs would reject it (except Postgres possibly if Dname has a unique index on it.). Also you are missing a join condition which you have hidden by adding DISTINCT. That query is not correct. – Martin Smith Dec 27 '12 at 21:49
  • Yes, MySQL. I'm using Sequel Pro to test out my queries. What should the correct answer be? – orange Dec 27 '12 at 21:52
  • 2
    You don't need the PROFESSOR table in there at all. SELECT D.DNO,D.DName,Count(W.Prof_SSN) FROM Department D LEFT JOIN Work_Department WD ON D.DNO = W.Dept_number GROUP BY D.DNO, Dname – Martin Smith Dec 27 '12 at 22:09

2NF: Remove Partial Dependencies

R = {A, B, C, D, E, F, G, H, I, J} includes partial dependencies.

D and E depend only on A, F depends only on B, G, H, I and J don't depend on the key (directly) at all.

R0 = {A, B, C}

R1 = {A, D, E, I, J}

R2 = {B, F, G, H}

R0, R1, and R2 contain no partial dependencies (or repeating groups) so they are 2NF. However R1 and R2 are still an issue, because they contain transitive dependencies.

3NF: Remove Transitive Dependencies

I and J depend on D, not on the key of R1. Therefore you need to further normalize R1 as follows:

R1 = {A, D, E, I, J}

R1a = {A, D, E}

R1b = {D, I, J}

Similarly, G and H depend only on F so R2 must be decomposed as follows:

R2 = {B, F, G, H}

R2a = {B, F}

R2b = {F, G, H}

Now all of your remaining relations (R0, R1a, R1b, R2a, R2b) are devoid of repeating groups, partial dependencies and transitive dependencies. That means your relations are in 3NF.

When you are looking at an relation that hasn't been normalized and a series of dependencies, you can often normalize by inspection just by recognizing what your primary keys are going to be. Any attribute or combination of attributes that functionally determine other attributes are going to end up as primary keys. Once you've got your primary keys defined, you just need to figure out which non-key attributes go with each key. This is obvious from the statement of what your functional dependencies are.

  • Thank you. Can I expand my question and ask how would you then convert to BCNF? (Or would I need to open a new question?) – orange Dec 26 '12 at 18:54
  • @orange - Note from Wikipedia and elsewhere: "Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF." There are no relations at 3NF in your question with multiple candidate keys therefore all of these relations are already in BCNF. Normal forms above 3NF are not always applicable. – Joel Brown Dec 26 '12 at 22:00
  • what abou this R = {A, B, C, D} and F = C → D, C → A, B → C, ABC → D, D → A. Thanks! – orange Dec 26 '12 at 22:11
  • Also am I right in assuming the relation I posted above is in 3NF to being with as it has no traversal dependencies and no partial dependencies as well? – orange Dec 26 '12 at 22:21
  • @orange - You should look again: R={A,B,C,D} by itself is only 1NF. There are partial dependencies. 3NF would have to include: {B,C} {C,D} and {D,A}. – Joel Brown Dec 27 '12 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.