ebook 165-6

Size: px

Start display at page:

Download "ebook 165-6"

梗诸
4 years ago
Views:

1 6 6.1 C o d d 5.1, r e s t r i c t p r o j e c t j o i n 6. 1 C o d d o r i g i n a l C o d d C o d d ) u n i o n i n t e r s e c t i o n d i ff e r e n c e Cartesian product 2) r e s t r i c t s e l e c t p r o j e c t j o i n d i v i d e C o d d

2 C o d d E X T E N D S U M M E R I Z E ( ) X R R X R X R SQL

3 108 3 S JOIN P C I T Y S JOIN P C I T Y (S JOIN P) WHERE CITY='Athens' R E N A M E R E N A M E S RENAME CITY AS SCITY S C I T Y S C I T Y S N A M E S JOIN SP P RENAME PNAME AS PN, WEIGHT AS WT ( S Q L S Q L ) Hall et al.[6.10] D a r w e n [ 6. 2 ]

4 6 109 R E N A M E S Q L S. S # 6.3 J O I N W H E R E <relational expre s s i o n> <tuple expre s s i o n> <p ro j e c t> <n o n p ro j e c t> p r o j e c t i o n <relational expre s s i o n> <n o n p ro j e c t> <relational expre s s i o n> <n o n p ro j e c t> <re n a m i n g> <relational expre s s i o n> <n o n p ro j e c t> <u n i o n> <relational expre s s i o n> <n o n p ro j e c t>

5 110 <i n t e r s e c t> <relational expre s s i o n> <n o n p ro j e c t> <relational expre s s i o n> <n o n p ro j e c t> <t i m e s> <relational expre s s i o n> <n o n p ro j e c t> <boolean expre s s i o n> <relational expre s s i o n> <relational expre s s i o S WHERE CITY = 'London' <re s t r i c t> <relational expre s s i o n> <n o n p ro j e c t> <j o i n> <relational expre s s i o n> <n o n p ro j e c t> <relational expre s s i o n> <n o n p ro j e c t> ( u n i o n c o m p a t i b i l i t y )

6 6 111 A B A UNION B A B A B 6-2 A B P 1 A UNION B a P A B A INTERSECT B A B A B 6-2 A I N T E R S E C T B b P 1 3. A B A MINUS B A B A B 6-2 A MINUS B c P 1 B MINUS A d P 1 M I N U S

7 112 A 1 : a 1 A 2 : a 2 A m : a m B 1 : b 1 B 2 : b 2 B n : b n A 1 : a 1 A 2 : a 2 A m : a m B 1 : b 1 B 2 : b 2 B n : b n w e l l - f o r m e d R E N A M E A B A TIMES B A B A B A B A B A A B 6-3 A B A TIMES B A X Y = > X Y X Y t r u e f a l s e A A X Y 1) X Y

8 6 113 S WHERE CITY = 'London' (EXTEND S ADD 'London' AS TEMP ) WHERE CITY = TEMP T E M P 6. 7 E X T E N D 2) b b 3) W H E R E W H E R E W H E R E <boolean expre s s i o n> <boolean expre s s i o n> <h o r i z o n t a l> ( h o r i z o n t a l ) A Y Z A X Y Z A X, Y,, Z } A Y X: x Y: y : z A X Y x, y, z v e r t i c a l 1) 2) A i d e n t i t y

9 114 3) A n u l l a r y 6. 8 ~ C I T Y S P W E I G H T P P # P N A M E C O L O R C I T Y P { ALL BUT WEIGHT } 7. j o i n 3 A B X1, X2,..., Xm, Y1, Y2,..., Yn {Y1, Y2,... Yn, Z1, Z2,..., Zp} Y Y 1, Y 2,..., Y n X X 1, X 2,..., X m A Z Z 1, Z 2,..., Z p B X 1, X 2,..., X m Y 1, Y 2,...,Y n Z 1, Z 2,..., Z p X Y Z A B A JOIN B X Y Z X : x,y : y, Z : z X x Y y A Y y Z z B 6-6 C I T Y S JOIN P 6-6

10 6 115 ( ) A B ( ) A X Y X Y A B X Y (A TIMES B) WHERE X ΘY 6-6 S JOIN P A B t t X Y S P C I T Y g r e a t e r- t h a n > > C I T Y C I T Y 6-7 ( e q u i j o i n ) C I T Y S JOIN P Tutorial D b p r i m i t i v e CITY 6. 3 small divide g r e a t

