Site search   
  Home    Publications

Browse Publications
List of Contents

Quantitative Neurogenetics & QTL Mapping

Genetics of Myopia

Control of Neuron Number and Stereology

Growth Cones and Dying Axons

Retina Development and Visual System Mutants

Grant Application

U.S. Patent


Need Help?
Help with Publications
Help with Nervenet
Contact Us

Note to the Reader Society for Neuroscience. Neuroscience 2002. Presented as a symposium in Bioinformatics 2002 A Neuroscientist's Guide to Tools and Techniques for Mining and Refining Massive Data Sets. Organized by Robert W. Williams, PhD. and Dan Goldowitz, PhD.

Print Friendly
Everyday bioinformatics for neuroscientists: From maps to microarrays

Robert W. Williams
Center for Neuroscience, Department of Anatomy and Neurobiology, University of Tennessee, Memphis, Tennessee 38163

Email questions and comments to


This chapter covers two topics:

First, I would like to introduce (or reintroduce) you to some of the key features of maps of the mouse and human genomes. These maps have become an important structural substrate around which many types of biological information is now being assembled. Those of you who have linked recently to the NCBI, ENSEMBL, and USCS Genome Browser sites for human and mouse genomes will have encountered the complex graphic conventions and acronyms that are used to display different types of genetic information. The progress in the last year has been astonishing and will have an impact on research that is carried out in most laboratories that have a molecular or genetic research angle.

My second aim is to summarize some of the basic informatics and computational tools and tricks used to manage large and small data sets. The scale of this work can range from a modest stereological analysis of cell populations in a few dozen cases, to large microarray databases, through to huge image data sets (see the chapters by P. Thompson and M. Martone). Most of us now use spreadsheets such as Excel in some capacity to manage lab data. I hope to show you a few useful tricks for managing large Excel spreadsheets. But I mainly hope to convince/encourage you that it is easy and worthwhile to extend beyond disjointed sets of Excel spreadsheets and to become comfortable, even proficient, using a simple relational database such as FileMaker or Microsoft Access. Over the past few years our group has become completely dependent on relational databases. Databases have replaced notebooks and spreadsheets for most lab work and even for some primary data analysis. The improvement in lab data handling has been amazing and initially unrelated files and data set can often be easily merged. Best of all, our lab data are now accessible using an Internet connection from any computer in the lab or across the world. Internet databases are obviously far easier to replicate, archive, and distribute than raw data stuck in a notebook.

Topic 1: Physical and Genetic Maps

Maps come in two major flavors: physical and genetic. Physical is an odd word in this context, but signifies that the map is based on sequence data and on an assemblage of YACs, BACs, and other clonable pieces of chromosomes that have been ordered into a contiguous stretch of DNA, preferably without any interruptions or ambiguities. The  NCBI site at NCBIContigInfo.html has a fine description about how the mouse physical maps (genome sequence) are being assembled. Quality of these physical maps is now vastly improved over the situation even one year ago and the progress will continue for several more years.
The unit of measure of a physical map is generally a base pair or nucleotide (bp or nt). In humans, by convention the 0 bp position is at the telomeric tip of the short P arm of each chromosome (usually illustrated at the tops of most figures) and the end is at the tip of the long Q arm. Murine chromosomes have extremely short P arms (all chromosomes are acrocentric), and the 0 bp position is within a few megabases (Mb) of the centromere. A typical chromosome in human or mouse is between 60 and 200 Mb long.
The great majority of genes have now been physically mapped in several key species (although sometimes they go unrecognized for a while), and the phrase gene mapping is beginning to loose its original meaning. The focus now is turning away from mapping genes to mapping phenotypes across sets of chromosomes and genes. By mapping a phenotype, I actually mean finding the set of polymorphic genes (genes with multiple alleles) that modulate some trait, for instance numbers of tyrosine hydroxlyase-positive neurons in the substantia nigra or risk of developing Alzheimer disease. I’ll come back to this topic.
In contrast, genetic maps are based on a somewhat more abstract analysis of the frequency of recombination events that occur during meiosis along paired-up sister chromosomes. The greater the distance in base pairs or centimorgans between two points of a single chromosome, the more likely that a recombination will occur between those two points to break up the original arrangement of genes on the parental chromosome. That original order is called the parental haplotype and the order of the recombination is called the recombinant haplotype.
Until a few years ago, all genetic maps were constructed by computing the frequency of recombination between genes and markers on chromosomes. The use of the term genetic in this context seems inappropriate or superfluous, but the idea was to minimize confusion: genetic maps are sometimes referred to as meiotic maps, linkage maps, recombination maps, or haplotype maps (that really helps to minimize confusion!), and the standard unit of measure of a genetic map is the centimorgan (cM, defined below).
Chromosomes usually measure from 50 to 300 cM.

