SQL 1. On further examination of your existing relational schema, your boss is t
ID: 3716127 • Letter: S
Question
SQL
1. On further examination of your existing relational schema, your boss is troubled by the fact that two users can have the same phone number as one of their numbers but with different types (e.g., one could say it's a mobile phone and the other could say it's a home phone). She wants to make sure that when two users do have a phone number in common, the type information is consistent.
(a) Start by looking at the existing Phone table. What non-trivial functional dependencies does the current table have?
(b) What additional functional dependency is needed to express your boss' rule about phone type consistency?
(c) Given this new functional dependency, what is the new primary key for the Phone table given its current schema?
(d) What normal form is the Phone table currently in after adding the boss’s new rule? Briefly show your reasoning.
(e) Decompose Phone into multiple tables to produce a well-normalized design (BCNF if possible, 3NF if not) if the current design isn't already there. Your solution must be lossless join and dependency preserving. List the decomposed tables along with their candidate keys.
Explanation / Answer
The phone table is not mentioned in the question.
But if the schema is, Phone (user_id , phone_no, type)
let, user_id be A , phone_no be B , and type be C then
for
(a). if two user can have same phone_no with different type the we can uniquely identify each tuple by BC,
therefore the non-trivial dependency must be BC --> A.
(b). now for phone type consistency, every phone_no must have same type , therefore type can br determined
phone_no, additional functional dependency will be B --> C.
(c). to uniquely identify each tuple the primary key must be ABC.
(d) and (e) can only be solved if we have all the functional dependency which are not mentioned in the question.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.