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 );