MET CS 669 Database Design and Implementation for Business Document Preview: MET CS 669 Database Design and Implementation for Business Health Term Project: Multi-Specialty Ambulatory Health Center...

BHUAHC Multi Specialty Hospital
1
Assignment:
Student Name:
University:
BHUAHC Multi Specialty Hospital
2
Multi-Specialty Ambulatory Health Center (BUAHC)
The BUAHC Hospital is Multi-Specialty Ambulatory Health Center (BUAHC).The
hospital maintains a moderate sized information systems (IS) department that includes 20 to 25
employees.
EEnnttiittyy RReellaattiioonnsshhiipp DDiiaagg
aammss aanndd TTaa
llee DDeeffiinniittiioonnss
The BUAHC Hospital database is large. Many of the hospital's applications require access to
data about patients. The central entity is the Patient. Patient data is stored in the patient table
described in Table B.1 below. Each patient is identified by a patient number value that is
assigned to the patient by the hospital. Additionally, each patient is assigned an account number
that is referenced by the hospital's billing system.
BHUAHC Multi Specialty Hospital
3
TABLE NAME: patient
Column Name Data type Size Comments
Putrid CHAR 6 Primary Key patient identifier, value assigned
y hospital. This value is also used to access
patient account information
pats’ CHAR 9 Social security number, unique value.
pat_last_name VARCHAR2 50 Last name.
pat_first_name VARCHAR2 50 First name.
pat_middle_name VARCHAR2 50 Middle name.
BHUAHC Multi Specialty Hospital
4
pat_street_address VARCHAR2 50 Street address.
pat_city VARCHAR2 50 City.
pat_state CHAR 2 State a
eviation.
pat_zip CHAR 9 Zip code.
pat_date_of_birth DATE Date of birth.
pat_telephone_number CHAR 10 Telephone number.
ed_number NUMBER 4 Foreign Key link to the bed table.
date_last_updated DATE Date record last updated.
Each time a patient receives Ambulatory treatment or attention from a member of the
nursing or physician medical staff at the hospital, an entry is made into the patient's automated
hospital record. This information is stored to the patient note table in the note_comment column.
This table allows storage of an individual note comment that is up to 4000 characters in size.
The table allows for storage of an unlimited number of comments for a patient. The relationship
etween patient and patient_note is one-to-many
Table :Patient_note
TABLE NAME: patient_note
Column Name Datatype Size Comments.
pat_id CHAR 6 Primary Key, patient identifier. Also serves as a
Foreign Key link to the patient table.
note_date DATE Primary Key, date/time note posted.
note_comment VARCHAR2 4000 Note on patient Ambulatory treatment or condition;
BHUAHC Multi Specialty Hospital
5
physician comment; nurse comment.
date_last_updated DATE Date record last updated.
When patients are admitted to the hospital, they are assigned to a specific bed. Patients
may request specific types of room accommodations. The hospital assigns each bed a unique
identifier known simply as a bed_number. A room may have zero, one or more beds. The
information system has three additional tables, bed, room, and bed_type. The bed_type table is
used as a validation table.
Bed table
TABLE NAME: bed
Column Name Data type Size Comments.
ed_number NUMBER 4 Primary Key; number identifying each bed.
oom_id CHAR 6 Foreign Key link to room table; identifies the room
where the bed is located.
ed_type_id CHAR 2 Foreign Key link to bed_type table; two-digit code
indicating the type of bed.
ed_availability CHAR 1 Code indicating if the bed is available or occupied;
N=not occupied; Y=is occupied.
date_last_updated DATE Date record last updated.
Bed_Type Table
TABLE NAME: bed_type
Column Name Data type Size Comments.
ed_type_id CHAR 2 Primary Key; two-digit code indicating the type of
BHUAHC Multi Specialty Hospital
6
ed.
ed_description VARCHAR2 50 Description of the bed type.
date_last_updated DATE Date record last updated.
Room Table
TABLE NAME: room
Column Name Data type Size Comments.
oom_id CHAR 6 Primary Key; Room identification value that
identifies each hospital room.
oom_description VARCHAR2 25 Description of each room.
date_last_updated DATE Date record last updated.
The hospital categorizes all personnel as staff members. Staff members include
physicians, registered nurses, licensed practicing nurses, various medical technicians,
administrative workers, and other personnel. All staff members are assigned to either a medical
ward or to a hospital department. A staff member is assigned to one and only one
ward/department while a ward/department may have many staff members assigned to it. Thus,
the relationship between the ward_dept and staff tables is one-to-many.
TABLE ward_dept
TABLE NAME: ward_dept
Column Name Data type Size Comments
BHUAHC Multi Specialty Hospital
7
ward_id CHAR 5 Primary Key; coded value to identify ward or
department.
ward_dept_name VARCHAR2 50 Ward or department name.
office_location VARCHAR2 25 Office location for the ward or department.
telephone_number CHAR 10 Office primary telephone number.
date_last_updated DATE Date record last updated
Staff Table
TABLE NAME: staff
Column Name Data type Size Comments
staff_id CHAR 5 Primary Key; value assigned by the hospital.
staff_ssn CHAR 9 Social security number; unique value.
staff_last_name VARCHAR2 50 Last name.
staff_first_name VARCHAR2 50 First name.
staff_middle_name VARCHAR2 50 Middle name.
ward_dept_assigned CHAR 5 Foreign Key link to ward_dept table.
office_location VARCHAR2 10 Office location.
date_hired DATE Date employee staff member was hired.
hospital_title VARCHAR2 50 Hospital title; examples: Radiologist; Registered
Nurse; Medical Technician.
work_phone CHAR 10 Work telephone number.
BHUAHC Multi Specialty Hospital
8
phone_extension CHAR 4 Work telephone number extension.
license_number CHAR 20 Medical licensure number assigned by the state
medical board; NULL value for non-medical staff
members.
salary NUMBER (9,2) Salary for salaried workers; NULL for hourly
workers.
wage_rate NUMBER (5,2)...