Box 1: Markers, SNPs, Microsatellites, QTLs, etc.

In dealing with maps of various types you will need to know how some key vocabulary is used by most geneticists: What is a marker, a microsatellite, a SNP, a polygene, a locus, and QTL? A marker is often a non-functional but polymorphic stretch of DNA, for example a short microsatellites or a single nucleotide polymorphisms (a SNP). Microsatellites (a term that derives from hybridization characteristics) is highly repetitive DNA sequence that tends to be highly polymorphic because polymerase has a very hard time replicating this boring DNA accurately. For example the sequence CAGCAGCAGCAGCAG. CAG is a tri-nucleotide microsatellite repeat that in the right reading frame will translate into a string of glutamine residues. If a microsatellite is in an exon, and if the number of repeats is abnormally large, then bad things can happen to neurons: Huntington disease is an extreme example. Markers, whether SNPs or microsatellites, will always be useful for efficiently screening the structure of genomes and the inheritance of blocks of DNA and blocks of genes. Markers that happen to be within genes, such as the key microsatellite in the huntingtin gene are of course interesting in their own right, especially when they correspond to resistance or susceptibility to a disease.

A polygene is an odd term that refers to the set of polymorphic genes that collectively control the variation in a trait. For example, the BRCA1 and BRCA2 genes are part of a cancer susceptibility polygene. Usually, we do not know the membership of a polygene; we just know that a small or large number of scattered genes modulate some trait. Finally, what is a locus? This is a term used to hedge bets. We would like to call everything a gene, but many times we only know the approximate chromosomal position that appears to contain factors that modulate variation in a trait. This chromosomal region may contain a single causative gene or it may contain a cluster of genes that collectively modulate a trait. The safe term is locus. A QTL is a so-called quantitative trait locus. That translates as follows: a chromosomal region that harbors one or more polymorphic genes that influence the variation in a trait in a graded (quantitative) manner. QTLs are relatives of the modifier loci that one sometimes hears about in the context of major disease genes and knockouts. A modifier locus is usually a QTL that modulates the severity of a phenotype.
The frequency of recombination is variable and depends on the chromosome, the species or strains, and the sex. Genetic maps are elastic. Genes and markers on genetic maps have the same order, but distances vary among experiments and populations. A useful metaphor: Genetic maps are similar to maps that measure the separation between cities in terms of the standard driving times required to get from one to another. Those times will be very contingent. In contrast, a physical map is structural and not subject to much change.
Let’s look at the type of maps that are now available on line at NCBI. Reading from the left side of this figure 2, we first see a cytogenetic ideogram of the smallest of the mice chromosomes, Chr 19. The Zoom is at its lowest setting and the lines and columns to the right side cover most of Chr 19. The left-most line is the approximate distance in millions of base pairs (M in the figure) from the tip of the chromosome; the right-most line is the genetic map measured in centimorgans. Again, there is no single genetic map, but many alternatives, and the alternative that is displayed by NCBI is the Mouse Genome Informatics group’s consensus map. There are lots of acronyms of these and other maps (FPC for finger print clone), and you can find out what each line or trace means by clicking on the column headers.
Will genetic maps fade out into the history of science as physical maps get better and better? Absolutely not. The simple reason is that when we try to discover the genetic basis of differences in phenotype, we almost invariable rely on recombination events to test the likelihood that a sequence variant is associated with a variant phenotype. (Cytogenetic abnormalities are an important exception.) Most of the discoveries of genes and gene polymorphisms (alleles) associated with diseases rely on probabilistic recombination events—either the historical recombinations between populations or the more recent recombination that are unique to large and small families. Even if we could snap our fingers and sequence the entire genome of every one on earth, we would still end up tracking the sites of recombinations and their relations with variation in phenotypes.
Mapping Genes

