Transcript Slide 1

Chapter 6
The Relational
Algebra and
Relational
Calculus
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Chapter 6 Outline
 Tekli İlişkisel Operasyon: SELECT ve
PROJECT
 Kümeler Kuramı ile İlişkisel Cebir
 İkili ilişkisel işlemler: JOIN ve BÖLÜMÜ
 Diğer işlemler
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Chapter 6 Outline (cont’d.)
 Ilişkisel işlemler örnekleri
 Tuple İlişkisel Hesaplama
 Domain İlişkisel Hesaplama
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
The Relational Algebra and
Relational Calculus
 İlişkisel Cebir

Ilişkisel model için operasyonların temel seti
 İlişkisel cebir ifadesi

Ilişkisel cebir işlemleri sırası
 Bağıntı analizi

Ilişkisel sorgular belirtmek için üst düzey
açıklayıcı bir dil
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Unary Relational Operations:
SELECT and PROJECT
 SELECT işlevi

Bir seçim koşulu karşılayan bir ilişki dizilerini
Altkümesi :
• Boole ifadesi formun hükümler içeren
• <attribute name> <comparison op> <constant value>
or
• <attribute name> <comparison op> <attribute
name>
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Unary Relational Operations:
SELECT and PROJECT (cont’d.)
 Örnek:
 <selection condition> R içindeki her tuple t
ye ayrı olarak uygulanır
 Koşul DOĞRU olarak değerlendirilirse,
tuple seçilir
 Koşullar AND, OR, and NOT
 Tekli

(tek ilişki üzerinde çalışır)
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Unary Relational Operations:
SELECT and PROJECT (cont’d.)
 Seçicilik

Bir seçim koşulu tarafından seçilen dizilerini bir
kesimi
 SELECT işlev değişmeli
 Farlı SELECT işlemlerini AND Operatörü
kullanarak basamakla
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
The PROJECT Operation
 Tablodan sütun seçer ve diğer sütunları
atar:
 Degree

Niteliklerin sayısı <attribute list>
 Çiftlerin eliminanasyonu

PROJECT işlem sonucu farklı tuple dizileridir
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Sequences of Operations and
the RENAME Operation
 In-line expression:
 Işemlerin sırası, oluşan ara ilişkilerin
gösterilmesi:
 Rename Ara sonuçların öznitelikleri
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Relational Algebra Operations
from Set Theory
 UNION, INTERSECTION, and MINUS
Çeşitli şekillerde iki set elemanları Birleştirme
 İkili işlemler
 İlişki dizilerini aynı tür olmalıdır

 UNION
R∪S
 R veya/ ve S içindeki tüm tupleları içerir
 Çift tuples elenir

Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Relational Algebra Operations
from Set Theory (cont’d.)
 INTERSECTION
R∩S
 Hem R ve hem de S olan tüm dizilerini
içermektedir

 SET DIFFERENCE (or MINUS)
R–S
 S de olmayıp R de olan tüm tuple ları içerir

Copyright © 2011 Ramez Elmasri and Shamkant Navathe
The CARTESIAN PRODUCT
(CROSS PRODUCT) Operation
 KRTEZYEN ÇARPIM





Çapraz Çarpım or Çapraz Birleşim
× ile gösterilir
Ikili küme işlemidir.
Ilişkilerin UNION uyumlu olmasıan gerek yoktur
Niteliklerin arasında bir seçim ile kullanılırsa
anlamlı olacaktır.
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Binary Relational Operations:
JOIN and DIVISION
 The JOIN işlemi

gösterimi
 Iki ilişki içindeki Alakalı kayıtları tek ve “uzun”
bir kayıtta birleştirir
 Genel Join şartları <condition> AND
<condition> AND...AND <condition>
 Example:
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Binary Relational Operations:
JOIN and DIVISION (cont’d.)
 THETA JOIN





Her <condition> formunda ki Ai θ Bj
Ai , R nin bir niteliği
Bj , S nin bir niteliği
Ai ve Bj aynı domain e sahipler
θ (theta) bir kıyaslama operatörüdür:
• {=, <, ≤, >, ≥, ≠}
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Variations of JOIN: The
EQUIJOIN and NATURAL JOIN
 EQUIJOIN
Sadece = karşılaştırma operatörü
 Aynı değere sahip kayıtları içerir ayrıca tüm
alanları içerir

 NATURAL JOIN

