ebook 165-5

Size: px

Start display at page:

Download "ebook 165-5"

订柏
5 years ago
Views:

1 [ 3. 3 ] 3. 3 S Q L S Q S Q L S Q L 4 S Q ( )

2 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 S # S # S e q u i v a l e n c e relation ( ) tuple ( ) cardinality ( ) attribute ( ) degree ( ) primary key ( ) domain ( ) 5-2

3 I N T E R G E R C H A R S # P # W E I G H T Q T Y I N T E R G E R S # I N T E G E R = < + * S U B S T R S # = < + * a b * + P. WEIGHT = SP. QTY P. CITY = S.CITY = [ 3. 3 ] A D T ( ) > + *

4 80 = 1. v v v v v n o n s c a l a r c o m p o n e n t 5-1 S # S N A M E S TAT U S C I T Y 1) 2) 3) 4) possible representation 2. Tutorial D 3 Tutorial D P a s c a l TYPE <type name> <possible representation>... TYPE S# POSSREP C H A R TYPE NAME POSSREP C H A R 19

5 5 81 TYPE P# POSSREP C H A R TYPE COLOR POSSREP C H A R TYPE WEIGHT POSSREP R A T I O N A L TYPE QTY POSSREP I N T E G E R 1) 3 S TAT U S C I T Y 2) / conceptual / internal mapping S # N A M E P # C O L O R Q T Y W E I G H T 3. 3 (RATIONAL) (REAL) 3) a b Q T Y Q T Y Q T 4) c a t a l o g e n t r y ) 8 DROP TYPE < type name > <type name> D R O P T Y P E 3. POINT C A RTESIAN P O L A R (a) (b)

6 82 ( s e l e c t o r ) T H E _ POINT T H E _ l i t e r a l( ) C A RT E S I A N P O L A R P O L A R C A RT E S I A N S I N C O S P O L A R THE_

7 5 83 T H E _ R T H E _ T H E TA T H E _ X T H E _ Y S Q RT A R C TA N T H E _ R T H E _ T H E TA P O I N T Q T Y T H E _ 100 Q T Y I N T E G E R Q T Y Q T Y L I N E S E G TYPE LINESEG POSSREP ( BEGIN POINT, END POINT); 4. R AT I O N A L A B S R AT I O N A L Z A B S A B S ( A M T 1 - A M T 2 ) R AT I O N A L DIST ( ) S Q L S Q L B

8 84 L E N G T H R AT I O N A L W I T H P O I N T = R E T U R N R AT I O N A L = = P O I N T Q T Y < R E T U R N I N T E G E R < Q T Y < P O I N T U P D AT E S R E T U R N S C A L L [ 3. 3 ] R E F L E C T x,y x, y T H E _ T H E T H E _ REFLECT T H E _ (read-only) (update) o b s e r v e r m u t a t o r ( )

9 5 85 DROP OPERTAOR REFLECT; 5. TYPE S# POSSREP (CHAR); S # S# ('S1'); S # P # Q T Y W H E R E... WHERE P#='P2' P # C H A R P # C H A R P #... WHERE P#=P#('P2') 3 = < > ( = ) C A S T C A S T _ A S _ R A T I O N A L ( 5 ) 6. a b

10 n Ti i= 1, 2, n r h e a d i n g b o d y a) n A i : Ti A i r Ti i= 1, 2,,n b) m t t A i : v i v Ti t A i i= 1, 2,,n m n 1) 2) A i Ti Ti ) P 8 3-4) n ( a r i t y n n 5) n 4 5 n- 6) r R E L AT I O N { H } R E L AT I O N { H } S # ( N A M E ) ( S TAT U S ) ( C I T Y ) 3 (S#, SNAME, STATUS, CITY ) ( ) (schem s c h e m e ) ( i n t e n t i o n ) ( e x t e n t i o n

