HI 320 HIM 3202 Rasmussen Appendix, Parkinson Disease, Stress & Autoimmune Disease Case Study This assignment requires the development of 4 sample studies

HI 320 HIM 3202 Rasmussen Appendix, Parkinson Disease, Stress & Autoimmune Disease Case Study This assignment requires the development of 4 sample studies with fielded and narrative data uses. The term “fielded data” means data that is stored in specified locations in a database table and usually according to specified formats, such as using only M/F/N to designate a patient’s biological sex. Example below.

Fielded data is also referred to as structured data. Another example is a yes/no question that is answered using a drop-down choice, the question ‘Are you a diabetic?’ which only has yes or no as possible answers could be captured using a fielded data format. This data can be placed into a specific field in the database to be retrieved later. Data that is not fielded are considered free-form, such as narrative paragraphs and unstructured statements in text. A fine example is the ‘chief complaint’ offered by a patient:

Consider and compare the advantages of using the fielded/structured data approach when searching an EHR system for patients who may need care reminders or quality reviews for care appropriateness. You may find it useful to consult literature or websites to help you develop examples of studies and then review sample studies to identify data that was likely ‘fielded/structured’ verses data that was likely free narrative text. However, all such references need to be fully identified, and the paper needs to be written in your own words, and it should not be quoted items from resources. Here is a sample study below that demonstrates the usefulness of both fielded and narrative data. (PLEASE do not use this example in your assignment submission as it will not generate any credit or points for you!)

For this assignment submission, develop and explain:

Three (3) examples of studies or information requests that you think may be most quickly or accurately done using structured/fielded data. For each example:
Explain the goal of your created study.
Identify fielded data that is relevant to each study. Describe it and identify the values of the field.
Indicate purpose of the fielded data to the study. What will it result in?
One (1) study or information request that could require using narrative text such as physician’s or nurses’ notes in records.
Explain the goal of your created study.
Identify narrative data that is relevant to the study.
Indicate purpose of the narrative data in the study.

The grading rubric is shown below. This assignment might be best created in a table format and should have adequate content to effectively communicate your study purpose and demonstrate comprehension of both fielded and text data fields. Please use proper spelling/grammar and be at least two (2) pages long.

Criteria Points
Study One fielded data – includes goal, relevant fielded data and purpose 8
Study two fielded data – includes goal, relevant fielded data and purpose 7
Study three fielded data – includes goal, relevant fielded data and purpose 7
Study four Narrative data – includes goal, relevant narrative data and purpose 8
Total Points 30
STUDY GUIDE:

Comparing Entities vs. Attributes vs. Relationships

Entities

When we decide how to organize our data for a database, first we need to determine how to structure its storage. You might think we could just begin entering data and change the structure later. That approach may work with a spreadsheet. However, large databases are not easily restructured and changed. We must first take a very careful view of the world and processes as they are. Then, we decide where we can most effectively place each item needed to be stored. Only after the structure has been developed and scrutinized can we then begin to use and enter data to the database.

Databases always contain Tables. We decide early in the process what tables are necessary and which ones might be redundant or not contribute to a good design.

Each Table within a database has a name, as it represents an Entity. Each data item entered has to relate to a specific Entity. There is no place for “loose data” within a database. Everything has a designated storage place. However, long fields can be created for data items that allow longer, free-text entry like a nurse’s note.

Entities are named using Nouns, such as: Nurse, Medications, Surgical instrument, Patient room, Admission, Attending physician, Physical therapist, Respiratory therapy treatment, and so forth. Entities include persons, places, things, or events.

At first when identifying Entities, do not use any verbs describing what that person or item “does.” For example, we would never have an Entity’s Table name such as “A nurse has to administer medications.” We start by listing our Tables, indicating only the Entity names — people, places, things involved in care — that we can think of, that we must have stored within from our system. Additional entities will arise or present themselves during the analysis process.

Attributes

