Unit - 2

1
• Data Modelling using the Entity-Relationship(ER)
model: Using High-Level conceptual Data Models for
Database Design, A sample Database Application,
Entity types, Entity Sets, Attributes and Keys,
Relationship Types, Relationship Sets, Roles and
Structural Constraints, Weak Entity types. Refining
the ER Design, ER Diagrams, Relationship Types of
Degree Higher than two, Relational Database Design
using ER-to-Relational Mapping.
• Relational Algebra: Unary Relational Operations,
SELECT and PROJECT, Relational Algebra Operations
from Set Theory, Binary Relational Operations: JOIN
and DIVISION, Aggregate functions and Grouping

2
 Sample Database Application
• Example: HOD of CSE department calls you and asks to develop the
following application
“Develop a database application to automate the process of course
registration”
• Your first task should be to have a discussion with your client i.e HOD, to
identify the requirements of the application.
– Requirements are as follows
• Department has students and faculty
• Department will offer a set of courses during each semester
• Each student in the department during course registration will be opting for courses offered by the
department
• Faculty will be handling courses

• Your second task should be to prepare a ER diagram which shows the

Design plan of the database application to be developed.

3
Example: House design plan

During plan preparation

Various notations and
Terminologies

4
 Design plan or ER diagram before actual development of the application

Course
Course
Code
USN Name Name

Opting
Student Courses

has
Handles
Offers

has Faculty
Department

ID Name
Name

5
Design plan or ER diagram before actual development of the application

Attribute

Entity

Relationship

Requirements Were
- Department has students and faculty
- Department will offer a set of courses during each semester
- Each student in the department during course registration will be
opting for courses offered by the department
- Faculty will be handling courses
6
Design plan or ER diagram before actual development of the application

Constraints
- Each semester student
should register for minimum
of 20 credits and maximum
of 30 credits
- Each faculty can handle
a maximum two courses
During each semester

Requirements Were
- Department has students and faculty
- Department will offer a set of courses during each semester
- Each student in the department during course registration will be
opting for courses offered by the department
- Faculty will be handling courses
7
 What is Entity, Entity Types, Entity sets ?

• Entity is object in real world which has

independent existence.
– Example: Student, Course, Faculty, Car, House,
College, Book, Food
Student Courses

ER Diagram Notation for Entity: Rectangle

8
List out the Entities which you will come across in
real world.

9
 What is Entity Types, Entity sets ?

• Entity type is collection of entities with common attributes

• Entity set collection of one or more attributes
Student Entity Type
USN Name Email ID Mobile No. DOB

1BM14CS001 Aditya aditya@[Link] 9448444160 1-1-1997

1BM14CS002 Bharath bharath@[Link] 8762244699 31-12-1996

Entity Set

10
 What is Attribute ?

• Attribute is a property that describes Entity.

Attributes:
USN, Name, Email ID,
Mobile Number, DOB
Student Entity Type
USN Name Email ID Mobile No. DOB

1BM14CS001 Aditya aditya@[Link] 9448444160 1-1-1997

1BM14CS002 Bharath bharath@[Link] 8762244699 31-12-1996

Entity Set What is Domain ?

Set of permitted values for an
Attribute.

11
 What is Attribute ?

• Attribute is a property that describes Entity.

Attributes:
USN, Name, Email ID,
Student Entity Type Mobile Number, DOB
USN Name Email ID Mobile No. DOB

1BM14CS001 Aditya aditya@[Link] 9448444160 1-1-1997

1BM14CS002 Bharath bharath@[Link] 8762244699 31-12-1996

Entity Set ER Diagram Notation for Attribute:

Email ID Mobile
USN Name No.

Student DOB

12
List attributes for
1. Car
2. Book

13
 Different categories of attributes

• Simple (Atomic) vs Composite

– Attributes that are not divisible are called Simple.
– Attributes that can be divided into smaller parts are called Composite.
– Ex.: Simple- USN, Composite- Name(First Name, Last Name), Address(Street
name, Area Name, Place)
• Single value vs Multiple valued
– Ex.: Single – USN, Multiple – Mobile numbers
• Stored vs Derived
– Ex.: Stored – DOB, Derived – Age
• Complex Attributes
– Ex.: {Address(Street name, Area Name, Place)} Office address and Residence
Address
• Null values
– Ex. : Middle Name is Optional i.e. Gautham Sharma or Gautham K Sharma
14
Attribute: ER diagram notations
Attribute

…..
Composite Attribute

Multivalued Attribute

Derived Attribute

15
 What is Key ?
• Key attribute is a attribute or a combination of attributes
which will uniquely identify remaining attributes of entity.
• What are the Key attributes in the following student table ?

USN Name Email ID Mobile No. DOB

1BM14CS001 Aditya aditya@[Link] 9448444160 1-1-1997

1BM14CS002 Bharath bharath@[Link] 8762244699 31-12-1996

16
 What is Key ?
• Key attribute is a attribute or a combination of attributes
which will uniquely identify remaining attributes of entity.
• What are the Key attributes in the following student table ?

USN Name Email ID Mobile No. DOB

1BM14CS001 Aditya aditya@[Link] 9448444160 1-1-1997

1BM14CS002 Bharath bharath@[Link] 8762244699 31-12-1996

ER Diagram Notation for Entity: Rectangle USN

17
 What is Key ?
• Key attribute is a attribute or a combination of attributes
which will uniquely identify remaining attributes of entity.
• What are the Key attributes in the following student table ?

USN Name Email ID Mobile No. DOB

1BM14CS001 Aditya aditya@[Link] 9448444160 1-1-1997

1BM14CS002 Bharath bharath@[Link] 8762244699 31-12-1996

1BM14CS003 Dinesh K dineshk@[Link] 8444160944 6-6-1998

1BM14CS004 Dinesh K Dineshk_4@[Link] 7446998762 6-6-1998

18
 What is Key ?
• Key attribute is a attribute or a combination of attributes
which will uniquely identify remaining attributes of entity.
• What are the Key attributes in the following student table ?

Faculty Department Salary

A CSE 20K

B CSE 21K

A EC 30K

B EC 27K

C CSE 22K

19
 What is Key ?
• Key attribute is a attribute or a combination of attributes
which will uniquely identify remaining attributes of entity.
• What are the Key attributes in the following student table ?

Faculty Department Salary

A CSE 20K

B CSE 21K

A EC 30K

B EC 27K

C CSE 22K

20
 What is Key ?
• Key attribute is a attribute or a combination of attributes
which will uniquely identify remaining attributes of entity.
• What are the Key attributes in the following student table ?

Faculty Department Salary

What is the Salary of Employee
A CSE 20K
whose name is A ?
B CSE 21K

A EC 30K

B EC 27K

C CSE 22K

21
 What is Key ?
• Key attribute is a attribute or a combination of attributes
which will uniquely identify remaining attributes of entity.
• What are the Key attributes in the following student table ?

Faculty Department Salary

What is the Salary of Employee
A CSE 20K
whose name is A ?
B CSE 21K

A EC 30K

B EC 27K Note:
Before determining the Key
C CSE 22K
attribute, look at the values
present but it will
not be consistent
22
 Activity To DO
