Relational Calculus in DBMS

In this article you read about Relational Calculus in DBMS

Two formal languages for the relational model: the relational algebra and the relational calculus.

The basic set of operations for the relational model is the relational algebra. These operations enable a user to specify basic retrieval requests. The result of a retrieval is a new relation, which may have been formed from one or more relations. The algebra operations thus produce new relations, which can be further manipulated using operations of the same algebra. A sequence of relational algebra operations forms a relational algebra expression, whose result will also be a relation that represents the result of a database query (or retrieval request). . Relational Calculus in DBMS

The relational algebra is very important for several reasons. First, it provides a formal foundation for relational model operations. Second, and perhaps more important, it is used as a basis for implementing and optimizing queries in relational database management systems (RDBMSs), as we discuss in Part 4. Third, some of its concepts are incorporated into the SQL standard query language for RDBMSs. Although no commercial RDBMS in use today provides an interface for relational algebra queries, the core operations and functions of any relational system are based on relational algebra operations. We will define these operations in detail in subsequent sections. Relational Calculus in DBMS

Whereas the algebra defines a set of operations for the relational model, the relational calculus provides a higher-level declarative notation for specifying relational queries. A relational calculus expression creates a new relation, which is specified in terms of variables that range over rows of the stored database relations (in tuple calculus) or over columns of the stored relations (in domain calculus). In a calculus expression, there is no order of operations to specify how to retrieve the query result-a calculus expression specifies only what information the result should contain. This is the main distinguishing feature between relational algebra and relational calculus. The relational calculus is important because it has a firm basis in mathematical logic and because the standard query language (SQL) for RDBMSs has some of its foundations in the tuple relational calculus. Relational Calculus in DBMS

Unary Relational Operations:

SELECT and PROJECT

The SELECT Operation

The SELECT operation is used to select a subset of the tuples from a relation that Satisfies a selection condition. One can consider the SELECT operation to be a filter that keeps only those tuples that satisfy a qualifying condition. The SELECT operation can also be visualized as a horizontal partition of the relation into two sets of tuples–those tuples that satisfy the condition and are selected, and those tuples that do not satisfy the condition and are discarded. For example, to select the EMPLOYEE tuples whose department is 4, or those whose salary is greater than $30,000, we can individually specify each of these two conditions with a SELECT operation as follows:

where the symbol o (sigma) is used to denote the SELECT operator and the selection condition is a Boolean expression specified on the attributes of relation R. Notice that R is generally a relational algebra expression whose result is a relationthe simplest such expression is just the name of a database relation. The relation resulting from the SELECT operation has the same attributes as R. The Boolean expression specified in <selection condition> is made up of a number of clauses of the form Relational Calculus in DBMS

<Attribute name> <comparison op> <constant value>,

where <attribute name> is the name of an attribute of R, <comparison op> is normally one of the operators 5,,5,>, 2, +}, and <constant value> is a constant value from in the attribute domain. Clauses can be arbitrarily connected by the Boolean operators and, or, and not to form a general selection condition. For example, to Select at the tuples for all employees who either work in department 4 and make over $25,000 per year, or work in department 5 and make over $30,000, we can specify the following SELECT operation:

(Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000) (EMPLOYEE)

The result is shown in below

