Infolinks

Wednesday 20 February 2013

Project appendix b



Appendix B
------------------------------------------------------------------------------------------------------------ 

 

The Riverbend Hospital Case


The Riverbend Hospital of Alton, Illinois is a regional, acute care facility.  The hospital maintains a moderate sized information systems (IS) department that includes 20 to 25 employees.  The IS department manager, Mr. John Blasé, directly supervises the senior employees in the department.  The department has a senior database administrator, a senior network administrator, two senior operating systems administrators (one for UNIX and one for Windows), and two project managers.  The IS staff also includes several senior programmer/analysts, and approximately 10 junior programmer/analysts. 
You were recently hired as a junior programmer/analyst based on your collegiate training in use of SQL.  Recently, you met with Ms. Juanita Benitez, the senior programmer/analyst in charge of the project team to which you are assigned.  Ms. Benitez has assigned you responsibility for developing SQL queries to support a portion of the firm's database.  She has directed you to study the entity-relationship diagram and database schema for the portion of the database that you will access. 

Entity Relationship Diagrams and Table Definitions

Figure B.1 depicts the entity-relationship diagram.  The Riverbend 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.
Figure B.1
Table B.1
TABLE NAME:  patient
Column Name
Datatype
Size
Comments
Pat_id
CHAR
6
Primary Key patient identifier, value assigned by hospital.  This value is also used to access patient account information
pat_ssn
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.
pat_street_address
VARCHAR2
50
Street address.
pat_city
VARCHAR2
50
City.
pat_state
CHAR
2
State abbreviation.
pat_zip
CHAR
9
Zip code.
pat_date_of_birth
DATE

Date of birth.
pat_telephone_number
CHAR
10
Telephone number.
bed_number
NUMBER
4
Foreign Key link to the bed table.
date_last_updated
DATE

Date record last updated.

Each time a patient receives 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 between patient and patient_note is one-to-many as shown in Figure B.1.  Table B.2 describes the patient_note table.
Table B.2
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 treatment or condition; 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, e.g., private, semi-private, ward.  The hospital assigns each bed a unique identifier known simply as a bed_number.  A room may have zero, one or more beds (some rooms do not contain beds, e.g., a radiology laboratory).  The information system has three additional tables, bed, room, and bed_type.  The bed_type table is used as a validation table.  Tables B.3, B.4, and B.5 describe the columns and structure of the bed, room, and bed_type tables.
Table B.3
TABLE NAME:  bed
Column Name
Datatype
Size
Comments.
bed_number
NUMBER
4
Primary Key; number identifying each bed.
room_id
CHAR
6
Foreign Key link to room table; identifies the room where the bed is located.
bed_type_id
CHAR
2
Foreign Key link to bed_type table; two-digit code indicating the type of bed.
bed_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.
Table B.4
TABLE NAME:  bed_type
Column Name
Datatype
Size
Comments.
bed_type_id
CHAR
2
Primary Key; two-digit code indicating the type of bed.
bed_description
VARCHAR2
50
Description of the bed type.
date_last_updated
DATE

Date record last updated.
Table B.5
TABLE NAME:  room
Column Name
Datatype
Size
Comments.
room_id
CHAR
6
Primary Key; Room identification value that identifies each hospital room.
room_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.  For example, a registered nurse may be assigned to General-Medical Surgical Ward #1 while a physician may be assigned to the Cardiology 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.  Tables B.6 and B.7 describe the staff and ward_dept tables.   
Table B.6
TABLE NAME:  ward_dept
Column Name
Datatype
Size
Comments
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

Table B.7
 TABLE NAME:  staff
Column Name
Datatype
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.
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)
Hourly wage rate for hourly employees; NULL for salaried workers.
date_last_updated
DATE

Date record last updated

There is a relationship between staff members with medical specialties and data stored in the medical_specialty table.  Sometimes a physician or medical technician will have more than one specialty; thus, the relationship between the staff and medical_specialty table is many-to-many and is implemented by the creation of a staff_medspec association table.  The staff_medspec table decomposes the many-to-many relationship into two one-to-many relationships named Has-Specialty and Assigned-Spec as shown in Figure B.1.  The staff_medspec table has a composite primary key consisting of the primary key from staff and the primary key from medical_specialty.  These tables are described in Tables B.8 and B.9.
Table B.8
TABLE NAME:  medical_specialty
Column Name
Datatype
Size
Comments
specialty_code
CHAR
3
Primary Key; medical specialty code.
spec_title
VARCHAR2
50
Title of the specialty.
how_awarded
VARCHAR2
100
How the specialty is awarded, e.g., by completion of medical board examination.
date_last_updated
DATE

