Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Given a schema r(A,B,C,D,E,F) and a set of functional dependencies

  • A --> BCD
  • BC --> DE
  • B --> D
  • D --> A

As we can see the schema is not even in 1NF as can be read here, now the question is can we directly decompose the schema into 3NF forms or we have to first bring it to 1NF.

If we directly decompose, it will be as follows

Since A --> BCD is not a trivial relation and A is not a primary the relation breaks up into

r1(A,B,C,D) and r2(A,E,F) and then we again break r2 because r1 is now in 3NF but not r2 then we get r1(A,B,C,D) r2(A,E) and r3(F)

But this does not make sense because we must first bring the relation into 1NF first and then decompose it, by doing that we can get two schemas r1(A,B,C,D,E) and r2(F). By this we see that r1 is now in BCNF and we need not further decompose it and r2 is also in BCNF.

share|improve this question
"F" does not appear on the right-hand side of any functional dependencies. That's a big flag waving in the wind. Do you know the significance of an attribute that doesn't appear on the right-hand side of any FD? – Mike Sherrill 'Catcall' Nov 17 '11 at 11:26
The schema does not violate 1NF, it's the data that may violate schema and bear unexpected dependencies. Wiki has some good examples. – Lyth Nov 17 '11 at 11:30
I have an understanding that this schema violates 1NF because as in 1NF "Each row of data must have a unique identifier (or Primary Key)" but the schema itself shows that there is no primary key then doesn't it violate 1NF? – Sachin Nov 17 '11 at 11:34
@Catcall Actually this was a question that was asked to us in our exam, and I think it was initially a typo error but then the teacher decided not to edit it. So is this question wrong or such schema is possible???? If F is not in the functional dependency of any attribute then how can we even have it in the design? – Sachin Nov 17 '11 at 11:38
@Lyth Wiki only address the issue of atomicity of attributes in 1NF, but is not the existence of a primary key also a feature of 1NF? This schema lacks a primary key – Sachin Nov 17 '11 at 11:41
show 1 more comment

migrated from stackoverflow.com Nov 17 '11 at 16:31

2 Answers

up vote 3 down vote

Always hit it at first shot? It is not guaranteed. You apply normalization rules to remove anomalies from relations. Relations rise to a more normal form. You observe for anomalies again and if there are anomalies apply rules again. It is possible to reach most normal form possible for the relation at first move (as you know there is a limit for normalization of any relation. Ron Fagin showed that it is often impossible to achieve DKNF form) but not always. Ron Fagin's paper may help.

share|improve this answer
So what you seem to say is that the 1st decomposition of the schema maybe correct even if it makes redundant schema as compared to the second decomposition – Sachin Nov 17 '11 at 13:47
Yes. Second decomposition is a refinement to the first decomposition. – Bahribayli Nov 17 '11 at 15:18
I understand that both the decompositions are correct, but practically when a more efficient decomposition exists then shouldn't the redundant decomposition be in some sense wrong? – Sachin Nov 18 '11 at 14:50
No it is not wrong so then what about a schema that carry some anomalies and can't be decomposed to DKNF to remove anomalies? Does it remain wrong? Some times database designers stop normalization at a level because of practical issues or other design concerns. – Bahribayli Nov 19 '11 at 6:57
Thanks a lot for your explanation It really helped. But I hope the second decomposition is not wrong any sense? – Sachin Nov 19 '11 at 12:12
up vote 2 down vote

Given a schema r(A,B,C,D,E,F) and a set of functional dependencies

 R(A,B,C,D,E,F)
 •A --> BCD 
 •BC --> DE
 •B --> D
 •D --> A

Let's see. F is'nt on any of the FDs side. That means we use the armststrongs axionoms to sett F->F wich is a trivial FD.

You don't need primary keys on this one. What you have to do is to find the candidate key(s), wich is a minimal off a superkey (there might be also several c.keys). I can almost imediatly see that AF is ("the one of probably many") c.key.

Then minimize the right side such as

  A --> B
  A--> C
  A--> D and so on and so on... Find out wich FD is on BCNF. If not then get down to buisness.

Try splitting them up by using the FDs. Eventually you will find out the true and lossless decomposition if there is. Hint: No rush...

Editing: What I can immediatly comment on your topic is, the relational schema has a standard 1NF(that's just the way it always is). What I mean by that is, when you are looking to check wheter the RS is in other NFs. Does it complete the rules to be in the 3NF, 2NF and so on... If it violates them all! then it must be in 1NF. So your objective is to find wich NF your particular relational schema you are working on.

Noteworthy thing that might come handy is. Write up all the FDs in front of yourself and create a LMR table. Lets say we check A. It appears on both side, therefor it's in the middle. B is also on both sides. So on and so on...

L(eft) | M(iddle) | R(ight)
       | ABCD     | E

Since F is not in any FDs, it must be in the candidate key.

As you might have noticed. Most of the attributes are in the middle. That means you have to check ALL of them with closure testing for if they are in a candidate key. After all this bla bla you probably think I talk only about the candidate key, but what makes it BCNF. That is, a non-trivial FD is if and only if X -> A, where X is a superkey. Check if you find X in any of the c.keys you found. If you find a match. Voila! The statement is true and therefor the RS is in BCNF.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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