Microsoft Access Sample Database - Hospital Admissions

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.

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.

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.

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.

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.

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 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.

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)

Diagnostics(DiagNo, descr, cost)

Admissions(AdmitNo, PatNo, Diag_Code, Admit_Date, Dischg_Date, CoPay)

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.

Submit the form below to be notified of new articles or other Microsoft Access resources made available...

Microsoft Access Hospital Admissions Student Database