Relational Algebra Database Systems: The Complete Book Ch 2.4 (plus preview of 15.1, 16.1)
The running theme… Replace [thing] with better, but equivalent [thing] .
The running theme… Replace [thing] with better, but equivalent [thing] . How can we tell if How can we tell if [thing] is better? [thing] is equivalent?
First, a few definitions…
Relational Data • Relation (Table): A collection of Tuples of Values • All tuples have the same set of attributes, or schema • What constraints are present on the collection? Uniqueness Order Matters <Redshirt, Ensign> <Kirk, Capt.> <Spock, Lt.> <Spock, Lt.> <Spock, Lt.> <Kirk, Capt.> <Kirk, Capt.> <McCoy, Lt. Cmdr> <Redshirt, Ensign> <Redshirt, Ensign> <Redshirt, Ensign> <McCoy, Lt. Cmdr> <Redshirt, Ensign> <Redshirt, Ensign> <McCoy, Lt. Cmdr> <Redshirt, Ensign> Set Bag List
Declarative Languages Declarative Imperative Say how you want Say what you want to get it “Look at every T report, For each week, “Get me the TPS reports” Sum up the sprocket count Find that week’s S report etc….” C, Scala, Java, SQL, RA, R, … Ruby, Python, …
Declarative languages make it easier to explore equivalent computations to find the best one.
How do you build a query processor?
Project Outline Parser & SQL Query Relational Algebra Translator .sql JSqlParser ??? Optimizer Statistics Trained Monkeys? Query Evaluation Execution Plan Result Engine
.sql JSqlParser .sql CCJSqlParser parser = new CCJSqlParser( ) Statement stmt; while((stmt = parser.Statement() != null) { if(stmt instanceof Select) { … } else if(stmt instanceof CreateTable) { … } }
.sql JSqlParser .sql CCJSqlParser parser = new CCJSqlParser( ) Statement stmt; while((stmt = parser.Statement() != null) { if(stmt instanceof Select) { … } else if(stmt instanceof CreateTable) { … } } Now what?
The Evaluation Pipeline Parsed Query Results Data
The Evaluation Pipeline Parsed Query Results Data Done?
The Evaluation Pipeline Parsed Query Results Data Done? No! Evaluating SQL is HARD.
The Evaluation Pipeline ??? Parsed Query Results Data First, transform the query into something simpler. (simpler, but equivalent)
What’s in the box?
Formal Query Languages • Two mathematical query languages form the basis for user-facing languages (e.g., SQL): • Relational Algebra: Operational, useful for representing how queries are evaluated. • Relational Calculus: Declarative, useful for representing what a user wants rather than how to compute it. 13
Formal Query Languages • Two mathematical query languages form the For basis for user-facing languages (e.g., SQL): Now • Relational Algebra: Operational, useful for representing how queries are evaluated. • Relational Calculus: Declarative, useful for representing what a user wants rather than how to compute it. 13
Preliminaries Queries are applied to Relations Q(Officers, Ships, …) A Query works on fixed relation schemas. … but runs on any relation instance 14
Preliminaries Important : The result of a query is also a relation ! Q 2 (Officers, Q 1 (Ships)) Allows simple, composable query operators 15
Example Instances Captains FirstName, LastName, Rank, Ship [James, Kirk, 4.0, 1701A] [Jean Luc, Picard, 4.0, 1701D] [Benjamin, Sisko, 3.0, DS9 ] Locations [Kathryn, Janeway, 4.0, 74656] Ship, Location [Nerys, Kira, 2.5, 75633] [1701A, Earth ] FirstOfficers [1701D, Risa ] [ 75633 , Bajor ] FirstName, LastName, Rank, Ship [DS9, Bajor ] [Spock, NULL, 2.5, 1701A] [William, Riker, 2.5, 1701D] [Nerys, Kira, 2.5, DS9 ] [Chakotay, NULL, 3.0, 74656] 16
Relational Algebra Operation Sym Meaning Selection Select a subset of the input rows 𝝉 Projection Delete unwanted columns π Cross-product x Combine two relations Set-difference - Tuples in Rel 1, but not Rel 2 Union U Tuples either in Rel 1 or in Rel 2 Also: Intersection, Join , Division, Renaming (Not essential, but can be useful) 17
Relational Algebra Each operation returns a relation! Operations can be composed (Relational Algebra operators are closed ) 18
Relational Algebra Data Relational Algebra Data
Relational Algebra A Set of Tuples Data [Set] Relational Relational Algebra Algebra A Set of Tuples Data
Relational Algebra A Set of Tuples A Bag of Tuples Data [Set] Relational Bag Relational Relational Algebra Algebra Algebra A Set of Tuples A Bag of Tuples Data
Relational Algebra A Set of Tuples A Bag of Tuples A List of Tuples Data Extended [Set] Relational Bag Relational Relational Relational Algebra Algebra Algebra Algebra A Set of Tuples A Bag of Tuples A List of Tuples Data
Relational Algebra A Set of Tuples A Bag of Tuples A List of Tuples Data Extended [Set] Relational Bag Relational Relational Relational Algebra Algebra Algebra Algebra A Set of Tuples A Bag of Tuples A List of Tuples Data Today
Projection ( π ) Delete attributes not in the projection list . π lastname, ship (Captains) FirstName, LastName, Rank, Ship [Spock, NULL, 2.5, 1701A] [William, Riker, 2.5, 1701D] [Nerys, Kira, 2.5, DS9 ] [Chakotay, NULL, 3.0, 74656] 20
Projection ( π ) Delete attributes not in the projection list . π lastname, ship (Captains) FirstName, LastName, Rank, Ship LastName, Ship [Spock, NULL, 2.5, 1701A] [Kirk, 1701A] [William, Riker, 2.5, 1701D] [Picard, 1701D] [Nerys, Kira, 2.5, DS9 ] [Sisko, DS9 ] [Chakotay, NULL, 3.0, 74656] [Janeway, 74656] [Kira, 75633] π rank (FirstOfficers) 20
Projection ( π ) Delete attributes not in the projection list . π lastname, ship (Captains) FirstName, LastName, Rank, Ship LastName, Ship [Spock, NULL, 2.5, 1701A] [Kirk, 1701A] [William, Riker, 2.5, 1701D] [Picard, 1701D] [Nerys, Kira, 2.5, DS9 ] [Sisko, DS9 ] [Chakotay, NULL, 3.0, 74656] [Janeway, 74656] Why is this strange? [Kira, 75633] π rank (FirstOfficers) Rank [2.5 ] [3.0 ] 20
Projection ( π ) Delete attributes not in the projection list . π lastname, ship (Captains) FirstName, LastName, Rank, Ship LastName, Ship [Spock, NULL, 2.5, 1701A] [Kirk, 1701A] [William, Riker, 2.5, 1701D] [Picard, 1701D] [Nerys, Kira, 2.5, DS9 ] [Sisko, DS9 ] [Chakotay, NULL, 3.0, 74656] [Janeway, 74656] Why is this strange? [Kira, 75633] Relational Algebra on Bags: π rank (FirstOfficers) Bag Relational Algebra Rank [2.5 ] [3.0 ] Why? 20
Projection ( π ) Queries are relations What is this (query) relation’s schema? π lastname, ship (Captains) 21
Selection ( ) 𝝉 Selects rows that satisfy the selection condition . 𝝉 rank < 3.5 (Captains) When does selection need FirstName, LastName, Rank, Ship to eliminate duplicates? [Benjamin, Sisko, 3.0, DS9 ] [Nerys, Kira, 2.5, 75633] π lastname ( 𝝉 rank > 3.5 (Captains)) What is the schema of these queries? LastName [Kirk ] [Picard ] [Janeway ] 22
Union, Intersection, Set Difference Each takes two relations that are union-compatible (Both relations have the same number of fields with the same types) Union : Return all tuples in either relation π firstname,lastname (Captains) U π firstname,lastname (FirstOfficers) FirstName, Lastname [James, Kirk ] [Jean Luc, Picard ] [Benjamin, Sisko ] [Kathryn, Janeway ] [Spock, NULL ] [William, Riker ] [Nerys, Kira ] [Chakotay, NULL ] 23
Union, Intersection, Set Difference Each takes two relations that are union-compatible (Both relations have the same number of fields with the same types) Intersection: Return all tuples in both relations π firstname,lastname (Captains) ∩ π firstname,lastname (FirstOfficers) FirstName, Lastname [Nerys, Kira ] 24
Union, Intersection, Set Difference Each takes two relations that are union-compatible (Both relations have the same number of fields with the same types) Set Difference: Return all tuples in the first but not the second relation π firstname,lastname (Captains) - π firstname,lastname (FirstOfficers) FirstName, LastName [James, Kirk ] [Jean Luc, Picard ] [Benjamin, Sisko ] [Kathryn, Janeway ] 25
Union, Intersection, Set Difference Each takes two relations that are union-compatible (Both relations have the same number of fields with the same types) What is the schema of the result of any of these operators? 26
Recommend
More recommend
