1 / 22

Introduction to Relational Algebra and SQL Queries

This document provides an overview of relational algebra, a mathematical notation and set of rules for manipulating relations, as well as its foundational role in SQL. It covers key operators like union, intersection, selection, and joins, illustrating their application through examples. The text also introduces bag semantics, explaining how relations work as multisets in SQL with practical queries involving loan tables, customer information, and more. This resource is essential for anyone looking to deepen their understanding of data manipulation in relational databases.

parry
Télécharger la présentation

Introduction to Relational Algebra and SQL Queries

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. Schedule • Today: • Relational Algebra. • Read Chapter 5 to page 199. • Next • SQL Queries. • Read Sections 6.1-6.2. • And then • Subqueries, Grouping and Aggregation. • Read Sections 6.3-6.4. Holliday - coen 178

  2. Relational Algebra • The relational algebra is a precise mathematical notation and set of rules for manipulating “relations”. • SQL is basically a more human readable form of the relational algebra. Holliday - coen 178

  3. “Core” Relational Algebra A small set of operators that allow us to manipulate relations in limited but useful ways. The operators are: 1. Union, intersection, and difference: the usual set operators. • But the relation schemas must be the same. 2. Selection: Picking certain rows from a relation. 3. Projection: Picking certain columns. 4. Products and joins: Composing relations in useful ways. 5. Renaming of relations and their attributes. Holliday - coen 178

  4. Relational Algebra  SELECT π PROJECT X CARTESIAN PRODUCT NATURAL JOIN Holliday - coen 178

  5. Selection R1 = C(R2) where C is a condition involving the attributes of relation R2. Example Relation Sells: JoeMenu = bar=Joe's(Sells) Holliday - coen 178

  6. Product R = R1R2 pairs each tuple t1 of R1 with each tuple t2 of R2 and puts in R a tuple t1t2. Holliday - coen 178

  7. Natural-Join R = R1R2is equivalent to R = C(R1R2) where c is the condition that the the values of the attributes that R1 and R2 have in common must match. Holliday - coen 178

  8. Example Sells = Bars = BarInfo = Sells Bars Holliday - coen 178

  9. Combining Operations Algebra = • Universe or domain of objects • Operators for constructing expressions. For relational algebra: • Domain = variables standing for relations + finite, constant relations. • Expressions constructed by applying one of the operators + parentheses. • Query = expression of relational algebra. Holliday - coen 178

  10. Bag Semantics A relation (in SQL, at least) is really a bag or multiset. • It may contain the same tuple more than once, although there is no specified order (unlike a list). • Example: {1,2,1,3} is a bag and not a set. • Select, project, and join work for bags as well as sets. • Just work on a tuple-by-tuple basis, and don't eliminate duplicates. Holliday - coen 178

  11. Bag Union Sum the times an element appears in the two bags. • Example: {1,2,1}  {1,2,3,3} = {1,1,1,2,2,3,3}. Bag Intersection Take the minimum of the number of occurrences in each bag. • Example: {1,2,1}  {1,2,3,3} = {1,2}. Bag Difference Proper-subtract the number of occurrences in the two bags. • Example: {1,2,1} – {1,2,3,3} = {1}. Holliday - coen 178

  12. Duplicate Elimination (R) = relation with one copy of each tuple that appears one or more times in R. Example R = A B 1 2 3 4 1 2 (R) = A B 1 2 3 4 Holliday - coen 178

  13. Bank Database Schema • Branch = (branch-name, branch-city, assets) • Customer = (customer-name, customer-street, customer-city) • Account = (branch-name, account#, balance) • Depositor = (customer-name, account#) • Loan = (branch-name, loan#, amount) • Borrower = (customer-name, loan#) Holliday - coen 178

  14. The Customer Table Holliday - coen 178

  15. The Account Table Holliday - coen 178

  16. The Loan Table Holliday - coen 178

  17. The Depositor Table Holliday - coen 178

  18. Queries on the Loan Table • Loan = (branch-name, loan#, amount) • Find the names of all the branches in the Loan relation select branch-name from Loan • (branch-name) (Loan) Holliday - coen 178

  19. More Queries on the Loan Table select * from Loan where amount > 3000 (amount>3000) (Loan) Holliday - coen 178

  20. Find the loan numbers for all loans made at the Oakland branch with loan amounts greater than 1200.  select loan# from Loan where branch-name="Oakland" and amount>1200 (loan#) (branch-name=“Oakland”^amount>1200) (Loan) Holliday - coen 178

  21. Cross Product Select A1, A2 from R1, R2 Result R = R1R2 • pairs each tuple t1 of R1 with each tuple t2 of R2 and puts in R a tuple t1t2. (A1, A2) (R1  R2) Holliday - coen 178

  22. Natural Join • Find the name of customers with an account at the Oakland branch. select customer-name from Depositor, Account where Depositor.account# = Account.account# and branch-name = "Oakland" (cust-name) (b-name=“Oakland”) (Depositor Account) Holliday - coen 178

More Related