11 116 d i v i d e Tutorial D <p e r> <relational expre s s i o n> <d i v i d e> <relational expre s s i o n> < d i v i d e > 6. 3 C o d d A B X 1, X 2,...., X m {Y 1, Y 2,..., Y n} C X 1, X 2,...., X m Y 1, Y 2,..., Y n C A B X 1,X 2,...,X m Y 1,Y 2,...,Y n X Y A C B A B C m e d i a t o r A DIVIDEBY B PER C X X : x C X : x, Y: y B Y: y A X C Y B Y 6-8 D E N D S S # M E D S P S # P # D O R D I V I D E B Y a l l C o d d (universal quantifier)( 7 ) ( e x i s t e n t i a l )

12 A B C (A UNION B) UNION C A UNION (B UNION C) U N I O N A UNION B UNION C I N T E R S E C T T I M E S J O I N M I N U S U N I O N I N T E R S E C T T I M E S J O I N M I N U S A UNION B B UNION A I N T E R S E C T T I M E S J O I N 17 T I M E S A B A JOIN A TIMES B Tutorial D T I M E S P 2 SP JOIN S WHERE P# = P# ('P2') S N A M E S P S S P S N A M E S TAT U S C I T Y P 2 S N A M E S N A M 2. P WHERE COLOR = COLOR ' R E D ' S N A M E JOIN SP {S#} JOIN S { S N A M E } 17 3.

13 118 S N A M E 4. S 2 S # 5. S A S B S # S # < S A < S B x,x x, y y,x W I T H W I T H W I T H W I T H 6. P 2 S N A M E 6 T 6 W I T H Ti lazy evaluation P RT V A S

14 T 6 T 5 S N A M E T 5 T 5 T T 4 S T R E N A M E R E N A M E C o d d [ 6. 1 ] C o d d 8 p r i m i t i v e SP JOIN S WHERE P# = P# 'P2' S N A M E P 2 SP WHERE P# = P# ' P 2 'JOIN SSNAME R E N A M E ( [6.1] )

15 C o d d 8 S E M I J O I N S E M I M I N U S E X T E N D S U M M A R I Z E T C L O S E Tutorial D <n o n p ro j e c t> 5 B N F <relational expre s s i o n> <n o n p ro j e c t> 1. A B X Y Z 6. 4 A B A B ( s e m i j o i n ) A SEMIJOIN B (A JOIN B) { X, Y} A B A B A P 2 S # S N A M E S TAT U S C I T Y S SEMIJOIN (SP WHERE P# = P# ( 'P2' ) ) 2. A B X Y Z 6. 4 A B ( s e m i d i ff e r e n c e ) A B SEMIDIFFERENCE B A MINUS (A SEMIJOIN B)

16 6 121 B A P 2 S # S N A M E S TAT U S C I T Y S SEMIMINUS (SP WHERE P# = P# ('P2')) 3. W E I G H T * 454 W H E R E W E I G H T * 454 ( e x t e n d ) E X T E N D EXTEND P ADD (WEIGHT 454) AS GMWT G M W T P G M W T P EXTEND E X T E N D S JOIN SP E X T E N D S Q L A LT E R TA B L E G M W T W H E R E a Z A b t t A Z A A Z e x p Z A 1 Z e x p *

17 122 1 ) EXTEND S ADD S u p p l i e r AS TAG S u p p l i e r S 2 ) EXTEND P JOIN SP ADD WEIGHT * Q T Y AS SHIPWT 3 ) EXTEND S ADD CITY AS SCITY {ALL BUT CITY} C I T Y S RENAME CITY AS SCITY RENAME E X T E N D R E N A M E 4 ) EXTEND P ADD WEIGHT * 454 AS GMWT WEIGHT * 16 AS OZWT 5 ) EXTEND S ADD COUNT SP RENAME S# AS WHERE X AS NP X = S# SP RENAME S# AS WHERE X X = S# S P (aggregate operator)count N P S # S 5 N P S 5 S P C O U N T C O U N T S U M AV G M A X M I N A L L A N Y Tutorial D <a g g regate operator invocation> <scalar expre s s i o n> C O U N T <attribute name> <re l a t i o n a l e x p re s s i o n> <attribute name> <relational expre s s i o n>

