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. .
      • 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.