AMO Database Query Interfaces February 1, 2006

1
AMO Database Query InterfacesFebruary 1, 2006

• Here are some notes on screen layouts for AMO
  database queries. Hopefully this document will
  give interface designers a start on developing
  web pages for database access. I included both
  our open ideas about user interface as well as
  specific layouts to start developing screens.
  See the later slides (starting with 6) for
  proposed screen layouts.
• pg

2
Screen Flow Query - List - Detail
The diagram below represents the screen flow that
is common throughout AMO database access queries.
This presentation focuses on the example of
sequence data because sequence is a major focus
of data gathering and investigation. However, a
similar flow will also apply for direct query of
location, sample and geochem data.

• Starting point for data request for example
• Select all the sequences for a specific location
• Select all sequences from low pH soils
• (many more criteria available )

Query

• Displays multiple results that meet query
  criteria
• Enables scrolling and paging through multiple
  results
• Enables download of the entire set of results to
  a text file (fasta)
• Enables selecting an individual sequence to
  proceed to the detail screen.

List

• Displays all data for a single selected sequence
• Also provides access to detailed location and
  sample information
• Can link to more details such as geochemistry
• Can navigate back to Query or List screens so
  user can revise choices and look at other
  sequences

Detail
3
Spatial (GIS) Queries Query - List - Detail
GIS queries can use the same list and detail
screens as attribute-based database queries.
Query by attribute is the common database query
based values of data in the database. For
example, select all sequences gathered on a
specific date.
GIS or spatial data queries use location to
select information. Examples select sequences
found within 1 meter of a given location or,
select sequences found within the bounds of a
specific vegetation type. Spatial queries and
queries by attribute may be combined. Spatial
queries can feed to the same list (and detail)
screens used by queries by attribute.
Query by Attribute
Query by Location
List
Detail
4
Screen Data Sequence Query Screen
There are at least three approaches to the
initial query screen. We can consider all of
them for AMO and then determine which best suits
our goals. The following slide recommends an
approach for AMO.
Theres a pre-defined query approach that SIMO
uses, where many query options are provided to
the user in table form. These are pre-defined
in that they are a much smaller set than a truly
open-ended query definition. The defined queries
reflect (perhaps) the most likely queries users
might want. For example, see (http//simo.marsci.
uga.edu/public_db/data_matrix_env.asp). A
corresponding approach for AMO would be to
anticipate what we think are the likely major
categories of queries, and provide a similar
interface for those queries in a pre-defined form.
Open queries give the user tools, using menus
or buttons, to select their own parameters for a
query. SIMO uses an extreme form of open query
to complement their predefined queries, that
allows users to define a very wide range of
choices, but requires significant technical
knowledge (http//simo.marsci.uga.edu/public_db/cu
stom_fasta.htm). An aspect of MaNIS is the
ability to select from a concept list - an open
query with a limited set of user
options. (http//manis.mvz.berkeley.edu/pres/Prese
ntationServlet?actionmenulochttp3A2F2Fbnhm.b
erkeley.museum2Fmanis2FDwC2Fdarwin2jrwInfodo.xm
lreqTypesearch)
1
2
Finally there are joint queries that span more
than one database. MaNIS uses this (also on
http//manis.mvz.berkeley.edu/pres/PresentationSer
vlet?actionmenulochttp3A2F2Fbnhm.berkeley.mu
seum2Fmanis2FDwC2Fdarwin2jrwInfodo.xmlreqType
search). AMO will want to include this in our
interface, though I recommend we develop our
single database interface first.
3
5
Screen Data Sequence Query Screen
The initial query approach recommended for AMO is
an open query giving the user options to select
on parameters of location name, sample date and
geochemical characteristics of sample locations.
One way to implement this would be drop-down
menus for each choice
Select
Location Name
Sample Date
Geochemistry
ltlist of choicesgt
ltlist of choicesgt
ltlist of choicesgt
Other forms of buttons or menus may be preferred.
In addition, Im sure well refine our list of
parameters and choices to offer as we proceed
with implementation.