18 6 123 S 1 S C O U N T S U M M A X M I N A L L A N Y t u r e f a l s e AVG 4. S U M M A R I Z E e x t e n d h o r i z o n t a l r o w - w i s e s u m m a r i z e v e r t i c a l c o l u m n - w i s e SUMMARIZE SP PER SP { P# } ADD SUM { QTY } AS TOTQTY 6-11 SUMMARIZE { P# TOTQTY } SP {P#} P # P # 6-11 S P P { P #} P # SUMMARIZE A PER B ADD SUMMARY AS Z B A B A B A 1 A 2 A n A 1 A 2 A n Z t t B Z Z A A 1 A 2 A n t A t A 1 A 2 A n B Z B 1 Z

19 124 SUMMARIZE P JOIN SP PER P {CITY } ADD COUNT AS NSP L o n d o n P a r i s R o m e 1) S U M M A R I Z E 2) <s u m m a r i z e> <summarize add> <summariy type> C O U N T S U M AV G M A X M I N A L L A N Y C O U N T D S U M D AV G D C O U N T D S U M D AV G Dd i s t i n c t s u m m a r y <scalar expre s s i o n> S U M M A R I Z E <relational expre s s i o n> <s c a l a r e x p re s s i o n> <s u m m a ry type> C O U N T <summarize add> <a g g regate operator invocation> <a g g regate operator invocation> <summarize add> S U M M A R I Z E S U M M A R I Z E 3) S U M M A R I Z E E X T E N D 4) SUMMARIZE SP PER SP {} ADD SUM (QTY) AS GRANDTOTAL s p S P s p s

20 [ 5. 5 ] s p S U M M A R I Z E G R A N D TO TA L s p Q T Y s p s p S U M M A R I Z E P E R P E R PER 5. Tc l o s e Tc l o s e A X Y X Y T A TCLOSE A A + A + A A {X x Y y} A z 1,z 2,...,z n T {X x Y z 1} {X z 1 Y z 2}... {X z n Y y} A {X x Y y} A + A x y A + A ( g r o u p ) ( u n g r o u p ) g r o u p SP GROUP P # Q T Y AS PQ 6-12 SP GROUP P # QTY AS PQ group SP by S# S # G R O U P P Q P Q P # Q T Y S P S P S # S P S # S # s P Q p q P Q P E R R E L AT I O N { T U P L E {}} ( 0 ) TA B L E _ D E E ( [ 3. 3 ] [ 5. 5 ] [ 6. 2 ])

21 S# SP S P x P # Q T Y w r a p p e d t u p l e - v a l u e y S # s x y p q S # s P Q p q 6-12 S P Q 6-12 SPQ UNGROUP PQ S P P # Q T Y P Q S P Q S # S P Q P Q S # s P # Q T Y p q S P Q S P Q P Q x x SPQ S # s S P Q P Q y S # s x y P # Q T Y p q S # s P # p Q T Y q S P G R O U P U N G R O U P n e s t ( u n n e s t ) g r o u p / u n g r o u p n e s t / u n n e s t N F 2 G R O U P U N G R O U P

22 6 127 r r [ 5. 4 ] T W O 6-13 T H R E E T H R E A RV X O N E T W O 6-13 ONE RV X functionally dependent A O N E T H R E T H R E E O N r RV X r r RV X RV X r RV X <relational expression> Θ <relational expression> <relational expre s s i o n> e q u a l s n o t e q u a l s s u b s e t o f p r o p e r s u b s e t o f s u p e r s e t o f p r o p e r s u p e r s e t o f A B

23 128 1) S{ CITY } = P{ CITY } C I T Y C I T Y 2) S{ S# } SP{ S# } D I V I D E B Y 6. 4 A B T2 WHERE A=B IS_EMPTY (<relational expression>) <relational expre s s i o n> t r u e f a l s t r RELATION { t } r S Q L t IN r I N 6.10 c l o s u r e n e s t e d relational expression

24 6 129 relation type inference rule 8 R E N A M E S E M I J O I N S E M I M I N U S E X T E N D S U M M A R I Z E T C L O S E G R O U P U N G R O U P 17 ( s t e p - a t - a - t i m e ) W I T H C o d d A B A JOIN B A TIMES B A B T I M E S D I V I D E B Y 6.6 S JOIN SP JOIN P 6.7 A n A n n * 1 = 1 * n = n 1 i d e n t i t y n n * 0 = 0 * n = d y a d i c n n> 1 A UNION B U N I O N C A B C n= 1 n=

25 J ~ / 6.18 / / 6.19 / / 6.20 / / S S 1 P S S S S 2 S s t a t u s S P 1 J P 1 P S

26 S / 6.48 Sx Sy Sx Sy 6.49 / S P J E. F. Codd: Relational Completeness of Data Base Sublanguages, in Randall J. Rustin (ed.), Data Base Systems, Courant Computer Science Symposia Series 6. Englewood Cliffs, N.J.: PrenticeHall (1972). C o d d o r i g i n a l [ 5. 1 ] C o d d R E N A M E S Q L S Q L [ 3. 3 ] A [ 3. 3 ] r e m o v e n o r C o d d 6.2 Hugh Darwen (writing as Andrew Warden): Adventures in Relationland, in C. J. Date, Relational Database Writings Reading, Mass.: Addison-Wesley (1990). D B M S 1) The Naming of Columns 2) In Praise of Marriage 3) The Kdysof the Kingdom 4) Chivalry 5) A Constant Friend 6) Table_Dee and Ta b l e _ D u m 7) Into the Unknown 6.3 Hugh Darwen and C. J. Date: Into the Great Divide, in C. J. Date and Hugh Darwen, Relational Database Writings Reading, Mass.: Addison-Wesley (1992). a C o d d [ 6. 1 ] b H a l l H i t c h c o c k To d d [ ] C o d d C o d d

