Question

Determining if a column’s data functionally determines another column’s data can usually be accomplished by asking the question “Can X have only one Y or can it have more than one Y?” An example might be “Can a doctor have only one patient or more than one patient?” If the answer is only one, then doctor functionally determines patient, otherwise, if a doctor can have more than one patient, the doctor does not functionally determine the patient. In other words, if doctor functionally determines patient, then if you know the doctor, you can know the one and only patient he/she has. If doctor does not functionally determine patient (which of course it doesn’t), then you can’t just talk about the doctor and his/her patient. You have to specify the patient. This means that the key would have to include both doctor and patient.

5.) Is the following true? Why or why not? Doctor, Patient -> Prescription

6.) Is the following true? Why or why not? Doctor, Patient, Prescription -> Refills Remaining 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.

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

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 # 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.

9.) 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 -> Department, Department Office Location (Note: a department can have only one office location)

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

Answer 1

ANSWER : In the above mentioned case, one column data will functionally determine the other column data, if the first column data have the other column data as only attribute. But if it has a multiple attribute then the one column data cannot functionally determine the other column data. There will be need for both the attributes to be determined simultaneously. The following mentioned statement is true as if an element possess only single variable then that single variable can be specified by the element. But if the element possess multiple variables then, if will require to specify that which element wants to be known.

Example: in the first case where the doctor have more than one patient, then we have to specify the patient so that we can identify that which prescription belongs to that patient including specifications of the prescription. In the second case, where the prescription have to be refilled, the doctor, patient and the prescription should be specified so that the correct prescription is refilled for the correct patient. This will be helpful for both physicians and the pharmacist.Or else it may result in misidentification of the patient and may cause medication errors.