Data Management

To create a new database that you want to give the name 'new_database.sqlite', just call sqlite3 with the new database name. sqlite3 new_database.sqlite The name of that file does not have to end with .sqlite, but it helps you to remember that this is an SQLite database. If you add tables and data in that database and quit, the data will automatically be saved.

There are two types of commands that you can run within SQLite: SQL commands (the same as in any other relational database management system), and SQLite-specific commands. The latter start with a period, and do not have a semi-colon at the end, in contrast to SQL commands (see later).

Some useful commands:

If you like to use a graphical user interface (or don’t work on a linux or OSX computer), you can use the DB Browser for SQLite which you can download here.

Note: In all code snippets that follow below, the sqlite> at the front represents the sqlite prompt, and should not be typed in…​

Let’s say we want to store which students follow the G0R72A course ("Data Visualisation in Data Science"). We want to keep track of their first name, last name, student ID, and whether or not they follow the course. This should allow for some easy queries, such as listing all people who take the course, or returning the number of people who do so. In this case, a flat database would suffice; i.e. a single table can hold all information.

Consider that we want to store which students follow which courses in MSc Statistics. So we’d like to keep:

This should allow for queries e.g. to find out which people follow a particular course, the average number of courses a student takes, etc.

Let’s take the same approach as above, and we simply add a column for each course.

This way of working (called the wide format) does present some issues, though.

An alternative is to use the long format:

This solves the issue of not having to store the information when a course is not taken, decreasing the number of cells needed from 5,150 to 2,000.

This is still not ideal though, as this design still suffers from a lot of redundancy: the first name, last name and student ID are provided over and over again. Imagine that we’d keep home address (street, street number, zip code, city, country) as well, that would look like this:

What if Martin Van Deun moves from Some Street 1 in MajorCity to Another Street 42 in AnotherCity? Then we would have to edit all the rows in this table that contain this information, which almost guarantees that you will end up with inconsistencies.

To get to the first normal form:

The above table violates several of these points:

The solution to these issues is to go from a wide format to a long format: remove columns by adding rows. For example, the information for the 3 different SNPs is now stored in different rows instead of different columns. The same is true for the non-atomic values: we just duplicate the row to be able to split up the diseases. This will end up with many rows but don’t worry about that.

The new schema:

Everything is still contained in a single table, which will change when we go to the second normal form.

In the new table above, we see that there are several columns that are 1-to-1 dependent on another column. For example, if we know the individual, we know their ethnicity. If we know the SNP, we know the chromosome, position and any diseases involved. For the 2nd normal form, we extract these into separate tables. In doing this, think about the concepts that you’re trying to separate.

genotypes table:

individuals table:

snps table:

Some observations (and good practices):

By the way, we see that the first 2 rows in the genotypes table are exactly the same apart from the unique ID, and the same is true for rows 3 and 4, so we can remove one for each (e.g. the ones with ID 2 and 4).

genotypes table:

The new schema:

In the snps table above, there are two rows that are exactly the same (not taking into account the id column), if it weren’t for the disease field.

Such case indicates a one-to-many or many-to-many relationship: a single SNP can be involved in multiple diseases. Again we have duplication here: the fact that SNP rs12345 is on chromosome 1 at position 12345 is captured twice. We can solve this by extracting another table, called diseases.

Although biologically incorrect, imagine that a disease can only be linked to a single SNP. This would be a one-to-many relationship: one SNP to many diseases. In that case we could create the following tables:

snps table:

diseases table:

We have now eliminated the disease column from the snps table so end up with 2 identical rows (rows 1 and 2) and can remove one of them.

But as we just mentioned, biologically speaking a single SNP can be involved in multiple diseases and a single disease can be influenced by multiple SNPs. This is a many-to-many relationship. In this case, we can’t just add a snp_id to the diseases table anymore (or you would have to use a non-atomic field which would violate the 1st normal form). You typically create a separate link table.

snps table:

diseases table:

disease2snp table:

To come back to the one-to-many relationships…​ So how do you know in which table to create the foreign key? Should there be an individual_id in the genotypes table? Or a genotype_id in the individuals table? That all depends on the type of relationship between two tables. This type can be:

One-to-many is obviously the same as many-to-one but looking at it from the other direction…​

When you have a one-to-one relationship, you can actually merge that information into the same table so in the end you won’t even need a foreign key. In the book example mentioned above, you’d just add the ISBN number to the books table.

When you have a one-to-many relationship, you’d add the foreign key to the "many" table. In the example below a single company will have many employees, so you add the foreign key in the employees table.

The companies table:

The employees table:

When you have a many-to-many relationship you’d typically extract that information into a new table. For the books/authors example, you’d have a single table for the books, a single table for the authors, and a separate table that links the two together. That "linking" table can also contain information that is specific for that relationship, but it does not have to. An example is the genotypes table above. There are many SNPs for a single individual, and a single SNP is measured for many individuals. That’s why we created a separate table called genotypes, which in this case has additional columns that denote the value for a single individual for a single SNP. For the books/authors example, this would be:

The books table:

The authors table:

The author2book table:

The information in these tables says that:

There are several ways to load data into a database. The method used above is the most straightforward but inadequate if you have to load a large amount of data.

It’s basically:

But this becomes an issue if you have to load 1,000s of records. Luckily, it’s possible to load data from a comma-separated file straight into a table. Suppose you want to load 3 more individuals, but don’t want to type the insert commands straight into the sql prompt. Create a file (e.g. called data.csv) that looks like this:

Using the DB Browser, you can just go to File → Import → Table from CSV File…​. Note that when you import a file like that, the system will automatically create the rowid column that will serve as the primary key.

