Data Systems and Issues

NCAEMSA Winter Conference 2004Wednesday February 18, 2004

William E. Ott, MS, Paramedic

CPCS Technologies

www . cpcstech . com

Integrated System

Data

Warehouse

EMSMedical Examiner

Hospitals

Data transformation and scrubbing

MedicalDirection

Data Reporting

LawEnforcement

Wide Area Wide Area Network (WAN)Network (WAN)

9.6 Kbit/s <2Mbs9.6 Kbit/s <2Mbs• Voice• SMS• e-Mail• Web browsing

• mCommerce• Internet access• Document transfer• Low/high quality video

GPS

Mobility – PAN, LAN, WAN

Local Area Network wLAN

802.11b802.11b

LAN

<11Mbs<11Mbs• Access•“hot spots”•LAN equivalent

WirelessBridge

WorkgroupSwitches

Personal Area Network (PAN)

<1Mbs<1Mbs• Access•Synchronization•10 Meters

Bluetooth

EMS as Information Workers

• What is involved?– Electronic patient records– CAD data pre and post response– GIS data pre and post response– System performance data– Application of performance data to the

continuing education program– Personnel data– System / Vehicle data– Facility/Event preplan data

Threats to Information Systems

• Malicious abuse• Denial of Service and related attacks• Virus, Worm, and Trojan attacks• Outside Hacker attacks• Theft of service• Theft of information• Poorly trained IT staff• Not staying current with system patches,

antivirus definitions, etc..• Not performing proper system maintenance• Poor or no backup and contingency plans

Threats to Productivity

• Spam

– wastes resources

– wastes time

– offensive, dangerous

• Popup ads

– wastes resources

– annoying

• Malicious use of resources

– wastes bandwidth, storage

– violates law and privacy

Threats to Privacy / Confidentiality

• No security plan• No security training or awareness• Smart or Meta Tags in shared documents• Social Engineering• Unencrypted network• Unencrypted e-mail• No firewall• No antivirus system• Rogue wireless• PDAs connecting to network and servers

Some Security Options

• Virtual Private Networking (VPN)• Active AntiVirus Screening• Stateful packet inspection Firewalling• Proxy servers• Opt-in e-mail • Database encryption• E-mail encryption• Network / PC security policies• Two Factor User Authentication• Aggressive Audit logging and review

Sources of Threats

• Employees

– Unintentional - acting in good faith

– Intentional - disgruntled or unhappy staff

– Software errors

• Environment

– Equipment failure

– Fire, flood, earthquake

Comprehensive Security Policy

• The policy must address:

– Physical Security

– Computer hardware and software inventory

– Personnel screening and selection

– Ongoing education

– Access and control procedures

Comprehensive Security Policy

• Must also address:

– Procedures for release of information

– Disposal of data

– Data backup and recovery

– Contingency planning

– Sanctions for noncompliance

– Periodic review

Costs of Security

• Reduced access to information.

• Increased time and effort to access information.

• Hardware and software to implement security.

• Staff time to implement and maintain security system.

Physical Security

• Control access to servers and network equipment.

• Locate workstations in secure area, not easily accessible to the public.

• Provide surge protection and uninterruptible power supplies.

• Provide fire alarms and fire suppression equipment.

Hardware Security

• Hardware should be dependable.• Non-proprietary to allow for easy repair and

replacement.• Critical systems should be mirrored and spare

parts available for likely to fail components.• Routine maintenance and tuning should be done.

Have a service contract in place! • Maintain accurate and up to date inventory.

Software Security

• Applications should be chosen with security in mind.

• Should have the capability of encryption for data storage and communication.

• System security software:– Firewall– Intrusion detection– Anti-virus– Disk defragmenter

• Maintain accurate and up to date inventory.

Access control

• Protect critical resources by limiting access to authorized and authenticated users.

• Specify:– who can access the information, – how it can be accessed, – when it can be accessed, and – under what conditions it can be accessed

What Are Potential Disasters? External

• Storms (hurricanes, tornados, floods, hail…)

• Accidents (planes, trains, automobiles, hazardous mat.)

• Regional Outages (power, communications…)

• Violence (civil unrest, terrorist acts, bioterrorism…)

Internal

• Hardware Failures (servers, data stores, cyber attacks..)

• Accidents (fires, water leaks, electrical…)

• Violence (disgruntled employee, corp. sabotage…)

Contingency Planning

• Plan for interruption of service.

• Have alternate plan for data capture and retrieval. (Paper?)

• Have adequate security for alternate plan.

Data Backup and Recovery

• One of the most crucial components!

• Most likely component to be ignored.

• Practice data recovery!

• Use data protection schemes such as mirroring, RAID.

• Large agencies should consider hot sites.

Disposal of Data

• Discarded computer parts and peripherals should be dependably erased or destroyed.

