Mister

ITRW 311 Mr R Botes Building 6-105 (Office hours will still be given) [e-mail]

DATABASES I

1

Chapter 4 The Relational Database Model
Textbook p116-121 Study Unit: 4

2

In this chapter, you will learn:
• What is meant by relational algebra? • How to manipulate database tables using relational set operators • How the DBMS supports the key relational operators: select, project and join. • The different types of joins. • How to write queries using relational algebra expressions.

3

Relational Algebra
• Relational algebra and relational calculus are the mathematical basis for ‘relational databases’. • Relational algebra…….
– Defines theoretical way of manipulating table contents using relational operators – Use of relational algebra operators on existing tables (relations) produces new relations

• Codd defined eight relational operators:

4

Relational Algebra Operators
• SELECT • PROJECT

• UNION
• INTERSECT

• DIFFERENCE • DIVIDE • PRODUCT
• JOIN

5

Re-visiting Set Theory

6

SELECT
• Yields values for all rows found in a table • Can be used to list either all row values or it can yield only those row values that match a specified criterion • Yields a horizontal subset of a table

7

SELECT (continued)
• The SELECT operator denoted by σθ , is formally defined as: σθ (R) or σ (RELATION)

• where σθ (R) is the set of specified tuples of the relation R and θ is the predicate (or criterion) to extract the required tuples.

8

SELECT (continued)

9

SELECT (continued)

10

PROJECT
• Project
– Yields all values for selected attributes – Yields a vertical subset of a table

11

PROJECT (continued)
• The PROJECT operator, denoted by ∏, is formally defined as:
∏ a1…an (R) or ∏ (Relation)

• where the projection of the relation R, denoted by ∏ a1…an (R) is the set of specified attributes a1…an of the relation R.

12

PROJECT (continued)

13

Union
• Union:
–