Instruction: Sometimes a key can technically functionally determine two attribut
ID: 3891343 • Letter: I
Question
Instruction:
Sometimes a key can technically functionally determine two attributes but does so transitively. An example would be: Employee -> Employee’s Boss, Employee’s Boss’s Email Address The problem is that a boss likely has several people that report to them. So, if everywhere we record an employee’s boss, we also record their boss’s email address, it’s possible that the same boss might appear to have different email addresses when looked up by one employee vs. another. Solving this problem is what 3NF is all about. Again, this is not just about reducing redundancy but also reducing the chance for inconsistent data. To solve this problem, we’d want to decompose the design into two tables: Employee -> Employee’s Boss Boss -> Boss’s Email Address Aside: This decomposition is sufficient to meet the requirements of 3NF. However, an even better design would involve realizing that a boss is also an employee and store all employees in one table and have the relationship between employee and boss recursively refer to other rows in the same table: Employee -> Employee Email Address, Boss The Boss attribute would refer to the Employee column of another row in that same table.
Question 10.) Should the following design be decomposed into two or more tables? Why or why not? And if decomposing, what columns belong in each resultant table? Doctor, Patient, Date and Time -> Diagnosis, Typical Prognosis (By Typical Prognosis, assume of the diagnosis in general, not the particular doctor diagnosing the particular patient.) Yes. Doctor, Patient, Date and Time -> Diagnosis Patient -> Typical Prognosis
Explanation / Answer
Columns in Table are:
Doctor, Patient, Date and Time, Diagnosis Patient, Typical Prognosis
Functional Dependencies are:
Doctor, Patient, Date and Time -> Diagnosis Patient, Typical Prognosis
Doctor, Patient, Date and Time -> Diagnosis Patient -> Typical Prognosis
In the example above we had:
Employee -> Employee’s Boss, Employee’s Boss’s Email Address
Boss -> Boss's Email Address
To solve this in the example above we made 2 tables so we will be doing similarly here in this example.
R1(Doctor, Patient, Date and Time, Diagnosis Patient)
R2(Diagnosis Patient, Typical Prognosis)
Underlined attributes are key attributes.
So the above two tables meet the requirements of 3NF