Refining the Database Design › Refined Data Structure Diagram for Commonweather Corporation

Refined Data Structure Diagram for Commonweather Corporation

Collapse relationships

You can eliminate unnecessary entities by embedding their data in a related entity type. By using a repeating data element instead of maintaining two separate entities, you can save storage space and also reduce CPU needed to access the repeating data as described below:

• The PHONE and SALARY GRADE records are ideal candidates for elimination because:
  • Each entity participates in only one relationship. The PHONE entity is related only to the OFFICE entity; the SALARY GRADE entity is related only to the JOB entity.
  • A maximum number of repetitions is predictable for each entity. A maximum of three phone numbers exists for each office; a maximum of four salary grades exists for each job.

  Thus we can eliminate the PHONE entity and place three PHONE NUMBER data elements in the OFFICE entity. We can also eliminate the SALARY GRADE entity and place four SALARY GRADE data elements in the JOB entity. If you define this database using SQL statements, each of the repeating data elements must have a unique name and, in the case of PHONE NUMBER and SALARY GRADE, allow null values.

Introduce redundancy

The PHYSICIAN, HOSPITAL, PATIENT, DENTIST, and INS CO entities are never processed independently of other entity types. Therefore, they do not need to be maintained independently in the database. In addition, information in the PROJECT and WORKER entities is already carried in the STRUCTURE entity. HEALTH INS PLAN and LIFE INS PLAN contain the same type of information and can be combined into a single entity. Information maintained in these entities can therefore be embedded in other related entities:

• INS CO information can be stored in HEALTH INS PLAN and LIFE INS PLAN.
• PHYSICIAN information can be maintained in NON-HOSPITAL CLAIM.
• HOSPITAL information can be maintained in HOSPITAL CLAIM.
• PATIENT information can be maintained in NON-HOSPITAL CLAIM, HOSPITAL CLAIM, and DENTAL CLAIM.
• DENTIST information can be maintained in DENTAL CLAIM.
• HEALTH INS PLAN and LIFE INS PLAN can be combined into one entity called INSURANCE PLAN.

Update anomalies for these entities will not present a problem for the organization. For example, since Commonweather users do not process DENTIST information by itself, inconsistent information in this entity will not compromise integrity or complicate business processing functions.

Eliminate unnecessary relationships

At this point, the health-related entities can be represented as:

The LIFE-PLAN relationship can be eliminated by treating it as another type of coverage available through an insurance plan. Although this change will require that an occurrence of COVERAGE be associated with each EMPLOYEE, it simplifies the database structure and the application processing.

The HIP-COVERAGE relationship can be eliminated also. Since there will never be more than 15 insurance plans in the database, the validity of an employee's insurance information (the plan code) can be enforced through other means such as a logical record facility path or an SQL CHECK constraint.

Also eliminate the DEPT-HEAD relationship. Integrity enforcement by the DBMS for this one-to-one relationship is not critical to Commonweather Corporation.

Add indexes

Add the following indexes to enforce unique constraints:

• An index on SKILL based on SKILL NAME
• An index on COVERAGE based on COVERAGE TYPE, PLAN CODE, and EMP ID
• An index on EMPOSITION based on JOB ID and EMP ID
• An index on EXPERTISE based on SKILL CODE and EMP ID
• An index on NON-HOSP PROCEDURE based on NON-HOSP CLAIM ID and PROCEDURE NUMBER
• An index on DENTAL PROCEDURE based on DENTAL CLAIM ID and PROCEDURE NUMBER

Note: You will see in the next chapter how some of these indexes can be eliminated.

Add the following indexes to provide generic search capability:

• An index on JOB based on JOB TITLE
• An index on EMPLOYEE based on EMP LAST NAME

Refined data structure diagram