Databases Management System

Relational Model
RDBMS Architecture
How does a SQL engine work ?

Relational
SQL Optimized
Algebra Execution
Query RA Plan
(RA) Plan

Declarativ Translate to Find logically Execute each

e query relational equivalent- operator of
(from algebra but more the optimized
user) expression efficient- RA plan!
expression
RDBMS Architecture
How does a SQL engine work ?

Relational
SQL Optimized
Algebra Execution
Query RA Plan
(RA) Plan

Relational Algebra allows us to translate

declarative (SQL) queries into precise and
optimizable expressions!
Structure of relational databases
• Table (Relation): A database object that holds a collection of data for a
specific topic. Table consist of rows and columns.
• Column (Attribute): The vertical component of a table. A column has a
name and a particular data type; e.g. varchar, decimal, integer, datetime
etc.
• Record (Tuple): The horizontal component of a table, consisting of a
sequence of values, one for each column of the table. It is also known as
row.
• A database consists of a collection of tables (relations), each having a
unique name.
Structure of Table - Student Table

Columns (5)

Attributes: RollNo Name Branch Semester SPI

Title of column
101 John CE 3 8

Cardinality = No of tuples (7)

102 Miller CI 3 7
Rows or 103 Bud CE 3 6
Tuples or
104 Raj EE 3 9
Records (7)
105 George CI 3 7
106 Jacob ME 3 8
107 Charlie CE 3 9

Degree = No of columns (5)

• Domain is a set of all possible unique values for a specific column.
• Domain of Branch attribute is (CE, CI, ME, EE)
Basic Relational Algebra Operations
Operator Description
Selection Display particular rows/records/tuples from a relation
Projection Display particular columns from a relation
Cross Product Multiply each tuples of both relations
Division Divides one relation by another
Rename Rename a column or a table
Joins Combine data or records from two or more tables
1. Natural Join / Inner Join
2. Equi Join, Theta Join
3. Outer Join
1. Left Outer Join 2. Right Outer Join 3. Full Outer Join
Set Operators Combine the results of two queries into a single result
1. Union 2. Intersection 3. Minus / Set-difference
1. Selection (),sigma Students(sid,sname,gpa)

• Returns all tuples which satisfy a SQL:

condition SELECT *
• Notation: sc(R) FROM Students
WHERE gpa > 3.5;
• Examples
• sSalary > 40000 (Employee)
• sname = ‘Smith’ (Employee)
RA:
• The condition c can be =, <, , >, 𝜎 𝑔𝑝𝑎> 3.5 (𝑆𝑡𝑢𝑑𝑒𝑛𝑡𝑠 )
, <>, Λ (AND), V (OR)
Selection Operator example [ condition (Relation) ]
• Display the detail of students belongs to “CE” branch.
Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
103 Harsh EE 8
104 Punit CE 9

 Branch=‘CE’ (Student)
Output
RollNo Name Branch SPI
101 Raj CE 8
104 Punit CE 9
Selection Operator example [ condition (Relation) ]
• Display the detail of students belongs to “CE” branch and having SPI
more than 8.
Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
103 Harsh EE 8
104 Punit CE 9

 Branch=‘CE’ Λ SPI>8 (Student)

Output
RollNo Name Branch SPI
104 Punit CE 9
Selection Operator example [ condition ]
(Relation)

• Display the detail of students belongs to either “EE” or “ME” branch.

Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
103 Harsh EE 8
104 Punit CE 9

 Branch=‘EE’ V Branch=‘ME’ (Student)

Output
RollNo Name Branch SPI
102 Meet ME 9
103 Harsh EE 8
Exercise Write down relational algebra for the following table.

Employee
EmpID Name Dept Salary
101 Nilesh Sales 10000
102 Mayur HR 25000
103 Hardik HR 15000
104 Ajay Admin 20000

1. Display the detail of all employee.

2. Display the detail of employee whose salary more than 10000.
3. Display the detail of employee belongs to “HR” Dept having Salary more than 20000.
4. Display the detail of employee belongs to either “HR” or “Admin” Dept.
5. Display the detail of employee whose Salary between 1000 and 25000 and belongs to
“HR” Dept .
2. Projection (),pie Students(sid,sname,gpa)

• Operation: Selects specified SQL:

