1 / 14

Relational Algebra continues…

Relational Algebra continues…. Rename Operation. Allows us to name, and therefore to refer to, the results of relational-algebra expressions. Allows us to refer to a relation by more than one name. Example:  x ( E ) returns the expression E under the name X

erin-hudson
Download Presentation

Relational Algebra continues…

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 continues…

  2. Rename Operation Allows us to name, and therefore to refer to, the results of relational-algebra expressions. Allows us to refer to a relation by more than one name. Example: x (E) returns the expression E under the name X If a relational-algebra expression E has arity n, then returns the result of expression E under the name X, and with the attributes renamed to A1 , A2 , …., An .

  3. Composition of Operations Can build expressions using multiple operations Example: A=C(r x s) r x s A=C(r x s) A B C D E         1 1 1 1 2 2 2 2         10 10 20 10 10 10 20 10 a a b b a a b b A B C D E       10 10 20 a a b 1 2 2

  4. Banking Example branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  5. Example Queries Find all loans of over $1200 amount> 1200 (loan) • Find the loan number for each loan of an amount greater than $1200 loan_number (amount> 1200 (loan)) • Find the names of all customers who have a loan, an account, or both, from the bank customer_name (borrower)  customer_name (depositor)

  6. Example Queries Find the names of all customers who have a loan at the Perryridge branch. customer_name (branch_name=“Perryridge” (borrower.loan_number = loan.loan_number(borrower x loan))) • Find the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank. customer_name (branch_name = “Perryridge” (borrower.loan_number = loan.loan_number(borrower x loan))) – customer_name(depositor)

  7. Additional Operations Additional Operations Set intersection Natural join Aggregation Outer Join Division All above, other than aggregation, can be expressed using basic operations we have seen earlier

  8. Set-Intersection Operation – Example Relation r, s: r s A B A B    1 2 1   2 3 r s A B  2

  9. Natural Join Operation – Example Relations r, s: • r s B D E A B C D 1 3 1 2 3 a a a b b           1 2 4 1 2      a a b a b r s A B C D E      1 1 1 1 2      a a a a b     

  10. Natural-Join Operation Let r and s be relations on schemas R and S respectively. Then, r s is a relation on schema R S obtained as follows: Consider each pair of tuples tr from r and ts from s. If tr and ts have the same value on each of the attributes in RS, add a tuple t to the result, where t has the same value as tr on r t has the same value as ts on s Example: R = (A, B, C, D) S = (E, B, D) Result schema = (A, B, C, D, E) rs is defined as:r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s)) • Notation: r s

  11. Aggregate Functions and Operations Aggregation function takes a collection of values and returns a single value as a result. avg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of values Aggregate operation in relational algebra E is any relational-algebra expression G1, G2 …, Gn is a list of attributes on which to group Each Fiis an aggregate function Each Aiis an attribute name

  12. Aggregate Operation – Example Relation account grouped by branch-name: branch_name account_number balance Perryridge Perryridge Brighton Brighton Redwood A-102 A-201 A-217 A-215 A-222 400 900 750 750 700 branch_nameg sum(balance) (account) branch_name sum(balance) Perryridge Brighton Redwood 1300 1500 700

  13. Aggregate Functions (Cont.) Result of aggregation does not have a name Can use rename operation to give it a name For convenience, we permit renaming as part of aggregate operation branch_nameg sum(balance) as sum_balance (account)

  14. Q1. Consider the following relational database, where the primary keys are underlined. Give an expression in the relational algebra to express each of the following queries: • employee( person_name, street, city) • Works(person_name, company_name, salary) • company( company_name, city) • manages(person_name, manager_name)

More Related