Now, consider what makes each single item (record) of an Entity different from the other records. When considering nurses — each individual nurse has a name, a particular job (staff vs. charge), shifts and services she/he can work, licensure level, and perhaps a nursing specialty, right? Each of those items can become a column in the Nurse table. The columns represent the Attributes. We also often call Attributes the “fields” of that table. When the table is all put together, those attributes serve to describe each individual nurse.

A Medication table would contain very different distinguishing features (Attributes) from the Nurse table. Medications are characterized by drug name, dosage level, administration form, controlled substance schedule, and so forth. Each medication, fortunately, has a naming system already called the ‘NDC Code’ that contains all those details on every drug. When a doctor writes a prescription order, the physician will specify the medication in the prescription along with other instructions for the patient (how often to take it, with or without food, etc.).

No single database table would “intermingle” nurse characteristics and the medications since they are two very different “things” — different Entities with different Attributes/characteristics. This is extremely important for the efficiency of the database.

As we develop Attributes in a table, remember that they are characteristics and also are usually nouns such as: drug name, dosage level, nurse licensure level, nurse role (floor staff/floater), etc.

Relationships

Relationships are where we start to be able to use verbs. Verbs are action words or groups of action words. Since so much data is in “separate” tables, we have to eventually link them together in a sort of sensible structure. It doesn’t do much good to look at a patient record, observe that they got a medication, but have no way to easily retrieve what that medication name was. Therefore, we use the Relational Database Model structure.

Now finally for the big picture, example Statements as used in the logical analysis:

Statement Example
Nurse (Entity/noun) Administers (Relationship/verb) a Medication (another Entity/noun) to the Patient (Entity/noun).

In this single example, we need at least three entities: Nurse, Medication, and Patient. At least two relationships will be needed: 1) Nurse administers the medication, 2) Patient receives the medication. This would not work out if we had only a Nurse and Patient entity. The Medication needs its own table, too.

Practice

See if you can now identify the Entities versus the Relationships in the following statements:

Statement 1:
Respiratory Therapist Delivers respiratory therapy treatment to a Patient.

Background information regarding Statement 1:
As you know, each time a respiratory therapist delivers a treatment, they must document it to the record with type, time of day, length of treatment and so forth.

Statement 2:
Nurse Evaluates the patient’s temperature.

Background information regarding Statement 2:
Nurses evaluate and document a patient’s temperature. They also make professional judgments on whether it may require alerting physicians on improvement or decline of the patient. The nurse will document not just the temperature in vital signs, or medication administration in the eMAR, but might also make notes regarding patient response to the item.

Video: Attributes and Relationships in Microsoft Access

Now, watch the video showing how these Attributes can be formed and more relationships used with our Microsoft Access program.

Cardinality and Its Uses

Logical Data Models

Logical Data Models allow us to create and consider partially specified tables and relationships. They are only partially specified because later on the data types, lengths, and values they can assume will be specified. The logical data model focuses only on entities, relationships, and individual attributes.

Cardinality

After developing the entities and relationships we will also specify “how many” of One Entity/thing can relate to how many of the Different Entity/thing. Unfortunately, it is not enough to just name our entities for the relational database! Here we provide just a basic introduction to “cardinality” (how many of this -to- how many of that) so that you can recognize that term.

Consider these examples:

How many physicians can one single patient see?
Zero, one, or perhaps more than one (many) physician, of course.
How many patients can one physician have?
One or more than one – we hope!
How many Social Security Numbers can one single person have?
One, or zero, but never more than one (legally)

We call this idea the Cardinality of Table relationships.

Cardinality is determined by the real world, not by how we would like it to be. We can choose from these: zero or one; one and only one; one to many; or zero, one, or many. “Many” in this context means any number higher than one. It takes at least two Entities (things) with a relationship to figure out the cardinality.

Think about the relationships described below and try to determine the Cardinality of each:

How many spouses can an adult person (in the United States) have at the same time? (One and only one, Either Zero OR one, Any number between zero to Several/Many of them)
How many children can one woman/mother have?
How many bank accounts can one adult have?
How many meals can one person eat in a day?
How many cookies can one child eat? (zero, one, or many/more than one)

