Test: Normal Forms - Civil Engineering (CE) 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.