Normalization and ERD (Entity Relationship Diagram) -

Normalize the following in 1NF, 2NF and 3NF. Construct the ERD after you normalize the system to the 3rd NF.

Assumptions :

Case 1.A physician can be associated with many hospitals, but must be associated with at least one

Case 2: A physician can have many specialties, or have no specialty.

The physician Master File from a DFD (data flow diagram) consists of the following data elements:

Social Security #

Physician ID

Last_Name

First_Name

Mid_Init

Hospital_Resident_ID

Hospital_Resident_Name

Hospital_Addr_Line1

Hospital_Addr_Line2

Hospital_State

Hospital_City

Hospital_ZIP

Specialty_Type

Specialty_Name

Specialty_College

Date_Graduated

Birth_Date

Year_First_Practiced

Years_Practiced

Annual_Earnings