attributes of a relation. SELECT DISTINCT
• It removes duplicate tuples sname,
(records) from the result. gpa
FROM Students;
• Notation: P A1,…,An (R)
• Example: project social-security
number and names: RA:
• P SSN, Name (Employee) Π 𝑠 𝑛𝑎𝑚𝑒 ,𝑔𝑝𝑎 (𝑆𝑡𝑢𝑑𝑒𝑛𝑡𝑠)
• Output schema: Answer(SSN,
Name)
Projection Operator example [ attribute set (Relation)]
• Display rollno, name and branch of all students.
Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
103 Harsh EE 8
104 Punit CE 9

 RollNo, Name, Branch (Student)

Output
RollNo Name Branch
101 Raj CE
102 Meet ME
103 Harsh EE
104 Punit CE
Exercise Write down relational algebra for the following table.

Student
RollNo Name Branch SPI
101 Raj CE 6
102 Meet ME 8
103 Harsh EE 7
104 Punit CE 9

1. Display rollno, name and SPI of all students.

2. Display name and SPI of all students.
3. Display the name of all students.
4. Display the name of all branches.
Combination of Projection & Selection Operation
• Example: Display rollno, name & branch of “ME” branch students.
Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
104 Punit CE 9

Output-1  Branch=‘ME’ (Student)

RollNo Name Branch SPI
102 Meet ME 9

 RollNo, Name, Branch ( Branch=‘ME’ (Student) )

Output-2
RollNo Name Branch
102 Meet ME
Combined Projection & Selection Operation
• Example: Display name, branch and SPI of students who belongs to “CE”
Branch and SPI is more than 8.
Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
104 Punit CE 9

 Name, Branch, SPI ( Branch=‘CE’ Λ SPI>8 (Student) )

Output
Name Branch SPI
Punit CE 9
Combined Projection & Selection Operation
• Example: Display name of students along with their branch who belong
to either “ME” Branch or “EE” Branch.
Student
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME 9
104 Punit CE 9
103 Jatin EE 7

 Name, Branch ( Branch=‘ME’ V Branch=‘EE’ (Student) )

Output
Name Branch
Meet ME
Jatin EE
Exercise Write down relational algebra for the following table.

Faculty
FacultyID Name Branch Salary
101 B. M. Patel CE 25000
102 A. M. Shah ME 35000
103 J. B. Katara EE 18000
104 H. N. Shukla CE 50000
105 V. K. Vyas ME 45000

1. Display the name of faculties belong to “CE” branch and having salary more than
25000.
2. Display the name of all “CE” and “ME” branch’s faulty.
3. List the name of faculty with their salary who belongs to “CE” or “ME” branch having
salary more than 35000.
4. Display the name of faculty along with their branch name whose salary between
25000 and 50000.
Cartesian Product / Cross Product
• Symbol: (Cross)
• Notation: Relation-1 (R1) Relation-2 (R2)
• Rare in practice; mainly used to express joins
• Operation: It will multiply each tuples of Relation-1 to each tuples of
Relation-2.
 Attributes of Resultant Relation = Attributes of R1 + Attributes of R2
 Tuples of Resultant Relation = Tuples of R1 * Tuples of R2
Cartesian Product / Cross Product
• Example:
Student Result
RollNo Name Branch RollNo SPI
101 Raj CE 101 8
102 Meet ME 103 9

Student Result
Student.RollNo Name Branch Result.RollNo SPI
101 Raj CE 101 8
101 Raj CE 103 9
102 Meet ME 101 8
102 Meet ME 103 9

If both relations have some attribute with the same name, it can be
distinguished by combing relation-name.attribute-name.
Natural Join (Inner Join)
• Symbol:
• Notation: Relation-1 (R1) Relation-2 (R2)
• Operation: Natural join will retrieve consistent data from multiple
relations.
• It combines records from different relations that satisfy a given
condition.
Steps
Step – 1 It performs Cartesian Product
Step – 2 Then it deletes inconsistent tuples
Step – 3 Then it removes an attribute from duplicate
attributes
Natural Join (Inner Join) example
Student Result
RollNo Name Branch RollNo SPI
101 Raj CE 101 8
102 Meet ME 103 9

Student Result Step 1 : Performs Cartesian Product

Student.RollNo Name Branch Result.RollNo SPI
101 Raj CE 101 8
101 Raj CE 103 9
102 Meet ME 101 8
102 Meet ME 103 9 Step 3 : Removes an attribute

Student Result Step 2 : Removes inconsistent tuples Student Result

