Chapter 3

Relational Model and Relational

Algebra
 3.1 . Introduction to Relational Model,
Relational Schema and concepts of keys,
Mapping ER and EER Model to the
Relational Model
3.2 Relational Algebra-Unary and set
operations, Relational algebra
queries
 Introduction Relational Model
• A Database model defines the logical design
and structure of a database and defines how
data will be stored, accessed and updated in a
database management system. While
the Relational Model is the most widely used
database model, there are other models too:
• Hierarchical Model
• Network Model
• Entity-relationship Model
• Relational Model
 Hierarchical Model
• This database model organizes data into a tree-
like-structure, with a single root, to which all the
other data is linked. The hierarchy starts from
the Root data, and expands like a tree, adding
child nodes to the parent nodes.
• In this model, a child node will only have a single
parent node.
• This model efficiently describes many real-world
relationships like index of a book, recipes etc.
 Network Model

• This is an extension of the Hierarchical model. In

this model data is organized more like a graph, and
are allowed to have more than one parent node.
• In this database model data is more related as
more relationships are established in this database
model. Also, as the data is more related, hence
accessing the data is also easier and fast. This
database model was used to map many-to-many
data relationships.
• This was the most widely used database model,
before Relational Model was introduced.
 Entity-relationship Model

• In this database model, relationships are created

by dividing object of interest into entity and its
characteristics into attributes.
• Different entities are related using relationships.
• E-R Models are defined to represent the
relationships into pictorial form to make it easier
for different stakeholders to understand.
• This model is good to design a database, which
can then be turned into tables in relational
model(explained below).
 Relational Model
• In this model, data is organized in two-
dimensional tables and the relationship is maintained
by storing a common field.
• This model was introduced by E.F Codd in 1970, and
since then it has been the most widely used database
model, in fact, we can say the only database model
used around the world.
• The basic structure of data in the relational model is
tables. All the information related to a particular type
is stored in rows of that table.
• Hence, tables are also known as relations in relational
model.
 Introduction to Relational Model
• Relational data model is the primary data
model, which is used widely around the world
for data storage and processing. This model is
simple and it has all the properties and
capabilities required to process data with
storage efficiency.
 Relational Model
• The Relational model stores data in the form of
tables.
• This concept is introduced by Dr. E. F. Codd ,a
researcher of IBM
• Relational model is most famous because of its
simplest structure as compare to other database
model.
• Relational model consist of three major
components–Data Structures ,Data Integrity,
Data Manipulation.
 Basic concept of Relational Model
• Tables − In relational data model, relations are saved in the format of Tables.
This format stores the relation among entities. A table has rows and
columns, where rows represents records and columns represent the
attributes.
• Tuple − A single row of a table, which contains a single record for that
relation is called a tuple.
• Relation instance − A finite set of tuples in the relational database system
represents relation instance. Relation instances do not have duplicate
tuples.
• Relation schema − A relation schema describes the relation name (table
name), attributes, and their names.
• Relation key − Each row has one or more attributes, known as relation key,
which can identify the row in the relation (table) uniquely.
• Attribute domain − Every attribute has some pre-defined value scope,
known as attribute domain.
Relational Model

• Important terminology of Relational Model:

• Attributes

• Student= Tuple-1
• Tuple-2
Relation
• Tuple-3
• Int. String String
• Domain

15
 Constraints

• Every relation has some conditions that must

hold for it to be a valid relation. These conditions
are called Relational Integrity Constraints. There
are Four main integrity constraints −
• Domain integrity constraints
• Key integrity constraints
• Referential integrity constraints
• Enterprise Constraint(Additional rules specified
by user)
 Domain Integrity Constraints

• The Domain integrity constraints check that whether the

attribute having Proper or right value in the database.

• Attributes have specific values in real-world scenario. For

example, age can only be a positive integer. The same
constraints have been tried to employ on the attributes of a
relation. Every attribute is bound to have a specific range of
values. For example, age cannot be less than zero and
telephone numbers cannot contain a digit outside 0-9.

• Constraints:
Data Types,Not NUll,Unique, Default-e.g exam
fees ,Check(Range)- e.g age
Identify the Domain Constraint

