Temporal Databases and Maintenance of Time-Oriented Clinical Data

Temporal Databases and Maintenance of Time-Oriented Clinical Data Yuval Shahar, M. D. , Ph. D.

A Clinical Scenario Ms. Jones was seen in the diabetes clinic on January 14 1997 at 11 A. M. Her blood-glucose value at that time was measured in the clinic as 220 mg/100 ml. She complained of vomiting and dizziness for the past 2 or 3 days. She was eventually hospitalized on the same day. A more accurate bloodglucose test that was taken at the same time as the one performed in the clinic returned from the laboratory on January 15 1997, with a value of 380 mg/100 ml. Ms. Jones was discharged on January 17 1997, and was seen again in the clinic on January 24 1997. At that time, several renal-function serum and urine tests were performed in addition to measuring blood-glucose values. A complete neurological assessment was carried out as well.

Uses of Clinical Data • Clinical decision making – monitoring – diagnosis – therapy • Clinical research • Administration and other tasks – Quality assessment – Billing • Legal records

Clinical Database Features • Clinical data is time oriented – different temporal aspects, such as when was the data valid, versus when was the data recorded • Often, there is inherent uncertainty regarding the time, value, or both aspects of the data • Data are often incorrect, incomplete, or inconsistent • Might require a specialized database management system (DBMS)

Data Quality Issues • Correctness – Validation during data entry – Validation by global data analysis • Completeness – Missing observations • Possible bias due to hidden contexts • Possible completion from neighboring values • Possible completion from related data types • Consistency – Consistent semantics over patients and time

A Temporal Query • “Determine if the oncology patient (currently under therapy by a chemotherapy protocol) had within the past 6 months at least two episodes that lasted for more than 3 weeks, of Grade II bone-marrow toxicity (due to a specific chemotherapy drug)” • Responding to such queries is necessary to support clinical management, such as when using a clinical guideline

The Time-Oriented Database (TOD) • Developed at Stanford during the 1970 s • A cubic, three-dimensional structure – patients X visits X clinical parameters --> value • Microcomputer version: MEDLOG • Two file structures: – One indexed by patients, for individual information – One indexed by parameter type, for statistical analysis

The ARAMIS Database • The American Rheumatism Association Medical Information System (ARAMIS) • Developed at Stanford during the 1970 s and maintained since that time in multiple sites • Contains longitudinal data concerning multiple patients who have rheumatic diseases or arthritis • Originally used TOD, then MEDLOG and other tools for analysis of chronic diseases

Types of Temporal Dimensions (Snodgrass and Ahn, 1986) • Transaction time: The time in which (or during which) data are stored in the database (e. g. , in which “the patient has mild anemia” was recorded) • Valid time: The time during which the data were true (e. g. , the period during which the patient did, in fact, have mild anemia) • User-defined time: A time stamp or interval that is specific to the application (e. g. , the time in which the anemia level was determined in the laboratory) => Transaction time and valid time define the database type

Database Types, A Temporal View • Snapshot databases: Have no time aspect (flat records) • Rollback databases: Have only transaction time (e. g. , a series of time-stamped updates to the patient’s current address and phone number) • Historical databases: Have only valid time (e. g. , a series of updates of the patient’s state of anemia during January 1997, deleting previous values that refer to that time period, keeping only the latest updates) • Bitemporal databases: Have both transaction time and valid time (e. g. , on February 12 1997, it was recorded that, during January 1997, the patient had mild anemia)

A Tale of Two Data Types

Time and Uncertainty • There is often uncertainty as to when the clinical episode started or ended, and what its duration was • One way of representing such uncertainty is by using a Variable Time Interval (sometimes augmented by min/max duration constraints) Beginning Body Time End

Temporal Reasoning and Temporal Maintenance • Temporal reasoning supports inference tasks involving time-oriented data; often connected with artificial-intelligence methods • Temporal data maintenance deals with storage and retrieval of data that has multiple temporal dimensions; often connected with database systems • Both require temporal data modelling

Examples of Temporal. Maintenance Systems • The TNET system and the TQuery query language (Kahn, Stanford/UCSF) • TSQL 2, a bitemporal-database query language (Snodgrass et al. , Arizona) • The Chronus/Chronus 2 projects (Stanford)

The TQuery Language (Kahn, 1991) • Used within the TNET temporal network system, which was used by the Stanford ONCOCIN oncology-therapy automated protocol-based system during the 1980 s • Each TNODE represents a time interval during which a clinical event happened • TQuery allows users to store and retrieve data using clinical contexts rather than dates • Query: : = <Function Attribute-Name When> – (that is, perform Function on Attribute-Name during When) • When: : = <Interval-Name Range <When> Pname Pcondition> (a recursive temporal specification)

Tquery Examples • (Visit (1 4)) – The first to fourth TNODES with type label = Visit • (Visit FIRST (Cycle (-4 – 1))) – The first of each of the TNODES with label = Visit from the last four TNODES with type label = Cycle • ((Visit Tx) All ((Cm. Tx POCC) ALL) WBC (NCOMPARE > $ 4. 5) -Select from all (type chemotherapy, subtype POCC) all the nodes with (type visit, subtype Tx) in which the value of attribute WBC exists and is greater than 4. 5

TSQL 2 (Snodgrass, 1995) • Designed by a committee of researchers, headed by Snodgrass at Arizona University • Consolidates existing approaches • Inherits from SQL-92 temporal types such as DATE – Adds the PERIOD data type • A linear, bounded at both ends, time line • No commitment to discrete, dense, or continuous temporal ontologies: Queries must include granularity to be meaningful • A bitemporal conceptual data model, timestamps tuples by a set of bitemporal chronons; each chronon (t, v) is a rectangle in valid time/transaction time space

The Bitemporal Conceptual Data Model Valid Time 17/3/95 23/2//95 Hospitalized(Jane) 5/1/95 27/11/94 23/2/95 1/4/95 21/6/95 3/7/95 Transaction Time

TSQL 2: Examples • What drugs were prescribed to Jane in 1996? SELECT Drug VALID INTERSECT (VALID (Prescription), PERIOD ‘[1996]’ DAY) FROM Prescription WHERE Name = ‘Jane’ • Insert a prescription with a known period of validity INSERT INTO Prescription VALUES (‘Jane’, ‘Dr. Max’, ‘Lasix’, ’ 50 mg’, INTERVAL ‘ 4: 00’ MINUTE) VALID PERIOD ‘[2000 -07 -23 – 2000 -8 -14]’

Chronus II (O’Connor et al. , 1999) • A Stanford model, influenced by TSQL 2 and the previous Das Chronus system, which it considerably enhances • Designed to support queries in the EON guidelinebased therapy system and the Tzolkin temporal mediator to patient data • Supports most of SQL-92 as well as extensions such as valid time, indeterminacy, multiple calendars, hierarchical types, temporal joins, etc. • Temporal indeterminacy uses the Snodgrass model of lower support, upper support, and a probability mass function to denote the event’s temporal distribution

Chronus II: Example • Select employees that have worked as a mechanic for longer than two months: TEMPORAL SELECT Name FROM Occupation WHERE Title = ‘Mechanic’ WHEN DURATION(Occupation, ‘Months’) >2

Summary • Clinical databases require representations that include a strong emphasis on time and uncertainty • Bitemporal databases are necessary to support clinical, research, administrative and legal requirements