NCAEMSA Winter Conference 2004 Wednesday February 18 2004

NCAEMSA Winter Conference 2004 Wednesday February 18, 2004 Data Systems and Issues William E. Ott, MS, Paramedic CPCS Technologies www. cpcstech. com

Integrated System Data transformation and scrubbing EMS Medical Examiner Data Warehouse Law Enforcement Hospitals Data Reporting Medical Direction

Mobility – PAN, LAN, WAN 802. 11 b Local Area Bluetooth Network w. LAN Personal Area Network (PAN) LAN <1 Mbs • Access Workgroup • Synchronization Switches • 10 Meters <11 Mbs • Access • “hot spots” • LAN equivalent Wide Area Network (WAN) Wireless Bridge GPS 9. 6 Kbit/s <2 Mbs • m. Commerce • SMS • Internet access • e-Mail • Document transfer • Web browsing • Low/high quality video • Voice

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 Anti. Virus 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? q External • Storms (hurricanes, tornados, floods, hail…) • Accidents (planes, trains, automobiles, hazardous mat. ) • Regional Outages (power, communications…) • Violence (civil unrest, terrorist acts, bioterrorism…) q 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 Control Mechanism Input Output Transformation

Four Parallel Systems • • User system Data system Software system Hardware system User Data Software Hardware

Input • Automatic data capture • User Assisted – Optical Mark Reader (OMR) – Optical Character Reader (OCR) – Keyboard – Voice recognition Control Mechanism Output Input Transformation

Transformation • Data is collected analyzed • Aggregation • Analysis • Validation Control Mechanism Output Input Transformation

Output • Reporting – Ad hoc – Exception reports – Aggregate • Publishing – Web-based Control Mechanism Output Input Transformation

Control Mechanism • • Quality improvement Education Administrative policies Medical protocols Control Mechanism Output Input Transformation

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. ID Address City State Zip. Code Age DOB Primary Key

Table • A collection of similar data organized in columns and rows (records). • Concept similar to a spreadsheet. Table Patient. ID ORA 4567 ORB 1111 ORA 1234 ORB 5678 City Danville Orlando Bithlo Taft Column State VA FL FL FL DOB 11/11/54 08/26/17 05/03/38 01/01/74 Row (Record)

Column • Each column is a data element. • The storage format for each column is defined • Column names are listed at the top Column Name Patient Table Patient. ID ORA 4567 ORB 1111 ORA 1234 ORB 5678 City Danville Orlando Bithlo Taft Column Data State VA FL FL FL DOB 11/11/54 08/26/17 05/03/38 01/01/74

Data Element • Data elements have different types • All data in a column will be of the same type. Patient Table Patient. ID ORA 4567 ORB 1111 ORA 1234 ORB 5678 City Danville Orlando Bithlo Taft Column Data State VA FL FL FL DOB 11/11/54 08/26/17 05/03/38 01/01/74

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: – Power. Builder – 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 – Fox. Pro • All processing occurs on the standalone. • Intended for smaller databases. • Front-end included. • RDBMS include: – Oracle – Informix – DB 2 – MS SQL • Processing occurs on the server. • Has tools for larger databases. • Requires front-end programming.

One-to-Many Relationship One Incident. Many Patients Incident Patient Event Event Event

One-to-Many Relationship One Patient. Many Events Patient Event – IV Access Event – O 2 Admin Event - Medication Event - Procedure

Many-to-Many Relationship Doctor One Patient. Many Doctors Doctor One Doctor. Many Patients Patient

One-to-One Relationships One Patient-One Home Address Patient Home Address One Ambulance-One Defibrillator Ambulance 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. ID Name Address City State Zip. Code Age DOB Primary Key Foreign Key Same value Links the two tables Treatment Patient. ID Treatment ID Medication Dosage Route

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 Og Oglesby John Doe Patient ID OR 13567 OR 54321 Tables are associated with the primary key Patient ID Medication OR 54321 Epinephrine OR 13567 ASA OR 54321 Oxygen OR 13567 Atropine Note: One-to-many relationship Dosage 1. 0 mg 162 mg 12 l/m 1. 0 mg

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 Database Name Patient ID B/P 1 Time 1 B/P 2 Time 2 Og Oglesby OR 13567 110/80 13: 45 116/82 13: 55 Relational Database Name Patient ID Og Oglesby OR 13567 Patient ID B/P Time OR 13567 110/80 13: 45 OR 13567 116/82 13: 55 OR 13567 120/82 14: 05 Note: One-to-many relationship

Table Join Name Og Oglesby John Doe Patient ID OR 13567 OR 54321 The Patient ID is the primary key in the patient table and the foreign key in the medication table Patient ID Medication OR 54321 Epinephrine OR 13567 ASA OR 54321 Oxygen OR 13567 Atropine Note: One-to-many relationship Dosage 1. 0 mg 162 mg 12 l/m 1. 0 mg

Table Joins Source Name Patient ID Og Oglesby OR 13567 John Doe OR 54321 Name Og Oglesby John Doe Patient ID OR 13567 OR 54321 Target Patient ID Medication Dosage OR 54321 Epinephrine 1. 0 mg OR 13567 ASA 162 mg OR 54321 Oxygen 12 l/m OR 13567 Atropine 1. 0 mg Medication Dosage ASA 162 mg Atropine 1. 0 mg Epinephrine 1. 0 mg Oxygen 12 l/m

Reporting Name Og Oglesby John Doe Patient ID OR 13567 OR 54321 Medication Dosage ASA 162 mg Atropine 1. 0 mg Epinephrine 1. 0 mg Oxygen 12 l/m Medication ASA Atropine Epinephrine Oxygen Dosage 162 mg 1. 0 mg 12 l/m

Reporting Name Og Oglesby John Doe Patient ID OR 13567 OR 54321 Patient ID OR 13567 Medication ASA Atropine Epinephrine Oxygen Medication Dosage 162 mg 1. 0 mg 12 l/m Dosage ASA Atropine 162 mg 1. 0 mg Epinephrine Oxygen 1. 0 mg 12 l/m OR 54321