Databases using SQL

We use DB Browser for SQLite and the Alzheimer’s Disease Neuroimaging Initiative Teaching dataset throughout this lesson. See Setup for instructions on how to download the data, and also how to install DB Browser for SQLite.

Motivation

To start, let’s orient ourselves in our project workflow. Previously, we used Excel and OpenRefine to go from messy, human created data to cleaned, computer-readable data. Now we’re going to move to the next piece of the data workflow, using the computer to read in our data, and then use it for analysis and visualization.

What is SQL?

SQL stands for Structured Query Language. SQL allows us to interact with relational databases through queries. These queries can allow you to perform a number of actions such as: insert, update and delete information in a database.

Dataset Description

The data we will be using come from a longitudinal study of Alzheimer’s disease called the Alzheimer’s Disease Neuroimaging Initiative (ADNI). ADNI began in 2004 and includes data gathered by investigators at 59 research centers across North America. Participants in the study are older adults along the full spectrum of cognitive health, from Normal Control (NC) through Early and Late Mild Cognitive Impairment (EMCI/LMCI) and Alzheimer’s dementia (AD). The dataset includes individual-level demographics, such as age, sex, and education; measures of cognitive performance, such as scores on tests of memory and attention; and results of biomarker assessment, including genetic markers of risk, brain volumes, and amounts of AD proteins in brain and cerebrospinal fluid (CSF).

This is a real dataset that has been used in over 1700 publications. We’ve simplified it for the workshop, removing or altering some information about individual patients in the process. ADNI is a fairly open dataset, meaning that many researchers who are not part of the original team have access to the data. However, because these data are about real patients, some extra permissions are required for each new use of the data. If you’re interested in taking the work we do today further with the full dataset, you should submit a separate data request to ADNI.

Questions

First, let’s download and look at some of the cleaned spreadsheets from the ADNI Teaching dataset.
We’ll need the following files:

• biomark.csv
• cog.csv
• demog.csv

Challenge

• How do clinical diagnostic groups (DX) differ in levels of amyloid beta (ABETA_mod)?
• How much does a typical patient’s memory change between baseline and follow-up, by visit?
• How have race and gender demographics in the study looked each year since 2010?

Goals

In order to answer the questions described above, we’ll need to do the following basic data operations:

• select subsets of the data (rows and columns)
• group subsets of data
• do math and other calculations
• combine data across spreadsheets

In addition, we don’t want to do this manually! Instead of searching for the right pieces of data ourselves, or clicking between spreadsheets, or manually sorting columns, we want to make the computer do the work.

In particular, we want to use a tool where it’s easy to repeat our analysis in case our data changes. We also want to do all this searching without actually modifying our source data.

Putting our data into a relational database and using SQL will help us achieve these goals.

Definition: Relational Database

A relational database stores data in relations made up of records with fields. The relations are usually represented as tables; each record is usually shown as a row, and the fields as columns. In most cases, each record will have a unique identifier, called a key, which is stored as one of its fields. Records may also contain keys that refer to records in other tables, which enables us to combine information from two or more sources.

Databases

Why use relational databases

Using a relational database serves several purposes.