SQLite contains a .import command to load this type of data. Syntax: .import <file> <table>. So you could issue:

Aargh…​ We get an error!

Error: data.tsv line 1: expected 3 columns of data but found 2

This is because the table contains an ID column that is used as primary key and that increments automatically. Unfortunately, SQLite cannot work around this issue automatically. One option is to add the new IDs to the text file and import that new file. But we don’t want that, because it screws with some internal counters (SQLite keeps a counter whenever it autoincrements a column, but this counter is not adjusted if you hardwire the ID). A possible workaround is to create a temporary table (e.g. individuals_tmp) without the id column, import the data in that table, and then copy the data from that temporary table to the real individuals.

Your individuals table should now look like this (using SELECT * FROM individuals;):

Why do we need queries? Because natural languages (e.g. English) are too vague: with complex questions, it can be hard to verify that the question was interpreted correctly, and that the answer we received is truly correct. The Structured Query Language (SQL) is a standardised system so that users and developers can learn one method that works on (almost) any system.

In order to write your queries, you’ll need to know what the database looks like. A relationship diagram including tables, columns and relations is very helpful here. See for example this relationship diagram for a pet store.

Questions that we can ask the database include:

Similarly, we already drew the relationship diagram for the genotypes.

Questions that we can ask:

Often you will want to export the output you get from an SQL-query to a file (e.g. CSV) on your operating system so that you can use that data for external analysis in R or for visualisation. This is easy to do. Suppose that we want to export the first 5 lines of the snps table into a file called 5_snps.csv.

Imagine that we’ve been storing the information on our individuals as above, but have not been consistent in capitalising the ethnicity. In some cases, a person can be of asian descent; in other cases he or she can be Asian. The same would go for the other ethnicities. To clean this up, let’s put everything in lower case. For argument’s sake we’ll only look at Asian here. First let’s check what we should get with a SELECT.

This will give us the rows that we will change. Are these indeed the ones? Then go forward with the update:

The WHERE clause is the same. The general syntax for an update looks like this:

In this example the column that is updated (ethnicity) is the same as the one in the WHERE clause. This does not have to be the case. What would the following do?

DELETE is similar to UPDATE but simpler: you don’t use the SET pragma. Same as with updating data, make sure that your WHERE clause is correct! Test this with a SELECT beforehand.

The general syntax:

For example:

If you only want to get the first 10 results back (e.g. to find out if your complicated query does what it should do without running the whole actual query), use LIMIT:

SNPs are spread across a chromosome, and might or might not be located within a gene.

What if you want to search for the SNPs that are not in genes? Imagine that our snps table has an additional column with the gene name, like this:

We cannot SELECT * FROM snps WHERE gene = ""; because that is searching for an empty string which is not the same as a missing value. To get to rs92873 you can issue SELECT * FROM snps WHERE gene IS NULL; or to get the rest SELECT * FROM snps WHERE GENE IS NOT NULL;. Note that it is IS NULL and not = NULL…​

Your queries might need to combine different conditions, as we’ve already seen above:

The result is affected by the order of the operations. Parentheses indicate that an operation should be performed first. Without parentheses, operations are performed left-to-right.

For example, if a = 3, b = -1 and c = 2, then:

De Morgan’s laws apply to SQL. The rules allow the expression of conjunctions and disjunctions purely in terms of each other via negation. For example:

The IN clause defines a set of values. It is a shortcut to combine several entries with an OR condition.

For example, instead of writing

you can use

Whenever you want the unique values in a column: use DISTINCT in the SELECT clause:

DISTINCT automatically sorts the results.

The order by clause allows you to, well, order your output. By default, this is in ascending order. To order from large to small, you can add the DESC tag. It is possible to order by multiple columns, for example first by chromosome and then by position:

For when you want to count things:

…​act as you would expect (only works with numbers, obviously):

Output is:

In some cases you might want to rename the output column name. For instance, in the example above you might want to have maximum_position instead of max(position). The AS keyword can help us with that.

GROUP BY can be very useful in that it first aggregates data. It is often used together with COUNT, MAX, MIN or AVG:

Whereas the WHERE clause puts conditions on certain columns, the HAVING clause puts these on groups created by GROUP BY.

For example, given the following snps table:

will return

whereas

will return

The HAVING clause must follow a GROUP BY, and precede a possible ORDER BY.

It is sometimes hard to get the exact rows back that you need using the WHERE clause. In such cases, it might be possible to construct the output based on taking the union or intersection of two or more different queries:

Sometimes you want to make fuzzy matches. What if you’re not sure if the ethnicity has a capital or not?

returns no results…​

Note that different databases use different characters as wildcard. For example: % is a wildcard for MS SQL Server representing any string, and * is the corresponding wildcard character used in MS Access. Check the documentation for the RDBMS that you’re using (sqlite, MySQL/MariaDB, MS SQL Server, MS Access, Oracle, …​) for specifics.

As we mentioned in the beginning, the general setup of a SELECT is:

But as you’ve seen in the examples above, the output from any SQL query is itself basically a table. So we can actually use that output table to run another SELECT. For example:

Of course, you can use UNION and INTERSECT in the subquery as well…​

Another example:

In some cases, you want to violate the 1st normal form, and have different columns represent the same type of data. A typical example is when you want to analyze your data in R using a dataframe. Let’s say we have expression values for different genes in different individuals. Being good programmers, we saved this data in the database like this:

In R, you will however probably want a dataframe that looks like this:

This is called a pivot table, and there are several ways to create these in SQLite. The method presented here is taken from http://bduggan.github.io/virtual-pivot-tables-opensqlcamp2009-talk/. To create such table (and store it in a view), you have to use group_concat and group_by: