Test: Functional Dependencies & Normalization- 1 - Computer Science Engineering (CSE) MCQ

3NF is considered to be most adequate for relational database design. It always preserves dependency as compare to BCNF which is more io restrictive form of 3NF. Although it does not eliminates redundancy due to MVD’s but it does satisfies two essential properties.
1. If eliminates redundancy due to FD’s. 
2. It preserve FD’s
4NF is not considered to be adequate because it does not preserve FD’s always. 

Conditions for various normal forms:

1. 1 NF – A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only.
2. 2 NF – A relation R is in second normal form (2NF) if and only if it is in 1NF and every non-key attribute is fully dependent on the primary key.
3. 3 NF – A relation R is in third normal form (3NF) if and only if it is in 2NF and every non-key attribute is non-transitively dependent on the primary key.
4. BCNF – A relation R is in Boyce-Codd normal form (BCNF) if and only if every determinant is a candidate key.

Example:
Relation R(XYZ) with functional dependencies {X -> Y, Y -> Z, X -> Z}.
Notice here Y -> Z, in question it is not mention that non prime attribute is only dependent on primary key so this FD is perfectly valid.
This relation is in 2NF but not in 3NF because of every non-key attribute is transitively dependent on the primary key. Here {X} will be candidate key.

Since there is no FD’s for which W functionally depends on other attributes.

Since for Database relation R(a, b, c, d) all values are atomic and

Here neither of a, b, c, d are primary key.
The primary key is 'ab‘ prime attributes = {a, b} here part of key (a in a → c and in b in b → d) is dependent on non key hence there is a partial dependency and the relation is not in 2 NF.
Hence (a) is the correct option

Third normal form is considered adequate for relational database design, it is inadequate in all situations with the relation having multiple, composes or overlapped candidate keys.

We can clearly see that transaction is the weak entity with the discriminator (key for weak entity) as transaction number. This discriminator is used to uniquely identify each transaction of a particular account.

In order to identify each transaction of any account the key of strong entity with the addition of the discriminator of weak entity can be taken. Here [Account number, transaction number] act as the primary key of weak entity. This is because weak entity has no existence without strong entity.

BCNF is the more restrictive compared to 3NF. 3NF is more restrictive as compared to 2NF and so on.
Hence option (d) is correct.

According to the given set of functional dependencies

Hence (d) is the correct option.