ITPEC IP October 2019 Question 28
The result of the Information-Technology Engineers Examination by some of the employees of a company is shown below. In order to manage this table with a relational database, which of the following is an appropriate method to partition the table into two (2) tables? Here, this company has employees with the same family name and first name.
Explanation
An employee table and a results table linked by Employee_ID — employee facts are stored once, each exam result separately.
Employee_name and Date_of_birth depend only on the employee: 0001 is always Hanako Sato, born 1985/4/29, yet the single table repeats them for every exam taken. Option (a) stores them once per Employee_ID. Examination_category, Examination_date and Pass_or_fail describe one exam attempt, so they stay together in the results table, which keeps Employee_ID to say whose result each row is. Because the company has employees with the same name, only the ID can make that link.
Answer (a)
Why not others:
- •(b) — links results by Employee_name; with two employees of the same name, a result could not be traced to one person
- •(c) — Examination_date sits in the employee table, apart from its category and result; for 0001, who sat on 2013/4/21 and 2013/10/20, nothing shows which result belongs to which date, and the name is still repeated
- •(d) — the same split of the date from its result, with Employee_name now repeated on every result row
Key rule: Put each attribute in the table of the key it depends on, and link tables by a unique identifier, never by a name.
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 IP past-paper collection or Report an issue.