Student.RollNo Name Branch Result.RollNo SPI RollNo Name Branch SPI
101 Raj CE 101 8 101 Raj CE 8
Natural Join (Inner Join) example
Branch B Faculty Step 1 : Performs Cartesian Product
BID BName Head Branch.BID BName Head FID FName Faculty.BID
1 CE Patel 1 CE Patel 1 Raj 1
2 ME Shah 1 CE Patel 2 Meet 2
2 ME Shah 1 Raj 1
Faculty
2 ME Shah 2 Meet 2
FID FName BID
1 Raj 1
B Faculty Step 2 : Removes inconsistent tuples
2 Meet 2 Branch.BID BName Head FID FName Faculty.BID
Step 3 : Removes an attribute 1 CE Patel 1 Raj 1
2 ME Shah 2 Meet 2
B Faculty
BID BName Head FID FName
To perform a natural join there must be one
1 CE Patel 1 Raj
common attribute (column) between two
2 ME Shah 2 Meet
relations.
Exercise: Write down relational algebras for the
following tables
• Relations
• Student (Rno, Sname, Address, City, Mobile)
• Department (Did, Dname)
• Academic (Rno, Did, SPI, CPI, Backlog)
• Guide (Rno, PName, Fid)
• Faculty (Fid, Fname, Subject, Did, Salary)
• List the name of students with their department name and SPI of all student
belong to “CE” department.

 Sname, Dname, SPI ( Dname=‘CE’ (Student (Department Academic)))

Exercise: Write down relational algebras for the
following tables
• Relations
• Student (Rno, Sname, Address, City, Mobile)
• Department (Did, Dname)
• Academic (Rno, Did, SPI, CPI, Backlog)
• Guide (Rno, PName, Fid)
• Faculty (Fid, Fname, Subject, Did, Salary)
• Display the name of students with their project name whose guide is “A. J. Shah”.

 Sname, Pname ( Fname=‘A.J.Shah’ (Student (Guide Faculty)))

Exercise

1. Write down relational algebras for the following tables:

Student (Rno, Sname, Address, City, Mobile)
Department (Did, Dname)
Academic (Rno, Did, SPI, CPI, Backlog)
Guide (Rno, ProjectName, Fid)
Faculty (Fid, Fname, Subject, Did, Salary)

i. List the name of students with their department name having backlog 0.
ii. List the name of faculties with their department name and salary having salary
more than 25000 and belongs to “CE” department.
iii. List the name of all faculties of “CE” and “ME” department whose salary is more
than 50000.
Outer Join
• Operation: In natural join some records are missing, if we want that
missing records than we have to use outer join.
• Types: Three types of Outer Join
1. Left Outer Join
2. Right Outer Join
3. Full Outer Join
Left Outer Join
• Display all the tuples of the left relation even through there is no
matching tuple in the right relation.
• For such kind of tuples having no matching, the attributes of right
relation will be padded with null in resultant relation.
• Example:
Student Result
RollNo Name Branch RollNo SPI
101 Raj CE 101 8
102 Meet ME 103 9

Student Result
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME null
Left Outer Join example
• Display Name, Salary and Balance of the of all employees.

Employee Customer
ID Name Salary ID Name Balance
2 Meet 15000 1 Raj 5000
3 Jay 20000 2 Meet 8000

 Name, Salary, Balance (Employee Customer)

Output
Name Salary Balance
Meet 15000 8000
Jay 20000 null
Right Outer Join
• Display all the tuples of right relation even through there is no matching
tuple in the left relation.
• For such kind of tuples having no matching, the attributes of left relation
will be padded with null in resultant relation.
• Example:
Student Result
RollNo Name Branch RollNo SPI
101 Raj CE 101 8
102 Meet ME 103 9

Student Result
RollNo Name Branch SPI
101 Raj CE 8
103 null null 9
Right Outer Join example
• Display Name, Salary and Balance of the of all customers.

Employee Customer
ID Name Salary ID Name Balance
2 Meet 15000 1 Raj 5000
3 Jay 20000 2 Meet 8000

 Name, Salary, Balance (Employee Customer)

Output
Name Salary Balance
Raj null 5000
Meet 15000 8000
Full Outer Join
• Display all the tuples of both of the relations. It also pads null values
whenever required. (Left outer join + Right outer join)
• For such kind of tuples having no matching, it will be padded with
null in resultant relation.
• Example: Student Result
RollNo Name Branch RollNo SPI
101 Raj CE 101 8
102 Meet ME 103 9