As you go through your day, start thinking about each item you use and what kind of “Cardinality” level it may have in relation to you. Thus, you will be starting to think like a database pro!

Expressing Relationships Through Diagrams

Symbols for Entities and Relationships

Now we can view some relationships adding symbols as used in your textbook.

Symbol for Entities

Entities are represented by boxes/squares like the example shown below.

Symbol for Relationships

Relationships are represented by elongated diamonds like the example shown below.

Entity-Relationship Diagrams

When used in Relationship Diagrams, these symbols are combined to show relationships between elements within a database. Study the Relationship Diagram examples below.

Example 1

Example 2

Example 3

Example 4

Chapter 4 Introduction to MySQL

Chapter 4 Introduction to MySQL

This chapter provides a brief introduction to databases—what they are and why they are used. It also discusses how a database differs from other strategies for storing data and will present some common design approaches. The focus is on one specific type of database called a relational database.

This chapter introduces different ways to create a graphical representation, or model, of a database; and discusses the symbols (and their meanings) used in these models. The entity relationship diagram (ERD) is the type of model most commonly used to create a visual image of a database. Another document commonly used to describe a database is called a data dictionary and it plays an essential role in ensuring that the data in a database are truly meaningful. Following background information on relational databases, examples are given demonstrating how queries can be generated using the textbook database using a language called structured query language (SQL).

Relational Databases

A database is a structured collection of data related to a specific domain. For example, common database software is Microsoft Excel. A spreadsheet is a specific kind of database called a flat file database. Some of the disadvantages of this way of storing data include the following:

•Multiple users cannot access and modify the file simultaneously.
•It can be hard to keep track of versions in single flat files.
•Flat files do not support large volumes of data well.
•It is hard to pull data out of these files programmatically.

An example of a flat file database that was created using Microsoft Excel is shown in figure 4.1. This database has rows and columns. The rows represent a single record or tuple, which is an ordered set of elements. A record in an Excel spreadsheet is the ordered set of data contained in each row. The columns represent the individual details, or attributes, of the record. The first three records in the Excel file show that it is difficult to know what the record (row) is based on. Is this a record about patients or diagnoses? Is this a record about medications? The rows in this database are based on the specific medication that a patient is taking for a given diagnosis. This inability to determine the basis of the record is one of the primary disadvantages of flat file databases.

Another disadvantage of a flat file database is that the number of columns tends to expand over time as more pieces of information are added to the database. Flat file databases tend to contain large amounts of redundant data. For example, the patient ID, patient gender, and patient diagnosis need to be reentered for each new medication prescribed for the diagnosis. Whenever redundant information appears in a database, errors have to be fixed in multiple locations. For example, if the name of a medication was inadvertently misspelled, all occurrences of the medication would need to be updated in the database. Large-scale changes to data in flat file databases are very time intensive. Finally, if there is the need to limit user access to certain columns of data, this is difficult to manage in a flat file database as one would need numerous versions of the database saved for each user.

Description of Relational Databases

Relational databases were designed to avoid the limitations of database architectures like flat file types. The term relational refers to the specific way in which data are stored in the database. This way of storing data is based on relational theory (Codd 1970). Relational theory specifies that the data for any database can be thought of very simply in terms of three things: the entities used to store data, the attributes of those entities, and the relations between them. In the relational model, entities are the nouns, or real world things, about what is used to store data. In the flat file database example shown in figure 4.1, the entities are patients, diagnoses, and medications. Attributes are the adjectives that describe the entities. Patients, for example, have attributes such as name, gender, race, and date of birth. Medications have attributes such as name, dose, brand, and route. Entities also have relations to each other. Relations are the verbs that describe how the entities are related. For example, patient acquires a disorder, a disorder is treated by a medication, and a medication is consumed by a patient.

Figure 4.1. Flat file type clinic database

When representing entities, attributes, and relations on a schematic known as an entity relation model, specific symbols are used for each database component. Figure 4.2 displays the various symbols. An entity is represented with a square; an attribute is represented with an oval; a relation is represented with a diamond. Later in the chapter how these symbols are used to develop a model for visualizing a relational database is explored.

