In this video, we are going to overview MIMIC-III structure as a relational database. Our aim is to understand the scheme of the database, what are the basic tables and how to navigate around. Later on, we're going to see how to form SQL queries starting from descriptive statistics. For example, extraction of mortality and stay of length and finally, extraction of data related to a patient record. MIMIC-III database consists of 26 tables and they're all listed here. The schema of the database reflects the inherent hospital sources of information. Since MIMIC-III links data from a hospital, the overall structure represents closely this row data. Only small adjustments have been made to take into consideration the feedback of the users. As we see here, MIMIC-III tables can be categorized in four groups. One of the group is the patient tracking. These tables are used to define and track patient stay. The tables under ICU data include all the data recorded during icustays. On the other hand, the data recorded within the table under the hospital category, includes all the data recorded in the hospital. Remember that the database link data between ICU unit in hospital but these are two different entities. Finally, the last category includes dictionary tables and they all have a prefix of d. Here, we're going to talk in details about the basic tables of MIMIC-III, which are the patient's table, the admission table, and the icustays table. Several summary statistics that we're going to describe later can be extracted based only on these tables. The patient table has a subject Id identifier that can link it to the admission table, as well as the ICU table. The patient table includes information with relation to the date of birth. We should pay attention here because the date of birth have been shifted for patient older than 89 years old. We should also note that the table records three different versions of date of death. These are the date of death according to the hospital. The date of death from Social Security database, and a date of death which matches the two dates and gives priority to the date of death at hospitality. The patient's table also includes an expired flag, which is a binary flag that records whether the patient has died according to either database. The admissions table has an additional identifier. The hospital admission identify that links the information with the icustays. The admissions table record every unique hospitalization for each patient in the database. It includes information with relation to admission time, discharge time, death time, type of admission, hospital expiry flag, diagnosis, and whether the patient has chart events data associated with his record. Finally, the icustays table record every unique ICU stay in the database. The icustay identifier is a generated identifier that is not based on any row data identifier. We should point out that the hospital in the ICU database are not increasingly linked. They used to be two separate database. Therefore they don't have any concept of an ICU and counter identifier. Taking all this together, subject Id refers to a unique patient identifier. Hospital admission Id refers to a unique admission to the hospital and icustay identification refers to a unique admission to an intensive care unit. Information in the icustays table include the first care unit and the last care unit, which are also information defined in the transfers table. It also include the first word and the last word, which refers to the physical locations within the hospitality. It includes in time and out time of when the patient was transferred in and out of the ICU. It also includes length of stay for the patient. We should point out that the icustays table have been in fact relieved from the transfers table. Specifically it groups a transfers stable based on icustay ID and excludes rows where there is no icustay ID. The transfers table, include additional information of patient movement from bed to bed within the hospital, including ICU admission and discharge. Finally, the callout table includes information regarding when a patient was cleared from ICU discharge and when the patient was actually discharged. A key table that includes data from the ICU unit is the chart events table. Here we can find all chart event observations for patients. The outputevents stable, on the other hand, contains all measurements related to output for a given patient. When we work with ICU data and in particular with chart events data, we should consider also the dictionary tables. This table provide definitions for identifiers. For example, every row of chart events is associated with a single item ID, which represents the concept measure. In this way, by joining the chart events table with a dictionary items table it is possible to identify the concept represented by a given item ID. The rest of the dictionary's table, they're also used for cross-referencing codes against their respective definitions. Finally, here we highlight some of the tables and the hospital data that they use very often, in particular, the lab events table containing laboratory test results for a patient. There is some duplication between chart events and lab events. In cases where there is a disagreement between measurements, lab events should be taken as the ground truth. In some cases, it would have been possible to merge tables. For example, we can merge the dictionary of ICT procedures with that CPT events table because both contain details relating to procedures and they could be combined. However, since the data sources are significantly different, they have been kept separately. The researchers are advised to develop database views and transform them as appropriately rather than combining the tables within the mimic data model. We should also point out that the patients' procedures recording in the procedures ICT table are coded using the International Statistical Classification of Diseases. Similarly, the diagnosis ICT table, are hospital assigned diagnosis coded using again, the International Statistical Classification of Diseases System. The corresponding dictionary tables, they hold the relative information with relation to the ICD-9 codes. Summarizing, the mimic read database holds more than 53,000 distinct hospital admission for patients age 60 years and above, and they are admitted to critical care between 2001 and 2012. It also contains more than 7,800 neonates that spam the period of 2001 to 2008. To correctly extract information from an electronic health record database, we need to understand the schema of the database, but also the source of the data. In almost every query, we will see that we will use SQL queries to join information between the basic tables which hold data for the patients' admissions in the hospital and ICU stays. In the next few videos, we will learn how to extract information about the patient characteristics such as age, gender, ICU units, as well as outcomes such as mortality and stay of length. We are also going to discuss the coating system used in mimic, which is based on the International Classification of Diseases, ICD-9 system.
