CSIS 325- Project Phase I - Instructions

Using the direct cost data that is accumulated throughout the year, HCO submits an annual cost report to its Medicare/Medicaid Intermediary on December 31. This cost report includes a total of direct costs as well as the indirect costs of the business. Indirect costs include salaries of staff personnel, rent, utilities, and other miscellaneous office supplies. Total direct costs and indirect costs are totaled in the annual cost report, and a per-patient cost reimbursement rate is determined by dividing the total costs (both direct and indirect) by the total number of patient visits. It is this cost-reimbursement rate that is billed to the Medicare/Medicaid home office when a home health care visit is incurred. Note that the direct costs that are accumulated and submitted within the cost report are only those costs associated with Medicare/Medicaid patients. Private pay and insurance patients are excluded from these calculations. Total indirect costs are included in the cost report submission as they cannot be directly traced to any individual patient or patient type.

For patients with insurance, a pre-determined rate per visit is negotiated with each insurance company. The rate-per-visit per insurance company is currently stored in an Excel spreadsheet. This rate per visit is based solely upon the skill level of the care provider. For instance, an RN has a skill level of 10, whereas an LPN has a skill level of 7, and an aide has a skill level of 3. An entry in the spreadsheet indicates that for All-Insurance (a local insurance company), any visit provided by a staff member with a skill level above 5 is reimbursed at a rate of $238 per visit, up to a maximum of 30 visits in a three-month period. Most other insurance companies also provide a rate for a range of skills levels up to a maximum number of visits within a given duration (usually in months). Once this maximum has been exceeded, a patient’s doctor must write a new referral. This referral serves as the basis for a new contract between the patient and HCO.

1. ERD

For this phase, assume you are a member of a team of database and GUI developers/analysts. As a member of the database team, you are required to create an ER Diagram that will facilitate the development of Health Care Options’s company-wide database. It should be created using ER Assistant and include entities, attributes, and relationships.

In addition to creating the tables, you must add at least 3 rows of data to each one. If there are any errors in your ERD, you will likely find them yourself at this point in the process and make any needed adjustments to your design. Remember, order matters when you are creating tables and loading data.

Finally, execute the following commands for each table and print out the results to show that you have successfully performed the CREATE TABLE step of this assignment and have populated the tables correctly.

1. CREATE TABLE statements for all of the tables in your ERD.

2. Screenshots of each table you created after running the exec sp_help and select statements described above.