Student Result
RollNo Name Branch SPI
101 Raj CE 8
102 Meet ME null
103 null null 9
Full Outer Join example
• Display Name, Salary and Balance of the of all employee as well as
customers.
Employee Customer
ID Name Salary ID Name Balance
2 Meet 15000 1 Raj 5000
3 Jay 20000 2 Meet 8000

 Name, Salary, Balance (Employee Customer)

Output
Name Salary Balance
Meet 15000 8000
Jay 20000 null
Raj null 5000
Equi-join (A=B) Students(sid,sname,gpa)
People(ssn,pname,address)

• For whatever JOIN type (INNER, OUTER, etc), if we use ONLY the
equality operator (=), then we say that the JOIN is an EQUI JOIN
• A theta join where q is an equality SQL:
• R1 A=B R2 = s A=B (R1  R2) SELECT *
FROM
• Example: Students S,
• Employee SSN=SSN Dependents People P
WHERE sname = pname;

Most common
join in practice! RA:
𝑆 ⋈ 𝑠 𝑛𝑎𝑚𝑒=𝑝𝑛𝑎𝑚𝑒 𝑃
Theta Join (q) Students(sid,sname,gpa)
People(ssn,pname,address)

• This is same as EQUI JOIN but it allows all other operators like >,
<, >= etc.
• A join that involves a predicate
SQL:
• R1 q R2 = s q (R1  R2) SELECT *
• Here q can be any condition FROM
Students,People
WHERE q;
Note that natural join
is a theta join + a
projection. RA:
𝑆𝑡𝑢𝑑𝑒𝑛𝑡𝑠 ⋈𝜃 𝑃𝑒𝑜𝑝𝑙𝑒
Set Operators
• Set operators combine the results of two or more queries into a single
result.
• Types of set operators
1. Union
2. Intersect (Intersection)
3. Minus (Set Difference)
Union Operator
• Symbol: U
• Notation: Relation1 U Relation2
• Operation: Combine the records from two or more queries in to a
single result.

Customer Employee Customer U Employee

Name Name Name
Raj Meet Manoj
Suresh Suresh Meet
Meet Manoj Raj
Suresh

Union removes duplicate records.

Union example
• Display Name of person who are either employee or customer.

Employee Customer
ID Name Salary ID Name Balance
2 Meet 15000 1 Raj 5000
3 Jay 20000 2 Meet 8000

 Name (Employee) U  Name (Customer)

Output
Name
Meet
Jay
Raj
Intersect Operator
• Symbol: ∩
• Notation: Relation1 ∩ Relation2
• Operation: Returns the records which are common from both queries.

Customer Employee Customer ∩ Employee

Name Name Name
Raj Meet Meet
Suresh Suresh Suresh
Meet Manoj
Intersect example
• Display Name of person who are employee as well as customer.

Employee Customer
ID Name Salary ID Name Balance
2 Meet 15000 1 Raj 5000
3 Jay 20000 2 Meet 8000

 Name (Employee) ∩  Name (Customer)

Output
Name
Meet
Minus (Set Difference) Operator
• Symbol: −
• Notation: Relation1 − Relation2
• Operation: Returns all the records from first (left) query that are not
contained in the second (right) query.

Customer Employee Customer − Employee Employee − Customer

Name Name Name Name
Raj Meet Raj Manoj
Suresh Suresh
Meet Manoj
Minus example
• Display Name of person who are employee but not customer.

Employee Customer
ID Name Salary ID Name Balance
2 Meet 15000 1 Raj 5000
3 Jay 20000 2 Meet 8000

 Name (Employee) -  Name (Customer)

Output
Name
Jay
Conditions to perform Set Operators
• Set operators will take two or more queries as input, which must be
union - compatible :
1. Both queries should have same (equal) number of columns

RollNo Name Branch SPI EmpNo Name Branch

101 Raj CE 8 101 Patel CE
102 Meet CE 7 102 Shah CE
103 Neel ME 9 103 Ghosh EE

RollNo Name Branch SPI EmpNo Name Branch Exp

101 Raj CE 8 101 Raj CE 8
102 Meet CE 7 102 Meet CE 1
103 Neel ME 9 103 Ghosh EE 9
Conditions to perform Set Operators
• Set operators will take two or more queries as input, which must be
union - compatible:
2. Corresponding attributes should have the same data type
RollNo Name Branch SPI EmpNo Name Branch Subject
101 Raj CE 8 101 Raj CE DBMS
102 Meet CE 7 102 Meet CE DS
103 Neel ME 9 103 Ghosh EE EEM

