Scribd
Upload
36 views40 pages

Relational Algebra and SQL: Dr. Ghulam Mustafa

Uploaded by

Fatima Irfan

The document discusses relational algebra operations including select, project, union, intersection, set difference, and rename. It provides examples of how to write queries using these operations on sample tables and describes what each operation does. It also compares relational algebra to SQL.

Copyright:

© All Rights Reserved
Download as PDF, TXT or read online from Scribd
Download as pdf or txt

Relational Algebra and SQL: Dr. Ghulam Mustafa

Uploaded by

Fatima Irfan
36 views40 pages
The document discusses relational algebra operations including select, project, union, intersection, set difference, and rename. It provides examples of how to write queries using these operations on sample tables and describes what each operation does. It also compares relational algebra to SQL.

Original Description:

DBMS SLIDES which involves all information related to DBMS

Original Title

Week6-DBMS

Copyright

© © All Rights Reserved

Available Formats

PDF, TXT or read online from Scribd

Did you find this document useful?

Is this content inappropriate?

The document discusses relational algebra operations including select, project, union, intersection, set difference, and rename. It provides examples of how to write queries using these operations on sample tables and describes what each operation does. It also compares relational algebra to SQL.

Copyright:

© All Rights Reserved
Download as PDF, TXT or read online from Scribd
Download as pdf or txt
36 views40 pages

Relational Algebra and SQL: Dr. Ghulam Mustafa

Uploaded by

Fatima Irfan
The document discusses relational algebra operations including select, project, union, intersection, set difference, and rename. It provides examples of how to write queries using these operations on sample tables and describes what each operation does. It also compares relational algebra to SQL.

Copyright:

© All Rights Reserved
Download as PDF, TXT or read online from Scribd
Download as pdf or txt
You are on page 1/ 40

Relational Algebra and SQL

CSC 220

Dr. Ghulam Mustafa

Department of Computer Sciences


Bahria University Lahore Campus

March 19, 2019


Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 1 / 40
Relational Algebra Relational Algebra

Outline
1 Relational Algebra
Relational Algebra
The Select Operation
2 Projection
Projection
3 Union
Union
4 Practice
5 Intersection
6 Set-difference
7 Renaming
8 Quiz
9 Takeaway!
Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 2 / 40
Relational Algebra Relational Algebra

Relational Algebra

The relational algebra is a procedural query language.


It consists of a set of operations that take one or two relations as
input and produce a new relation as their result.
The fundamental operations in the relational algebra are select,
project, union, set difference, Cartesian product, and rename
The select, project, and rename operations are called unary
operations, because they operate on one relation.
The other three operations operate on pairs of relations and are,
therefore, called binary operations.

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 3 / 40


Relational Algebra The Select Operation

Outline
1 Relational Algebra
Relational Algebra
The Select Operation
2 Projection
Projection
3 Union
Union
4 Practice
5 Intersection
6 Set-difference
7 Renaming
8 Quiz
9 Takeaway!
Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 4 / 40
Relational Algebra The Select Operation

The Select Operation

The select operation selects tuples that satisfy a given predicate.


We use the lowercase Greek letter sigma (σ) to denote selection.
The predicate appears as a subscript to σ.

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 5 / 40


Relational Algebra The Select Operation

The Select Operation-Syntax

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 6 / 40


Relational Algebra The Select Operation

The Select Operation-Operators

In general, the comparisons can be done using the following operators.

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 7 / 40


Relational Algebra The Select Operation

Scenario-1

Find movies made by Hanson after 1997

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 8 / 40


Relational Algebra The Select Operation

Scenario1-Solution

Find movies made by Hanson after 1997. What will be the output of
following?

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 9 / 40


Relational Algebra The Select Operation

Scenario1-Solution

Find movies made by Hanson after 1997. What will be the output of
following?

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 10 / 40


Relational Algebra The Select Operation

Scenario2

Find movies made by Coen with the 8.2 rating?.

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 11 / 40


Relational Algebra The Select Operation

Scenario3-Table

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 12 / 40


Relational Algebra The Select Operation

Scenario3

Find tuples of the instructor relation where the instructor is in the


“Physics” department?
Find all instructors with salary greater than $90,000?
Find the instructors in Physics with a salary greater than $90,000?
Find all instructors with salary less than $50,000?
Find the instructors in Finance with a salary of $80,000? What will be
the output of this query?

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 13 / 40


Projection Projection

Outline
1 Relational Algebra
Relational Algebra
The Select Operation
2 Projection
Projection
3 Union
Union
4 Practice
5 Intersection
6 Set-difference
7 Renaming
8 Quiz
9 Takeaway!
Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 14 / 40
Projection Projection

Projection

The project operation is a unary operation that returns its argument


relation, with certain attributes left out
Projection is denoted by the uppercase Greek letter pi (π).

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 15 / 40


Projection Projection

Example
Find all movies and their ratings

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 16 / 40


Projection Projection

Scenario4-Table
Select ID, name, salary from relation "instructor"?

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 17 / 40


Projection Projection

Scenario4-Table

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 18 / 40


Projection Projection

SQL VERSUS RELATIONAL ALGEBRA

The term select in relational algebra has a different meaning than the
one used in SQL, which is an unfortunate historical fact.
In relational algebra, the term select corresponds to what we refer to
in SQL as where.
Similarly, Project corresponds to Select in SQL

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 19 / 40


Projection Projection

Scenario5-Table

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 20 / 40


Projection Projection

Scenario5- Select and Project

Find the set of all courses taught in the Fall 2009

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 21 / 40


Projection Projection

Scenario5-Solution

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 22 / 40


Projection Projection

Practice

Find the set of all courses taught in the Spring 2010?


Find the set of all courses taught in the Summer 2009?
Write SQL queries for above-mentioned examples?

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 23 / 40


Union Union

Outline
1 Relational Algebra
Relational Algebra
The Select Operation
2 Projection
Projection
3 Union
Union
4 Practice
5 Intersection
6 Set-difference
7 Renaming
8 Quiz
9 Takeaway!
Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 24 / 40
Union Union

Union

To return results for multiple queries


Denoted as U

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 25 / 40


Union Union

Example

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 26 / 40


Practice

Practice

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 27 / 40


Practice

Practice

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 28 / 40


Practice

Union

The Cartesian-product operation, denoted by a cross (X ), allows us to


combine information from any two relations.

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 29 / 40


Intersection

Intersection

R ∩ S returns a relation containing all tuples that occur in both R and


S

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 30 / 40


Intersection

Example

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 31 / 40


Set-difference

Set-difference

R - S returns a relation containing all tuples in R but not in S

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 32 / 40


Set-difference

Example

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 33 / 40


Renaming

Renaming

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 34 / 40


Renaming

Practice

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 35 / 40


Renaming

Solution

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 36 / 40


Renaming

Practice

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 37 / 40


Quiz

Quiz

Produce a relation that shows the branch, customer name, balance


and account number for all customers that have a loan bigger than
RS2500.00 and all customers that have a balance smaller than
RS100.00 at Johar town branch.

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 38 / 40


Takeaway!

1 Relational Algebra
Relational Algebra
The Select Operation
2 Projection
Projection
3 Union
Union
4 Practice
5 Intersection
6 Set-difference
7 Renaming
8 Quiz
9 Takeaway!

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 39 / 40


Takeaway!

Takeaway!

Dr. Ghulam Mustafa (BULC) Week6 March 19, 2019 40 / 40

You might also like