Relational algebra DBMS, Lecture notes of Relational Database Management Systems (RDBMS)

Download Relational algebra DBMS and more Lecture notes Relational Database Management Systems (RDBMS) in PDF only on Docsity!

Relational Set Operators

DATABASE MANAGEMENT

SYSTEM

Objective

s

2 

To introduce the Relational Set Operators in

Relational Database Model

 Select

 Project

 Union

 Intersect

 Difference

 Product

 Join

Selec

t

4 SELECT, also known as RESTRICT, yields values for all rows found in a table that satisfy a given condition. (horizontal subset of a table)

Projec

t

5 PROJECT yields all values for selected attributes. (vertical subset of a table)

Intersec

t

7 INTERSECT yields only the rows that appear in both tables. (tables must be union-compatible, for example, you cannot use INTERSECT if one of the attributes is numeric and one is character-based).

Differenc

e

8 DIFFERENCE yields all rows in one table that are not found in the other table; that is, it subtracts one table from the other. (tables must be union-compatible) Note that subtracting the first table from the second table is not the same as subtracting the second table from the first table.

Joi

n

10 JOIN allows information to be combined from two or more tables. JOIN is the real power behind the relational database, allowing the use of independent tables linked by common attributes. For Example if we have following tables,

Natural

Join

11 A natural join links tables by selecting only the rows with common values in their common attribute(s). A natural join is the result of a three-stage process:

1. A PRODUCT of the tables is created.

Natural Join

(contd.)

13

3. A PROJECT is performed on the results of Step 2 to yield a single copy of each attribute, thereby eliminating duplicate columns.

Other Forms of

Join

14  (^) Another form of join, known as equijoin, links tables on the basis of an equality condition that compares specified columns of each table. The outcome of the equijoin does not eliminate duplicate columns, and the condition or criterion used to join the tables must be explicitly defined. The equijoin takes its name from the equality comparison operator (=) used in the condition.  (^) If any other comparison operator is used, the join is called a theta join.  An inner join is a join that only returns matched records from the tables that are being joined. (natural join, equijoin and theta join are often classified as inner join).  (^) In an outer join , the matched pairs would be retained, and any unmatched values in the other table would be left null.

Other Forms of Join

(contd.)

16

2. A right outer join yields all of the rows in the AGENT table, including those that do not have matching values in the CUSTOMER table.

Conclusio

n

In this Lecture, we… (^) Relational Set Operators in Relational  (^) Introduced following Database Model:  (^) Select  (^) Project  (^) Union  (^) Intersect  (^) Difference  (^) Product  (^) Join 17