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
