Constructing Table Maker Queries

When constructing a Table Maker query, there are three main steps to follow:

Step 1 — Decide which table you wish to search against. Basically, just select the table that contains the data you wish to find. If you aren't sure which table would contain your data, or even what a table is, consult the Understanding the Database section. After choosing your table, use the drop-down menu located at the top of Table Maker Query page to select the table you chose to search against.

Step 2 — Determine your search criteria. Having determined the table you wish to search against, it's time to decide if you wish to impose additional constraints on your search, other than just that the record be from your selected table. If you don't want another constraint, just enter a "%" into the "Search Value" box. If you do want to further constrain your search, you first need to choose the table that you wish to use to constrain your search. More often than not you will probably only be searching within one table, but if you have a need to construct a complex query, this option is available to you. After choosing the table you wish to use to constrain your search, you then need to choose a field, a relation, and a value. In choosing these three things, you are asking the database to return to you all the records from your chosen table, where your selected field is of a certain relation to your value.

Having defined your constraint, it's now time to enter it into Table Maker. First, select the table you wish to use for your constraint from the "Related Tables" dialog. After this, under the "Search Fields" option, use the drop down menu to select the field of your choice. Next, using the "Operators" drop down menu, select the operator that defines the relationship between your field and your value. Last, enter your value into the the "Search Value" box.

Step 3 — Decide which fields to display in your result. When you conduct your search, Table Maker is going to go to the database and find all of the records that fit your constraints. Having found all of these records, however, it needs to know what fields in these records it should bring back to display in your result. Once you've chosen the fields you wish to display in your result, select them from the "List of fields" box, and use the ">>" button to add them to the "Fields to display" box. To undo an add, simply select the field from the "Fields to display" box, and use the "<<" button to move it back.

return to top

Interpreting your Results

Due to the sheer number of results that the execution of some queries may return, it is impractical that all of the results be displayed at once. To prevent the user from being overwhelmed and the web-page from being too sluggish, only 100 results will be displayed at a time. By clicking on the various numbers in the "Sets" section, you can gain access to the rest of your results.

By default, your results will be listed in alphabetical order according to the data in the first field you selected. If you wish to change how your results are ordered, you may click on the down arrow to the right of a field name, and all of your results will be re-ordered alphabetically, in accordance with data in the column you just selected.

In addition to changing the way your results are displayed, you can also modify your original search query, and generate a new list of results. To do this, simply type in a new query into the textfield at the top (or modify your existing one), and hit the "Execute SQL Query" button.

return to top

Sample Search

This example will be carried out following the steps listed in the Constructing Queries section.

EXAMPLE: Retrieving all the ESTs that are generated from flower tissue. Jump to the video of this search being performed.

Step 1 — Which table should we search? Well, we want to find all the ESTs from a specific source, so we should select the "source" table to search against. From the Query page, we select the "source" field from the top-most pull-down.

Step 2 — How should we specify our searching constraints, i.e., which field to search for and how? First, the researcher is only interested in ESTs, so let's go ahead and add this constraint now. This can be specified by searching the "division" field (corresponding to GenBank division) of the "source" table. So, choose "division" from "Search Field", select the "equals" from the "Operator" menu, and type "EST" into the "Search Value" box. Now we've limited our search to EST data.

Next, we need to limit to our search to make sure that the remaining results come from flower tissue. As you would probably guess, the field that we're going to use is in the "tissue_type" field of the "source" table. To enter in this constraint, select "tissue_type" under "Search Field", type in either "floral" or "flower" into "Search Value" box, and then select "contains" from "Operator", since we're trying to find all the records where the "tissue_type" field contains "floral" or "flower". Now our search is limited to just EST data that comes from flower tissue.

Step 3 — It's time for us to specify which columns to display in our result. From the "List of Fields" box, we are going to select the following fields, and move them to the "Fields to Display" box using the ">>" button.

Field to Select		Reason for Selection
source.gi source.mol_type source.organism source.cell_line source.cell_type source.clone_lib source.cultivar source.dev_stage source.tissue_lib source.tissue_type		shows the GenBank gi# shows the type of the sequence, "EST" etc? shows the species name, "Zea mays" etc? shows the name of cell line (if available) shows the name of cell type (if available) shows the name of clone library (if available) shows the cultivar information (if available) shows developmental stage (if available) shows tissue library name (if available) shows tissue type (if available)

Retrieving all the ESTs that are generated from flower tissue

Download this video

return to top

Understanding the Database

Database Terminology

A database is a collection of tables, which themselves are a collection of rows (a.k.a. records), and columns (a.k.a. fields). Let's take a look at a sample database which is comprised of a subset of tables from the actual Table Maker database. This database has three tables in it: "UniProt_summary", "GenBank_taxonomy", and "old_GenBank_Summary". Here's a visual representation of our database.

As you can see from above, the names of the various database components are different than one would expect at first. Here are some general rules regarding the various names of database parts. First, the names of tables are usually in the singular (even though you might guess they would be plural). Also, spaces aren't allowed in the names of fields, so underscores ("_") are usually substituted in their place.

The PK and the FK labels in our diagram indicate which field in the table is a primary key, and which fields in the database are foreign keys. Primary keys are unique fields in a table that are stored by any other table wishing to refer to it. The "oldGenBank_summary" table wants to be associated with the "GenBank_taxonomy" table, so it stores the PK of the table in it's own matching FK field. The dashed line with the branched end serves as a visual representation of this relationship.

Querying Records

Databases are usually so large and so full of information, that the only real useful way to interact with them is through queries. Queries are basically just questions that you are asking of the database. For instance, if you wanted the database to get you all of the organisms in the "GenBank_taxonomy" table, you'd type in the following query, and be returned a list of organisms.

select organism from GenBank_taxonomy;

If you wanted to print out the organisms stored in both the "UniProt_summary" and the "GenBank_taxonomy" tables, you run into a bit of a problem. In both tables, the names of the organisms are stored in a field called "organism", and the database won't be able to distinguish which "organism" field we want. Here's how we make a query that overcomes this issue.

select UniProt_summary.organism, GenBank_taxonomy.organism from UniProt_summary, GenBank_taxonomy;

Notice how we just put the name of the table in front of the field to distinguish between the two, and how we used a comma to instruct the 3 database to search multiple tables. Now, let's try a more complex query. Let's say that you wanted to find an organism's classification, and you know that the division in the GenBank record that references your organism is "EST". This query becomes more difficult, since we're not just searching one table, but another table related to it.

select GenBank_taxonomy.classification from GenBank_taxonomy, oldGenBank_summary where oldGenBank_summary.division = "EST" and GenBank_taxonomy.organism = oldGenBank_summary.organism;

The "where" clause on the right consists of two parts. The first, 'oldGenBank_summary.division = "EST"', creates a subgroup of divisions to search -- divisions that have the name "EST". The second part, 'GenBank_taxonomy.organism = oldGenBank_summary.organism', states that we're only interested in records of organisms that are also in the oldGenBank_summary table, which from the previous step contains only records where the division is equal to "EST".

The Table Maker Database

The database that Table Maker serves as a gateway to is much more complex than the simple database illustrated above, containing dozens of tables, rather than just three. All of the tables in this database are based on the GenBank feature definitions, which can be found here.

Below is a graphical representation of all of the different tables in this database, and their corresponding fields. This representation, combined with the information in the GenBank feature definitions, should aid you in determining which tables and fields to use when you are constructing your Table Maker queries.

return to top