• Eventually all three types of queries (see
  previous slide) may be required.

6
Screen Data Sequence List Screen
Below is a sample layout for a sequence list
screen. This sample is completely unformatted
all AMO styles are yet to be applied. Also,
since we are in preliminary design, the actual
fields desired for this screen will probably
change as we refine our design. However this
represents a general format for the sequence list
display. Note the second row (shaded) which
indicates the data element to populate each
column (the format is tablename.columnname).
The following four rows provide some sample data
to illustrate the example.

• Also useful on this screen
• A reminder for users that displays the criteria
  they chose from the query screen that brought
  them to this point. (For example in a header
  position or other highlighted format)
• A link or button to download ALL the sequences in
  this list to a text file (fasta format). May
  require dialog box to choose location for the
  downloaded file.

Location ID Location Name AMO Sequence Name GenBank Accession Bases Sample ID Sample Date
location.locationID location.locName sequence.sequenceName sequence.GenBankNumber sequence.pairs soilsample.soilsampleID soilsample. sampleDate
0000001 Talus site north probe T3_A_C1 AY999999.9 648 0000001 2000-07-17
0000001 Talus site north probe T3_A_D1 AY888888.8 659 0000001 2000-07-17
0000002 Talus site data logger T0-D3 AY555555.5 681 0000002 2001-08-21
0000002 Talus site data logger T0-D12 AY444444.4 784 0000002 2001-08-21
Note that some data elements are formatted to
indicate they are hyperlinks (blue, underlined).
Clicking on a link for AMO Sequence Name takes
the user to the detail screen for that sequence.
Clicking on the GenBank Accession link goes to
the corresponding external link for the GenBank
information on that sequence. It may be
desirable to make many other fields on this
screen sortable and linkable, e.g., click on
Location Name to see detail about that location
OR to select all sequences for that location.
(design details still open)
This screen also needs navigation buttons
(previous, next, first, last) to handle cases
where there are multiple screens worth of records
in the list.
7
Sequence Detail Screen Alternatives
There are (at least) these two options for laying
out the sequence detail and providing the user
access to additional detail relevant to the
sequence.
A concise sequence detail screen has inks to
detail screens for location and sample (also able
to link back to screens - the arrows are two-way)
Sequence Detail
Location Detail
link-to
Alternative1
Sample Detail
h
Sequence Detail --------- Sample
Detail ---------- Location Detail
Alternative2
A long sequence detail screen requires page-downs
but includes sample detail and location detail on
that same screen. (E.g., SIMO uses this form)
8
Screen Data Sequence Detail Screen
Here is a sample layout for the sequence detail
screen Much more data will be present on this
screen after we implement phylogeny information
currently this is the basic layout for sequence
data. See the following slides for some sample
data in this format.
16s rRNA Gene Sequence for sequence.sequenceName
sequence.sequence Format lay the sequence out in column and row format each row of base pairs has 6 blocks of 10 See (http//simo.marsci.uga.edu/public_db/matrix_details_env.asp?id1068match) for an example of this layout. See next slide for an example sequence. Number of base pairs sequence.pairs sequence.sequence Format lay the sequence out in column and row format each row of base pairs has 6 blocks of 10 See (http//simo.marsci.uga.edu/public_db/matrix_details_env.asp?id1068match) for an example of this layout. See next slide for an example sequence. Number of base pairs sequence.pairs
GenBank Integer ID sequence.GenBankGI
GenBank Accession Number sequence.GenBankNumber
GenBank Defintion sequence.GenBankDefinition
9
Sequence Detail Sample Data
This sample corresponds to line 1 on the list
screen sample data slide.
16s rRNA Gene Sequence for T3_A_C1
GCTCACACCGATAGTCCCTCTAAGAAGTCGCGTCCAAAGGAAGAATCCTTTGTCGAACTATTTAGCAGGTCGAGGTCTCGTTCGTTAACGGAATTAACCAGACAAATCACTCCACCAACTAAGAACGGCCATGCACCACCACCCACAGAATCAAGAAAGATCTTCAATCTGTCAATCCTTACTGTGTCTGGACCTGGTAAGTTTTCCCGTGTTGAGTCAAATTAAGCCGCAGGCTCCACTCCTGGTGGTGCCCTTCCGTCAATTCCTTTAAGTTTCAGCCTTGCGACCATACTCCCCCCGGAACCCAAAGACTTTGATTTCTCATAAGGTGCGAATGAAGTCGAAAATGCAACGTTCACTCATCCCTAGTCGGCATAGTTTATGGTTGAGACTACGACGGTATCTGATCGTCTTCGATCCCCCAACTTTCGTTCTTGATTAAAGAAGACATCCTTGGTGAATGCTTTCGCATAAGTTAGTCTTCAGCGAATCCGAGAATTTCACCTCTGGCCGCTGAATACTAGCACCCCCAACTGTCCCTATCAATCATTACTTCAGTCCTAGAAACCAACAAAATAGAACCGAAGTCCTAAATTATTATTCCATGCTAATGTATTCATAGCATAAAGCCTGCTTGAAACACTCTAA Number of base pairs 648 GCTCACACCGATAGTCCCTCTAAGAAGTCGCGTCCAAAGGAAGAATCCTTTGTCGAACTATTTAGCAGGTCGAGGTCTCGTTCGTTAACGGAATTAACCAGACAAATCACTCCACCAACTAAGAACGGCCATGCACCACCACCCACAGAATCAAGAAAGATCTTCAATCTGTCAATCCTTACTGTGTCTGGACCTGGTAAGTTTTCCCGTGTTGAGTCAAATTAAGCCGCAGGCTCCACTCCTGGTGGTGCCCTTCCGTCAATTCCTTTAAGTTTCAGCCTTGCGACCATACTCCCCCCGGAACCCAAAGACTTTGATTTCTCATAAGGTGCGAATGAAGTCGAAAATGCAACGTTCACTCATCCCTAGTCGGCATAGTTTATGGTTGAGACTACGACGGTATCTGATCGTCTTCGATCCCCCAACTTTCGTTCTTGATTAAAGAAGACATCCTTGGTGAATGCTTTCGCATAAGTTAGTCTTCAGCGAATCCGAGAATTTCACCTCTGGCCGCTGAATACTAGCACCCCCAACTGTCCCTATCAATCATTACTTCAGTCCTAGAAACCAACAAAATAGAACCGAAGTCCTAAATTATTATTCCATGCTAATGTATTCATAGCATAAAGCCTGCTTGAAACACTCTAA Number of base pairs 648
GenBank Integer ID (not yet assigned)
GenBank Accession Number (not yet assigned)
GenBank Defintion (not yet defined)
10
Sequence Detail Sample Data
This sample corresponds to line 2 on the list
screen sample data slide.
16s rRNA Gene Sequence for T3_A_D1
CTTGAGCCAAAGTCCCTCTAAGAAGCTGGCGACCAACAAAAGTCAATCTAGCTATTTAGCAGGTCANGGTCTCGTTCGTTATCGCAATTAAGCAGACAAATCACTCCACCAACTAAGAACGGCCATGCACCACCACCCATATAATCAAGAAAGAGCTATCAATCTGTCAATCCTAAACATGTCTGGACCTGGTGAGTTTTCCCGTGTTGAGTCAAATTAAGCCGCAGGCTCCACGCCTTGCGCAATTTCTTAGGCTTGTAGCCTAAAAGCACGATCATATCTTAACGAATTACAATAATTCGACGACCTCCACTTGATCTGTGGACCCTAGAAGGGTTGCAGGTCAGCTATAAATGCCGTCCATGCAGTTAAAAGGTCTAACCGAGCGAATCGGTCATATAGGCACCACAAGAGTGGTTAATCTGGTGCCCTTCCGTCAATTTCTTTAAGTTTCAGCCTTGCGACCATACTCCCCCCAGAGCCCAAAAACTTTGATTTCTCGTAAGGTGCCGAACGGGGCATAAAAAACCACCGTCCGATCCCTAGTCGGCATAGTTTATGGTTAAGACTACGACGGTATCTGATCGTCTTCGATCCCCTAACTTTCGTTCACTGATTAATGAAAACATCCTTGGCGAATGCTTTCGCAATAGTTAGTC Number of base pairs 659 CTTGAGCCAAAGTCCCTCTAAGAAGCTGGCGACCAACAAAAGTCAATCTAGCTATTTAGCAGGTCANGGTCTCGTTCGTTATCGCAATTAAGCAGACAAATCACTCCACCAACTAAGAACGGCCATGCACCACCACCCATATAATCAAGAAAGAGCTATCAATCTGTCAATCCTAAACATGTCTGGACCTGGTGAGTTTTCCCGTGTTGAGTCAAATTAAGCCGCAGGCTCCACGCCTTGCGCAATTTCTTAGGCTTGTAGCCTAAAAGCACGATCATATCTTAACGAATTACAATAATTCGACGACCTCCACTTGATCTGTGGACCCTAGAAGGGTTGCAGGTCAGCTATAAATGCCGTCCATGCAGTTAAAAGGTCTAACCGAGCGAATCGGTCATATAGGCACCACAAGAGTGGTTAATCTGGTGCCCTTCCGTCAATTTCTTTAAGTTTCAGCCTTGCGACCATACTCCCCCCAGAGCCCAAAAACTTTGATTTCTCGTAAGGTGCCGAACGGGGCATAAAAAACCACCGTCCGATCCCTAGTCGGCATAGTTTATGGTTAAGACTACGACGGTATCTGATCGTCTTCGATCCCCTAACTTTCGTTCACTGATTAATGAAAACATCCTTGGCGAATGCTTTCGCAATAGTTAGTC Number of base pairs 659
GenBank Integer ID (not yet assigned)
GenBank Accession Number (not yet assigned)
GenBank Defintion (not yet defined)
11
Sequence Detail Sample Data
This sample corresponds to line 3 on the list
screen sample data slide.
16s rRNA Gene Sequence for T0-D3
ACACTGCTCTCTATCAGTCTTTGACTGTTACTGTGAGAAAATTAGAGTGTTTCAAAGCAGGCTATTGCAGGAATACATTAGCATGGAATAACGAACGTCTAGAATATTGGTTAATTCTAGATAACGATTAATAGGGACAGTTGGGGGCATTAGTATTTAATTGTCAGAGGTGAAATTCTTGGATTATTTAAAGACTAACGTATGCGAAAGCATTTGCCAAGGATGTTTTCATTAATCAAGAACGAAAGATAGGGGATCAAAGACGATCAGATACCGTCGTAGTCCTATCTATAAACTATGCCGACTAGGGATTAGGGTGGAAAAGTACCATCTTAGTACCTTATGAGAAATCAAAGTCTTTGGGTTCTGGGGGGAGTATGGTCGCAAGACTGAAACTTAAAGAAATTGACGGAAGGGCACCACCAGGAGTGGAGCCTGCGGCTTAATTTGACTCAACACGGGGAAACTTACCAGGTCCAGACGTAGTAAGGATTGACAGATTGATAGCTCTTTCTTGATTCTATGGGTGGTGGTGCATGGCCGTTCTTAGTTGGTGGAGTGATTTGTCTGGTTAATTCCGTTAACGAACGAGACCTTAACCTGCTAACTAGACTAGTCCATTTTATGGATTCAGTCTTCTTAGAGGGACTATGTTGCGASAGCACAGGAAGTTTGAGGCAA Number of base pairs 681 ACACTGCTCTCTATCAGTCTTTGACTGTTACTGTGAGAAAATTAGAGTGTTTCAAAGCAGGCTATTGCAGGAATACATTAGCATGGAATAACGAACGTCTAGAATATTGGTTAATTCTAGATAACGATTAATAGGGACAGTTGGGGGCATTAGTATTTAATTGTCAGAGGTGAAATTCTTGGATTATTTAAAGACTAACGTATGCGAAAGCATTTGCCAAGGATGTTTTCATTAATCAAGAACGAAAGATAGGGGATCAAAGACGATCAGATACCGTCGTAGTCCTATCTATAAACTATGCCGACTAGGGATTAGGGTGGAAAAGTACCATCTTAGTACCTTATGAGAAATCAAAGTCTTTGGGTTCTGGGGGGAGTATGGTCGCAAGACTGAAACTTAAAGAAATTGACGGAAGGGCACCACCAGGAGTGGAGCCTGCGGCTTAATTTGACTCAACACGGGGAAACTTACCAGGTCCAGACGTAGTAAGGATTGACAGATTGATAGCTCTTTCTTGATTCTATGGGTGGTGGTGCATGGCCGTTCTTAGTTGGTGGAGTGATTTGTCTGGTTAATTCCGTTAACGAACGAGACCTTAACCTGCTAACTAGACTAGTCCATTTTATGGATTCAGTCTTCTTAGAGGGACTATGTTGCGASAGCACAGGAAGTTTGAGGCAA Number of base pairs 681
GenBank Integer ID (not yet assigned)
GenBank Accession Number (not yet assigned)
GenBank Defintion (not yet defined)
12
Sequence Detail Sample Data
This sample corresponds to line 4 on the list
screen sample data slide.
16s rRNA Gene Sequence for T0-D3
GTAGTTGGATTTGGATGGGTTGTTGCGGTCCGCTCTGGTGAGTACTGCTGTGGCCTATCTTTCTGCCGGGGACGGGCTCCTGGGCTTCACTGTCCGGGACTGGAGTGGCGATGTTACTTTGAGTAAATTAGAGTGTTCAAAGCAGGCNTACGCTCTGAATACTTTAGCATGGAATAACACGATAGGACTCTGGCCTATCTTGTTGGTCTGTAGGACCGGAGTAATGATTAAGAGGGACAGTCGGGGGCATTCGTATTTCATTGTCAGAGGTGAAATTCTTGGATTTATGAAAGACGAACTACTGCGAAAGCATTTGCCAAGGATGTTTTCATTAATCAAGAACGAAAGTTGGGGGCTCGAAGACGATTAGATACCGTNGTAGTCTCAACCATAAACGATGCCGACTAGGGATTGGCGGATGTTTATTCAATGACTCCGCCAGCACCTTATGAGAAATCAAAGTTTTTGGGTTCCGGGGGGAGTATGGTCGCAAGGCTGAAACTTAAAGGAATTGACGGAAGGGCACCACCAGGCGTGGAGCCTGCGGCTTAATTTGACTCAACACGGGAAAACTTACCAGGTCCAGACATAGTGAGGATTGACAGATTGAGAGCTCTTTCTTGATTCTATGGGTGGTGGTGCATGGCCGTTCTTAGTTGGTGGGTTGCCTTGTCAGGTTGATTCCGGTAACGAACGAGACCTCAGCCTGCTAAATAGTCCTAGTCGCTTCTTGCGGCTAGCTGACTTCTTAGAGGGACTATGGCGTTAGTCAAGGAAGTATGAGTTTGAGGCAA Number of base pairs 681 GTAGTTGGATTTGGATGGGTTGTTGCGGTCCGCTCTGGTGAGTACTGCTGTGGCCTATCTTTCTGCCGGGGACGGGCTCCTGGGCTTCACTGTCCGGGACTGGAGTGGCGATGTTACTTTGAGTAAATTAGAGTGTTCAAAGCAGGCNTACGCTCTGAATACTTTAGCATGGAATAACACGATAGGACTCTGGCCTATCTTGTTGGTCTGTAGGACCGGAGTAATGATTAAGAGGGACAGTCGGGGGCATTCGTATTTCATTGTCAGAGGTGAAATTCTTGGATTTATGAAAGACGAACTACTGCGAAAGCATTTGCCAAGGATGTTTTCATTAATCAAGAACGAAAGTTGGGGGCTCGAAGACGATTAGATACCGTNGTAGTCTCAACCATAAACGATGCCGACTAGGGATTGGCGGATGTTTATTCAATGACTCCGCCAGCACCTTATGAGAAATCAAAGTTTTTGGGTTCCGGGGGGAGTATGGTCGCAAGGCTGAAACTTAAAGGAATTGACGGAAGGGCACCACCAGGCGTGGAGCCTGCGGCTTAATTTGACTCAACACGGGAAAACTTACCAGGTCCAGACATAGTGAGGATTGACAGATTGAGAGCTCTTTCTTGATTCTATGGGTGGTGGTGCATGGCCGTTCTTAGTTGGTGGGTTGCCTTGTCAGGTTGATTCCGGTAACGAACGAGACCTCAGCCTGCTAAATAGTCCTAGTCGCTTCTTGCGGCTAGCTGACTTCTTAGAGGGACTATGGCGTTAGTCAAGGAAGTATGAGTTTGAGGCAA Number of base pairs 681
GenBank Integer ID (not yet assigned)
GenBank Accession Number (not yet assigned)
GenBank Defintion (not yet defined)
13
Sequence Detail Sample Data
This sample shows the additional GenBank fields
populated.
16s rRNA Gene Sequence for (none)
GTGTCTTTCGACACTCGCCGGCTTTCACCGGTTTAGAGATGCATGTCAAGGCCTGGTAAGGTTCTTCGCGTTGCATCGAATTGAGCCACATACTCCACCGCTTGTGCGGGTCCCCGTCAATTTCTTTGAGTTTTAATCTTGCGACCGTACTCCCCAGGCGGCACGTTTAACGCGTTAGCTCCGGCGCAGAAGGGGTCGAATCCCCCCACACCAAACGTGCACCGTTTACTGCCAGGACTACCGGGGTATCTAATCCCGTTTGCTCCCCTGGCCTTCGTGTCTCAGTGTCAGGAACTGTCCAGAGACCCGCCTTCGCCGCTGGTCTTCCTTACGATATCTACGCATTTCACTGCTACACCGTAAATTCCAGTCTCCTCTCCAGTCCTCAAGCACGCCAGTATCGAATGCAGTTTCGGAGTTAAGCTCCGAAATTTCACACCCGACTTAGCGCGCCACCTACACACCCTCTACGCCCAATGAATCCGAACAACGCTTGAGACCTCTGTATTACCGCGGCTGCTGGCACAGAGTTAGCCGTCTCTTCCTCTTCTGGTACTATCA Number of base pairs 561 GTGTCTTTCGACACTCGCCGGCTTTCACCGGTTTAGAGATGCATGTCAAGGCCTGGTAAGGTTCTTCGCGTTGCATCGAATTGAGCCACATACTCCACCGCTTGTGCGGGTCCCCGTCAATTTCTTTGAGTTTTAATCTTGCGACCGTACTCCCCAGGCGGCACGTTTAACGCGTTAGCTCCGGCGCAGAAGGGGTCGAATCCCCCCACACCAAACGTGCACCGTTTACTGCCAGGACTACCGGGGTATCTAATCCCGTTTGCTCCCCTGGCCTTCGTGTCTCAGTGTCAGGAACTGTCCAGAGACCCGCCTTCGCCGCTGGTCTTCCTTACGATATCTACGCATTTCACTGCTACACCGTAAATTCCAGTCTCCTCTCCAGTCCTCAAGCACGCCAGTATCGAATGCAGTTTCGGAGTTAAGCTCCGAAATTTCACACCCGACTTAGCGCGCCACCTACACACCCTCTACGCCCAATGAATCCGAACAACGCTTGAGACCTCTGTATTACCGCGGCTGCTGGCACAGAGTTAGCCGTCTCTTCCTCTTCTGGTACTATCA Number of base pairs 561
GenBank Integer ID 28435712
GenBank Accession Number AY192335.1
GenBank Defintion Uncultured Verrucomicrobia bacterium clone W3-2F 16S ribosomal RNA gene, partial sequence