Date record last updated.

Table B.9
TABLE NAME:  staff_medspec
Column Name
Datatype
Size
Comments
staff_id
CHAR
3
Primary Key; also serves as Foreign Key link to staff table.  Staff number.
specialty_code
CHAR
8
Primary Key; also serves as Foreign Key link to medical_specialty table.  Medical specialty code.
date_awarded
DATE

Date the specialty was awarded to the staff member.
date_last_updated
DATE

Date record last updated.

The hospital provides patients with various medical services.  Services are categorized for insurance reporting purposes because insurance companies expect hospitals to use standard reporting categories and service codes.  The hospital maintains data about services in a service table.  Additionally, a service_cat (category) table stores validation data about service code categories.  The relationship between a service_cat and service is one-to-many because each service falls into a single category.  These two tables are described in Tables B.10 and B.11.
Table B.10
TABLE NAME:  service
Column Name
Datatype
Size
Comments
service_id
CHAR
5
Primary key; standard medical service identifier.
service_description
VARCHAR2
50
Description of service provided.
service_charge
NUMBER
(9,2)
Standard fee for a service; may be modified by the individual administering the service as required by the medical situation.
service_comments
VARCHAR2
2000
Comments regarding the service including how it should be administered, contraindications, etc.
service_cat_id
CHAR
3
Foreign Key link to service_cat table; Service category identifier.
date_last_updated
DATE

Date record last updated.
Table B.11
TABLE NAME:  service_cat
Column Name
Datatype
Size
Comments
service_cat_id
CHAR
3
Primary key; Service category identifier.
service_cat_desc
VARCHAR2
50
Service category description.
date_last_updated
DATE

Date record last updated

The hospital keeps records of the services provided by a staff member to each patient.  The rendering of a service is termed a treatment and is implemented by three relationships named Gets-Treatment, Treatment-Provided, and Gives-Treatment linking treatment to the patient, service, and staff tables, respectively as shown in Figure B.1.  Each treatment has a unique identifier, the treatment_number.   Each table related to treatment is linked by storing a FOREIGN KEY column (pat_id, service_id, and staff_id) in the treatment table.  The hospital maintains information about treatments including the patient, staff member, and service as well as the date/time of the treatment and the charge.  Additional comments regarding each treatment are also recorded.  These comments may include diagnosis information.  Table B.12 describes the treatment table.
Table B.12
 TABLE NAME:  treatment
Column Name
Datatype
Size
Comments
treatment_number
NUMBER
9
Primary Key; unique for a treatment within a given date.
treatment_date
DATE

Primary Key; required to ensure uniqueness of the Treatment_number column.
pat_id
CHAR
6
Foreign Key link to patient table.
staff_id
CHAR
5
Foreign Key link to staff table.
service_id
CHAR
5
Foreign Key link to service table.
actual_charge
NUMBER
(9,2)
Actual charge for the service provided.
treatment_comments
VARCHAR2
2000
Additional comments regarding the treatment such as diagnosis information.
date_last_updated
DATE

Date record last updated.

Physicians (staff members) prescribe medicines to be administered to patients by other staff members.  The hospital stores medicines in its internal pharmacy department.  Information about medicines that are stocked is stored in the medicine table.  The prescription of medicine is an association table that relates the staff, patient, and medicine tables, and is implemented as the table named prescription with three one-to-many relationships named Orders-Prescription, Gets-Prescription, and Medicine-Provided.  Only one medicine may be prescribed for each prescription.  The medicine and prescription tables are described in Tables B.13 and B.14.

Table B.13
TABLE NAME: medicine
Column Name
Datatype
Size
Comments
medicine_code
CHAR
7
Primary Key; medicine standard code.
med_name_sci
VARCHAR2
50
Scientific name of the medicine.
med_name_common
VARCHAR2
50
Common name of the medicine.
normal_dosage
VARCHAR2
300
Normal dosage of the medicine for a prescription.
medicine_comments
VARCHAR2
500
Comments about the medicine.
quantity_stock
NUMBER
5
Quantity stocked
unit_measure
VARCHAR2
20
Unit of measure, e.g., liters, grams, tablet, capsules.
date_last_updated
DATE

Date record last updated.
Table B.14
TABLE NAME:  prescription
Column Name
Datatype
Size
Comments
pre_number
NUMBER
9
Primary Key; Prescription number system generated.

pre_date

DATE

Prescription Date.

medicine_code

