RDBMS and SQL

Madhavan Mukund
https://www.cmi.ac.in/~madhavan

Lecture 2, 22 August 2024

The relational model — Edgar F Codd

All information is stored as tables

(relations)

Instructor
Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 2 / 13
The relational model — Edgar F Codd

All information is stored as tables

(relations)
Mechanism for working with tables

Instructor
Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 2 / 13
The relational model — Edgar F Codd

All information is stored as tables

(relations)
Mechanism for working with tables
Describing table structure (schema)

-[S , XSzXSz

r = RXR XIR

Instructor
Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 2 / 13
The relational model — Edgar F Codd

All information is stored as tables

(relations)
Mechanism for working with tables
Describing table structure (schema)
Additions, deletions, modifications

Instructor
Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 2 / 13
The relational model — Edgar F Codd

All information is stored as tables

(relations)
Mechanism for working with tables
Describing table structure (schema)
Additions, deletions, modifications
Extracting useful information
(querying)

Instructor
Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 2 / 13
The relational model — Edgar F Codd

All information is stored as tables

(relations)
Mechanism for working with tables
Describing table structure (schema)
Additions, deletions, modifications
Extracting useful information
(querying)
Initially, focus on abstract query
language

Instructor
Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 2 / 13
 > to
All instructor names &
depts with salay

...
Relational algebra — select and project

List instructors from Physics Full row

!
department with salary above 90,000
Select rows that meet
-

some condition --

No (r-relation --X

I condition(Instructor
L

W
Physics'
dept name
salary >
=

9000
and
Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 3 / 13
Relational algebra — select and project

List instructors from Physics

department with salary above 90,000

j I Instructor)
depl: name :

"Physics
N

Salary > 90000

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 3 / 13

Relational algebra — select and project

[
List instructors from Physics
department with salary above 90,000
List names of instructors

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 3 / 13

Relational algebra — select and project

List instructors from Physics

department with salary above 90,000
List names of instructors
List names of instructors from Physics
department with salary above 90,000

Tu
name
(* depl : name : Physics' (Inst)
Salary for

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 3 / 13

Relational algebra — select and project

List instructors from Physics

department with salary above 90,000
List names of instructors
List names of instructors from Physics
department with salary above 90,000

Can I?
we
swap - &

T-
deptia--
.
(name (Instructor)
salay
Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 3 / 13
Relational algebra — select and project

List instructors from Physics

department with salary above 90,000
List names of instructors Y

dept
name
List names of instructors from Physics
-
department with salary above 90,000
~ salary
To no (r)
,

roz ((r) ro . (Noz(r) -

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 3 / 13

Relational algebra — join
List details of courses o↵ered by instructors teacher-ID

-- fat

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 4 / 13

For now in Instructor table
every

For every in Teaches table

row

If the two rows match ,

extract the

details
 Can ar

ri]] (r
,
i

Rm)
(rz , Re)
S XSz
,

i
(r Rm) ,
Relational algebra — join
Instructor ⇥ Teaches

-
-

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 5 / 13

Relational algebra — join
Instructor.ID = teaches.ID (Instructor ⇥ Teaches)

oo

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 5 / 13

Relational algebra — join
- bowtie
Instructor ./ Instructor.ID = teaches.ID Teaches — cartesian project with select

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 5 / 13

Relational algebra — join

Find all taxpayers from Chennai with annual income over Rs 1 cr

factor
s
name

sour to
21
Gu
Voter A IT

VoterName Dist
Chenna .
n
a

Per

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 6 / 13

Relational algebra — join

Find all taxpayers from Chennai with annual income over Rs 1 cr

(st(Voret) A overNa (am

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 6 / 13
 Al B

O
says
-
match value
for same

column names in & R2

"Natural"
join
r
,
a
r (no0)
Relational algebra — set operations

IPLIO2
,
1922024

Ur
Eli
removed
Duplicates y

# it
Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 7 / 13
Relational algebra — set operations

V Or y

n M AND 1

To (r Tor (r)

vo(r) =
Er) Uro(r)
To, 102 (v) =
E, (r) n
To (r)
Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 7 / 13
Relational algebra — set operations

Relative
Not? T

Complement

To, (r) ComplementTon(r)]

H
C
In r rows
,

that do not

Salify on

Madhavan Mukund RDBMS and SQL RDBMS-SQL, Lecture 2, 22 Aug 2024 7 / 13