1 / 17

Relational algebra

Learn about relational algebra, a procedural language used to query databases, and explore examples of basic relational operations. Discover the different types of query languages and their categories.

twhobrey
Download Presentation

Relational algebra

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 • Reading: 7.4, 7.6, 7.7 • Contents: • Basic relational algebra operations • Examples of queries in relational operations

  2. Query Languages • Language in which user requests information from the database. • Categories of languages • procedural • non-procedural • “Pure” languages: • Relational Algebra • Tuple Relational Calculus • Domain Relational Calculus • Pure languages form underlying basis of query languages that people use.

  3. Relational Algebra Relational Algebra is a Procedural language • Six basic operators • select • Project • rename • union • set difference • The join operation • Cartesian product

  4. 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 Select Operation – Example

  5. Select Operation (II) • Notation: p(r) • p is called the selection predicate p has following form: <attribute> op <attribute> or <constant> where op is one of: =, , >, . <.  • Example of selection:deptname=“ITM”( Department )

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

  7. Rename Operation • Allows us to name, and therefore to refer to, the results of relational-algebra expressions. • Example: ITM_dept deptname= “ITM” ( Department ) Family01(name01) pdependent_name(sESSN= ’01’ (Dependent)

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

  9. A B A B    1 2 1   2 3 s r a 2 Intersection Operation – Example • Relations r, s: r s: A B

  10. A B A B    1 2 1   2 3 s r A B  1 Minus Operation – Example • Relations r, s: r - s:

  11. Constraints in union and difference operation • Set union, intersection and minus must be taken between compatible relations. • r and s must have the same arity • attribute domains of r and s must be compatible

  12. 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 Cartesian-Product Operation-Example

  13. A B C D E Relations r, s:   1 2   r c 10 10 20 10 a a b b r s rA = Cs: A B C D E   1 2   10 10 a a Join operation - Example

  14. 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 s A B C D E      1 1 1 1 2      a a a a b      Natural join operation - example • Relations r, s: r r * s

  15. Banking Schema 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)

  16. Examples of queries in relational operations • Find all branchs of branch_city is Lund. • Find the loan number for each loan of an amount greater than 1000000 kr. • Find the names of all customers who have a loan, an account, or both, from the bank. • Find the names of all customers who have an account, but no any loans from the bank.

  17. Examples of queries in relational operations • Find the names of all customers who have both an account and a loan from the bank. • Find the names of all customers who have a loan at a branch in Lund.

More Related