Functional dependencies interview questions, with answers

The questions, with answers

1. 1.What does a functional dependency X → Y actually mean?

   That the value of X determines the value of Y: any two rows that agree on X must agree on Y. roll_no → name says two rows with the same roll number cannot have different names. It is a statement about every possible legal state of the table, not about the data that happens to be there today — a business rule, decided by the designer. The direction matters: student_id → dept does not imply dept → student_id, since a department has many students. An FD is trivial when Y is a subset of X (name, dept → name always holds) and non-trivial otherwise; only non-trivial FDs carry information, and only they matter for normalisation.

2. 2.How do you check whether a functional dependency holds against actual data?

   Look for a counterexample: two rows with the same X value and different Y values. If Item has rows (sku 'ABC', price 100) and (sku 'ABC', price 120), then sku → price does not hold in this data, because one sku maps to two prices. If no such pair exists, the data is consistent with the FD — but that does not prove the FD is a rule of the schema, only that today's rows do not violate it; an empty table is consistent with every FD. Interviewers use this to separate the two ideas: data can refute a dependency, never establish one. In SQL the check is GROUP BY X HAVING COUNT(DISTINCT Y) > 1, which lists the offending X values.

   -- sku values that map to more than one price: any row here refutes sku -> price
   SELECT sku
   FROM Item
   GROUP BY sku
   HAVING COUNT(DISTINCT price) > 1;

3. 3.What are Armstrong's axioms, and why do they matter?

   Three rules that generate every dependency implied by a set of FDs. Reflexivity: if Y is a subset of X, then X → Y. Augmentation: if X → Y, then XZ → YZ for any Z. Transitivity: if X → Y and Y → Z, then X → Z. They are sound (they never derive a false FD) and complete (they derive every implied one), so "is this FD implied" always has a mechanical answer. Three convenient rules follow from them: union (X → Y and X → Z give X → YZ), decomposition (X → YZ gives X → Y and X → Z), and pseudo-transitivity. In practice nobody derives by hand — attribute closure is faster — but the axioms are what makes the closure algorithm correct, and interviewers ask for them by name.

4. 4.How do you compute the closure of a set of attributes, and what is it used for?

   Start with X⁺ = X. Repeatedly scan the FDs: whenever the left side of an FD is contained in X⁺, add its right side. Stop when a full pass adds nothing. For R(A, B, C, D, E) with A → B, BC → D and D → E: AC⁺ starts as {A, C}, A → B adds B, BC → D now applies and adds D, D → E adds E, so AC⁺ = {A, B, C, D, E}. That answers three questions at once. Is X → Y implied? Yes if Y is inside X⁺. Is X a super key? Yes if X⁺ contains every attribute — so AC is a super key here, and because A⁺ is only {A, B} and C⁺ is only {C}, neither part works alone, which makes AC the candidate key. And which FDs hold in a decomposed table? Those whose closure, restricted to that table's attributes, still reaches the right side.

5. 5.What is a canonical cover, and how do you compute one?

   A minimal set of FDs equivalent to the original: every right side is a single attribute, no left side has an attribute you could remove without changing what is implied, and no whole FD is redundant. Compute it in three steps. Split right sides: A → BC becomes A → B and A → C. Remove extraneous left-side attributes: for AB → C, check whether A⁺ under the set already contains C; if so B is extraneous and the FD becomes A → C. Remove redundant FDs: drop an FD and see whether its right side is still in the closure of its left side under the remaining FDs. For G = {A → B, B → C, AC → D, A → D}: in AC → D the attribute C is extraneous, because A⁺ under G already contains D, so it collapses to A → D and the duplicate is dropped; then A → D is not redundant, because A⁺ under {A → B, B → C} alone is {A, B, C}. The cover is {A → B, B → C, A → D}.

6. 6.What is a lossless-join decomposition, and how do you test for it?

   Splitting a relation into two smaller ones is lossless if joining them back gives exactly the original rows — no extra, spurious tuples. It is the property that makes decomposition safe: a lossy split invents rows on rejoin, which is worse than the redundancy you were removing. For a split of R into R1 and R2, the test is that the shared attributes, R1 ∩ R2, form a super key of at least one side: R1 ∩ R2 → R1 or R1 ∩ R2 → R2. Splitting Employee(emp_id, name, dept_id, dept_name) into (emp_id, name, dept_id) and (dept_id, dept_name) is lossless because dept_id → dept_name makes dept_id a key of the second table; splitting it into (emp_id, name) and (name, dept_id, dept_name) is lossy, because name determines nothing. For more than two pieces there is a tableau (chase) algorithm, but two-way splits cover most exam questions.

7. 7.What does dependency preservation mean, and why can it conflict with BCNF?

   A decomposition preserves dependencies if every original FD can be checked by looking at a single decomposed table — no join needed to enforce it. That matters because an FD you can only check across a join is one the database cannot enforce with a plain key or unique constraint. The conflict: BCNF demands that every determinant be a super key, and some FD sets cannot reach BCNF without splitting an FD across tables. The classic example is (student, course, instructor) with student, course → instructor and instructor → course: to reach BCNF you split on instructor → course, and the first FD is then only checkable by joining. 3NF is the compromise — always achievable losslessly with all dependencies preserved, at the price of a little redundancy — which is why 3NF, not BCNF, is the usual practical target.

8. 8.What is a multivalued dependency, and how does it lead to 4NF?

   A multivalued dependency X →→ Y says that the set of Y values associated with an X value is independent of the other attributes. If a course has a set of textbooks and, separately, a set of instructors, storing (course, textbook, instructor) in one table forces every combination to be listed — three textbooks and two instructors mean six rows — and adding a textbook means adding one row per instructor. That repetition is not caught by any functional dependency, because nothing determines anything: it is a multivalued dependency, course →→ textbook and course →→ instructor. Fourth normal form requires that every non-trivial multivalued dependency has a super key on its left, which here means splitting into (course, textbook) and (course, instructor). It is the normal form beyond BCNF that interviewers most often ask you to name and explain in a sentence.

How the diagnostic asks it

One question from the DBMS bank, exactly as a sitting would show it. The bank has 4 on functional dependencies and 60 across DBMS.