1 / 20

Relational Algebra : #I

Relational Algebra : #I. Based on Chapter 2.4 & 5.1. Relational Languages. Query Language : Define data retrieval operations for relational model Express easy access to large data sets in high-level language, not complex application programs Languages

Jimmy
Download Presentation

Relational Algebra : #I

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. Relational Algebra : #I Based on Chapter 2.4 & 5.1 cs3431

  2. Relational Languages • Query Language : • Define data retrieval operations for relational model • Express easy access to large data sets in high-level language, not complex application programs • Languages • Relational Algebra : procedural semantics based on set or bag theory • Relational Calculus : logic-based language of denoting what is to be retrieved (but not how) • SQL: syntactic sugar for relational calculus. cs3431

  3. Basics • Relational Algebra is defined on bags --- versions for relations (sets) exist too. • Bag or multi-set : • allow duplicate values; but order is not significant. cs3431

  4. Basics • Query Algebra : • nested expression of algebra operators that accept as input relations and outputs a relation • Example Query : • SELECT [gpa > 3.0] ( UNION (Ugrads,Grads) ) • Closure of Relational Algebra : • operators work on relations and returns a relation cs3431

  5. Relational Algebra Basics • Relational algebra includes : • set operators, and • other operators specific to relational model. cs3431

  6. Set Operators • Union, Intersection, Difference • Defined only for union compatible relations. • Relations are union compatible if • they have same sets of attributes and • the same types (domains) of attributes • Example : Union compatible or not? • Student (sNumber, sName) • Course (cNumber, cName) cs3431

  7. Union:  • Consider two bags R1 and R2 that are union-compatible. R1  R2 R1 R2 Suppose a tuple t appears in R1m times, and in R2ntimes. Then in the union, t appears m + n times. cs3431

  8. Intersection: ∩ • Consider two bags R1 and R2 that are union-compatible. • Suppose tuple t appears in R1m times, and in R2n times. • Then in intersection, t appears min (m, n) times. R1 R2 R1 ∩R2 cs3431

  9. Difference: - • Consider two bags R1 and R2 that are union-compatible. • Suppose tuple t appears in R1m times & in R2n times. • Then in R1 – R2, t appears max (0, m - n) times. R1 R2 R1 – R2 cs3431

  10. Idempotent property • Idempotent property : • Operation applied twice gives same result as when applied once • Example : • Filter-BLUE ( Filter-BLUE ( images )) cs3431

  11. Bag vs Set Semantics • Union is idempotent for sets: (R1  R2)  R2 = R1  R2 • What about union for bags ? • Union is not idempotent for bags. • What about intersection ? • Intersection is idempotent for sets • Intersection is idempotent for bags • What about difference ? • Difference is idempotent for sets • Difference is not idempotent for bags cs3431

  12. Bag vs Set Semantics • R1R2 = R1 – (R1 – R2 ). • For sets ? • For bags ? • Yes. True for both. cs3431

  13. Cross Product (Cartesian Product): X • Consider two bags R1 and R2. • Suppose a tuple t1 appears in R1m times, and a tuple t2 appears in R2n times. • Then in R1 X R2, t1t2 appears m*ntimes. R1 XR2 R1 R2 cs3431

  14. Basic Relational Operations • Select, Project cs3431

  15. Basic Relational Operations • Select: σC (R): • selects subset of tuples of R that satisfies selection condition C. σ(C ≥ 6) (R) R cs3431

  16. Select • Select is commutative: • σC2 (σC1 (R)) = σC1 (σC2 (R)) • Select is idempotent: • σC (σC (R)) = σC (R) • We can combine multiple select conditions into one condition. • σC1 (σC2 (… σCn (R)…)) = σC1 AND C2 AND … Cn (R) cs3431

  17. Project: πA1, A2, …, An (R) • πA1, A2, …, An (R), with A1, A2, …, An  attributes AR • returns tuples in R, but only columns A1, A2, …, An. πA, B (R) R cs3431

  18. Project: Bag vs Set Semantics • For bags, cardinality of R = cardinality of πA1, A2, …, An (R). • For sets, cardinality of R ≥ cardinality of πA1,A2, …, An (R). • For sets and bags • Is project commutative ? πAπB ( Relation ) • NO ! • Is project idempotent ? πA πA ( Relation ) • YES ! cs3431

  19. Equivalences with Select/Project • σ [sal>100k) (π sal ( Employee )) = πsal ( σ [sal>100k) ( Employee )) • σ [sal>100k) (πsal,name ( Employee )) = πsal,name ( σ [sal>100k) (πsal,name ( Employee )) • σ [sal>100k) (π name ( Employee )) = πname ( σ [sal>100k) ( Employee )) cs3431

  20. Summary So Far • Key Property: • Closure of Relational Algebra • Basic Operators: • Set Operators: Union, Intersection, Difference • Cartesian Product (simple form of “Join”) • Select, Project • Logical Rewrite Rules: • Idempotent, commutative, associative. cs3431

More Related