This article presents the Hospital Admissions sample database
used in some of our classes. It is purposefully
very simple in the spirit of Chris Date's famous Supplier_Parts
sample database.
You can download the MS Access database (and others) at
box.com.
Background
The Hospital Admissions database is used to admit a patient
into a hospital and record the diagnosis and copayment. Both are
recorded in the admissions record. As might be expected for such a
simple database, the design is fully normalized.
Business Rules
Diagnostic codes always begin with an alpha
character.
Each admissions record must have an admission date.
The discharge date is recorded when the patients is
discharged and must be greater than or equal to the
admission date.
Database Description
Entities
There are three tables: patients, admissions and diagnostics.
The patients table holds the patient demographics.
Diagnostics contains the information
about a diagnosis and the cost. The date that a patient is
admitted and the copayments are
recorded in the admissions table.
Constraints
In the patients table lName, birthdate, and zip are required.
The diagnostics table has an input mask to ensure the first
character is an alpha.
There is also a validation rule to ensure the admissions
record admit date is less than or equal to the discharge
date. This rule has to be created via the table's property
sheet as it involves more than one column. Technically, this
is what is called a table constraint. The others are column
constraints.
The database also has referential integrity constraints
discussed below.
Relationships
The admissions table forms an intersection table between the
patients and diagnoses. Patients may be admitted more than
once with the same diagnosis and a given diagnostic code may
have been applied to multiple patients. This is a many-to-many relationship
which is represented in a physical design by an intersection
table. In this case the admissions record, which we need
anyway to record the admission and discharge dates and
copayment.
There are one-to-many relationships from patients and
diagnoses to admissions. (Many-to-many relationships always
break down into two one-to-many relationships like this).
Access can't represent the following directly in the diagram but the many
side of each relationship is optional and the one side is
mandatory. An admissions record must refer to a valid
patient
and diagnostic code. Note that the primary key and foreign
key for the diagnostic code have different names. This is
OK. Access will still enforce the relationship. Further the
various Microsoft Access wizards will now know how to relate the two tables
even though there is no column with a common name in each
table.
Keys
The primary keys in the patients and
diagnostics tables are PatNo and DiagNo, respectively.
DiagNo is a diagnostic code like A1, C22, etc. We plan to
eventually change these to actual IC9 codes or perhaps
ICD10.
The primary key in the admission table is
AdmitNo, a surrogate(autonumber). There are foreign keys to
the patients table and the diagnostics table. The foreign
key in the admissions table is Diag_Code, a different name
than than the primary key in the diagnostics table. But it
does have the same data type and length.
Referential Integrity
Referential integrity is enforced from the admissions table
to both patients and diagnostics. This means that when an
admissions record is added to the database, Access will
check to ensure that a valid patient number and diagnostic
code exists. You can tell by
the little annotations or symbols of 1 and infinity that
referential integrity is being enforced.
We also set the option to cascade updates. This means that if
the primary key in a parent table (the table on the one
side) is changed, Access will automatically update the
foreign keys in the child table(s) to maintain the integrity
of the reference. In this case if PatNo or DiagNo are
changed in the patients or diagnostics tables the admissions
record
foreign keys will be updated automatically. If you don't
cascade updates you are doing what is termed update
restrict. There are other referential integrity update
options such as set the foreign key to null or a default but
Access does not offer those.
You can also cascade deletes. This means that if the parent
record is deleted the corresponding child records are also
deleted. If deletes are not cascaded then the delete of the
parent record will generate an error message. We did not set cascade
delete in this database. This is technically called delete
restrict.
Text Format Schema
The schema can also be represented in text with a few
conventions: bold = required field,
underline = primary key, italic
= foreign key.
Patients(PatNo, fName,
lName, BirthDate, address, city, state,
zip)
Hopefully, you have found this a useful explanation of the
Hospital Admission student database. You may be working with this
database in our classes so a little background could prove
helpful. Perhaps others can profit from this article as
well.
Dan D'Urso
Laguna Niguel, CA 92677
December 2017
articles at dhdurso dot org
Submit the form below to be notified of new articles or other
Microsoft Access resources made available...