Emp_ID Name Salary

8001 ABC 15000

8002 PQR 13000

A MNO 12000

8004 16000

8005 ASD 5000

8006 EFR 1000000

 Key Integrity Constraints

• Under Key constraint Primary key is the main

factor.
• Primary key uniquely identifies each record in a
table. primary key=NOT Null and Unique
• In a relation with a key attribute, no two tuples
can have identical values for key attributes.
• Key constraints are also referred to as Entity
Constraints.
• Eg. Employee id is primary key.
so if we add any repeated value or null value in
the table it will show error.
 Referential integrity Constraints /Foreign Key Constraints

• Referential integrity constraints is specified between two

relations and is to maintain consistency among the tuples
in two relations.

• Referential integrity constraints work on the concept of

Foreign Keys. A foreign key is a key attribute of a relation
that can be referred in other relation.

• Referential integrity constraint states that if a relation

refers to a key attribute of a different or same relation,
then that key element must exist.
 Referential integrity Constraints
Here, one table is referring other table.
Student Department
Student Name Dept-code Credits Dept-code Dept-Name
ID
101 CSE
101 John 101 12
102 EEB
102 Robin 102 14
103 ECE

103 XYZ 103 10 104 MECH

104 PQR 104 10

● Primary Key can’t accept null [Link] Key can accept null
values.
• We can assign only one Primary key in a table but we can assign
more than one foreign key.
• Delete Cascade: if a record in the parent table is deleted then
corresponding record from the child table wil automatically
 Enterprise Constraints
● also referred as Semantic Constraint
● Example:
In College System a class can have
maximum of 70 students.
 Mapping ER and EER Model to the
Relational Model
• There are several processes and algorithms
available to convert ER Diagrams into
Relational Schema.
• ER diagrams mainly comprise of −
• Entity and its attributes
• Relationship, which is association among
entities.
Ignore derived Atrributes.
3.2 Relational Algebra-Unary and set
operations, Relational algebra
queries
Relational Model

• Query Language:
– is a language in which a user request information from the
database.
– Higher level than standard programming language.
– Query Language classified as :
• Procedural Query language
• Non Procedural Query Language
• Procedural Query language:
– user instruct the system to perform a sequence of operation on DB to
compute desired result.
– Example : Relational Algebra
• Non-Procedural Query language:
– The user describe the desired information without giving a specific
procedure.
– Example : Tuple relational calculus, Domain relational calculus
33
 Relational Algebra
• It consist of a set of a set of operations that take one or two
relations as input and produce a new relation as their
result.

• Every database management system must define a query

language to allow users to access the data stored in the
database. Relational Algebra is a procedural query language
used to query the database tables to access data in
different ways.
• In relational algebra, input is a relation(table from which
data has to be accessed) and output is also a relation(a
temporary table holding the data asked by the user).
Relational Model

Relational Algebra operators

Unary Binary Additional

operators operators operators

Cartesian
Select Intersection
product

Project Union Join

Set
Rename Division
difference

Assignment
36
• The primary operations that we can perform
using relational algebra are:
• Select
• Project
• Union
• Set Difference
• Cartesian product
• Rename
Relational Algebra Operators
• Operator No: 1
• Name of Operator : Select
• Purpose :
– selects the tuples (rows) that satisfy the given predicate
(condition)
• Symbol : Greek letter sigma- σ
• Syntax: σ (Predicate) (relation-name/Expression)
• Note :
– Predicate can be defined using operations =, ≠, <, ≤, >, ≥, ^(and) ,
v(or)
• Example :

38
 Select Operation (σ)

• This is used to fetch rows(tuples) from table(relation)

