Question

Please show how to do it by hand! Thanks!

PART II: Transform the following ERD to 3-Normalization-Form (3NF). Note: Make fur sure you include the Stalf Supervisor Patent Stalf Type D z Doctor N Nurse Doctor Experience

Answer 1

To convert it into 3 NF, you have to make sure that you understand first normal form and second normal form. A first normal form doesn't have multiple values in same row cell. For example if a same person has multiple telephone numbers then instead of storing those values in same row cell, we would split the column with second column containing second telephone number of a person.

In order for database to be in second normal form there should not be any attributes dependency over other. For third normal form conversion there should not be any transivity between attributes. In the figure below, I've removed the transitivity between staff type and staff details.

Doctor table Doc First Last Quali Address tor nam name ficati Record assigned on 250 1 Chris Harris Pedia 15th Avenue, tricia Georgetown 2John Doe Radio 12 Downing 175 Staff table logist street Staff Staff Staff Last Staff id First name Type name 1 John Doe Doctor Nurse table Nurs First Last Qualifica Address e id name nam tion Record assign 2 Meg Tan Nurse ChrisHarris Doctor Meg Tan Associate 15th Avenue, Georgetown urse Sam Doe Critical 12 Downing street 175 care

Now we have to show relationship between patient and doctor. There can be cases where multiple staff is taking care of single patient.

Record table Record id Diagnose 200 Mild tear detected in right ACL 250 175 presence of diabetes type 1 severe infection Patient table Patient Patient name id(primary key) Record id (foreign key) Treatment description Phil Simon 100 2 doses of morphine 2 Lau Ken 150 Kurt Green 175

Complete tables are given below -

Doctor table Record table Record assigned Doc First Last Quali Address tor nam name ficati Record id Diagnose on 1 Chris Harris Pedia 15th Avenue, tricia Georgetown 250 200 Mild tear detected in right ACL presence of diabetes type 1 250 Staff tablıe Staff Staff Staff Last Staff id First name Type name Patient table 1 John Doe Doctor Patie Patient Record id nt name (foreign key) description id(pri mary key) Treatment 2 Meg Tan Nurse ChrisHarris Doctor Nurse table Phil Simon Lau Ken 150 Kurt Green 100 2 doses of morphine Nurs First Last Qualifica Address e id name nam tion Record assign 2 175 Meg Tan Associate 15th Avenue, urse Georgetown