Notes

Outline

Analysis of the Central District Clinic Project 4 will require the creation of a database system to support the operation of a fictitious health clinic in downtown Seattle … by studying how an organization functions it is possible to understand how it can be most effectively supported with IT

The Central District Clinic The Central District Clinic (CDC) is a fictitious walk-in health clinic offering lab tests for drugs, HIV, AIDS, and other diseases It is staffed by volunteer health professionals and a volunteer receptionist … no need to be concerned with the database issues of billing or payroll The lab testing is not performed on-site, but is contracted with Seattle labs and hospitals The goal of our effort is to provide all the database facilities needed to support all aspects of the client processing and testing

Consider The Operation Of The CDC Starting with a client entering … The receptionist gathers contact information from the client and enters on the client record; if a client has visited before the data is verified The receptionist queues the client to see one of the health professionals working that day (After waiting long enough to read last year’s STD Weekly) the client sees the health professional who fills out a chart with a brief medical history and orders the tests to be run The client’s specimens are labeled At the end of the day the specimens for each test are grouped and compared against a manifest listing all of the tests of that type ordered that day and sent out

Operation (Continued) The results are returned and recorded in each client’s record The outcomes are: positive, negative, failed or inconclusive, and exceptional A letter is composed to be sent to the client reporting the result of the test(s) A mailing label is affixed to the envelope, the letter is verified and it is mailed to the client Quarterly, summary statistics are compiled reporting on the clinic’s activities including the number of clients and statistics on the number of positive cases of different diseases The past quarter’s records are archived

Needs Analysis Review the operation of the clinic to determine what tasks need to be supported by the database system … use the verbs as short hand Enter client data Queue client for health professional Fill out chart Order tests Label Specimens Specimens compared to manifest Results recorded in client record Reply letter composed Quarterly statistics compiled Records archived

Enter Client Data Need a table and form for capturing client data

Queue Client With Med Professional The reference to medical professional implies that the volunteers at the clinic should be recorded … what data should be saved? Contact Information Medical license information Home office or hospital Define a table and a form for capturing the data

Further Attention To Queuing What does queuing really mean? Associating a client with the medical professional that will see him or her Such associations are phenomena that can and should be recorded in a database … the phenomenon is a client “seeing” a medical professional, which is called a Visit What is the relevant Visit data? Client, but only the key is needed Medical Professional, key only Date of visit

A Fundamental Idea Notice that the database design contains tables for “things” such as the Clients table and the table for the medical professional’s information These tables represent information about entities The Visits table is different in an important way … It represents information about a relationship between entities, namely that the Client and the MedPro are connected or associated by a visit Pairing the keys of the related entities is the mechanism The relational database model (that’s the set of concepts on which modern databases like Access are built) represents information about entities and relationships

Fill Out Chart During the visit the medical professional will ask the client questions and discover information … where will this information be recorded? Visits -- it represents the event of the client seeing the medical professional, making it a logical location What information will be gathered … History -- what is the relevant past medical data? Symptoms -- what’s wrong now? Notes -- observations about the patient, comments Tests requested

Order Tests Ordering tests can be thought of as the final action of a visit by a client to a medical professional What activities are created by ordering a test Collecting specimens … not an IT task Labeling specimens The label must … Uniquely identify the patient, but respect privacy Must specify the test to be run Must identify the CDC

Compare To Manifest A manifest is a list of things that are supposed to be in a package When the specimens are sent to the lab at the end of the day, there must be a separate list of all the specimens collected for each test … What information is on the list? How is this information created? A manifest for a given day for a given test can be created by selecting all those records that have the proper date and have that test checked … derive a new table

CDC Database Design -- Summary The three primary tables have been formulated: Clients, MedPros and Visits The concept of representing entities and relationships in tables has been introduced

Recall The CDC Operation At the CDC there are various operations, some performed on a per/client cycle, others performed at larger intervals Enter client data Queue client for health professional Fill out chart Order tests Label Specimens Specimens compared to manifest Results recorded in client record Reply letter composed Quarterly statistics compiled Records archived