which satisfies a given condition.
• Syntax: σp(r)
• Where, σ represents the Select Predicate, r is the
name of relation(table name in which you want to
look for data), and p is the prepositional logic, where
we specify the conditions that must be satisfied by
the data. In prepositional logic, one can
use unary and binary operators like =, <, > etc, to
specify the conditions.
• Let's take an example of the Student table we specified above
in the Introduction of relational algebra, and fetch data
for students with age more than 17.
• σage > 17 (Student)
• This will fetch the tuples(rows) from table Student, for
which age will be greater than 17.
• You can also use, and, or etc operators, to specify two
conditions, for example,
• σage > 17 and gender = 'Male' (Student)
• This will return tuples(rows) from table Student with
information of male students, of age more than 17.(Consider
the Student table has an attribute Gender too.)
Relational Model
Player
Player_id Team_id Country Age Runs Wickets
1001 101 India 25 10000 300
1003 102 Pakistan 29 5000 25
1002 103 England 30 2000 70
1011 104 Australia 27 3000 120
1004 101 India 28 20000 200
1006 101 India 22 15000 50
1007 102 Pakistan 28 3000 80
1008 103 England 24 6200 90
1009 104 Australia 29 2900 320
1010 102 Pakistan 31 2800 80
1005 101 India 21 5200 400

41
Relational Algebra Operators

• Example on Select:
1. Query : Find all the tuples from player relation for which country is
“India”
2. Ans : σ(country = ‘India’) (Player)

Output :

Player_i Team_id Country Age Runs Wickets

d
1001 101 India 25 10000 300

1004 101 India 28 20000 200

1006 101 India 22 15000 50

1005 101 India 21 5200 400

42
Relational Algebra Operators

• Example on Select:
2. Query : Select all the players for which runs are greater than or equal to
10000
– Ans : σ(runs ≥ 10000) (Player)
– Output :

Player_i Team_id Country Age Runs Wickets

d

1001 101 India 25 10000 300

1004 101 India 28 20000 200

1006 101 India 22 15000 50

43
Relational Algebra Operators

• Example on Select:
3. Query : Select all the players for which runs are greater than 6000 and
age less than 25.
– Ans : σ(runs > 6000 ^ age < 25) (Player)
– Output :

Player_i Team_id Country Age Runs Wickets

d

1006 101 India 22 15000 50

1008 103 Englan 24 6200 90

d

44
Relational Algebra Operators
• Operator No: 2
• Name of Operator : Project
• Purpose :
– Is a unary operation and it returns its arguments relation
with certain attributes left out
• Symbol : Greek letter pi- ∏
• Syntax: ∏(A1,A2,A3…An)(Relation/Expression)
• Note : It eliminates duplicate tuples
• Example :

45
 Project Operation (∏)

• Project operation is used to project only a certain set of attributes

of a relation. In simple words, If you want to see only
the names of all the students in the Student table, then you can
use Project Operation.
• It will only project or show the columns or attributes asked for,
and will also remove duplicate data from the columns.
• Syntax: ∏A1, A2...(r)
• where A1, A2 etc are attribute names(column names).
• For example,
• ∏Name, Age(Student)
• Above statement will show us only the Name and Age columns
for all the rows of data in Student table.
Relational Algebra Operators

• Example on Project:
1. Query : Find all the countries in a Player.
2. Ans : ∏ (Country ) (Player)

Output :

Country
India
Pakistan
England
Australia

47
Relational Algebra Operators

• Example on Project:
1. Query : Find all the team-id and countries from the table Player.
2. Ans : ∏ (Team_id, Country ) (Player)

Output :

Team_id Country
101 India
102 Pakistan
103 England
104 Australia

48
Relational Algebra Operators

• Example on composition of Select and project :

1. Query : Find all the team-id and country, age and runs for the player
whose runs are greater than 6000 and age is less than 25.
2. Ans : ∏ (Team_id, Country, Age, Runs)(σ(runs > 6000 ^ age < 25) (Player))

Output :

Team_id Country Age Runs

101 India 22 15000

103 England 24 6200

49
Relational Algebra Operators
• Operator No: 3
• Name of Operator : Union
• Purpose :
– The resultant relation P has tuples drawn from R and S ,such that a tuple in P is either in
R or S or in both of them.
• Symbol : U
• Syntax: P = R U S
• Note :
– It eliminates duplicate tuples
– For union R and S relation must be compatible with each other.

• Compatible relations Means : must satisfy 2 conditions

1. R and S are of same arity ( no of attributes must be same)
2. The domain must be same.

50
 Union Operation (∪)

• This operation is used to fetch data from two

