Page 37 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes i.e., join A and B based on same condition. This join is equivalent to performing a cartesian
product on two relations followed by a selection operator. Thus,
A c B= c A B

The application of conditional join on relations employee and Dept_location results into a new
relation whose degree is 11.

Degree

The degree of a relation is equal to the number of fields (columns).
Consider the equation

Projection 7

Employee D. No >7 Dept_location
This statement means:
The relation employee and Deptlocation are joined based on the condition DNo>7 and the
resulting relation is projection 7. Projection 7 will include all the tuples from employee
and dept-location where DNo>7.
The operators which can be used with condition in order to compare the two relations are
“>, <, , ,

Ename Ed Bdate Address Sex Salary Dno Phone Age DNo Address
number (years)
Brown 12345264 28-7-1968 Chicago M 40000 8 773210192 28 8 Detroit
John 12345261 1-08- 1965 New M 25000 9 773213218 41 9 Chicago
Jersey
Brown 12345264 28-7-1968 Chicago M 40000 8 773271872 51 8 Detroit
Bill 12345265 25-7-1955 Detroit F 35000 8 773271842 51 9 Chicago
Bill 12345265 25-7-1955 Detroit F 35000 8 773271842 52 9 Chicago
Jill 12345266 04-4-1965 New F 42000 8 773291828 41 8 Detroit
York
Jill 12345266 04-4-1965 New F 42000 8 773291628 41 9 Chicago
York
Donald 12345267 02-8-1768 Detroit M 20000 9 773423145 28 8 Detroit
Donald 1234526 62-8-1768 Detroit M 20000 9 773423175 28 9 Chicago
John 12345261 10-8-1965 New M 25000 8 773213218 41 8 Detroit
Jersey

Project 7 = Employee Dept_location.
DNo >7
Equi-join

Equi-join is same as conditional join, the only difference being only equity ‘=’ operator is used
to join the two relations.

Example: We may join DEPARTMENT and DEPTJLOCATION relation with the condition
that

DNo . Department = DNo .Dept_location.
1 2

30 LOVELY PROFESSIONAL UNIVERSITY