completing the Normalization step for several of your entities from your E/R dia

Question

completing the Normalization step for several of your entities from your E/R diagram.

You will normalize the following entities to 3rd normal form - show ALL steps - 1st normal form, 2nd normal form and 3rd normal form for each of the following entities:

PATIENT

BED

EMPLOYEE

DEPARTMENT

TREATMENT

TEST

After going through your steps of normalization - add in your Foreign keys based on your cardinalities from your E/R diagram. You will add new attributes to several of your tables.

SSN en ploy ee Is Mahal Inswan isto TEM Medhcrhe Sa DID bmert ome ddiess est ate anol tihme ehnabss ness has ?heer ytm iotectibi

Explanation / Answer

Employee(Eid,Ename,Address,Phone,Type,Salary,Dname,Daddress)

Functional Dependency

1.Eid->{Ename,Address,Phone, Type,Renumeration,Dname,Daddress }

2.Eid->{Type}

3.Dname->Daddress

4.Type->Renumeration

As Phone is a multivalued attribute so Employee table is not in 1NF.

1NF Normalization

1.EmployeePhone(Eid,Phone)

  2. Employee(Eid,Ename,Address,Type,Renumeration,Dname,Daddress)

Table 2 is 2NF but not in 3 NF as it violates transitive dependency

As from functional dependency1 and 3

Eid->Dname

Dname->Daddress

From functional dependency1 and 4

Eid->Type

Type->Renumeration

3NF Normalization

Employee(Eid,Ename,Address, Type,Dname)

EmployeeType(Type,Renumeration)

Department(Dname,Dlocation)

The resulting tables in 3NF are

EmployeePhone(Eid,Phone)

Employee(Eid,Ename,Address, Type,Dname)

EmployeeType(Type,Renumeration)

Department(Dname,Dlocation)

Lab(LabEmployeeId,TechnicianID,EngineerID,TypeofEngineer,AdminID,TypeofAdmin,SkillPRogrammerID,TypeofSkillProgrammer)

FunctionalDependency

1. LabEmployeeId->{TechnicianID,EngineerID,AdminID,SkillProgrammerID}

2.EngineerID->Typeofengineer

3. AdminID->TypeofAdmin

4.SkillProgrammerID->TypeofSkillProgrammer

The table is in 1NF and 2NF but not in 3NF as transitive dependency is not satisfied

3NF normalization

LabEngineer(EngineerID,Typeofengineer)

LabAdmin(AdminID,TypeofAdmin)

LabSkillProgrammer(SkillProgrammerID,TypeofSkillProgrammer)

Lab(LabEmployeeId, TechnicianID, EngineerID, AdminID, SkillProgrammerID)

Foreign keys EngineerID,AdminID and SkillPRogrammerID references LabEngineer(EngineerID), LabAdmin(AdminID), LabSkillProgrammer(SkillProgrammerID) respectively.

So the tables are:

LabEngineer(EngineerID,Typeofengineer)

LabAdmin(AdminID,TypeofAdmin)

LabSkillProgrammer(SkillProgrammerID,TypeofSkillProgrammer)

Lab(LabEmployeeId, TechnicianID, EngineerID, AdminID, SkillProgrammerID)

Patient(SSn,Pname,City,InsuranceNumber,Did,Dname,illness,Dateand Time,Bednumber,Medicine)

The functional dependencies are:

1.SSn->{Pname,City,Insurancenumber}

2.Did->Dname

3.SSn,Illness->Did,DateandTime,Bedno

4.Did,Illness->Medicine

2NF

1.Patient(SSn,Pname,City,InsuranceNumber,Did,Dname)

2Bed(SSn,Illness,Did,DateandTime)

3.Treatment(SSn,Illness,Medicine,Bedno)

As Functional Dependency 2 violates transitive dependency it in not in 3NF

3NF

Doctor(Did,Dname)

Patient(SSn,Pname,City,InsuranceNumber,Did)     Foreign keyDid references Doctor(Did)

Bed(SSn,Illness,Did,DateandTime,Bedno)                   Foreign key SSn references Patient(SSn)

Treatment(Did,Illness,Medicine)            Foreign key Did references Doctor(Did)

So the tables are

        

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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