The Problem of Queuing When the receptionist finishes filling out the Clients form, the client is queued for the medical professional What does it mean in database terms to “queue” someone?  [This isn’t a standard idea, its just something that we need to invent for this project.] Recall that queuing established the relationship between a client and a medical professional … we represent one of these relationships by a row in the Visits table, so ...

Construct A Client’s Form To Queue Once the data is entered on the Client form, the receptionist must choose a medical professional

Clicking on Queue ... Clicking on Queue brings up a miniform with 3 fields in it from the Visits table, not from the Clients table Pairing the client and the medical professional creates the relationship we want to represent, so ...

Getting A Linked Form When working in the form wizard follow these steps to get the linked form for the queuing: Move all of the fields from Clients to the form Move the three fields from Visits: VisitID, MedProID, ClientID When queried for how you want the two table’s data displayed, say linked form

Handling Tests There are two aspects to a test request: The information that a test is requested The outcome of the test The two aspects could be handled in a single field, but will be treated separately For each <test>, there is a <test>R checkbox field for the request For each <test>, there is a <test>O text field for the outcome On the form, only the request checkboxes are shown

Ordering The final act of a visit is for the medical professional to “order” the tests The action of ordering in the database system is to create the client’s tracking number This involves adding a command button control and programming the creation of the tracking number

The Order Button Like any command button control, the programmer places it on the form and programs the event handler

Computing Tracking Number The “preloaded” command event handler has code:

Entering Tracking Number The tracking number field is something the user cannot enter (locked field) and cannot change … What is the tracking number in English? It is the letters “CDC” followed by the computer’s hexadecimal encoding of the number of months since the creation of this program followed by the VisitID

The Results ... Click on Order to create tracking number

Form Commands Other activities of the CDC database system can make use of form command buttons Clear -- it is possible to remove all of the text on a form before it goes into the database … this would be advantageous in case the receptionist goofs up badly or someone just gets up and walks out Print Labels -- it is possible to print out the specimen labels on command from the Visits form

Summary A series of critical operations for the clinic database Queuing … setting up the relationship in Visits Handling tests Ordering tests Developing the tracking number More command buttons

Recall The CDC Design ... As it exists so far, the CDC database system has the following components: Clients Table and Clients Form with Queue mechanism MedPros Table and MedPros Form Visits Table and Visits Form with Order button to set the tracking number What remains is to prepare for the “post visit” processing The main idea in “post visit” processing is to build new tables from the data in the Visits table These derived tables are produced by queries

Queries Queries are commands to the database system describing how to construct a (new) table from existing tables

A Query SELECT Visits.Date, Visits.TrackingNum, Visits.DrugR, Visits.HIVR, Visits.AIDSR, Visits.HepatitisBR, Visits.ExceptR FROM Visits;

Queries Do More Than Save Columns Records with specific properties can be selected

Specifying The Criteria For Testing Beginning with the basic query that selects the proper columns, enter Design view and edit the Criteria

Changes To The Query Are Visible SELECT Visits.Date, Visits.TrackingNum, Visits.DrugR, Visits.HIVR, Visits.AIDSR, Visits.HepatitisBR, Visits.ExceptR FROM Visits WHERE (((Visits.TrackingNum)<>"") AND ((Visits.DrugR)=Yes));

Another Example Create a table with tracking numbers for those requesting a drug test together with their first names … first construct the basic table

The Basic Query Columns have been selected from two base tables

Finishing Query With Selections … Then limit selections to nonempty tracking numbers and Drug test requests

Joining Tables Together

Build A Table Summarizing Tests Today -- a table with tracking numbers for any Client that requested a test Fields come from Visits Tracking number Date The request fields from the tests

Must Be More Selective

At The CDC, Form Today’s Tests Save any test requests for nonempty tracking numbers created today

The SQL For Today

The Table From The Today Query Today becomes the base for building other tables For example, the Drug Test Manifest table is the date and tracking number for all Drug tests

Making The Manifest Once the list of Today’s Drug tests is created, the tracking numbers can be printed for the manifest

Wrapping Up Today There should be a table built from Today for each test and a manifest for each created in exactly the same way Labels can be printed from the same test tables Use the Report Wizard for labels to create the right form