11 5 87 { S# : S#, SNAME : NAME, STATUS : STATUS, CITY : CITY } ( S1, Smith, 20, London ) { S# : S#( 'S1' ), SNAME : NAME( 'Smith' ), STATUS : 20, CITY : 'London' } S 1 S #(' S 1 ') S # S # 5-1

12 88 1. S # C I T Y S # C I T Y S 1 S Q L S Q L C O B O L 4 S Q L ORDER BY S N A M E C I T Y S TAT U S S # flopping over

13 n A i : v i (i= 1, 2,, n) a b 18 c S _ S P P Q S E M P E M P # repeating group

14 a b ( ) VAR <relvar name>base <relation type > <candidate key definition list> [<foreign key definition list>]; <relation type> RELATION {<attribute commalist>} <a t t r i b u t e> <attribute name> <type name> <candidate key definition list> <f o reign key definition list> <x y z> B N F <xyz list> <x y z> <x y z> 3-9

15 5 91 1) R E L AT I O N a r r a y ( ) 2) 3) h e a d i n g b o d y a t t r i b u t e t u p l e c a r d i n a l i t y d e g r e e 4) 5) 8 PRIMARY KEY {<attribute name commalist>} 6) 8 DROP VAR <relvar name>; D R O P 9 2. Tutorial D <relvar name>:=<relational expression>; <relational expre s s i o n> <relvar name>

16 92 S R a b S R Tutorial D S 6 S I N S E RT D E L E T E U P D AT E U P D AT E 3. 3 I N S E RT D E L E T E U P D AT E I N S E RT R E L AT I ON{ } T U P L E { }

17 5 93 <attribute update> <attribute name> : =<expression> <boolean expre s s i o n> 6 I N S E RT U P D AT E D E L E T E U P D AT E 1) 1 2) 1 8 S 1 S 4 t 5.5 SQL S Q L I N S E RT U P D AT E D E L E T E S Q L S Q L S Q S Q L S Q L S Q L

18 94 S Q L S Q L S Q 5. 2 P O I N T L I N E S E G S Q L S Q S Q L S # P # C H A R S Q L S Q L S # P # S Q L S Q L 8 8 / / 8 I N T E G E R F L O AT S Q L S Q L C R E ATE DOMAIN 1) <builtin type name> 2) <default spec> D E FA U LT <d e f a u l t> <d e f a u l t> niladic C U R R E N T _ D AT E 3) <c o n s t r a i n t> 8 S Q L A LTER DOMAIN A LT E R S Q L 18

19 5 95 D O M A I N <default spec> DROP DOMAIN DROP DOMAIN <domain name><option>; <o p t i o n> R E S T R I C T C A S C A D E R E S T R I C T D R O P C A S C A D E D R O P ( c a s c a d e ) S Q L S Q L S Q L S S # S N A M E C R E ATE TA B L E TA B L A LTER TA B L E DROP TA B L E TA B L E <base table element> <column definition> <c o n s t r a i n t> <c o n s t r a i n t> 8 <column definition> <column name> <type or domain name> { <default spec> } <type or domain name> <d e f a u l t s p e c> <d e f a u l t s p e c> I N S E RT N U L L N U L L C R E ATE TA B L E A LTER TA B L E

20 96 ALTER TABLE S ADD COLUMN DISCOUNT INTEGER DEFAULT -1; D I S C O U N T DROP TA B L E DROP TABLE <base table name><option>; <o p t i o n> R E S T R I C T C A S C A D E DROP DOMAIN R E S T R I C T C A S C A D E 5.6 a b R E L ATION type g e n e r a t o r a b T H E _ T H E _ R E L AT I O N {H} 1 3

21 Tutorial D Tutorial D S Q L S Q L SQL S Q L a b S Q L S Q L SELECT * FROM S, P ; a) b) c) E M P # d) S Q L c PA RT _ S T R U C T U R E a) Tutorial D b ) 5. 1 a c) Tutorial D D R O P b 5.4 Tutorial D q u a n t i t y Q T Y I N T E G E R

22 C I R C L E T H E _ a b C I R C L E n n= I N S E RT U P D AT E D E L E T E 5.12 Tutorial D D R O P 5.1 E. F. Codd: A Relational Model of Data for Large Shared Data Banks, CACM 13, No. 6 (June 1970). Republished in Milestones of Research-Selected Papers (CACM 25th Anniversary Issue), C A C M26, No. 1 (January 1983). See also the earlier version Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks, IBM Research Report RJ599 (August 19th. 1969). N o t e: That earlier version was Codd's very first publication on the relational model. 30 C o d d t i m e - v a r y i n g - r e l a t i o n relation variables t i m e - v a r y i n g - r e l a t i o n r e l a t i o n s vary with time