• It keeps your data separate from your analysis.
  • This means there’s no risk of accidentally changing data when you analyze it.
  • If we get new data we can just rerun the query.

  Database Management Systems

  There are a number of different database management systems for working with relational data. We’re going to use SQLite today, but basically everything we teach you will apply to the other database systems as well (e.g. MySQL, PostgreSQL, MS Access, MS SQL Server, Oracle Database and Filemaker Pro). The only things that will differ are the details of exactly how to import and export data and the details of data types.

  Relational databases

  Let’s look at a pre-existing database, the adni.sqlite file from the ADNI Teaching dataset that we downloaded during Setup. Click on the “Open Database” button, select the adni.sqlite file, and click “Open” to open the database.

  You can see the tables in the database by looking at the left hand side of the screen under Database Structure tab. Here you will see a list under “Tables.” Each item listed here corresponds to one of the csv files we were exploring earlier. To see the contents of any table, click on it, and then click the “Browse Data” tab next to the “Database Structure” tab. This will give us a view that we’re used to - just a copy of the table. Hopefully this helps to show that a database is, in some sense, just a collection of tables, where there’s some value in the tables that allows them to be connected to each other (the “related” part of “relational database”).

  The “Database Structure” tab also provides some metadata about each table. If you click on the down arrow next to a table name, you will see information about the columns, which in databases are referred to as “fields,” and their assigned data types.
  (The rows of a database table are called records.) Each field contains one variety or type of data, often numbers or text. You can see in the biomark table that most fields contain numbers (BIGINT, or big integer, and FLOAT, or floating point numbers/decimals) while the demog table is mostly made up of text fields.

  The “Execute SQL” tab is blank now - this is where we’ll be typing our queries to retrieve information from the database tables.

  • Relational databases store data in tables with fields (columns) and records (rows)
  • Data in tables has types, and all values in a field have the same type (list of data types)
  • Queries let us look up data or make calculations based on columns

  Database Design

  • Every row-column combination contains a single atomic value, i.e., not containing parts we might want to work with separately.
  • One field per type of information
  • No redundant information
    • Split into separate tables with one table per class of information
    • Needs an identifier in common between tables – shared column - to reconnect (known as a foreign key).

    Import

    Before we get started with writing our own queries, we’ll create our own database. We’ll be creating this database from the three csv files we downloaded earlier. Close the currently open database (File > Close Database) and then follow these instructions:

    1. Start a New Database
       • Click on the New Database icon or select File » New Database
       • Assign a name to the new database, choose the folder where you’d like to save it, and click Save. This creates the database in the selected folder.
    2. Choose Start the import Database -> Import
    3. We will be importing tables and not creating tables from scratch, so click Cancel to edit out of the next pop-up window.
    4. Select File > Import > Table from CSV file… Choose biomark.csv from the data folder we downloaded and click Open.
    5. Give the table a name that matches the file name or use the default.
    6. If the first row has column headings, be sure to check the box next to “Column names in first line.”
    7. Be sure the field separator and quotation options are correct. If you’re not sure which options are correct, test some of the options and until the preview at the bottom of the window looks right.
    8. Click OK
    9. Back on the Database Structure tab, you should now see the table listed. Right click on the table name and choose Modify Table, or click on the Modify Table just under the tabs and above the table.
    10. In the center panel of the windown you’ll see, set the data types for each field using the suggestions in the table below:

    Field	Data Type	Motivation	Table(s)
    RID	INTEGER	Field contains an ID coded as an integer	biomark
    PTID	TEXT	Field contains an ID coded as text	biomark
    VISCODE	TEXT	Field contains text data	biomark
    SITE	INTEGER	Field contains an ID coded as an integer	biomark
    COLPROT	TEXT	Field contains text data	biomark
    ORIGPROT	TEXT	Field contains text data	biomark
    EXAMDATE_mod	TEXT	Field contains a date coded as text	biomark
    FDG	REAL	Field contains measured numerical data	biomark
    PIB	REAL	Field contains measured numerical data	biomark
    AV45	REAL	Field contains measured numerical data	biomark
    ABETA_mod	REAL	Field contains measured numerical data	biomark
    TAU_mod	REAL	Field contains measured numerical data	biomark
    PTAU_mod	REAL	Field contains measured numerical data	biomark
    FDG_bl	REAL	Field contains measured numerical data	biomark
    PIB_bl	REAL	Field contains measured numerical data	biomark
    AV45_bl	REAL	Field contains measured numerical data	biomark
    ABETA_bl_mod	REAL	Field contains measured numerical data	biomark
    TAU_bl_mod	REAL	Field contains measured numerical data	biomark
    PTAU_bl_mod	REAL	Field contains measured numerical data	biomark

    Finally, click OK one more time to confirm the operation.

    Challenge

    • Import the cog and demog tables

    You can also use this same approach to append new fields to an existing table.

    Adding fields to existing tables

    1. Go to the “Database Structure” tab, right click on the table you’d like to add data to, and choose Modify Table, or click on the Modify Table just under the tabs and above the table.
    2. Click the Add Field button to add a new field and assign it a data type.

    Data types

    Data type	Description
    CHARACTER(n)	Character string. Fixed-length n
    VARCHAR(n) or CHARACTER VARYING(n)	Character string. Variable length. Maximum length n
    BINARY(n)	Binary string. Fixed-length n
    BOOLEAN	Stores TRUE or FALSE values
    VARBINARY(n) or BINARY VARYING(n)	Binary string. Variable length. Maximum length n
    INTEGER(p)	Integer numerical (no decimal).
    SMALLINT	Integer numerical (no decimal).
    INTEGER	Integer numerical (no decimal).
    BIGINT	Integer numerical (no decimal).
    DECIMAL(p,s)	Exact numerical, precision p, scale s.
    NUMERIC(p,s)	Exact numerical, precision p, scale s. (Same as DECIMAL)
    FLOAT(p)	Approximate numerical, mantissa precision p. A floating number in base 10 exponential notation.
    REAL	Approximate numerical
    FLOAT	Approximate numerical
    DOUBLE PRECISION	Approximate numerical
    DATE	Stores year, month, and day values
    TIME	Stores hour, minute, and second values
    TIMESTAMP	Stores year, month, day, hour, minute, and second values
    INTERVAL	Composed of a number of integer fields, representing a period of time, depending on the type of interval
    ARRAY	A set-length and ordered collection of elements
    MULTISET	A variable-length and unordered collection of elements
    XML	Stores XML data

    SQL Data Type Quick Reference

    Different databases offer different choices for the data type definition.

    The following table shows some of the common names of data types between the various database platforms:

    Data type	Access	SQLServer	Oracle	MySQL	PostgreSQL
    boolean	Yes/No	Bit	Byte	N/A	Boolean
    integer	Number (integer)	Int	Number	Int / Integer	Int / Integer
    float	Number (single)	Float / Real	Number	Float	Numeric
    currency	Currency	Money	N/A	N/A	Money
    string (fixed)	N/A	Char	Char	Char	Char
    string (variable)	Text ( <256) / Memo (65k+)	Varchar	Varchar2	Varchar	Varchar
    binary object OLE Object Memo Binary (fixed up to 8K)	Varbinary ( <8K)	Image ( <2GB) Long	Raw Blob	Text Binary	Varbinary

    Key Points

    • SQL allows us to select and group subsets of data, do math and other calculations, and combine data.
    • A relational database is made up of tables which are related to each other by shared keys.
    • Different database management systems (DBMS) use slightly different vocabulary, but they are all based on the same ideas.