Ch04relationalalgebra 110310232134 phpapp01, Lecture notes of Database Management Systems (DBMS)

Download Ch04relationalalgebra 110310232134 phpapp01 and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

Chapter 4

Relational Algebra

2

Chapter 5 Objectives

 (^) Meaning of the term relational completeness.  (^) How to form queries in relational algebra.  (^) How to form queries in tuple relational calculus.  (^) How to form queries in domain relational calculus.  (^) Categories of relational DML.

4

Relational Algebra

 (^) Relational algebra operations work on one or more relations to define another relation without changing the original relations.  (^) Both operands and results are relations, so output from one operation can become input to another operation.  (^) Allows expressions to be nested, just as in arithmetic. This property is called closure.

5

Relational Algebra

 (^) Five basic operations in relational algebra: Selection, Projection, Cartesian product, Union, and Set Difference.  (^) These perform most of the data retrieval operations needed.  (^) Also have Join, Intersection, and Division operations, which can be expressed in terms of 5 basic operations.

7

Relational Algebra Operations

8

Selection (or Restriction)

predicate (R)

• (^) Works on a single relation R and defines a relation that contains only those tuples (rows) of R that satisfy the specified condition ( predicate ).

10

Projection

col1,... , coln(R)

• (^) Works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.

11

Example - Projection

 (^) Produce a list of salaries for all staff, showing only staffNo, fName, lName, and salary details. staffNo, fName, lName, salary(Staff)

13

Example - Union

 (^) List all cities where there is either a branch office or a property for rent. city(Branch)  city(PropertyForRent)

14

Set Difference

 R – S

• (^) Defines a relation consisting of the tuples that are in relation R, but not in S.
• (^) R and S must be union-compatible.

16

Intersection

 R  S

• (^) Defines a relation consisting of the set of all tuples that are in both R and S.
• (^) R and S must be union-compatible.  (^) Expressed using basic operations: R  S = R – (R – S)

17

Example - Intersection

 (^) List all cities where there is both a branch office and at least one property for rent. city(Branch)  city(PropertyForRent)

19

Example - Cartesian product

 (^) List the names and comments of all clients who have viewed a property for rent. (clientNo, fName, lName(Client)) X (clientNo, propertyNo, comment (Viewing))

Cartesian Product

20