Prepare for your exams
Get points
Guidelines and tips
Sell on Docsity
Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Earn points to download

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

Guidelines and tips

Sell on Docsity

Docsity AI

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

20 Points

For each uploaded document

Answer questions

5 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

Part 6: Relational Algebra, Lecture notes of Algebra

Imam Mohammad Ibn Saud Islamic University (IMSIU)Algebra

Relational Algebra. 6-6. Example Database (3). • RESULTS: one row for each submitted solution to an exercise. SID: Student who wrote the solution.

Typology: Lecture notes

2021/2022

Uploaded on 09/07/2022

nabeel_kk 🇸🇦

4.6

(65)

1.3K documents

1 / 117

This page cannot be seen from the preview

Don't miss anything!

6. Relational Algebra 6-1

Part 6: Relational Algebra

References:

•Elmasri/Navathe:Fundamentals of Database Systems, 3rd Edition, 1999.

Section 7.4 “Basic Relational Algebra Operations”,

Section 7.5 “Additional Relational Algebra Operations”,

Section 7.6 “Examples of Queries in Relational Algebra”

•Kemper/Eickler: Datenbanksysteme (in German), 4th Edition, 2001.

Section 3.4, “Die relationale Algebra” (“The Relational Algebra”)

•Silberschatz/Korth/Sudarshan: Database System Concepts, Third Edition, 1999.

Section 3.2: “The Relational Algebra”

•Lipeck: Skript zur Vorlesung Datenbanksysteme (in German), Univ. Hannover, 1996.

•Codd: A relational model of data for large shared data banks. Communications of the

ACM, 13(6), 377–387, 1970. Reprinted in CACM 26(1), 64–69, 1983.