relations(tables) in temporary relation(result of another
operation).
• For this operation to work, the relations(tables) specified
should have same number of attributes(columns) and same
attribute domain. Also the duplicate tuples are
automatically eliminated from the result.
• Syntax: A ∪ B
• where A and B are relations.
• For example, if we have two
tables RegularClass and ExtraClass, both have a
column student to save name of student, then,
• ∏Student(RegularClass) ∪ ∏Student(ExtraClass)
• Above operation will give us name
of Students who are attending both regular
classes and extra classes, eliminating repetition.
Relational Algebra Operators

• Example on Union:
1. Query : find names of the customers having an account or loan.
2. Ans : ∏ (cust_name ) (Depositor) U ∏ (cust_name ) (Borrower)

Output :

Cust-
Name
ANIL
OMKAR
RAHUL
RAJ
RAMESH
SACHIN
SUMIT

53
Relational Algebra Operators
• Operator No: 4
• Name of Operator : Difference
• Purpose :
• P contains those tuples in R but not in S.
• Means removes common tuples from the first relation

• Symbol : -
• Syntax: P = R - S
• Note :
– It eliminates duplicate tuples
– For difference R and S relation must be compatible with each other.

• Compatible relations Means : must satisfy 2 conditions

1. R and S are of same arity ( no of attributes must be same)
2. The domain must be same.

54
 Set Difference (-)

• This operation is used to find data present in one

relation and not present in the second relation. This
operation is also applicable on two relations, just like
Union operation.
• Syntax: A - B
• where A and B are relations.
• For example, if we want to find name of students
who attend the regular class but not the extra class,
then, we can use the below operation:
• ∏Student(RegularClass) - ∏Student(ExtraClass)
Relational Algebra Operators

• Example on Difference:
1. Query : find names of the customers having a loan but not the account.
2. Ans : ∏ (cust_name) (Borrower) - ∏ (cust_name) (Depositor)

Output :

Cust-Name

RAMESH

ANIL

56
Relational Algebra Operators
• Operator No: 5
• Name of Operator : Intersection
• Purpose :
– Common tuples in R and S
– The resultant relation P has tuples drawn from R and S ,such that a tuple in P is in R and
S
• Symbol : -
• Syntax: P = R ⋂ S
• Note :
– It eliminates duplicate tuples
– For intersection R and S relation must be compatible with each other.

• Compatible relations Means : must satisfy 2 conditions

1. R and S are of same arity ( no of attributes must be same)
2. The domain must be same.

57
Relational Algebra Operators

• Example on intersection:
1. Query : find names of the customers having an account and Loan.
2. Ans : ∏ (cust_name) (Depositor) ⋂ ∏ (cust_name) (Borrower)

Output :

Cust-
Name
SACHIN

RAJ

58
Relational Algebra Operators
• Operator No: 6
• Name of Operator : Cartesian product
• Purpose :
– It is the concatenation of tuples belonging to the two relations.
– The resultant relation P contains all possible combinations of tuples in
R and S.
• Symbol : X
• Syntax: P = R X S
• Note :
– Not eliminates duplicate tuples
– No compatible relations required.

59
 Cartesian Product (X)

• This is used to combine data from two different relations(tables)

into one and fetch data from the combined relation.
• Syntax: A X B
• For example, if we want to find the information for Regular Class
and Extra Class which are conducted during morning, then, we
can use the following operation:
• σtime = 'morning' (RegularClass X ExtraClass)
• For the above query to work,
both RegularClass and ExtraClass should have the attribute time.
Relational Algebra Operators

• Example on Cartesian product

• R– relation S-relation
A B C D
a1 b1 c1 d1
a2 b2 c2 d2
c3 d3
c4 d4
• P= R X S

A B C D
a1 b1 c1 d1
a1 b1 c2 d2
a1 b1 c3 d3
a1 b1 c4 d4
a2 b2 c1 d1
a2 b2 c2 d2
a2 b2 c3 d3
a2 b2 c4 d4 61
Relational Algebra Operators

• Example on Cartesian product

• R– relation S-relation
A C D
A B
a1 b1 a1 c1 d1
a2 b2 a2 c2 d2
• P= R X S

