200 likes | 469 Views
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
E N D
Relational Algebra : #I Based on Chapter 2.4 & 5.1 cs3431
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
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
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
Relational Algebra Basics • Relational algebra includes : • set operators, and • other operators specific to relational model. cs3431
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
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
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
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
Idempotent property • Idempotent property : • Operation applied twice gives same result as when applied once • Example : • Filter-BLUE ( Filter-BLUE ( images )) cs3431
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
Bag vs Set Semantics • R1R2 = R1 – (R1 – R2 ). • For sets ? • For bags ? • Yes. True for both. cs3431
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
Basic Relational Operations • Select, Project cs3431
Basic Relational Operations • Select: σC (R): • selects subset of tuples of R that satisfies selection condition C. σ(C ≥ 6) (R) R cs3431
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
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
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
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
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