Entities

A database is a collection of data related to a specific topic or domain. In the database for this textbook, the domain is Quality of Healthcare in the United States. The database can be understood as falling in this domain because all of the data are publicly available and published by federal agencies for reporting on the quality of healthcare in the United States. A relational database takes a specific approach to managing data in a domain. The first step is to identify the major entities in the domain. Entities are groups of data that represent things that exist in the real world. In the textbook domain, examples of entities include geographic region, hospitals, nursing homes, hospital-associated infections, and patient deaths. These entities are depicted in figure 4.3.

Figure 4.2. Symbols used to depict database components

Source: Chen 1976.

Figure 4.3. Entities in the domain

Attributes

Attributes are adjectives that describe the characteristics of the entity. For example, all patients have a name, gender, and date of birth. These attributes are intrinsic features of the patient—it is hard to think of an entity of this type that does not have these attributes as defining characteristics. Most patients also have an address, phone number, and at least one significant person who is intimately involved in their life (a guardian, a significant other, or a close friend). These features are not intrinsic attributes of the patient, and some of them can be thought of as distinct entities with specific relations to the patient entity, rather than attributes of the patient.

In the domain covered in this textbook, geographic information is an important attribute of most entities. This is because the domain covers a large geographic area, and healthcare quality may vary by geographic region. The attributes are depicted in figure 4.4.

Relations

Relations are the verbs that describe the way two entities relate to each other. For example, a patient ingests a medication, a medication treats a condition, and a provider orders a medication. These relations are shown in figure 4.5.

Figure 4.4. Attributes of entities in the domain

Figure 4.5. Relations between entities in the domain

Database Diagrams

Databases are detailed and complex entities. They can have hundreds of tables, thousands of fields, and billions of rows of data. Like any complex entity, it can be easier to understand a database if there is a visual representation of it. Visual representations of databases are typically called models. A model helps to create an image of the overall database. A model also presents the individual pieces of the database and how these pieces fit together. It is a single source that contains all the essential information needed to understand the database.

Models give a standard way to depict and focus on the relevant information about a specific type of real-world entity, and the process of creating a model is called modeling. When it comes to databases, there are two modeling terms that are relevant. The first is ERD, and the second is Unified Modeling Language (UML) model. An ERD offers a visualization of the entities of a database—what attributes belong to each entity and how each entity is related. A UML model is a standard notation that can be used for depicting entities, attributes, and relations, and is used for many diagrams including an ERD.

Entity Relationship Diagrams

An entity-relationship diagram (ERD) is a diagram that depicts the entities and relationship between entities in a given domain. As stated previously, a database is a structured collection of data related to a specific domain. Typically, an ERD is created to represent not just the domain, but the database itself. When developing an ERD, early versions of the model may contain entities, attributes, and relationships that may not become part of the final database. However, the final ERD for a database is often the very specification that is used to build the physical database. When an ERD is converted into physical databases, the entities become tables and the attributes become columns. The relations between tables are implemented as keys in each of the two tables they connect. Keys, discussed in detail later in the chapter, are specially chosen columns in a table that help define its relationship to both to the tuple (row) and to other tables.

The diagram in figure 4.6 is one section of the larger ERD for the database used in this textbook. This ERD was generated using the ERD modeling software included with MySQL Workbench. MySQL Workbench has a design module that can aid in the development of a database. Essentially, users can create an ERD, which can be forward engineered into a database. Using the UML model notations, entities appear as squares, the attributes appear as line items within the square, and relationships appear as lines with symbols at either end of them. Some columns have a small key icon next to them, and others have a diamond icon. Columns with the key icon next to them are primary key (PK) columns, and those with red diamond icons next to them are foreign keys (FKs).

Tables and Columns

Relational databases typically create a separate table for each entity. There are four entities shown in figure 4.6 including, hospital_general_information, ipps_2011, hcahps, and readmission_reduction. If there are different instances of a specific entity in the domain that are different enough, a table for each type of entity can be created. An example of an entity that is often implemented as two different entities in a database is a patient visit. Visits to a hospital ambulatory clinic are different enough from an inpatient visit that two tables can be created: one for inpatient visits (admissions) and a separate table for outpatient visits (office visits).

Tables contain columns, or fields. The columns in a table are the attributes of the entity that the table represents. Columns in the hospital_general_information tables shown in figure 4.6 are the important attributes that describe a hospital. These attributes include the name of the hospital, address, city, state, phone number, the type of hospital, ownership type, and an indicator as to whether the hospital offers emergency services.

Relationship Cardinality

Cardinality refers to the type of relationship one table has with another table. More specifically, cardinality explains which number of records in one table can be associated with which number of records in another.

Figure 4.6. Part of the ERD for the textbook database

There are three basic types of relationships that can exist between two tables and are depicted with specific notations on an ERD (see figure 4.7). These are

1.One-to-one
2.One-to-many
3.Many-to-many

A one-to-one relationship means that each record in the first table is associated with one—and only one—record in the second table. Similarly, each record in the second table is associated with only one record in the first table. An example of a one-to-one relationship is the relationship between a patient table and the patient–spouse table. These kinds of relationships are rare in databases. In general, when a one-to-one relationship exists, one of the tables is extended to include the columns in the other. In the case of the patient–spouse table, a spouse column would be added to the patient table. This takes the entity “spouse” and turns it into an attribute of the patient entity. If, however, there were multiple pieces of data to store about each patient’s spouse, a separate spouse table could be created.

A one-to-many relationship is a relationship in which each record in the first table is associated with many records in the second table. Each record in the second table, however, is associated with only one record in the first table. An example of a one-to-many relationship is patients and phone numbers. A patient may have many phone numbers (a home phone number, a work phone number, and a cell phone number). However, any given phone number is associated with only one patient. One-to-many relationships are implemented by adding a column from the table on the “one side” of the relationship to the table on the “many side” of the relationship. In the example of patients and phone numbers, a column would be added to store the patient id in the phone number table.

Figure 4.7. Relationship cardinality

A many-to-many relationship is one in which each record in the first table is associated with many records in the second table. Similarly, each record in the second table is associated with many records in the first table. An example of a many-to-many relationship is the relationship between patients and diagnoses. Each patient may have many diagnoses, and each diagnosis may be given to many patients. Many-to-many relationships between entities are very common. They are more complicated to implement in a database than one-to-many relationships. One-to-many relationships are implemented by adding a column from one table to the other table. Many-to-many relationships, however, require an entirely new table to manage the relationship. The relationship between patients and medications may be a many-to-many relationship. That is, a patient may have zero, one, or many medications. Similarly, a medication may be given to zero, one, or many patients. In order to manage this relationship, a third table needs to be created to store each patient–medication combination. This table will have a one-to-many relationship to the patients table, and a one-to-many relationship with the medications table. This type of table is commonly referred to as an intermediate table.

An intersect table was used in the textbook database and is shown in figure 4.8. The ep_provider_paid_ehr table includes information about providers that were paid for attesting for Meaningful Use under the electronic health record (EHR) incentive program. Many providers were paid once and some were paid more than once. The national_downloadable_file table includes information about each individual provider including their name, medical school, type of specialty, and the organization they are associated with. Some providers are listed more than once since they can be associated with more than one organization. Because a single provider may occur more than once in the ep_provider_paid_ehr table and the national_downloadable_file table there exists a many-to-many relationship. The provider_intersect table was added as an intermediate table to manage the many-to-many relationship that exists between these two tables and generate two one-tomany relationships.

Relationships between tables are depicted in models using a line with a symbol at each end. The line is either dashed or solid (more on that later) and runs between the related tables. Figure 4.7 depicts the symbols commonly used in ERD models. Note that this diagram depicts each line with a symbol at only one end. In an ERD, the symbol is placed at both ends of the line connecting the tables. Figure 4.6 shows several examples of one-to-many relationship that exist in the database used in the textbook. As shown, there is a one-to-many relationship between the hospital_general_information and hcahps tables. That is, one hospital can have one or many rows of data in the hcahps table.

