Test: Normal Forms - Engineering Mathematics MCQ

If the truth table cannot be created, we consider the reduction to normal forms as an alternative.

Full form of DNF is Disjunctive Normal Form.

Since attribute D is not a part of any FD, it must be a part of the candidate key.
AD+ = {ABCDEFGH}
BD+ = {ABCDEFGH}
ED+ = {ABCDEFGH}
FD+ = {ABCDEFGH}
CD+, GD+, and HD+ do not all the attributes. So, they can not be candidate keys.
Candidate keys are AD, BD, ED, and FD.
A → BC, B → CFH, and F → EG are partial dependencies. So, the relation is not in 2NF.
Hence the relation is in 1NF but not in 2NF.

Concept:
Normalization: Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies.

1NF (First Normal Form):

• Each table cell should contain a single value.
• Each record needs to be unique.

2NF (Second Normal Form):

• It should be in 1NF.
• Single Column Primary Key that does not functionally dependant on any subset of candidate key relation.

3NF (Third Normal Form):

• It should be in 2NF.
• It has no transitive functional dependencies.

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 –> Y, X is a super key.

The given relation schema:
Singer(singerName, songName).

• Every Binary Relation ( a Relation with only 2 attributes ) is always in BCNF. If a relation is BCNF then it should be in 3NF, 2NF, 1NF.

Hence Singer(singerName, songName) is Boyce-Codd Normal Form (BCNF).
Hence the correct answer is BCNF.

X = (PQRS)

Set of functional dependencies

F = {QR → S, R → P, S → Q}

QR → {Q, R, S, P}

SR → {S, R, P, Q}

QR and SR are keys of Relation schema X

R → P …

part of key (R) → non key(P)

It is partial dependency and hence not in 2nd normal form and therefore not in BCNF (Given)

X is decomposed into two schemas Y and Z, where Y = (PR) and Z = (QRS)

For Y = (PR)

R → P

Y is in BCNF because binary attribute (R is a key).

For Z = (QRS)

QR → S

S → Q

SR → {R, S, Q}

QR → {R, Q, S}

QR and SR are keys of Relation schema X

Z is not in BCNF because

S → Q and S is not Super key.

∴ statement I is incorrect

Dependency Preserving:

R → P is in Y

QR → S in Z

S → Q is in Z

Hence, it is dependency preserving.

Lossless:

Y ∩ Z = R which is key of Y.

Therefore, it is Lossless

Statement II is correct.

Hence, option 3 is the answer

Concept:
If A → B and B → C then A → C is logically implied to A → B and B → C FDs this is nothing but transitivity rule.

Explanation:
A → B
B → H
Then we can say that A->H which is logically implied by above both FDs
So option 4 is the correct answer.

There are 2 types of normal forms in which reduction can be performed.

The following are the types of normal forms to which reduction can be performed -

• Disjunctive Normal Form
• Conjunctive Normal Form

Concept: A relation is in 1NF if every values in the relation are atomic.

A relation R with nontrivial functional dependency X → A, where X is not a superkey and A is a non-prime attribute and X is not a proper subset of any key is in called to be in 2NF.
A relation R with nontrivial functional dependency X → A, where X is not a superkey and A is a prime attribute, is called to be in 3NF.
A relation R with nontrivial functional dependency X → A, where X is a superkey is called to be in BCNF.

Explanation:
Statement I: corresponds to a relation that is in 3NF but not in BCNF, because for BCNF, X must be a superkey.

Statement II: corresponds to a relation that is only in 2NF by definition.

Statement III: corresponds to a relation that is not even in 2NF. It is in 1NF.

Statement IV: corresponds to a relation that is not even in 1NF.

Statement 1: TRUE
BCNF (Boyce Codd Normal Form):
A relation R is in BCNF whenever a non – trivial functional dependency X → A holds in R, where X is the super-key of R.
A binary relation is always in BCNF. A binary relation contains only two attributes.
Functional dependency that is possible from a binary relation is one.

Example:
Consider R(A, B), in this only one functional dependency is possible either A → B or B → A
In both the cases, left hand side will be the super key. In this way R(A, B) is always in BCNF.
If a relation is in BCNF then it is in 1NF, 2 NF and 3 NF

Statement 2: FALSE
Set 1 = {AB → C, D → E, AB → E, E→ C}
Set 2 = {AB → C, D → E, E → C}
Set 2 cannot derive   AB → E since in set 2 (AB)+ = {A, B, C}
The two sets of functional dependencies are not the same and hence one cannot be minimal of other.