PROJECT 3:
The
Database System
CSE100/INFO
100
Fluency
with Information Technology
Spring 2002
Needs Analysis: Identifying Important Entities
Part I:
Understanding and Designing the PSHC Database Structure
Part II:
Finishing the Client Visit and Processing Samples in the PSHC Database
This next
section of this course is an overview of database technology and relational
database design. While learning the
concepts behind database design is helpful, one can only plan on mastering
those concepts by working through an actual project. This project is intended to improve your
understanding of databases and to provide an opportunity to study how the
information system of an organization is created to serve its specific needs.
Your goal is to build the database application for an
imaginary regional health clinic to be called the Puget Sound Health Clinic
(PSHC). The clinic provides a variety of services and referrals, but the
project will be limited to the clinic's lab testing activities. The clinic
performs drug testing as well as testing for a variety of diseases: HIV, AIDS,
Hepatitis B, etc. An important aspect of
the database system is to assure privacy for the clients. A receptionist and
several health professionals staff the clinic.
The database system is intended to support their daily testing
activities.
Understanding
what things (items, people, events, etc) are important to an organization and
need to be tracked is the first step in any database system design. The next
step is to identify how all of those things associate with one other. By exploring how an organization works and
how information flows through it, you can identify those items most important
to the system.
In a real world design process, the database developer or
team spends many hours interviewing the system users and tracking organization
flow in order to identify the many items, events, actions and people that it is
necessary to track. In these interviews
a model of the system will be developed.
This section is intended to replace many of those interviews.
This database is intended to support the processing of
clients who come to the clinic to have a test performed. During their visit they will give personal
information, be seen by a health professional and provide one or more fluid
specimens for testing. The actual tests
are not performed at the PSHC, but rather are performed at one or more labs in
Look at the flow of information through the clinic as a
guide to the design.
Arrival:
When a client arrives, the receptionist interviews him or
her to gather a small amount of personal data that is entered into the client’s
record. If the client has visited the
PSHC before, the receptionist simply verifies the information in the existing
record. The receptionist is then responsible for scheduling the client with one
of the health professionals.
All appointments are for the current day (this is a
walk-in clinic) and are set up on a first come, first serve basis. Any emergency cases would immediately be
diverted to the local emergency room and not dealt with in the clinic.
Meeting
With the Health Professional:
After the client has waited for the health professional to
be free, the health professional interviews the patient to find out a brief
medical history and the type of test(s) sought.
The health professional fills out a form that is a record of the clinic
visit and the discussion. The form has
space for “chart” information as well as check boxes for the tests to be given,
including an “exceptional” situation.
If tests are ordered by the health professional (i.e. one
or more check boxes have been checked) then a specimen (urine, blood, etc.) will
be collected from the client to be sent to the lab. A label must be generated
to attach to the sample. The label must include the pertinent information about
the test and the person requesting it.
To assure privacy a tracking number is used whenever a client must be
identified outside the clinic. The
number is not randomly generated, but neither is it easily identifiable with a
particular client.
Lab
Manifest:
Each day the lab test samples will be gathered together.
An employee compares all the specimens against a computer generated "lab
manifest" listing all of the requested tests by tracking number. After
making sure that all specimens are accounted for, each set of test specimens is
then grouped (Aids, HepB, etc.). The same employee will print each individual
test manifest, check that all specimens exactly match, and ship them to the
appropriate lab with that paper manifest (e.g. Aids). An electronic version of the manifest may
also be sent to the lab.
Results
Returned:
The lab will return the manifest. The results of each test
will be filled in. The outcomes are: positive, negative, failed (must be
repeated), inconclusive, and exceptional. When the results of all tests requested on a
particular visit are available, a letter to the patient is generated. In the
case that the test failed, the client is encouraged to return to provide
another specimen, which will produce a separate visit transaction. Once the letter is generated, the visit is
"closed". To assist the
clinic’s administration and public health officials, summary statistics are
generated each quarter. These include the number of clients visiting, and
summaries of the outcomes, i.e. number of positives, negatives, etc. in each
category.
Analyzing all of the previous description will reveal those
entities (items, people, actions and events) most important to the clinic that
should be tracked.
The actions and processes described above give us an idea of
the items, people, events and their relationships to each other that need to be
tracked. Once identified, they become
components of the system. These and
additional components are necessary to support the clinic’s daily operation and
should be included in the basic database design. They are numbered in the following
description.
From the Arrival description, it is evident that tracking
Client information is very important. A
Client table (1) is required to hold that information. This will contain
In order to help the receptionist to enter information
into this table, a form (2) is required. The form should be attractive,
be embellished with the lab’s name and look suitably professional. Once new client information has been added to
the table through use of the form, the receptionist will schedule the client
with a health professional. The
scheduling of a visit between a doctor and patient will require opening an
additional form that can start a visit record with the appropriate
information. A mechanism must be
provided for doing so.
From the Meeting description, there must be a Health Professional table (3) containing important
information about each volunteer doctor, nurse, etc. This table should hold:
·
Clinic Volunteer ID (specific to
the Health Clinic)
·
First and Last Name
·
Degree (RN, MD, etc)
·
Contact information
·
State health license number
·
Social Security Number
·
Notes (days available to
volunteer, etc)
There is a form (4) for entering data for this
table that is attractive and consistent in design with the other forms.
Also from the Meeting description is the fact that each
event of a client seeing a health professional should be recorded. The table to hold this data is called Visit
(5). The table will include:
·
The ID for the Visit
·
The client’s ID from the Client
table
·
The Health Professional’s ID
from the Health Professional table
·
Tests requested (need a field
for each test)
·
Test outcome data (need a field
for each test outcome)
·
A tracking number
·
Recent Medical History (since
last visit)
·
Current Symptoms
·
Remarks/Notes
The tracking number is not the unique record ID for that
table (that is another attribute).
Instead it is a multi-digit number used to identify the specimens. This
number preserves the anonymity of the client while the specimens and tests are
being processed outside the clinic. One tracking number is associated with each
visit of a client, and so if more than one test is ordered on a single visit,
the same tracking number will be used on each. There should be a field to
record the outcome of each test, once it is returned.
The scheduling of a visit between a doctor and patient by
the receptionist will require use of a form (6) that can start a visit
record with the appropriate client and doctor information. The receptionist
needs to view different pieces of data than the health professional. The Reception desk is only concerned that:
·
The Visit be uniquely identified
from any other that day
·
The proper client identification
number is noted
·
The proper health professional
identification number is noted
For the interactions between the client and the health
professional, another form (7) for entering the additional data will be
created for this table. It should be set up with:
·
The Visit identification number
·
The date of the visit (to verify
the record is not from another time)
·
The client’s name: first and
last
·
The health professional’s name:
first and last
·
Health professional
certification (RN, MD, etc)
·
A way to show if a test should
be ordered
·
The tracking number
·
Notes fields (symptoms, history,
remarks, etc.)
For each test requested, a label (8) must be
printed to be affixed to the specimen. This label includes the clinic’s name,
address and phone number, the date and the tracking number.
From the Lab Manifest description, it is necessary to
create (9) a complete manifest of all tests to be sent to the labs with
the specimens that:
·
Lists the tracking numbers for
all requested tests
·
Shows the checked boxes for each
test as confirmation that it was ordered.
From the complete manifest each individual test manifest
will be created to send to the various labs. Though a paper copy is sent with
the specimens, an electronic copy should also be sent to the labs showing the
tracking numbers requesting that test.
This way the clinic and the lab can each run a check of specimens
against the test manifest.
The Results Returned description explains that the lab
will return the manifest for each test set submitted to it. The test manifests
will come to the receptionist who will enter the results fields each test. There will only be one of 4 possible results
listed (positive, negative, etc). This requires a form for easy data entry by
the receptionist (10). The
information that the receptionist sees, however, should be limited to the
tracking number and the outcome field for entering the results.
From the Notification description, there must be a letter
(11) generated to the client once all of the tests from a visit have been
received. The letter reports the results of each test. If any test’s outcome is
"fail" or "inconclusive", a paragraph is inserted
recommending that the client return to the lab for a retest. Notice that since
a single letter is generated for (possibly) multiple tests, sorting the merged
Lab Manifest table on the tracking number field will result in grouping
together the tests of one visit. Once the letter and a mailing label (12) have
been generated, the record can be closed and moved from the Visit table to the
Visit Archive table (13). Similarly, all returned Lab Manifest table records that
have been reported should also be moved to the Lab Manifest Archive table
(14). The summary statistics report (15)
is generated from the Lab Manifest Archive table. The statistics are (a) the
number of visits, (b) the number of tests, (c) the numbers of tests of each
type and the outcomes. In addition, there should be a summary of the number of
visits by month for each month in the quarter.
Finally, because of privacy practices in the medical field
and possibly state laws, the database must be password protected. Further, it
should contain an "auto close" facility that exits the database when
no activity has occurred for a while. This will protect unintended access when
the health professional leaves the computer.
The following components of the PSHC information clinic
were identified in the Needs Analysis section:
Client table
Client table entry form
Health Professional table
Health Professional table entry
form
Visit table
Reception Visit record entry
form
Health Professional Visit record
completion form
Specimen label
Lab Manifest table for each
test, created daily
Test results entry form
(Notification letter to client
containing test results) Not Part
of Project 3
(Mailing label to client) Not Part of Project 3
(Visit Archive) Not
Part of Project 3
(Manifest Archive) Not Part of Project 3
(Clinic quarterly statistics) Not Part of Project 3
(Password Protection of
database) Not Part of Project 3
The goals of Part 1 are:
The purpose of this task is to visualize the flow of
information and how it should be embodied in a database. Write a narrative description of a particular
client’s visit to the PSHC, describing the flow of information that takes
place. The narrative begins, “<Name
of client> walks into the PSHC, is greeted by the receptionist and sits
down at a chair by the reception desk….”. From here, your narrative should describe the
information flow process: what
information is transferred, from whom and to whom. The people and places involved in the
information flow are the client, the receptionist, the health professional and
the testing lab. You should explicitly
state how information in input to the system, who it goes through and where it
is stored.
Task 2: Build the Basic Database Structures and Create
Simple Data Entry Forms
Now that you have tracked the information flow through the
PSHC system, it’s time to build the structures that have been identified to
hold the information considered important enough to track.
Task 3: Build Relationships
The purpose of this task is to relate the information in
different tables based on a single attribute so that eventually the information
can be associated into an integrated whole.
Task 4: Start the Client Flow Process
The
purpose of this task is to support the receptionist and the health professional
in their meetings with the clients. :
Part I is due at
BEFORE TURNING IN YOUR DATABASE, BE SURE TO “COMPACT AND
REPAIR” IT USING THE INSTRUCTIONS GIVEN TO YOU IN LAB.
The goal of part II of the project is to complete the
first portion of the client visit and support the processing of samples and
recording of information. Specifically:
Task 5: Continue Client Flow
The
purpose of this task is to support the health professional in their meeting
with the clients.
Before
starting this task, let’s look at the tracking number in a little more detail.
The tracking number is a unique number used to label
specimens when they leave the clinic for testing. The main goal is to assure client anonymity,
so the number should not be easily traced to the client. In the tasks for Part II, the tracking number
is the way to refer to the client. The
tracking number is only generated when the tests are ordered (in other words,
when the client consultation with the health professional is over).
Using the Tool Box in Design View, add a command button to
the Consultation Form. The caption of
the button should be “Order Tests” and the Name should be cmdOrder. The code to be added to the cmdOrder_Click event is shown in the Appendix below.
This is what happens in the code: When the Order Tests button is clicked at the
end of the consultation, the tracking number field, which is normally locked to
keep someone from accidentally editing it, is unlocked. The text in the field is set to (assigned) a letter string using the code given below and
relocked. The code is part of the click
event handler.
So, to add in the correct code piece you need to know the
name of the tracking number text box on your form. In the click event for the command button, at
the point where you are shown in the screen shot below, enter statements that
will:
The assigned value simply adds a hexadecimal number to the
letters “PSHC”. The hexadecimal number
encodes the number of months since the system was built times one million plus
the VisitID.
Test the
functionality of your system with the following:
A. Construct a query that will pull all tests requested for the current
date. The query information comes from
the Visit table and requires:
·
The tracking number to be shown
·
The date (only Visit records for
the current date are to be listed)
·
The request field when at
least one test request field (the checkbox) has been checked. This is giving you a hint about establishing
query criteria. There is no programming
involved.
B. Once you have this data retrieved, save the query as qryMainManifest.
C. Use this query as the base for constructing each individual test
manifest. These queries should be called
<test>_Manifest, where <test> is the name of one of the five
tests. Show only the tracking number and
date for the individual test manifest, but use the test request as criteria.
D. Look at the SQL View for the queries generated with the QBE in
Access. For one of the individual test
queries, write a sentence saying in words what the query is doing. Identify each clause of the query and say
what it is referring to (the particular field, table/query or constraint). This write up should just be a description,
line by line, of the SQL and is to be put just before your reflection paragraph
for Task 11.
Generate the queries that are used to reconstruct the
manifests in Task 8. It is assumed that
the results are written on the test’s manifest at the lab, and that the list is
returned at some time in the next week.
You do not have to create the
forms to enter the results, although this would be a logical component to
include in the system.
Add two paragraphs just after the SQL explanation for Task
8. Reflect on how well your system
works. Use these questions as a start to
your reflection:
Add a final paragraph discussing the privacy issues
in the design of this type of database:
C.
How does the design help to
protect privacy?
D. From a privacy perspective, what things would you change to improve privacy on this system. Be specific. Do not simply say “Add password protection to the database”.
The Part II database is due at
BEFORE TURNING IN YOUR DATABASE, BE SURE TO “COMPACT AND REPAIR” IT USING THE
INSTRUCTIONS GIVEN TO YOU IN LAB.
Instructions for how
to establish the field properties are listed in the description column of
the screen shots Part I: Sample Design View of Client Table
Part I: Sample Design View of Health
Professional Table
Part I: Sample Design View of Visit Table
Part II:
Sample Consultation Form
Part II:
Tracking Number Code to Add to Command button