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 # S # S 3-8 5-2 e q u i v a l e n c e relation ( ) tuple ( ) cardinality ( ) attribute ( ) degree ( ) primary key ( ) domain ( ) 5-2
5 79 5.2 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 ( ) > + *
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 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 2 2. 6 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 3 3. 6 5) 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)
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_
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 1 00 100 100 Q T Y 1 00 3-8 4-5 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
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 ( )
5 85 DROP OPERTAOR REFLECT; 5. TYPE S# POSSREP (CHAR); S # S# ('S1'); S # P # Q T Y 3 3. 4 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
86 25 5.3 3 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 3 3-8 4 4-5 4-6 3) P 8 3-4) n ( a r i t y 1 2 3 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 } 5. 4 5-1 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
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
88 1. S # C I T Y S # C I T Y S 1 S Q L 5. 3 5. 6 S Q L 2. 5-1 5 97 1 5. 6 C O B O L 4 S Q L ORDER BY 3. 5-1 S N A M E C I T Y S TAT U S S # flopping over
5 89 4. n A i : v i (i= 1, 2,, n) a 5. 10 b 18 c 5. 2 5-3 S _ S P P Q S 5 5-3 E M P E M P # repeating group
90 11. 6 11 3 3. 4 a b ( ) 5.4 3 9 1. 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> 4 4. 6 <x y z> B N F <xyz list> <x y z> <x y z> 3-9
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>
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 { } 6 6. 3 6. 4
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 4 1. 4 S Q L S Q L S Q S Q L S Q L S Q L
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) 4 4. 2 <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
5 95 D O M A I N <default spec> 4. 19 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 ) 4. 1 9 2. 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 4 4. 6 N U L L N U L L C R E ATE TA B L E 4 4-1 A LTER TA B L E
96 ALTER TABLE S ADD COLUMN DISCOUNT INTEGER DEFAULT -1; D I S C O U N T 4 5 5 1 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
5 97 6 11 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 ; 5. 1 3 3-6 a) b) c) E M P # d) S Q L c 5. 2 5. 3 PA RT _ S T R U C T U R E 4 4-6 a) Tutorial D b ) 5. 1 a c) Tutorial D D R O P b 5.4 Tutorial D 4-5 4 5.5 5. 2 q u a n t i t y Q T Y I N T E G E R 4-5 5. 4 4-5
98 5.6 5. 4 5.7 C I R C L E T H E _ a b C I R C L E 5.8 5.9 n n= 0 5.10 5. 11 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 1958-1982 (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
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). 2 0 80 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 1989-1991. Reading, Mass.: Addison-Wesley (1992). [ 5. 6 ]
100 5.4 Hugh Darwen: Relation-Valued Attributes, in C. J. Date and Hugh Darwen, Relational Database Writings 1989 1991. 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 1989 1991. Reading, Mass.: Addison-Wesley (1992). D a r w e n ( N u l l o l o g y ) (study of nothing) 2 3 5. 9 5.6 C. J. Date: Why Duplicate Rows Are Prohibited, in Relational Database Writings 1985 1989. 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 1989 1 9 9 1. 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 1989-1991. 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 www.intelligententerprise.com. C o d d 2 0 70 5.10 Mark A. Roth, Henry F. Korth, and Abraham Silberschatz: Extended Algebra and Calculus for Nested Relational Databases, ACM TODS 13, No. 4 (December 1988).
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 2 6 7 5.1 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
102 5.2 5.3 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 0 7 4-6 7 c) 5-4 PART_STRUCTURE 5.4
5 103 5.5 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
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 5.9 5. 4 0 0 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 ] 5.10 5. 3 5. 11 I N S E RT U P D AT E D E L E T E
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