Health Care Patient Tracking System
Overview
The Health Care Reform Act has forced Hack-N-Cough Health Care Inc. to redesign their
patient tracking system. They have gathered requirements and wish to begin design of
the new system. The new tracking system will hold patient information and records in
accordance with the HCRA and give patients and administrators timely information
regarding care given to beneficiaries.
Patient Information
Patient information will be held in a central table for easy data retrieval. Each patient
will be uniquely identified with a member number. All patient records will be the usual
location and contact information such as address and telephone numbers.
Company Coverage
Companies contract with Hack-N-Cough to offer coverage to their employees. The
health care plans offered by companies through Hack-N-Cough are captured in the
database. Each company under contract is given a group number to uniquely identify it
within the system. Companies may add employees to their plan or remove them as
necessary. All employee records must be effective dated to ensure they receive coverage
during the appropriate time. Employee family members are eligible for coverage as well
and must be captured. Each employee and family member has a deductible, which can be
different within a family.
Doctor Visits
Each visit to the doctor or hospital will trigger a claim to Hack-N-Cough. The claims
will be input into the system and information captured for the doctor or medical facility
visited. Each visit should be uniquely identified by a claim number that ties back to the
patient.
Treatment
Since each visit to the doctor could produce more than one treatment, each treatment will
be captured as it relates to a doctor visit. The system must store the medication or
procedure, along with a begin date and end date. Treatments have been standardized and
are held in a base table. Each treatment will have an associated cost which Hack-N-
Cough will pay, along with the amount which was actually billed by the treatment
provider. The difference must be paid by the patient until the deductible is reached, after
which no patient payment is necessary.
Instructions
Database Design
1) Draw an Entity Relationship Diagram (ERD) – using the above problem
statement, draw an ERD that depicts the entities, relationships between
entities and cardinality of the relationships. You may draw this by hand
(neatly) or use software suitable for this task. Explain any notations you may
use in a legend for clarity.
2) Convert the ERD to a relational schema – Show tables, columns, primary
and foreign keys.
Implement the Database Design
1) Create SQL scripts – SQL scripts will be created that implement the schema
defined above.
2) Create SQL reports #1– Create SQL queries that answer the following
questions that patients typically ask.
a. How many claims have I had over the past 4 quarters?
b. How many claims were denied?
c. How much of my deductible do I have remaining to pay?
3) Create SQL reports #2 – create SQL queries that answer the following
questions that corporate accounting commonly have.
a. How much does Hack-N-Cough spend on treatments versus how
much providers bill?
b. What is the total paid out by quarter for all treatments?
Turn in the completed design, schema creation scripts and queries for full credit.
Teams will be judged based upon (1) Design - 40%, (2) DDL - 30%, and (3) Queries
– 30%.