Relational Algebra Instructor Mohamed Eltabakh meltabakhcs wpi edu
Relational Algebra Instructor: Mohamed Eltabakh meltabakh@cs. wpi. edu 1
What we learned so far… l Entity-Relationship Model l l Converting it to relational model Relational Model l Creating table Defining constraints Inserting, deleting, and updating data We learn next how to query the data 2
Query Language l l Define data retrieval operations for relational model Express easy access to large data sets in high-level language, not complex application programs Classification of Query Languages Procedural Declarative You define how to get the data You do not define how to get the data 3
Algebra Behind the Query Language Relational Algebra l Set of operators that operate on relations l Operator semantics based on Set or Bag theory l Relational algebra form underlying basis (and optimization rules) for SQL 4
Relational Algebra l Basic operators l l l Set Operations (Union: ∪, Intersection: ∩ , difference: – ) Select: σ Project: π Cartesian product: x rename: ρ l More advanced operators, e. g. , grouping and joins l The operators take one or two relations as inputs and produce a new relation as an output l One input unary operator, two inputs binary operator 5
Relational Algebra l Similar in concept to arithmetic operators and expressions (w + t) / ((x + y) * 3) w, t, x, y are called variables +, /, * are called binary operators l In relational algebra, instead of variables we have relations πcustomer_name (R ⋈ σbalance <100 (S) ) R, S are DB relations Relational operators 6
Set Operators l Union, Intersection, Difference l Defined only for union compatible relations l Relations are union compatible iff l l l they have same sets of attributes (schema), and the same types (domains) of attributes Example : Union compatible or not? l l Student (s. Number, s. Name) Course (c. Number, c. Name) Not compatible 7
Union over sets: l Consider two relations R and S that are union-compatible R R S S A B A B 1 2 1 2 3 4 3 4 5 6 8
Union over sets: l Notation: R ∪ S l Defined as: l l R ∪S = {t | t∈R or t∈S} For R ∪S to be valid, they have to be union-compatible 9
Difference over sets: – l R – S are the tuples that appear in R and not in S l Defined as: l R – S = {t | t ∈R and t∈ S} R S R–S A B A B 1 2 3 4 5 6 10
Intersection over sets: ∩ l Consider two Relations R and S that are unioncompatible R S R∩ S A B A B 1 2 1 2 3 4 3 4 5 6 11
Intersection: ∩ l Notation: R ∩ S l Defined as: l l R ∩ S = { t | t ∈ r and t ∈ s } Note: R ∩ S = R– (R–S) S R 12
Selection: σ l Select: σc (R): l l c is a condition on R’s attributes Select subset of tuples from R that satisfy selection condition c σ(C ≥ 6) (R) R A B C 1 2 5 3 4 6 1 2 7 13
Selection: σ l Notation: σc(R) l c is called the selection predicate l Defined as: l l σc(R) = {t | t ∈ R and c(t) is true} c is a formula in propositional calculus consisting of terms connected by : l l ∧ (and), ∨ (or), ¬ (not) Each term is one of: l l l <attribute> op <attribute> | <attribute> op <constant> op is one of: =, = , >, ≥. <. ≤ Example of selection: l σ branch_name=“Perryridge” ^ balance>1000 (account) 14
Selection: Example R σ ((A=B) ^ (D>5)) (R) σ (D > C) (R) 15
Project: π l πA 1, A 2, …, An (R), with A 1, A 2, …, An attributes AR l returns all tuples in R, but only columns A 1, A 2, …, An l A 1, A 2, …, An are called Projection List πA, C (R) R A B C 1 2 5 3 4 6 1 2 7 1 2 8 A C 1 5 3 6 1 7 1 8 16
Extended Projection: πL (R) l Standard project l l L contains only column names of R Extended projection l L may contain expressions and assignment operators π C, V A, X C*3+B (R) 17
Extended Projection: πL (R) Example Rename column A to V π C, Compute this expression and call it X V A, X C*3+B (R) R A B C 1 2 5 3 4 6 1 2 7 1 2 8 C V X 5 1 17 6 3 22 7 1 23 8 1 26 18
Extended Projection: πL (R) Example What If I want to select column B, and add another column called ‘Test’ containing word ‘Ok’ π B, ‘Ok’ As Test (R) R A B C B Test 1 2 5 2 Ok 3 4 6 4 Ok 1 2 7 2 Ok 1 2 8 2 Ok 19
Cross Product (Cartesian Product): X RXS R S 20
Cross Product (Cartesian Product): X l Notation R x S l Defined as: l l R x S = {t q | t ∈ r and q ∈ s} Assume that attributes are all unique, otherwise renaming must be used 21
Renaming: ρ l ρS (R) changes relation name from R to S l ρS(A 1, A 2, …, An) (R) renames also attributes of R to A 1, A 2, …, An ρS(B->X, C, D) (R) ρS (R) R S S B C D X C D 2 3 10 2 3 11 6 7 12 22
Composition of Operators l The true power is when you form expressions and put several operators together l We do the same with arithmetic operators (w + t) / ((x + y) * 3) 23
Composition of Operators l l Can build expressions using multiple operations Example: σA=C(R x S) RXS R S σA=C(R x S) 24
Banking Example l branch (branch_name, branch_city, assets) l customer (customer_name, customer_street, customer_city) l account (account_number, branch_name, balance) l loan (loan_number, branch_name, amount) l depositor (customer_name, account_number) l borrower (customer_name, loan_number) 25
Example Queries 26
Example Queries (Cont’d) 27
Example Queries (Cont’d) 28
Example Queries Find customers’ names who have neither accounts nor loans πcustomer_name(customer) (πcustomer_name(borrower) U πcustomer_name(depositer)) 29
- Slides: 29