Normalized Data for the Commonweather Corporation

Normalizing the Data › Normalized Data for the Commonweather Corporation

Normalized Data for the Commonweather Corporation

The data entities and relationships for the Commonweather Corporation are listed in first, second, and third normal forms in the following tables.

Data entities for Commonweather in first normal form

The bold entities and relationships were added to organize the information in first 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 already in second normal form.

Data	Entity/ relationship	Description
OFFICE * OFFICE CODE OFFICE ADDRESS OFFICE SPEED DIAL OFFICE AREA CODE	Entity	Describes offices in which employees work.
CALLS * OFFICE CODE * OFFICE PHONE	Relationship	Relates OFFICE and PHONE.
PHONE * OFFICE PHONE	Entity	Describes office phones; this entity was derived from the OFFICE entity because its attributes appeared as repeating elements.
IS LOCATED * OFFICE CODE * EMP ID	Relationship	Relates EMPLOYEE and OFFICE.
SKILL * SKILL CODE SKILL NAME SKILL DESCRIPTION	Entity	Describes the skills for each employee.
EXPERT IN * SKILL CODE * EMP ID SKILL LEVEL DATE ACQUIRED	Relationship	Relates SKILL and EMPLOYEE.
DEPARTMENT * DEPT ID DEPT NAME	Entity	Describes the departments that employees belong to.
BELONGS TO * DEPT ID * EMP ID	Relationship	Relates DEPARTMENT and EMPLOYEE.
HEADS * DEPT ID * EMP ID	Relationship	Relates DEPARTMENT and EMPLOYEE.
JOB * JOB ID JOB TITLE JOB DESCRIPTION REQUIREMENTS MAX SALARY MIN SALARY NUMBER OF POSITIONS	Entity	Describes the jobs employees perform within the company.
PAYS * JOB ID * SALARY GRADE	Relationship	Relates JOB and SALARY GRADE.
SALARY GRADE * JOB ID * SALARY GRADE GRADE MIN SALARY GRADE MAX SALARY	Entity	Describes the salary grades for each job; this weak entity was derived from JOB because its attributes appeared as repeating elements.
IS POSITIONED * JOB ID * EMP ID SALARY OVERTIME RATE COMMISSION PERCENT BONUS PERCENT START DATE TERMINATION DATE	Relationship	Relates EMPLOYEE and JOB.
PROJECT * PROJECT CODE PROJECT DESCRIPTION EST START DATE ACT START DATE EST END DATE ACT END DATE	Entity	Describes projects that employees work on and lead.
WORKS ON * PROJECT CODE * EMP ID WO START DATE WO END DATE	Relationship	Relates EMPLOYEE and PROJECT.
LEADS * PROJECT CODE * EMP ID	Relationship	Relates EMPLOYEE and PROJECT
REPORTS TO * WRKR EMP ID * SUPR EMP ID WRKR START DATE WRKR END DATE	Relationship	Relates those employees who are supervisors to other employees who are workers.
MANAGES * SUPR EMP ID * WRKR EMP ID SUPR START DATE SUPR END DATE	Relationship	Relates those employees who are workers to other employees who are supervisors.
EMPLOYEE * EMP ID EMP NAME SS NUMBER EMP ADDRESS EMP HOME PHONE DATE OF BIRTH DATE OF HIRE DATE OF TERMINATION STATUS	Entity	Describes company employees.
INSURED BY * EMP ID * LIFE PLAN CODE	Relationship	Relates EMPLOYEE and LIFE INS PLAN.
LIFE INS PLAN * LIFE PLAN CODE INSCO NAME INSCO ADDRESS INSCO PHONE PLAN DESCRIPTION GROUP NUMBER	Entity	Describes a life insurance plan for each employee.
CHOOSES * EMP ID * HEALTH PLAN CODE * COVERAGE TYPE	Relationship	Relates EMPLOYEE and COVERAGE.
COVERAGE * HEALTH PLAN CODE * COVERAGE TYPE COVERAGE DESCRIPTION SELECTION DATE TERMINATION DATE	Entity	Describes health coverage for each employee.
SPECIFIES * HEALTH PLAN CODE * COVERAGE TYPE	Relationship	Relates COVERAGE and HEALTH INS PLAN.
HEALTH INS PLAN * HEALTH PLAN CODE GROUP NUMBER INSCO NAME INSCO ADDRESS INSCO PHONE PLAN DESCRIPTION	Entity	Describes health insurance plans for employees.
PAYS FOR DENT * HEALTH PLAN CODE * COVERAGE TYPE * DENTAL CLAIM ID	Relationship	Relates COVERAGE and DENTAL CLAIM.
DENTAL CLAIM * DENTAL CLAIM ID EMP ID COVERAGE TYPE DATE OF CLAIM PATIENT NAME RELATION TO EMPLOYEE PATIENT SEX PATIENT DATE OF BIRTH PATIENT ADDRESS DENTIST LICENSE NUMBER DENTIST NAME DENTIST ADDRESS	Entity	Describes a dental claim for an employee; in this example, the DENTAL CLAIM entity has an atomic key, DENTAL CLAIM ID.
LISTS A DP * DENTAL CLAIM ID * PROCEDURE ID	Relationship	Relates DENTAL CLAIM and 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 entity was derived from the DENTAL CLAIM entity because its attributes appeared as repeating elements.
PAYS FOR HOSP * HOSPITAL CLAIM ID * HEALTH PLAN CODE * COVERAGE TYPE	Relationship	Relates COVERAGE and HOSPITAL CLAIM.
HOSPITAL CLAIM * HOSPITAL CLAIM ID EMP ID COVERAGE TYPE DATE OF CLAIM PATIENT NAME RELATION TO EMPLOYEE PATIENT SEX PATIENT DATE OF BIRTH PATIENT ADDRESS DIAGNOSIS HOSPITAL NAME HOSPITAL ADDRESS HOSPITAL PHONE HOSPITAL CHARGES ADMIT DATE DISCHARGE DATE	Entity	Describes a hospital claim for an employee.
PAYS FOR PHY * HEALTH PLAN CODE * COVERAGE TYPE * NON-HOSPITAL CLAIM ID	Relationship	Relates COVERAGE and NON-HOSPITAL CLAIM.
NON-HOSPITAL CLAIM * NON-HOSPITAL CLAIM ID EMP ID COVERAGE TYPE DATE OF CLAIM PATIENT NAME RELATION TO EMPLOYEE PATIENT SEX PATIENT DATE OF BIRTH PATIENT ADDRESS DIAGNOSIS PHYSICIAN ID PHYSICIAN NAME PHYSICIAN ADDRESS NUMBER OF NON-HOSP PROCEDURES PHYSICIAN CHARGES	Entity	Describes a non-hospital claim for an employee.
LISTS A NHP * NON-HOSPITAL CLAIM ID * NON-HOSPITAL PROCEDURE ID	Relationship	Relates NON-HOSPITAL CLAIM and NON-HOSPITAL PROCEDURE.
NON-HOSPITAL PROCEDURE * NON-HOSPITAL CLAIM ID * PROCEDURE ID PROCEDURE DESCRIPTION PROCEDURE FEE SERVICE DATE	Entity	Describes the procedures for a particular hospital claim; this weak entity was derived from the NON-HOSPITAL CLAIM entity because its attributes appeared as repeating elements.