Almost any sequence of nucleotides from Drosophila, C. elegans, human, and mouse can now be physically mapped using BLAT or BLAST to the nearest base pair in a matter of seconds (see Effective Mining of Information in Sequence Databases, by David Deitcher in this Short Course). Jim Kent’s BLAT program illustrated below is a remarkable web tool that works well for mouse and human sequence. Paste any nucleotide or peptide sequence into BLAT at and within 1–2 seconds you will be rewarded with a list of hits. This new resource made it possible in collaboration with John Hogenesch and colleagues at the Genome Institute of the Novartis Foundation, to locate the base pair position of almost all the GenBank entries used to make the Affymetrix U74Av2 GeneChip (Fig. 5).
If you simply need to explore a genome location to view sequence, intron-exon structure, fish out promoter motifs, then just enter a key word and the BLAT search will deliver you to a particular part of the genome. In the figure below, I entered a search for HOXB8. Then zoomed-out to get a view of the entire human HOXB complex on Chr.

Figure 1. Physical and genetic maps of mouse chromosome 19 from the National Center for Bioinformatics. This figure can be expanded to reveal fine details and sequence from almost any region.

If you visit this impressive web site you can get complete descriptions of the various traces that are essentially graphical annotations and summaries of the human and mouse genomes. You can zoom into the level of the nucleotide sequence.

Mapping Brain and Behavioral Traits

Mapping phenotypes is a much more difficult task these days than locating an arbitrary gene sequence. When we talk about mapping a gene that influences circadian rhythm, neuron number, anxiety, susceptibility to Parkinson disease, alcoholism, or schizophrenia, we are really talking about matching differences in structure or function to one or more chromosomal regions; so-called gene loci (see BOX 1). We would love to map genes for Parkinson disease, but what that usually means is that we would like to identify statistically significant association between variability in susceptibility to Parkinson disease with a genetic polymorphism (variation) that may be distributed widely across the genome. In other words, we are mapping a phenotype to multiple regions of the genome. This is the crux of forward genetics. If the process of mapping these traits intrigues you, then link to a previous Short Course tutorial on forward genetic methods at
This Short Course contains much additional information on procedures for mapping traits and genes.
Chromosome maps have a complex and heterogeneous structure. This is visible at the cytogenetic level as differences in banding patterns and at a finer grain as large fluctuations in mean gene density. The haphazard way in which chromosomes differ between even fairly closely related species demonstrates abundant chromosomal plasticity. However, gene location, order, and orientation can also be important as highlighted by the conservation of the HOXB gene families illustrated in figure 2 from the extremely useful Genome Brower web site at the University of California Santa Cruz.

Figure 2. Detailed view of the human HOXB complex on chromosome 17 taken from the University of California Santa Cruz Genome Browser.
Topic 2: Excel and relational databases

