Genetic data in PostgreSQL

People get usually famous for the things they’ve done. Well, that’s not entirely true. They usually get famous for the things they’ve done, when they were successful. You don’t get famous for attempting and being unsuccessful, now do you?

It works the same way for the scientific publications. All scientists work hard trying various things, and when they finally succeed, they publish a paper. But what happens with all those hours spend on unsuccessful attempts? Nobody seems to be proud of blowing a whole laboratory up. Or whatever didn’t work for them. This means that other people can never learn that something was unsuccessful and they’re likely to get the same, unfeasible, idea and repeat the same research. Needless to say, unsuccessfully.

Not that I’m proud of what I’ve done here, but I will at least allow other people to find this post on Google, when searching for genetic data and relational database. I’ll describe what I did, so they at least don’t do it the way I did.

Method of comparing hospitals in the EACTS Congenital Database

I have published my MSc thesis on-line. It’s available for (free) download in PDF format. It contains:

1. An example of complicated data reshaped to a form which allows statistical analysis
2. A method of comparing hospitals fairly

Read more and download the thesis.

Tetralogy of Fallot database representation

Tetralogy of Fallot is a significant and complex congenital heart disease. It consists of four heart malformations. So if a patient is described to have TOF, it means that she/he has all those four malformations together.

However, the separate malformations are already present on the diagnoses list, as separate entities. From a data-modeling perspective, it’s a redundancy on the factors (malformations, diseases) list. This leads to problems with interpretation. As the VSD is one of TOF’s components, is already present on the diseases list, and users are allowed to enter both VSD and TOF diagnoses, there are patients with all four combinations in the database.

Malcolm Tredinnick’s SQL puzzle solution

The puzzle

Malcolm has asked, how to find the classes that were attended by all of the students from a given list. Then, he proposed a solution with a HAVING clause. I’ll call it the one-join solution. I’d like to suggest another one, which I’ll call multi-join.

I’ve made a benchmark to evaluate the execution time. A statistical tool was used to create a mathematical model of the execution time.