Every relationship between tables is actually two relationships—the relationship of the first table to the second table, and the relationship of the second table to the first table. For example, in the relationship between the Patients and the PrimaryCareProviders table in the diagram in figure 4.9, the symbols are shown on both sides of the line joining the two tables. The “zero or one” symbol is on the PrimaryCareProvider side of the relationship, and the “zero, one, or many” symbol is on the Patients side of the relationship. There are two relationships depicted here: the first shows how many patients a given primary care provider can have, the second shows how many primary care providers a given patient can have.

Figure 4.8. An intermediate table

Figure 4.9. Reading the relationship between two tables

To read the relationship from the PrimaryCareProviders table to Patients table in figure 4.9, start at the PrimaryCareProvider table and follow the line to the symbol at the Patients table end of the line. At the Patients table, the symbol on the line represents zero, one, or many, which shows that each record in the PrimaryCareProvider can be related to zero, one, or many records in the Patients table. That is, a given primary care provider may have no patients, one patient, or many patients in this database. Next, read the relationship from Patients to PrimaryCareProviders, starting with the Patients table and following the line to the symbol at the PrimaryCareProvider. This symbol nearest the PrimaryCareProvider tables represents that each record in the Patients table may have either no record or one record in the PrimaryCareProvider table. That is, one patient will have only one primary care provider, but may have none.

Primary Keys

Every table in a database must have a primary key (PK). The PK is the column (or columns) that uniquely identify a record in the table. Because this column uniquely identifies the entire record, the value in the PK column must be unique. Shown in figure 4.6, the PK for the Patients table is the medical record number (MRN). Each patient is given a unique MRN. The system will not allow a record to be added to the Patients table when the MRN already exists in the table. This is because the database uses the value in the MRN column to identify that particular record, or tuple. A PK cannot contain a NULL value. Some value must be entered into the column in order to create a new record.

Figure 4.10. Primary keys

Figure 4.11. The relationship between a primary key (PK) and a foreign key (FK)

In MySQL, PKs are identified in the data model by a golden colored key icon. Figure 4.10 shows that the MRN is the PK for the Patients table. Similarly, ProblemListID is the primary key in the ProblemList table, and DiagnosisID is the primary key in the Diagnosis table.

Foreign Keys

Foreign keys (FKs) are essential to implementing a relationship between tables. An FK is a column in one table that holds the primary key value of the table to which it is related. An FK always points to the PK in another table. If the PK in one table exists as an FK in another table, it creates a relationship between the two tables. Earlier, the example of patients and phone numbers was used as an instance of a one-to-many relationship. The way these two tables are related is by putting a column in the phone number table to store the patient ID of the patient to whom each number is related. The patients table is related to the phone number table by creating an FK column in the phone number table (see figure 4.11). This FK column contains the value of the PK for the record in the patients table to which the phone number record is related.

The Data Dictionary

A data dictionary is an essential tool for effectively managing and using the data in a database. A data dictionary is a structured way of documenting the data stored in a database. Data dictionaries vary in content. At minimum, a data dictionary includes the names and definitions of all entities and attributes in the database. It includes important metadata such as data type, data length, keys, and constraints. A data dictionary often includes information about the source of the data, as well as the information about any conversions or transformations the data undergo as they move from the original system to the current database.

The primary goal of creating and actively maintaining a data dictionary is to ensure that data are defined consistently and standardized throughout the system. The American Health Information Management Association (AHIMA) emphasizes the central role a data dictionary plays in improving the reliability, dependability, and trustworthiness of data use (AHIMA 2012). The data dictionary provides a source for the definition of each data element within the database, its format, and the relationship between different fields and tables. It is critical for working with data stored within the system. Figure 4.12 provides an example of the data dictionary from this textbook’s database.

The data dictionary shown in figure 4.12 displays the name of the database table,