27 132 small divide great divide Tutorial D 6. 5 C o d d A B {X Y} {Y} X Y A DIVIDEBY B {X} {X x} B {Y y} A {X x Y y} X A Y B Y X 6.4 C. J. Date: Quota Queries (In three parts), in C. J. Date, Hugh Darwen, and David McGoveran, Relational Database Writings Reading, Mass.: Addison-Wesley (1998). Tutorial D W X W Y # _ H E AV I E R P 1 P 2 P Michacl J. Carey and Donald Kossmann: On Saying Enough Already! in SQL, Proc Int. Conf. on Management of Data, Tucson, Ariz. (May 1997). [ 6. 4 ] S TOP AFTER ORDER BY S TO P A F T E R S TO P AFTER 3 S TOP AFTER 2 S TOP AFTER I B M I B S Q L 6.6 R. C. Goldstein and A. J. Strnad: The MacAIMS Data Management System, Proc ACM SICFIDET Workshop on Data Description and Access (November 1970). [ 6. 7 ] 6.7 A. J. Strnad: The Relational Approach to the Management of Data Bases, Proc. IFIP Congress, Ljubljana, Yugoslavia (August 1971).

28 6 133 M a c A I M S n C o d d C o d d M a c A I M S 6.8 M. G. Notley: The Peterlee IS/1 System, IBM UK Scientific Centre Report UKSC-0018(March 1972). [ 6. 9 ] 6.9 S. J. P. Todd: The Peterlee Relational Test Ve h i c l e A System Overview, IBM Sys. J. 1 5, No.4 (1976).- P RT V The Peterlee Relational Test Ve h i c l e I B M p e t e r l e e U K I S / 1 I S / 1 C o d d n I S B L Information System Base Language [ ] I S B L [ ] P RT V R E N A M E E X T E N D S U M M A R I Z E 17 (lazy evaluation feature) W I T H 6.10 P. A. V. Hall, P. Hitchcock, and S. J. P. Todd: An Algebra of Relations for Machine Computation, Conf. Record of the 2nd ACM Symposium on Principles of Programming Languages, Palo Alto, Calif. (January 1975) Patrick A. V. Hall: Relational Algebras, Logic, and Functional Programming, Proc ACM SIGMOD Int. Conf. on Management of Data, Boston, Mass. (June 1984). a 4 G L s 2 b 6.12 Anthony Klug: Equivalence of Relational Algebra and Relational Calculus Query Languages Having Aggregate Functions, JACM 29, No.3 (July 1982) ~ JOIN 6. 4 I N T E R S E C T

29 134 A INTERSECT B A B A B D I V I D E B Y X A C Y B C D I V I D E B Y A X B Y C X Y S DIVIDEBY P PER SP 6.3 A INTERSECT B 6. 2 T I M E S JOIN J O I N T I M E S 6.4 A B A B A B B A B 6.5 C o d d D I V I D E B Y C o d d D I V I D E B Y D I V I D E B Y A B B A A

30 6 135 C o d d D I V I D E B Y 6.6 S # P # C I T Y n 2 n i d e n t i t y n n u l l a r y 0 A A TA B L E _ D U M TA B L E _ D E E [ 5. 5 ] 6.8 TA B L E _ D E E TA B L E _ D E E D E E 1 D E E T I M E S J O I N 6.9 T I M E S TA B L E _ D U M R 6.10 D E E D U M O R A N D AND NOT D E E D E D U M D U M DUM D U M D E E D E E D U M 0 e x t e n d s u m m a r i z e D E E D U M 1 D E E D U M 1 D I V I D E B Y S E M I J O I N S E M I M I N U S T C L O S E G R O U P U N G R O U P R

31 RT RT RT RT u n i v e r s a l [ ] TA B L E _ D E E W I T H W I T H

32 P E R J

34 S P Q 6. 49

ebook 165-5

ebook 165-5 3 5 6 7 8 9 [ 3. 3 ] 3. 3 S Q L S Q 4. 21 S Q L S Q L 4 S Q 5 5.1 3 ( ) 78 5-1 3-8 - r e l a t i o n t u p l e c a r d i n a l i t y a t t r i b u t e d e g r e e d o m a i n primary key 5-1 3 5-1 S #