1 / 22

# Schedule

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. . Relational Algebra.

## Schedule

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