# Can we decompose a schema into BCNF when it is not even in 1NF

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.

• "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? Nov 17, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 11:41

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.

• 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? Nov 18, 2011 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. Nov 19, 2011 at 6:57

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.