23 5 99 DECLARE N INTEGER N time-varying integer t i m e - v a r y i n g - r e l a t i o n 5.2 E. F. Codd: The Relational Model for Database Management Version 2. Reading, Mass.: Addison- Wesley ( 1990) C o d d 1 RM / V1 2 RM / V2RM / V1 RM / V2 RM / V1 RM / V2 RM / V1 RM / V2 A M B N C P D DBMS Q E DBA S F T I V J X L Z [ 5. 7 ~ 5. 8 ] S.S# = SP. P # s h i p m e n t C o d d D C O domain check override S.S# S P. P # D C O D C O THE_S# ( S# ) = THE_P# ( P# ) C H A R 5. 2 D C O 5.3 Hugh Darwen: The Duplicity of Duplicate Rows, in C. J. Date and Hugh Darwen, Relational Database Writings Reading, Mass.: Addison-Wesley (1992). [ 5. 6 ]

24 Hugh Darwen: Relation-Valued Attributes, in C. J. Date and Hugh Darwen, Relational Database Writings Reading, Mass.: Addison-Wesley (1992). 5.5 Hugh Darwen: The Nullologist in Relationland, in C. J. Date and Hugh Darwen, R e l a t i o n a l Database Writings Reading, Mass.: Addison-Wesley (1992). D a r w e n ( N u l l o l o g y ) (study of nothing) C. J. Date: Why Duplicate Rows Are Prohibited, in Relational Database Writings Reading. Mass.: Addison-Wesley (1990). 17 [ 5. 3 ] 5.7 C. J. Date: Notes Toward a Reconstituted Definition of the Relational Model Version 1 (RM/Vl). in C. J. Date and Hugh Darwen, Relational Database Writings Reading, Mass.: Addison-Wesley (1992). C o d d RM / V1 2 1 [ 3. 3 ] 5.8 C. J. Date: A Critical Review of the Relational Model Version 2 (RM/V2), in C. J. Date and Hugh Darwen, Relational Database Writings Reading, Mass.: Addison-We s l e y (1992). C o d d RM / V2[5.2] 5.9 C. J. Date: 30 Years of Relational (series of twelve articles), Intelligent Enterprise 1, Nos. 1 3 and 2, Nos. 1 9 (October 1998 onward). Note: Most installments after the first are published in the onlne portion of the magazine at C o d d Mark A. Roth, Henry F. Korth, and Abraham Silberschatz: Extended Algebra and Calculus for Nested Relational Databases, ACM TODS 13, No. 4 (December 1988).

25 5 101 N F 2 N F 2 = N F N F = non first normal form N F 2 n r N F 2 n r A T n r t A A r e p e a t i n g g r o u p 5. 3 n r N F 2 N F a (CARD) b) T Y P E AT T R I B U T E T Y P E N A M E c) N A M E 5. 2 d) N F 2

26 NAME NONNEG_INTEGER RV K I N D { B a s e Vi e w } a) b) 5-4 R E LVA R C A R D PA RT _ S T R U C T U R E c) 5-4 PART_STRUCTURE 5.4

27 S P J 5.6 a) B O O L E A N 5.7 b) NAME ( THE_NAME (JNAME ) THE_NAME (PNAME ) N A M E c) Q T Y d) Q T Y + Q T Y ( 100 ) e) S TAT U S f) B O O L E A N g) T H E _ C O L O R ( C O L O R ) = P. C I T Y h) C I R C L E

28 104 T H E _ a l e n g t h l e n g t b l e n g t h l e n g t h a r e a A R E A 5.8 DROP TYPE T Y P alter type D a r w e n [ 5. 5 ] TA B L E _ D E E TA B L E _ D U M D E E D U M D E E D U M D E E t r u e y e sd U M f a l s e n o 6 8 [ 5. 5 ] I N S E RT U P D AT E D E L E T E

29 5 105 D E L E T E DELETE RELVAR WHERE RVNAME = NAME E M P 5.12 R E LVA R E M P E M P U P D AT E D E L E T E I N S E RT a b D B A E M P a R E LVA R E M P b AT T R I B U T E E M P I N S E RT D R O P D E L E T E S Q L A LT E R A LTER TA B L E A LT E R U P D AT E DROP VA R DROP TYPE

ebook 165-6

ebook 165-6 6 6.1 C o d d 5.1, 6.1 3 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 6-1 8 o r i g i n a l C o d d 8 8 6. 10 C o d d 8 6-1 8 1) 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