Normalize your designs for all tables to ensure they are 3NF, and verify that all primary, secondary, and foreign keys are identified properly. Update your ERD to reflect any changes.

Question

What will be an ideal response?

Answer 1

Answers will vary depending on the logical design that students prepared during the systems analysis phase of the SDLC. Students should apply the guidelines described in this chapter and consider the normalization examples described in the chapter.
A sample normalized design for the entities in the ERD required in Task 1 follows:

PATIENT (PATIENT NUMBER, PATIENT FIRST NAME, PATIENT LAST NAME, DATE OF BIRTH, HOUSEHOLD NUMBER, RELATIONSHIP CODE)
APPOINTMENT (APPT NUMBER, PATIENT NUMBER, APPOINTMENT DATE, APPOINTMENT TIME, PROVIDER NUMBER)
APPT SERVICE (APPT SERVICE NUMBER, APPT NUMBER, CPT CODE, STATUS, FEE, EXPLANATION)
PROVIDER (PROVIDER NUMBER, PROVIDER NAME)
EMPLOYER (EMPLOYER NUMBER, EMPLOYER NAME, INSURANCE GROUP NUMBER)
HOUSEHOLD (HOUSEHOLD NUMBER, HOUSEHOLD LAST NAME, HOUSEHOLD FIRST NAME, HOUSEHOLD ADDRESS, HOUSEHOLD CITY, HOUSEHOLD STATE, HOUSEHOLD ZIP CODE, EMPLOYER NUMBER, HOUSEHOLD HOME PHONE, HOUSEHOLD WORK PHONE, PRIOR BALANCE, CHARGES YTD, HOUSEHOLD PAYMENTS YTD, INSURANCE PAYMENTS YTD)
INSURANCE COMPANY (INSURANCE COMPANY NUMBER, INSURANCE COMPANYADDRESS, INSURANCE COMPANY CITY, INSURANCE COMPANY STATE, INSURANCE COMPANY ZIP CODE, INSURANCE COMPANY PHONE)
CPT CODE AND FEE (CPT CODE, CPT DESCRIPTION, STANDARD FEE)
CLAIMS (CLAIM NUMBER, INSURANCE COMPANY NUMBER, APPT SERVICE NUMBER, CLAIM AMOUNT, CLAIM DATE)
MTD CHARGES (CHARGE NUMBER, APPT SERVICE, PATIENT NUMBER, CHARGE DATE, FEE)
MTD PAYMENTS (PAYMENT NUMBER, HOUSEHOLD NUMBER, PAYMENT DATE, PAYMENT AMOUNT, PAYMENT SOURCE, INSURANCE COMPANY NUMBER)

Note: Point out that the APPT SERVICE record might have a primary key based on the unique combination of APPOINTMENT and CPT CODE because only one procedure normally would be performed on a particular patient during a specific appointment. Assigning a primary key of APPT SERVICE NUMBER, however, makes it easier to link this file to other entities. Also mention that assigning primary keys to the Claims, MTD charges, and MTD payments transaction files makes it easier to trace specific transactions and provides an audit trail.