• Removable media should be accounted for.

• Hardcopy printed from computerized records should be controlled.

System Components

Transformation

Input Output

Control Mechanism

Four Parallel Systems

• User system

• Data system

• Software system

• Hardware system

Data

Software

Hardware

User

Input

• Automatic data capture

• User Assisted– Optical Mark

Reader (OMR)– Optical Character

Reader (OCR)– Keyboard– Voice recognition

Transformation

Input Output

Control Mechanism

Transformation

• Data is collected and analyzed

• Aggregation• Analysis• Validation

Transformation

Input Output

Control Mechanism

Output

• Reporting– Ad hoc– Exception reports– Aggregate

• Publishing– Web-based

Transformation

Input Output

Control Mechanism

Control Mechanism

• Quality improvement• Education• Administrative policies• Medical protocols

Transformation

Input Output

Control Mechanism

Systems Architecture

• Stand-alone

• Peer network

• Mainframe-terminal

• Client-server

• Terminal-server

Stand Alone

• Each computer functions alone.• No connection with any other

computers.• Easy to maintain.• File transfer by “sneaker net”

only.

Peer Network

• Computers connected to each other.

• Limited to file and print sharing.

• Connected via local area network.

• Share of data weakens security.

• No central control.

Mainframe-Terminal

• May be mini-computer or mainframe.• Commonly referred to as legacy

system.• “Dumb” terminals.• All activity on main computer.• Connected with cable. • Normally not GUI based application. • Not conducive to ad hoc queries and

reporting.

Client-Server• Client are fully functional

computers.• Server may host applications.• File sharing and printing normally

done through server.• Connected via local or wide area

network.• May be very secure.• High cost of multiple client

workstations (purchase and maintenance)

Terminal-Server

• New technology.• Multiple “dumb” terminals

connected to server.• Applications, printing, file storage

are on server.• Connected via local or wide area

network.• Centrally maintained software.• Low-cost network terminal.

Clustering

• Multiple servers.• Servers are joined and share

processing.• Service is maintained with

failure of single server.• Highly dependable with little

down-time.

Database Systems

Schema

• Pronounced SKEE-mah.

• The organization or structure for a database.

• Often used to refer to a graphical depiction of the database structure.

Data Components

• Database

• Tables

• Records

• Columns

Tables

Table Name

Column Names

Patient

PatientIDAddress

CityState

ZipCodeAgeDOB

Primary Key

Table• A collection of

similar data organized in columns and rows (records).

• Concept similar to a spreadsheet.

Patient TablePatientID City State DOBORA4567 Danville VA11/11/54ORB1111 OrlandoFL 08/26/17ORA1234 Bithlo FL 05/03/38ORB5678 Taft FL 01/01/74

ColumnRow

(Record)

Table

Column

• Each column is a data element.

• The storage format for each column is defined

• Column names are listed at the top

Patient TablePatientID City State DOBORA4567 Danville VA11/11/54ORB1111 OrlandoFL 08/26/17ORA1234 Bithlo FL 05/03/38ORB5678 Taft FL 01/01/74

ColumnData

ColumnName

Data Element

• Data elements have different types

• All data in a column will be of the same type.

Patient TablePatientID City State DOBORA4567 Danville VA11/11/54ORB1111 OrlandoFL 08/26/17ORA1234 Bithlo FL 05/03/38ORB5678 Taft FL 01/01/74

ColumnData

Data Element Types

• Character or text• Numerical

– Integer– Fixed – Real

• Date (time)• Binary or raw• Memo or long• Link

Data Elements

• Each field has a type.

• The length of the field is set for character fields.

• Most other fields can expand to accommodate more data.

Data Elements

• Beware! – Not all fields containing numbers should be number fields.

• Numbers that are not used in any arithmetic should be in character fields.

• Examples are Social Security numbers, telephone numbers and any other identification number.

Database Front-End

• Front-end: the interface that the user sees to input and manipulate data.

• Front-ends are usually built using some programming language such as: – PowerBuilder– Visual Basic– Java– Delphi

• Usually connect to some relational database.

Database Back-end

• Back-end: the relational database used to store and manipulate the data.

• Relational database management (RDBM)

Relational Database

• A collection of data items organized as a set of tables (like spreadsheets).

• Tables may be linked to form new tables.

• Has rows and columns to show the relationships between items.

• Tree-like structure.

Flat File Database

• Stores information in single file.

• Does not allow a one-to-many relationship.

• Limits the amount of data that may be input per record.

Desktop DB vs. RDBMS

• Desktop include:– Access– Approach– Filemaker Pro– FoxPro

• All processing occurs on the standalone.

• Intended for smaller databases.

• Front-end included.

• RDBMS include:– Oracle– Informix– DB2– MS SQL

• Processing occurs on the server.

• Has tools for larger databases.

• Requires front-end programming.