Notice that the comparison operators in the set {<,<, 5, >, 2, +} apply to attributes whose domains are ordered values, such as numeric or date domains. Domains of strings of characters are considered ordered based on the collating sequence of the characters. If the domain of an attribute is a set of unordered values, then only the comparison operators in the set {5, #} can be used. An example of an unordered domain is the domain Color = {‘red’, ‘blue, green, ‘white’, ‘yellow, …} where no order is specified among the various colors. Some domains allow additional types of comparison operators; for example, a domain of character strings may allow the comparison operator SUBSTRING_OF. Relational Calculus in DBMS

In general, the result of a SELECT operation can be determined as follows. The <selection condition> is applied independently to each tuple t in R. This is done by substituting each occurrence of an attribute A; in the selection condition with its value in the tuple t[A;). If the condition evaluates to TRUE, then tuple t is selected. All the selected tuples appear in the result of the SELECT operation. The Boolean conditions AND, OR, and NOT have their normal interpretation, as follows: Relational Calculus in DBMS

(cond1 AND cond2) is TRUE if both (condi) and (cond2) are TRUE; otherwise, it is FALSE.

The SELECT operator is unary; that is, it is applied to a single relation. Moreover, the selection operation is applied to cach luple individually; hence, selection conditions cannot involve more than one tuple. The degree of the relation resulting from a SELECT operation-its number of attributes-is the same as the degree of R. The number of tuples in the resulting relation is always less than or equal to the number of tuples in R. That is, lo, (R)|R| for any condition C. The fraction of tuples selected by a selection condition is referred to as the selectivity of the condition. Notice that the SELECT operation is commutative; that is, Relational Calculus in DBMS

Notice that the SELECT operation is commutative; that is,

Hence, a sequence of SELECTs can be applied in any order. In addition, we can always combine a cascade of SELECT operations into a single SELECT operation with a conjunctive (AND) condition; that is,

The PROJECT Relational Calculus in DBMS

Operation If we think of a relation as a table, the SELECT operation selects some of the rows from the table while discarding other rows. The PROJECT operation, on the other hand, selects certain columns from the table and discards the other columns. If we are interested in only certain attributes of a relation, we use the PROJECT operation to project the relation over these attributes only. Therefore, the result of the PROJECT operation can be visualized as a vertical partition of the relation into two relations: one has the needed columns (attributes) and contains the result of the operation and the other contains the discarded columns. For example, to list each employee’s first and last name and salary, we can use the PROJECT operation as follows: Relational Calculus in DBMS

The resulting relation is shown in Figure 6.1(b). The general form of the PRO operation is Relational Calculus in DBMS

where a (pi) is the symbol used to represent the PROJECT operation, and <attribute list> is the desired list of attributes from the attributes of relation R. Again, notice that R is, in general, a relational algebra expression whose result is a relation, which in the simplest case is just the name of a database relation. The result of the PROJECT operation has only the attributes specified in <attribute list> in the same order as they appear in the list. Hence, its degree is equal to the number of attributes in <attribute list>. Relational Calculus in DBMS

If the attribute list includes only nonkey attributes of R, duplicate tuples are likely to occur. The PROJECT operation removes any duplicate tuples, so the result of the PROJECT operation is a set of tuples, and hence a valid relation. This is known as duplicate elimination. For example, consider the following PROJECT operation: Relational Calculus in DBMS

The result is shown in Figure 6.1(c). Notice that the tuple <F, 25000> appears only once in Figure 6.1(c), even though this combination of values appears twice in the EMPLOYEE relation. Duplicate elimination involves sorting to detect duplicates and hence adds more processing. If duplicates are not eliminated, the result would be a multiset or bag of tuples rather than a set. This was not permitted in the formal relational model, but is allowed in practice. In Chapter 8 we will see that the user can control whether duplicates should be eliminated or not. Relational Calculus in DBMS

The number of tuples in a relation resulting from a PROJECT operation is always less than or equal to the number of tuples in R. If the projection list is a superkey of R__that is, it includes some key of R—the resulting relation has the same number of tuples as R. Moreover, Relational Calculus in DBMS

as long as <list2> contains the attributes in <listl>; otherwise, the left-hand side is an incorrect expression. It is also noteworthy that commutativity does not hold on PROJECT.

Sequences of Operations and The RENAME Operation

The relations shown in Figure 6.1 do not have any names. In general, we may want to apply several relational algebra operations one after the other. Either we can write the operations as a single relational algebra expression by nesting the operations, or we can apply one operation at a time and create intermediate result relations. In the latter case, we must give names to the relations that hold the intermediate results. For example, to retrieve the first name, last name, and salary of all employees who work in department number 5, we must apply a SELECT and a PROJECT operation. We can write a single relational algebra expression as follows:

shows the result of this relational algebra expression. Alternatively, we can explicitly show the sequence of operations, giving a name to each intermediate relation:

It is often simpler to break down a complex sequence of operations by specifving intermediate result relations than to write a single relational algebra enrelational algebra expression. We can also use this technique to rename the attributes in the intermedin in the intermediate and result Relational Calculus in DBMS

relations. This can be useful in connection with more complex operations such as UNION and JOIN, as we shall see. To rename the attributes in a relation, we simply list the new attribute names in parentheses, as in the following example:

These two operations are illustrated in Figure 6.2(b).

If no renaming is applied, the names of the attributes in the resulting relation of a SELECT operation are the same as those in the original relation and in the same order. For a PROJECT operation with no renaming, the resulting relation has the Same attribute names as those in the projection list and in the same order in which they appear in the list. Relational Calculus in DBMS

can also define a formal RENAME operation which can rename either the rela- name or the attribute names, or both-as a unary

operator. The general ME operation when applied to a relation R of degree n is denoted by any of the following three forms: Relational Calculus in DBMS

Where the symbol p (rho) is used to denote the RENAME operator, S is the new relation name, and Bi, B2, …, B, are the new attribute names. The first expression oth the relation and its attributes, the second renames the relation only, and the third renames the attributes only , If the attributes of R and ( A 1 , A2 ………….. An ) in that order , then each A , is renamed as Br. Relational Calculus in DBMS