instruction : Sometimes a key that is made up of more than one attribute (like D

Question

instruction : Sometimes a key that is made up of more than one attribute (like Doctor, Patient) can technically functionally determine another attribute (like the Patient’s birth date). But it is also true that you don’t need Doctor in order to functionally determine a Patient’s birth date. This situation is what 2NF is all about. Making sure that all functionally determined attributes need all of the key columns to functionally determine them. In cases where you don’t, you need to decompose the related data into two or more tables. One for the full key (and any attributes you need the full key to determine), and one for each attribute that is only determined by a part of they key (along with that part of the key). For example, if you had: Doctor, Patient -> Last Appointment Date and Time, Patient Birth Date You would want to create two tables. One for: Doctor, Patient -> Last Appointment Date and Time And one for: Patient -> Patient Birth Date The problem this solves is that if a patient is seeing more than one doctor (perhaps a general practitioner and a neurologist), you don’t have to duplicate the patient’s birth date for each doctor he/she visits. This is redundant and additionally makes it possible for the “two” birth dates to differ in the database when clearly a person doesn’t have two birth dates.

Question 7.) 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?

Patient, Hospital Room Number -> Hospital Floor

question 8.) 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 -> Hospital Admit Date and Time, Doctor’s Pager #

Explanation / Answer

Q7) Here situation is similar to given in theory above.

We can see that you don't Patient to functionally determine the Hospital Floor.

The new functional dependency looks like Hospital Room Number -> Hospital Floor

So the resultant table can be broken into 2 tables

Table1(Patient,Hospital Room Number)

Table2(Hospital Room Number, Floor)

-------------------------------------------------------------------

Q8) Here again the column Doctor's Pager does not depend on Patient, it will be same for every patient which the Doctor sees. So the condition is same as 2NF, the example given in theory above.

We can break this table into 2 tables

Table1(Doctor, Patient, Hospital Admit Date and Time)

Table2(Doctor, Doctor's Pager)

-------------------------------------------------------------------------

In case of any doubts just comment down.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.