Project:
Implement a database including E-R diagram in MS Access based on the following requirements:
FRANK’S
Frank’s
Frank’s
·
· Laboratory: a center within the hospital such as radiology or hematology, which performs tests on patients. Labs have a name and location.
· Patient: a person who is either admitted to the hospital or is registered in an outpatient program. Each patient has a patient number (identifier), name, date of birth, and telephone number. Resident patients have a Date Admitted. Each outpatient is scheduled for zero or more return visits, which have a data and comments.
· Physician: a member of the hospital medical staff who may admit patients to the hospital and who may administer medical treatments. Each physician has a physician ID (identifier), name, specialty, and pager number in addition to the normal information such as address and telephone number.
· Bed: a hospital bed that is assigned to a patient who has been admitted to the hospital. Each bed has a bed number (identifier), a composite attribute consisting of the room number and the bed number.
· Item: any medical or surgical item that may be used in treating a patient. Each item has an item number (identifier), description and unit cost. Items are supplied by Vendors, who also provides supply items for housekeeping and maintenance purposes.
· Employee: any person employed as part of the hospital staff. Each employee has an employee number (identifier), name, and date hired, as well as mailing address, birth date, and telephone number. Employees are made up of three major groups: nurse, staff, and technician. Only nurses have a Certificate, which indicates the qualification (RN, LPN, etc.) Staff has a job classification; technicians have a skill.
· Treatment: any test or procedure performed by a physician on behalf of a patient. Each treatment has a treatment code, treatment name, and a description.
Further analysis has revealed the following additional information.
· Each nurse is assigned to one and only one care center. A care center may have one or more nurses assigned to it. Also for each care center, one of the nurses assigned to it is designated nurse-in-charge for that care center.
· A patient must be admitted to the hospital by exactly one physician. A physician may admit any number of patients, or may not admit any patients.
· Physicians perform any number of treatments on behalf of any number of patients, or may not perform any treatments. A patient may have treatments performed by any number of physicians, but must be treated by at least one physician. For each treatment performed on behalf of a given patient by a particular physician, the hospital records the following information: treatment date, treatment time, and results.
· A patient may optionally consume any number of items. A given item may be consumed by one or more patients, or may not be consumed. For each item consumed by a patient, the hospital records the following: date, time, quantity, and total cost (which can be computed by multiplying quantity times cost).
· Each technician is assigned to one or more laboratories. Each lab must have at least one technician assigned to it and may have any number of technicians assigned.
· A bed may or may not have a resident patient assigned to it at a given time.
· Each bed is assigned to a care center. There may be no beds assigned to a particular care center or a care center may have one or more beds assigned to it.
In addition to the information above, the following business rules have been identified.
· A nurse cannot be appointed nurse-in-charge of a care center unless he or she has a RN certificate.
· Admission date must be earlier than discharge date.
· Each time a patient is admitted to the hospital or registered as an outpatient, they receive a new patient number.
· A patient must be admitted to the hospital or registered as an outpatient before any treatments or tests are performed or any supplies used.
A number of views were also identified. Four significant views are presented below.
1. Patient Billing: Charges incurred by each patient are accumulated during the patient’s stay at the hospital. A sample bill is presented below (Figure 1). A patient, identified by a patient number, is billed for items (identified by an item code)
Figure 1: Patient Bill
Frank’s
123 Any Street
Statement of account for: Patient No. 32549
Patient Name: Baker, Mary Date Admitted: 09/01/99
Patient Address: 300 Oak Street Date Discharged:09/10/99
Item Code Description Charge
200 Room Semi-private 450.00
205 Thermometer 10.00
307 X-Ray 75.00
413 Lab Tests 35.00
Balance Due: 570.00
2. Room Utilization: The room
utilization report (Figure 2), used for bed scheduling and to track room and
bed utilization, is a daily report that shows the status of each room at the
hospital. The following information is
required for this report: location of room (room number – bed number, example
=> 100-1), type of accommodations (PR=private, SP=semi-private), information
about the patient including patient number, patient name, and expected
discharge date for the patient. All beds
must be listed on the report, whether or not they are assigned to a specific
patient.
Date: 10/01/99
LOCATION ACCOM PATIENT No. PATIENT NAME EXP. DISCHARGE DATE
100-1 PR 6213 Rose, David 10-17-99
101-1 PR 1379 Cribbs, John 10/15/99
102-1 SP
102-2 SP 1239 Miller, Ruth 10/16/99
103-1 PR 7040 Ortega, Juan 10/19/99
3. Patient Display: The patient display (Figure 3) is an on-demand display provided when a qualified person enters the patient number at a terminal. Information that appears on this display includes: patient number, patient name, patient address, (street, city, state, zip), date admitted, expected discharge date, location (room and bed), insurance company, and admitting physician.
Figure 3: Patient Display
Patient No:3249
Patient Name:Baker, Mary
Patient Address:
Brooklyn
Date Admitted: 09/12/1999
Exp. Discharged: 09/15/99
Location: 437-2
Extension: 529
Third Party: Blue Cross
Admitting Physician: Dr. Pain Hurt
4. Physician Report: This report is prepared daily for each physician on staff at Frank’s Reconstruction showing all of the patients for the physician. In addition to physician information (physician ID, phone number, name), the report contains the patient number, patient name, location, and the procedure or treatment that was performed for each patient. A physician may not have treated a patient during a given period, or may have treated one or more patients. Also, a patient may not have seen a physician (for example, was just admitted) or may have been seen by one or more physicians.
Figure 4: Physician’s Report
Frank’s
Physician’s Report
Date: mm-dd-yyyy Physician Name: xxxxxx x xxxxxxxxxx
Physician Phone: 123-4567
Patient Patient Name Location Procedure
6083 Brown, Mary 184-2 Tonsillectomy
3157 Miller, John 216-1 Observation
4139 Stone, Carl 107-3 Throat Culture
Do the following queries:
1. List the complete dept. table
2. List the name, job and salary of all employees.
3. What is the name and department number of employee 7369?
4. List all employees who have the job CLERK.
5. Display the name and salary of employees who earn more than 2850.
6. List the name and salary of employees whose salary is not in the range of 1500 to 2850.
7. Display the employee name, job, and start date of employees hired between February 20, 1981 and May 1, 1981. Order list in ascending order by start date. (Note: Enter the date criteria values as ‘20-Feb-YYYY’)
8. Display the employee name and department number of all employees in departments 10 and 20 in alphabetical order by name.
9. List the name and salary of employees who earn more than $1500 and are in department 10 or 30.
10. Display the name and job title of all employees who do not have a manager.
11. Display the name, salary, and commission for all employees who earn commissions. Sort the data in descending order of salary and commission.
12. Display the names of employees who have two Ls in their names and are in department 30 or their manager is 7782.
13. Write a query to display name, department number and department name for all employees.
14. Create a unique listing of all jobs that are in department 30. Include the location of department 30 in the output.
15. Write a query to display the employee name, department name, and location of all employees who earn a commission. (Hint: …Comm is Not Null)
16. Write a
query to display the name, job, department number, and department name for all
employees who work in
17. Write a query to display the number of people with the same job..
18. Determine the number of managers without listing them.
19. List the jobs and average salary of employees with the job.
20. List all employees who have a salary greater than MILLER.
21. Display list of all employees who have the same job as MILLER.
22. List the employee name, job, and salary for all employees whose salary is equal to the minimum salary.
A student may be called on to explain any part of the assignment in front of the class including their E-R diagram project in the screen by the Professor.
Solutions:
HospitalDBInstructions.htm FrankHospitalExplanationE-Rdiagram.htm hospital.txt hospitalinsert.txt hospitalQueries.txt hospital.mdb