R.A B S.A C D
a1 b1 a1 c1 d1
a1 b1 a2 c2 d2
a2 b2 a1 c1 d1
a2 b2 a2 c2 d2
62
Relational Algebra Operators

• Example on Cartesian product

• Emp – relation Branch-relation
E# Enam E# Br-name Salary
e
1 1 VASHI 1000
ABC
2 THANE 2000
2 XYZ

• P= Emp X Branch
Emp.E# Ename Branch.E# Br-name Salary
1 ABC 1 VASHI 1000
1 ABC 2 THANE 2000
2 XYZ 1 VASHI 1000
2 XYZ 2 THANE 2000

63
Relational Algebra Operators

• Example on Cartesian product

• P = Emp X Branch
Emp.E# Ename Branch.E# Br-name Salary
1 ABC 1 VASHI 1000
1 ABC 2 THANE 2000
2 XYZ 1 VASHI 1000
2 XYZ 2 THANE 2000

• Query : Find E#, Name of Employee along with Branch name and
salary.
• Ans : σ(Emp.E# =Branch.E#) (Emp X Branch)
Emp.E# Ename Branch.E# Br-name Salary

1 ABC 1 VASHI 1000

2 XYZ 2 THANE 2000

64
Relational Algebra Operators

• Example on Cartesian product cont….

• Query : Find E#, Name of Employee along with Branch name and
salary.
• Ans : σ(Emp.E#
Emp.E#
(Emp X Branch)
=Branch.E#)Ename Branch.E# ------------Step1
Br-name Salary
1 ABC 1 VASHI 1000
2 XYZ 2 THANE 2000

• Final ans :
• ∏ (Emp.E#, Ename, Br-name, Salary) (σ(Emp.E# =Branch.E#) (Emp X Branch) )
E# Ename Br-name Salary
1 ABC VASHI 1000
2 XYZ THANE 2000

65
Relational Algebra Operators
• Operator No: 7
• Name of Operator : Assignment
• Purpose :
– To assign sub-expression to some temporary variable.
– Provides a convenient way to express complex queries.
– Write query as a sequential program consisting of
• a series of assignments
• followed by an expression whose value is displayed as a result of the
query.
– Assignment must always be made to a temporary
relation variable.
• Symbol : 🡨
• Syntax: P🡨 expression
66
Relational Algebra Operators

• By referring previous example :

• ∏ (Emp.E#, Ename, Br-name, Salary) (σ(Emp.E# =Branch.E#) (Emp X Branch) )

E# Ename Br-name Salary

1 ABC VASHI 1000
2 XYZ THANE 2000

• Same query we can write by using assignment operator

• R1🡨 Emp X Branch
• R2 🡨 σ(Emp.E# =Branch.E#) (R1)
• R3🡨 ∏ (Emp.E#, Ename, Br-name, Salary)(R2)

67
Relational Algebra Operators
• Query : Find the names of customer who have a
loan at “Perryridge” branch.
• Solution: R1 🡨 σ(branch-name = “Perryridge”)(Loan)

loan-no branch-name amount

L-15 Perryridge 1500

L-16 Perryridge 1300

68
Relational Algebra Operators
• R2 🡨 R1 x borrower
Cust-name [Link]-no [Link]-no branch-name amount
Adams L-16 L-15 Perryridge 1500
Adams L-16 L-16 Perryridge 1300
Curry L-93 L-15 Perryridge 1500
Curry L-93 L-16 Perryridge 1300
Hayes L-15 L-15 Perryridge 1500
Hayes L-15 L-16 Perryridge 1300
Jackson L-14 L-15 Perryridge 1500
Jackson L-14 L-16 Perryridge 1300
Jones L-17 L-15 Perryridge 1500
Jones L-17 L-16 Perryridge 1300
Smith L-11 L-15 Perryridge 1500
Smith L-11 L-16 Perryridge 1300
Smith L-23 L-15 Perryridge 1500
Smith L-23 L-16 Perryridge 1300
Williams L-17 L-15 Perryridge 1500
Williams L-17 L-16 Perryridge 1300

69
Relational Algebra Operators

• R3 🡨σ(loan. Loan-number = borrower. Loan-number ) (R2)

Cust-name [Link]-no [Link]- branch- amount
no name
Adams L-16 L-16 Perryridge 1300

Hayes L-15 L-15 Perryridge 1500

• R4🡨
∏customer-name(R3) Cust-name
Adams
Hayes

70
Relational Algebra Operators
• Final answer :
• ∏cust-name(σ[Link]-no = [Link]-no( ( σ branch-
name = “Perryridge”(loan)) x borrower))

Cust-name
Adams

Hayes

71
Relational Algebra Operators
• Operator No: 8
• Name of Operator : Rename
• Purpose :
– use to rename existing relation & form a new relation.
• Symbol : ρ (a Greek letter --rho)
• Syntax:
– 1. ρnew-relation-name(existing relation-name)

– 2. ρnew-relation-name(A1,A2…) (existing relation-name)

72
 Rename Operation (ρ)

• This operation is used to rename the output

relation for any query operation which returns
result like Select, Project etc. Or to simply
rename a relation(table)
• Syntax: ρ(RelationNew, RelationOld)
Relational Algebra Operators

• Example of issue before using Rename operator

• Suppose : - R– relation

A B
a1 b1
a2 b2

• P= R X R
A B A B
a1 b1 a1 b1
a1 b1 a2 b2
a2 b2 a1 b1

a2 b2 a2 b2

74
Relational Algebra Operators

• Example of issue before using Rename operator

• Suppose : - R– relation
A B • P= R X ρs (R)
a1 b1
R.A R.B S.A S.B
a2 b2 a1 b1 a1 b1
a1 b1 a2 b2
a2 b2 a1 b1
a2 b2 a2 b2

• Or P= R X ρS(M,N)(R)
A B M N
a1 b1 a1 b1
a1 b1 a2 b2
a2 b2 a1 b1
a2 b2 a2 b2
75
Relational Algebra Operators
• Query : Find the largest account balance in the bank.
• Solution:
• R1 🡨 Account X ρd(Account) R1-Output = 6 attributes and 49 tuples
account [Link]

• R2 🡨 ∏[Link](σ([Link]<[Link]) (R1))
– Output of R2 – 1 attribute and 6 tuples
Balanc
e
500
400
700
750
350
76
Relational Algebra Operators
• Query : Find the largest account balance in the bank.
• Solution: cont.…
• R3 🡨 ∏ balance(Account) -output 1 attribute and 7 tuples
• R4 🡨 R3 – R2 final answer – 900

R3 R2

• R4 = -
(900)

77
Relational Algebra Operators
• Exercise
• Query : Find the names of all customer who live on the same
street and in the same city as that of “Smith”.
• Solution :
• You have to write
• Relational algebra equation ? ?

• Output :
Cust-name
Curry
Smith

78
Relational Algebra Operators
[Link]
operation-in-relational-algebra-dbms
• Operator No: 9
• Name of Operator : Division
• Purpose : To divide
• Symbol : ÷
• Syntax: P = R ÷ S
• Example : 1
• A R B B S P=R÷S
a1 b1 A
a1 b2 b1 a1
a2 b1
a3 b1
b2
a4 b2 a5
a5 b1
a5 b2
79
Relational Algebra Operators
• Example : 2
• R S P=R÷S
A B
a1 B A
b1
a1 b2
a1
a2 b1 b1 a2
a3 b1 a3
a4 b2
a5
a5
b1
a5 b2
• Example : 3 and 4 Keeping R as it is and changing S relation….
• S P=R÷S S P=R÷S
B A
a1 B A
• a2 b1
a3 b2
a4
b3
a5

80
Relational Algebra Operators
• Operator No: 10
• Name of Operator : Natural Join
• Purpose :
– Operation forms a Cartesian product of its argument, performs a selection forcing
equality on those attribute that appear in both relation schemas.
• Symbol : ⋈
• Syntax: P = R ⋈ S
• Example : suppose
• EMP Branch
Emp- Street City Branch-Name
Emp- Sal
name name
XYZ SEC-8 AIROLI XYZ SBI-A 1500
ABC SEC-3 VASHI ABC SBI-V 1300
IJK NAB MULUND PQR SBI-M 5300
NML Sec-5 CBD NML SBI-C 1500

88
Relational Algebra Operators
• Example : EMP Branch

• Solution:
∏ ([Link]-name, Street, City, Branch-name, Sal) (σ([Link]-name = [Link]-name) (Emp X Branch) )
Or
EMP ⋈ Branch
Final output :
Emp-name Street City Branch-Name Sal
XYZ SEC-8 AIROLI SBI-A 1500
ABC SEC-3 VASHI SBI-V 1300
NML Sec-5 CBD SBI-C 1500
89
Relational Algebra Operators
• Types of Join : Left Outer join ,Right outer join, Full outer join
• Left outer join : operation allows keeping all tuple in the left relation. However,
if there is no matching tuple is found in right relation, then the attributes of right
relation in the join result are filled with null values.
• Example : EMP Branch

EMP Branch
Final output :
Emp-name Street City Branch-Name Sal
XYZ SEC-8 AIROLI SBI-A 1500
ABC SEC-3 VASHI SBI-V 1300
IJK NAB MULUND NULL NULL
NML Sec-5 CBD SBI-C 1500
91
Relational Algebra Operators
• Right outer join : operation allows keeping all tuple in the right relation.
However, if there is no matching tuple is found in the left relation, then the
attributes of the left relation in the join result are filled with null values.
• Example : EMP Branch

EMP Branch
Final output :
Emp-name Street City Branch-Name Sal
XYZ SEC-8 AIROLI SBI-A 1500
ABC SEC-3 VASHI SBI-V 1300
PQR NULL NULL SBI-M 5300
NML Sec-5 CBD SBI-C 1500

92
Relational Algebra Operators
• Full outer join : all tuples from both relations are included in the result,
irrespective of the matching condition.
• Example : EMP Branch

EMP Branch
Final output :

Emp-name Street City Branch-Name Sal

XYZ SEC-8 AIROLI SBI-A 1500
ABC SEC-3 VASHI SBI-V 1300
IJK NAB MULUND NULL NULL
PQR NULL NULL SBI-M 5300
NML Sec-5 CBD SBI-C 1500

93
Relational Algebra Operators
• Query : Find names of the branches located in
“Brooklyn” city.
• Solution :
• R1 🡨 ∏branch-name (σbranch-city = “Brooklyn” (branch))
Branch-
nm
Brighton

Downtown

94
Relational Algebra Operators
• Query : Find all customer names who have an
account at all branches located in “Brooklyn” city.
• Solution : CONT….
• R2 🡨∏customer-name, branch-name (depositor ⋈ account)
Customer-name Branch-name
Hayes Perry ridge
Johnson Downtown
Johnson Brighton
Jones Brighton
Lindsay Redwood
Smith Mianus
Turner Round Hill

95
Relational Algebra Operators
• Query : Find all customer names who have an
account at all branches located in “Brooklyn” city.
• Solution : CONT….
• R 3🡨 R2 ÷ R1
Customer- Branch-
Customer- name name
Hayes Perry ridge
name Johnson Downtown
Johnson Johnson
Jones
= Brighton
Brighton
÷
Lindsay Redwood
Smith Mianus
Turner Round Hill

96
 Extended Relational Algebra operators
• 1. Insertion :
– To insert a tuple in the existing relation.
Example :

account ← account ∪ {(“Perryridge”, A-973, 1200)}

depositor ← depositor ∪ {(“Smith”, A-973)}

97
 Extended Relational Algebra operators
• 2. Update: (Generalized Projection)
– To modify selected tuple of relation.
Syntax :
R 🡨 ∏ F1, F2, …, Fn (r)
Where F1,F2,... Can attribute or formula
Example:
– Make interest payments by increasing all balances
by 5 percent.
account ← ∏ AN, BN, BAL * 1.05 (account)

98
 Extended Relational Algebra operators
• 2. Delete:
– To delete selected tuple from relation.
Syntax :
R 🡨R-E
Example:
Delete all account records in the Perryridge branch.
account
account ← account – σ branch-name = “Perryridge” (account)

99