Chapter5 Relational Algebra and Relational Calculus

Chapter5 Relational Algebra and Relational Calculus

5.1The Relational Algebra
The relational algebra is a theoretical language with operations that work on one or more relations todefine another relation without changing the orginal
relation(s).
Thus both the operands and the results are relatons,and so the output from

one operation can become the input to another operation.

This ability allows expressions to be nested in the relational algebra.This property is called closure:relations are closed under the algebra.

The five fundamental operations in relational algebre-Selection,Projection,Chartesian produce,Union and Set difference---

perform most of the data retrieval operation that we are interested in.In addition,there are also the Join,Intersection,and Division operations, which can be expressed in terms of the five basic operations.

5.1.1Unary Operation
we start discussion of the relational algebra by examining the two unary operations:Selection and Projection.
• Selection

σpredicate(R) Theselection operation works on a single relation R and defines a relation that contains only those tuples of Rthat satisfy the

  specified condition.

• Projection
Πa1,...,an(R)The Projection operation works on a single relation R and defines a relation that contains avertical subset of R, extracting the values of sprcified attributes and eliminateing duplicates.
5.1.2 Set Operations
Union

R∪S :The union of two relations R and S define a relation that contains all the tuples of (R or S) ,or (both R and S),duplicate tuples being eliminated.R and S must be union-compatible.
If R and S have I and j tuples,respectively , their union is obtained by concatenating them into one relation with a maximum of (I + j) tuples.Union is
possible only if the schemas of the two relation match,that is ,if they have the same number of attributes with each pair of corresponding attributes having
the same domain.

eg.Πcity(Branch)∪Πcity(PropertyForRent)

Set difference

R∪S:The set difference operation define a relationconsisting of tuples that are in relation R,but not in S.R and S must be union-compatible.
eg.Πcity(Branch)-Πcity(PropertyForRent)

Intersetion

R∩S:The intersection operation defines a relation consisting of set of all tuples that are in both R and S.R and S must be union-compatible.
eg.Πcity(Branch)∩Πcity(PropertyForRent)

Cartesian product

R x S: The cartesian product operation defines a relation that is concate-nation of every tuples relation R with every tuple of relation S.
The cartesian product operation multiples two relations to define another relation consisting of all possible pairs of tuples from two relations.Therefore,if one relation has I tuples and N attributes,and the other has J tuple and M attributes ,the Cartesian product will contain(i * J) tuples with(N + M) attributes.It is possible that the relations may have attribute with the same name.In this case , the attribute names are prefixed with the relation name to maintain the uniqueness of attribute names within a relation.

Decomposing complex operations

We can decompose such operations into a series of smaller relational algebra operations andgive a name to the result of a relation.
We use the assigment operation , denote by⟵...in this case ,the right-hand side of the operation is assigned to the left-hand side.
eg.TempViewing(clientNo,propertyNo,comment)⟵Π(clientNo,propertyNO,comment)(Viewing)
ρs(E) or ρs(a1,...,an)(E):The rename operation provides a new name S for the expression E ,and optionally names the attributes as a1,...,an.

5.1.3Join Operations

Theta join(Θ-join):The Theta join operation defines a relation that containtuples satisfying the predicate F from the Cartsian product of R and S.The pred-icate F is of the form R.a1=S.b1,where may be one of the comparison operator(<,≤,≥,>,=)
In the case where the predicate F contain only equality(=),the term Equijoin is used instead.

Natural Join
R⋈S:The natural join is an equijoin of two relation R and S over all common attribute x.One occurrence of each common attribute is eliminated from the
result.
Outer Join
Semijion
5.1.4 Division Operation

R ∻ S:The Division operation defines a relation over the attribute C that consists of the set of tuples from R that match the combination of every tuple in S.

5.1.5Aggregation and Grouping Operations
Aggregation operations

зAL(R) Applies the aggregate function list,AL,to the relation R to define a relation over the aggregate list.AL contains one or more(<aggregation_function>,<attribute>)pairs.
The main aggregation function are:
• COUNT
• SUM
• AVG
• MIN
• MAX

Grouping operation

GaзAL(R):Group the tuples of relation R by the grouping attributes , GA ,and then applies the aggregate function list AL to define a new relation,AL contains one or more(<aggregation_function>,<attribute>)pairs.The resulting relation contain the grouping attributes,GA,along with the result of each the
aggregation functions.

5.2The Relational Calculus
The relational calculus is not related to differential and integral calculus in mathematics,but take its name from a branch of symbolic logic called predicate
calculus.
In first-order logic or predicate calculus,a predicate is a truth-valued function with agruments.When we substitute values for the arguments,the function
yield expression,called a proposition.
If P is a predicate,then we can write the set of all x such that P is true for x,as:
{x|P(x)}
When applied to database,it is found in two forms:tuple relational calculus
and domain relational calculus.
5.2.1Tuple Relational Calculus
.
.
.
Informally,we may describe the relational algebra as a (high-level)procedural language:it can be used to tell the DBMS how to build a new relation from one or more relation in the database.Again,informally,we may describe the relational calculus as a nonprocedural language:it can be used fo formulate the definition of a relaion in terms of one or more database relatoins.... They have been used as the basis for other,higher-level Data Manipulate
Language.