ITPEC FE Morning October 2018 Question 27
“a → b” represents the fact that when the value of attribute a is determined, the value of attribute b is determined uniquely. For example, “Employee number → Employee name” represents that when the employee number is determined, the employee name is determined uniquely. Based on this notation, when the relations between attributes a through j are established as shown in the figure below, which of the following is an appropriate combination of three (3) tables that defines the relations in a relational database?
Explanation
Table 1 (a, b, c, d, e), Table 2 (b, f, g, h), Table 3 (e, i, j) — make one table per determinant, holding it as the key and everything it determines.
The figure has three determinants: a determines b, c, d, e; b determines f, g, h; e determines i, j. Each becomes the primary key of its own table. Table 1 keeps b and e as ordinary columns, and they act as foreign keys into Tables 2 and 3, so every dependency is stored exactly once and nothing is lost. Putting f, g, h or i, j into Table 1 would create transitive dependencies through b and e and repeat the same values in many rows.
Answer (b)
Why not others:
- •(a) — Table 1 holds only a, so nothing records which b, c, d, e belong to which a; Table 3 puts f, g, h beside i, j without b or e, their determinants
- •(c) — Table 1 keeps the transitive dependency a → b → f, g, h, and c, d and e are split off from a, which determines them
- •(d) — no table contains both a and b, or a and e, so the dependencies a → b and a → e are lost
Key rule: In third normal form each determinant keys its own table with its dependents, and the determinant stays in the referring table as a foreign key.
Databases · AI-written, may contain errors
This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official FE past-paper collection or Report an issue.