Which normal form is the highest that satisfies the functional dependencies(DBMS)?

DBMSDatabaseBig Data Analytics

Let's take the dependencies F= {AB->CDEF, AF->ED, F->B} which ONE of the following is the highest normal form that a table R=ABCDEF could satisfy.

Select any one from the following −

  • No normal form would be satisfied.
  • Boyce-Codd Normal Form
  • 1st Normal Form
  • 2nd Normal Form
  • 3rd Normal Form

Given Functional dependencies of F are as follows −

AB ---> CDEF

AF ---> ED

F ---> B

Now in R.H.S (Right Hand Side) of Functional dependencies of F, attributes B,C,D,E,F are all present but attribute A is missing. So, attribute A must be the part of Super Key.

We have to find the closure of A (A+),

A+ = A only, So attribute A alone can't be the key. Thus we have to combine A with other attributes of F and then find candidate keys of F.

Find the Candidate Keys of F

AB+ = ABCDEF

AC+ = AC

AD+ = AD

AE+ = AE

AF+ = AFBCDE

Now since closure of (AC, AD, AE) is not covering all the attributes of F. So again, we have to combine AC, AD, AE to check whether their combinations become a candidate key of F or not.

So ACD+ = ACD

ACE+ = ACE

ADE+ = ADE

ACDE+ = ACDE

So finally, we are confirmed that AB and AF are the only candidate keys of F as their closure is covering all the attributes of F.

So prime attributes of F are A, B, F and non-prime attributes of F are C, D, E.

Check for Highest Normal Form (NF)

We will start from highest normal forms in which BCNF form is the highest normal form and if F is in BCNF normal form then it will also be under 1NF, 2NF and 3NF.

Check For BCNF

All functional dependencies of F X ---> Y, where all X must be the super key.

Out of all functional decencies of F below, only AF and AB are the super keys but F is not a super key.

(AB ---> CDE, AF ---> ED, F ---> B)

So F is not in BCNF as all X of (X ---> Y) are not super keys.

Check for 3NF

For F to be in 3NF there shouldn't be an occurrence of transitive functional dependency for non prime attributes or say no non-prime attributes in F should derive to other non-prime attributes.

And in F (X --> Y) All X of Functional dependency are prime attributes as AF, AB are super keys and F is a prime attribute. So, no occurrence of transitive dependency and thus it is in 3NF.

And F is also in 2NF as no partial dependencies are there, i.e. no part of candidate keys are deriving to any non-prime attributes.

If F is in 3NF then it is also in 2NF and 1NF. As 3NF is the highest Normal Form out of them.

Conclusion

The table R(A,B,C,D,E) satisfies 3NF.

raja
Published on 06-Jul-2021 12:32:11
Advertisements