Normalization And Functional Dependencies (FDs) : MCQs & PYQs with Explanation

Option C

Given the relation R(A, B, C, D) with functional dependencies:
A → B
B → C
C → D
Primary Key: A
Check for BCNF:
A relation is in Boyce-Codd Normal Form (BCNF) if for every functional dependency X→Y,  X is a candidate key or super key.
So, BCNF ensures that each non—key (non—prime) attributes is dependent only on the candidate key.
A→B: A is the primary key (Super Key): Satisfy BCNF condition.
B→C: B is not a Super Key : Violates BCNF condition.
C→D: C is not a Super Key : Violates BCNF condition.
Check for 3NF:
The relation is in 3NF if there is no transitive dependency i.e., no non—prime attribute can determine another non—prime attribute.
A→B: A is the prime attribute : Satisfy 3NF condition.
B→C: Both B and C are non—prime attributes : Violates 3NF condition.
C→D: Both C and D are non—prime attributes : Violates 3NF condition.
Check for 2NF:
The relation is in 2NF if there is no partial dependency i.e., no proper subset of candidate key can determine the non—prime attribute. 
The relation is at least in 2NF, if key contains only one attribute (here A is primary key, only one attribute no subset possible), then there is no partial dependency because one attribute has no subset. So, the relation is in 2NF. Among the given options the correct answer is The relation is not in BCNF.

Option B

Normalization organizes relational database tables to minimize data redundancy and prevent anomalies during insert, update, or delete operations.
A database is considered adequately designed when it reaches Third Normal Form (3NF) because it:

• Removes partial dependency as handled in 2NF.
• Removes transitive dependency.
• Ensures data is stored logically and efficiently.

Option B

Normalization up to Third Normal Form (3NF) is carried out in three logical steps.
First, the table is converted to First Normal Form (1NF) by removing repeating groups and multivalued attributes so that each field contains atomic values.
Next, the table is converted to Second Normal Form (2NF) by removing partial dependencies, ensuring that every non-key attribute is fully dependent on the entire primary key.
Finally, the table is converted to Third Normal Form (3NF) by removing transitive dependencies, so that no non-prime attribute can determine another non-prime attribute.

Option A

A Functional Dependency (FD) describes a relationship where one set of attributes uniquely determines another set of attributes in a relation. 
Key Dependency is a special case of functional dependency in which a key attribute determines all other attributes in the table.

Option D

First find the candidate key using the given functional dependencies.

• AB → CD
• C → E
• DE → P
• P → C
• B → G

Compute closure of AB:
AB⁺ = {A, B}
AB⁺ = {A, B, C, D}  Using AB→CD
AB⁺ ={A, B, C, D, E} Using C→E
AB⁺ ={A, B, C, D, E, P} Using DE→P
AB⁺ ={A, B, C, D, E, G, P} Using B→G
Thus AB is a candidate key.
Prime attributes = A, B
Non-prime attributes = C, D, E, G, P
For Second Normal Form (2NF), non-prime attributes should not depend on a part of a composite key.
But we have B → G. Here B is only a part of the candidate key (AB) and it determines G, which is a non-prime attribute. This is a partial dependency, which violates 2NF. Therefore, the relation R is not in 2NF.

Option C

To see which dependencies follow from the given ones, compute attribute closures using the given FDs: P→QR and RS→T. If an attribute set’s closure contains the right-hand side, that dependency is implied.
(a) 

P→R
Find P⁺ :
P⁺ = {P}
P⁺={P,Q,R} Using P→QR
R is in P+ ⇒ P→R inferred.
(b)

PS → T
Find PS⁺ :
(PS)⁺={P,S}
(PS)⁺={P,S,Q,R} Using P → QR
Now, (PS)⁺ has RS so Using RS→T
(PS)⁺={P,S,Q,R,T}
T is in (PS)⁺ ⇒ PS → T inferred.
(c)

R→T
Find R⁺ : 
R⁺ = {R}
No dependency with just R, so cannot go further.
T is not in R⁺ ⇒ R→T cannot be inferred.
(d)

PS → Q
Find PS⁺ : 
(PS)⁺ = {P,S)
(PS)⁺ = {P,S,Q,R) Using P→QR
Now, (PS)⁺ has RS so Using RS→T
(PS)⁺={P,S,Q,R,T}
Q is in (PS)⁺ ⇒ PS → Q inferred.

Option C

A relation is said to be in First Normal Form (1NF) when each attribute contains only atomic or indivisible values. This means there are no repeating groups or multivalued attributes in the table.

Option B

In the context of database design, Normalization is a process used to organize data efficiently within a database, reducing redundancy and improving data integrity.

For the given relation R(A,B,C,D,E,F)  with functional dependencies AB→C, C→ABDE, and ADE→F. We first identify the candidate keys. Here, C and AB are candidate keys because each can uniquely identify all attributes.
The prime attributes (part of key) are A, B, and C, while D, E, and F are non-prime.

To check which normal form the relation satisfies, we start with 2NF (Second Normal Form), which requires that all non-key (non-prime) attributes are fully functionally dependent on the entire primary key. Since no partial dependency exists here, the relation is in 2NF.
For 3NF, every functional dependency X→Y must satisfy means X is a superkey or Y is a prime attribute.

However, the dependency ADE→F violates this because ADE is not a superkey, and  F is not a prime attribute. This means the relation does not satisfy 3NF.
Finally, since the relation is not in 3NF, it also is not in BCNF (Boyce-Codd Normal Form), which is stricter than 3NF.

Option B

Armstrong’s Axioms are a set of inference rules used to derive all functional dependencies (FDs) logically implied by a given set of FDs in a database.

Statement I says that if a functional dependency B → C holds, then adding the same set of attributes A to both sides results in AB → AC. This is known as the Augmentation Rule. Statement II says that if B ⊆ A (B is a subset of A), then A → B always holds, which is called the Reflexivity Rule.

Option A

The decomposition of schema R(A,B,C,D) with functional dependencies A→B and C→D into R1(AB) and R2(CD), is dependency preserving but not lossless join.
Lossless Join: A decomposition is lossless if you can always recover the original relation by joining the decomposed relations, without losing any data. For a lossless join, the intersection of the schemas must be a key in at least one of the decomposed relations.
Dependency Preserving: A decomposition is dependency preserving if every functional dependency in the original relation is still enforceable in at least one of the decomposed relations without needing to join.

• The intersection between R1 and R2 is empty (AB∩CD=∅), so the lossless join condition fails.
• The dependencies A→B and C→D can still be enforced within R1 and R2 respectively. So, these dependencies are preserved.