Reference no: EM132850325

DBAS 5206 Database Development - Durham College

Mountain View Community Hospital

Case Requirements

Note: There is much to be discovered by you and your group to enable the writing of a requirements document for your business case. The narrative you have been provided only gives an overview of the business problem at hand. You are to define the problem and write the requirements through investigation of the business case. You can achieve this through interviewing the Professor who will act as the client in each case, and by researching existing, similar, business cases. Use the dedicated MS Team channel for your group to complete these inquiries throughout your development process.

Keep in mind, that just because you find a solution to a similar problem, it may not be a good fit or be accepted by the client in your own case.

Mountain View Community Hospital is a not-for-profit, short-term general hospital. It is the only hospital in the city of Mountain View, a rapidly growing city with a population of about 25,000 people in the heart of the Rocky Mountains. MVCH serves the surrounding rural areas as well as the city of Mountain View. At the present time the hospital has 100 beds. However, plans call for expansion to 150 beds in the near future.

Hospital Organization

As with most hospitals, MVCH is divided into two organizational groups. The physicians, headed by Dr. Browne (Chief of staff) are responsible for providing quality medical care to their patients. The group headed by Ms. Baker (hospital administrator) provides the nursing, clinical, and administrative support required by the physicians to service their patients.

Present Information Systems

MVCH leases its server and storage equipment from a major hardware vendor. Plans call for adding a faster processor, additional memory and/or possibly moving to virtual disk storage in the next budget cycle year; however, the extent of these additions has yet to be determined.

Present information systems are batch-oriented and include application programs for Patient Accounting, Billing, Accounts Receivable and Financial Accounting. These application packages were obtained from a software vendor specializing in hospital applications.

Nathan Heller, who was recently appointed head of Information Systems, identified the following deficiencies with the present system:

1. The system does not support the medical staff by recording or reporting the results of laboratory tests and procedures.

Case Requirements

2. Since the entire system is batch-oriented, it does not support immediate system- wide access for processes like patient registration or inquiries regarding billing.
3. The system does not accumulate costs by department or cost centre.
4. The system is inflexible and does not respond well to changing management needs or to the frequent changes in reporting requirements of external health systems agencies.
Management at MVCH had for some time recognized that the present information systems were not responsive to their needs. Mr. Lopez, the Hospital's assistant administrator, who had previous experience with database systems in a large city hospital, had advocated that MVCH investigate the database approach. Mr. Heller was hired as manager of Information Systems partly because of his experience with database systems. Following are the major documents, reports and displays that are required by MVCH Hospital.

Patient Display
A display of the data maintained for each patient is shown in figure 3-1. A clerk in the Admissions Office would enter this data when a patient is first admitted to the hospital. A patient record should be able to be retrieved by supplying either the patient number or patient name. When a patient is later readmitted, the data is up-dated.

Figure 3-1.

PATIENT-NO: 12345
PATIENT-NAME: Baker Mary A.
PATIENT-ADDRESS: 300 Oak St.
CITY-PROV-PC: Mountain View, BC V1V 1V1
TELEPHONE:250 555-5555
SEX:F
HCN:444 333 222
LOCATION:328B
EXTENSION:623
DATE-ADMITTED:10/02/2014
FINANCIAL-STATUS:ESI
DISCHARGE-DATE:

Most of the fields are self-explanatory with a few exceptions. LOCATION is the room and bed location the patient is assigned to. The first three digits indicate the room and the last character indicates the bed in the room. The location is updated with any other information when and if the patient is re-admitted. FINANCIAL-STATUS is the patients second financial source of health coverage after provincial Medicare, if the patient doesn't have a second source of health coverage, the financial status is listed as "Self". DISCHARGE-DATE is the most recent date the patient was discharged. When the patient is re-admitted, this field is reset to null.

A patient's record should be maintained on-line for a period of two years after the last discharge. If the patient is not readmitted within two years, the record is archived and removed from the active database. At the present time there are about 15,000 active patient records in the MVCH information system.

Physician Display

Some 50 physicians refer their patients to MVCH. A possible display showing typical physician data is shown in figure 3-2.
Figure 3-2.

PHYSICIAN-NO: 4321
PHYSICIAN-NAME: M. D. Thayer
TELEPHONE: 250 555-4444
SPECIALTY: Paediatrics

