Database 101

• J. Anthony Parker, MD PhD
• Beth Israel Deaconess Medical Center
• Joint Program in Nuclear Medicine
• Harvard Medical School

Outline

• Introduction
• Database Management System Elements
• Tables
• Index
• Relationships between Tables
• Screens
• Reports
• Browse
• Key Points
• Examples
• Design
• Client / Server
• Conclusions

Introduction

• Definition: Stores and Provides Access to Data
• Why Should You Care
• Major Computer Applications
• Word Processing
• Spreadsheets
• Presentation Software (Graphics, Slides)
• Web Browser / E-mail
• Databases
• Hospital Information System is a Database
• Radiology Information System is a Database
• Organizes Research
• Types
• Flat File
• Network: pointers
• Hierarchical: parent/child pointers
• Relational
• Object-oriented
• Not Mature wrt Word Processing or Spreadsheets

Database Management System Elements

Tables

• Hold All the Data
• Very Simple
• Two Dimensional
• Rows = Records
• Columns = Fields
• Data Types (Limited)
• Number
• Text
• Date
• Logical
• Object
• Example
• Setup Table

Index

• Efficient Access Method
• Some or All Fields or Combinations may be Indexed
• "Sorted" List of Field Values
• Seperate "Sorted" List for each Field which is Indexed
• "Sorted" List Points to Table
• Tables aren't Sorted
• Example
• Setup Table with Index
• May be Complex
• Can be Rebuilt from Tables

Relationships between Tables

• Common Field in Two Tabless
• Can be A Combination of Fields
• Example of a Relation: Table1.MRN = Table2.MRN
• Ties Tables Together
• One-to-many
• Example
• Patients Related to Studies by MRN
• Studies Related to Dose by Study #
• Inventory Related to Dose by Isotope
• Nuclear Medicine Physician, Radiopharmacist, Technologist each have a View of the Data
• Relations are
• Not in Tables
• Affect How Tables are Used
• Provide for Sophisticated Database Design
• Data should be Organized Logically
• In general, Same Type of Data Should Only be in One Field

Data Entry Screens

• One Record (Row) per Screen
• Can Include Input and Output e.g. Identification Information
• Often Represented by a Form
• Examples: Invoice, Order, Requisition, Patient Demographics
• Control Flow of Application
• Example, Browse
• Often Responsible for Data Input Validation

Reports

• Obtains Information from Database
• Low Level: Query -> Output
• Higher Level: From a Screen
• Predefined Report: From a Menu
• Formats for Output (e.g. Printer)
• May Include Calculations

Browse Table

• Like Spreadsheet
• Copy to / from Spreadsheet, Flat File
• Allows Data Entry / Modification
• Example

Key Points

• Tables are Simple
• Maintainable
• Reliable
• Indexes and Relations can be Complex
• Relational Database Design
• Takes Some Experience
• Helps Organize Project

Examples

• Radiology Coding System
• Patient Registration: Data Entry Screen, Table
• Studies
• CT Data Entry Screen
• OB Data Entry Screen
• Biometry Data Entry Screen
• Codes Entry Screen
• Database Structure
• Studies Related to Patients by MRN
• Codes and Biometry Related to Studies by Study Number
• Same Code Table for All Modailites
• Lookup Study
• Lookup Code
• Print Menu -> Report
• Teaching File
• Case Entry Screen, Collection
• Items, Item Types
• Status

Design

• Seperate Entities become Seperate Tables
• Attributes of Entitites become Columns
• Relations between Entities
• Normalization
• No Repeated Columns
• Denormalization

Client / Server

• Database are a Major Client / Server Application
• Client
• Screens
• Reports
• Server
• Tables
• Indexes
• Communication
• SQL
• Old Model - Middleware
• World Wide Web (Forms Capability)
• Multiple Users
• Distributed Databases

Conclusions

• Tables are Simple Two Dimensional Data Structures
• Indexes Facilitate Rapid Data Access
• Relations Model Application Complexity
• Good Database Design may Provide Good Application Design
• Major Client / Server Application