Introduction to Health Care Data Analytics Module 4

Introduction to Health Care Data Analytics Module 4: Data Analysis Tools and Techniques Lecture c, Databases Part II This material was developed through a collaboration between Bellevue College and the Veterans Health Administration, U. S. Department of Veterans Affairs, funded in part by the Department of Health and Human Services, Office of the National Coordinator for Health Information Technology award number 90 WT 0002. Except where otherwise noted, this work is licensed under the Creative Commons Attribution-Non. Commercial. Share. Alike 4. 0 International License. To view a copy of this license, visit http: //creativecommons. org/licenses/by-nc-sa/4. 0/.

Learning Objectives • Define data analytics terms • Describe the process steps of data analytics and the tools used in each step • Describe the role of the data analyst • Identify tools and techniques used to analyze and interpret health care data effectively • Describe key database concepts. • Describe the various types of databases and how they are structured • Describe key data warehouse concepts • Describe enterprise data architecture as seen in health care organizations 2

Overview This lecture further explores the various conceptual models of database designs and types of queries Image by Stuart Miles, 2014 3

Types of Databases • Databases and database management systems (DBMS) are categorized by structure (how the data is related or organized) • Example database models: – Flat file – Hierarchical – Network – Relational – Object oriented 4

Flat File Database • Contains one table without any structured relationship between the records • Advantages: Simple, all records stored in one place, easy to search and filter • Disadvantages: Cannot handle large volumes of data, data redundancy, low data security, multiple versions • Example: Microsoft Excel spreadsheet 5

Hierarchical Database • Widely used on mainframe computers in the 1960 s; found today on older systems • Data is organized in a tree-like structure • Each child record has one and only one parent (1: 1) • Each parent record can have one or more child records (1: M) • Relationships are organized using address pointers linking records • Start at the root to retrieve data • VA (VISTA) is a hierarchical database Shelly, Cashman & Rosenblatt, 2003 Image by Brandt, K. , 2016 6

Hierarchical Database: Parent-to-Child Relationships Image by Brandt, K. , 2016 7

Hierarchical Database Example Image by Brandt, K. , 2016 8

Hierarchical Database: Advantages & Disadvantages Advantages • Able to handle large volumes of data • Able to map simple business processes (one to many) • Improved data integrity, data security, data sharing, and access speed Disadvantages • Not user friendly • Data structural dependencies • Cannot model all types of relationships • Reduced flexibility • Lack of standards 9

Network Database • Similar to the hierarchical database in structure except that each child entity can have more than one parent • Each relationship called a set • Each set contains a parent record (owner) and child (member) • Not widely used today but may exist in legacy systems • Advantages: Map complex relationships, improved structural dependencies • Disadvantages: Still not user friendly Image by Brandt, K. , 2016 10

Relational Database • Most common type of database • Tables are connected via primary and foreign keys • Primary key (PK): Unique identifier for each distinct row within a table • Foreign key (FK): An identifier that is a primary key in a different table • Relationship between PK and FK are displayed using an entity relationship diagram or E-R diagram • Examples: Microsoft Access, SQL Server, My. SQL, IBM DB 2, Oracle Database 11

Entity Relationship Diagram (E-R Diagram) Image by Brandt, K. , 2016, using Microsoft Access Northwind sample database 12

ER-Diagram Example • One patient has one or more appointments • One or more appointments are scheduled for one patient Image by Brandt, K. , 2016 13

Relational Database: Advantages & Disadvantages Advantages • User friendly • Complete data structure independence • Improved security with privilege access control • Reduced data redundancies • Perform ad hoc queries with SQL Disadvantages • Costly to set up and maintain • Changes between the relationship between tables may affect other relationships within the database • Multiple versions 14

Object-Oriented Database • Developed for more complex data types (images, audio, and video) • Represent real-world objects • Data and their relationships are in a single structure Advantages: • Can store more complex types of data Disadvantages: • Not user friendly • Lack of marketing interest • Lack of standards • Decreased flexibility 15

Object-Oriented Database Example Image by Brandt, K. , 2016 16

Flat & Hierarchical Databases: Advantages & Disadvantages Database Structure Flat Hierarchical Advantages Disadvantages Simple to set up Records stored in one place Easy to understand Records are in one table, making it easy to search and filter Manages large volumes of data Able to model business processes and data as simple relationships Increased data security and data integrity due to DBMS Improved data sharing (one version used by all) Speed of access increased due to predefined data paths Difficult to manage large volumes of data Data redundancy Low security Multiple versions possible Not user friendly Difficult to manage due to data structural dependencies Unable to model all types of real-world data relationships Reduced flexibility Lack of standards 17

Network & Relational Databases: Advantages & Disadvantages Database Structure Network Advantages Relational Disadvantages Deals with large amounts of data (more than hierarchical) Able to map more complex relationship types Improved data structural independence High-speed data retrieval Database standards Promotes data integrity Still not user friendly System complexity— more than hierarchical Supports large volumes of data User friendly Structural independence Better security with privilege access control Reduced data redundancy—data is stored once Query language to create complex queries (SQL) Expensive to set up and maintain Data complexity— lots of records can be affected through a change in the relationship structure Multiple versions possible 18

Object Oriented Database: Advantages & Disadvantages Database Structure Object Oriented Advantages Able to store complex data types (graphics, video and audio) Disadvantages Not user friendly Not widely accepted by major vendors Lack of standards Reduced flexibility (lacks ad hoc query capability) 19

Query • A question answered by the database • May use various methods, such as Structured Query Language (SQL) and Query-by. Example (QBE) • Results presented on screen to be saved or printed 20

Query Example SQL Statement Query-by-Example (QBE) Images by Brandt, K. , 2016 Query Results 21

Summary • Conceptual database model: – A database structure using entities, attributes and instances – Depicts relationships between entities – Follows business rules – Types: Flat, hierarchical, network, relational and object oriented • Database queries can be performed using SQL and QBE methods 22

References Data Analytics Tools and Techniques References – Lecture c Capron, H. L. , & Johnson, J. A. (2004). Computers: Tools for an information age (8 th ed. ). Upper Saddle River, NJ: Prentice Hall. Rob, P. , & Coronel, P. (2004). Database systems: Design, implementation & management (6 th ed. ). Boston: Course Technology. Shelly, G. B. , Cashman, T. J. , & Rosenblatt, H. J. (2003). Systems analysis and design (5 th ed. ). Boston: Course Technology. Images Slide 3: Miles, Stuart (2014). Database magnifier shows bytes magnification and computing stock photo. Retrieved from http: //www. freedigitalphotos. net/images/database-magnifier-shows-bytesmagnification-and-computing-photo-p 294453 Slides 5– 8, 10, 12– 13, 16: Brandt, K. (2016). Graphics 23

Introduction to Health Care Data Analytics: Data Analytics Tools and Techniques Lecture c This material was developed through a collaboration between Bellevue College and the Veterans Health Administration, U. S. Department of Veterans Affairs, funded in part by the Department of Health and Human Services, Office of the National Coordinator for Health Information Technology award number 90 WT 0002. Version 1. 0/Fall 2016 24