Physician-Patient Report
Each referring physician requires a daily report showing the patients who are currently admitted to the hospital and who were referred by that physician. The format for this report is shown in figure 3-3. At any given time, each physician has an average of two patients under her or his care at the hospital.

Figure 3-3.

PHYSICIAN-NO: 4042                                             DATE: 10/04/2014

PHYSICIAN-NAME: DUNN, A. J.

Daily Revenue Report
The Daily Revenue Report is a listing of all revenue-generating transactions that have been reported on a particular day. The format of this report is shown in figure 3-4.

Figure 3-4. Daily Revenue Report 10/04/2012

The transactions on this report are sorted by patient, as shown in the figure. The only field that requires additional explanation is the COST-CENTRE. This is an organizational subdivision used for accounting purposes.

For each item that is charged to a patient, a clerk would enter the transaction on-line. Following are guidelines to be used in this process.

1. As a clerk enters a transaction, he or she determines the financial source for that item and patient combination from patient record.
2. Each item (identified by an ITEM-CODE) can be associated with one and only one cost centre.
3. The charge for a particular item is the same for all patients.
Patients at MVCH incur an average of about five charge transactions per day per stay at the hospital.
Room Utilization Report
The Room Utilization Report (figure 3-5) is also a daily report that shows the occupancy of the hospital rooms. It is used for scheduling and control purposes.

Figure 3-5. Room Utilization Report 10/04/2014

The field TYPE indicates the type of accommodations for each room location. Possible values would include PR: Private, SP: Semiprivate, IC: Intensive Care, W3: Ward, 3 beds and W4: Ward, 4 beds.

Patient Bill

A statement is printed and mailed to the patient three days after being discharged from the hospital. The format of this statement is shown in figure 3-6. The various charges are grouped by cost centre as shown.

The balance due is the balance before the patient's insurance coverage pays its share. The average length of stay for a patient is three days.

Revenue Analysis

The Revenue Analysis report is a weekly report that shows the total revenues, by cost centre, and the distribution of revenues by method of payment. The format of this report is shown in figure 3-7.

Figure 3-7. Revenue Analysis Date: 10/06/2014

Maintaining an Open Dialog

Nathan and his team are keenly interested in openness and transparency between competing groups so that any final solutions come as close as possible to an accurate representation of the efficient and effective data structure that the hospital hopes to benefit from.

Case Requirements

In groups of three, prepare a formal report containing your group's recommendations for the database design for Mountain View Community Hospital. This report should contain at a minimum:
• Cover page and a table of contents (pagination throughout the report)
• An introductory memo that includes your understanding of the business problem and
how your group approached the development of a solution.
• A mission statement.
• A complete and accurate list of functional and non-functional requirements explicitly based upon your individual group's interaction with the client via the private MS Team channel provided by your Professor. You are not to use input provided to other groups, as it may differ from what you are told.
• A system description including a system boundary of the hospital system to-be, and the resulting major user views. (visual aides for your presentation)
• Your group's final 3NF design in the standard format...
tableName (primaryKeyField(s), otherField(s), foreignKeyField(s), ...)
...with each field separated by a coma.
o Including a list of all (including candidate keys) functional dependencies for all tables.
• An entity relationship diagram that precisely matches your relational schema.
• A data dictionary indicating the domain (name, meaning and description) for each field of each table.
• Enclose the complete report in some form of "nice" report cover for presentation to the client.
Your report should be prepared with the utmost care and close attention paid to report structure, sentence structure, spelling and grammar. This report will be presented to Nathan, Dr. Browne and Ms. Baker as one item to be considered in their decision of which group to go with for the database design.
In addition to the report, you will have to create a development database to help illustrate the system functionality to the hospital team using SQL Server.
• Create the database, all tables and all required relationships to allow for the development of supporting applications.
• All tables should contain a reasonable amount of development data. At a minimum include data for 20 patients, 12 physicians and four or five Cost Centres each with at least two items per centre. All tables related to tables containing data on those objects listed should also have an appropriate amount of data to test your database properly against these requirements.
Create a Windows form application using VB or C# and Crystal Reports to create two different reports against your SQL Server database.
• The "Room Utilization Report" for all rooms.
• The "Physician-Patient Report" for all patients currently in the hospital.

Think about how views (stored procedures) could help you with this program. Also, be sure to consider the program Style Guide when writing this application. Full marks are only achievable on this component for solutions that include all necessary aspects of the Style Guide including full, descriptive documentation.

Attachment:- Database Development.rar