New Century Health Clinic

1. Create an initial entity-relationship diagram for the New Century Health Clinic system

Answers will vary depending on the logical design that students prepared during thes ystems analysis phase of the SDLC. Students should understand and apply the guidelines on ERDs and cardinality described in this chapter. The form of the diagram is not important 
— systems analysts use various styles, and each CASE tool has its own way of displaying the information. The main objective is to represent the entities and the relationships among them. A sample diagram follows:



2. Normalize your table designs

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, EMPLOYERNAME, INSURANCEGROUPNUMBER)

 HOUSEHOLD (HOUSEHOLDNUMBER, HOUSEHOLD LASTNAME, HOUSEHOLDFIRSTNAME, HOUSEHOLDADDRESS, HOUSEHOLDCITY, HOUSEHOLDSTATE, HOUSEHOLDZIPCODE, EMPLOYERNUMBER, HOUSEHOLDHOME PHONE, HOUSEHOLDWORK PHONE, PRIOR BALANCE, CHARGESYTD, HOUSEHOLDPAYMENTSYTD, INSURANCEPAYMENTSYTD) 

INSURANCE CO (INSURANCECOMPANYNUMBER, INSURANCECOMPANY ADDRESS, INSURANCECOMPANYCITY, INSURANCECOMPANYSTATE, INSURANCECOMPANYZIPCODE, INSURANCECOMPANYPHONE)

CPT_CODE_FEE (CPTCODE, CPTDESCRIPTION, STANDARDFEE)

CLAIMS (CLAIMNUMBER, INSURANCE COMPANYNUMBER, APPTSERVICENUMBER, CLAIMAMOUNT, CLAIMDATE)

 CHARGES (CHARGENUMBER, APPT_SERVICE, PATIENT_NUMBER, CHARGE _DATE, FEE)

 MTD PAYMENTS (PAYMENT_NUMBER, HOUSEHOLDNUMBER, PAYMENTDATE,PAYMENTAMOUNT, PAYMENTSOURCE, INSURANCE COMPANYNUMBER)