Databases

Collections of data

Data may be collected, manipulated and retrieved in various ways:

• plain text editor - simple editing and retrieval
• word processor - adds tables and simple calculations
• spreadsheet programme - adds more sophisticated calculations
• database management system (DBMS) - adds formats, structures, rules, ...

Reasons for a DBMS

A DBMS is a software package for defining and managing a database.

A ‘real’ database includes

• definitions of
  • field names
  • data formats (text? binary? integer? etc.)
  • record structures (fixed-length? pointers? field order, etc.)
  • file structures (sequential? indexed? etc.)
• rules for validating and manipulating data
• data

A DBMS provides Data independence.

One must be able to change storage mechanisms and formats without having to modify all application programmes. For example:

• method of representation of alphanumeric data (e.g., changing date format to avoid Y2000 problem)
• method of representation of numeric data (e.g., integer vs. long integer or floating-point)
• units (e.g., metric vs. furlongs)
• record structure (example)
• file structure (sequential, sorted, indexed, etc.)

Database organization

Four main types of database organization:

• Flat
• Hierarchical
• Relational
• Object-oriented

Flat databases

A single kind of record with a fixed number of fields.

Notice the repetition of data, and thus an increased chance of errors.

Hierarchical databases

Hierarchical relationships among different types of data.

• can be very easy to answer some questions, but very difficult to answer others
• if one-to-many relationship is violated (e.g., a patient can have more than one physician) then the hierarchy becomes a network

Hierarchical example

Question #1: What is the average weight of babies delivered by Jones to mothers with heights > 170 cm?

Question #2: How many babies with birth weights > 2.5 kg have been delivered by each physician?

Question #1: What is the average weight of babies delivered by Jones to mothers with heights > 170 cm?

First we scan through the Physician records looking for Jones.

Question #1: What is the average weight of babies delivered by Jones to mothers with heights > 170 cm?

Now we've found Jones ...

Question #1: What is the average weight of babies delivered by Jones to mothers with heights > 170 cm?

... so we scan through the relevant Mother records looking for mothers with heights > 170 cm.

Question #1: What is the average weight of babies delivered by Jones to mothers with heights > 170 cm?

This mother's height is > 170 cm ...

Question #1: What is the average weight of babies delivered by Jones to mothers with heights > 170 cm?

... so we scan through her babies looking at the weights.

Question #1: What is the average weight of babies delivered by Jones to mothers with heights > 170 cm?

Now we can calculate the average weight for these two babies.

We've read 6 records in all.

Question #2: How many babies with birth weights > 2.5 kg have been delivered by each physician?

To get at the weights in all the baby records, we have to first read each physician record ...

Question #2: How many babies with birth weights > 2.5 kg have been delivered by each physician?

... and, for each physician, read each mother record ...

Question #2: How many babies with birth weights > 2.5 kg have been delivered by each physician?

... and finally, for each mother, read each baby record.

Question #2: How many babies with birth weights > 2.5 kg have been delivered by each physician?

Another baby record ...

Question #2: How many babies with birth weights > 2.5 kg have been delivered by each physician?

We've had to read 17 records in all, including all the mother records (which we weren't really interested in).

Relational databases

Data are organized as logically independent tables. Features:

• ‘Natural’
• Not so strongly biased towards specific questions
• Expresses relationships by means of shared data rather than explicit pointers
• Theoretical basis: relational algebra, calculus; closure
• Operations on tables (Join, Project, Select) to form new tables

Operations on tables

• Join: combine tables having common columns
• Project: extract specified columns, remove duplicate rows
• Select: select rows satisfying a condition

Relational examples

Question #1: What is average weight of babies delivered by Jones to mothers with heights > 170 cm?

First we Join the Mother and Physician tables.

M#	MHt	MBirth	Ph#	PhName
123456	150	4605	123	Smith
392382	177	4912	220	Jones
238427	162	5204	123	Smith
649308	174	5409	123	Smith
732293	155	5810	220	Jones

Next we Project the columns that we're interested in.

PhName	MHt	M#
Smith	150	123456
Jones	177	392382
Smith	162	238427
Smith	174	649308
Jones	155	732293

Next we Select the records with PhName = Jones and MHt > 170.

One record satisfies the selection criteria.

PhName	MHt	M#
Jones	177	392382

Next we Join this with the Baby table.

The join with the baby table results in 2 records.

PhName	MHt	M#	Deliv	MWt	BWt
Jones	177	392382	7512	57.4	3.6
Jones	177	392382	7706	58.2	3.4

From these we can calculate the average weight.

We have carried out 4 relational operations (Join, Project, Select and Join).

Question #2: How many babies with birth weights > 2.5 kg have been delivered by each physician?

First we Select babies with BWt > 2.5

B#	M#	Deliv	MWt	BWt
941830	392382	7512	57.4	3.6
743920	392382	7706	58.2	3.4
840238	238427	7801	64.1	2.7
230484	123456	7803	60.3	3.4
749200	649308	7902	58.7	2.9
530284	123456	8005	62.3	3.7
638201	649308	8101	57.9	3.1

