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.CriteriaPointsStudy One fielded data – includes goal, relevant fielded data and purpose8Study two fielded data – includes goal, relevant fielded data and purpose7Study three fielded data – includes goal, relevant fielded data and purpose7Study four Narrative data – includes goal, relevant narrative data and purpose8Total Points30STUDY GUIDE:Comparing Entities vs. Attributes vs. RelationshipsEntitiesWhen 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.AttributesNow, 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.RelationshipsRelationships 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 ExampleNurse (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.PracticeSee 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 AccessNow, watch the video showing how these Attributes can be formed and more relationships used with our Microsoft Access program.Cardinality and Its UsesLogical Data ModelsLogical 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.CardinalityAfter 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 DiagramsSymbols for Entities and RelationshipsNow we can view some relationships adding symbols as used in your textbook.Symbol for EntitiesEntities are represented by boxes/squares like the example shown below.Symbol for RelationshipsRelationships are represented by elongated diamonds like the example shown below.Entity-Relationship DiagramsWhen used in Relationship Diagrams, these symbols are combined to show relationships between elements within a database. Study the Relationship Diagram examples below.Example 1Example 2Example 3Example 4Chapter 4 Introduction to MySQLChapter 4 Introduction to MySQLThis 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 DatabasesA 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 DatabasesRelational 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.EntitiesA 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 componentsSource: Chen 1976.Figure 4.3. Entities in the domainAttributesAttributes 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.RelationsRelations 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 domainFigure 4.5. Relations between entities in the domainDatabase DiagramsDatabases 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 DiagramsAn 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 ColumnsRelational 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 CardinalityCardinality 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 databaseThere 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 are1.One-to-one2.One-to-many3.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 tableFigure 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 KeysEvery 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 keysFigure 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 KeysForeign 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 DictionaryA 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, a description of the table of data, and the names, data types, definition, and key information for each column of data in the table. Two tables of data are displayed. The first is the hospital_general_information table. The description states that the table contains general information on hospitals that have been registered with Medicare. The information includes a unique identifier for the hospital, name, address, city, state, zip code, county name, phone number, type, ownership type, and an indicator of offering emergency services for each hospital. The PK is the provider_id, which is a unique identifier for each hospital using the CMS Certification Number (CCN). There are two PKs including the state_code and zipcode for each hospital. The second table is called hcahps, which offers a list of hospital ratings for the Hospital Consumer Assessment of Healthcare Providers and Systems (HCAHPS) survey regarding patient experiences during inpatient hospital stays. The table includes information about the questionnaire item, the answers, the percentage and number of patients that provided specific answers, and the start and end dates of the collection period. The data dictionary shown in figure 4.12 can be used to understand some of the data that are shown in the ERD diagram in figure 4.6. This way, when an individual is developing a query he or she can understand the definitions of each data element as well as how these data elements may be related.Introduction to MySQL and the Textbook DatabaseNow that the basic concepts and components of relational databases have been introduced, the next part of the chapter will focus on a discussion of MySQL Workbench, the software that is used for working with this textbook’s database.MySQL and MySQL WorkbenchMySQL Workbench is a software that allows the user to talk to a MySQL database. MySQL Workbench is not the database itself. Instead, it is an application installed on a client computer. This software allows the user to connect to, visualize, and communicate with any MySQL database (see figure 4.13). The database that is connected could be running on the same computer that MySQL Workbench is running. More often, though, it will be running on a dedicated server somewhere else. A MySQL server is just a computer with MySQL database management system (DBMS) software installed on it. A DBMS is software that allows the user to create and manage databases. Most DBMSs allow the user to create things other than just databases. For example, virtually all DBMSs provide tools to allow administrators to create logins, as well as rules about which databases these users can access. Each MySQL DBMS can have potentially hundreds of individual MySQL databases. For example, if a DBMS is thought of as a big island in an ocean, the individual databases would be the towns or villages on this island. As stated earlier in the chapter, a database is just a structured collection of data stored in a single file. For example, a hospital has a MySQL DBMS installed on a server. On this server, a user may want to create one database to store information specific to each department. In this case, the server is running the DBMS, and the databases contain the collection of data files related to what the user wants to track for each department.Figure 4.12. Part of the data dictionary for the textbook databaseFigure 4.13. How the MySQL Workbench application relates to the MySQL databaseStructured Query LanguageMySQL Workbench uses Structured Query Language (SQL) language for working with databases. SQL is the universal language of databases. No matter what database is used, an understanding of American National Standards Institute (ANSI) standard SQL, with only minor modifications, provides the ability to communicate with virtually any database in the world. All SQL databases are selective about the exact words used, as well as the order of these words. One way to become good at SQL is to memorize a few general rules about how to form good SQL sentences and practice implementing these general rules. Anytime a user talks to a database, the user is asking it to do something for him or her. All database requests fall into one of four general categories: create, retrieve, update, and delete (CRUD) (Heller 2007). When it comes to databases, these are the only four things the user can ask the database to do. Although some requests can be far reaching, a user can only ask the database to create, retrieve, update, or delete something. This simplifies the process of learning the language, because it allows the user to create a few general SQL templates that can be used over and over. Not only can the user create a general template for each type of request, but he or she can also create a master template on which all four of the specific templates can be based. An SQL statement can be as simple as a few words, or span your entire screen. No matter how simple or complex your SQL statement, all queries follow the same basic structure. And just like the English language is made of words formed from only 26 letters, any query imaginable can be written with about 30 basic keywords. The next section will discuss the structure of an SQL statement, starting with a simple statement and gradually reviewing more complex statements. As these examples are introduced, the most important (and commonly used) keywords in the language will be presented.Retrieving DataA complete SQL statement consists of a minimum of two SQL keywords: SELECT and FROM. These key words allow the user to specify the minimum information the database needs in order to retrieve data. The SELECT keyword is used to specify which column(s) contain the data to retrieve. The FROM keyword is used to specify the name of the table (or tables) in which to find where the data live. The basic SQL statement is formatted as follows: To write the SQL statement, start with the keyword SELECT. This keyword SELECT tells the database to retrieve data, and what follows is the list of attributes (also known as columns) containing the data to retrieve. If more than one attribute is retrieved, the name of attribute is specified after the SELECT statement with a comma separating each attribute name. The comma tells the database that what follows is the name of another attribute. After the names of the attributes that will be retrieved have been listed, the keyword FROM is used next to tell the database the name of the table (or tables) where the listed attributes can be found.Figure 4.14. Example of a basic MySQL query For example, to know the hospital name, address, city, and state of all hospitals in the database, the first thing to do is to determine the name of the table that contains the hospital data. The next thing is to determine the name of each attribute that holds the hospital data desired for retrieval: hospital name, address, city, and state. The ERD and data dictionary is used to determine the name of the table and attributes. When reviewing the ERD data dictionary provided online with this textbook, the hospital data are stored in a table called hospital_general_information. Looking at the columns in this table (figure 4.14), the names of the columns are hospital_name, address, city and state. Notice that a comma is included after each attribute in the SELECT statement except the last one. In a SELECT clause, the comma tells the database that the next item in the list is another attribute. The keyword FROM after the last field in the list tells the database that there are no more attributes (so a comma is not necessary). After the FROM statement, the name of the table where these attributes are found is listed. The query is terminated with a semicolon. The semicolon tells the database that the query is done and the data can be retrieved.Filtering DataThere are several ways to filter, or limit, the data one wants to retrieve. The simplest (and most common) way to filter data is to use the WHERE clause. The WHERE clause allows the user to include conditions that must be met in order to include the data in the results. The WHERE clause always comes immediately after the FROM clause:For example, the name of the hospital, address, city, and state for all hospitals in the database that are in the state of California can be retrieved by adding a WHERE clause to the query. An SQL statement, or query, has already been provided in figure 4.14 to retrieve these fields from the database. In order to limit the results to those hospitals in the state of California, a WHERE clause needs to be added. It’s important that when using the WHERE clause, users specify the exact criteria. If the incorrect criteria are provided, no results may be retrieved. For instance, when reviewing the data dictionary, the attribute for state in the hospital_general_information table is written with uppercase letters as an abbreviation. Therefore, in the WHERE clause the state of California must be specified as ‘CA’ rather than ‘California’ or ‘ca’. If ‘CA’ is not specified, zero observations will be returned. An example of the query is shown in figure 4.15. Notice the condition state = ‘CA’. For each record in the patient table, the query engine will determine whether the value in the state field meets the condition (is equal to ‘CA’). If the condition is true, the record will be retrieved and included in the final data set. If the condition is false, it will not be retrieved, and will not appear in the result set returned by the query. In this example, the equality operator (the = sign) is used in the condition. This is a commonly used operator for the WHERE clause, but other operators are also commonly used. Figure 4.16 displays the types of operators that can be used in a WHERE clause.Figure 4.15. Example of a MySQL query with WHERE clauseFigure 4.16. Types of WHERE clause operatorsFigure 4.17. Using the MySQL wildcard operator Figure 4.17 shows the use of the wildcard operator. The SQL statement in figure 4.16 returns the hospital name and ownership status for all hospitals containing the string “government” as the ownership status. In the WHERE clause, the wildcard operator (%) is used along with the logical operator LIKE to specify that the query should return all observations when the hospital_ownership includes the word “government.” The hospital_ownership can take on the following values:•Government—federal•Voluntary nonprofit—private•Government—hospital district or authority•Voluntary nonprofit—other•Proprietary•Government—state•Voluntary nonprofit—church•Government—federal•Government—local•Physician•TribalWhen the WHERE clause specifies that observations will only be returned when hospital_ownership status contains the word “government,” this limits observations to those hospitals that have an ownership status of the following:•Government—federal•Government—hospital district or authority•Government—state•Government—federal•Government—localJoining TablesWhen data are retrieved from a relational database, the data most often reside in more than one table. In order to retrieve data from multiple tables, how the tables are to be joined must be specified. In SQL, the keyword JOIN is used to join tables. This keyword is used within the FROM clause. The FROM clause is where all tables containing columns from which we want data are specified. The general format of a query with a JOIN is as follows:The keyword JOIN must be used anytime more than one table is used in the query. The JOIN is specified between each of the table names. If a query is executed where specific attributes are returned from the hospital_general_information table, the FROM clause looks like this:However, if there are attributes returned that are found in more than one table, the FROM clause will be followed by a JOIN. For instance, if a query is executed where specific attributes are returned from the hospital_general_information table and the readmission_reduction table, the FROM clause looks like this: Oftentimes, novice SQL programmers will list the tables they want to join with a space or comma between the table names. This syntax is NOT correct. The names of the tables must be separated using the keyword JOIN. The keyword JOIN is rarely used without using the keyword ON. The keyword ON is used to specify the columns used to relate the two tables. For example, there is an attribute named provider_id in the hospital_general_information table and the readmission_reduction table. This field contains the provider ID of the hospital. The provider_id is the PK in the hospital_general_information table and a FK in the readmission_reduction table. When the hospital_general_information and readmission_reduction tables are joined, the field that links these tables needs to be specified. To do this, the keyword ON is used immediately following the name of second table. The method for listing attributes after the SELECT statement is also different when a JOIN is included in the query. If two tables are joined together, and there will be attributes returned from both of those tables, the attributes need to be listed with the table name and attribute name in the SELECT statement. The name of the table where the attribute is found is listed first, followed by a period, and ends with the name of the specific attribute. For instance, if the hospital name is to be returned from the hospital_general_information table and the number of readmission is to be returned from the readmission_reduction table, the attributes would be specified after the SELECT statement as follows: An example of a MySQL query that returns the hospital name, address, city, state, readmission measure name, and number of readmissions for that measure is shown in figure 4.18. The query in figure 4.18 can be added to by returning observations that meet certain criteria by adding a WHERE clause. The hospital name, address, city, state, and number of readmissions can be returned for only those hospitals located in the state of California by adding a WHERE clause (figure 4.19). The query shown in figure 4.19 can be expanded by joining three tables and returning attributes that are available in each of the joined tables. For instance, patient satisfaction survey results can be added to the query by adding attributes related to the question name and percentage that responded to the answer located in the hcahps table. These attributes include hcahps_question and hcahps_answer_percent, respectively. Refer to the ERD in figure 4.6—it shows that the hcahps table is related to the hospital_general_information table. Therefore, when the hcahps table is joined in the query, the ON statement will relate the PK from hospital_general_information to the matching FK in the hcahps table, which is provider_id. In addition, the readmission_reduction table will be joined to the hospital_general_inform
Get Answer Please answer all parts in detail:
A random sample of twenty five STAT 231 students was taken in
Question Get Answer Please answer all parts in detail:
A random sample of twenty five STAT 231 students was taken in order to investigate whether or not there is a relationship between the second quiz mark (Y) and the first quiz mark (x). The relationship is modeled using the following:
In a particular game, a fair die is tossed. If the number of spots
Question In a particular game, a fair die is tossed. If the number of spots showing is a 3 or a 4 you win $2. If number of spots showing is 6 you win $5. Otherwise, you lose $7. Let X be the amount that you win when playing the game once. The expected value of X is
A 42-year-old male reports pain in his lower back for the past month. The pain sometimes radiates to his left
A 42-year-old male reports pain in his lower back for the past month. The pain sometimes radiates to his left leg. In determining the cause of the back pain, based on your knowledge of anatomy, what nerve roots might be involved? How would you test for each of them? What other symptoms need to be explored? What are your differential diagnoses for acute low back pain? Consider the possible origins using the Agency for Healthcare Research and Quality (AHRQ) guidelines as a framework. What physical examination will you perform? What special maneuvers will you perform?With regard to the case study you were assigned:Consider what history would be necessary to collect from the patient in the case study you were assigned.Consider what physical exams and diagnostic tests would be appropriate to gather more information about the patient’s condition. How would the results be used to make a diagnosis?Identify at least five possible conditions that may be considered in a differential diagnosis for the patient.Post an episodic/focused note about the patient in the case study to which you were assigned using the episodic/focused note template provided in the Week 5 resources. Provide evidence from the literature to support diagnostic tests that would be appropriate for each case. List five different possible conditions for the patient’s differential diagnosis, and justify why you selected each.
Please answer all parts in detail: Please solve them without using the probability table.
Religion and Theology Assignment Writing ServiceQuestion Get Answer Please answer all parts in detail: Please solve them without using the probability table. Q: A random sample of twenty five students was taken in order to investigate whether or not there is a relationship between the second quiz mark (Y) and the first quiz mark (x). The relationship is modeled using the following:
You are considering franchising a Shell gasoline station and need
Question You are considering franchising a Shell gasoline station and need information on the amount of gas sold daily at the station you are interested in owning. Suppose the amount of gasoline sold daily is uniformly distributed with a minimum of 2000 gallons and a maximum of 5000 gallons. What is the probability that the daily sales will fall between 2500 gallons and 3000 gallons?
To make informed decisions and achieve strategic goals, health care leaders must carefully analyze an organization’s financial position. A ratio
To make informed decisions and achieve strategic goals, health care leaders must carefully analyze an organization’s financial position. A ratio analysis, for example, may impact decisions for strategic initiatives such as expansions, consolidations, mergers, and acquisitions. For this Assignment, you calculate financial ratios and consider their implications for organizations.To prepare:Review the Week 7 Assignment document in this week’s Learning Resources. Examine the financial data for the health care organizations in each scenario.Note: Your Assignment should show effective application of triangulation of content and resources in your conclusion and recommendations.The AssignmentUsing the scenarios and financial data provided in the Week 7 Assignment document, calculate financial ratios and evaluate their implications on organizational decision making.
What is the probability of rolling a four with a six sided die? What
Question Get Answer What is the probability of rolling a four with a six sided die? What is the probability that a person can roll a four 3 times in a row? (assume that rolling the die each time does not affect the outcome of the next roll)
An article in the San Jose Mercury News stated that students in the
Question An article in the San Jose Mercury News stated that students in the California state university system take 4.5 years, on average, to finish their undergraduate degrees. Suppose you believe that the mean time is longer. You conduct a survey of 41 students and obtain a sample mean of 5.1 with a sample standard deviation of 1.2. Do the data support your claim at the 1% level?Construct a 95% confidence interval for the true mean
A 20-year-old male complains of experiencing intermittent headaches. The headaches diffuse all over the head, but the greatest intensity and
A 20-year-old male complains of experiencing intermittent headaches. The headaches diffuse all over the head, but the greatest intensity and pressure occurs above the eyes and spreads through the nose, cheekbones, and jaw..Consider what history would be necessary to collect from the patient in the case study you were assigned.Consider what physical exams and diagnostic tests would be appropriate to gather more information about the patient’s condition. How would the results be used to make a diagnosis?Identify at least five possible conditions that may be considered in a differential diagnosis for the patient.Use the Episodic/Focused SOAP Template and create an episodic/focused note about the patient in the case study to which you were assigned using the episodic/focused note template provided in the Week 5 resources. Provide evidence from the literature to support diagnostic tests that would be appropriate for each case. List five different possible conditions for the patient’s differential diagnosis, and justify why you selected each.
Given two independent random samples with the following results: n1= 14 n2= 9
Question Given two independent random samples with the following results: n1= 14 n2= 9 x1=92.30 x2=81.50s1= 19.75 s2= 25.75Use this data to find the 90% confidence interval for the true difference between the population means. Assume that the population variances are not equal and that the two populations are normally distributed.Find the point estimate that should be used in constructing the confidence interval.What Is the margin of error? 6 decimal placesWhat is the confidence interval? 6 decimal places