DATABASEchw22

2020-03-01 130浏览

  • 1.DATABASE Principles, Programming, and Performance Patrick O’Neil and Elizabeth O’Neil Lectured by Dr. Lican HuangEmail:licanhuang@zist.edu.cn huang_lican@yahoo.co.ukhttp://virgo.sourceforge.net/licanhuang.htmlZhejiang Sci-Tech University Lican Huang 1
  • 2.2.7 Native Relational Operations Projection. Limit columns and cast out dupli cate rows. (Example, not Def.)  PROJECTION. Given table R where Head (R) = A1, . . . , An, project R on subset of c olumns T := R[Ai1, . . ., Aik], where list in brackets is a subset of the complete set of attributes. Cross out all columns and colu mn values in R not in set, then eliminate d uplicate rows. (See Def 2.7.1 in text.) Zhejiang Sci-Tech University Lican Huang 2
  • 3.2.7 Native Relational Operations   Example 2.7.1. List all customer names fro m the CUSTOMERS table of Figure 2.2. Ans wer toQuery:CUSTOMERS[cname]. Result is table with cname heading, TIpTop, Basics , Allied, ACME. (DRAW IT) (Note cast out du plicate rows.) List city and percent commissions for all ag ents. Any duplicates go away? (New York, 6; but note duplicates in one of two colum ns is OK.) Zhejiang Sci-Tech University Lican Huang 3
  • 4.2.7 Native Relational Operations    SELECTION. R where Condition. The Condition is a logical condition that ca n be determined from the values of a single row of C. Very simple kind of c onditions. Definition 2.7.2. Ai µ Aj or Ai µ a, where Ai and Aj are attributes of R, a is a constant. µ is anyof:<, >, =, <=, >=, <> Zhejiang Sci-Tech University Lican Huang 4
  • 5.2.7 Native Relational Operations If C, C', also get C and C', C or C', andfinally:not C. IfU:=Swhere C1, andV:=Swhere C2, then we have S where C1 and C2 means the same as U∩ V S where C1 or C2 means the same as U ∪ V S where not C1 means the same as S - U Zhejiang Sci-Tech University Lican Huang 5
  • 6.2.7 Native Relational Operations  Example:Give the cid and cname of all customers living in Dallas with dis count greater than 8. (CUSTOMERS where city = 'Dalla s' and discnt > 8) [cid, cname] Zhejiang Sci-Tech University Lican Huang 6
  • 7.2.7 Native Relational Operations example 2.7.2 2.7.3 2.7.4 Zhejiang Sci-Tech University Lican Huang 7
  • 8.2.7 Native Relational Operations Precedence of Relational Operations P 53 Project Select TIMES JOIN, DIVIDBY INTERSECTION UNION, DIFFERENCE Zhejiang Sci-Tech University Lican Huang 8
  • 9.2.7 Native Relational Operations Def. 2.7.4 Join. Join two tables into a new table. Head(R) = A1…AnB1...Bk Head(S) = B1…BkC1…Cm New Table T (JOIN) Head(T) = A1…AnC1…Cm   Zhejiang Sci-Tech University Lican Huang 9
  • 10.2.7 Native Relational Operations      What’s the difference between Join a nd Production? See Page 53. Exampel 2.7.6 Example 2.7.7 Example 2.7.8 Zhejiang Sci-Tech University Lican Huang 10
  • 11.2.7 Native Relational Operations The Division Operation Def. 2.7.5 Page 57 Theorem 2.7.8 see page 57 R=TXS then T=R÷S Zhejiang Sci-Tech University Lican Huang 11
  • 12.2.7 Native Relational Operations Why Division? Example 2.7.9 Example 2.7.10 Example 2.7.11 Zhejiang Sci-Tech University Lican Huang 12
  • 13.2.8 The Interdependence of Operations  Theorem 2.8.1 page 61 A∩B = A-(A-B)  Theorem 2.8.2 page 61 T:=(RXS)where R.B1 = S.B1 and … a nd R.Bk = S.Bk  Theorem 2.8.3 page 62 R÷S = R[A1…An]-((R[A1…An] X S)-R) [A1….An] Zhejiang Sci-Tech University Lican Huang 13
  • 14.2.9 Illustrative Examples Example 2.9.1 –2.9.12 Zhejiang Sci-Tech University Lican Huang 14
  • 15.2.10 Other Relational Operations OUTER JOIN OUTERJ LEFT OUTER JOIN LOUTERJ RIGHT OUTER JOIN ROUTERJ THETA JOIN JN(Ai 8 Bj) Zhejiang Sci-Tech University Lican Huang 15
  • 16.2.10 Other Relational Operations Outer Join Def. 2.10.1 Outer Join page 69 Zhejiang Sci-Tech University Lican Huang 16
  • 17.2.10 Other Relational Operations Theta Join Theta may be >, < >=, <=, =, <>. Def. 2.10.2 Theta Join Zhejiang Sci-Tech University Lican Huang 17
  • 18.homework 2.4—2.15 Zhejiang Sci-Tech University Lican Huang 18