| #### Developing Time-Oriented Database Applications in SQL Richard T. Snodgrass |

# TEMPORAL DATABASE DESIGN

## Contents :

This document outlines the steps in the Temporal Database Design. We still the case of Brad De Groot's feed yard application, discussed informally in Chapter 2. We listed it briefly here:

They started getting sick in early June of 1997. Some just had a bad stomach ache; others had severe cramping and were passing blood. They suffered from a potentially lethal strain of the bacterium Escherichia coli, E. coli O157:H7. By mid-August some dozen-odd cases, all in Colorado, were traced back to a processing plant in Columbus, Nebraska. The plant's operator, Hudson Foods, eventually recalled 25 million pounds of frozen hamburger to attempt to stem the outbreak.

That particular plant processes about 400,000 pounds of hamburger daily. Ironically, this plant had received high marks for its cleanliness and adherence to Federal food processing standards. What led to the recall of about one-fifth of the plant's annual output was the lack of a database that could track the patties back to the slaughterhouses that supply carcasses to the Columbus plant. It is believed that the meat was contaminated in one of these slaughterhouses, but without such tracking, all were suspect.

Put simply, the lack of an adequate temporal database cost Hudson Foods $25 million.

Dr. Brad de Groot is a veterinarian at the University of Nebraska at Lincoln, about 60 miles southeast of Columbus. He is interested in improving the health maintenance of cows on their way to your freezer. He hopes to establish the temporal relationships between putative risk factor exposure (e.g., a previously healthy cow sharing a pen with a sick animal) and subsequent health events (e.g., the healthy cow later succumbs to a disease). These relationships can lead to an understanding of how disease is transferred to and among cattle, and ultimately to better detection and prevention regimes. As input to this epidemiological study, Brad is collecting data from commercial feed-yard record keeping systems to extract the movement of some 55,000 head of cattle through the myriad pens of large feed lots in Nebraska.

Following is the steps for this case study.

> Conceptual Design

> Logical Design

> Physical Design

> Advanced Design Aspects

#

# Conceptual Design

## Schema for the Examples

> Oracle8 Server

# Logical Design

## Operations

> Oracle8 Server

# Physical Design

## Operations

> Oracle8 Server

# Advanced Design Aspects

> Oracle8 Server

#### Code Credits

Wei Li, Department of Computer Science, University of Arizona (weil@cs.arizona.edu)

#### HTML Credits

Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
April 27, 1999 (Last Update)