I need help normalizing this the rest of the way into 2Nf, and 3NF. Original UNF

Question

I need help normalizing this the rest of the way into 2Nf, and 3NF.

Original UNF:Appointment(ApptID, ApptDate, ApptTime, EmpID, EmpName, EmpAddress, EmpHireDate,EmpHPhone, EmpCPhone, EmpJobCode, EmpJobTitle, EmpSalary, PatID, PatName, PatAddress,PatPhone, PatEmail, PatNotificationMethod,(ProcID, ProcName, ProcDescription, ProcBaseFee,ProcBaseTime, ProcActualFee, ProcActualTime, ProcComment,ProcInsurPayment))

1) Converting into 1NF
*In this step you get rid of the Repeating group ProcID, so you make it into a composite Key.
Appointment(ApptID(PK), ProcID(PK,FK) ApptDate, ApptTime, EmpID, EmpFName, EmpMName, EmpLName,EmpStAddress, EmpCity, EmpState, EmpZip, EmpHireDate, EmpHPhone, EmpCPhone, EmpJobCode, EmpJobTitle, EmpSalary, PatID, PatFName, PatMName, PatLName, PatAddress, PatPhone, PatEmail, PatNotificationMethod, (ProcID(PK), ProcName, ProcDescription, ProcBaseFee, ProcBaseTime, ProcActualFee, ProcActualTime, ProcComment, ProcInsurPayment, ApptID(FK))

2) 2NF
I know you have to make sure each non-hey attribute is only dependent on the one key, and you have to move the ones who depend on more than one key into a new relation but I'm
just not sure to go about it.

3)3NF
I....Have no idea. You eliminate more relations to get rid of redundancy and duplications...but how to do that I'm just sort of lost.

*If you could indicate the Primary Keys with (PK), and then Foreign Keys with (FK) That would help me a lot! Chegg doesn't have underline, so that'll be the best way for me to understand!

Explanation / Answer

1NF:
Appointment(ApptID(PK), ProcID(PK,FK) ApptDate, ApptTime, EmpID, EmpFName, EmpMName, EmpLName,EmpStAddress, EmpCity, EmpState, EmpZip, EmpHireDate, EmpHPhone, EmpCPhone, EmpJobCode, EmpJobTitle, EmpSalary, PatID, PatFName, PatMName, PatLName, PatAddress, PatPhone, PatEmail, PatNotificationMethod, (ProcID(PK), ProcName, ProcDescription, ProcBaseFee, ProcBaseTime, ProcActualFee, ProcActualTime, ProcComment, ProcInsurPayment, ApptID(FK))

For 2NF:
It includes,
   - UNF should be in 1NF before going to convert it.
   - Non-prime attribute should be moved and create new relations based on its subset.
  
Here EmpID and PatID are non-prime attributes for ApptID. Since ApptID is PK. So it can be moved to new relations. Like,

(EmpID(PK), EmpFName, EmpMName, EmpLName,EmpStAddress, EmpCity, EmpState, EmpZip, EmpHireDate, EmpHPhone, EmpCPhone, EmpJobCode, EmpJobTitle, EmpSalary)
(PatID(PK), PatFName, PatMName, PatLName, PatAddress, PatPhone, PatEmail, PatNotificationMethod)
eg:
EmpID   EmpJobTitle           EmpSalary
101       WEB developer       38,000
101       Quality analysis   38,000
202       Web designer       38,000
303       Quality analysis   40,000
303       team leader           40,000

cosider above table, EmpID is a PK and EmpSalary is a non-prime attribute, so we can split it to 2NF,
Emplyee_details:
EmpID   EmpSalary
101       38,000
202       38,000
303       40,000

and
Employee_job_details:
EmpID   EmpJobTitle
101       WEB developer
101       Quality analysis
202       Web designer
303       Quality analysis
303       team leader
here one employee may work on more than one jobs. Hence its split over PK.

Result:

(EmpID(PK), EmpFName, EmpMName, EmpLName,EmpStAddress, EmpCity, EmpState, EmpZip, EmpHireDate, EmpHPhone, EmpCPhone, EmpJobCode, EmpJobTitle, EmpSalary)
(PatID(PK), PatFName, PatMName, PatLName, PatAddress, PatPhone, PatEmail, PatNotificationMethod)

For 3NF:
It includes,
   - UNF should be in 2NF before going to convert it.
   - Non-prime attribute should be moved based on its FK.
PK of EmpID and PatID has declared as FK of ApptID. Since ApptID is PK.  

Result:

Appointment(ApptID(PK), ProcID(PK,FK), EmpID(PK,FK), PatID(PK,FK) ApptDate, ApptTime, (EmpID(PK), ApptID(FK), EmpFName, EmpMName, EmpLName,EmpStAddress, EmpCity, EmpState, EmpZip, EmpHireDate, EmpHPhone, EmpCPhone, EmpJobCode, EmpJobTitle, EmpSalary), (PatID(PK), ApptID(FK), PatFName, PatMName, PatLName, PatAddress, PatPhone, PatEmail, PatNotificationMethod), (ProcID(PK), ProcName, ProcDescription, ProcBaseFee, ProcBaseTime, ProcActualFee, ProcActualTime, ProcComment, ProcInsurPayment, ApptID(FK))

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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