In the context of relational database normalization, which of the following statements is/are true?

Question

A database has 25,000 fixed-length records of 100 bytes (primary key 15 bytes). The data file is block-aligned so each record is fully contained within a block. The file is indexed by a primary index file, also block-aligned and ordered. Block size is 1024 bytes and a block pointer is 5 bytes. Each index entry stores the block’s anchor key (15 bytes) and a pointer (5 bytes). Binary search on an index file of $b$ blocks needs $\lceil \log_2 b \rceil$ block accesses in the worst case. Given a key, the number of block accesses required to identify the data file block that may contain the record, in the worst case, is ____________.

Answer 1

A quick way to see this is to picture the normal forms as nested boxes, where $1NF \supseteq 2NF \supseteq 3NF \supseteq BCNF$, with BCNF being the strictest and 1NF the loosest.

There is a proven synthesis procedure that turns any relation and its functional dependency set into a 3NF decomposition that is both lossless join and dependency preserving at the same time. Since this procedure always succeeds, option (A) holds.
That same 3NF decomposition automatically satisfies the looser 2NF and 1NF conditions too, since 3NF implies 2NF implies 1NF. The guarantee therefore carries downward to weaker normal forms, so dependency-preserving 2NF and 1NF decompositions are also always achievable. This makes option (B) correct and shows option (D) is wrong, since it wrongly denies this for 2NF.
BCNF sits above 3NF in strictness, and that is exactly where the guarantee breaks. Take $R(A,B,C)$ with $AB \to C$ and $C \to B$: $R$ fails BCNF because of $C \to B$, but any lossless BCNF split, such as $(A,C)$ and $(B,C)$, destroys the ability to check $AB \to C$ without rejoining. So a dependency-preserving BCNF decomposition is not always possible, confirming option (C).

Putting these together, (A), (B) and (C) hold while (D) does not.

$$\boxed{\text{Correct options: (A), (B), (C)}}$$

Answer 2

A quick way to see this is to picture the normal forms as nested boxes, where $1NF \supseteq 2NF \supseteq 3NF \supseteq BCNF$, with BCNF being the strictest and 1NF the loosest.

There is a proven synthesis procedure that turns any relation and its functional dependency set into a 3NF decomposition that is both lossless join and dependency preserving at the same time. Since this procedure always succeeds, option (A) holds.
That same 3NF decomposition automatically satisfies the looser 2NF and 1NF conditions too, since 3NF implies 2NF implies 1NF. The guarantee therefore carries downward to weaker normal forms, so dependency-preserving 2NF and 1NF decompositions are also always achievable. This makes option (B) correct and shows option (D) is wrong, since it wrongly denies this for 2NF.
BCNF sits above 3NF in strictness, and that is exactly where the guarantee breaks. Take $R(A,B,C)$ with $AB \to C$ and $C \to B$: $R$ fails BCNF because of $C \to B$, but any lossless BCNF split, such as $(A,C)$ and $(B,C)$, destroys the ability to check $AB \to C$ without rejoining. So a dependency-preserving BCNF decomposition is not always possible, confirming option (C).

Putting these together, (A), (B) and (C) hold while (D) does not.

In the context of relational database normalization, which of the following statements is/are true?

Show Hint

The Correct Option is A, B, C

Solution and Explanation

Questions Asked in GATE CS exam