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