Bioinformatics is closely associated with genomics and the analysis of sequence data and maps (see Box 2), but in this section I would like to broaden that definition to include handling information that is typically generated and processed in laboratories every day. In the biotech and pharmaceutical industry work of this type is handled by a LIM system (a laboratory information management system). This type of everyday “bioinformatics” often starts with simple decisions about unique case Ids and identifiers to be used in experiments, extends through to the organization, use, and security of lab notebooks, and often ends with the extraction, analysis and archiving of data and experiments with spreadsheets and statistical programs. Most of this type of information handling is taken for granted and many of us (and especially our mentors) assume that there is not much room for modification or improvement in the daily cycle of data generation, analysis, and publication.
In fact, the efficiency and sophistication of the day-to-day aspects of data acquisition and handling can be substantially improved. It is becoming more important to have a lab database and a web site for more that just a curriculum vitae and a set of pdf files. Lab web sites are becoming one of the most effective ways to communicate results. provides a good example of how our lab publishes data on-line.
In this section I will make some suggestions about how to move in the direction of using relational databases to improve lab informatics. The expense of entering this new sphere is modest and the gain in scientific efficiency can be substantial. Best of all, these new tools make collaborative research across cities and continents much more practical.

Excel: uses and abuses

Exposure to practical lab bioinformatics often starts with Excel. Excel has become a pervasive (almost obligatory) vehicle for data email exchange. It is also a very powerful tool for analysis. Not many of us have read or reread the Excel manual: we usually learn on the fly. Let me summarize some of the key features:

1. File size. Excel has a limit of 65,536 rows and 256 columns. That is usually not a problem. Our lab still uses Excel for some aspects of microarray analysis. You can easily pack 12,500 rows and 240 columns worth of data into an Excel file and you can have multiple spreadsheets in a single file. We have several Excel files that are about 120 MB in size, and the program runs reliably if given 300 MB of memory. However, for all but the smallest projects, it is not a good idea to store data files long term in Excel. You will hear more about this in the next section. In brief, Excel is for analysis—not for archiving and databasing. Running up against the table size limits of Excel is not hard these days. If you begin to work with even a single Affymetrix GeneChip at the cell level (about 500,000 cells/chip) then you will have to use another software tool (SAS, Systat, SPSS, S-Plus, Matlab, DataDesk, FileMaker, MySQL, PosgreSQL, etc). More on this later in the section on Relational Databases.

2. Transposing data. It is easy to transpose a data set in Excel (that is, switch rows to columns and columns to rows). Select the region of interest and copy it. Then select the upper left cell of the destination for the transposed data and use the Paste Special command. There is a check box labeled “transpose.” Paste Special is a very useful feature that we use extensively to convert equations to values. This can reduce RAM requirements and speed execution. Keep equations if you need them permanently for updating. But if you just want the values, convert equations to values using Paste Special. You can also transpose values and leave formats alone.

Figure 3. Using lookup functions in Excel to exchange selected data from between files. Details of this method are described in the text. Note the equation at the top of this figure. This equation places new data from the Neocortex database (left side) into the Neocortex column of the Caudate database (right side)

3. Merging complex tables that share a unique field is easy to do in Excel. Let’s say you have an Excel table consisting of 6,000 gene transcripts expressed in the caudate nucleus. You have another list of 12,000 gene transcripts with data on neocortex expression sent to you by a colleague. You want to extract the neocortical values and align them with the set of 6000 caudate transcript values. The problem is that the tables do not overlap perfectly. The solution is simple. If the two tables share a common field type, for example an Affymetrix ID number or a GenBank accession number, you are in business. Just use the vertical lookup command as shown in Fig. 3. Excel will help explain the use, but here is my version of help: open both files, then add a new column in the Caudate Table labeled Neocortex. Type in a variant of the equation that is listed toward the top of the next figure. These equations have the form: =VLOOKUP(CellID, LookupTable, Offset, FALSE)
.The CellID (A2 in the example below) is the spreadsheet cell that contains the unique ID that both tables share (the Probe set ID 92996_at in this example). The Neocortex table is just a region that will be interrogated by Excel to find the single matching row in the Neocortex table (row 2773 in the figure); the Offset is an integer that instructs Excel to copy data from the Nth column to the right of the ID column. In this case, the offset is 3. FALSE is a flag that instructs Excel to use only perfect matches. Make sure that this equation works for the first few cells in your new column and then copy the formula down the whole column. You may need to put dollar signs in front of some cell references to lock the reference in place so that the definition of the table does not change as you copy down the column.