CHAR
7
Foreign Key link to medicine table.
pat_id
CHAR
6
Foreign Key link to patient table.
staff_id
CHAR
5
Foreign Key link to staff table.
dosage_prescribed
VARCHAR2
50
Dosage prescribed, e.g., 50 mg tablet.
dosage_directions
VARCHAR2
500
Directions for administering the medicine, e.g., two times daily.
date_last_updated
DATE

Date record last updated.

Create Riverbend Tables and Indexes 

The CreateRiverbendDatabase.sql program script contains the SQL statements that create the Riverbend database tables and indexes.  We provide the CREATE TABLE commands in this appendix to make it easy for you to understand the table and column names used in the end-of-book exercises provided in Appendix C.  The complete script can be downloaded from the textbook website. 
REM CreateRiverbendDatabase.sql script.
REM Table room
CREATE TABLE room (
    room_id               CHAR(6)  CONSTRAINT pk_room PRIMARY KEY,
    room_description      VARCHAR2(25),
    date_last_updated     DATE );

REM Table bed_type
CREATE TABLE bed_type (
    bed_type_id           CHAR(2)  CONSTRAINT pk_bed_type PRIMARY KEY,
    bed_description       VARCHAR2(50),
    date_last_updated     DATE );

REM Table bed
CREATE TABLE bed (
    bed_number            NUMBER(4)  CONSTRAINT pk_bed PRIMARY KEY,
    room_id               CHAR(6)    CONSTRAINT fk_bed_room
        REFERENCES room(room_id),
    bed_type_id           CHAR(2)    CONSTRAINT nn_bed_type_id NOT NULL
        CONSTRAINT fk_bed_bedtype REFERENCES bed_type(bed_type_id),
    bed_availability      CHAR(1),
    date_last_updated     DATE );

REM Table patient
CREATE TABLE Patient (
    pat_id                CHAR(6)  CONSTRAINT pk_patient PRIMARY KEY,
    pat_ssn               CHAR(9)  CONSTRAINT nn_pat_ssn NOT NULL,
    pat_last_name         VARCHAR2(50)  CONSTRAINT nn_pat_last_name NOT NULL,
    pat_first_name        VARCHAR2(50)  CONSTRAINT nn_pat_first_name NOT NULL,
    pat_middle_name       VARCHAR2(50),
    pat_street_address    VARCHAR2(50),
    pat_city              VARCHAR2(50),
    pat_state             CHAR(2),
    pat_zip               CHAR(9),
    pat_date_of_birth     DATE,
    pat_telephone_number  CHAR(10),
    bed_number            NUMBER(4)  CONSTRAINT fk_pat_bed
        REFERENCES bed(bed_number),
    date_last_updated     DATE );

REM Table patient_note
CREATE TABLE patient_note (
    pat_id                CHAR(6),
    note_date             DATE,
    note_comment          VARCHAR2(4000),
    date_last_updated     DATE,
    CONSTRAINT fk_pat_note_patient FOREIGN KEY (pat_id)
        REFERENCES patient ON DELETE CASCADE,
    CONSTRAINT pk_section PRIMARY KEY (pat_id, note_date) );

REM Table ward_dept
CREATE TABLE ward_dept (
    ward_id               CHAR(5)  CONSTRAINT pk_ward_dept PRIMARY KEY,
    ward_dept_name        VARCHAR2(50)  CONSTRAINT nn_ward_dept_name NOT NULL,
    office_location       VARCHAR2(25)  CONSTRAINT nn_ward_dept_location NOT NULL,
    telephone_number      CHAR(10),
    date_last_updated     DATE );

REM Table staff
CREATE TABLE staff (
    staff_id              CHAR(5)  CONSTRAINT pk_staff PRIMARY KEY,
    staff_ssn             CHAR(9)  CONSTRAINT nn_staff_ssn NOT NULL,
    staff_last_name       VARCHAR2(50)  CONSTRAINT nn_staff_last_name NOT NULL,
    staff_first_name      VARCHAR2(50)  CONSTRAINT nn_staff_first_name NOT NULL,
    staff_middle_name     VARCHAR2(50),
    ward_dept_assigned    CHAR(5)  CONSTRAINT fk_staff_ward_dept
        REFERENCES ward_dept(ward_id),
    office_location       VARCHAR2(50),
    date_hired            DATE DEFAULT NULL,
    hospital_title        VARCHAR2(50)  CONSTRAINT nn_staff_title NOT NULL,
    work_phone            CHAR(10),
    phone_extension       VARCHAR2(4),
    license_number        VARCHAR2(20),
    salary                NUMBER,
    wage_rate             NUMBER(5,2),
    date_last_updated     DATE );

