Relational Query Languages

A query language is a language using which a user can request data from the database.

Query Languages can be procedural (the user instructs the system to perform a set of operations on the database) or non-procedural (the user specifies only what information is needed without specifying how to obtain that information).

We will discuss the following relational query languages:

• Relational Algebra (procedural)

• Tuple Relational Calculus (non-procedural)

• Domain Relational Calculus (non-procedural)

Relational Operators

A relational operator takes as input one or more relations and outputs a new relation.

• selection of tuples

  The selection operator $\sigma$ returns the rows of a relation that satisfy a selection predicate.

  Ex. $\sigma _{A=B\, and\, C>5}(r)$

• selection of columns (projection)

  The projection operator is denoted by $\Pi$

  Ex. $\Pi _{A,\, C} (r)$

  Note that duplicate tuples will be eliminated automatically.

• join (cartesian product)

  This operation is applied on two relations and the output is another relation. The join operator ($\times$) basically merges pairs of tuples, one from each relation, to create a new relation.

  Ex. If r and s are two relations , $r\times s$ will be

• union

  The union operation ($\cup$) can be performed on two relations that have the same attributes.

  Ex. If r and s are two relations , $r\cup s$ will be

• set difference

  The set difference (-) operation can be performed on two relations that have the same attributes. The result will contain tuples that are in the first relation but not those that are in the second relation.

  Ex. If r and s are two relations , r-s will be

• intersection The intersection operation ($\cap$) can be performed on two relations that have the same attributes. The result will contain tuples that are both in the first and second relations. Ex. If r and s are two relations , $r\cap s$ will be Note that $r\cap s = r-(r-s)$

• natural join The natural join ($\bowtie$) of r and s results in the set of all combinations of tuples in r and s that are equal on their common attribute names. If r and s are two relations , $r\bowtie s$ will be The result of the natural join operation is usually smaller than that of the cartesian product operation. Note that natural join is associative i.e. $(a\bowtie b) \bowtie c = a\bowtie (b \bowtie c)$ and commutative i.e. $a \bowtie b = b \bowtie a$

• theta join $r\bowtie _\theta s = \sigma _\theta (r \times s)$

• outer join The outer join operation ($\sqsupset\bowtie$, $\bowtie\sqsubset$, $\sqsupset\bowtie\sqsubset$) ia an extension of the join (natural join) operation that avoids loss of information. It computes the join and then adds tuples from one relation that do not match tuples in the other relation to the result of the join. null is used to denote missing values in the result.

  Ex. if we have two relations instructor and teaches, given by and respectively, then the natural join ($instructor\bowtie teaches$) would be:

  The left outer join ($instructor \sqsupset\bowtie teaches$) would be: The right outer join ($instructor \bowtie\sqsubset teaches$) would be: and the full outer join ($instructor \sqsupset\bowtie\sqsubset teaches$) would be:

• rename The rename operator ($\rho$) allows us to name, and therefore refer to, the results of a relational operation.

• assignment The assignment operator ($\leftarrow$) lets us assign the result of an operation to a temporary relation variable.

More about null values

• Two nulls are treated to be the same

• The result of any arithmetic operation involving a null value is null

• Comparisons with null values return the special truth value: unknown

• Three-valued logic using the truth value unknown:

  • OR:

    (unknown or true) = true,

    (unknown or false) = unknown

    (unknown or unknown) = unknown

  • AND:

    (true and unknown) = unknown,

    (false and unknown) = false,

    (unknown and unknown) = unknown

  • NOT:

    (not unknown) = unknown

• The result of select predicate is treated as false if it evaluates to unknown