Introduction to Healthcare Informatics, Third Edition PDF

EMPOWERING PEOPLE TO IMPACT HEALTH™ ® EMPOWERING PEOPLE TO IMPACT HEALTH™ Introduction to Healthcare Informatics, Third Edition Chapter 5: Understanding Databases...

EMPOWERING PEOPLE TO IMPACT HEALTH™ ® EMPOWERING PEOPLE TO IMPACT HEALTH™ Introduction to Healthcare Informatics, Third Edition Chapter 5: Understanding Databases ® 1 | AHIMA.ORG©2023 | AHIMA.ORG ©2023  EMPOWERING PEOPLE TO IMPACT HEALTH™ Objectives, 1 Identify the difference in flat data files versus relational databases Apply basic SQL commands to select data for reporting and analysis ® 2 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Objectives, 2 Utilize a data dictionary to understand data attributes Discover the role of data modeling in database maintenance and design Understand the various types of data resources Understand importance of Data Marts and Data Warehouses ® 3 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Databases in Healthcare May be used for storing Operational data Financial data Clinical data Data used for Quality improvement Operational improvement Research ® 4 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Database Terminology Data tables Patient I D Provider I D Appointment Appointment Records date time A B D239 SM123 1/23/2012 9 00 a.m. D I R235 SM123 1/23/2012 9 30 a.m. Fields JKF764 SM123 1/23/2012 10 00 a.m. Database Database management system (DBMS) ® 5 ©2023 | AHIMA.ORG © 2020 American Health Information Management Association  EMPOWERING PEOPLE TO IMPACT HEALTH™ Determine Appropriate Database to Store Data How many records will the database contain? How do the variables relate to each other? What database tools are available for use? How will the data be pulled or queried from the database? ® 6 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Types of Databases—Flat Files, 1 Methods of storing flat files Spreadsheet tabs Text files Format Rows and columns Limitations Does not allow relationships between variables in separate tables The same data element may need to be stored in multiple tables ® 7 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Types of Databases—Relational Databases, 3 Entity relationship diagram (E R D) Primary key Foreign key Cardinality Jump to Types of Databases—Relational Databases, 3, Appendix ® 8 ©2023 | AHIMA.ORG © 2020 American Health Information Management Association  EMPOWERING PEOPLE TO IMPACT HEALTH™ Types of Databases—Other Hierarchical databases Common in E H R’s NoSQL Used to optimize access to big data Object oriented databases Store images and methods to use them ® 9 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Types of Relationship Cardinality One-to-one: Each row in one table relates to one and only one row in the other One-to-many: Each row in one table may relate to many rows in a second table; each row in the second table relates to only one row in the first table Many-to-many: Each row in one table may relate to many rows in a second table; each row in the second table may relate to many rows in the first table ® 10 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Forms of Normalization, 1 First Normal Form Eliminate repeating groups in individual tables Create a separate table for each set of related data Identify each set of related data with a primary key Second Normal Form Create separate tables for sets of values that apply to multiple records Relate these tables with a foreign key Third Normal Form Eliminate fields that do not depend on the key ® 11 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Relationships Between Normalized Tables Jump to Relationships Between Normalized Tables, Appendix ® 12 ©2023 | AHIMA.ORG © 2020 American Health Information Management Association  EMPOWERING PEOPLE TO IMPACT HEALTH™ Object-Oriented Databases Types of information about the object The data itself (audio clip, image, and such) Method—how to use the data ® 13 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Database Management Software Choice depends on Type of database Size of database Complexity of the relationships Common relational database software Microsoft Access Oracle Microsoft SQL Server ® 14 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Data Dictionary, 1 Attributes (or metadata) Field name Table name Description of the field Data type (numeric or test, field length) Data frequency (required field or not) Primary or foreign key Valid values Data source Field creation date Field termination date Update Frequency ® 15 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Structured Query Language (SQL) SQL roles: An interactive query language A database programming language A database administration language A client/server language An internet data access language A distributed database language A database gateway language ® 16 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Data Modeling, 1 Outputs of the data modeling process Diagram 0 Entity-relationship diagram (E R D) Data Dictionary ® 17 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Data Modeling, 2 Process of data modeling The conceptual model The logical model The physical data model ® 18 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Clinical Data Warehouses and Data Marts Decision support databases Common ontology Data marts may store portions of data that are used for a particular purpose or department ® 19 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Summary Understanding database structure and design is essential Skills which can be used for all database management Data modeling Data dictionary creation Writing SQL queries ® 20 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Data Resources – Add’l Readings SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL 3rd Edition Storytelling with Data: A Data Visualization Guide for Business Professionals: Storytelling with Data: Let’s Practice An Introduction to Database Management Systems The Language of SQL: How to Access Data in Relational Databases Head First SQL: Your Brain on SQL –A Learner’s Guide ® 21 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Data Resources – Product Documentation Oracle - https://docs.oracle.com/ database/121/SQLRF/toc.htm Postgres - https://www.postgresql.org/docs/ current/ Google Bigquery - https://cloud.google.com/ bigquery/docs MySql - https://dev.mysql.com/doc/ ® 22 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ APPENDIX ® 23 ©2023 | AHIMA.ORG  EMPOWERING PEOPLE TO IMPACT HEALTH™ Types of Databases—Relational Databases, 3, Appendix The patient information and the visits are presented using two tables. The relational database manages the relationship between these two tables. The patient information table, labeled patient info, lists the following information: patient ID (pronounced I D), first name, last name, MI (pronounced M I), address 1, address 2, city, state, zipcode, insurance ID, insurance company, and insurance plan. The table labeled visits lists the following information: account number, patient ID, service from date, service to date, and department. “Patient ID” in the patient information table and the visits table are related as the patient ID is the primary key in the patient information table and the foreign key in the visits table. Jump back to Types of Databases—Relational Databases, 3 ® 24 ©2023 | AHIMA.ORG © 2020 American Health Information Management Association  EMPOWERING PEOPLE TO IMPACT HEALTH™ Relationships Between Normalized Tables, Appendix There are four tables. The first table is labeled visits, the second table is labeled services, the third table is labeled CDM (pronounced C D M), and the fourth table is labeled revenue codes. The visits table lists the following information: account number, patient ID (pronounced I D), and service date. The service table lists the following information: account number, CDM item, and units. The CDM table lists the following information: CDM item, revenue code, HCPCS (pronounced H C P C S) code, and unit charge. The revenue code table lists the following information: revenue code and definition. “Account number” is listed under the table labeled visits as well as the table labeled services and is hence eliminated. “Account number” is the primary key in the visits table and the foreign key in the services table. “CDM (pronounced C D M) item” is listed under the table labeled services as well as the table labeled CDM and is hence eliminated. CDM item, under the table labeled services, is the primary key and, under the table labeled CDM, is the foreign key. “Revenue code” is listed under the table labeled revenue codes as well as the table labeled CDM. Revenue code is the primary key in the revenue codes table. Jump back to Relationships Between Normalized Tables, Appendix ® 25 ©2023 | AHIMA.ORG © 2020 American Health Information Management Association