1. A well-managed database might help the hospital achieve its mission by:
a. Tracking operating costs, and identifying costs that are rising or are above average for similar hospitals.
b. Providing an electronic medical record for each patient, so that physicians can quickly assemble all relevant information.
c. Developing historical records of various treatment programs, to identify which programs and procedures are most effective (and which are not).
d. Providing data and information to track important measures such as average length of patient stay, cost per day for a patient, and so on.
2. Database technology can provide several mechanisms to ensure that data are protected and that the hospital conforms to HIPAA standards. The following table is taken from the standards and highlights some areas that database technology can help with regarding compliance.
Standards Implementation Specifications Security and Training Login Monitoring
Password Management Contingency Plan Data Backup Plan Device and Media Controls Data Backup and Storage Access Control Encryption Transmission Security Encryption
3. Some of the costs and risks of using databases that the hospital must manage carefully are:
a. New, specialized personnel. This will include database designers, database administrators, and other skills that are in short supply in a tight job market.
b. Installation and management cost and complexity. This may include installing a new DBMS as well as hardware and operating system upgrades.
c. Conversion costs. These are primarily the costs of converting older applications to a database environment. Alternatively, the hospital may choose to purchase new systems from a vendor.
Need for explicit backup and recovery. These are the system costs associated with backup and recovery procedures.
e. Organizational conflict. The hospital must anticipate the costs of data administration and other activities related to developing a consensus on data definitions, ownership, and maintenance.
4. Data quality is of utmost importance in a hospital environment. This is particularly true when considering applications such as patient care administration and clinical services, since poor data quality could directly affect patient care. Less critical, but still important, is data quality for financial management and administrative services.
5. There are several complex data types that must be managed by a hospital:
a. Medical scans (MRI, X-ray, etc.)
b. Clinical test data (blood test, etc.)
c. Documents (admission forms, physician referrals, etc.)
d. Unstructured text (doctors’ orders, doctors’ notes, etc.)
e. Test results (electrocardiograms, ultrasound, etc.)
6. The PATIENT and PATIENT CHARGES tables are linked by the Patient_Number attribute, which is common to both tables. Thus, for example, patient no. 4238 has three separate charges at the present time.
7. Mountain View Community Hospital could use personal databases that reside on PDAs for physicians to have access to databases that contain information on prescribed treatment for various diseases. Also, physicians could enter notes into a database on the PDA which could then be later uploaded to a patient information system.
Workgroup databases could be utilized to contain information specific to one ward, for example an extensive database of cancer treatments. Also, workgroup databases might be utilized to collect information for epidemiological studies being conducted by one group of researchers.
Departmental databases could be utilized to store records that are only pertinent to that department, but might not become part of the patient’s full medical record. For example, radiology might store all images from a patient x-ray or MRI, while only the ones used for diagnosis would be contained in the medical record. Also, radiology might keep a database of images to help radiologists in diagnosis.
An enterprise database could be created to store all information about a patient, such as the medical record, billing, etc.
Web-enabled databases could be utilized as described in Question 8.
8. Mountain View Community Hospital could use Web-based applications in several ways. Perhaps one of the greatest areas that it could use this technology would be to establish an Intranet for access to databases by internal hospital personnel. Also, the hospital could investigate an extranet application to do third-party billing directly with insurance companies. Another application would be to enable access to medical databases and prescription drug databases that are available online. Web-based applications have several benefits, including streamlining such processes as third-party billing. One major risk associated with any Web-based application is security. Even having data available via an Intranet could still pose a security risk to the hospital if network security did not prevent unauthorized access from the outside.
1. Relationship between PATIENT and PATIENT CHARGES:
2. Metadata chart:
Data Item Value
Name Type Length Min Max Description Patient_Name Character 30 __ __ Patient’s name Patient_Number Integer 4 0000 9999 Patient ID Patient_Address Character 40 __ __ Street/City/State/Zip Item_Description Character 20 __ __ Item name Item_Code Integer 3 00 999 Item ID Amount Decimal 7 0.0 99,999.99 Amount charged
Note to instructor: Student answers may vary as there are many other attributes that could be listed in this solution.
3. Patient Bill view:
4. E-R diagram (simplified) for hospital:
Name Type Length Min Max Description Source Employee_ID Integer 4 1 9999 Employee ID Human
Resources Date_Hired Date 8 Date of Hire Human Resources Employee_Type Text 15 Type of Employee Human Resources
b. 1:M Physician to Patient
M:M Patient to Charge
The simplified ER diagram completed for exercise 4 suggests these relationships.
c. The Patient table will contain information about a patient, such as Patient_ID, Patient_LastName, Patient_FirstName, Patient_Middle_Initial. The Patient table is likely to have stored the Physician_ID value for the physician who admitted the patient. In order for this data to be useful for human interpretation, one would need to be able to link the Patient table with other tables, such as the Physician table, Charge table, etc.
d. A sample report might look like the following:
Mountain View Community Hospital
Daily Inpatient Roster
Date: March 1, 2006
Patient Name Physician Admit Date Care Center Room
Terri Smith Larry Moxley Feb. 24, 2006 Cancer Care Center 100
Chris Bailey Larry Moxley Mar 1, 2006 General Care Center 102
Total Patients: 2
2. SELECT *
WHERE Patient_LastName > ‘A’;
P1. Some examples of entities are:
PRESCRIPTION – this entity would track prescription drugs that are stocked and
prescribed to patients.
INSURANCE_CARRIER – this entity would contain information about insurance
carriers, including address information.
ITEM – this entity would contain information on items, such as specialized
equipment, which might be consumed by patients.
LABORATORY – this entity would contain information on laboratory tests.
P2. Some other views would be:
Treatment View – what patients received which treatments.
Physician View – what patients were treated and/or admitted by which physicians.
Laboratory View – which patients were tested.
Pharmacy View – what drugs were prescribed to which patients.
Nurse_Supervisor View – how are patients assigned to nurses.
Care Center View – which patients are in a particular care center. What staff have been
assigned to the care center.
Lab Supervisor View – which technicians performed certain tests.
P3. Preliminary Enterprise Data Model for Mountain View Community Hospital; this two entity segment is presented as an example of the kind of diagram a student might develop. Student answers may vary; what is important is that the entities are appropriate for the case and that the preliminary cardinalities are drawn correctly for the situation.