PROJECT 4:
The
CSE100/INFO
100
Fluency
with Information Technology
Autumn 2001
Introduction
This
project is intended to expand your understanding of databases, and to provide
an opportunity to study how the information system of an organization is
created and customized to better serve its needs.
The task is to build the database and information system
for an imaginary
Understanding
what is to be done is the first requirement of any problem solving effort. The following discussion describes how the
clinic operates. From this process the
database requirements will emerge.
Context
The focus of the database support is on processing 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 test
is not performed at the SCCHC, but rather is performed at one or more labs in
These steps in the process can guide 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
SCCHC before, the receptionist simply verifies the information in the existing
record. The receptionist is responsible for scheduling the client with one of
the health professionals.
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 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.
Lab Manifest:
Each day the lab test samples will be grouped together by
test requested. These are compared against a computer generated "lab
manifest" that lists all of the requested tests by tracking number. The
test specimens are sent to the appropriate lab with a paper manifest, and an
electronic version of the manifest is also sent to the lab. An employee will
print the manifest, check that all specimens exactly match, and ship them to
the lab with the manifest.
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 are available, the 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 the database
components.
Needs Analysis
The processes described above imply the basic database
design components needed to support the clinic’s daily operation. These components are numbered in the
following description.
From the Arrival description, it is evident that a Client
table (1) is required. This contains the client’s name, contact information and
birth date. Additionally, a form (2) is required to assist the receptionist in
recording the information. The form should be attractive, be embellished with
the lab’s name and look suitably professional.
Once the form is completed, the receptionist will schedule the client
with a health professional, and so a mechanism must be provided for this
activity.
From the Meeting description, it follows that there must
be a Visit table (3), containing the pertinent information of the visit. This
will include the patient’s ID from the Client table, fields for each test and
the tracking number. The tracking number is not the unique record ID, which is
also required, but 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, the same tracking
number will be used on each. There should be a field to record the outcome of
the test, once it is returned. A form
(4) for entering the data will be created for this table. It will include a check
box for each test to be run. For each
test requested, a label (5) 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. The "chart"
information in the Visit table includes notes fields for "presenting
symptoms", "medications" and "remarks". Additionally,
the first and last name (with certification designation, such as in Bill
Jones, MD or Carol Jones, LPN) of the health professional that sees
the client is included on the Visit data entry form. This implies that there is
a health professional table (6). This table includes a health professional’s ID
number, which is used in the Visit table. In addition to the ID, there is the
professional’s name, certification designation, contact information, social
security number, license number and a notes field. There is a form (7) for
entering data for this table that is attractive and consistent in design with
the other tables.
From the Lab Manifest description, it is necessary to
create (8) a table for each test to be sent to the labs with the specimens.
Each table, known as a Lab Manifest, lists the tracking numbers of all requests
for that test for a given day. Though a paper copy is sent with the specimens,
an electronic copy should also be sent to the labs to be filled out with the
results. So, the Lab Manifest table contains a field for the results of each
test, of which there can be the four outcomes.
The Results Returned description explains that the lab
will return the Lab Manifest table for all tests submitted to it, and this
should be done electronically. The Lab Manifest table will come to the
receptionist who will transfer the file to the database system. The results
fields will have been filled in for each test.
From the Notification description, there must be a letter
(9) 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 (10) have been
generated, the record can be closed and moved from the Visit table to the Visit
Archive table (11). Similarly, all returned Lab Manifest table records that
have been reported should also be moved to the Lab Manifest Archive table
(12). The summary statistics report (13)
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.
Summary
The following components of the SCCHC information clinic
were identified in the Needs Analysis section:
Client table
Client table
entry form
Visit table
Visit table
entry form
Specimen label
Health
Professional table
Health
Professional table entry form
Lab Manifest
table for each test, created daily
(Notification
letter to client containing test results) Not
Part of Project 4
(Mailing label
to client) Not
Part of Project 4
(Visit Archive) Not Part of Project 4
(Manifest
Archive) class=MsoNormal style="TEXT-INDENT: 15pt">(Password Protection of
database) Not
Part of Project 4
Part I: Understanding and
Designing the SCCHC Database
The goals of Part 1 are:
- Understanding
the flow of information through the Community Health Clinic
- Create
the basic tables and forms that will make up the database
- Establish
the relationships among the fields of the database
- Set-up
the client processing through the system
Task 1: Understanding the flow if information
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
SCCHC, describing the flow of information that takes place. The narrative begins, “<Name of client>
walks into the SCCHC, 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 involved in the information flow
are the client, the receptionist, the health professional and the testing lab.
Task 2: Build the Basic Database Structures
The purpose of this task is to create the core of the
SCCHC database in response to the flow of information in Task 1. (If you were truly designing this system
yourself, you would figure out what tables you needed based on the flow of
information.) Define (create) the
structures for three tables: tblClient, tblHealthPro,
and tblVisit. These tables must have at least the fields
(attributes) listed below in the screen captures and have the same
properties. However, it will be up to
you to change the names of each field so that is descriptive of the attribute
it holds, but does not include any spaces or punctuation.
Use the Form Wizard (you will see how this works in lab) to create a Form that
enters data into the tblHealthPro table. Call the form frmClinicHealthStaff.
Create another to enter data into the tblClient
table. Call the form frmClient. Make the form attractive and easy to read
(change the color, font, layout, etc.) for the user who will eventually have to
add data to the system with it. Using
the two forms you have created, add four sample entries to the tblClient and tblHealthPro
tables. Use names of family members for
the Health Professionals and friends for the clients.
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.
Establish that Client records have a one-to-many relationship with Visit
records, and that HealthPro records have a
one-to-many relationship with Visit records as well.
That means that a client could participate in one or more
visits to the clinic and a health professional will also participate in one or
more visits.
Task 4: Set-up Client Flow
The
purpose of this task is to support the receptionist and the health professional
in their meetings with the clients.
Using the form wizards, create a form called frmReception
that:
- Contains
all the information (fields) from the tblClient,
as well as the following three fields from the tblVisit: VisitID, ClientID, HealthProID
- When
the Wizard asks you how you want to view your data, make sure tblClient is the highlighted
table on the left and select “Linked Forms” on the right.
- Name
the second (linked) form frmSeeHealthPro. This
secondary form is linked to Reception, but will add new rows to the Visit
table.
- When
the Wizard has set up the form, go into design view to edit it and make it
more convenient for the receptionist.
Notice that when the receptionist clicks on “See Health Pro” in
form view, he or she can add the data that starts a new row in the Visit
table. This sets up the client for
consultation with the health professional that day.
- Set
up another, separate form called frmConsult
that will be used by the health professional to record their visit
interaction with the client. Information
will be required from all three tables, tblClient,
tblHealthPro and tblVisit.
Decide what information should be displayed from each table. Your choice should limit the information
displayed to just the essential data required for the consultation between
client and health professional. For
example, displaying the client first and last name is essential, but
displaying the health professional’s address is not. (HINT:
None of the tables will have ALL of their information displayed on
the Consult form.) Using the form
wizard set up the Consult form and edit it to
look attractive and easy to read. For example, you will want to display
the health professionals name so that they know this client is their
responsibility, but you don’t need to label the text box with the field
name of “Doctor” or “Health Professional”-they know who they are!
Part I Turn In
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.
Part II: Processing Samples in
the SCCHC Database
The goal of part II of the project is to support the
processing of samples and recording of information. Specifically:
- Implement
a tracking number for the set of tests requested on each visit so lab
testing is anonymous
- Understand
and implement a query to create a manifest list for each type of test
- Using
a report wizard construct a paper list of all tracking numbers requesting
a particular test and print labels for each.
- Create
the query that could eventually be used with a form to assist the
receptionist in recording the test results for each Client.
Before
starting these tasks, 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 (when the clients consultation with the health professional is over).
Task 5: Set Tracking Number
Using the Tool Box in Design View, add a command button to
the Consult 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. Use the wizard process to add the button for
this operation. Place the button, select Form Operation and “Refresh form data”
for your options. When you have finished
adding the button, add the additional code (shown below and also found at the
end of the Appendix) to the click event by going to View>Code on the
Menu bar. 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 a letter
string using the code given below and relocked.
The code is part of the click event handler. Because you are not using the wizard to add
part of the operation, you will enter a lot of code not immediately
understandable. The main part of the
code piece:
TrackingNumber.SetFocus
TrackingNumber.Locked = False
TrackingNumber.Text = "SCCHC" & Hex((12*(Year(Date)-
_
Year("
TrackingNumber.Locked = True
simply adds a hexadecimal number to
letters “SCCHC”. The hexadecimal number
encodes the number of months since the system was built times one million plus
the VisitID.
Task 6: Build Virtual Manifest Tables with Queries
Construct a query for each test listing the tracking
numbers of clients who have had that test ordered today. The query information comes from the Visit
tables and requires the tracking number and the date as well as the request
field (not displayed) when the request field (the checkbox) has the value
yes. Only Visit records for the
current date are to be listed. The query
should be called <test>_Manifest, where <test> is the name of one
of the five tests.
Look at the SQL queries generated for you by the QBE in
Access. For one of the 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 or constraint). This write up should just be a direct
translation of the SQL and is to be put just before your reflection paragraph
for Task 9.
Task 7: Paper and Labels
Using the Report wizard create the manifest list for each
test from the Manifest “tables” of Task 6.
The lists should give the tracking numbers and dates, as well as the
name, address and phone number for the clinic at the top. Using the Label wizard (part of Reports)
create labels for each specimen. The
label should have the tracking number, the date, the type of test requested and
the name, address and phone number of the SCCHC.
Task 8: Recording Results
Reconstruct the queries of Task 6 to provide a convenient
“table” for the receptionist to enter the test results returned by the lab. (It
is optional to provide a form for this table.)
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. Using a query construct a virtual table (a
view) for each test like the Manifest table but with the added field
<test>Outcome. Also, unlike Task
6, the date should not be equal to the current date, but rather be equal to a
date typed in by the receptionist. By
making the criteria for that field a request set in square brackets: [Date
Tests Were Requested] the query will prompt the user for a date with the
request phrase when run.
Task 9: Reflection
Add a paragraph to the document you have created for Task
6 reflecting on how well your system works.
Specifically:
·
Are there things that you would change? Why?
·
Are there things you could add to make the system
more useful? For example, if the medical
professional meets with the client to explain verbally the outcome of the
tests, what database support do you think that activity would require?
Finally, add a paragraph discussing the privacy issues in the design of this type of database:
· How does the design help to protect privacy?
· From a privacy perspective, what things would you change to improve privacy. Be specific.
Part II Turn In
Part II is due at
BEFORE TURNING IN YOUR DATABASE, BE SURE TO “COMPACT AND REPAIR” IT USING THE
INSTRUCTIONS GIVEN TO YOU IN LAB.
Appendix A:
Screen Shots of Sample Table Structures and Form Design
Part I: Sample Design View of HealthPro Table
Part I: Sample Design View of Visit Table
Part I: Sample
Clinic Health Staff Form
Part II:
Sample Consult Form
Part II:
Tracking Number Code to Add to Command button
