南开大学数据库原理lectureRelational AlgebraPPT学习教案

1、会计学1南开大学数据库原理南开大学数据库原理lectureRelational Algebra第1页/共66页第2页/共66页213:RRR=第3页/共66页R( A B C ) 2 5 7 2 6 9 3 6 7S( A B C ) 2 6 9 2 8 9 3 6 7RS( A B C ) 2 5 7 2 6 9 3 6 7 2 8 9 RS( A B C ) 2 5 7 RS( A B C ) 2 6 9 3 6 7 RS( A B C A B C ) 2 5 7 2 6 9 2 5 7 2 8 9 2 5 7 3 6 9 2 6 9 2 6 9 2 6 9 2 8 9 2 6 9 3 6

2、 7 3 6 7 2 6 9 3 6 7 2 8 9 3 6 7 3 6 7 第4页/共66页)(:21RRC=第5页/共66页SellsbarbeerpriceJoesBud2.50JoesMiller2.75SuesBud2.50SuesMiller3.00barbeerpriceJoesBud2.50JoesMiller2.75)(:SellsJoeMenusJoebar=第6页/共66页Relation rABC=B D 5 (r)ABCD123710第7页/共66页AB11112222CD 1019201010102010EaabbaabbABCDE1221

3、02020aab第8页/共66页)(:21RRL=第9页/共66页SellsbarbeerpriceJoesBud2.50JoesMiller2.75SuesBud2.50SuesMiller3.00beerpriceBud2.50Miller2.75Miller3.00)(,Sells:Pricespricebeer=第10页/共66页213:RRR = C21RR C第11页/共66页Sells(bar,beer,price )Bars(name,addr )JoesBud2.50JoesMaple St.JoesMiller2.75SuesRiver Rd.SuesBud2.50Sues

4、Coors3.00 BarInfo := Sells Sells.bar = B Barsbar,beer,price,name,addr JoesBud2.50JoesMaple St.JoesBud2.50SuesRiver RdJoesMiller2.75JoesMaple St.JoesMiller2.75SuesRiver Rd.SuesBud2.50Joes Maple St.SuesBud2.50SuesRiver Rd.SuesCoors3.00JoesMaple St.SuesCoors3.00SuesRiver Rd.第12页/共66页Sells(bar,b

5、eer,price )Bars(name,addr )JoesBud2.50JoesMaple St.JoesMiller2.75SuesRiver Rd.SuesBud2.50SuesCoors3.00 BarInfo := Sells Sells.bar = B Bars BarInfo(bar,beer,price,name,addr )JoesBud2.50JoesMaple St.JoesMiller2.75JoesMaple St.SuesBud2.50SuesRiver Rd.SuesCoors3.00SuesRiver Rd.第13页/共66页第14页/共66页

6、Sells(bar,beer,price )Bars(bar,addr )JoesBud2.50JoesMaple St.JoesMiller2.75SuesRiver Rd.SuesBud2.50SuesCoors3.00 BarInfo := Sells BarsNote B has become Bars.bar to make the naturaljoin “work.” BarInfo(bar,beer,price,addr )JoesBud2.50Maple St.JoesMilller2.75Maple St.SuesBud2.50River Rd.SuesCo

7、ors3.00River Rd.第15页/共66页第16页/共66页Bars(name, addr )JoesMaple St.SuesRiver Rd. R(bar, addr )JoesMaple St.SuesRiver Rd.R(bar, addr) := Bars第17页/共66页213:RRR = C214:RRR=)(:43RRC=第18页/共66页213:RRR = Ccan be rewritten:)(:213RRRC=第19页/共66页parentheses to force the order you desire.第20页/共66页第21页/共66页第22页/共66页

8、BarsSellsprice3 AND beer=“bud”barR(name)nameaddr=“Maple St”第23页/共66页第24页/共66页SellsSellsS(bar, beer1, price)beer != beer1bar第25页/共66页第26页/共66页第27页/共66页nschema.第28页/共66页bags, but 1,2,1 != 1,1,2 as lists.第29页/共66页第30页/共66页第31页/共66页R(A,B )125612AB1212A+B5 (R) =第32页/共66页R(A,B ) 125612A151A (R) =第33页/共66页

9、R(A,B )S(B,C )1234567812AR.BS.BC123412785634567812341278R S =第34页/共66页R(A,B )S(B,C )1234567812AR.BS.BC12341278567812341278R R.BS.B S =第35页/共66页第36页/共66页第37页/共66页第38页/共66页第39页/共66页第40页/共66页第41页/共66页第42页/共66页R =AB123412AB1234(R) =第43页/共66页第44页/共66页R =AB123452B (R) = (5,2), (1,2), (3,4)第45页/共66页第46页/共6

10、6页R =AB1234A+BA1A2311733A+B,A,A (R) =第47页/共66页第48页/共66页R =AB133432SUM(A) = 7COUNT(A) = 3MAX(B) = 4AVG(B) = 3第49页/共66页第50页/共66页1.The grouping attributes and2. Their groups aggregations. 第51页/共66页R =ABC123456125 A,B,AVG(C) (R) = ?First, group R :ABC123125456Then, average C withingroups:ABAVG(C)12 445

11、6第52页/共66页第53页/共66页R = ABS =BC12234567(1,2) joins with (2,3), but the other two tuplesare dangling.R S =ABC12345NULLNULL67第54页/共66页SELECTPROJ*JOINRENAMEUNIONINTERSECT MINUSTAUDELTAGAMMAOUTERJOIN-第55页/共66页R S 第56页/共66页S(S#,C#) 1 C1 1 C2 1 C3 1 C4 2 C1 2 C2 3 C2 4 C2 4 C3 4 C4 5 C4CA(C#) C2CB(C#) C1 C

12、2CC(C#) C1 C2 C3 C4SCA(S#) 1 2 3 4SCB(S#) 1 2SCC(S#) 1第57页/共66页S(S#, SN, SD, SA) S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20 S6 F CS 22C(C#, CN, PC#) C1 G C2 H C1 C3 I C1 C4 J C2 C5 K C4SC(S#, C#, G) S1 C1 A S1 C2 A S1 C3 A S1 C5 B S2 C1 B S2 C2 C S2 C4 C S3 C2 B S3 C3 C S3 C4 B S4 C3 D S

13、4 C5 A S5 C2 C S5 C3 B第58页/共66页第59页/共66页思考题：查询不学C2课的学生姓名和年龄第60页/共66页第61页/共66页Consider following the relational database Employee(employee-name,street,city) Works(employee-name,company-name,salary) Company(company-name,city) Manages(employee-name,manager-name) . For each of the following queries, give an expression in the relation a