100 Crack-the-Exam MCQs: Database Design and Normalization
An institutional-grade, highly rigorous test bank specifically tailored for PPSC, PMS, CSS, NTS, and specialized full-stack/database engineering civil service examinations. Challenge your mastery over functional dependencies, normal forms (1NF to DKNF), and lossless joins.
Q1. If a relation scheme $R$ has functional dependencies $F$, what is the primary objective of finding the minimal cover (canonical cover) $F_c$?
Explanation: A canonical or minimal cover removes extraneous attributes and redundant functional dependencies, ensuring the set of FDs is simplified to its core footprint without changing the closure $F^+$.
Q2. A relation $R(A, B, C, D)$ satisfies the functional dependencies $A \rightarrow B$ and $B \rightarrow C$. What type of dependency exists between $A$ and $C$?
Explanation: Since $A \rightarrow B$ and $B \rightarrow C$, then $A \rightarrow C$ via transitivity. If $A$ is the primary key, this non-prime to non-prime dependency represents a transitive dependency violated in 3NF.
Q3. In database design, 2NF directly targets and eliminates which of the following anomalies/phenomena?
Explanation: 2NF requires that the relation is in 1NF and no non-prime attribute is functionally dependent on a proper subset of any candidate key (eliminates partial dependencies).
Q4. Which normal form deals explicitly with "MVDs" (Multi-valued Dependencies) to avoid independent multi-valued facts from causing massive redundancy?
Explanation: 4NF handles multi-valued dependencies. A relation is in 4NF if for every non-trivial multi-valued dependency $X \twoheadrightarrow Y$, $X$ is a superkey.
Q5. Under what condition is a decomposition of relation $R$ into $R_1$ and $R_2$ guaranteed to be a lossless join decomposition?
Explanation: For a binary decomposition to be lossless, the common attributes must form a superkey for at least one of the decomposed schemas.
Q6. Which of the following statements is true regarding BCNF and dependency preservation?
Explanation: Unlike 3NF (which always allows lossless-join and dependency-preserving decomposition), BCNF can sometimes force you to choose between breaking down a relation into BCNF or preserving all FDs.
Q7. What does Armstrong's Axiom of 'Pseudo-transitivity' state?
Explanation: Pseudo-transitivity states that if $X \rightarrow Y$ holds and $WY \rightarrow Z$ holds, then $WX \rightarrow Z$ holds.
Q8. Fifth Normal Form (5NF) is also referred to as:
Explanation: 5NF eliminates redundancies caused by join dependencies; hence it is mathematically referred to as Project-Join Normal Form (PJNF).
Q9. If a relation is in BCNF, it is definitively guaranteed to be in:
Explanation: BCNF is a stricter extension of 3NF. Thus, any relation satisfying BCNF structurally complies with 1NF, 2NF, and 3NF automatically.
Q10. In a relational database design, the ultimate theoretically perfect normal form where all constraints are explicit consequences of domains and keys is:
Explanation: Domain-Key Normal Form (DKNF) is the ultimate ideal structure where every constraint can be verified strictly via domain properties and key definitions.
Q11. What type of anomaly occurs when a user cannot add record details because an entire independent entity's information is mandatory?
Explanation: Insertion anomalies happen when a structural dependency blocks data addition until another unrelated fact is supplied.
Q12. Let attribute closure $X^+ = R$. This implies that the set of attributes $X$ is a:
Explanation: If an attribute closure yields all attributes of relation $R$, it is a superkey. If it has no smaller subset that does the same, it is a candidate key.