4. Excel as a statistical analysis program. Simple statistics (mean, median, average, errors) can be computed quickly for thousands of rows or columns of data in Excel. This is an ideal use of Excel. It is also possible to perform tens of thousands of t tests in Excel in less than a minute. If you have ten arrays worth of data (5 wildtype and 5 knockout array data sets), then you can perform a quick t test for every transcript using the formula:
=TTEST(WT1:WT5; KO1:KO5, 2, 3)
WT1:WT5 is the range of the wildtype data in a single row (five columns worth of values. KO1:KO5 is the same thing for knockout samples. The parameter 2 instructs Excel to compute the 2-tailed probability. The final parameter 3 instructs Excel to assume that the variance of the two groups is not equal. Excel will return the probability of the t test rather than the t value. If you have done any array work you will already be familiar with the multiple tests problem (see the chapter in this Short Course by Dan Geschwind and colleagues). An array consisting of 10,000 transcripts should generate about 500 false positive results with alpha probabilities of less than 0.05; 100 with P <.01; and 10 with a P <.001, etc. If you plot the P values against their rank order (rank on the x axis from lowest to highest P values, and the actual P value or log of the P value on the Y axis), then you will end up with an interesting plot that can be helpful to estimate how many false discoveries you are making at any given P value. (For more on the Benjamini and Hochberg method of false discovery rates see ).
It is not a good idea to use Excel in place of sophisticated statistics programs. If you are gearing up for regression analysis, ANOVA, non-parametric statistics, factor analysis, principal component analysis, then buy one of the many good statistics packages. SAS, SPSS, StatView, Matlab, and DataDesk are powerful tools. DataDesk in particular is an amazing program that makes working with very large data sets more like a game than a chore. We routinely review all of our array data with DataDesk and use this program to generate draft figures for papers. If you buy this inexpensive program be sure to work through the excellent manual. Ample rewards.

5. Excel to normalize array data sets. This is a good use for Excel. Excel can compute rank orders: =RANK(TEST_CELL, ALL_CELLS); compute the logarithm base 2: =LOG(VALUE, 2); and compute the Z-score for a cell: =STANDARIZE(VALUE,AVERAGE, STDEV). In many of these formulas you will need to lock one cell reference so that values do not change when you copy or fill. Use the dollar sign to lock a reference in a formula, for example if the cell that contains the average is C12450, then enter it as C$12450. If you copy down the column then the reference to the average will not change. If you copy to the right however, then the reference may change to D$12450, since the column letter was not locked. To lock both use the format $C$12450.

6. Using Excel as a database program. Don’t bother. Excel is great, but it is definitely not a database program. If you have played with the database functions that are built into Excel then you have all of the experience and motivation that you need to graduate to one of several much better, more powerful, and easier to use database

Figure 4. Internet access to over 50 laboratory databases hosted on an inexpensive but robust lab computer: a Macintosh G4 running OS X and FileMaker server. The top panel is a partal listing of some of the related databases, including CageDB (animal colony), CelloidinDB (histology), EyeDB (eye phenotypes), DNADB (sample preparation), F2DB (genotypes), etc.
programs. FileMaker Pro and Access are programs with which you can get comfortable in a few days. Read the next section for details on the migration to relational databases.

Moving beyond Excel: Relational databases
A bioinformatic imbalance. We often do a great job handling the hard problems in neuroscience and bioinformatics but often neglect to take care of the simple housekeeping. This imbalance can lead to serious problems. Imagine a sophisticated research lab performing hundreds of microarray experiments and generating and processing megabytes of data every day. Such a lab will almost invariably have expensive bioinformatics tools (GeneSpring, SpotFire, etc.) and computer systems for handling array data. But the same lab may not have a simple database to track the large number of tissue and RNA samples that are stored in several freezers. In order to confirm the sex and age of all of the cases in the array data base they may have to rummage through a set of lab note books and Excel spreadsheets. To determine the size of the litter to which a particular animal belonged may involve the laborious analysis of animal cage cards kept in a shoebox in the animal colony. It may not be practical to determine even after an interval of a few months which of several investigators, students, or technicians extracted the RNA; did they use Trizol or RNAStat?
These examples highlight a problem in the typical application of bioinformatics. We tend to think of bioinformatics as high level analysis that is applied at the final stages of preparing papers for publication. The bioinformatic tools enter ex machina to the rescue. Most of us run microarrays and then learn how to apply sophisticated statistical methods to parse and interpret patterns of gene expression change. Bioinformatics should actually be built into a laboratory from the ground up. Data should ideally flow from one stage and level to the next without the need to transcribe or reformat. Below is one example that describes how to accomplish this transformation in your laboratory information management.

The limits of spreadsheets. In 1994 we began a series of experiments with the aim of estimating the population of retinal ganglion cell axons in the optic nerves of several hundred (now over a thousand) mice. For each optic nerve we typically counted 25 electron micrographs and entered the counts per micrographs and per case in a single row of an Excel spreadsheet. We calculated means and standard errors for each nerve and row of data. There seems to be no significant downside to this simple system.
There were a few minor problems that in aggregate became serious and that illustrated the inadequacy of using Excel as a research database. How does one handle right and left optic nerves when both sides are counted? That seems simple; just enter the two sides in separate rows. The consequence is that some animals were represented on two rows, whereas the majority are represented on one row.
A second problem was that every time we added data for a particular strain we had to rewrite some of the Excel formulas used to compute strain averages. It became awkward to maintain both individual data and strain averages in a single spreadsheet.
A third problem was keeping track of the latest version of the spreadsheet. As many as three investigators were working on the spreadsheet each day, and it was difficult to track versions and to make sure that information was accumulated and collated correctly. This was a pain to do especially after the Excel file grew to a large size.
A fourth problem involved the integration of other data types into the spreadsheet. When we were writing up our results it became obvious that we would need to consider variables such as brain weight, age, sex, body weight, and litter size as potential modulators of retinal ganglion cell axon number. Unfortunately, these data types were scattered in several other databases. We diligently transcribed data from cage cards and other small Excel databases and lab notebooks into our optic nerve spreadsheet. This transcription was associated with the introduction of many transcription errors and every new case that we added required us to transcribe data from 2–4 other notebooks.

Box 2: Good reading and reference.

Biological sequence analysis: probabilistic models of proteins and nucleic acids (1998) by R Durbin, SR Eddy, A Krogh, G. Mitchison. $35. The standard text on sequence analysis; the core topic of bioinformatics. You can take a tour of the first 23 pages of this book at
Bioinformatics, a practical guide to the analysis of genes and proteins 2nd ed. (2001) edited by AD Baxevanis, BF Francis Ouellette. $70. Provides an overview of common resources and an introduction to Perl. The main drawback is that practical web-based bioinformatics is moving so quickly that revisions are needed quarterly. A careful reading of NCBI on-line documentation will cover much of the same ground. But if you need hardcopy for bedtime reading...
Bioinformatics, the machine learning approach 2nd ed. (2001) by P Baldi, S Brunak. $50. A more conceptual companion to the Practical Guide. Most of the reviews are favorable, but I have to agree that the coverage of topics I know best (array analysis) is of uneven quality. Chapter 13 includes an armada of web resources for molecular bioinformatics that is still useful.
Biometry, 3rd ed. (2001) by RR Sokal, FJ Rohlf. $96. This is one of the best first courses you can take in statistics. Full of fine examples. Were you aware that the standard deviation is a biased estimate and is usually too low (p. 53)? This book does not have statistical tables.
Data reduction and error analysis for the physical sciences. 2nd ed. (1992) by PR Bevington, DK Robinson. $50. Predates bioinformatics but if you want an absolutely lucid presentation of the foundations of data analysis with lots of practical advice and code snippets this is the right Short Course. Includes some of the statistical tables missing from Biometry.
Applied Multivariate Statistical Analysis, 5th ed (1998) RA Johnson, DW Wichern. $105. This volume is a classic but rigorous coverage (“more equation than words”) that covers the mind-bending world of multivariate analysis. SK Kachigan wrote a much more accessible and shorter text: ($30, Multivariate Statistical Analysis: A Conceptual Introduction). LG Grimm and R Yarnold assembled a collection of solid and accessible chapters in Reading and Understanding Multivariate Statistics ($21) that gets strong reviews on Amazon.
Fundamentals of database systems 3rd ed (200) by R Elmasri, SB Navathe. $70. A thorough textbook that will introduce you to the theory and practice of implementing database systems.

The solution.
The solution was obvious but seemed both risky and impractical: convert our entire laboratory to a relational database management system and begin to enter and reenter all data into a set of interconnected database files or tables. The idea was to eliminate laboratory notebooks and spreadsheets as much as possible. The process began in the animal colony and extended through to post publication databases that are now on-line.
What is a relation? The key feature of a relational database is that it consists of an often large number of small tables of data that are linked using key ID fields (for example the Probe_set_ID field in the previous example).
Instead of trying to cram all data types into a single unwieldy table (the Excel model), the idea is to parse data into more manageable and logical pieces. The structure or scheme of a whole lab database system is then defined in large part by how information flows between and among the various tables. In the context of an animal colony, rather than having a single complex ColonyDB table, it is related tables: CageDB, a RackDB, an AnimalDB, more effective to break up the data types into four smaller LitterDB. These four tables would all be linked by relations and key fields. For example, each cage in the CageDB has a Rack_ID. The relation provides a conduit for information flow and display. A very important idea in relational databases design is to minimize redundant data among the related tables. Ideally, all data only are entered into the single most appropriate table. You do not want to have to enter the sex and age of an animal more than once. A perhaps counterintuitive example: birth data would typically be entered into the LitterDB, not the AnimalDB. The AnimalDB would inherit the date of birth data by following the relational trail between a specific animal and the litter to which it belongs. Minimizing data redundancy actually improves the data integrity of the system. You won’t end up with animals that have two or more different dates of birth. The organization of your database and how you view and work with the data are two separate issues. Don’t confuse the underlying database structure with the database interface. For example, the form illustrated in Fig 5, actually displays data from four different tables and makes use of relations that rely on the Probe Set ID, the Gene Symbol, the Locus Link ID, and the GenBank accession number. The layout of the form can be changed in a matter of seconds to simplify data entry or viewing. Once the right relations have been made it is also simple to compute new values and new field types based on data in a multitude of different tables. You can export and print data from any and all of the tables, and you can compute new data types across the tables.

Figure 5. Example of a one-to-many relation being used to track and analyze microarray data. The primary database table contains 12,422 records, each of which corresponds to a unique Probe Set ID (94733_at in this case). Each probe set, in turn, relates to 16 perfect matches held as individual records in a second lower-level database table-- the Probe Sequences that are shown in the lower panel. Selecting the Link to Ensembl button (right side) opens a window on the mouse sequence web site. Apparently complex databases of this type are simple to make using FileMaker.

Choosing a database is a important issue since you will probably have to live with, manage, and pay for occasional upgrades of software for a long time. The choice is not irrevocable, but migrating from one database to another can take months. Even a “simple” upgrade can take weeks.
We considered and experimented with a few alternative relational database programs, including Microsoft’s Access, FileMaker Pro, Helix, Acius’s 4D, and Panorama. FileMaker was our final choice because of the ease of implementing complex and visually self-explanatory tables and relations. It lacks many sophisticated features expected on enterprise products like Oracle 9, but that is not what we needed. FileMaker now has strong support for Macintosh, Windows, and Linux platforms. Upgrades have kept pace with technology without sacrificing ease of implementation. The interface with Excel is also smooth, making FileMaker an easy upgrade to a relational database system.
FileMaker vs. MySQL. We have compared the efficiency of implementing database systems in FileMaker and a free and powerful relational database called MySQL (Fig. 6). The Mouse Brain Library ( was originally implemented as a FileMaker database in just under two weeks by a high school senior with strong programming skills. This web-accessible database has performed admirably for several years with almost no unintentional downtime and now accommodates a wide variety of images for approximately 3000 histological slides and over 200 strains of mice. The Internet interface was not difficult to implement in FileMaker and allows rapid searches by genotype for acquisition of images.

Figure 6.
Internet implementations of the Mouse Brain Library ( using FileMaker or MySQL The MBL in concert with the iScope, and a collection of C++ and CGI-like web interface programs deliver images that range in resolution from whole slides (top), down to ~0.2 microns per pixel per slide. The iScope is an Internet-driven microscope that can deliver Z-axis image stacks in color and at sizes up to 1280x960 pixels. These stacks are suitable for high-resolution on-line high-resolution stereology.
Once we had built and full tested the FileMaker version we then decided to replicate the entire system using a free and powerful relational database called MySQL on a Linux platform. This free implementation took a skilled database programmer just over 3 months. That is not atypical for MySQL. However, replicating the MySQL implementation from one site to another site took less than a week. The moral is that if you want to maximize efficiency of time and ease of implementation then use a database system that has a strong and logical interface and high-level graphical interface tools. In contrast, if you want to provide a free system for use by a broader community then either convert to MySQL or PostgreSQL (both open source databases that run on most major operating systems: see and If speed is a major consideration (lots of array files), then MySQL is now a faster database management system than PostgreSQL or FileMaker. For a cogent comparison of these DBMS see

A precaution:
There is a certain macho urge to use the most robust heavy-iron commercial program you can get your hands on as part of a laboratory database system. Oracle, Sybase, and similar high-end systems are intended primarily for mission-critical 24:7 activity (student records, payroll, etc.). Experts on databases generally know these systems well, and they genuinely think they are being helpful by recommending Oracle with its sophisticated transactional processing. But Oracle and Sybase are a mismatch for a typical laboratory. Research and lab databases need to change on a weekly basis. The layout of fields for data entry may change on a daily basis. Local control, speed, and mobility are far more important than processing speed or high level feature sets. Don’t go hunting with a tank. You need to know how to make changes to the structure of your tables, in the layout of your entry forms, and how to efficiently export data for downstream statistical analysis. A strong point in favor of Excel is its transparency, and you don’t want to lose that advantage when moving to a relational database. You need to retain full control of your own data.
Security. Backing up and making weekly permanent archives are both critical. The difference between a backup and an archive is that the backup is volatile on a daily, weekly, or monthly basis and will be overwritten at some point. In contrast, archives are intended to be as permanent as possible. Even the simple systems such as FileMaker Server Edition will backup on any schedule you would like. Archiving to CD or DVD at the end of the week is a new obligation that needs to be taken seriously, but that would be true no matter what system you use.

Figure 7. Gene to protein synopsis taken from the Google image archive (source: Rockefeller Univ.)


This work was supported in part by grants from the Human Brain Project (MH 62009). I thank my colleagues Drs. Lu Lu, David Airey, Glenn Rosen, Mel Park, Guomin Zhou, Elissa Chesler, Siming Shou, Ken Manly, and Jonathan Nissanov. Special thanks to an extraordinary group of programmers: Tony Capra, Michael Connolly, Alex Williams, Nathan Laporte, Arthur Centeno, and Yanhua Qu. Thanks to Emily Carps for help editing.
Williams RW (2002) Everyday bioinformatics for neuroscientists: from maps to microarays. In: Bioinformatics 2002: a neuroscientist’s guide to tools and techniques for mining and refining massive data sets. (Williams RW, Goldowitz D, eds) pp. XX–XX. Washington: Society for Neuroscience.


Copyright © 2002 by R.W. Williams


Neurogenetics at University of Tennessee Health Science Center

Print Friendly Top of Page

Home Page  |  Genome DBs  |  Phenome DBs  |  Publications  |  People & Associates
Mouse Brain Library  |  Related Sites  |  |   MBL.ORG

Robert W. Williams | Alex Williams © 2002, modify this page