Doctors Information Technology(DocIT) is an IT services company supporting medical practices (clients). You will address only insurance claims processing: however, what you develop must be able to generalize and expanded to these other areas of their clients medical practice. Draw an ERD to represent each phase of the development of an insurance claims processing database. Make additional assumption when needed.

Phase I/ Scenario I

The first phase deals with a few core elements. Draw an ERD to represent this initial phase, described by the following:

A patient is assigned a patient ID and you need to keep track of a patients gender, date of birth, name, current address, and list of allergies.

A staff member (doctor, nurse, physicians assistant, etc.) has staff ID, job title, gender, name, address, and list of degrees or qualifications.

A staff may be included in the database even if they do not have to meet with a patient. Similarly, the company wants to record patient information even if they have not been seen by its staff members.

If a patient is related to other patients, the company would like to know who they are related to.

When a patient is scheduled to meet with a staff member(s), a date and time of when the appointment is scheduled or when it occurred as well as a date and time when the appointment was made, and list of reasons for the appointment must be recorded.

Phase II/ Scenario II

Use the ERD developed for Phase I to capture the following information by making some changes, adding more entities and attributes, or adding more relationships.

The company later realizes their clients needs to understand more about the nature of patients relationships. For example, they need to know if their patients are spouses, parents, siblings, etc. The knowledge about their patients will allow them to provide more proper treatments to their patients.

Phase III/ Scenario III

In this phase, you will extend the database design to begin to handle insurance claims. Use the ERD developed for Phase II to capture the following information by making some changes, adding more entities, or adding more relationships.

Each appointment may generate several insurance claims (some patient are self-pay, with no insurance coverage). Each claim is for a specific action taken in the medical practice, such as seeing a staff member, performing a test, administering a specific treatment, etc. Each claim has an ID, a claim code, date of action was done, date the claim was filed, amount claimed, amount paid on the claim, optionally a reason code for not paying full amount, and the date the claim was (partially) paid.

Each patient may be insured under policies with many insurance companies. Each patient policy has a policy number; possibly a group code; and the type of coverage (e.g. medicines, office visit, outpatient procedure).

A medical practice deals with many insurance companies because of the policies for their patients. Each company has an ID, name, mailing address, IP address, and company contact person.

Each claim is filed under exactly one policy. If for some reason a particular action with a patient necessitates more than one insurance policy (e.g. a patient might reach some reimbursement limit under her primary policy, so a second claim must be filed for the same action with the company associated with the secondary policy). With this situation, the medical practices need to designate the type of their patients policy to identify whether they are primary, secondary, or whatever in the sequence of processing claims.