IntroductiontoDatabase

按一下以編輯母片標題樣式,按一下以編輯母片文字樣式,第二層,第三層,第四層,第五層,4,B-,23,Wei-Pang Yang, Information Management, NDHU,按一下以編輯母片標題樣式,按一下以編輯母片文字樣式,第二層,第三層,第四層,第五層,1-,*,Introduction to Database,CHAPTER 4B,(,補,),DB2 and SQL,Overview,Data Definition,Data Manipulation,The System Catalog,Embedded SQL,Overview,2,Background,Relational Model: proposed by Codd, 1970,Ref: CACM Vol. 13, No.6, A relational model of data for large shared data banks,Relational Databases,Definition: A,Relational Database,is a database that is perceived by its users as a,collection of tables,(and nothing but tables).,Supplier-and-Parts Database,P# PNAME COLOR WEIGHT CITY,P1 Nut Red 12 London,P2 Bolt Green 17 Paris,P3 Screw Blue 17 Rome,P4 Screw Red 14 London,P5 Cam Blue 12 Paris,P6 Cog Red 19 London,P,S# SNAME STATUS CITY,S1 Smith 20 London,S2 Jones 10 Paris,S3 Blake 30 Paris,S4 Clark 20 London,S5 Adams 30 Athens,S,S# P# QTY,S1 P1 300,S1 P2 200,S1 P3 400,S1 P4 200,S1 P5 100,S1 P6 100,S2 P1 300,S2 P2 400,S3 P2 200,S4 P2 200,S4 P4 300,S4 P5 400,SP,(Hierarchical),IMS,Relational Databases,(cont.),S, P, SP: 3 relations (tables),A row in a relation is called a tuple (record),S, P: entities; SP: relationship,primary key,: S# in S, P# in P, (S#, P#) in SP,atomic,: not a set of values, instead of repeating group,S# P#,-,S1 P1, P2, P3, P4, P5, P6 ,S2 P1, P2 ,. .,. .,. .,atomic,Normalization,S# SNAME STATUS CITY,S1 Smith 20 London,S2 Jones 10 Paris,S3 Blake 30 Paris,S4 Clark 20 London,S5 Adams 30 Athens,S,P# PNAME COLOR WEIGHT CITY,P1 Nut Red 12 London,P2 Bolt Green 17 Paris,P3 Screw Blue 17 Rome,P4 Screw Red 14 London,P5 Cam Blue 12 Paris,P6 Cog Red 19 London,P,S# P# QTY,S1 P1 300,S1 P2 200,S1 P3 400,S1 P4 200,S1 P5 100,S1 P6 100,S2 P1 300,S2 P2 400,S3 P2 200,S4 P2 200,S4 P4 300,S4 P5 400,SP,Host,Language,+ DSL,Host,Language,+ DSL,Host,Language,+ DSL,Host,Language,+ DSL,Host,Language,+ DSL,User A1,User A2,User B1,User B2,User B3,External View, # &,External View,B,External/conceptual,mapping A,Conceptual,View,External/conceptual,mapping B,Conceptual/internal,mapping,Stored database (Internal View),Database,management,system,dictionary,(DBMS),e.g. system,catalog,DBA,Storage,structure,definition,(Internal,schema,),Conceptual,schema,External,schema,A,External,schema,B,(Build and,maintain,schemas,and,mappings),#,&,DSL (Data Sub Language),C, Pascal,e.g.,SQL,1,2,3,1,2,3,.,100,Major System Components:,DB2,Source,Module,Modified,Source,Module,DBRM,Object,Module,Load,Module,Application,Plan,PL/I-Compiler,Bind,Pre-compiler,Linkage,Editor,(Load Module),(Application Plan),Runtime Supervisor,Data Manager,Buffer Manager,(Other),DB,PL/I + SQL:,EXEC SQL CREATE TABLE S ( S# CHAR(5), .),EXEC SQL SELECT SNAME INTO :SNAME FROM S,IF SQLCODE 0 THEN .,CALL CREATE( .),CALL SELECT( .),IF SQLCODE 10000 AND,CUTOMER.C#=INVOICE.C,I,nternal Form,:,(,(S SP),Operator,:,SCAN C using region index, create C,SCAN I using amount index, create I,SORT C?and I?on C#,JOIN C?and I?on C#,EXTRACT name field,Calls to Access Method,：,OPEN SCAN on C with region index,GET next tuple,.,.,.,Calls to,：,GET10th to 25th bytes from,block #6 of,Language Processor,Optimizer,Operator Processor,Access Method,File System,database,Language,Processor,Access,Method,e.g.B-tree; Index;,Hashing,DBMS,Embedded SQL,11,Embedded SQL:,Dual-mode,Dual-mode principle: any SQL statement that can be used at terminal (interactive), can also be used in an application program (programmable).,PL/I,(Record operations) vs.,SQL,(Set operations),PL,SQL,call,:,Embedded SQL:,a Fragment,Fragment of a PL/I program with embedded SQL,1,EXEC SQL BEGIN DECLARE SECTION ;,2 DCL SQLSTATE CHAR(5) ;,3 DCL P# CHAR(6) ;,4 DCL WEIGHT FIXED DECIMAL(3) ;,5 EXEC SQL END DECLARE SECTION ;,6 P# = P2 ; /* for example */,7 EXEC SQL SELECT P.WEIGHT,8 INTO :WEIGHT,9 FROM P,10 WHERE P. P# = :P# ;,11 IF SQLSTATE = 00000 ,12 THEN . ; /* WEIGHT = retrieved value */,13 ELSE . ; /* some exception occurred */,Embedded SQL:,a Fragment,(cont.),1. Embedded SQL statements are prefix by EXEC SQL.,2. Executable statements can appear wherever.,(non-executable statements: e.g. DECLARE TABLE, DECLARE CURSOR).,3. SQL statements can reference,host variable,.,(PL/I,變數,),4. Any table used should be declared by DECLARE TABLE,because it is used by pre-compiler.,5.,SQLSTATE/SQLCODE,:,feedback information,of SQL, stored in,SQLCA,(,SQL C,ommunication,A,rea).,SQLSTATE = 0 success, 0 warning, 0,If more than one record are satisfied: SQLCODE 0,How to deal with NULL value? Indicator variable!,EXEC SQL SELECT STATUS INTO :RANK :RANKIND,FROM S,WHERE S#=:GIVENS#,RANKIND: an indicator variable, 15-bit signed binary integer.,If RANKIND = -1 THEN /* Status was NULL */,Operation:,Multiple SELECT,Multiple SELECT:,How to handle the cases that more than one record are satisfied?,Cursor,Cursor,A kind of pointer that can be run through a set of records.,EXEC SQL DECLARE,X,CURSOR FOR /*define cursor S*/,SELECT S#, SNAME,FROM S,WHERE CITY =:Y;,EXEC SQL OPEN,X,; /*activate cursor, execute the query*/,DO for all S records accessible via X;,EXEC SQL FETCH,X,INTO :S#, :SNAME,. /*advance pt., assign values */,END;,EXEC SQL CLOSE,X,; /*deactivate cursor X*/,e.g. Y = London,X,S#,SNAME,define cursor,end,S1 Smith,OPEN:,X,FETCH:,S4 Clark,X,S1 Smith,S4 Clark,S1,PL/I,S#,SNAME,Smith,var,var,Embedded SQL: An Example,Embedded SQL,A comprehensive example,The program accepts four input values : a part number (GIVENP#), a city name (GIVENCIT), a status increment (GIVENINC), and a status level (GIVENLVL). The program scans all suppliers of the part identified by GIVENP#. For each such supplier, if the supplier city is GIVENCIT, then the status is increased by GIVENINC; otherwise, if the status is less than GIVENLVL, the supplier is deleted, together with all shipments for that supplier. In all cases supplier information is listed on the printer, with an indication of how that particular supplier was handled by the program.,Embedded SQL:,An Example,(cont.),SQLEX: PROC OPTIONS (MAIN) ;,DCL GIVENP # CHAR(6) ;,DCL GIVENCIT CHAR(15) ;,DCL GIVENINC FIXED BINARY(15) ;,DCL GIVENLVL FIXED BINARY(15) ;,DCL S# CHAR(5) ;,DCL SNAME CHAR(20) ;,DCL STATUS FIXED BINARY(15) ;,DCL CITY CHAR(15) ;,DCL DISP CHAR(7) ;,DCL MORE_SUPPLIERS BIT(1) ;,EXEC SQL INCLUDE SQLCA ;,/* p.2-41,*/,EXEC SQL DECLARE S TABLE,( S# CHAR(5) NOT NULL,SNAME CHAR(20) NOT NULL,STATUS SMALLINT NOT NULL,CITY CHAR(20) NOT NULL ) ;,EXEC SQL DECLARE SP TABLE,( S# CHAR(5) NOT NULL,P# CHAR(6) NOT NULL,QTY INTEGER NOT NULL ) ;,PL/I,Var.,Embedded SQL:,An Example,(cont.),EXEC SQL DECLARE Z CURSOR FOR,SELECT S#, SNAME, STATUS, CITY,FROM S,WHERE EXISTS,( SELECT *,FROM SP,WHERE SP. S# = S. S#,AND SP. P# = : GIVENP# ),FOR UPDATE OF STATUS ;,EXEC SQL WHENEVER NOT FOUND CONTINUE ;,EXEC SQL WHENEVER SQLERROR CONTINUE ;,EXEC SQL WHENEVER SQLWARNING CONTINUE ;,ON CONDITION ( DBEXCEPTION ),BEGIN ;,PUT SKIP LIST ( SQLCA ) ;,EXEC SQL ROLLBACK ;,GO TO QUIT ;,END ;,Embedded SQL:,An Example,(cont.),GET LIST ( GIVENP#, GIVENCIT, GIVENINC, GIVENLVL ) ;,EXEC SQL OPEN Z ;,IF SQLCODE 0 /*,不正常,*,/,THEN SIGNAL CONDITION ( DBEXCEPTION ) ;,MORE_SUPPLIERS = 1 B ;,DO WHILE ( MORE_SUPPLIERS ) ;,EXEC SQL FETCH Z INTO :S#, :SNAME, :STATUS, :CITY ;,SELECT ; /* case */ /* a PL/I SELECT, not a SQL SELECT */,WHEN ( SQLCODE = 100 ) /* Not found */,MORE_SUPPLIERS = 0 B ;,WHEN ( SQLCODE 100 & SQLCODE 0 ) /* Warning */,SIGNAL CONDITION ( DBEXCEPTION ) ;,主程式,成功,=0,Embedded SQL:,An Example,(cont.),WHEN ( SQLCODE = 0 ) /* success */,DO ;,DISP = bbbbbbb ; /* empty the display buffer */,IF CITY = GIVENCIT,THEN,DO ;,EXEC SQL UPDATE S,SET STATUS = STATUS + : GIVENINC;,WHERE CURRENT OF Z ;,IF SQLCODE 0,THEN SIGNAL CONDITION ( DBEXCEPTION ) ;,DISP = UPDATED ;,END ;,ELSE,IF STATUS GIVENLVL,THEN,DO ;,EXEC SQL DELETE,FROM SP,WHERE S# = : S# ;,Embedded SQL:,An Example,(cont.),IF SQLCODE 0 & SQLCODE 100,THEN SIGNAL CONDITION ( DBEXCEPTION );,EXEC SQL DELETE,FROM S,WHERE CURRENT OF Z ;,IF SQLCODE 0,THEN SIGNAL CONDITION ( DBEXCEPTION);,DISP = DELETED ;,END ;,PUT SKIP LIST ( S#, SNAME, STATUS, CITY, DISP ) ;,END ; /* WHEN ( SQLCODE = 0 ) */,END ; /* PL/I SELECT */,END ; /* DO WHILE */,EXEC SQL CLOSE Z ;,EXEC SQL COMMIT ; /* normal termination */,QUIT: RETURN ;,END ; /* SQLEX */,