Page 38 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 2: Database Relational Model

Where DNo and DNo are two instances of respective relations. This condition indicates - join Notes
1 2
the tuples where DNo = DNo . The degree of resulting relation will be the sum of degrees of
1 2
two relation minus the number of fields they have in common. More precisely,
The degree of relation A is x
The degree of relation B is y and the number of common fields is z
Then degree of resulting relation = x + y – z.

Projection 8

DEPARTMENT No1.DEPARTMENT =DNo2.DEPT-LOCATION DEPT_LOCATION projection 8 will contain
all the fields of DEPARTMENT AND DEPT_LOCATION AND the common fields will be
included only once.
Project 8: Department Dept-location
c
Where c = DNo .Department = DNo .Dept_location
1 2
DName DNo Manager-id DLocation
Administration 7 2431 New York
Research 8 3731 Detroit
Head Quarters 9 4341 Chicago

Natural Join

This is the default join operation i.e., no condition is specified. Natural join is equivalent to
cartesian product. If two relations have a common field then the application of natural join is
equivalent to equi join and if no field is common then the natural join is cartesian product of the
two relations.
We can denote the operation as,
A B where A and B are two relations.
If we apply natural join operation as Departments and dept-location then the result will be same
as projection 8 as they have only DNo field in common.

Outer Joins

This is a special case “join” operator which considers the NULL values. Generally a ‘join’ operation
performs the cross product of two tables and applies certain join conditions. Then it selects those
rows from the cross product that satisfies the given condition. But with outer joins, DBMS allows
us to select those rows which are common (satisfies the given) and even those rows that do not
satisfy the given condition. To understand this, consider simple instances of project and department
table as shown.

Department D1 Project P1
Dept_Mid DNo PNo PNo Pname
101 2 11 44 D
97 5 22 11 A
120 4 33 22 B
If we perform join operation on these two tables.

LOVELY PROFESSIONAL UNIVERSITY 31