Create a set of normalized relations given the sample data below. Start with a s

Question

Create a set of normalized relations given the sample data below. Start with a single table (could be in NNF) and then proceed to 1NF, 2NF and 3NF. If the first table is in NNF, explain why it is NNF and the steps to take it to 1NF. Do not draw a dependency diagram for NNF; only the relational schema is needed. At each subsequent NF show the relational schema and the dependency diagram for each table. Write out the dependencies. State clearly any (reasonable) assumptions you may make. Business Rules: A patient can only have one surgery per day A patient can have the same surgery multiple times At most one drug is administered for each surgery Each drug can have multiple side effects

Explanation / Answer

1. 1NF:

Relational schema: Patient#, Surgeon#, Surgery Date, Patient Name, Patient Address, Surgeon Name, SurgeryID, Surgery, Drug Admin, Observed sideeffects

This table is in 1NF because, though there are many columns with same data in multiple rows but each row as a whole is unique.

Primary key: (Patient#,SurgeryID,Surgeon#,SurgeryDate)

2. 2NF:

As per the 2NF, there must not be any partial dependency of any column on primary key. It means that for a table that has a primary key, each column in the table must depend upon the entire p[rimary key for its existence. If any column depends only on one part of the concatenated primary key, then the table fails Second normal form.

Normalized Relational schema:

PatientName, PatientAddress depends only Patient#

SurgeonName depends only on Surgeon#

Surgery,DrugAdmin depends on Surgery#

SideEffects depend on the primary Key (Patient#,SurgeryID,Surgeon#,SurgeryDate)

Table 1: Patient

Patient#,PatientName, PatientAddress

Table 2: Surgeon

Surgeon#,Surgeon

Table 3: Surgery

SurgeryID,Surgery,DrugAdmin

Table 4: Details

Patient#,Surgeon#,SurgeryID,SurgeryDate,SideEffects

Dependencies:

Patient#,SurgeryDate->Surgery#

Surgery#->DrugAdmin

Patient#->PatientAddress,PatientName

Surgeon#->Surgeon

SurgeryID->Surgery,DrugAdmin

Patient#,Surgeon#,SurgeryID,SurgeryDate -> SideEffects

3NF: Transitive functional dependency of non-prime attribute on any super key should be removed.

As in current, Sideeffects, depends just on surgery, for a particular surgery there is a specific set of drugs used and for it there will be sideeffects

Thus, relational scehma:

Table 1: Patient

Patient#,PatientName, PatientAddress

Table 2: Surgeon

Surgeon#,Surgeon

Table 3: Surgery

SurgeryID,Surgery,DrugAdmin

Table 4: Details

Patient#,Surgeon#,SurgeryID,SurgeryDate

Table 5: Sideeffects

SurgeryID,DrugAdmin,SideEffects

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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