Identifying Attributes › Identifying the Attributes of Each Entity › Establishing Primary Keys

Establishing Primary Keys

What is a primary key?

The primary key for each entity must be a unique key. From a business standpoint, the primary key should also be the most important element(s) in the entity. The requirements of your organization will determine which unique key attribute will be the primary key.

Suppose that you must select a primary key for the EMPLOYEE entity. Since both the EMP ID and EMP SS NUM attributes can be used to uniquely identify an occurrence of this entity, you need to select one of these keys. The EMP ID attribute is probably used most often for processing; therefore, this element is the best choice for the primary key.

Entities with primary keys

Once you have determined the primary key for an entity, you should mark this key with an asterisk (*), as shown below:

 OFFICE                                  JOB     

     * OFFICE CODE                           * JOB ID
       OFFICE ADDRESS                          JOB TITLE
       OFFICE SPEED DIAL                       JOB DESCRIPTION
       OFFICE AREA CODE                        REQUIREMENTS
       OFFICE PHONE                            MAX SALARY
                                               MIN SALARY
 DEPARTMENT                                    NUMBER OF POSITIONS
                                               NUMBER OPEN
     * DEPT ID                                 SALARY GRADE
       DEPT NAME
       DEPT HEAD ID

 SKILL                                   PROJECT 

     * SKILL CODE                            * PROJECT CODE
       SKILL NAME                              PROJECT LEADER
       SKILL DESCRIPTION                       PROJECT DESCRIPTION
                                               EST START DATE
                                               ACT START DATE

 EMPLOYEE                                   EST END DATE
                                               ACT END DATE
     * EMP ID
       EMP NAME                             HEALTH INS PLAN
       SS NUMBER
       EMP ADDRESS                           * HEALTH PLAN CODE
       EMP HOME PHONE                          INSCO NAME
       DATE OF BIRTH                           INSCO ADDRESS
       DATE OF HIRE                            INSCO PHONE
       DATE OF TERMINATION                     PLAN DESCRIPTION
       STATUS                                  GROUP ID

 LIFE INS PLAN                           COVERAGE

     * LIFE PLAN CODE                        * HEALTH PLAN CODE
       INSCO NAME                            * COVERAGE TYPE
       INSCO ADDRESS                           COVERAGE DESCRIPTION
       INSCO PHONE                             SELECTION DATE
       PLAN DESCRIPTION                        TERMINATION DATE
       GROUP ID

 DENTAL CLAIM                            HOSPITAL CLAIM

     * DENTAL CLAIM ID                       * HOSPITAL CLAIM ID
       EMP ID                                  EMP ID
       COVERAGE TYPE                           COVERAGE TYPE
       DATE OF CLAIM                           DATE OF CLAIM
       PATIENT NAME                            PATIENT NAME
       RELATION TO EMPLOYEE                    RELATION TO EMPLOYEE
       PATIENT SEX                             PATIENT SEX
       PATIENT DATE OF BIRTH                   PATIENT DATE OF BIRTH
       PATIENT ADDRESS                         PATIENT ADDRESS
       NUMBER OF DENTAL PROCEDURES             DIAGNOSIS
       TOTAL CHARGES                           TOTAL CHARGES
       DENTIST LICENSE NUMBER                  HOSPITAL NAME
       DENTIST NAME                            HOSPITAL ADDRESS
       DENTIST ADDRESS                         ADMIT DATE
       PROCEDURE ID                            DISCHARGE DATE
       PROCEDURE DESCRIPTION
       PROCEDURE FEE
       SERVICE DATE

 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
       NUMBER OF PROCEDURES
       TOTAL CHARGES
       DIAGNOSIS
       PHYSICIAN ID
       PHYSICIAN NAME
       PHYSICIAN ADDRESS
       PROCEDURE ID
       PROCEDURE DESCRIPTION
       PROCEDURE FEE
       SERVICE DATE