RollNo Name Branch SPI EmpNo Name Branch Exp

101 Raj CE 8 101 Raj CE 8
102 Meet CE 7 102 Meet CE 1
103 Neel ME 9 103 Ghosh EE 9
Division Operator
• Symbol: ÷
• Notation: Relation1 ÷ Relation2
• Condition to perform operation:
• Attributes of relation2 is proper subset of attributes of relation1.
• Operation:
• The output of the division operator will have attributes =
All attributes of relation1 – All attributes of relation2
• The output of the division operator will have tuples =
Tuples in relation1, which are associated with the all tuples of relation2.
Division Operator example
Student Subject Student ÷ Subject
Name Subject Subject Name
Raj DBMS DBMS Meet
Raj DS DS Suresh
Meet DBMS DE
Meet DS
Meet DE
Suresh DBMS
Suresh DS
Suresh DE
Rohit DS
Rohit DE
Division Operator example
A B1 B2 B3
Sno Pno Pno Pno Pno
S1 P1 P2 P2 P1
S1 P2 P4 P2
S1 P3 P4
S1 P4
S2 P1 A ÷ B1 A ÷ B2 A ÷ B3
S2 P2 Sno Sno Sno
S3 P2 S1 S1 S1
S4 P2 S2 S4
S4 P4 S3
S5 P4 S4
Exercise: List the name of students doing a project in
all technologies
Student Project
Rno Name Technology TID Technology
101 Raj .NET 1 .NET
101 Raj iPhone 2 PHP
102 Meet .NET 3 iPhone
102 Meet PHP 4 Android
102 Meet iPhone
102 Meet Android
103 Vijay PHP Output
104 Jay .NET Name
104 Jay PHP Meet
104 Jay iPhone Jay
104 Jay Android

 Name, Technology (Student) ÷  Technology(Project)

Rename/Renaming Operator
• Symbol: ρ (Rho)
• Notation: ρ A (X1,X2….Xn) (Relation)
• Operation: The rename operator returns an existing relation under a
new name.
• How to use:
• ρ x (E)
• Returns a relation E under a new name X.
• ρ A1, A2. …,An (E)
• Returns a relation E with the attributes renamed to A1, A2, …., An.
• ρ x(A1, A2. …,An) (E)
• Returns a relation E under a new name X with the attributes renamed to A1, A2, …., An.
Rename Operator example (Relation)
Student
Rno Name CPI
101 Raj 8
102 Meet 9
103 Suresh 7

ρPerson (Student)

Person
Rno Name CPI
101 Raj 8
102 Meet 9
103 Suresh 7
Rename Operator example (Attribute)
Student
Rno Name CPI
101 Raj 8
102 Meet 9
103 Suresh 7

ρ (RollNo, StudentName, CPI) (Student)

Student
RollNo StudentName CPI
101 Raj 8
102 Meet 9
103 Suresh 7
Rename Operator example (Attribute & Relation both)

Student
Rno Name CPI
101 Raj 8
102 Meet 9
103 Suresh 7

ρPerson (RollNo, StudentName) ( RNo, Name (Student))

Person
RollNo StudentName
101 Raj
102 Meet
103 Suresh
Rename Operator example
Rno Name CPI
101 Raj 8
Find out maximum CPI from student table.
102 Meet 9 ρA (Student) X ρB (Student)))
103 Suresh 7

A.Rno A.Name A.CPI B.Rno B.Name B.CPI

101 Raj 8 101 Raj 8
101 Raj 8 102 Meet 9
101 Raj 8 103 Suresh 7
102 Meet 9 101 Raj 8
102 Meet 9 102 Meet 9
102 Meet 9 103 Suresh 7
103 Suresh 7 101 Raj 8
103 Suresh 7 102 Meet 9
103 Suresh 7 103 Suresh 7
Rename Operator example

 A.CPI<B.CPI (ρA (Student) X ρB (Student)))

A.Rno A.Name A.CPI B.Rno B.Name B.CPI

101 Raj 8 101 Raj 8
101 Raj 8 102 Meet 9
101 Raj 8 103 Suresh 7
102 Meet 9 101 Raj 8
102 Meet 9 102 Meet 9
102 Meet 9 103 Suresh 7
103 Suresh 7 101 Raj 8
103 Suresh 7 102 Meet 9
103 Suresh 7 103 Suresh 7
Rename Operator example

 A.CPI<B.CPI (ρA (Student) X ρB (Student)))

