Chapter 9

Capstone Case:   New Century Health Clinicc

Description:

New Century Wellness Group offers a holistic approach to healthcare with an emphasis on preventive medicine as well as traditional medical care. In your role as an IT consultant, you will help New Century develop a new information system.

Background

After completing the user interface, input, and output design for the new information system, you will now focus on the data design of the DBMS that will support the system. Begin by reviewing the DFDs that you prepared in Chapter 5 and the object models that you created in Chapter 6.

Tasks

1. Create an initial ERD for the new system that contains at least eight entities.
2. Analyze each relationship to determine if it is 1:1, 1:M, or M:N.
3. 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.
4. Review the Data Dictionary you created in Chapter 5 and double-check all data dictionaryentries. Make sure that the entries for data stores, records, and data elements are documentedcompletely and correctly. Determine what codes, if any, will be used and be sure they aredocumented in the data dictionary.

Solution :

Tasks

1. Create an initial ERD for the new system that contains at least eight entities.

Answers will vary depending on the logical design that students prepared during the systems 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 ERD follows:

2. Analyze each relationship to determine if it is 1:1, 1:M, or M:N.

The relationships are shown in the ERD above.

3. 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.

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.

4. Review the Data Dictionary you created in Chapter 5 and double-check all data dictionary entries. Make sure that the entries for data stores, records, and data elements are documented completely and correctly. Determine what codes, if any, will be used and be sure they are documented in the data dictionary.

Answers will vary depending on the data dictionary that students create in Chapter 5.  Documentation should be written to define the logic used to develop the codes for future systems analysts that work on New Century’s information system.

You Need a Professional Writer To Work On Your Paper?