Next we Project the columns we're interested in.

B#	BWt	M#
941830	3.6	392382
743920	3.4	392382
840238	2.7	238427
230484	3.4	123456
749200	2.9	649308
530284	3.7	123456
638201	3.1	649308

Next we Join this with the Mother table.

B#	BWt	M#	MHt	MBirth	Ph#
941830	3.6	392382	177	4912	220
743920	3.4	392382	177	4912	220
840238	2.7	238427	162	5204	123
230484	3.4	123456	150	4605	123
749200	2.9	649308	174	5409	123
530284	3.7	123456	150	4605	123
638201	3.1	649308	174	5409	123

Now we can count the babies for each physician.

We've done 3 relational operations.

Relational implementation

Relational operations are computationally expensive.

A relational front end may be built on top of a hierarchical back end.

Relational software

• MySQL
• Oracle ($)
• IBM DB2 ($)
• Ingres → Sybase ($), MS SQL Server ($), Postgres, PostgreSQL
• HSQL (Java; used in OOo)
• OpenOffice.org Base
• MS Access ($)
• etc.

Object-oriented databases

Object-oriented analysis is another way to model the world, involving abstraction, encapsulation, modularity and hierarchy (with inheritance).

An object consists of data and methods.

Classes are used to group objects which have the same types of data and the same methods.

Abstraction

Consider only features which are necessary for the problem at hand.

For example, a person may be defined by the ability to ask for the ID number, age, height and weight.

Encapsulation

The internal structure of an object is hidden.

For example, all we know is that we can ask for the age, not whether the age is (1) periodically updated or (2) calculated on demand from birth date and current date.

Modularity

Grouping classes and objects into ‘cohesive and loosely coupled modules’.

For example, the classes person and cat might be implemented within the same module because they share many things (like how to calculate age).

Hierarchy

Objects are instances of classes, e.g., JDoe is an instance of the class patient

Classes and objects form two different hierarchies.

• Class hierarchy (‘kind’): e.g., patient is a kind of person. A class inherits (and specializes) the characteristics of its parent class.
• Object hierarchy (‘part’): e.g., the object JDoe.height is a part of the object JDoe.

Examples of class hierarchies

Example of object hierarchy

• Delivery
  • DateEvent
  • Physician
    • IDNum
    • Name
  • Mother
    • IDNum
    • DateBirth
    • Height
  • Baby
    • IDNum
    • DateBirth
    • WeightB
  • WeightM

Examples of objects

Persons

Events

Object-oriented implementation

An object-oriented front end may be built on top of a relational back end.

For example, open-source Hibernate.

Centralized databases

E.g., hospital information system. Complex.
Advantages:

• shared data, reduced redundancy
• fewer inconsistencies in data
• enforcement of standards
• security restrictions
• balancing of conflicting requirements

Historical note: ‘The minimum computer memory required for average data bases varies from 30K to 160K bytes.’ (Fundamentals of Data Base Systems, S.M. Deen, 1977, p. 5). This memory requirement is less than the size of the level-2 cache in a current entry-level PC.

A centralized DBMS will offer different views of the data.

Different applications will need to look at data in different ways. For example:

• Clinical lab doesn't need to see patient demographics
• Admission/discharge office doesn't need to see lab results
• Neither needs to see payroll data

Electronic Medical Records

2001 survey: EMR's used by general practitioners (ref) :

• > 85%: Sweden,Netherlands
• > 50%: Denmark, UK, Finland, Austria
• < 50%: Germany, Belgium, Italy, Luxembourg, Ireland
• < 20%: Greece, USA

2005 study: in USA, EMR's used by

• < ¹/₃ of hospitals,
• < ¹/₅ of private-practice physicians

Current efforts

• Canada Health Infoway
  • launched 2001
  • goal: interoperable EMR's for 50% of population by 2010
• Office of the National Coordinator for Health Information Technology (USA)
  • launched 2004
  • goal: interoperable EMR's for ‘most’ by 2014
• Global

• Biotechnology Council
  • joint effort of AMA, IEEE, AIChE, ASME and 6 other groups (USA, 2005)
  • goal: to help standardization of everything from medical terminology to networking protocols
  • so medical records can be stored electronically and sent anywhere instantly
  • privacy, security, and understandability
    (article; comment)

Importance of semantics, structured terminology.

At MUHC

Oacis

Jointly with CHUM.
Open architecture using HL7.

• master patient index
• results-display module
• order-entry module with embedded expert system
• clinical-notes module
• multidisciplinary care plan

Used by clinicians.
Can be linked to medical devices.

(Further details on Oacis)

DRG system

• Diagnosis Related Groups
• coding and abstracting of the medical record
• grouping cases by complexity
• 3M™ products: Health Data Management and CodeFinder™ (with ICD-10, starting 2006)
• used by archivists, after discharge
• reports to be added to patient records