Attribute Represent Employee entity
using ER diagram notation
….. Which has following attributes
- Employee ID (Key)
Composite Attribute - Name
- Address(House No., Street
name, Area name, Place)
- Mobile Number (Can have
Multivalued Attribute
more than one )
- DOB
- Age
Derived Attribute

Entity

______ Key Attribute

23
 Relationship Type
• A Relationship Type defines a relationship set among entities of certain
entity types.
• Example, an Faculty works_for a department, a student enrolls_for in a
course. Here, works_for and enrolls_for are called relationships.
relationship

Works_for
Faculty Department

Enrolls_for
Student Course

24
 Relationship Type
• A Relationship Type defines a relationship set among entities of certain entity types.
• Example, an Faculty works_for a department, a student enrolls_for in a course. Here,
works_for and enrolls_for are called relationships.

Dr. A
CSE
Dr. B

Dr. C
ISE

Dr. D

Faculty
Department
Entity Works_for
Entity
relationship
25
 Relationship Type
• A Relationship Type defines a relationship set among entities of certain entity types.
• Example, an Faculty works_for a department, a student enrolls_for in a course. Here,
works_for and enrolls are called relationships.

Avinash
DBMS
Balaji

Chandan
Java

Dinesh

Student
Course
Entity enrolls_for
Entity
relationship
26
 Relationship Set
• An Relationship Set is a collection of relationships all belonging to one relationship type.

Here relationship set,

has 4 relationships

Avinash
DBMS
Balaji

Chandan
Java

Dinesh

Student
Course
Entity enrolls_for
Entity
relationship
27
 Relationship
• The association among entities is called a relationship. or A
Relationship is one instance in a Relationship Set.

Avinash
DBMS
Balaji

Chandan
Java

Dinesh

Student
Course
Entity enrolls_for
Entity
relationship
28
 Relationship Degree
• Binary Relationship: Degree two, two entities
are participating
Dr. A
CSE
Dr. B

Dr.C
ISE

Dr. D

Faculty
Department
Entity Works_for
Entity
relationship
29
 Relationship Degree
• Ternary Relationship: Degree three, three entities are
participating
Micro Systems
Pvt. Ltd
Laptop
UNIQ systems Manufacturing
Pvt. Ltd
Supplier
Entity Desktop
Hard disk Manufacturing

Keyboard
Project
Part supplies
Entity
Entity relationship
30
 Relationship Degree
• Ternary Relationship: Degree three, three entities are
participating
Micro Systems
Pvt. Ltd

UNIQ systems Laptop

Pvt. Ltd Manufacturing
Supplier
Entity
Desktop
Hard disk Manufacturing

Keyboard
Project
Part supplies
Entity
Entity relationship
31
 Recursive Relationship
• In some cases the same entity type participates in more than once in a
relationship type in different roles.

Dr. A Supervisor

Dr. B Subordinate

Dr. C Subordinate

Faculty
Entity supervision
relationship
32
 Recursive Relationship
• In some cases the same entity type participates in more than once in a
relationship type in different roles.

Subordinate
Dr. A
Supervisor

Dr. B

Dr. C

Supervisor
Principal

Faculty
Entity supervision
relationship
33
 Relationship Constraints or Structural Constraints

Two Types
1. Cardinality Ratios
a. One to one (1:1)
b. One to Many (1:M)
c. Many to Many (N:M)
2. Participation Constraints
a. Total
b. Partial

34
 Cardinality Ratios
• Cardinality is a constraint on a relationship specifying the number of entity
instances that a specific entity may be related to via the relationship.
One to One:
One entity from entity set A can be associated with at most one entity of
entity set B and vice versa.

35
 Cardinality Ratios
• Cardinality is a constraint on a relationship specifying the number of entity
instances that a specific entity may be related to via the relationship.
One to One:
One entity from entity set A can be associated with at most one entity of
entity set B and vice versa.

Dr. A
CSE

Dr. B ISE

Faculty Department
Heads
Entity Entity
relationship
36
 Cardinality Ratios
• Cardinality is a constraint on a relationship specifying the number of entity
instances that a specific entity may be related to via the relationship.
One to One:
One instance of one entity type can participate in one instance of other entity
type.

1 1
Faculty Heads Department

37
 Cardinality Ratios
• Cardinality is a constraint on a relationship specifying the number of entity
instances that a specific entity may be related to via the relationship.
One to Many:
One entity from entity set A can be associated with more than one entities of entity
set B however an entity from entity set B, can be associated with at most one entity.

1 M
Entity A R Entity B

38
 Cardinality Ratios
• Cardinality is a constraint on a relationship specifying the number of entity
instances that a specific entity may be related to via the relationship.
One to Many:
One entity from entity set A can be associated with more than one entities of entity
set B however an entity from entity set B, can be associated with at most one entity.

Departme 1 M
Has Student
nt

39
 Cardinality Ratios
• Cardinality is a constraint on a relationship specifying the number of entity
instances that a specific entity may be related to via the relationship.
Many to Many:
One entity from A can be associated with more than one entity from B and vice versa.

N M
Entity A R Entity B

40
 Cardinality Ratios
• Cardinality is a constraint on a relationship specifying the number of entity
instances that a specific entity may be related to via the relationship.
Many to Many:
One entity from A can be associated with more than one entity from B and vice versa.

41
 Cardinality Ratios
• Cardinality is a constraint on a relationship specifying the number of entity
instances that a specific entity may be related to via the relationship.
Many to Many:
One entity from A can be associated with more than one entity from B and vice versa.

42
 Participation Constraint
• Minimum number of relationship instance that each entity can participate
in.
• Total Participation − Each entity is involved in the relationship. Total
participation is represented by double lines.
• Partial participation − Not all entities are involved in the relationship.
Partial participation is represented by single lines.

43
 Participation Constraint
• Minimum number of relationship instance that each entity can participate in.
• Total Participation − Each entity is involved in the relationship. Total participation
is represented by double lines.
• Partial participation − Not all entities are involved in the relationship. Partial
participation is represented by single lines.

44
 Participation Constraint
• Minimum number of relationship instance that each entity can participate in.
• Total Participation − Each entity is involved in the relationship. Total participation
is represented by double lines.
• Partial participation − Not all entities are involved in the relationship. Partial
participation is represented by single lines.

What is wrong in following relationship representation ?

45
 Participation Constraint
• Minimum number of relationship instance that each entity can participate in.
• Total Participation − Each entity is involved in the relationship. Total participation
is represented by double lines.
• Partial participation − Not all entities are involved in the relationship. Partial
participation is represented by single lines.

46
 Weak Entity
• A weak entity set is one which does not have any primary key associated
with it.
• It relies on a combination of its attributes and the primary key of a related
strong entity to create a unique identifier
• Example : Installment entity: An installment entity can only exist if a loan
entity exists.
•

47
 Weak Entity
• A weak entity set is one which does not have any primary key associated
with it.
• A weak entity type normally has partial key which is the set of attributes
that can uniquely identify weak entities that are related to same owner
entity.

48
 ER Diagram Symbols
Attribute

…..
Composite Attribute

Multivalued Attribute

Derived Attribute

Entity Weak Entity

