Question

Discuss the normal forms for a relational database schema. What does functional dependency mean? Given is the relational schema R( A, B, C, D, E ) as well as the functional dependencies • AC → BDE • B → D • A → E. Convert the schema into its 3rd normal form.

Answer 1

Normal Forms:-

Normalization of data is a process of analyzing the given relation schema based on their FD's and primary keys to achieve the desirable properties of (1) minimizing redundancy and (2) minimizing the insertion, deletion, and update anomalies

The following are the different normal forms:-

1 NF

2 NF

3 NF

4 NF

BCNF

All those normal forms are in one way or the other normalize the given Relational schema R

Functional Dependency:-

A functional dependency (FD) is a constraint between two sets of attributes in a relation from a database

Eg:- X $\rightarrow$ Y , here X is functionally depends on Y

Given Relational database schema R(A,B,C,D,E)

and the Functional dependencies FD's are AC $\rightarrow$ BDE , B $\rightarrow$ D , A $\rightarrow$ E

From the given FD's , let AC be the candidate key and verifying is done below as follows:-

AC⁺ = [ACBDE]

Since AC determines all the attributes , AC is the candidate key.

The FD's are:-

AC $\rightarrow$ BDE

B $\rightarrow$ D

A $\rightarrow$ E

Decomposing to 3NF:-

In the FD AC $\rightarrow$ BDE

AC is the candidate key and it determines the non - prime attributes. Hence, it is in 3 NF.

The FD B $\rightarrow$ D is not in 3NF because a non - prime attribute determines another non prime attributes.

The FD A $\rightarrow$ E is not in 3NF because a part of candidate determines another non prime attribute.

So , the decomposition is as follows:-

R1( B,D )

R2( A,E )

for the candidate key , there is one more i.e, R3( A,C )

Note:-

Prime attributes:-

The attributes that are part of the candidate key or the super key.

Non-Prime attributes:-

The attributes that are not part of the candidate key or the super key.