Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

DataBase Development and Implementation Lec06, Study notes of Database Management Systems (DBMS)

University of GreenwichDatabase Management Systems (DBMS)

"Detail Summery about Relational DB Languages Relational Algebra, Meaning of the term relational Completeness, Relational algebra, Relational Algebra Basic

Typology: Study notes

2010/2011

Uploaded on 09/08/2011

rossi46
rossi46 🇬🇧

4.5

(10)

313 documents

1 / 6

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
DBDI // Rel Algebra 30-May-07
Lecture 6 1
DBDI/ Lecture 6
Relational DB Languages
Relational Algebra
Dr. Ala Al-Zobaidie
The slides are based on the textbook Database Systems by Connolly & Begg
30/05/2007 DBDI / Rel Algebra 2
Lecture’s Objectives
• Basis of Relational DB Language
• Meaning of the term relational
Completeness.
• Relational algebra.
• Relational Algebra Basic & Derived
Operations
30/05/2007 DBDI / Rel Algebra 3
Introduction
• Relational algebra & relational calculus are formal
languages associated with the relational model.
• Informally:
– relational algebra is a (high-level) procedural language &
– relational calculus a non-procedural language.
• Formally both are equivalent to one another.
•A relationally complete language can perform all
basic, meaningful operations on relations.
• Any relational language as powerful as relational
algebra is called relationally complete.
• SQL is a superset of RA
30/05/2007 DBDI / Rel Algebra 4
Relational Algebra /1
• 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.
• It is a set-oriented language in which all tuples,
possibly from different relations, are manipulated in
one statement without looping.
• Allows expressions to be nested, just as in arithmetic.
This property is called closure.
30/05/2007 DBDI / Rel Algebra 5
Relational Algebra /2
• 8 operations in relational algebra:
•5 basic:
– Traditional Set Operations:
• Selection, Projection, Cartesian product, Union, & Set
Difference.
• These perform most of the data retrieval operations needed.
• 3 additional:
– Derived Set operations:
• Intersection, Join & Division
• Derived operations can be expressed in terms of the above 5
basic operations
30/05/2007 DBDI / Rel Algebra 6
Relational Algebra Operations
pf3
pf4
pf5

Partial preview of the text

Download DataBase Development and Implementation Lec06 and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

DBDI/ Lecture 6

Relational DB Languages

Relational Algebra

Dr. Ala Al-Zobaidie

The slides are based on the textbook Database Systems by Connolly & Begg

30/05/2007 DBDI / Rel Algebra 2

Lecture’s Objectives

• Basis of Relational DB Language

• Meaning of the term relational

Completeness.

• Relational algebra.

• Relational Algebra Basic & Derived

Operations

30/05/2007 DBDI / Rel Algebra 3

Introduction

  • Relational algebra & relational calculus are formal languages associated with the relational model.
  • Informally:
    • relational algebra is a (high-level) procedural language &
    • relational calculus a non-procedural language.
  • Formally both are equivalent to one another.
  • A relationally complete language can perform all basic, meaningful operations on relations.
  • Any relational language as powerful as relational algebra is called relationally complete.
  • SQL is a superset of RA

30/05/2007 DBDI / Rel Algebra 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.
  • It is a set-oriented language in which all tuples, possibly from different relations, are manipulated in one statement without looping.
  • Allows expressions to be nested, just as in arithmetic. This property is called closure.

Relational Algebra /

  • 8 operations in relational algebra:
  • 5 basic:
    • Traditional Set Operations:
      • Selection, Projection, Cartesian product, Union, & Set Difference.
      • These perform most of the data retrieval operations needed.
  • 3 additional:
    • Derived Set operations:
      • Intersection, Join & Division
      • Derived operations can be expressed in terms of the above 5 basic operations

Relational Algebra Operations

30/05/2007 DBDI / Rel Algebra 7

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 ).

30/05/2007 DBDI / Rel Algebra 8

Example - Selection (or Restriction)

• List all staff with a salary greater than

σsalary > 10000 (Staff)

30/05/2007 DBDI / Rel Algebra 9

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.

30/05/2007 DBDI / Rel Algebra 10

Example - Projection

• Produce a list of salaries for all staff,

showing only staffNo, fName, lName, and

salary details.

ΠstaffNo, fName, lName, salary(Staff)

Union

• R ∪ S

  • Union of two relations R and S defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated.
  • R and S must be union-compatible.

• If R and S have I and J tuples,

respectively, union is obtained by

concatenating them into one relation with a

maximum of ( I + J ) tuples.

Example - Union

• List all cities where there is either a branch

office or a property for rent.

Πcity(Branch) ∪ Πcity(PropertyForRent)

30/05/2007 DBDI / Rel Algebra 19

Example - Cartesian Product and Selection

• Use selection operation to extract those

tuples where Client.clientNo =

Viewing.clientNo.

σClient.clientNo = viewing.clientNo ((∏clientNo,fName,lName (Client))^ Χ (∏clientNo,propertyNo,comment (Viewing)))

• Cartesian product and Selection can be reduced

to a single operation called a Join.

30/05/2007 DBDI / Rel Algebra 20

Join Operations

• Join is a derivative of Cartesian product.

• Equivalent to performing a Selection,

using join predicate as selection formula,

over Cartesian product of the two

operand relations.

• One of the most difficult operations to

implement efficiently in a RDBMS, and

one reason why RDBMSs have intrinsic

performance problems.

30/05/2007 DBDI / Rel Algebra 21

Join Operations

• Various forms of join operation

– Theta join

– Equijoin (a particular type of Theta

join)

– Natural join

– Outer join

– Semijoin

30/05/2007 DBDI / Rel Algebra 22

Relational Algebra Operations

  • R (^) F S
    • Defines a relation that contains tuples satisfying the predicate F from the Cartesian product of R and S.
    • The predicate F is of the form R.ai θ S.b (^) i where θ may be one of the comparison operators between two fields from two tables via some comparison operator (<, ≤, >, ≥, =, ≠).

Theta join (θ-join)

Theta join (θ-join)

  • Can rewrite Theta join using basic Selection and Cartesian product operations. - R FS = σF(R Χ S)
  • The theta-join is similar to the Cartesian product followed by a select.
  • Degree of a Theta join is sum of degrees of the operand relations R and S. If predicate F contains only equality (=), the term Equijoin is used.

30/05/2007 DBDI / Rel Algebra 25

Example - Equijoin

• List the names and comments of all

clients who have viewed a property for

rent.

(ΠclientNo,fName,lName (Client)) (^) Client.clientNo = Viewing.clientNo (ΠclientNo,propertyNo,comment (Viewing))

30/05/2007 DBDI / Rel Algebra 26

Natural Join

• R S

  • An Equijoin of the two relations R and S over all common attributes x.
  • One occurrence of each common attribute is eliminated from the result.

30/05/2007 DBDI / Rel Algebra 27

Example - Natural Join

• List the names and comments of all clients

who have viewed a property for rent.

(ΠclientNo,fName,lName (Client)) (ΠclientNo,propertyNo,comment(Viewing))

30/05/2007 DBDI / Rel Algebra 28

Outer join

• To display rows in the result that do not

have matching values in the join column,

use Outer join.

• R S

  • (Left) outer join is join in which tuples from R that do not have matching values in common columns of S are also included in result relation.

Example - Left Outer join

• Produce a status report on property

viewings.

Π propertyNo,street,city(PropertyForRent) Viewing

Semijoin

• R F S

  • Defines a relation that contains the tuples of R that participate in the join of R with S.

‹ Can rewrite Semijoin using Projection and Join:

R FS = Π A(R F S)