Introduction to Personal Databases

1
Introduction to Personal Databases
Biomedical Informatics Fall 2008 Woods Hole, MA

• David Remsen
• 23 September 2008

2
Two data management practicals
3
Workshop Objectives

• Extend Principles of Database Design to a
  hands-on practical that applies the principles
• Create a set of tables to model a simple use case
• Add content (data) to the tables
• Define the relationships between the tables
• Define different views of the data
• Utilize the power of RDBMS to optimize a workflow

4
Use Case

• Create a simple data structure that tracks tests
  given to patients across one or more admissions.
• Records information about a patient
• Enable a patient to be admitted one or more times
• Create a catalog of tests administered to
  patients during the admission period
• Record test results given to patients
• Complete in 2 hours!!

5
Sample Data
6
One to Many
7
Many to Many
8
One to Many in One Table
9
One to Many in One Table
10
Simple patient record database
11
Relational Database Management System
12
End of Introduction
13
Create first table Patients
14
Specify options for columns
15
Add records with initial entry View
16
Changing the view with Layouts
17
Add additional views of the same table
Select from pre-defined general types or start
with a blank view
18
Proceed through steps select columns
19
New view is created as new layout
20
Create the Admissions table
Its generally a good idea to create a primary
key for every table
21
Have the system work for you
RDBMS features facilitate data entry
22
Better, but still doing it the hard way
Manually entering the patient ID.
23
Your first relationship
24
Finding common ground/maintaining integrity
25
Making relationships work for you
A one to one relationship view
26
One to Many Relationship Views
View multiple admissions for one patient.
27
One to Many Relationship Views
Select the columns from the related database to
add to the view
28
Modify to optimize data entry
Enable new admissions to be made from the
Patients Table view
29
Create a new admission the easy way
30
Create two more tables Tests
Create the table and make at least the following
records
31
Create two more tables Test Results
32
Relationships can be complicated
Table Results joins Admissions and Tests in a
many-to-many relationship
33
View test results from Admissions View
34
Let the system work for you
Create a Value List made up of tests from the
Test tables
35
Define a list of values
36
Attach the value list to a field
37
Select a Test from the list to enter a new result
38
Summary
A normalized view of the use case we created.