1 / 46

Chapter 6 The Relational Algebra and Relational Calculus

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

Download Presentation

Chapter 6 The Relational Algebra and Relational Calculus

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Chapter 6 The Relational Algebra and Relational Calculus

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

  3. Chapter 6 Outline (cont’d.) • Ilişkisel işlemler örnekleri • Tuple İlişkisel Hesaplama • Domain İlişkisel Hesaplama

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

  5. Unary Relational Operations:SELECT and PROJECT • SELECT işlevi • Bir seçim koşulu karşılayan bir ilişkidizilerini Altkümesi: • Boole ifadesi formun hükümler içeren • <attribute name> <comparison op> <constant value> or • <attribute name> <comparison op> <attribute name>

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

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

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

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

  10. Relational Algebra Operationsfrom Set Theory • UNION, INTERSECTION, and MINUS • Çeşitli şekillerde iki set elemanları Birleştirme • İkili işlemler • İlişkidizilerini aynı tür olmalıdır • UNION • R ∪ S • R veya/ ve S içindeki tüm tupleları içerir • Çift tuples elenir

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

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

  13. 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:

  14. 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: • {=, <, ≤, >, ≥, ≠}

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

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

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

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

  19. Operations of Relational Algebra

  20. Operations of Relational Algebra (cont’d.)

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

  22. Additional Relational Operations • Generalized projection • Allows functions of attributes to be included in the projection list • Aggregatefunctions and grouping • Common functions applied to collections of numeric values • Include SUM, AVERAGE, MAXIMUM, and MINIMUM

  23. Additional Relational Operations (cont’d.) • Group tuples by the value of some of their attributes • Apply aggregate function independently to each group

  24. Recursive Closure Operations • Operation applied to a recursiverelationship between tuples of same type

  25. 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:

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

  27. Examples of Queriesin Relational Algebra (cont’d.)

  28. Examples

  29. Examples of Queriesin Relational Algebra (cont’d.)

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

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

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

  33. Existential and Universal Quantifiers • Universal quantifier (∀) • Existential quantifier (∃) • Define a tuple variable in a formula as free or bound

  34. Sample Queries in Tuple Relational Calculus

  35. Notation for Query Graphs

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

  37. Using the Universal Quantifier in Queries

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

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

  40. The Domain Relational Calculus (cont’d.) • QBE language • Based on domain relational calculus

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

More Related