______ Key Attribute ____ Partial Key

49
ER Diagram Symbols
Relationship

Identifying Relationship

Total Participation of Entity A in R

Cardinality Ratio 1:N for Entity A : B in R

50
 Unit 2:
ER diagram design for a given requirements

51
 Database Design
Database design: Why do we need it ?
• Agree on structure of the database before deciding on a particular implementation

Consideration Issues such as:

• What entities to model
• How entities are related
• What constraints exist in the domain
• How to achieve good design

ER diagram is a conceptual design of database

52
 Database Design Process
Requirement Conceptual Logical, Physical,
Analysis Design Security etc.,

Requirement Analysis
- What is going to be stored ?
- How is it going to be used ?
- What are we going to do with the data ?
- Who should access the data ?

Technical and Non-technical

people are involved

53
 Database Design Process
Requirement Conceptual Logical, Physical,
Analysis Design Security etc.,

Conceptual Design
- A high-level description of the database.
- Sufficiently precise that technical people can understand it.
- But, not so precise that non-technical can not participate.

This is were Entity-Relationship (ER)

Diagram fits in.

54
 Database Design Process
Requirement Conceptual Logical, Physical,
Analysis Design Security etc.,

ER diagram is visual syntax for DB design which

is precise enough for technical points, but abstracted
enough for non-technical people.

55
Main phases of Database Design

56
 ER Diagram Symbols
Attribute

…..
Composite Attribute

Multivalued Attribute

Derived Attribute

Entity Weak Entity

______ Key Attribute ____ Partial Key

57
ER Diagram Symbols
Relationship

Identifying Relationship

Total Participation of Entity A in R

Cardinality Ratio 1:N for Entity A : B in R

58
 Activity: ER diagram
Decision: Relationship vs Empty

Question 1: What does the following ER diagram say ?

59
 Activity: ER diagram
Decision: Relationship vs Empty

Question 2: What does the following ER diagram say ?

60
 Activity: ER diagram
Decision: Relationship vs Empty
Question 3: What does the following ER diagram say ?

61
 Activity: ER diagram design
Draw ER diagram for the following requirements
Universal Records has decided to store information about musicians who perform on its albums
(as well as other company data) in a database.
• Each musician that records at Universal has an ID(Key) and name.
• Each instrument used in songs recorded at Universal has a unique identification number
(Key) , a name and a musical key.
• Each album recorded on the Universal label has a unique identification number (Key) , a title,
a copyright date and a format.
• Each song recorded at Universal has a Song ID (Key), a title and an author.
• Each musician may play several instruments, and a given instrument may be played by
several musicians.
• Each album has a number of songs on it, but no song may appear on more than one album.
• Each song is performed by one or more musicians, and a musician may perform a number of
songs.
• Each album has exactly one musician who acts as its producer. A musician may produce
several albums, of course.

62
Each musician that records at Universal has an ID (Key) and name.

63
Each instrument used in songs recorded at Universal has a unique identification
number (Key) , a name and a musical key.

64
Each album recorded on the Universal label has a unique identification number (Key) ,
a title, a copyright date and a format.

65
Each song recorded at Universal has a Song ID (Key), a title and
an author.

66
Each musician may play several instruments, and a given instrument may be
played by several musicians.

67
Each album has a number of songs on it, but no song may appear on more
than one album.

68
Each song is performed by one or more musicians, and a musician may
perform a number of songs.

M 1
M

N
N N

69
Each album has exactly one musician who acts as its producer. A musician
may produce several albums, of course

1 M

M M 1

N
N
M

70
• An ER diagram is a pictorial representation of the
information that can be captured by a database.
Such a “picture” serves two purposes:
– It allows database professionals to describe an overall design concisely
yet accurately.
– (Most of) it can be easily transformed into the relational schema.
• The ER diagram represents the conceptual level of database
design meanwhile the relational schema (or database table) is
the logical level for the database design.

71
 Converting ER diagram to Tables
• Entities and Simple Attributes
– An entity type within ER diagram is turned into a table.
– Each attribute turns into a column (attribute) in the table. The key
attribute of the entity is the primary key of the table which is usually
underlined.
USN DOB
Name

Student
Student Table
USN Name DOB
Primary
1BM14CS001 Aditya 1-1-1997
Key
1BM14CS002 Bharath 31-12-1996

72
 Converting ER diagram to Tables
• Multi-Valued Attributes
USN DOB
Name

Mobile
Student

73
 Converting ER diagram to Tables
• Multi-Valued Attributes USN DOB
Name

Mobile
Student
Which of the following representation of the table for Multivalued Attribute is best ?

USN Name DOB Mobile 1 Mobile 2

1BM14CS001 Aditya 1-1-1997 8766655433

1BM14CS002 Bharath 31-12-1996 9762255433 7066722433

Student Table Mobile Table

USN Mobile
USN Name DOB
1BM14CS001 8766655433
1BM14CS001 Aditya 1-1-1997
1BM14CS002 9762255433
1BM14CS002 Bharath 31-12-1996
1BM14CS002 7066722433
74
 Converting ER diagram to Tables
• Multi-Valued Attributes USN DOB
Name
Primary
Key Mobile
Student

Student Table Mobile Table

USN Mobile
USN Name DOB Foreign
Key 1BM14CS001 8766655433
1BM14CS001 Aditya 1-1-1997
1BM14CS002 9762255433
1BM14CS002 Bharath 31-12-1996
1BM14CS002 7066722433

If you have a multi-valued attribute, take the attribute and turn it into a
new entity or table of its own. Then make a 1:N relationship between
the new entity and the existing one. In simple words,
1. Create a table for the attribute.
2. Add the primary (id) column of the parent entity as a foreign key
within the new table
75
 Converting ER diagram to Tables
Street Area
Name Name

• Composite AttributeDOB Address Place

USN Name

Student Mobile

76
 Converting ER diagram to Tables
Street Area
Name Name

• Composite AttributeDOB Address Place

USN Name

Student Mobile

Student Table
USN Name DOB Street Area Place
1BM14CS001 Aditya 1-1-1997 RK Road Nagar Mandya

1BM14CS002 Bharath 31-7-1996 S V Road Layout Kolar

USN Mobile
Mobile Table 1BM14CS001 8766655433

1BM14CS002 9762255433

1BM14CS002 7066722433
77
 Converting ER diagram to Tables
Street Area
Name Name

• Derived Attribute DOB Address Place

USN Name

Age Student Mobile

Student Table
USN Name DOB Street Area Place
1BM14CS001 Aditya 1-1-1997 RK Road Nagar Mandya

1BM14CS002 Bharath 31-7-1996 S V Road Layout Kolar

USN Mobile
Mobile Table 1BM14CS001 8766655433

1BM14CS002 9762255433

1BM14CS002 7066722433
78
 Converting ER diagram to Tables
• Relationship: One-to-One

D-ID
F-ID 1
1

F-Name
D-Name

79
 Converting ER diagram to Tables
• Relationship: One-to-One
D-ID
F-ID 1
1

F-Name
D-Name

Approach 1: Foreign Key

