1 / 25

Database Algebra Operators and Examples

Learn about relational algebra operators, syntax, semantics, and examples in advanced database systems. Explore union, intersection, join, and more.

jdrakeford
Download Presentation

Database Algebra Operators and Examples

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. 7.1 Introduction • Eight operators of the relational algebra: 1. The traditional set operators union, intersection, difference, and Cartesian product. 2. The special relational operators restrict, project, join, and divide. (See Fig. 7.1) Advanced Database System

  2. Advanced Database System

  3. 7.2 Closure Revisited • The output from any given relational operation is another relation. • Nested relational expressions Advanced Database System

  4. 7.3 The Original Algebra: Syntax • (See Page178-179) <relation exp> ::= Relation {<tuple exp commalist>} | <relvar name> | <relation op inv> | <with exp> | <introduced name> | (<relation exp>) • <relation op inv> ::= <project> | <nonproject> • <project> ::= <relation exp> { [All But] <attribute name commalist> } • <nonproject> ::= <rename> | <union> | <intersect> | <minus> • | <times> | <where> | <join> | <divide> Advanced Database System

  5. 7.3 The Original Algebra: Syntax (Cont.) <rename> ::= <relation exp> Rename ( <renaming commalist> ) <union> ::= <relation exp> Union <relation exp> <intersect> ::= <relation exp> Intersect <relation exp> <minus> ::= <relation exp> Minus <relation exp> <times> ::= <relation exp> Times <relation exp> <where> ::= <relation exp> Where <bool exp> <join> ::= <relation exp> Join <relation exp> <divide> ::= <relation exp> Divideby <relation exp> Per <per> <per> ::= <relation exp> | ( <relation exp>, <relation exp> ) <with exp> ::= With <name intro commalist> : <exp> <name intro> ::= <exp> As <introduced name> Advanced Database System

  6. 7.4 The Original Algebra: Semantics • Tuple-homogeneous • Union, Intersect, and Difference (See Fig. 7.2) • Product (See Fig. 7.3) If we need to construct the Cartesian product of two relations that do have any such common attribute names, we must use the Rename operator first to rename attributes appropriately. • Restrict (See Fig. 7.4) • Project (See Fig. 7.5) Advanced Database System

  7. Fig. 7.2 Union, intersection, and difference Advanced Database System

  8. Fig. 7.3 Cartesian product example Advanced Database System

  9. Fig. 7.4 Restriction examples Advanced Database System

  10. Fig. 7.5 Projection examples Advanced Database System

  11. 7.4 The Original Algebra: Semantics (Cont.) • Join • natural join (See Fig. 7.6) • θ-join (See Fig. 7.7) ((S Rename City As Scity) Times (P Rename City As Pcity)) Where Scity > Pcity • Divide (See Fig. 7.8) Advanced Database System

  12. Fig. 7.6 & Fig. 7.7 Advanced Database System

  13. Fig. 7.8 Division examples Advanced Database System

  14. 7.5 Examples • Exam 1: ((Sp Join S) Where P#=P#(‘P2’)) {Sname} • Exam 2: (((P Where Color=Color(‘Red’)) Join Sp ) {S#} Join S) {Sname} • Exam 3: ((S {S#} Divideby P {P#} Per Sp {S#, P#}) Join S) {Sname} • Exam 4: S {S#} Divideby (Sp Where S#=S#(‘S2’)) {P#} Per Sp {S#, P#} • Exam 5: (((S Rename S# As Sa) {Sa, City} Join (S Rename S# As Sb) {Sb, City}) Where Sa < Sb) {Sa, Sb} • Exam 6: ((S {S#} Minus (Sp Where P#=P#(‘P2’)) {S#}) Join S) {Sname} Advanced Database System

  15. 7.6 What Is the Algebra For? • The operators join, intersect, and divide can be defined in terms of the other five. • Of the remaining five, however, none can be defined in terms of the other four, so we can regard those five as constituting a primitive or minimum set. • Some possible applications: 1. Defining a scope for retrieval 2. Defining a scope for update 3. Defining integrity constraints 4. Defining derived relvars 5. Defining stability requirements 6. Defining security constraints Advanced Database System

  16. 7.6 What Is the Algebra For? (Cont.) • A high-level, symbolic representation of the user‘s intent • Transformation rules ((Sp Join S) Where P#=P#(‘P2’)) {Sname} ((Sp Where P#=P#(‘P2’)) Join S) {Sname} • The algebra thus serves as a convenient basis for optimization. • A language is said to be relationally complete if it is at least as powerful as the algebra. Advanced Database System

  17. 7.7 Further Points • Associativity and Commutativity • Associative: Union, Intersect, Times, Join e.g. (A Union B) Union C = A Union (B Union C) = A Union B Union C • Commutative: Union, Intersect, Times, Join e.g. A Union B = B Union A • Some Equivalences e.g. r { } = Table_Dum if r=empty, Table_Dee otherwise (a nullary projection) r Join Table_Dee = Table_Dee Join r = r r Times Table_Dee = Table_Dee Times r = r Advanced Database System

  18. 7.7 Further Points (Cont.) • Some Generalizations If s contains no relations at all, then: • The join of all relations in s is defined to be Table_Dee. • The union of all relations in s is defined to be the empty relation. • The intersection of all relations in s is defined to be the “universal” relation. Advanced Database System

  19. 7.8 Additional Operators • (See Page 196) <semijoin> ::= <relation exp> Semijoin <relation exp> <semiminus> ::= <relation exp> Semiminus <relation exp> <extend> ::= Extend <relation exp> Add ( <extend add commalist> ) <extend add> ::= <exp> As <attribute name> <summarize> ::= Summarize <relation exp> Per <relation exp> Add ( <summarize add commalist> ) <summarize add> ::= <summary type> [ ( <scalar type> ) ] As <attribute name> <summary type> ::= Count | Sum | Avg | Max | Min | All | Any | Countd | Sumd | Avgd| … <tclose> ::= Tclose <relation exp> Advanced Database System

  20. 7.8 Additional Operators (Cont.) • Semijoin  (a Join b) {X, Y} e.g. S Semijoin (Sp Where P#=P#(‘P2’)) • Semidifference  a Minus (a Semijoin b) e.g. S Semiminus (Sp Where P#=P#(‘P2’)) • Extend e.g. Extend P Add (Weight＊454) As Gmwt (See Fig. 7.9) Advanced Database System

  21. 7.8 Additional Operators (Cont.) • Exam 1: Extend S Add ‘Supplier’ As Tag • Exam 2: Extend (P Join Sp) Add (Weight＊Qty) As Shipwt • Exam 3: (Extend S Add City As Scity) {All But City} Rename • Exam 4: Extend P Add (Weight＊454 As Gmwt, Weight＊16 As Ozwt) • Exam 5: Extend S Add Count((Sp Rename S# As X) Where X=S#) As Np (See Fig. 7.10) Advanced Database System

  22. 7.8 Additional Operators (Cont.) • Summarize e.g. Summarize Sp Per P {P#} Add Sum(Qty) As Totqty (See Fig. 7.11) • Exam 1: Summarize Sp Per P {P#} Add (Sum(Qty) As Totqty, Avg(Qty) As Avgqty) • Exam 2: Summarize Sp Per S {S#} Add Count As Np • Summarize is not a primitive operator. Extend • Exam 3: Summarize S Per S {City}Add Avg(Status) As Avg_Status • Exam 4: Summarize Sp Per Sp { }Add Sum(Qty) As Grandtotal • Tclose  the transitive closure of a Advanced Database System

  23. 7.9 Grouping and Ungrouping • Group e.g. SP Group (P#, Qty) As PQ (See Fig. 7.12) • Ungroup e.g. SPQ Ungroup PQ • The reversibility of the Group and Ungroup operations (See Fig. 7.13) • Functionally dependency Advanced Database System

  24. The End. Advanced Database System

More Related