IT-243-L13-14-2. pdf

DBMS

1. Database Systems
Lec# 13 &14

2. Topics
RelationalAlgebra:
•Join

3. Join
•Joininvolvingcrossproduct,selection,and(sometimes)projection.
•Aspecialformofcrossproductoftwotables
•TypesofJoin
1.ThetaJoin
2.EquiJoin
3.NaturalJoin
4.SemiJoin
5.InnerJoin
6.OuterJoin

4. Theta Join
Apply the condition through selecton one relation and then only
those rows are used in the cross product with the second relation
R1R2

5. ranksalarydeptfacNamefacId
F5678
F4567
F3456
F2345
32000
27000
23000
21000
ProfessorMNGSamad
Asso ProfENGAyesha
Asso ProfCSETahir
lecturerCSEUsman
COURSE
F2345Introduction to
Accounting
C3425
F4567Money & Capital
Market
C5678
Financial
Management
C4567
F2345Database SystemsC3456
fIdcrTitlecrCode
FACULTY
Theta Join Example
(σ
rank = ‘AssoProf’(FACULTY)) X COURSE
F2345

6. © Virtual University of Pakistan
Asso Prof27000ENGAyeshaF4567
Asso Prof27000ENGAyeshaF4567
Asso Prof27000ENGAyeshaF4567
Asso Prof27000ENGAyeshaF4567
Asso Prof23000CSETahirF3456
Asso Prof23000CSETahirF3456
Asso Prof23000CSETahirF3456
Asso Prof23000CSETahirF3456
RanksalarydeptfacNamefacId
F2345Introduction to
Accounting
C3425
F2345Database SystemsC3456
Financial
Management
C4567
F4567Money & Capital
Market
C5678
F2345Introduction to
Accounting
C3425
F4567Money & Capital
Market
C5678
Financial
Management
C4567
F2345Database SystemsC3456
fIdcrTitlecrCode
(σ
rank = ‘AssoProf’(FACULTY)) X COURSE

7. EquiJoin
•Rows are joined on the basis of values of a common attribute between the
two relations
•Rows having the same value in the common attribute are joined.
•Common attributes appears twice in the output
•Common attribute with the same name is qualified with the relation name
in the output

8. ranksalarydeptfacNamefacId
F5678
F4567
F3456
F2345
32000
27000
23000
21000
ProfessorMNGSamad
Asso ProfENGAyesha
Asso ProfCSETahir
lecturerCSEUsman
COURSE
F2345Introduction to
Accounting
C3425
F4567
Money & Capital
Market
C5678
F2345Database SystemsC3456
fIdcrTitlecrCode
FACULTY
Equijoin Example
FACULTY FACULTY.facId= COURSE.fIdCOURSE

9. lecturer21000CSEUsmanF2345
Asso Prof27000ENGAyeshaF4567
lecturer21000CSEUsmanF2345
RanksalarydeptfacNamefacId
F2345Introduction to
Accounting
C3425
F4567Money & Capital
Market
C5678
F2345Database SystemsC3456
fIdcrTitlecrCode
Equijoin Example
FACULTY FACULTY.facId= COURSE.fIdCOURSE

10. Natural Join
•Also called simply the join, most general form of join
•Same as equijoin with common column appearing once

11. Natural Join Example
FACULTY
FACULTY.facId, COURSE.facIdCOURSE
ranksalarydeptfacNamefacId
F5678
F4567
F3456
F2345
32000
27000
23000
21000
ProfessorMNGSamad
Asso ProfENGAyesha
Asso ProfCSETahir
lecturerCSEUsman
COURSE
F2345Introduction to
Accounting
C3425
F4567Money & Capital
Market
C5678
Financial
Management
C4567
F2345Database SystemsC3456
facIdcrTitlecrCode
FACULTY

12. lecturer21000CSEUsmanF2345
Asso Prof27000ENGAyeshaF4567
lecturer21000CSEUsmanF2345
RanksalarydeptfacNamefacId
Introduction to
Accounting
C3425
Money & Capital
Market
C5678
Database SystemsC3456
crTitlecrCode
Natural Join Example
FACULTY
FACULTY.facId, COURSE.facIdCOURSE

13. Semijoin
•First take the natural join of two tables
•Then take the projection on the attributes of first table
FACULTY COURSE

14. Semijoin Example
FACULTY COURSE
ranksalarydeptfacNamefacId
F4567
F2345
27000
21000
Asso ProfENGAyesha
lecturerCSEUsman

15. Inner Join
•An inner join includes only those tuples with matching attributes
and the rest are discarded in the resulting relation.
•Theta Join, Equijoin, and Natural Join are called inner joins

16. Outer Join
•All tuples in the left relation and right relation are
output.
•Denoted by ⟗
•types of outer join depends one the data kept in the
relations
•Left Outer Join
•Right Outer Join

17. ranksalarydeptfacNamefacId
F5678
F4567
F3456
F2345
32000
27000
23000
21000
ProfessorMNGSamad
Asso ProfENGAyesha
Asso ProfCSETahir
lecturerCSEUsman
COURSE
F2345Introduction to
Accounting
C3425
F4567Money & Capital
Market
C5678
Financial
Management
C4567
F2345Database SystemsC3456
fIdcrTitlecrCode
FACULTY
F2345

18. Outer Join Example
F5678 32000ProfessorMNGSamad
Financial ManagementC4567
Money & Capital Market
.
Money & Capital
Market
ranksalarydeptfacNamefacId
F3456
F4567
F2345
F2345
23000
27000
21000
21000
Asso Prof
CSETahir
Asso ProfENGAyesha
lecturerCSEUsman
lecturerCSEUsman
F4567C5678
F2345
Database SystemsC3456
facIdcrTitlecrCode
Intro. To Accounting
C3425
F2345
FACULTY OUTER JOIN COURSE

19. •Keep all of the tuples from the “left” relation
•Join with the right relation
•Pad the non-matching tuples with nulls
•Denoted by
Left Outer Join

20. Left Outer Join Example
FACULTY LEFT JOIN COURSE
ranksalarydeptfacNamefacId
F4567
F3456
F2345
F2345
27000
23000
21000
21000
Asso ProfENGAyesha
Asso ProfCSETahir
lecturerCSEUsman
lecturerCSEUsman
Money & Capital Market
. To Accounting
C5678
Intro. To AccountingC3425
F2345Database SystemsC3456
facIdcrTitlecrCode
F5678 32000ProfessorMNGSamad
F4567

21. Right Outer Join
•Same as the left, but keep tuples from the “right” relation
•Denoted by

22. Right Outer Join Example
F2345
FACULTY RIGHT OUTER JOIN COURSE
Money & Capital Market
.
Money & Capital
Market
ranksalarydeptfacNamefacId
F2345
F4567
F2345
21000
27000
21000
lecturerCSEUsman
Asso ProfENGAyesha
lecturerCSEUsman
F2345
F4567C5678
Database SystemsC3456
facIdcrTitlecrCode
Intro. To AccountingC3425
Financial ManagementC4567