Faculty Table Department Table
F-ID F-Name
D-ID D-Name Heading
1 Dr. H S Guruprasad F-ID
2 Dr. Umadevi V 10 CSE 1
3 Dr. Gowrishankar 20 ISE 3

80
 Converting ER diagram to Tables
• Relationship: One-to-One
D-ID
F-ID 1
1

F-Name
D-Name

Approach 1: Foreign Key

Department Table
Primary Primary Foreign
Key Faculty Table Key Key
F-ID F-Name
D-ID D-Name Heading
1 Dr. H S Guruprasad F-ID
2 Dr. Umadevi V 10 CSE 1
3 Dr. Gowrishankar 20 ISE 3

81
 Converting ER diagram to Tables
• Relationship: One-to-One
D-ID
F-ID 1
1

F-Name
D-Name
Approach 2: Merged Relation approach
Merging two entity types and relationship into one single relation.
This may be appropriate when both participations are total

Faculty –Department Table

F-I F-Name D-ID D-Name HOD
D
1 Dr. H S Guruprasad 10 CSE Yes
2 Dr. Umadevi V 10 CSE No
3 Dr. Gowrishankar 20 ISE Yes
82
 Converting ER diagram to Tables
• Relationship: One-to-One
D-ID
F-ID
1 1

F-Name
D-Name
Approach 3: Cross-reference or relationship relation approach

Faculty Table Department Table HOD Table

F-ID F-Name D-ID D-Name D-ID F-ID
1 Dr. H S Guruprasad 10 1
10 CSE
2 Dr. Umadevi V 20 3
20 ISE
3 Dr. Gowrishankar
Lookup Table

83
 Converting ER diagram to Tables
• Relationship: One-to-Many

USN
D-ID M
Departme 1 Student
Has
nt
D-Name
S-Name

84
 Converting ER diagram to Tables
• Relationship: One-to-Many
USN
D-ID M
Departme 1 Student
Has
nt
D-Name
S-Name
Approach 1 Student Table
Department Table USN S-Name D-ID
D-ID D-Name 1BM14CS001 Akash 10
10 CSE 1BM14CS002 Bharath 10
20 ISE 1BM14CS003 Ragu 10
1BM14CS004 Mohan 20
1BM14CS005 Nikil 20

85
 Converting ER diagram to Tables
• Relationship: One-to-Many
USN
D-ID M
Departme 1 Student
Has
nt
D-Name
S-Name
Approach 2 Student Table Department
Department Table USN S-Name Student Table
USN D-ID
D-ID D-Name 1BM14CS001 Akash
1BM14CS002 Bharath 1BM14CS001 10
10 CSE
1BM14CS003 Ragu 1BM14CS002 10
20 ISE
1BM14CS004 Mohan 1BM14CS003 10
1BM14CS005 Nikil 1BM14CS004 20
1BM14CS005 20
86
 Converting ER diagram to Tables
• Relationship: Many-to-Many

USN C-ID
Student Enrolls_for Course
S-Name C-Name
87
 Converting ER diagram to Tables
• Relationship: Many-to-Many
USN C-ID
Enrolls_for
Student Course
S-Name C-Name

Student Table Course Table Student

Course Table
USN S-Name C-ID C-Name
USN C-ID
1BM14CS001 Avinash 10 DBMS
1BM14CS001 10
1BM14CS002 Balaji 20 Java
1BM14CS001 20
1BM14CS003 Chandan
1BM14CS002 20
1BM14CS003 10

88
 Converting ER diagram to Tables
• Relationship: Many-to-Many
USN C-ID
Enrolls_for
Student Course
S-Name C-Name

Student Table Course Table Student

Course Table
USN S-Name C-ID C-Name
USN C-ID
1BM14CS001 Avinash 10 DBMS
1BM14CS001 10
1BM14CS002 Balaji 20 Java
1BM14CS001 20
1BM14CS003 Chandan
1BM14CS002 20
1BM14CS003 10

89
 Converting ER diagramPartial
to Tables
Name
Key -----
F-ID F-Name

Faculty Dependents_of
Dependent

Owner
Identifying Weak
Entity
Relationship Entity

90
 Converting ER diagramPartial
to Tables
Name
Key -----
• Weak Entity
F-ID F-Name

Faculty Dependents_of
Dependent

Owner
Identifying Weak
Entity
Relationship Entity

Faculty Table Dependent Table

F-ID F-Name F-ID Dependent

Name
10 Dr. Guruprasad 10 Ram
20 Dr. Gowrishankar 10 Ravi
20 Ram

91
 Activity To Do
Convert the Following ER diagram to Database table schema

1 M

M M 1

N
N
M

92
 Database
Musician Table
tables or Schema Diagram
Name Muscian-ID
Album Table
Album-ID Copyright Format Title Producer
Date ID

Song Table
Song-ID Author Title Album-ID

Performing Table
Muscian-ID Song-ID
Plays Table
Muscian-ID Instr-ID
Instrument Table
Instr-ID Name M-Key

93
 Database
Musician Table
tables or Schema Diagram
Name Muscian-ID
Album Table
Album-ID Copyright Format Title Producer
Date ID

Song Table
Song-ID Author Title Album-ID

Performing Table
Muscian-ID Song-ID
Plays Table
Muscian-ID Instr-ID

Instrument Table
Instr-ID Name M-Key

94
 Unit 2:
Introduction to Relational Algebra

95
 Why you should Learn “Relational Algebra” ?

Relational Algebra is
• Core of Relational Query Language, Example: SQL
• Provides framework for Query implementation and optimization
• Is a mathematical language for manipulating relations.

96
 Relational Algebra based on Relational Model

student_info Won Turing

USN Name award 1981
1BM14CS001 Arjun
1BM14CS002 Balaji Relational model due to Edgar Teds Codd,
a mathematician at IBM in 1970

97
 Relational Algebra based on Relational Model

student_info Won Turing

USN Name award 1981
1BM14CS001 Arjun
1BM14CS002 Balaji Relational model due to Edgar “Ted” Codd,
a mathematician at IBM in 1970

Column or Attribute

Row USN Name Table

or
1BM14CS001 Arjun or
Tuple
Relation
or 1BM14CS002 Balaji
Record
Database table Name: student_info

98
 RDBMS Architecture
Relational Database Management System (RDBMS)

How does a SQL engine work ?

SQL Relational Optimized

Algebra (RA) Execution
Query RA Plan
Plan

Declarative query Find logically Execute each

Translate to relational
(from user) equivalent- but more operator of the
algebra expression
efficient- RA expression optimized plan!

99
 RDBMS Architecture
Relational Database Management System (RDBMS)

How does a SQL engine work ?

Relational Algebra allows us to translate declarative (SQL) queries

into precise and optimizable expressions!

100
 What is an “Algebra”
• Mathematical system consisting of:
– Operands: variables or values from which new values can be
constructed.
– Operators: symbols denoting procedures that construct new values
from given values.

101
 What is Relational Algebra (RA) ?
• An algebra whose operands are database tables or relations or variables
that represent relations.
• Operators are designed to do the most common things that we need to do
with relations in a database.
– The result is an algebra that can be used as a query language for relations.
Relational Algebra Operations

102
 RA operates on sets!
• RDBMSs use multisets, however in relational algebra formalism we will
consider sets!

