up previous
Up: Quel Previous: Quel

Converting Queries Easily Into Any Language

  1. I've found that students (and myself) have trouble getting from a database query expressed in English to a query expressed in one of the languages we have covered.
  2. I've found a method that seems to help, involving an intermediate step.

    1. Decide on the relations required to answer the query.
      • You'll need relations containing attributes explicitly mentioned, plus relations needed to ``traverse'' between needed relations.
      • In some cases you will need more than one copy of a relation.
      • Don't include unneeded relations.

    2. Draw them on a piece of paper.
      • It helps to draw them in a sensible order.
      • Draw them in the order you would ``traverse'' them. This will simplify the drawing of links.

    3. Draw in links and constant values.
      • Put links between attributes in different relations wherever the attributes are required to satisfy some comparison operator (equals, less than, etc.).
      • I use ordinary lines for equals, and write any other comparison operator on the line at some convenient spot.
      • Write in constant values, where some attribute must have a specific value.

    4. Now take the diagram, and convert it into the language required. We'll discuss this stage in more detail.
  3. Converting the Diagram to a Specific Language Query

    For simpler queries, the following advice works. Where you need set operations or division, a little more thought is needed.

    1. Relational Algebra: we'll do a correct but not necessarily optimal query.
      • Do an appropriate combination of Cartesian products and natural joins of the relations required.
      • Do a select where the predicate demands that all the links and constants in your diagram be true.
      • Don't forget that natural joins will take care of some of your diagram's links.
      • Finally, do a project of the attributes to be printed out.

    2. Tuple Relational Calculus:
      • Create a tuple variable for each of the relations in your diagram.
      • Make sure the parentheses give you the required scope.
      • Ensure each link and constant in your diagram corresponds to some part of your predicate.
      • Make sure t gets the attributes that should be printed out.

    3. Domain Relational Calculus:
      • Create domain variables. Name them sensibly.
      • Remember that equality is forced by using the same domain variable in several places.
      • Other comparison operators may be explicitly stated, e.g. tex2html_wrap_inline490 .
      • Remember to use the existential qualifier for domain variables, and to make sure your scoping is correct.

    4. SQL: similar to relational algebra.
      • Put all the relations needed in the from clause.
      • Remember to use tuple variables when you have more than one copy of a relation, or for general convenience.
      • Express each of the links and constants in your diagram as part of the predicate in the where clause.
      • State the attributes to be printed out in the select clause.

    5. QBE: your diagram is almost QBE to start with.
      • Select the skeleton tables needed.
      • Remember that you only need one skeleton table per relation. You can put more than one line in a skeleton table.

      • Force equality on links by using the same domain variables in different places (see the connection to domain relational calculus?).

      • Use the condition box where necessary.
      • Use P. to print out the attributes. Remember to use a result relation if attributes are printed out from more than one skeleton table.

    More complicated queries will take more thought, but I believe this intermediate step of making a diagram is always helpful.

next up previous
Next: About this document Up: Quel Previous: Quel

Osmar Zaiane
Tue Jun 9 14:17:04 PDT 1998