$R(A,B,C,D,E)$ and Fd's are $F\{AB\rightarrow CD,ABC\rightarrow E,C\rightarrow A\}$
Candidate keys are: $AB,CB$
Prime attributes or key attributes $=A,B,C$ and non-prime attributes or non-keys attributes $=D,E$
Second Normal Form$(2NF)$:- A relation is in $2NF$ if it has No Partial Dependency. i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table.
Partial Dependency – If the proper subset of candidate key determines non-prime attribute, it is called partial dependency.
The given Fd's are in $2NF$.
Third Normal Form$(3NF)$:-
- A relation is in third normal form, if there is no transitive dependency for non-prime attributes as well as it is in second normal form.
- A relation is in $3NF$ if at least one of the following condition holds in every non-trivial function dependency $X\rightarrow Y$
- $X$ is a super key.
- $Y$ is a prime attribute (each element of $Y$ is part of some candidate key).
Transitive dependency – If $A\rightarrow B$ and $B\rightarrow C$ are two FD's then $A\rightarrow C$ is called transitive dependency.
The given Fd's are in $3NF$.
Boyce-Codd Normal Form $(BCNF)$:-
A relation $R$ is in BCNF if $R$ is in Third Normal Form and for every FD, LHS is super key. A relation is in BCNF iff in every non-trivial functional dependency $X \rightarrow Y, X$ is a super key.
The given Fd's are not in $BCNF$ because Fd's $C\rightarrow A, C$ is not super key.
If the relation is not in $BCNF$, then no need to check $4NF$.
Important Points:
- If a relation is in BCNF, then 3NF is also satisfied.
- If all attributes of relation are prime attribute, then the relation is always in 3NF.
- Every Binary Relation ( a Relation with only 2 attributes ) is always in BCNF.
- If a Relation has only singleton candidate keys( i.e. every candidate key consists of only 1 attribute), then the Relation is always in 2NF( because no Partial functional dependency possible).
References:
So,the correct answer is $(D)$.