• Also: we will consider the named perspective, where every attribute must
have a unique name
– attribute order does not matter…

103
 Unary Relational Operations
• Select Operation (σ sigma)
• Project Operation (∏ phi)

104
 Select Operation (σ sigma)
• It selects tuples that satisfy the given predicate from a relation.
• Select written as

Notation σselection condition(R)

• Where σ stands for selection predicate
• R stands for relation / table name
• Selection condition is prepositional logic formula which may use
connectors like and, or, and not. These terms may use relational operators
like − =, ≠, ≥, < , >, ≤.

105
 Select Operation (σ)
Select Operation (σ)
Returns all tuples which satisfy a condition
SQL:
σselection condition(R)
SELECT *
FROM Student
Student WHERE marks > 60;
Equivalent
Relational Algebra
Expression

106
 Select Operation (σ)
Select Operation (σ)
Returns all tuples which satisfy a condition
SQL:
σselection condition(R)
SELECT *
FROM Student
Student WHERE marks > 60;

Relational Algebra Expression

107
 Select Operation (σ)
Select Operation (σ)
Returns all tuples which satisfy a condition
SQL:
σselection condition(R)
SELECT *
FROM Student
Student WHERE name=‘Avinash’;

Relational Algebra Expression

108
 Project Operation (∏ phi)
• It projects column(s) that satisfy a given predicate.
• Written as
Notation ∏A1,…., An (R)
• Where A1, A2 , An are attribute names of relation R.
• Duplicate rows are automatically eliminated, as relation is a
set.

109
 Project Operation (∏ phi)
• Eliminates columns, then removes duplicates
SQL:
• Written as
Notation ∏A1,…., An (R) SELECT DISTINCT
dep_num
Student FROM Student;

Relational Algebra Expression

dep_nu
m
10
20

110
 Project Operation (∏ phi)
• Eliminates columns, then removes duplicates
• Written as SQL:
Notation ∏A1,…., An (R) SELECT DISTINCT
name, dep_num
Student FROM Student;

Relational Algebra Expression

111
 Note that RA Operators are Compositional!
Student

SELECT DISTINCT
name, marks
FROM Student
WHERE marks > 60;

How do we represent this query in

Relational Algebra?

112
 Note that RA Operators are Compositional!
Student

SELECT DISTINCT
name, marks
FROM Student
WHERE marks > 60;

How do we represent this query in

Relational Algebra?

113
 Note that RA Operators are Compositional!
Student

SELECT DISTINCT
name, marks
FROM Student
WHERE marks > 60;

How do we represent this query in

Relational Algebra?
Are these logically equivalent?

114
 Note that RA Operators are Compositional!
Student

SELECT DISTINCT
name, marks
FROM Student
WHERE marks > 60;

How do we represent this query in

Relational Algebra?
Are these logically equivalent?

115
 Question
Student

What is the output of following

relational algebra query ?
SELECT DISTINCT
name, marks
FROM Student
WHERE marks > 60;

116
 Question
Student

What is the output of following

relational algebra query ?
SELECT DISTINCT
name, marks
FROM Student
WHERE marks > 60;

117
 Question
Student

What is the output of following

relational algebra query ?
SELECT DISTINCT
usn, name, marks
FROM Student
WHERE marks > 60;

118
 Question
Student

What is the output of following

relational algebra query ?
SELECT DISTINCT
usn, name, marks
FROM Student
WHERE marks > 60;

119
 Question
• Consider the relational schema Schedule containing Theater name, Movie
Title and timing at which movie will be played. Time in the Schedule table
is stored in 24Hr format i.e., 6:00pm will be stored as 18:00
Schedule(Theater, MovieTitle, Time)

Write the following query both in Relational Algebra and SQL.

List the theater name and time where we can watch the movie “FndingNemo" after
3pm.

120
 Question
• Consider the relational schema Schedule containing Theater name, Movie
Title and timing at which movie will be played. Time in the Schedule table
is stored in 24Hr format i.e., 6:00pm will be stored as 18:00
Schedule(Theater, MovieTitle, Time)

Write the following query both in Relational Algebra and SQL.

List the theater name and time where we can watch the movie “FndingNemo" after
3pm.

SELECT Theater, Time

FROM Schedule
WHERE MovieTitle = ’FindingNemo’ AND time >= 15:00;

121
Database Philosophy

Won Turing
award 1981

Edgar Ted Codd

122
 Rename Operation (ρ rho)
• The rename operation allows us to rename either relation
name or attribute names or both.
• Written as
Notation
ρS(B1,…., Bn) (R) or ρS(R) or ρ(B1,…., Bn) (R)

• S is the new relation name and B1, B2….Bn are new attribute
names.

123
 Rename Operation (ρ rho)
•
SQL:
The rename operation allows us to rename the either relation name or attribute names or
Student
both. SELECT
usn as Student_USN
name as Student_Name
dep_num as Department_Number
FROM Student;

ρStudent_USN, Student_Name, Department_Number (Student)

Student

124
Binary Operations

125
• The CARTESIAN PRODUCT or CROSS JOIN returns the Cartesian product of the sets of
records from the two or more joined tables.
• Notation: table1 × table2
table1 table2
ID M ID N Relational Algebra Expression
1 a 2 p table1 x table2
2 b 3 q
4 c 5 r

126
 Join Operation
Written as R ⋈joincondition S
Emp_Dept Dept

Emp_ID D_ID D_num D_name

121 2 1 Sale
240 4 2 Account
4 Marketing

Emp_Dept ⋈D_ID=D_num Dept

Emp_ID D_ID D_num D_name
121 2 2 Account
240 4 4 Marketing

127
• Join Written as R ⋈joincondition S
• JOIN operation is used to combine related records from two tables into a single
records
• A general join condition is of the form
<condition> AND <condition> AND……AND <condition>
• Where each condition is of the form Ai θ Bj , Ai is an attribute in relation R and Bj is
an attribute in relation S, Ai and Bj have same domain
– It is called a theta join, if θ is one of the comparison operators {< , > , = , ≠ , ≤ , ≥}.
– Once again, if θ is = it's called an equijoin, and
– if the equijoin is on same-named attributes it's called a natural join and written as *.
• Similarly, there are left, right, and full outer joins; written as ⟕, ⟖, and
⟗ respectively.

128
 Join Operations
Join

Inner Join Outer Join

Left Outer Join ⟕

Right Outer Join ⟖

Natural Join(*) (common attribute Full Outer Join ⟗

names and domain
should exist between
two tables)

129
 Equi Join
table1 table2
num M ID N
num M ID N
1 a 2 p
2 b 2 p
2 b 3 q
4 c 5 r
Select *
From table1, table 2
Where num=id;

What is the equivalent

relational algebra expression ?

130
 Equi Join
Select *
From table1, table 2
table1 table2 Where num=id;
num M ID N
1 a 2 p
num M ID N
2 b 3 q
2 b 2 p
4 c 5 r

result
num M ID N
2 b 2 p

131
 table1 table2
Question
result
num M ID N
num M ID N
1 a 2 p
2 b 2 p
2 b 3 q
4 c 5 r

Are these logically equivalent?

