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

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