IntroductionMIMIC2 DataRunning SQLite on the MIMIC2 DatasetUsing the DatabaseQuestions6.814/6.830 Problem Set 1 (Fall 2014) 1Problem Set 1: SQLAssigned: 9/8/2014Due: 9/15/2014 11:59 PMSubmit to the 6.830 Stellar Site (https://stellar.mit.edu/S/course/6/fa14/6.830/homework/)You may work in pairs on this problem set. Clearly indicate the name of your partner. Only one of you needs to submiton Stellar.1 IntroductionThe purpose of this assignment is to provide you with hands-on experience with the SQL programming language. SQL is adeclarative language in which you specify the data you want in terms of logical expressions that specify the properties thereturned data should satisfy but not how to actually compute the answer.We will be using the SQLite open source database, which provides a standards-compliant SQL implementation. In reality,there are slight variations between the SQL dialects of different vendors—especially with respect to advanced features, built-infunctions, and so on. The SQL tutorial at http://sqlzoo.net/, provides a good introduction to the basic features of SQL;after following this tutorial you should be able to answer most of the problems in this problem set (the last few questions maybe a bit tricky). You may also wish to refer to Chapter 5 of “Database Management Systems.” This assignment mainly focuseson querying data rather than modifying it. http://sqlzoo.net/ includes a reference section that describe how to createtables and modify records; you should read it so that you are familiar with these aspects of the language.SQLite a very easy database to use because a database in simply stored in a file. We have put a database file on athena for you;you may download this file to your local machine and run SQLite there, or log in to athena and use it. More details are given inSection 3 below.2 MIMIC2 DataIn this problem set, you will write a series of queries using the SELECT statement over an anonymized medical patientdatabase containing information about approximately 500 patients. http://mimic.physionet.org/UserGuide/UserGuide.pdf).The MIMIC2 project, led by Prof. Roger Mark of MIT, consists of detailed data on about 42,000 patient stays in the variousintensive care units (ICUs) at the Beth Israel/Deaconess Medical Center in Boston. These data include tabular data report-ing prescriptions, the results of laboratory tests, clinical orders, billing codes, minute-by-minute summary data from bedsidemonitors, narrative text that includes nursing and doctors’ notes, discharge summaries, radiology and pathology reports, andhigh-resolution waveform data on a fraction of these patients for whom such data were offloaded from the monitors and suc-cessfully matched to their clinical records. The entire database has been de-identified, so that it is (supposed to be) impossible tolink the records back to specific real individuals. One may access the entire data set by passing the COUHES training course inresponsible human subjects research and signing a limited data use agreement in which you promise not to further redistributethe data or try to contact patients if you discover flaws in our de-identification algorithms. For convenience, the project has alsoproduced a subset of data about 500 deceased patients. Under U.S. laws, dead people have vastly reduced privacy rights, so wemake these records available without having to do the training or sign the agreement. Nevertheless, we ask you not to furtherredistribute the data; others who are interested may obtain the most up to date version directly from the MIMIC project.The tables that will be used in this problem set include a meddurations, chartevents, d chartitems, d meditems,d patients, icustayevents, medevents and labevents. Patient information is stored in d patients, referredas the patient table throughout this document. As the database went through a careful de-identication process, the patient6.814/6.830 Problem Set 1 (Fall 2014) 2table only stores the patient identifier (Subject ID), gender (sex), date of birth (dob, shifted) and date of death (dod, shifted). Medication(s) given to a patient are recorded in the medevents, d meditems, a meddurations and additives(not used in this problem set) tables. Patient medical chart data is recorded in the chartevents, d chartitems, a -chartdurations and formevents tables (the latter two are not used in this problem set). The ICU (intensive care unit)stay events are recorded in the icustayevents table, in which intime and outtime denote the check-in and check-outtimes from the ICU.Example “Data Definition Language” (DDL) commands used to create d patients and icustayevents tables is asfollows:CREATE TABLE d_patients (subject_id INTEGER NOT NULL PRIMARY KEY, -- patient idsex VARCHAR(1) DEFAULT NULL, -- patient genderdob DATETIME NOT NULL, -- patient date of birthdod DATETIME DEFAULT NULL, -- patient date of deathhospital_expire_flg VARCHAR(1) DEFAULT ’N’ -- whether patient died at hospital);CREATE TABLE icustayevents (icustay_id int(11) NOT NULL PRIMARY KEY, -- ICU stay event idsubject_id int(11) NOT NULL, -- patient idintime datetime NOT NULL, -- check in timeouttime datetime NOT NULL, -- check out timelos double NOT NULL, -- length of stayfirst_careunit int(11) DEFAULT NULL, -- first care unit stayedlast_careunit int(11) DEFAULT NULL, -- last care unit stayedUNIQUE KEY icustayev_u1 (subject_id,intime),FOREIGN KEY subject_id REFERENCES d_patients(subject_id),FOREIGN KEY first_careunit REFERENCES careunits(cuid),FOREIGN KEY last_careunit REFERENCES careunits(last_careunit),KEY icustayevents_o1 (intime),KEY icustayevents_o2 (outtime))CREATE TABLE name defines a new table called name in SQL. Within each command is a list of field names and types(e.g., subject id INTEGER indicates that the table contains a field named subject id with type INTEGER). Each fielddefinition may also be followed by one or more modifiers, such as:• PRIMARY KEY: Indicates this is a part of the primary key (i.e., is a unique identifier for the row). Implies NOT NULL.• NOT NULL: Indicates that this field may not have the special value NULL.In addition, FOREIGN KEY (field) REFERENCES indicates that this field is a foreign key which references an attribute(i.e., column) in another table. Values of this field must match (join) with a value in the referenced attribute. Phrases followingthe “--” in the above table definitions are comments.Notice that the above tables reference each other