Data structure diagram showing Commonweather entities in first normal form

Data structure diagram showing Commonweather entities in first normal form.

Data entities for Commonweather in second normal form

No changes were made to organize the information in second normal form.

Data entities for Commonweather in second normal form.

Data entities for Commonweather in third normal form

The bold entities and relationships were added to organize the information in third normal form.

Data	Entity/ Relationship	Description
OFFICE * OFFICE CODE OFFICE ADDRESS OFFICE SPEED DIAL OFFICE AREA CODE	Entity	Describes the offices employees work in.
CALLS * OFFICE CODE * OFFICE PHONE	Relationship	Relates OFFICE and PHONE.
PHONE * OFFICE PHONE	Entity	Describes office phones; this entity was derived from the OFFICE entity because its attributes appeared as repeating elements.
IS LOCATED * OFFICE CODE * EMP ID	Relationship	Relates EMPLOYEE and OFFICE.
SKILL * SKILL CODE SKILL NAME SKILL DESCRIPTION	Entity	Describes skills for each employee.
EXPERT IN * SKILL CODE * EMP ID SKILL LEVEL DATE ACQUIRED	Relationship	Relates SKILL and EMPLOYEE.
DEPARTMENT * DEPT ID DEPT NAME	Entity	Describes departments in which employees work.
BELONGS TO * DEPT ID * EMP ID	Relationship	Relates DEPARTMENT and EMPLOYEE.
HEADS * DEPT ID * EMP ID	Relationship	Relates DEPARTMENT and EMPLOYEE.
JOB * JOB ID JOB TITLE JOB DESCRIPTION REQUIREMENTS MAX SALARY MIN SALARY NUMBER OF POSITIONS	Entity	Describes the jobs employees perform within the company.
PAYS * JOB ID * SALARY GRADE	Relationship	Relates JOB and SALARY GRADE.
SALARY GRADE * JOB ID * SALARY GRADE GRADE MIN SALARY GRADE MAX SALARY	Entity	Describes salary grades for each job; this entity was derived from the JOB entity because its attributes appeared as repeating elements.
IS POSITIONED * JOB ID * EMP ID SALARY OVERTIME RATE COMMISSION PERCENT BONUS PERCENT START DATE TERMINATION DATE	Relationship	Relates JOB and EMPLOYEE.
PROJECT * PROJECT CODE PROJECT DESCRIPTION EST START DATE ACT START DATE EST END DATE ACT END DATE	Entity	Describes the projects that employees work on.
WORKS ON * PROJECT CODE * EMP ID WO START DATE WO END DATE	Relationship	Relates EMPLOYEE and PROJECT.
LEADS * PROJECT CODE * EMP ID	Relationship	Relates EMPLOYEE and PROJECT.
REPORTS TO * WRKR EMP ID * SUPR EMP ID WRKR START DATE WRKR END DATE	Relationship	Relates those employees who are supervisors to other employees who are workers.
MANAGES * SUPR EMP ID * WRKR EMP ID SUPR START DATE SUPR END DATE	Relationship	Relates those employees who are workers to other employees who are supervisors.
EMPLOYEE * EMP ID EMP NAME SS NUMBER EMP ADDRESS EMP HOME PHONE DATE OF BIRTH DATE OF HIRE DATE OF TERMINATION STATUS	Entity	Describes company employees.
INSURED BY * EMP ID * LIFE PLAN CODE	Relationship	Relates EMPLOYEE and LIFE INS PLAN.
LIFE INS PLAN * LIFE PLAN CODE PLAN DESCRIPTION GROUP NUMBER	Entity	Describes the life insurance plan for each employee.
CHOOSES * EMP ID * HEALTH PLAN CODE * COVERAGE TYPE	Relationship	Relates EMPLOYEE and COVERAGE.
COVERAGE * HEALTH PLAN CODE * COVERAGE TYPE COVERAGE DESCRIPTION	Entity	Describes the health coverage chosen by each employee.
SPECIFIES * HEALTH PLAN CODE * COVERAGE TYPE	Relationship	Relates HEALTH INS PLAN and COVERAGE.
HEALTH INS PLAN * HEALTH PLAN CODE GROUP NUMBER PLAN DESCRIPTION	Entity	Describes the health insurance for each employee.
PROVIDES LIP * LIFE PLAN CODE * INSCO NAME	Relationship	Relates INS CO and LIFE INS PLAN.
PROVIDES HIP * HEALTH PLAN CODE * INSCO NAME	Relationship	Relates INS CO and HEALTH INS PLAN.
INS CO * INSCO NAME INSCO ADDRESS INSCO PHONE	Entity	Describes insurance companies; this entity was derived from the LIFE INS PLAN and HEALTH INS PLAN entities to avoid transitive dependencies; in second normal form, the attributes INSCO ADDRESS and INSCO PHONE were transitively dependent on the non-key attribute INSCO NAME.
PAYS FOR DENT * HEALTH PLAN CODE * COVERAGE TYPE * DENTAL CLAIM ID	Relationship	Relates COVERAGE and DENTAL CLAIM.
DENTAL CLAIM * DENTAL CLAIM ID DATE OF CLAIM	Entity	Describes a dental claim for an employee; in this example, the DENTAL CLAIM entity has an atomic key, DENTAL CLAIM ID.
LISTS A DP * DENTAL CLAIM ID * PROCEDURE ID	Relationship	Relates DENTAL CLAIM and 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 entity was derived from the DENTAL CLAIM entity because its attributes appeared as repeating elements.
DENT CLAIMED FOR * DENTAL CLAIM ID * DENTIST LICENSE NUMBER	Relationship	Relates DENTIST and DENTAL CLAIM.
DENTIST * DENTIST LICENSE NUMBER DENTIST NAME DENTIST ADDRESS DENTIST PHONE	Entity	Describes the dentist who performed 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.
CLAIMS DENT * DENTAL CLAIM ID * PATIENT NAME * EMP ID	Relationship	Relates PATIENT and DENTAL CLAIM.
PAYS FOR HOSP * HOSPITAL CLAIM ID * HEALTH PLAN CODE * COVERAGE TYPE	Relationship	Relates COVERAGE and HOSPITAL CLAIM.
HOSPITAL CLAIM * HOSPITAL CLAIM ID EMP ID COVERAGE TYPE DATE OF CLAIM HOSPITAL CHARGES ADMIT DATE DISCHARGE DATE DIAGNOSIS	Entity	Describes a hospital claim for an employee.
HOSP CLAIMED FOR * HOSPITAL CLAIM ID * HOSPITAL NAME	Relationship	Relates HOSPITAL CLAIM and HOSPITAL.
HOSPITAL * HOSPITAL NAME HOSPITAL ADDRESS HOSPITAL PHONE	Entity	Describes the hospital in which a patient was treated; this entity was derived from the HOSPITAL CLAIM entity to avoid transitive dependencies; in second normal form, the attributes HOSPITAL ADDRESS and HOSPITAL PHONE were transitively dependent on the non-key attribute HOSPITAL NAME of the HOSPITAL CLAIM entity.
CLAIMS HOSP * HOSPITAL CLAIM ID * PATIENT NAME * EMP ID	Relationship	Relates PATIENT and HOSPITAL CLAIM.
PAYS FOR PHY * HEALTH PLAN CODE * COVERAGE TYPE * NON-HOSPITAL CLAIM ID	Relationship	Relates COVERAGE and NON-HOSPITAL CLAIM.
NON-HOSPITAL CLAIM * NON-HOSPITAL CLAIM ID DATE OF CLAIM DIAGNOSIS	Entity	Describes a non-hospital claim for an employee.
LISTS A NHP * NON-HOSPITAL CLAIM ID * PROCEDURE ID	Relationship	Relates NON-HOSPITAL CLAIM and NON-HOSPITAL PROCEDURE.
NON-HOSPITAL PROCEDURE * NON-HOSPITAL CLAIM ID * PROCEDURE ID PROCEDURE DESCRIPTION PROCEDURE FEE SERVICE DATE	Entity	Describes the procedures for a particular non-hospital claim; this entity was derived from the NON-HOSPITAL CLAIM entity because its attributes appeared as repeating elements.
PHYS CLAIMED FOR * NON-HOSPITAL CLAIM ID * PHYSICIAN ID	Relationship	Relates NON-HOSPITAL CLAIM and PHYSICIAN.
PHYSICIAN * PHYSICIAN ID PHYSICIAN NAME PHYSICIAN ADDRESS PHYSICIAN PHONE	Entity	Describes a physician who performed a service for a patient; this entity was derived from the NON-HOSPITAL CLAIM entity to avoid transitive dependencies; in second normal form, the attributes PHYSICIAN NAME, PHYSICIAN ADDRESS, and PHYSICIAN PHONE were transitively dependent on the non-key attribute PHYSICIAN ID of the NON-HOSPITAL CLAIM entity.
CLAIMS NHOSP * NON-HOSPITAL CLAIM ID * PATIENT NAME * EMP ID	Relationship	Relates NON-HOSPITAL CLAIM and PATIENT.
PATIENT * EMP ID * PATIENT NAME RELATION TO EMPLOYEE PATIENT SEX PATIENT DATE OF BIRTH PATIENT ADDRESS	Entity	Describes a patient who makes a claim; this entity was derived from the DENTAL CLAIM, HOSPITAL CLAIM, and NON-HOSPITAL CLAIM entities to avoid transitive dependencies; in second normal form, the attributes RELATION TO EMPLOYEE, PATIENT SEX, PATIENT DATE OF BIRTH, and PATIENT ADDRESS were transitively dependent on the non-key attributes PATIENT NAME and EMP ID of the DENTAL CLAIM entity; PATIENT is a weak entity related to EMPLOYEE.

Data structure diagram showing Commonweather entities in third normal form

Data structure diagram showing Commonweather entities in third normal form.