database-normalizationfunctional-dependencies3nfbcnf# Understanding BCNF Functional Dependency

I was following this tutorial for BCNF decomposition. The functional dependencies given are:

```
A->BCD
BC->AD
D->B
```

These are concerned with the relation R(A,B,C,D). The conditions for BCNF include:

The relation must be in 3NF and when X->Y, X must be a

superkey

The given relation doesn't have a transitive FD but D->B is a partial FD--or is it that the three FDs represent 3 separate relations?

If they represent 3 separate relations, why is it that D is not a key and if they are all in the same relation then D->B is a partial functional dependency.

Solution

If we write the given set of FDs with *singleton right-hand side*, we have -

A->B

A->C

A->D

BC->A

BC->D

D->B

We can see at once **2** transitive dependencies. We have A->D and D->B so we don't need A->B and also we have BC->A and A->D so we don't need BC->D. So now we have -

A->C

A->D

BC->A

D->B

or

A->CD

BC->A

D->B

The keys here are **A**, **BC** and **CD**. Since each attribute of the relation **R** comes at least once in each of the keys, all the attributes in your relation **R** are prime attributes.

*Note that if a relation has all prime attributes then it is already in 3NF.*

**Hence the given relation R is in 3NF**. I hope you get why you are completely wrong here - *"The given relation though doesn't have a transitive FD but D->B is a partial FD "*. I just proved that the relation is in 3NF which is a higher normal form then 2NF and hence in turns proves that the relation is in 2NF and hence **no partial dependency**.

To be in BCNF, for each functional dependency *X->Y*, *X* should be a key. We see that the last functional dependency D->B violates this since D is not a key. Therefore to convert into BCNF we can break our relationship **R** into **R _{1}** and

R_{1}(A,C,D)

R_{2}(B,D)

- Partial Functional Dependency , still in 3NF?
- Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
- Is it really better to respect the normal forms and create an additional table for one simple field?
- What exactly does database normalization do?
- relational database design (normalizing many-to-many mappings)
- Is this database relation in 3NF and BCNF and why?
- Understanding Database Normalization - Second Normal Form(2NF)
- Intuition behind 2NF in normalization of relational databases
- why is there no MVD, is it because of the interrelationships between the attributes, and why is it in 4NF and not in 5NF?
- Functional dependencies after normalization
- Does it violate any normalization rules if I add a boolean column to a db table?
- First Normal Form Database Normalization
- 4NF, Multivalued Dependencies without Functional Dependencies
- Database Design: Composite key vs one column primary key
- Pros and Cons of Constraints across Many-To-Many relationship
- Database Normalization BCNF decomposition
- Finding a relation in 3NF but not in BCNF
- Make Into First Normal Form
- How does this relation produce candidate keys?
- What are database normal forms and can you give examples?
- Realm relationships vs embedded objects
- Is redundant data an acceptable trade-off in a normalized database structure?
- What's the better database design: more tables or more columns?
- 3NF and lossless decomposition of relation and functional dependencies
- Problems creating table after normalisation
- Normal forms - 2nd vs 3rd - is the difference just composite keys? non trivial dependency?
- Can you move compound keys and/or foreign keys to other tables when normalizing to 3NF (third normal form)
- Determining if this data is really in 4th normal form?
- Joining with a json column makes the result very slow
- Normalisation--2NF vs 3NF