1 / 33

CMSC424: Database Design

CMSC424: Database Design. Lecture 4. Database relations. Given attribute domains Branches = { Downtown, Brighton, … } Accounts = { A-101, A-201, A-217, … } Balances = R. Review: Relational Data Model Key Abstraction: Relation. Mathematical relations.

baba
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 4 CMSC424, Spring 2005

  2. Database relations Given attribute domains Branches = { Downtown, Brighton, … } Accounts = { A-101, A-201, A-217, … } Balances = R Review: Relational Data Model Key Abstraction: Relation Mathematical relations • Given sets: R = {1, 2, 3}, S = {3, 4} • R  S = { (1, 3), (1, 4), (2, 3), (2, 4), (3, 3), (3, 4) } • A relation on R, S is any subset () of R  S (e.g: { (1, 4), (3, 4)}) Account Branches  Accounts  Balances { (Downtown, A-101, 500), (Brighton, A-201, 900), (Brighton, A-217, 500) } CMSC424, Spring 2005

  3. Review: Terms and Definitions • Tables = Relations • Columns = Attributes • Rows = Tuples • Relation Schema (or Schema) • A list of attributes and their domains • We will require the domains to be atomic • E.g. account(account-number, branch-name, balance) • Relation Instance • A particular instantiation of a relation with actual values • Will change with time CMSC424, Spring 2005

  4. Bank Database: Schema CMSC424, Spring 2005

  5. Bank Database: An Instance CMSC424, Spring 2005

  6. Act as Integrity Constraints i.e., guard against illegal/invalid instance of given schema Invalid!! Review: Keys and Relations As in the E/R Model: • 1. Superkeys • set of attributes of table for which every row has distinct set of values • 2. Candidate keys • “minimal” superkeys • 3. Primary keys • DBA-chosen candidate keys e.g., Branch = (bname, bcity, assets) Þ CMSC424, Spring 2005

  7. More on Keys • Determining Primary Keys • If relation schema derived from E-R diagrams, we can determine the primary keys using the original entity and relationship sets • Otherwise, same way we do it for E-R diagrams • Find candidate keys (minimal sets of attributes that can uniquely identify a tuple) • Designate one of them to be primary key • Foreign Keys • If a relation schema includes the primary key of another relation schema, that attribute is called the foreign key CMSC424, Spring 2005

  8. Schema Diagram for the Banking Enterprise CMSC424, Spring 2005

  9. Relational Query Languages Recall: Query = “Retrieval Program” • Theoretical QL’s give semantics to Practical QL’s Language Examples: • Theoretical: • Relational Algebra • Relational Calculus • Tuple Relational Calculus (TRC) • Domain Relational Calculus (DRC) • Practical: • SQL (originally: SEQUEL from System R) • Quel (used in Ingres) • Datalog (Prolog-like – used in research lab systems) CMSC424, Spring 2005

  10. Relation Relational Operator Relation Relation Relational Algebra • Basic Operators • select ( σ) • project ( p) • union ( ) • set difference ( – ) • cartesian product (  ) • rename ( ρ) CMSC424, Spring 2005

  11. Select ( σ) Notation: σpredicate(Relation) Relation: Can be name of table, or another query Predicate: • Simple • attribute1 = attribute2 • attribute = constant value (also: ≠, <, >, ≤, ≥) • 2. Complex • predicate AND predicate • predicate OR predicate • NOT predicate CMSC424, Spring 2005

  12. Select ( σ) Notation: σpredicate(Relation) • Examples: σbcity = “Brooklyn”(branch) = σassets > 8M (σbcity = “Brooklyn” (branch)) = CMSC424, Spring 2005

  13. Project ( p ) Notation: pA1, …, An (Relation) • Examples: • Each Ai an attribute • Idea: p selects columns (vs. σ which selects rows) pcstreet, ccity (customer) = CMSC424, Spring 2005

  14. Project ( p ) Notation: pA1, …, An (Relation) • Examples: • Each Ai an attribute • Idea: p selects columns (vs. σ which selects rows) pbcity (σassets > 5M (branch)) = CMSC424, Spring 2005

  15. Union (  ) Notation: Relation1 Relation2 • R  S valid only if: • R, S have same number of columns (arity) • R, S corresponding columns have same domain (compatibility) • Example: (pcname (depositor))  (p cname (borrower)) = CMSC424, Spring 2005

  16. Set Difference ( – ) Notation: Relation1- Relation2 • R - S valid only if: • R, S have same number of columns (arity) • R, S corresponding columns have same domain (compatibility) • Example: (pbname (σamount ≥ 1000 (loan))) – (p bname (σbalance < 800 (account))) = CMSC424, Spring 2005

  17. Set Difference ( – ) Notation: Relation1- Relation2 • R - S valid only if: • R, S have same number of columns (arity) • R, S corresponding columns have same domain (compatibility) • Example: (pbname (σamount ≥ 1000 (loan))) – (p bname (σbalance < 800 (account))) = – = CMSC424, Spring 2005

  18. Cartesian Product (  ) Notation: Relation1Relation2 • R  S like cross product for mathematical relations: • every tuple of R appended to every tuple of S • Example: depositor borrower = How many tuples in the result? A: 56 CMSC424, Spring 2005

  19. Rename ( ρ) Notation: ridentifier (Relation) • renames a relation, or Notation: ridentifier0 (identifier1, …, identifiern) (Relation) • renames relation and columns of n-column relation • Use: • massage relations to make , – valid, or  more readable CMSC424, Spring 2005

  20. Rename ( ρ) Notation: ridentifier0 (identifier1, …, identifiern) (Relation) • renames relation and columns of n-column relation • Example: rres (dcname, acctno, bcname, lno) (depositor borrower) = CMSC424, Spring 2005

  21. Rename ( ρ) Notation: ridentifier0 (identifier1, …, identifiern) (Relation) • renames relation and columns of n-column relation • Example: rres (dcname, acctno, bcname, lno) (depositor borrower) = res = CMSC424, Spring 2005

  22. Example Query in RA • Determine lno’s for loans that are for an amount that is larger than the amt of some other loan. (i.e. lno’s for all non-minimal loans) Can do in steps: Temp1 … Temp2  … Temp1 … … CMSC424, Spring 2005

  23. Bank Database: An Instance CMSC424, Spring 2005

  24. Example Query in RA 1. Find the base data we need Temp1 p lno,amt (loan) 2. Make a copy of (1) Temp2 ρTemp2 (lno2,amt2)(Temp1) CMSC424, Spring 2005

  25. Example Query in RA 3. Take the cartesian product of 1 and 2 Temp3 Temp1 Temp2 CMSC424, Spring 2005

  26. Example Query in RA • plno ( • σamt > amt2 (p lno,amt (loan)  (ρTemp2(lno2,amt2)(p lno,amt (loan))))) 4. Select non-minimal loans Temp4 σamt > amt2 (Temp3) 5. Project on lno Result  plno (Temp4) … or, if you prefer… CMSC424, Spring 2005

  27. What we learned so far… • Relational Algebra Operators • Select • Project • Set Union • Set Difference • Cartesian Product • Rename • These are called fundamental operations CMSC424, Spring 2005

  28. Formal Definition • Basic expression • A relation in the database • A constant relation e.g. {(A-101, Downtown, 500), (A-215, Mianus, 700)…} • Let E1 and E2 be two relational-algebra expressions, then the following are also: • σP(E1), where P is a predicate on attributes in E1 • pS(E1), where S is a list containing some attributes in E1 • E1 E2, • E1 – E2 • E1 E2 • ρx(E1), where x is the new name for the result of E1 CMSC424, Spring 2005

  29. 3. Outer Joins ( ) 2. Division ( ) • Redundant: Above can be expressed in terms of minimal RA •  e.g. depositor borrower = • π …(σ…(depositor  ρ…(borrower))) • Added as convenience • Natural Join ( ) Relational AlgebraRedundant Operators • 4. Update ( ) (we’ve already been using) CMSC424, Spring 2005

  30. Relation1 Relation2 Notation: depositor borrower ≡ πcname,acct_no,lno (σcname=cname2 (depositor  ρt(cname2,lno) (borrower))) Natural Join Idea: combines ρ, , σ = r s CMSC424, Spring 2005

  31. Division Relation2 Relation1 Notation: Idea: expresses “for all” queries = s Query: Find values for A in r which have corresponding B values for all B values in s r CMSC424, Spring 2005

  32. Division  Another way to look at it: and 17  3 = 5 The largest value of i such that: i 3 ≤ 17 Relational Division = s t The largest value of t such that: ( t  s r ) r CMSC424, Spring 2005

  33. Division A More Complex Example ? = t s r CMSC424, Spring 2005

More Related