# Transforming Relational Expressions

The first step of the optimizer says to implement such expressions that are logically equivalent to the given expression. For implementing such a step, we use the equivalence rule that describes the method to transform the generated expression into a logically equivalent expression.

Although there are different ways through which we can express a query, with different costs. But for expressing a query in an efficient manner, we will learn to create alternative as well as equivalent expressions of the given expression, instead of working with the given expression. Two relational-algebra expressions are equivalent if both the expressions produce the same set of tuples on each legal database instance. A legal database instance refers to that database system which satisfies all the integrity constraints specified in the database schema. However, the sequence of the generated tuples may vary in both expressions, but they are considered equivalent until they produce the same tuples set.

### Equivalence Rules

The equivalence rule says that expressions of two forms are the same or equivalent because both expressions produce the same outputs on any legal database instance. It means that we can possibly replace the expression of the first form with that of the second form and replace the expression of the second form with an expression of the first form. Thus, the optimizer of the query-evaluation plan uses such an equivalence rule or method for transforming expressions into the logically equivalent one.

The optimizer uses various equivalence rules on relational-algebra expressions for transforming the relational expressions. For describing each rule, we will use the following symbols:

θ, θ1, θ2 : Used for denoting the predicates.

L1, L2, L3 : Used for denoting the list of attributes.

E, E1, E2 …. : Represents the relational-algebra expressions.

Let's discuss a number of equivalence rules:

This rule states the deconstruction of the conjunctive selection operations into a sequence of individual selections. Such a transformation is known as a cascade of σ.

σθ1 ᴧ θ 2 (E) = σθ1θ2 (E))

Rule 2: Commutative Rule

a) This rule states that selections operations are commutative.

σθ1θ2 (E)) = σ θ2θ1 (E))

b) Theta Join (θ) is commutative.

E1θ E 2 = E 2θ E 1 (θ is in subscript with the join symbol)

However, in the case of theta join, the equivalence rule does not work if the order of attributes is considered. Natural join is a special case of Theta join, and natural join is also commutative.

However, in the case of theta join, the equivalence rule does not work if the order of attributes is considered. Natural join is a special case of Theta join, and natural join is also commutative.

This rule states that we only need the final operations in the sequence of the projection operations, and other operations are omitted. Such a transformation is referred to as a cascade of ∏.

∏L1 (∏L2 (. . . (∏Ln (E)) . . . )) = ∏L1 (E)

Rule 4: We can combine the selections with Cartesian products as well as theta joins

Rule 4: We can combine the selections with Cartesian products as well as theta joins

1. σθ (E1 x E2) = E ⋈ E2
2. σθ1 (E1θ2 E2) = E1θ1ᴧθ2 E2

Rule 5: Associative Rule

a) This rule states that natural join operations are associative.

(E1 ⋈ E2) ⋈ E3 = E1 ⋈ (E2 ⋈ E3)

b) Theta joins are associative for the following expression:

(E1θ1 E2) ⋈ θ2ᴧθ3 E3 = E1θ1ᴧθ3 (E2θ2 E3)

In the theta associativity, θ2 involves the attributes from E2 and E3 only. There may be chances of empty conditions, and thereby it concludes that Cartesian Product is also associative.

#### Note: The equivalence rules of associativity and commutatively of join operations are essential for join reordering in query optimization.

Rule 6: Distribution of the Selection operation over the Theta join.

Under two following conditions, the selection operation gets distributed over the theta-join operation:

a) When all attributes in the selection condition θ0 include only attributes of one of the expressions which are being joined.

σθ0 (E1θ E2) = (σθ0 (E1)) ⋈ θ E2

b) When the selection condition θ1 involves the attributes of E1 only, and θ2 includes the attributes of E2 only.

σθ1ꓥ θ2 (E1θ E2) = (σθ1 (E1)) ⋈ θ ((σθ2 (E2))

Rule 7: Distribution of the projection operation over the theta join.

Under two following conditions, the selection operation gets distributed over the theta-join operation:

a) Assume that the join condition θ includes only in L1 υ L2 attributes of E1 and E2 Then, we get the following expression:

L1υL2 (E1θ E2) = (∏L1 (E1)) ⋈ θ (∏L2 (E2))

b) Assume a join as E1 ⋈ E2. Both expressions E1 and E2 have sets of attributes as L1 and L2. Assume two attributes L3 and L4 where L3 be attributes of the expression E1, involved in the θ join condition but not in L1 υ L2 Similarly, an L4 be attributes of the expression E2 involved only in the θ join condition and not in L1 υ L2 attributes. Thus, we get the following expression:

L1υL2 (E1θ E2) = ∏L1υL2 ((∏L1υL3 (E1)) ⋈ θ ((∏L2υL4 (E2)))

Rule 8: The union and intersection set operations are commutative.

E1 υ E2 = E2 υ E1

E1 ꓵ E2 = E2 ꓵ E1

However, set difference operations are not commutative.

Rule 9: The union and intersection set operations are associative.

(E1 υ E2) υ E3 = E1 υ (E2 υ E3)

(E1 ꓵ E2) ꓵ E3 = E1 ꓵ (E2 ꓵ E3)

Rule 10: Distribution of selection operation on the intersection, union, and set difference operations.

The below expression shows the distribution performed over the set difference operation.

σp (E1 − E2) = σp(E1) − σp(E2)

We can similarly distribute the selection operation on υ and ꓵ by replacing with -. Further, we get:

σp (E1 − E2) = σp(E1) −E2

Rule 11: Distribution of the projection operation over the union operation.

This rule states that we can distribute the projection operation on the union operation for the given expressions.

L (E1 υ E2) = (∏L (E1)) υ (∏L (E2))

Apart from these discussed equivalence rules, there are various other equivalence rules also.

Next TopicCandidate Key

### Feedback   