*, Gözterilir
 ikinci nitelik içindeki (fazladan ) nitelikleri
kaldırır EQUIJOIN farklı olarak
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Variations of JOIN: The
EQUIJOIN and NATURAL JOIN
(cont’d.)
 Join seçiciliği

Beklenen büyüklükteki birleşmenin max
büyüklüğe bölünmesi nR * nS
 Inner joins
Eşle ve birleştir operasyonudur.
 Daha önceden Kartezyen çarpım
kombinasyonu olarak ve Seçim olarak iffade
edildi

Copyright © 2011 Ramez Elmasri and Shamkant Navathe
A Complete Set of Relational
Algebra Operations
 Set of relational algebra operations {σ, π,
∪, ρ, –, ×} is a complete set

Any relational algebra operation can be
expressed as a sequence of operations from
this set
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
The DIVISION Operation
 Denoted by ÷
 Example: retrieve the names of employees
who work on all the projects that ‘John
Smith’ works on
 Apply to relations R(Z) ÷ S(X)

Attributes of R are a subset of the attributes of
S
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Operations of Relational Algebra
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Operations of Relational Algebra
(cont’d.)
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Notation for Query Trees
 Query tree

Represents the input relations of query as leaf
nodes of the tree
 Represents the relational algebra operations
as internal nodes
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Additional Relational Operations
 Generalized projection

Allows functions of attributes to be included in
the projection list
 Aggregate functions and grouping

Common functions applied to collections of
numeric values
 Include SUM, AVERAGE, MAXIMUM, and
MINIMUM
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Additional Relational Operations
(cont’d.)
 Group tuples by the value of some of their
attributes

Apply aggregate function independently to
each group
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Recursive Closure Operations
 Operation applied to a recursive
relationship between tuples of same type
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
OUTER JOIN Operations
 Outer joins

Keep all tuples in R, or all those in S, or all
those in both relations regardless of whether or
not they have matching tuples in the other
relation
 Types
• LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL
OUTER JOIN

Example:
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
The OUTER UNION Operation
 Take union of tuples from two relations that
have some common attributes

Not union (type) compatible
 Partially compatible

All tuples from both relations included in the
result
 Tut tuples with the same value combination will
appear only once
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Examples of Queries
in Relational Algebra (cont’d.)
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Examples
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Examples of Queries
in Relational Algebra (cont’d.)
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
The Tuple Relational Calculus
 Declarative expression

Specify a retrieval request nonprocedural
language
 Any retrieval that can be specified in basic
relational algebra

Can also be specified in relational calculus
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Tuple Variables and Range
Relations
 Tuple variables

Ranges over a particular database relation
 Satisfy COND(t):
 Specify:

Range relation R of t
 Select particular combinations of tuples
 Set of attributes to be retrieved (requested
attributes)
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Expressions and Formulas
in Tuple Relational Calculus
 General expression of tuple relational
calculus is of the form:
 Truth value of an atom

Evaluates to either TRUE or FALSE for a
specific combination of tuples
 Formula (Boolean condition)

Made up of one or more atoms connected via
logical operators AND, OR, and NOT
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Existential and Universal
Quantifiers
 Universal quantifier (∀)
 Existential quantifier (∃)
 Define a tuple variable in a formula as free
or bound
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Sample Queries in Tuple
Relational Calculus
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Notation for Query Graphs
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Transforming the Universal and
Existential Quantifiers
 Transform one type of quantifier into other
with negation (preceded by NOT)

AND and OR replace one another
 Negated formula becomes unnegated
 Unnegated formula becomes negated
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Using the Universal Quantifier in
Queries
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Safe Expressions
 Guaranteed to yield a finite number of
tuples as its result

Otherwise expression is called unsafe
 Expression is safe

If all values in its result are from the domain of
the expression
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
The Domain Relational Calculus
 Differs from tuple calculus in type of
variables used in formulas

Variables range over single values from
domains of attributes
 Formula is made up of atoms

Evaluate to either TRUE or FALSE for a
specific set of values
• Called the truth values of the atoms
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
The Domain Relational Calculus
(cont’d.)
 QBE language

Based on domain relational calculus
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Summary
 Formal languages for relational model of
data:

Relational algebra: operations, unary and
binary operators
 Some queries cannot be stated with basic
relational algebra operations
• But are important for practical use
 Relational calculus

Based predicate calculus
Copyright © 2011 Ramez Elmasri and Shamkant Navathe