One-to-Many Relationship

Incident

Patient

Event

Event

Event

Event

Patient

Event

Event

Event

Event

Patient

Event

Event

Event

Event

One Incident-Many Patients

One-to-Many Relationship

Patient

Event – IV Access

Event – O2 Admin

Event - Medication

Event - Procedure

One Patient-Many Events

Many-to-Many Relationship

Doctor

Patient

Patient

Patient

Patient

One Doctor-Many

Patients

Doctor

Doctor One Patient-Many

Doctors

One-to-One Relationships

Patient

One Patient-One Home Address

Home Address

Ambulance

One Ambulance-One Defibrillator

Defibrillator

Keys

• A key field should be present in each table.

• Tables are related (linked) using keys.

• A key may be made of multiple combined fields.

Primary Keys

• Primary keys are values that uniquely identify each record within the table.

• Primary keys must always be filled in and not duplicate any of the other values in the table.

Foreign Keys

• Tables may contain a “foreign” key.

• Foreign keys are the primary key for a related table.

• Multiple records may have the same foreign key that link them to a single record in the related table.

Table Relationship

Patient

PatientIDName

AddressCity

StateZipCode

AgeDOB

Foreign Key

TreatmentPatientID

Treatment IDMedication

DosageRoute

Primary Key

Same valueLinks the two tables

Table Joins

• May create a new table, “target”, from the source tables.

• May be temporary – called a “query”.

• May use many tables to assemble the desired data set.

Table Join

Name Patient ID Og Oglesby OR13567John Doe OR54321

Patient ID Medication DosageOR54321 Epinephrine 1.0 mgOR13567 ASA 162 mgOR54321 Oxygen 12 l/mOR13567 Atropine 1.0 mg

Tables are associated with the primary key

Note: One-to-many relationship

Relational vs. Flat File

• Flat file databases are limited to predefined number of data occurrences.

• Most desktop databases are relational, however, some applications are designed as flat file.

Relational vs. Flat File

Note: One-to-many relationship

Name Patient ID Og Oglesby OR13567

Patient ID B/P B/P Time OR13567 110/80 13:45OR13567 116/82 13:55 OR13567 120/82 14:05

Name Patient ID B/P1 Time1 B/P2 Time2 Og Oglesby OR13567 110/80 13:45 116/82 13:55

Flat File Database

Relational Database

Table Join

Name Patient ID Og Oglesby OR13567John Doe OR54321

Patient ID Medication DosageOR54321 Epinephrine 1.0 mgOR13567 ASA 162 mgOR54321 Oxygen 12 l/mOR13567 Atropine 1.0 mgNote: One-to-many relationship

The Patient ID is the primary key in the patient table and the foreign key in the medication table

Table Joins

Name Patient ID Medication DosageOg Oglesby OR13567 ASA 162 mgOg Oglesby OR13567 Atropine 1.0 mgJohn Doe OR54321 Epinephrine 1.0 mgJohn Doe OR54321 Oxygen 12 l/m

Patient ID Medication DosageOR54321 Epinephrine 1.0 mgOR13567 ASA 162 mgOR54321 Oxygen 12 l/mOR13567 Atropine 1.0 mg

Name Patient IDOg Oglesby OR13567John Doe OR54321

SourceSource

Target

Table Joins

Name Patient ID Medication DosageOg Oglesby OR13567 ASA 162 mgOg Oglesby OR13567 Atropine 1.0 mgJohn Doe OR54321 Epinephrine 1.0 mgJohn Doe OR54321 Oxygen 12 l/m

Patient ID Medication DosageOR54321 Epinephrine 1.0 mgOR13567 ASA 162 mgOR54321 Oxygen 12 l/mOR13567 Atropine 1.0 mg

Name Patient IDOg Oglesby OR13567John Doe OR54321

SourceSource

Target

Reporting

Name Patient ID Medication DosageOg Oglesby OR13567 ASA 162 mgOg Oglesby OR13567 Atropine 1.0 mgJohn Doe OR54321 Epinephrine 1.0 mgJohn Doe OR54321 Oxygen 12 l/m

Name Patient ID Medication DosageOg Oglesby OR13567 ASA 162 mgOg Oglesby OR13567 Atropine 1.0 mgJohn Doe OR54321 Epinephrine 1.0 mgJohn Doe OR54321 Oxygen 12 l/m

Reporting

Name Patient ID Medication DosageOg Oglesby OR13567 ASA 162 mgOg Oglesby OR13567 Atropine 1.0 mgJohn Doe OR54321 Epinephrine 1.0 mgJohn Doe OR54321 Oxygen 12 l/m

Name Patient ID Medication DosageOg Oglesby OR13567

ASA 162 mgAtropine 1.0 mg

John Doe OR54321 Epinephrine 1.0 mgOxygen 12 l/m