1 / 44

Chapter 6: Formal Relational Query Languages

Chapter 6: Formal Relational Query Languages. Relational Algebra basics. ER for Banking Enterprise. Schema Diagram for the Banking Enterprise. Query Languages. Categories of languages procedural non-procedural “Pure” languages: Relational Algebra Tuple Relational Calculus

orien
Download Presentation

Chapter 6: Formal Relational Query Languages

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. Chapter 6: Formal Relational Query Languages • Relational Algebra basics

  2. ER for Banking Enterprise

  3. Schema Diagram for the Banking Enterprise

  4. Query Languages • Categories of languages • procedural • non-procedural • “Pure” languages: • Relational Algebra • Tuple Relational Calculus • Domain Relational Calculus • Declarative languages: • SQL

  5. Relational Algebra • Procedural language • Six basic operators • Select  • Projection  • Union  • set difference – • Cartesian product x • Rename  • The operators take one or more relations as inputs and give a new relation as a result.

  6. Select Operation – Example A B C D • Relation r         1 5 12 23 7 7 3 10 • A=B ^ D > 5(r) A B C D     1 23 7 10

  7. Select Operation • Notation: p(r) • p is called the selection predicate • Defined as: p(r) = {t | t  rand p(t)} Where p is a formula in propositional calculus consisting of terms connected by :  (and),  (or),  (not)Each term is one of: <attribute>op <attribute> or <constant> where op is one of: =, , >, . <. 

  8. Example of selection • branch-name=“Perryridge”(account) • Selection gives a horizontal subset of a relation • a subset of all the tuples (rows) of a relation account • branch-name=“Perryridge”(account) ? ?

  9. Project Operation – Example • Relation r: A B C     10 20 30 40 1 1 1 2 A C A C • A,C (r)     1 1 1 2    1 1 2 =

  10. Project Operation • Notation:A1, A2, …, Ak (r) where A1, A2 are attribute names and r is a relation name. • The result is defined as the relation of k columns obtained by erasing the columns that are not listed • Duplicate rows removed from result, since relations are sets

  11. Example of Projection • To eliminate the branch-name attribute of accountaccount-number, balance (account) • Projection gives a vertical subset of a relation • a subset of all the columns of a relation account account-number, balance (account) ?

  12. Union Operation – Example A B A B • Relations r, s:    1 2 1   2 3 s r r  s: A B     1 2 1 3

  13. Union Operation • Notation: r s • Defined as: r s = {t | t  r or t  s} • For r s to be valid. • r,s must have the same arity (same number of attributes) • The attribute domains must be compatible (e.g., 2nd column of r deals with the same type of values as does the 2nd column of s)

  14. Example of Union • Find all customers with either an account or a loancustomer-name (depositor)  customer-name (borrower) depositor customer-name (depositor) ?  ? customer-name (borrower) borrower ?

  15. Set Difference Operation – Example A B A B • Relations r, s:    1 2 1   2 3 s r r – s: A B   1 1

  16. Set Difference Operation • Notation r – s • Defined as: r – s = {t | t rand t  s} • Set differences must be taken between compatible relations. • r and s must have the same arity • attribute domains of r and s must be compatible

  17. Example of Set Difference • Find all customers with either an account or a loancustomer-name (depositor)  customer-name (borrower) depositor customer-name (depositor) ? - ? customer-name (borrower) borrower ?

  18. Cartesian-Product Operation-Example A B C D E Relations r, s:   1 2     10 10 20 10 a a b b r s r xs: 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

  19. Cartesian-Product Operation • Notation r x s • Defined as: r x s = {t q | t  r and q  s} • Assume that attributes of r(R) and s(S) are disjoint. (That is, R  S = ). • If attributes of r(R) and s(S) are not disjoint, then renaming must be used.

  20. the borrower relation

  21. the loan relation

  22. Result of borrower |X| loan

  23. Cartesian-Product Operation • Cartesian-Product itself is usually not so useful • It is often used as a “pre-processing” • Other operators such as selection and projection will follow

  24. 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 20 20 a a b 1 2 2

  25. Rename Operation • 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 • x(A1, A2, …, An)(E) returns the result of expression E under the name X, and with the attributes renamed to A1, A2, …., An.

  26. Rename Operation • Example: downtown-account(account-number,branch-name,balance) (branch-name=“Downtown”(account) account ?

  27. Banking Example branch (branch-name, branch-city, assets) customer (customer-name, customer-street, customer-only) account (account-number, branch-name, balance) loan (loan-number, branch-name, amount) depositor (customer-name, account-number) borrower (customer-name, loan-number)

  28. 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))

  29. Example Queries • Find the names of all customers who have a loan, an account, or both, from the bank customer-name (borrower)  customer-name (depositor) • Find the names of all customers who have a loan and an account at bank. customer-name (borrower)  customer-name (depositor)

  30. 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)

  31. Example Queries • Find the names of all customers who have a loan at the Perryridge branch. • Query 1customer-name(branch-name = “Perryridge”( borrower.loan-number = loan.loan-number(borrower x loan)))  Query 2 customer-name(loan.loan-number = borrower.loan-number( (branch-name = “Perryridge”(loan)) x borrower))

  32. Example Queries Find the largest account balance • Rename account relation as d • The query is: balance(account) - account.balance (account.balance < d.balance(account x rd (account)))

  33. branch branch branch-name assets

  34. account account-number branch-name balance

  35. depositor customer-name account-number

  36. customer customer-name customer-street customer-city

  37. borrower customer-name loan-number

  38. loan loan-number branch-name amount

  39. customer

  40. branch

  41. loan

  42. account

  43. borrower

  44. depositor

More Related