132
 Natural Join
table1 table2
ID M ID N
1 a 2 p
2 b 3 q result
4 c 5 r ID M N
2 b p

133
table1 table2
Question
result
ID M ID N ID M N
1 a 2 p 2 b p
2 b 3 q
4 c 5 r

Are these logically equivalent?

134
What will be the output of following Relational Algebra
Expression

135
What will be the output of following Relational Algebra
Expression

136
 Natural Join
table1 table2
ID M ID N
1 a 2 p
2 b 3 q result
ID M N
4 c 5 r
2 b p

Natural join does not use any comparison operator. It does not
concatenate the way a Cartesian product does. We can perform a
Natural Join only if there is at least one common attribute that exists
between two relations. In addition, the attributes must have the same
name and domain. Natural join acts on those matching attributes
where the values of attributes in both the relations are same.

137
 Problem to solve
Consider three tables
• SAILORS(Sal_ID, SalName, Rating, Age)
• RESERVES(Sal_ID , Boat-ID, Rdate)
• BOATS(Boat-ID, BoatName, Color)

Write Relational Algebra express for the following

i. Find all the names of Sailors who have reserved boat with ID 2
ii. Find names of sialors who have reserved RED boat
iii. Find the colors of the boat reserved by Avinash

138
Problem to solve

139
Consider three tables
Problem to solve
SAILORS(Sal_ID, SalName, Rating, Age)
RESERVES(Sal_ID , Boat-ID, Rdate)
BOATS(Boat-ID, BoatName, Color)

Write Relational Algebra express for the following

i. Find all the names of Sailors who have reserved boat with ID 2

OUTPUT
--------
Balaji
Dinesh

140
Consider three tables
Problem to solve
SAILORS(Sal_ID, SalName, Rating, Age)
RESERVES(Sal_ID , Boat-ID, Rdate)
BOATS(Boat-ID, BoatName, Color)
Write Relational Algebra express for the following
i. Find all the names of Sailors who have reserved boat with ID 2

SELECT *
temp1 <- σBoat_ID=2 (RESERVES) FROM RESERVES
WHERE Boat_ID = 2;
temp1

141
Consider three tables
Problem to solve
SAILORS(Sal_ID, SalName, Rating, Age)
RESERVES(Sal_ID , Boat-ID, Rdate)
BOATS(Boat-ID, BoatName, Color)
Write Relational Algebra express for the following
i. Find all the names of Sailors who have reserved boat with ID 2

SELECT SAIL_ID
temp1 <- σBoat_ID=2 (RESERVES) FROM RESERVES
temp2 <- 𝞹SAL_ID(temp1) WHERE Boat_ID = 2;

temp2

142
 Problem to solve
i. Find all the names of Sailors who have reserved boat with ID 2

temp1 <- σBoat_ID=2 (RESERVES)

temp2 <- 𝞹SAL_ID(temp1)
temp3 <- SAILORS ⋈ SAILORS.Sal_ID=temp2.Sal_ID ( temp2)

SELECT *
temp3
FROM SAILORS
NATURAL JOIN
(SELECT SAL_ID
FROM RESERVES
WHERE Boat_ID = 2);

143
 Problem to solve
i. Find all the names of Sailors who have reserved boat with ID 2

temp1 <- σBoat_ID=2 (RESERVES)

temp2 <- 𝞹SAL_ID(temp1)
temp3 <- SAILORS ⋈ SAILORS.Sal_ID=temp2.Sal_ID ( temp2)
result <- 𝞹SAL_Name(temp3)

result SELECT SalName

FROM SAILORS
NATURAL JOIN
(SELECT SAL_ID
FROM RESERVES
WHERE Boat_ID = 2);
144
 Problem to solve
i. Find all the names of Sailors who have reserved boat with ID 2

temp1 <- σBoat_ID=2 (RESERVES)

temp2 <- 𝞹SAL_ID(temp1)
temp3 <- SAILORS ⋈ SAILORS.Sal_ID=temp2.Sal_ID ( temp2)
result <- 𝞹SAL_Name(temp3)

145
 SAILORS(Sal_ID, SalName, Rating, Age), RESERVES(Sal_ID , Boat-ID, Rdate),
BOATS(Boat-ID, BoatName, Color)
Write Relational Algebra express for the following
ii. Find names of sailors who have reserved RED boat

OUTPUT
--------
Balaji
Dinesh
146
 SAILORS(Sal_ID, SalName, Rating, Age), RESERVES(Sal_ID , Boat-ID, Rdate),
BOATS(Boat-ID, BoatName, Color)
Write Relational Algebra express for the following
ii. Find the colors of the boat reserved by Avinash

OUTPUT
--------
Red

147
 Join Operations
Join

Inner Join Outer Join

Left Outer Join ⟕

Right Outer Join ⟖

Natural Join(*) (common attribute Full Outer Join ⟗

names and domain
should exist between
two tables)

148
 Left Outer Join ⟕
result <- table1 ⟕ table2
table1 table2 result
ID M ID N ID M ID N
1 a 2 p 2 b 2 P
2 b 3 q 1 a
4 c 5 r 4 c

149
 Right Outer Join ⟖
result <- table1 ⟖ table2
table1 table2
result
ID M ID N
ID M ID N
1 a 2 p
2 b 2 P
2 b 3 q
3 q
4 c 5 r
5 r

150
 Full Outer Join ⟗
result <- table1 ⟗ table2
table1 table2 result
ID M ID N ID M ID N
1 a 2 p 2 b 2 p
2 b 3 q 3 q
4 c 5 r 5 r
1 a
4 c

151
 Relational Algebra Set Operations - semantics

Consider two relations R and S.

• UNION of R and S
the union of two relations is a relation that includes all the tuples that are either in R or in S
or in both R and S. Duplicate tuples are eliminated.
• INTERSECTION of R and S
the intersection of R and S is a relation that includes all tuples that are both in R and S.
• DIFFERENCE of R and S
the difference of R and S is the relation that contains all the tuples that are in R but that are
not in S.

For set operations to function correctly the relations R and S must be union compatible. Two
relations are union compatible if
• they have the same number of attributes
• the domain of each attribute in column order is the same in both R and S.

152
Set Operation – Union ∪

result

result <- R U S

153
Set Operation – Intersection ∩

result

result <- R ∩ S

154
Set Operation – Difference –

result <- R – S result <- S – R

155
 Problem to Solve: Writing Relational Algebra Expression

Consider the following three tables

• STUDENT(StudNum, StudName)
• PROJECT(ProjNum, ProjArea)
• ASSIGED_TO(StudNum,ProjNum)
i. Obtain student number and student name of all students who are working on both
the projects having project number 75 and 81
ii. Obtain student number and student name of all those students who do not work on
project number 68
iii. Obtain the student number and student name of all those students who are
working on project with name “Database”
iv. Obtain student number and student name of all students other than the student
with number 554 who works on atleast one project.

156
 Problem to Solve: Writing Relational Algebra Expression

Consider the following three tables