REM Table medical_specialty
CREATE TABLE medical_specialty (
    specialty_code        CHAR(3)  CONSTRAINT pk_medical_specialty PRIMARY KEY,
    spec_title            VARCHAR2(50)  CONSTRAINT nn_medical_spec_title NOT NULL,
    how_awarded           VARCHAR2(100),
    date_last_updated     DATE );

REM Table staff_medspec
CREATE TABLE staff_medspec (
    staff_id              CHAR(5),
    specialty_code        CHAR(3),
    date_awarded          DATE DEFAULT SYSDATE,
    date_last_updated     DATE,
    CONSTRAINT fk_staff_medspec FOREIGN KEY (staff_id) REFERENCES staff,
    CONSTRAINT fk_medspec_med_spec FOREIGN KEY (specialty_code)
        REFERENCES medical_specialty,
    CONSTRAINT pk_staff_medspec PRIMARY KEY (staff_id, specialty_code) );

REM Table service_cat
CREATE TABLE service_cat (
    service_cat_id        CHAR(3)  CONSTRAINT pk_service_cat PRIMARY KEY,
    service_cat_desc      VARCHAR2(50)  CONSTRAINT nn_service_cat_desc NOT NULL,
    date_last_updated     DATE );

REM Table service
CREATE TABLE service (
    service_id            CHAR(5)  CONSTRAINT pk_service PRIMARY KEY,
    service_description   VARCHAR2(50)  CONSTRAINT nn_service_description NOT NULL,
    service_charge        NUMBER(9,2)  CONSTRAINT ck_service_charge
        CHECK (service_charge >= 0),
    service_comments      VARCHAR2(2000),
    service_cat_id        CHAR(3)  CONSTRAINT fk_service_service_cat
        REFERENCES service_cat(service_cat_id),
    date_last_updated     DATE );

REM Table treatment
CREATE TABLE treatment (
    treatment_number      NUMBER(9),
    treatment_date        DATE,
    pat_id                CHAR(6)  CONSTRAINT nn_treatment_pat_id NOT NULL,
    staff_id              CHAR(5)  CONSTRAINT nn_treatment_staff_id NOT NULL,
    service_id            CHAR(5)  CONSTRAINT nn_treatment_service_id NOT NULL,
    actual_charge         NUMBER(9,2)  CONSTRAINT ck_treatment_actual_charge
        CHECK (actual_charge >= 0),
    treatment_comments    VARCHAR2(2000),
    date_last_updated     DATE,
    CONSTRAINT fk_treatment_patient FOREIGN KEY (pat_id) REFERENCES patient,
    CONSTRAINT fk_treatment_staff FOREIGN KEY (staff_id) REFERENCES staff,
    CONSTRAINT fk_treatment_service FOREIGN KEY (service_id) REFERENCES service,
    CONSTRAINT pk_treatment  PRIMARY KEY (treatment_number, treatment_date) );

REM Table Medicine
CREATE TABLE medicine (
    medicine_code         CHAR(7)  CONSTRAINT pk_medicine PRIMARY KEY,
    med_name_sci          VARCHAR2(50)  CONSTRAINT nn_medicine_name_sci NOT NULL,
    med_name_common       VARCHAR2(50)  CONSTRAINT nn_medicine_name_common NOT NULL,
    normal_dosage         VARCHAR2(300) CONSTRAINT nn_medicine_dosage NOT NULL,
    medicine_comments     VARCHAR2(500),
    quantity_stock        NUMBER(12)  CONSTRAINT ck_medicine_qty_stock
        CHECK (quantity_stock >= 0),
    unit_measure          VARCHAR2(20),
    date_last_updated     DATE );

REM Table prescription
CREATE TABLE prescription (
    pre_number            NUMBER(9)  CONSTRAINT pk_prescription PRIMARY KEY,
    pre_date              DATE,
    medicine_code         CHAR(7)  CONSTRAINT nn_prescription_medicine_code NOT NULL,
    pat_id                CHAR(6)  CONSTRAINT nn_prescription_pat_id NOT NULL,
    staff_id              CHAR(5)  CONSTRAINT nn_prescription_staff_id NOT NULL,
    dosage_prescribed    VARCHAR2(50) 
        CONSTRAINT nn_prescription_dosage_presc NOT NULL,
    dosage_directions    VARCHAR2(500),
    date_last_updated     DATE,
    CONSTRAINT fk_prescription_medicine FOREIGN KEY (medicine_code)
        REFERENCES medicine,
    CONSTRAINT fk_prescription_patient FOREIGN KEY (pat_id) REFERENCES patient,
    CONSTRAINT fk_prescription_staff FOREIGN KEY (staff_id) REFERENCES staff );

No comments:

Post a Comment