A.Rno A.Name A.CPI B.Rno B.Name B.CPI

101 Raj 8 101 Raj 8
101 Raj 8 102 Meet 9
101 Raj 8 103 Suresh 7
102 Meet 9 101 Raj 8
102 Meet 9 102 Meet 9
102 Meet 9 103 Suresh 7
103 Suresh 7 101 Raj 8
103 Suresh 7 102 Meet 9
103 Suresh 7 103 Suresh 7
Rename Operator example

 A.CPI<B.CPI (ρA (Student) X ρB (Student)))

A.Rno A.Name A.CPI B.Rno B.Name B.CPI

101 Raj 8 102 Meet 9
103 Suresh 7 101 Raj 8
103 Suresh 7 102 Meet 9
Rename Operator example

∏A.CPI ( A.CPI<B.CPI (ρA (Student) X ρB (Student)))

A.Rno A.Name A.CPI B.Rno B.Name B.CPI

101 Raj 8 102 Meet 9
103 Suresh 7 101 Raj 8
103 Suresh 7 102 Meet 9
Rename Operator example

∏A.CPI ( A.CPI<B.CPI (ρA (Student) X ρB (Student)))

A.CPI
8
7
Rename Operator example

∏CPI (Student) ─ ∏A.CPI ( A.CPI<B.CPI (ρA (Student) X ρB (Student)))

CPI A.CPI CPI

8 8 9
— = =
9 7
7
Aggregate Functions
• Symbol: g or G
• Notation: g function-name (column) (Relation)
• Operation: It takes a more than one value as input and returns a single
value as output (result).
• Aggregate functions are:
1. Sum (It returns the sum (addition) of the values of a column.)
2. Max (It returns the maximum value for a column.)
3. Min (It returns the minimum value for a column.)
4. Avg (It returns the average of the values for a column.)
5. Count (It returns total number of values in a given column.)
Aggregate Functions example
Student
Rno Name Branch Semester CPI
101 Ramesh CE 3 9
102 Mahesh EC 3 8
103 Suresh ME 4 7
104 Amit EE 4 8
105 Anita CE 4 8
106 Reeta ME 3 7
107 Rohit EE 4 9
108 Chetan CE 3 8
109 Rakesh CE 4 9

sum
• Example: Find out sum of CPI of all students.
73
• G sum(CPI) (Student)
Aggregate Functions example
Student
Rno Name Branch Semester CPI
101 Ramesh CE 3 9
102 Mahesh EC 3 8
103 Suresh ME 4 7
104 Amit EE 4 8
105 Anita CE 4 8
106 Reeta ME 3 7
107 Rohit EE 4 9
108 Chetan CE 3 8
109 Rakesh CE 4 9

max min
• Example: Find out maximum & minimum CPI.
9 7
• G max(CPI), min(CPI) (Student)
Aggregate Functions example
Student
Rno Name Branch Semester CPI
101 Ramesh CE 3 9
102 Mahesh EC 3 8
103 Suresh ME 4 7
104 Amit EE 4 8
105 Anita CE 4 8
106 Reeta ME 3 7
107 Rohit EE 4 9
108 Chetan CE 3 8
109 Rakesh CE 4 9

count
• Example: Count the number of students.
9
• G count(Rno) (Student)
Exercise

1. Write down relational algebras for the following table:

Employee (person-name, street, city)
Works (person-name, company-name, salary)
Company (company-name, city)
Managers (person-name, manager-name)
i. Find the names of all employees who work for “TCS”.
ii. Find the names and cities of residence of all employees who work for “Infosys”.
iii. Find the names, street and city of residence of all employees who work for “ITC” and
earn more than $10,000 per annum.
iv. Find the names of all employees in this database who live in the same city as the
company for which they work.
Exercise

1. Write down relational algebras for the following table:

Employee (person-name, street, city)
Works (person-name, company-name, salary)
Company (company-name, city)
Managers (person-name, manager-name)
v. Find the names of all employees working in “TCS” who earn more than 25000 and
less than 40000.
vi. Find the name of employee whose manager is “Ajay Patel” and salary is more than
50000.
vii. Display the name of employee with street, city, company name, salary and manager
name staying in “Rajkot” and working in “Ahmedabad”.
viii. Find maximum, minimum and average salary of all employee.
ix. Find out the total number of employee.
Thank you..!!