IFAcorpus: PostgreSQL Web Interface manual

Most data of the IFAcorpus are stored in a PostgreSQL database. For simple access to these data, browseable WWW interfaces are available. These interfaces give a limited view on the underlying SQL queries. The principal aim is to allow querrying the speech and analysis files for intelligent selections. A very good second is to allow original research on the compiled data.
Several functions are available:


Some examples

See also some real life research examples.

(click on 'Example' and then on 'SUBMIT QUERY' on the bottum of the page)

    $DHTMLServer/Fragments.html">
  1. All content words with a deleted word-final /t/ from running speech
  2. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION="/Fragments.html">
  3. All word initial intervocalic /d/ realizations from meaningfull read speech
  4. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION = "/DBstatistics.html">
  5. Phoneme frequency from spontaneous speech
  6. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION = "/DBstatistics.html">
  7. Mean F1/F2 values from monophthong vowels of a single female speaker: Isolated vowels from Fixed Pronunciation style (not utterance final) and lexically stressed vowels from Informal speech and Read Sentences
  8. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION = "/DBstatistics.html">
  9. Corrected Mean duration values from intervocalic consonants from all speakers and meaningfull speaking styles excluding sentence boundaries
  10. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION = "/DBstatistics.html">
  11. Compare realized plural /n/'s for NOUNS and VERBS by style (i.e., how often is a written '-en' actualy pronounced as /-@n/)
  12. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION = "/DBstatistics.html">
  13. Compare realized plural /n/'s for NOUNS and VERBS in Text and Sentence reading by word frequency (i.e., how often is a written '-en' actualy pronounced as /-@n/)
  14. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION = "/DBstatistics.html">
  15. Count recorded sentences per speaker and style
  16. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION = "/DBstatistics.html">
  17. Count aligned sentences per speaker and style
  18. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION = "/DBstatistics.html">
  19. Information on subjects
  20. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION = "/Fragments.html">
  21. Select all realizations of the word 'HEKS'
  22. $METHOD="GET" unless $METHOD eq "POST"; "$METHOD"; " ACTION = "/Fragments.html">
  23. Select citation examples of all Dutch Phonemes (isolated vowels and VCV)

Select database table

First, you should select the table you want to use for the selection or from which you would like to get statistics. For obvious reason, I will not allow to join tables in arbitrary ways (if you don't onderstand this, calculate the cartesian product for a few of these tables). However, you are allowed to combine tables by selecting a number of secondary tables. The system will then combine these in an intelligent (I hope) way. That is, you do not have to make sure that the records are combined in the correct ways, just enter the conditions. NOTE: that the construction of an intelligent query becomes rather complicated when you join tables! You now must ensure that you use the correct attributes from the correct tables.

The other two global parameters that are of importance are the number of WHERE and CASE conditions and the METHOD of the request. The former allows you to ask for more room to specify the exact items you want to select, i.e., the size of the WHERE and CASE clauses in the PostgreSQL SELECT statement. The latter option (METHOD)is of secondary importance. Some browsers/OS combinations can have problems with responses to POST requests. You can change this method here if you get errors.

(Please, try it out) $METHOD">
TABLE:
Secondary Tables:

Number of WHERE conditions:
Submission Method CHECKED if $METHOD eq "POST">POST CHECKED if $METHOD ne "POST">GET
(switch if there are problems with broken connections)

Current Settings:


Global speech parameters

After making sure that you get the correct table, you should select the global speech parameters. This is a very global selection of recorded sentences in terms of speaker, text type, and speaking style.

Attributes

Speaker
Text material
Speaking Style

The Current selection is:


Specific selection criteria

After you have determined which speech to use, you have to specify exactly what items you want to select. These are found in the WHERE clause of a PostgreSQL SELECT statement. You have only a number of pre-specified choices. From Left to Right these are:

A possible WHERE clause would be:

(note, the required ''-quotes are entered automatically, DON'T enter them yourself)

This WHERE clause could be entered in the following manner:

WHERE (optional)

Information on table field names


CASE clauses

CASE clauses are used to combine values into new classes. Although PostgreSQL will allow quite complex CASE statements, our web interface will only allow single-attribute remappings. The mechanism is like that of the WHERE clauses.

In a CASE clause, you can either map the new values to the old attribute name, or you can select a new name of the form 'Case1', 'Case2' etc. Cases with the same name are mapped together. For Example:
Is entered as:

CASE (optional, note that you can only change the name to one of the choices)