• STUDENT(StudNum, StudName), PROJECT(ProjNum, ProjArea),
ASSIGED_TO(StudNum,ProjNum)
i. Obtain student number and student name of all students who are working on both the projects
having project number 75 and 81 ASSIGNED_TO
STUDENT PROJECT
StudNum StudName ProjNum ProjArea StudNu Proj
m Num
554 Avinash 56 Java
554 56
555 Balaji 68 Database
555 68
556 Chandan 75 Database
556 75
557 Dinesh 81 Database
556 81
558 Harish
557 75

157
 Problem to Solve: Writing Relational Algebra Expression

i. Obtain student number and student name of all students who are working on both the projects
having project number 75 and 81

result
-----------------
556 Chandan

158
 Problem to Solve: Writing Relational Algebra Expression

i. Obtain student number and student name of all students who are working on both the projects
having project number 75 and 81

result
-----------------
556 Chandan

temp1 <- σProjNum=75(ASSIGNED_TO)

temp2 <- 𝞹StudNum(temp1)
temp3 <- σProjNum=81(ASSIGNED_TO)
temp4 <- 𝞹StudNum(temp3)

temp5 <- temp2 ∩ temp2

result <- STUDENT ⋈ [Link]=[Link] (temp5)

159
 Problem to Solve: Writing Relational Algebra Expression

ii. Obtain student number and student name of all those students who do not work on project
numberSTUDENT
68 ASSIGNED_TO
StudNum StudName StudNu Proj
m Num
554 Avinash
554 56
555 Balaji
555 68
556 Chandan
556 75
557 Dinesh
556 81
558 Harish
557 75 result
-----------------
554 Avinash
556 Chandan
557 Dinesh
558 Harish

160
 Problem to Solve: Writing Relational Algebra Expression

i. Obtain student number and student name of all those students who do not work on project
number 68 result
-----------------
554 Avinash
556 Chandan
557 Dinesh
558 Harish

temp1 <- σProjNum=68(ASSIGNED_TO)

temp2 <- 𝞹StudNum(temp1)
temp3 <- 𝞹StudNum(STUDENT)
temp4 <- temp3 – temp2

result <- STUDENT ⋈ [Link]=[Link] (temp4)

161
 Homework Problem : Writing Relational Algebra Expression

iii. Obtain the student number and student name of all those students who are working on
project with name “Database”

result
-----------------
555 Balaji
556 Chandan
557 Dinesh

162
 Homework Problem: Writing Relational Algebra Expression

iv. Obtain student number and student name of all students other than the student with number
554 who works on atleast one project.

result
-----------------
555 Balaji
556 Chandan
557 Dinesh

163
 Relational Algebra : Division Operation ÷

The division operator is used for queries which involve the ‘all’
qualifier such as
• “Which persons have a bank account at ALL the banks in the country?”
• “Which students are registered on ALL the courses given by Sonthos?”
• “Which students are registered on ALL the courses that are taught in period 1?”
• Find sailors who have reserved ALL boats

R ÷ S is used when we wish to express queries with “ALL”

164
 Relational Algebra : Division Operation ÷
The division operator takes as input two relations, called the dividend relation
(a on scheme A) and the divisor relation (b on scheme B) such that all the
attributes in B also appear in A and B is not empty. The output of the division
operation is a relation on scheme A with all the attributes common with B.

A B A÷B

165
 Relational Algebra : Division Operation ÷
The division operator takes as input two relations, called the dividend relation
(a on scheme A) and the divisor relation (b on scheme B) such that all the
attributes in B also appear in A and B is not empty. The output of the division
operation is a relation on scheme A with all the attributes common with B.

A B A÷B

166
Relational Algebra : Division Operation ÷
A B A÷B

167
 Relational Algebra : Division Operation ÷
Completed DBProject
Student Task Task
Feroz Database1 Database1
Feroz Database2
Database2
Feroz Compiler1
Eshwar Database1
Sara Database1
What is the Output of the following
Sara Database2
relational Algebra Expression
Eshwar Compiler1
Completed ÷ DBProject

168
 Relational Algebra : Division Operation ÷
Completed DBProject Completed ÷ DBProject
Student Task Task
Feroz Database1 Student
Database1
Feroz
Feroz Database2
Database2 Sara
Feroz Compiler1
Eshwar Database1
Sara Database1
Sara Database2
Eshwar Compiler1

169
 Relational Algebra : Division Operation ÷
Completed DBProject Completed ÷ DBProject
Student Task Task
F D1 Student
D1
F
F D2
D2
F C1
E D1
Is the following two relational
E C1
algebra expressions logically equivalent ?

T ← Completed ÷ DBProject

T₁ ← 𝞹Student(Completed)
T₂ ← T₁ × DBProject
T3 ← T₂ - Completed
T4 ← 𝞹Student (T3 )
T ← T₁ - T4

170
 Write relational Algebra Expresion
Find all bank customers who have account in all Branches of Bommasandra
Account Branch
branch_ acct_n
cid balance branch_id branch_city
id o
1 52 8103 43101.45 51 Belgaum

3 53 4826 752.80 52 Bijapur

1 53 7898 48206.10 53 Bommasandra

2 59 2135 468923.06 54 Hubli

1 59 1290 456.50 55 Bijapur

2 54 0073 1006.28 59 Bommasandra

Customer
cid c_name RESULT
1 Harish
c_name
2 Triveni
Harish
3 Eshwar

171
 Find all bank customers who have account in all Branches in Bommasandra

BommB ← 𝞹 branch_id(σbranch_city='Bommasandra'(Branch)) --find all branches located

in Bommansandra

BommB
branch_id
53
59
172
 Find all bank customers who have account in all Branches in Bommasandra

BommB ← 𝞹 branch_id(σbranch_city='Bommasandra'(Branch)) --find all branches located

in Bommansandra
CB ← 𝞹 c_name,branch_id(Customer * Account) -- find all customers' branches

BommB
CB
branch_id
53
59
173
 Find all bank customers who have account in all Branches in Bommasandra

BommB ← 𝞹 branch_id(σbranch_city='Bommasandra'(Branch)) --find all branches located

in Bommansandra
CB ← 𝞹 c_name,branch_id(Customer * Account) -- find all customers' branches

RESULT ← CB ÷ BinB -- divide to get those customers with an

account in every Bommasandra branch

BommB
CB RESULT
branch_id
53 c_name
59 Harish
174
175
 Query Tree Notation
• Query Tree
– An internal data structure to represent a query
– Standard technique for estimating the work involved in
executing the query, the generation of intermediate results, and
the optimization of execution
– Nodes stand for operations like selection, projection, join,
renaming, division, ….
– Leaf nodes represent base relations
– A tree gives a good visual feel of the complexity of the query
and the operations involved
– Algebraic Query Optimization consists of rewriting the query or
modifying the query tree into an equivalent tree.

176
 Query Tree Notation: Example
• Write query: For every project located in ‘Surat’, list the project number,
the controlling department number, and the department manager's last
name, address, and birth date.

177
 Query Tree Notation: Example
• Example: For every project located in ‘Surat’, list the project number, the
controlling department number, and the department manager's last
name, address, and birth date.

((σPlocation='Surat'(Project) ⋈Dnum=Dnumber
𝞹Pnumber, Dnum, Lname, Address, Bdate
Department) ⋈Mgr_ssn=Ssn Employee)

