1 / 28

CMSC424: Database Design

CMSC424: Database Design. Lecture 5. Review: Relational Algebra. Relational Algebra Operators Select (  ) Project (  ) Set Union (U) Set Difference (-) Cartesian Product (  ) Rename (  ) These are called fundamental operations. 3. Outer Joins ( ).

jaxon
Download Presentation

CMSC424: Database Design

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. CMSC424: Database Design Lecture 5 CMSC424, Spring 2005

  2. Review: Relational Algebra • Relational Algebra Operators • Select () • Project () • Set Union (U) • Set Difference (-) • Cartesian Product () • Rename () • These are called fundamental operations CMSC424, Spring 2005

  3. 3. Outer Joins ( ) 2. Division ( ) • Natural Join ( ) Relational AlgebraRedundant Operators • 4. Update ( ) CMSC424, Spring 2005

  4. Relation1 Relation2 Notation: Natural Join Idea: match tuples on common attributes = r s CMSC424, Spring 2005

  5. Division Relation2 Relation1 Notation: Idea: expresses “for all” queries Query: Find customers who have accounts in all branches in Brooklyn r1 all branches in Brooklyn r2  associate customers with branches they have accounts in Now what ? Use the division operator CMSC424, Spring 2005

  6. loan borrower = Outer Joins Motivation: loan = borrower = = • Join result loses… •  any record of Perry •  any record of Hayes CMSC424, Spring 2005

  7. loan borrower = Outer Joins borrower = loan = 1. Left Outer Join ( ) • preserves all tuples in left relation ┴ = NULL CMSC424, Spring 2005

  8. loan borrower = Outer Joins borrower = loan = 2. Right Outer Join ( ) • preserves all tuples in right relation ┴ = NULL CMSC424, Spring 2005

  9. loan borrower = Outer Joins borrower = loan = 3. Full Outer Join ( ) • preserves all tuples in both relations ┴ = NULL CMSC424, Spring 2005

  10. Update Identifier  Query Notation: Common Uses: 1. Deletion: r  r – s e.g., account  account – σbname=Perry (account) (deletes all Perry accounts) 2. Insertion: r  r  s e.g., branch  branch  {(Waltham, Boston, 7M)} (inserts new branch with bname = Waltham, bcity = Boston, assets = 7M) 3. Update: r  πe1,…,en (r) e.g., account  πbname,acct_no,bal*1.05 (account) (adds 5% interest to account balances) CMSC424, Spring 2005

  11. Extended Relational Algebra • Generalized projection • Aggregates CMSC424, Spring 2005

  12. Generalized Projection  e1,…,en (Relation) Notation: e1,…,en can include arithmetic expressions – not just attributes Example credit = Then… πcname, limit - balance (credit) = CMSC424, Spring 2005

  13. Generalized Projection  e1,…,en (Relation) Notation: e1,…,en can include arithmetic expressions – not just attributes Example credit = Then… πcname, limit - balance as limitbalance (credit) = CMSC424, Spring 2005

  14. 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 CMSC424, Spring 2005

  15. Aggregate Operation – Example • Relation r: A B C         7 7 3 10 sum-C gsum(c) as sumC(r) 27 CMSC424, Spring 2005

  16. Aggregate Functions and Operations • General form: • G1, G2, …, GngF1( A1), F2( A2),…, Fn( An)(E) • E is any relational-algebra expression • G1, G2 …, Gn is a list of attributes on which to group (can be empty) • Each Fiis an aggregate function • Each Aiis an attribute name CMSC424, Spring 2005

  17. 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 balance Perryridge Brighton Redwood 1300 1500 700 CMSC424, Spring 2005

  18. Other Theoretical Languages • Relational Calculus • Non-procedural • Tuple relational calculus • Examples • Safety • Domain relational calculus CMSC424, Spring 2005

  19. Review: Query Languages CMSC424, Spring 2005

  20. SQL - Introduction • Standard DML/DDL for relational DB’s • DML = Data Manipulation Language (queries, updates) • DDL = Data Definition Language (create tables, indexes, …) • Also includes • View definition • Security (Authorization) • Integrity constraints • Transactions • History • Early 70’s, IBM system R project (SEQUEL) • Later, become standard (Structured Query Language) CMSC424, Spring 2005

  21. SQL: Basic Structure • SELECT A1, ….., An • FROM r1, ….., rm • WHERE P Equivalent to: • A1,A2,…,An (σP (r1 …  rn)) CMSC424, Spring 2005

  22. A Simple SELECT-FROM-WHERE Query • Similar to SELECT bname FROM loan WHERE amt > 1000  bname( amt > 1000(loan) ) • But not quite • Why preserve duplicates? • Can instead write : • SELECT DISTINCT bname • FROM loan • WHERE amt > 1000 • (removes duplicates from result) • We will discuss bag algebra a bit later Duplicates are retained (i.e., result not a set) CMSC424, Spring 2005

  23.  cname, balance (depositor account ) Another SELECT-FROM-WHERE Query • Similar to SELECT cname, balance FROM depositor, account WHERE depositor.acct_no = account.acct_no • Returns: • Note: • Can also write • SELECT d.cname, a.balance • FROM depositor as d, • account as a • WHERE d.acct_no = a.acct_no • (neccessary for self-joins) CMSC424, Spring 2005

  24. The SELECT Clause • Equivalent to (generalized) projection, despite name • Can use ‘*’ to get all attributes e.g: SELECT * FROM loan • Can write SELECT DISTINCT to eliminate duplicates • Can write SELECT ALL to preserve duplicates (default) • Can include arithmetic expressions e.g: SELECT bname, acct_no, balance*1.05 FROM account CMSC424, Spring 2005

  25. The FROM Clause • Equivalent to cartesian product () (or , depending on WHERE clause) • Binds tuples in relations to variable names • e.g:FROM borrower, loan • Computesborrowerloan • Identifies borrower, loan columns in result, allowing one to write • WHEREborrower.lno = loan.lno • e.g: FROM borrower as b, loan as l • allows one to write • WHERE b.lno = l.lno CMSC424, Spring 2005

  26. The WHERE Clause • Equivalent to Selection, despite name • WHERE predicate can be: • Simple attribute relop attribute (or constant) (relop: =, <>, <, >, <=, >=) • 2. Complex (usingAND, OR, NOT, BETWEEN) • e.g:SELECT lno • FROM loan • WHERE amt BETWEEN 90000 AND 100000 • is the same as… • SELECT lno • FROM loan • WHERE amt >= 90000 AND amt <= 100000 CMSC424, Spring 2005

  27. Data Definition Language • Allows specification of relation schema as well as: • Attribute domains • Integrity constraints • Security and authorization information • Creation of Indexes • … CMSC424, Spring 2005

  28. DDL CREATE TABLE branch (branch-name char(15) not null, branch-city char(30), assets integer, primary key (branch-name), check (assets >= 0)) DROP TABLE branch ALTER TABLE branch ADD zipcode integer CMSC424, Spring 2005

More Related