See also: [http://www1.acm.org:81/classics/nov95/toc.html] (incomplete)

Stefan Brass: Datenbanken I Universit¨at Halle, 2004

Discover Lecture notes of Algebra Imam Mohammad Ibn Saud Islamic University (IMSIU)

Partial preview of the text

Download Part 6: Relational Algebra and more Lecture notes Algebra in PDF only on Docsity!

Part 6: Relational Algebra

References:

Elmasri/Navathe:Fundamentals of Database Systems, 3rd Edition, 1999.Section 7.4 “Basic Relational Algebra Operations”, Section 7.5 “Additional Relational Algebra Operations”,Section 7.6 “Examples of Queries in Relational Algebra”
Kemper/Eickler: Datenbanksysteme (in German), 4th Edition, 2001.Section 3.4, “Die relationale Algebra” (“The Relational Algebra”)
Silberschatz/Korth/Sudarshan: Database System Concepts, Third Edition, 1999.Section 3.2: “The Relational Algebra”
Lipeck: Skript zur Vorlesung Datenbanksysteme (in German), Univ. Hannover, 1996.
Codd: A relational model of data for large shared data banks. Communications of theACM, 13(6), 377–387, 1970. Reprinted in CACM 26(1), 64–69, 1983. See also: [http://www1.acm.org:81/classics/nov95/toc.html] (incomplete)

Objectives

After completing this chapter, you should be able to:

enumerate and explain the operations of relational algebra. Especially, you should know the five basic operations.
write relational algebra queries of the type “join- select-project”. Plus simple queries involving set difference and union.
discuss correctness and equivalence of given rela- tional algebra queries.

Example Database (1)

STUDENTS

SID FIRST LAST EMAIL

101 Ann Smith · · · 102 Michael Jones (null) 103 Richard Turner · · · 104 Maria Brown · · ·

EXERCISES CAT ENO TOPIC MAXPT H 1 Rel. Algeb. 10 H 2 SQL 10 M 1 SQL 14

RESULTS

SID CAT ENO POINTS

101 H 1 10

101 H 2 8

101 M 1 12

102 H 1 9

102 H 2 9

102 M 1 10

103 H 1 5

103 M 1 7

Example Database (2)

STUDENTS: one row for each student in the course.

SID: “Student ID” (primary key). FIRST, LAST: First and last name. EMAIL: Email address (can be null).

EXERCISES: one row for each graded exercise.

CAT: Exercise category (key together with ENO). E.g. ’H’: homework, ’M’: midterm exam, ’F’: final exam. ENO: Exercise number (within category). TOPIC: Topic of the exercise. MAXPT: Max. no. of points (How many points is it worth?).

Relational Algebra (1)

Relational algebra (RA) is a theoretical query lan- guage for the relational model.
Relational algebra is not used in any commerical system on the user interface level.
However, variants of it are used to represent queries internally (for query optimization and execution).
Knowledge of relational algebra will help in under- standing SQL and relational database systems. E.g. one talks about “joins” (a relational algebra operation) even when discussing SQL queries. Explicit joins were added in SQL-92.

Relational Algebra (2)

An algebra is a set together with operations on this

set.

For instance, the set of integers together with the

operations + and ∗ forms an algebra.

In the case of relational algebra, the set is the set

of all finite relations.

One operation of relational algebra is ∪ (union).

This is natural since relations are sets.

Relational Algebra (4)

Since the output of a relational algebra operation

is again a relation, it can be input for another re- lational algebra operation. And so on, until the query result is computed (again a relation). The relational algebra is so simple because the relational model has only a single construct: The relation.

A query is then a term/expression in this algebra.
Arithmetic expressions like (x + 2) ∗ y are familiar.
In relational algebra, relations are connected:

πFIRST, LAST(STUDENTS @@ σCAT=’M’(RESULTS)).

Relational Algebra (5)

Minor Data Model Differences to SQL:

Null values are usually excluded in the definition of relational algebra, except when operations like the outer join are defined (last section of this chapter). Even for the outer join, the null value is treated simply like an addi- tional value added to every data type. Using a three-valued logic as in SQL would make the definitions significantly more complicated.
Relational algebra treats relations as sets, i.e. any duplicate tuples are automatically eliminated. In SQL, relations are multisets and can contain duplicates. If neces- sary, one has to request duplicate elimination explicitly (“DISTINCT”). In relational algebra, one does not have to think about duplicates.

Selection (1)

• The operation σϕ selects a subset of the tuples of

a relation, namely those which satisfy the conditi- on ϕ. Selection acts like a filter on the input set. σ is the greek letter sigma, ϕ is the greek letter phi. All textbooks use σ for selection, but ϕ is not standard. In ASCII, write e.g. SELECTcondition.

Example:

σA=

    

A B

    

A B

Selection (2)

The selection condition has the following form:

〈Term〉〈Comparison-Operator〉〈Term〉

The selection condition returns a boolean value (true or false) for a given input tuple.
〈Term〉 (or “expression”) is something that can be evaluated to a data type element for a given tuple: an attribute name, a data type constant, or an expression composed from attributes and con- stants by data type operations like +, −, ∗, /.

Selection (4)

• σϕ(R) can be implemented as:

(1) Create new temporary relation T ; (2) foreach tuple t from input relation R do (3) Evaluate condition ϕ for tuple t; (4) if true then (5) insert t into T ; (6) fi (7) od; (8) return T ;

With other data structures (e.g. a B-tree index), it

might be possible to compute σϕ(R) without rea-

ding each tuple of the input relation.

Selection (5)

Of course, the attributes used in the selection con- dition must appear in the input table:

σC=

  

A B

 

 = Error

The following is legal, but the selection is super- fluous, because the condition is always true:

σA=A

  

A B

 

A B

Selection (7)

• σϕ(R) corresponds to the following SQL query:

SELECT *

FROM R

WHERE ϕ

I.e. selection corresponds to the WHERE-clause.
A different relational algebra operation called “pro-

jection” corresponds to the SELECT-clause in SQL. This can be slightly confusing.

Extended Selection (1)

In the basic selection operation, only simple con- ditions consisting of a single comparison (“atomic formula”) are possible.
However, one can extend the possible conditions by permitting to combine the single conditions by the logical operators ∧ (and), ∨ (or), and ¬ (not): ϕ 1 ϕ 2 ϕ 1 ∧ ϕ 2 ϕ 1 ∨ ϕ 2 ¬ϕ 1 false false false false true false true false true true true false false true false true true true true false

Part 6: Relational Algebra, Lecture notes of Algebra

Related documents

Partial preview of the text

Download Part 6: Relational Algebra and more Lecture notes Algebra in PDF only on Docsity!

Part 6: Relational Algebra

Objectives

Example Database (1)

STUDENTS

SID FIRST LAST EMAIL

RESULTS

SID CAT ENO POINTS

101 H 1 10

101 H 2 8

101 M 1 12

102 H 1 9

102 H 2 9

102 M 1 10

103 H 1 5

103 M 1 7

Example Database (2)

Relational Algebra (1)

Relational Algebra (2)

Relational Algebra (4)

πFIRST, LAST(STUDENTS @@ σCAT=’M’(RESULTS)).

Relational Algebra (5)

Selection (1)

• The operation σϕ selects a subset of the tuples of

σA=

A B

A B

Selection (2)

Selection (4)

• σϕ(R) can be implemented as:

might be possible to compute σϕ(R) without rea-

Selection (5)

σC=

A B

 = Error

σA=A

A B

A B

Selection (7)

• σϕ(R) corresponds to the following SQL query:

SELECT *

FROM R

Extended Selection (1)