Relational Algebra: Understanding Operators, Expression Trees, and Bag Model of Data, Slides of Database Management Systems (DBMS)

Download Relational Algebra: Understanding Operators, Expression Trees, and Bag Model of Data and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Relational Algebra

Operators

Expression Trees

Bag Model of Data

What is an “Algebra”

• Mathematical system consisting of:

• Operands --- variables or values from which new

values can be constructed.

• Operators --- symbols denoting procedures that

construct new values from given values.

Roadmap

• There is a core relational algebra that has

traditionally been thought of as the relational

algebra.

• But there are several other operators we shall

add to the core in order to model better the

language SQL --- the principal language used

in relational database systems.

Core Relational Algebra

• Union, intersection, and difference.

• Usual set operations, but require both operands

have the same relation schema.

• Selection: picking certain rows.

• Projection: picking certain columns.

• Products and joins: compositions of relations.

• Renaming of relations and attributes.

Example

7

Relation Sells: store candy price 7-11 Twizzlers 2. 7-11 Kitkat 2. Kroger Twizzlers 2. Kroger Kitkat 3.

7-11Menu := SELECTstore=“7-11”(Sells): store candy price 7-11 Twizzlers 2. 7-11 Kitkat 2.

Projection

• R1 := PROJ L (R2)

• L is a list of attributes from the schema of R2.
• R1 is constructed by looking at each tuple of R2,

extracting the attributes on list L , in the order

specified, and creating from those components a

tuple for R1.

• Eliminate duplicate tuples, if any.

Product

• R3 := R1 * R

• Pair each tuple t1 of R1 with each tuple t2 of R2.
• Concatenation t1t2 is a tuple of R3.
• Schema of R3 is the attributes of R1 and then R2,

in order.

• But beware attribute A of the same name in R

and R2: use R1. A and R2. A.

Example: R3 := R1 * R

11

R1( A, B )

R2( B, C )

R3( A, R1.B, R2.B, C )

Example

13

Sells( store, candy, price ) Stores(name, addr ) 7-11 Twiz. 2.50 7-11 Maple St. 7-11 Kitkat 2.75 Kroger River Rd. Kroger Twiz. 2. Kroger Pez 3.

StoreInfo := Sells JOIN (^) Sells.store = Stores.name Stores

StoreInfo(store, candy, price, name, addr ) 7-11 Twiz. 2.50 7-11 Maple St. 7-11 Kitkat 2.75 7-11 Maple St. Kroger Twiz. 2.50 Kroger River Rd. Kroger Pez 3.00 Kroger River Rd.

Natural Join

• A frequent type of join connects two relations

by:

• Equating attributes of the same name, and
• Projecting out one copy of each pair of equated

attributes.

• Called natural join.

• Denoted R3 := R1 JOIN R2.

Renaming

• The RENAME operator gives a new schema

to a relation.

• R1 := RENAME R1(A1,…,A n ) (R2) makes R1 be a

relation with attributes A1,…,A n and the

same tuples as R2.

• Simplified notation: R1(A1,…,A n ) := R2.

Example

17

Stores(name, addr ) 7-11 Maple St. Kroger River Rd.

R( store, addr ) 7-11 Maple St. Kroger River Rd.

R(store, addr) := Stores

Sequences of Assignments

• Create temporary relation names.

• Renaming can be implied by giving relations a

list of attributes.

• Example: R3 := R1 JOIN C R2 can be written:

R4 := R1 * R

R3 := SELECT C (R4)

Expressions in a Single Assignment

• Example: the theta-join

R3 := R1 JOIN C R2 can be written

R3 := SELECT C (R1 * R2)

• Precedence of relational operators:

– [SELECT, PROJECT, RENAME] (highest).

– [PRODUCT, JOIN].

– INTERSECTION.

– [UNION, --]