Question

Logical Data Modeling -

Question 1: Normalize the logical data model step by step, i.e. 1st NF->2nd NF->3rd NF. (First Normal Form, Second Normal Form and Third Normal Form)

Question 2: Draw the ERD (Entity Relationship Data) after you normalize the system to the 3rd NF

The physician Master File from a DFD (Data Flow Diagram) contains 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

Answer 1

1. Please find below the data dictionary for the fields in Physician Master Data File:

• Social Security # : SSN for the physician
• Physician ID: A unique ID in the datafor physician
• Last_Name: Physician last name
• First_Name: Physician first name
• Mid_Init: Initials for middle name
• Hospital_Resident_ID: Hostpital’s resident unique ID
• Hospital_Resident_Name: Hospital’s Name
• Hospital_Addr_Line1: Hospital Address line 1
• Hospital_Addr_Line2: Hospital Address line1
• Hospital_State: State of the hospital resident
• Hospital_City: City of the hospital resident
• Hospital_ZIP: Zip Code for the hospital resident
• Specialty_Type: Physician speciality type
• Specialty_Name: Speciality Name
• Specialty_College: College of speciality
• Date_Graduated: Physician’s graduated date
• Birth_Date: Physician’s date of birth
• Year_First_Practiced: Year when Physician started practice
• Years_Practiced: Total years of phyisician’s practice
• Annual_Earnings: Physician’s annual earning

Normalization to 1^st NF:

A database relation is called in first NF if:

1. Each cell in the relation contains single value only.
2. Primary key is identified for the relation.

The dataset given can be assumed to have single value in each cell with below assumptions, i.e. 1.1 is satisfied:

Assumption #1: The physician practices for one hospital only.

Assumption #2: The physician has only speciality.

Identifying primary key for 1.2: let’s given the relation name as PHYSICIAN and it can have below primary key:

PHYSICIAN(Physician_ID)

The relation PHYSICAN is in 1NF now.

Normalization to 2^nd NF:

A database relation is called in second NF if:

1. The database relation is in 1NF.
2. All attributes in the relation are fully functional dependent on the Primary key. Where fully functional dependency means that the non-key attributes can be determined from the key attribute (primary key) only.

The database relation given is in 1NF already thus 2.1 is satisfied.

Let’s consider 2.2, there are below functional dependencies in the relation PHYSICIAN:

• Physician_ID-> {Last_Name, First_Name, Mid_Init, Social Security#, Date_Graduated, Birth_Date, Year_First_Practiced, Years_Practiced, Annual_Earning}
• Hospital_Resident_ID-> {Hospital_Resident_Name, Hospital_Addr_Line1, Hospital_Addr_Line2, Hospital_State, Hospital_City, Hospital_ZIP}
• Specialty_Type ->{ Specialty_Name, Specialty_College}

The above functional dependency needs to be removed. Let’s break the relation in below parts:

**Primary keys are Underlined and highlighted

• PHYSICIAN (Physician_ID, Last_Name, First_Name, Mid_Init, Social Security#, Date_Graduated, Birth_Date, Year_First_Practiced, Years_Practiced, Annual_Earning)
• HOSPITAL_RESIDENT(Hospital_Resident_ID,Hospital_Resident_Name, Hospital_Addr_Line1, Hospital_Addr_Line2, Hospital_State, Hospital_City, Hospital_ZIP)
• SPECIALITY (Specialty_Type, Specialty_Name, Specialty_College)
• PHYSICIAN_HOSPITAL(Physician_ID, Hospital_Resident_ID)
• PHYSICIAN_SPEICIALITY(Physician_ID, Specialty_Type)

The primary keys are underlined and highlighted in the relations. The above 5 relations are in 2NF.

Normalization to 3^rd NF:

A database relation is called in third NF if:

1. The database relation is in 2NF.
2. There is no transitive dependency in the relation where transitive dependency is indirect dependency on a non-key attribute on key attribute. E.g. A non-key attribute X is dependent on other non-key attribute Y which is dependent on key attribute Z, the attribute X is called transitive dependent on Z.

The relations identified in Normalization to 2^nd NF are already in required normal form that is 3.1 is already satisfied.

Let’s consider 3.2 for relation HOSPITAL_RESIDENT the relation has below transitive dependency:

Hospital_Resident_ID -> Hospital_State

Hospital_State -> Hospital_Zip

Thus Hospital_Zip is transitively dependect on Hospital_Resident_ID. To remove the same. Break the relation in below two relations:

• HOSPITAL_RESIDENT(Hospital_Resident_ID,Hospital_Resident_Name, Address_ID)
• Address( Address_ID, Hospital_Addr_Line1, Hospital_Addr_Line2, Hospital_State, Hospital_City, Hospital_ZIP)

Now all the relations listed below are in 3NF having Primary/compiste key highlighted and bold.

• PHYSICIAN (Physician_ID, Last_Name, First_Name, Mid_Init, Social Security#, Date_Graduated, Birth_Date, Year_First_Practiced, Years_Practiced, Annual_Earning)
• HOSPITAL_RESIDENT(Hospital_Resident_ID,Hospital_Resident_Name, Address_ID)
• Address( Address_ID, Hospital_Addr_Line1, Hospital_Addr_Line2, Hospital_State, Hospital_City, Hospital_ZIP)
• SPECIALITY (Specialty_Type, Specialty_Name, Specialty_College)
• PHYSICIAN_HOSPITAL(Physician_ID, Hospital_Resident_ID)
• PHYSICIAN_SPEICIALITY(Physician_ID, Specialty_Type)

2. The ERD for the given normalized database system is as below:

The given ERD has below relations:

• One Physician has one Speciality, but Many Physician can have same Speciality (Many to One)
• One Physician practice at one Hospital_Resident, but one Hospital can have many Physician (Many to One).
• One Hospital resides at One Address (One to One).