Data Manupolation Languages Part 2-Distributive Data SystemsLecture Slides, Slides of Distributed Database Management Systems

Download Data Manupolation Languages Part 2-Distributive Data SystemsLecture Slides and more Slides Distributed Database Management Systems in PDF only on Docsity!

Types of Join

 A special form of cross product

of two tables

 Different types

 Theta, Equi, Natural, Semi,

Outer Joins are different types

Theta Join

We apply the condition through

select on one relation and then

only those rows are used in the

cross product with the second

relation

R S

F4567 Ayesha ENG 27000 Asso Prof

F4567 Ayesha ENG 27000 Asso Prof

F4567 Ayesha ENG 27000 Asso Prof

F4567 Ayesha ENG 27000 Asso Prof

F3456 Tahir CSE 23000 Asso Prof

F3456 Tahir CSE 23000 Asso Prof

F3456 Tahir CSE 23000 Asso Prof

F3456 Tahir CSE 23000 Asso Prof

facId facName dept salary Rank

Introduction to F Accounting

C

C3456 Database Systems F

C4567 Financial Management

Money & Capital F Market

C

Introduction to F Accounting

C

Money & Capital F Market

C

C4567^ Financial Management

C3456 Database Systems F

crCode crTitle fId

(σ rank = ‘Asso Prof’

(FACULTY)) X COURSE

Equijoin

 Rows are joined on the basis of

values of a common attribute

between the two relations

 Rows having he same value in

the common attribute are joined

facId facName dept salary rank

F

F

F

F

Samad MNG Professor

Ayesha ENG Asso Prof

Tahir CSE Asso Prof

Usman CSE lecturer

COURSE

Introduction to F Accounting

C

Money & Capital F Market

C

C4567 Financial Management

C3456 Database Systems F

crCode crTitle fId

FACULTY

Equijoin Example

FACULTY FACULTY.facId = COURSE.fId COURSE

F2345 Usman CSE 21000 lecturer

F4567 Ayesha ENG 27000 Asso Prof

F2345 Usman CSE 21000 lecturer

facId facName dept salary Rank

Introduction to F Accounting

C

Money & Capital F Market

C

C3456 Database Systems F

crCode crTitle fId

Equijoin Example

FACULTY FACULTY.facId = COURSE.fId COURSE

Natural Join

 Also called simply the join, most

general form of join

 Same as equijoin with common

column appearing once

Natural Join Example

 facName, crTitle (FACULTY

FACULTY.facId, COURSE.facId COURSE)

facId facName dept salary rank

F

F F F 32000 27000 23000 21000

Samad MNG Professor Ayesha ENG Asso Prof Tahir CSE Asso Prof Usman CSE lecturer

COURSE

Introduction to F Accounting

C

Money & Capital F Market

C

C4567 Financial Management C3456 Database Systems F

crCode crTitle facId

FACULTY

Introduction to

Database Systems

Lecture 13

Fall 2011

F2345 Usman CSE 21000 lecturer

F4567 Ayesha ENG 27000 Asso Prof

F2345 Usman CSE 21000 lecturer

facId facName dept salary Rank

Introduction to Accounting

C

Money & Capital Market

C

C3456 Database Systems

crCode crTitle

Natural Join Example

FACULTY FACULTY.facId, COURSE.facId COURSE

Practice Queries

 Show all the programs offered

by university

 Show the total semesters and

credits in each program

 Show the faculty name, salary

and rank

Practice Queries

 Show the records of students who

do not have telephone

 Show the mid-term marks, sessional

and final marks of each student

 Show the name, stId and semester

result of all students for the current

semester

Practice Queries

 List the names of the courses,

names of faculty who are

teaching those courses in

current semester

 Show the name of the student

and names of the courses in

which he has registered so far

 Show the name of the student

and names of the courses in

which he has registered in the

current semester