chapter 6 the relational algebra and relational calculus
DESCRIPTION
Chapter 6 The Relational Algebra and Relational Calculus. Chapter 6 Outline. Tekli İlişkisel Operasyon: SELECT ve PROJE CT Kümeler Kuramı ile İlişkisel Cebir İkili ilişkisel işlemler: JOIN ve BÖLÜMÜ Diğer işlemler. Chapter 6 Outline (cont’d.). Ilişkisel işlemler örnekleri - PowerPoint PPT PresentationTRANSCRIPT
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley
Chapter 6
The Relational
Algebra and Relational
Calculus
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
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 andRelational 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 Operationsfrom 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 Operationsfrom 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 Queriesin Relational Algebra (cont’d.)
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Examples
Copyright © 2011 Ramez Elmasri and Shamkant Navathe
Examples of Queriesin 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 Formulasin 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