178
 Query Tree Notation: Example
𝞹Pnumber, Dnum, Lname, Address, Bdate (
(σPlocation='Surat'(Project) ⋈Dnum=Dnumber Department) ⋈Mgr_ssn=Ssn Employee)

179
 Generalized Projection 𝞹
• Extends the projection operation by allowing arithmetic
functions to be used in the projection list.

𝞹 F1, F2, …, Fn(E)

• E is any relational-algebra expression
• Each of F1, F2, …, Fn are are arithmetic expressions involving
constants and attributes in the schema of E.

180
 Generalized Projection 𝞹
• Given relation
credit-info(CustomerName, Limit, CreditBalance)
Customer-name Limit CreditBalance
Avinash 2000 500
Balaji 700 100
Chandan 1500 1000

Find how much money each person can spend:

181
 Generalized Projection 𝞹
• Given relation
credit-info(CustomerName, Limit, CreditBalance)
Customer-name Limit CreditBalance
Avinash 2000 500
Balaji 700 100
Chandan 1500 1000

Find how much money each person can spend:

result <- ∏customer-name, (limit – credit-balance) (credit-info)
Customer-name Limit - CreditBalance
Avinash 1500
Balaji 600
Chandan 500

182
 Generalized Projection 𝞹
• Another Example
Consider a relation
EMPLOYEE(EMP-ID, Salary, Deduction, Years-of-Service)
A report may be required to show:
• Net_salary = Salary – Deduction
• Bonus = 2000 * Years-of-Service
• Tax = Salary * 25%

Then a generalized projection combined with renaming may be:

report <- ρ(Net_salary, Bonus, Tax )
(𝞹 EMP-ID, (Salary –Deduction), (2000 * Years-of-Service), (Salary * 0.25) (EMPLOYEE))

183
 Aggregate Function ℱ (script F)
• Aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

ℱMAX marks (Student)

MAX_marks
-------------
100

184
Aggregate Function ℱ (script F)
ℱCOUNT usn, AVERAGE marks(Student)

COUNT_usn AVERAGE_marks
---------------------------------
5 75

185
 Using Grouping with Aggregation
<grouping attribute> ℱ <aggregate function list>
• <grouping attribute> is a list of attributes of the relation specified in R and
<aggregate function list> is a list of (<function> <attribute>)

ℱ
dep_num COUNT usn, AVERAGE marks
(Student)

dep-num COUNT_usn AVERAGE_marks

---------------------------------------------
10 4 71.25
20 1 90

186
 Problem to Solve: Writing Relational Algebra Expression

Consider the following three tables

• SALESPERSON(SalesPersonID,Name)
• TRIP(SalesPersonID, From, To, Trip-ID)
• EXPENSE(TripID, Amount)
i. Print the total trip expenses incurred by sales person with ID 504
ii. Give the trip details for the trip that exceeded Rs. 10,000/-
iii. Print the sales person ID and Name of the sales men who took trips to Delhi

187
 Problem to Solve: Writing Relational Algebra Expression

Consider the following three tables

• SALESPERSON(SalesPersonID,Name)
• TRIP(SalesPersonID, From, To, Trip-ID)
• EXPENSE(TripID, Amount)
SALESPERSON EXPENSE
SalesPersonID Name Trip-ID Amount

504 Avinash 10 10000

505 Balaji 11 8000

506 Chandan 12 15000

TRIP
SalesPersonID From To Trip-ID
504 Chennai Delhi 10
504 Bangalore Bombay 11
505 Bangalore Srinagar 12

188
 Problem to Solve: Writing Relational Algebra Expression

i. Print the total trip expenses incurred by sales person with ID 504

result
--------
18000

189
 Problem to Solve: Writing Relational Algebra Expression

i. Print the total trip expenses incurred by sales person with ID 504

result temp1 <- σSalespersonID=504(TRIP)

-------- temp2 <- 𝞹Trip-ID(temp1)
18000 temp3 <- EXPENSE ⋈ [Link]-ID=[Link]-ID (temp2)
result <- ℱSUM Amount (temp3)

190
 Problem to Solve: Writing Relational Algebra Expression

ii. Give the trip details for the trip that exceeded Rs. 10,000/-

result
----------------------------------------
504 Chennai Delhi 10 10000
505 Bangalore Srinagar 12 15000

191
 Problem to Solve: Writing Relational Algebra Expression

ii. Give the trip details for the trip that exceeded Rs. 10,000/-

result
----------------------------------------
504 Chennai Delhi 10 10000
505 Bangalore Srinagar 12 15000

temp1 <- σAmount > 10000(EXPENSE)

result <- 𝞹 ([Link], [Link], [Link], [Link]-ID,[Link])
(TRIP ⋈
[Link]-ID=[Link]-ID
(temp1))

192
 Problem to Solve: Writing Relational Algebra Expression

iii. Print the sales person ID and Name of the sales men who took trips to Delhi

result
--------------
504 Avinash

193
 Problem to Solve: Writing Relational Algebra Expression

iii. Print the sales person ID and Name of the sales men who took trips to Delhi

result
--------------
504 Avinash

temp1 <- σTo=‘Delhi’(TRIP)

temp2 <- 𝞹SalesPerson-ID(temp1)
temp3<-SALESPERSON ⋈ [Link]=[Link]-ID
(temp2)
result <- 𝞹 SalesPersonID, Name (temp3)

194
 Problem to Solve: Writing Relational Algebra Expression

Consider the following relational schema describing a movie database

• Schedule(Theater, Title, Time)
• Movies(Title, Director, Actor)
• Produced(Producer, Title)
• See(Spectator, Title)
• Liked(Spectator, Title)
• A movie is directed by only one director but can be produced by several Producers. A
spectator may like a movie without having seen it. Write the following two queries in both
Relational Algebra and SQL.
Note: In relational algebra you must use the expression form and are not allowed to use linear
sequence or expression trees. You are also NOT allowed renaming of relations. You may use
renaming of attributes. You may use numerical comparisons (e.g. R:A > 5) in both SQL and
Relational Algebra.
• List the people who liked movies that they have not seen
• List the producers who produced a movie that does not appear in a theater.

195
 Problem to Solve: Writing Relational Algebra Expression

See(Spectator, Title)
Liked(Spectator, Title)

• List the people who liked movies that they have not seen

196
 Problem to Solve: Writing Relational Algebra Expression

Schedule(Theater, Title, Time)

Movies(Title, Director, Actor)
Produced(Producer, Title)

• List the producers who produced a movie that does not appear in a theater.

197
 Relational Algebra Operations
• Unary Operations - operate on one relation.
These include select, project and rename
operators.
• Binary Operations - operate on pairs of
relations. These include union, set difference,
intersection, division, cartesian product, join,
equality join, natural join, Left Outer join,
Right outer join and full outer join.

198
 Thank You for Your Time and Attention !
Students Should read through the
-Relational algebra example queries given in the ELMARSI and NAVATHE text book in
chapter 6 of section 6.5
-Relational Model constraints and Relational database schema, Update Transactions
and Dealing with constraint violation from ELMARSI and NAVATHE text book in chapter
5 of sections 5.2 and 5.3

199