Listing Data in Third Normal Form

Normalizing the Data › How To Normalize Data › Listing Data in Third Normal Form

Listing Data in Third Normal Form

To organize data entities in third normal form, perform the following steps:

Remove transitively dependent attributes:
1. Locate any non-key attributes that are facts about another non-key attribute.
2. Remove these attributes and create a new entity.
3. Create a new relationship that relates the new entity to the original entity.
Update the E-R diagram to reflect your changes.

Dental claim information in third normal form

The entities and relationships that describe dental claim information are listed in third normal form in the following table.

The bold entities and relationships were added to organize the information in third normal form. Since none of the entities listed contain attributes that are dependent on part of the primary key, the information shown in this table is also in second normal form.

Data	Entity/ Relationship	Description
DENTAL CLAIM * DENTAL CLAIM ID EMP ID DATE OF CLAIM	Entity	Describes a dental claim for an employee.
LISTS A DP * DENTAL CLAIM ID * DENTAL PROCEDURE ID	Relationship	Relates DENTAL CLAIM to DENTAL PROCEDURE.
DENTAL PROCEDURE * DENTAL CLAIM ID * PROCEDURE ID PROCEDURE DESCRIPTION PROCEDURE FEE SERVICE DATE	Entity	Describes the procedures for a particular dental claim; this weak entity was derived from the DENTAL CLAIM entity because its attributes appeared as repeating elements.
CLAIMS DENT * EMP ID * PATIENT NAME * DENTAL CLAIM ID	Relationship	Relates PATIENT to DENTAL CLAIM.
PATIENT * EMP ID * PATIENT NAME RELATION TO EMPLOYEE PATIENT DATE OF BIRTH PATIENT ADDRESS	Entity	Describes a patient who makes a claim; this entity was derived from the DENTAL CLAIM entity to avoid transitive dependencies; in second normal form, the attributes RELATION TO EMPLOYEE, PATIENT DATE OF BIRTH, and PATIENT ADDRESS were dependent on the non-key attributes PATIENT NAME and EMP ID of DENTAL CLAIM.
DENT CLAIMED FOR * DENTAL CLAIM ID * DENTIST LICENSE NUMBER	Relationship	Relates DENTIST to DENTAL CLAIM.
DENTIST * DENTIST LICENSE NUMBER DENTIST NAME DENTIST ADDRESS	Entity	Describes the dentist who performs dental work for a patient; this entity was derived from the DENTAL CLAIM entity to avoid transitive dependencies; in second normal form, the attributes DENTIST NAME and DENTIST ADDRESS were transitively dependent on the non-key attributes DENTIST NAME and DENTIST ADDRESS of the DENTAL CLAIM entity.

Dental claim information in third normal form.