Database system by VISHAL PATIL

Unit-I
Database
Management
System
(6KS02)

LESSON .4
Topics to be Cover:
The Relational Algebra:
 Fundamental Operations:
 .. The Select Operation
 .. The Project Operation
 ..Composition of Relational Operations
 ..The Union Operations

Learning Objectives
 Understanding the Concept of Procedural Query Language
 Understanding the Select, Project and Composition of relational
Operations.
 Understanding the Union Operations

The Relational Algebra
 The Relational algebra is procedural query language.
It consists of a set of operations that take one ortwo
relations as input and produce new relation as their
result.
• Six basic operators
• select: σ
• project: ∏
• union: ∪
• set difference: –
• Cartesian product: x
• rename: ρ
 The Select project , and
rename operations are called
unary operations, because they
operate on one relation.
The otherthree operations
operate on pairs of relations
and are called binary
operations.

 Select Operation:
consider sample relation
loan with schema given as
Loan-schema=(loan-number,
branch-name,amount)
 The select operation selects tuples that satisfy a given predicate.
We use the lowercase Greek letter sigma (σ) to denote selection.
The predicate appears as a subscript to σ. and relation followed by
parenthesis.
 Thus, to select those tuples of the loan relation where the branch
is “Perryridge,” we write
σbranch-name =“Perryridge”(loan)
 We can find all tuples in which the amount lent is more than

 Select Operation:
consider sample relation
loan with schema given as
Loan-schema=(loan-number,
branch-name,amount)
 In general, we allow comparisons using =, =, <, ≤, >, ≥ in the
selection predicate.
 Furthermore, we can combine several predicates into a larger
predicate by using the connectives and (∧), or (∨), and not ( ￢ ).
Thus, to find those tuples pertaining to loans of more than $1200
made by the Perryridge branch, we write
σbranch-name=“Perryridge” amount>∧ 1200 (loan)

Project Operation:
Suppose we want to list all loan numbers and the amount of the
loans, but do not care about the branch name.
The project operation allows us to produce this relation. The
project operation is a unary operation that returns its argument
relation, with
certain attributes left out.
Projection is denoted by the uppercase Greek letter pi (Π).
We list those attributes that we wish to appear in the result as a
subscript to Π. The argument relation follows in parentheses.
Thus, we write the query to list all loan numbers and the amount
of the loan as
Πloan-number,amount(loan)
result?

Composition of Relational Operations
The fact that the result of a relational
operation is itself a relation
is important.
Consider the more complicated query
“Find those customers who live in Harrison.” We write:
Πcustomer-name (σcustomer-city=“Harrison” (customer))
Notice that, instead of giving the name of a relation as the argument
of the projection operation, we give an expression that evaluates to a
relation.

Union Operation:
Consider a query to find the names of all bank customers who have
either an account or a loan or both. To answer this query, we need
the information in the depositor relation (Figure 3.5) and in the
borrower relation (Figure 3.7).
We know how to find the names
of all customers with a loan in the bank
Πcustomer-name(borrower)
We also know how to find the names
of all customers with an account in the bank:
Πcustomer-name(depositor)

Union Operation:
To answer the query, we need the union of these two sets; that is, we need all
customer names that appear in either or both of the two relations.
 We find these data by the binary operation union, denoted, as in set theory, by ∪.
So the expression needed is
Πcustomer-name(borrower ) ∪ Πcustomer-name (depositor)
The result relation for this query appears in Figure
Notice that there are 10 tuples in the result, even though
there are seven distinct borrowers and six depositors.
This apparent discrepancy occurs because Smith,
Jones, and Hayes are borrowers as well as depositors.
Since relations are sets, duplicate values are eliminated.

The Set Difference Operation
The set-difference operation, denoted by −, allows us to find tuples
that are in one relation but are not in another.
 The expression r − s produces a relation containing those tuples in
r but not in s.
We can find all customers of the
bank who have an account
but not a loan by writing
Πcustomer-name (depositor) − Πcustomer-name(borrower )
The result relation for
this query appears in Figure 3.13.

The Cartesian Product Operation
The Cartesian-product operation, denoted by a cross (×), allows
us to combine information from any two relations. We write the
Cartesian product of relations r1 and r2 as r1 × r2.
For example, the relation schema for r = borrower × loan is
(borrower.customer-name, borrower.loan-number, loan.loan-number,
loan.branch-name, loan.amount)

Suppose that we want to find the names of all customers who have
a loan at the Perryridge branch.
We need the information
in both the loan relation
and the borrower relation
to do so.
σbranch-name =“Perryridge”(borrower × loan)

The Rename Operation
Unlike relations in the database, the results of relational-algebra expressions do
not have a name that we can use to refer to them. It is useful to be able to give
them names; the rename operator,
denoted by the lowercase Greek letter rho (ρ),
Given a relational-algebra expression E, the expression
ρx(E) returns the result of expression E under the name x.
Thus, we can also apply the rename operation to a relation r to get the same
relation under a new name.
A second form of the rename operation is as follows.
ρx(A1,A2,...,An)(E)
returns the result of expression E under the name x, and with the attributes
renamed to A1,A2, . . .,An.

What we Learned?
 Database Schema
 Relation
 Keys Concepts in Database
 Query languages.

HOMEWORK
 Explain the Keys Concepts in Database.
 What is Database Schema.

Database system by VISHAL PATIL

More Related Content

What's hot

Viewers also liked

Similar to Database system by VISHAL PATIL

Recently uploaded

Database system by VISHAL PATIL