IntroductiontoDatabase

1、4B-1Wei-Pang Yang, Information Management, NDHUIntroduction to DatabaseCHAPTER 4B (補補)DB2 and SQLqOverviewq Data Definitionq Data Manipulationq The System Catalogq Embedded SQL1-2Overview4B-3Wei-Pang Yang, Information Management, NDHUBackgroundRelational Model: proposed by Codd, 1970 Ref: CACM Vol.

2、13, No.6, A relational model of data for large shared data banks4B-4Wei-Pang Yang, Information Management, NDHURelational DatabasesDefinition: A Relational Database is a database that is perceived by its users as a collection of tables (and nothing but tables). Supplier-and-Parts DatabaseP# PNAME CO

3、LOR WEIGHT CITYP1 Nut Red 12 LondonP2 Bolt Green 17 ParisP3 Screw Blue 17 RomeP4 Screw Red 14 LondonP5 Cam Blue 12 ParisP6 Cog Red 19 London PS# SNAME STATUS CITYS1 Smith 20 LondonS2 Jones 10 ParisS3 Blake 30 ParisS4 Clark 20 LondonS5 Adams 30 Athens S S# P# QTY S1 P1 300 S1 P2 200 S1 P3 400 S1 P4 2

4、00S1 P5 100S1 P6 100S2 P1 300S2 P2 400S3 P2 200S4 P2 200S4 P4 300S4 P5 400SP(Hierarchical)IMS4B-5Wei-Pang Yang, Information Management, NDHURelational Databases (cont.)S, P, SP: 3 relations (tables)A row in a relation is called a tuple (record)S, P: entities; SP: relationshipprimary key: S# in S, P#

5、 in P, (S#, P#) in SPatomic: not a set of values, instead of repeating group S# P# - S1 P1, P2, P3, P4, P5, P6 S2 P1, P2 . . . . . .atomicNormalizationS# SNAME STATUS CITYS1 Smith 20 LondonS2 Jones 10 ParisS3 Blake 30 ParisS4 Clark 20 LondonS5 Adams 30 Athens SP# PNAME COLOR WEIGHT CITYP1 Nut Red 12

6、 LondonP2 Bolt Green 17 ParisP3 Screw Blue 17 RomeP4 Screw Red 14 LondonP5 Cam Blue 12 ParisP6 Cog Red 19 London P S# P# QTY S1 P1 300 S1 P2 200 S1 P3 400 S1 P4 200S1 P5 100S1 P6 100S2 P1 300S2 P2 400S3 P2 200S4 P2 200S4 P4 300S4 P5 400SP4B-6Wei-Pang Yang, Information Management, NDHUHost Language+

7、DSLHost Language+ DSLHost Language+ DSLHost Language+ DSLHost Language+ DSLUser A1User A2User B1User B2User B3External View # &External ViewBExternal/conceptualmapping AConceptualViewExternal/conceptualmapping BConceptual/internalmappingStored database (Internal View)Databasemanagementsystem diction

8、ary(DBMS) e.g. system catalogDBAStoragestructuredefinition(Internalschema)ConceptualschemaExternalschemaAExternalschemaB(Build andmaintainschemasandmappings)#& DSL (Data Sub Language)C, Pascale.g. SQLSQL123123.1004B-7Wei-Pang Yang, Information Management, NDHUMajor System Components: DB2SourceModule

9、ModifiedSourceModuleDBRMObjectModuleLoadModuleApplicationPlanPL/I-CompilerBindPre-compilerLinkageEditor(Load Module)(Application Plan)Runtime SupervisorData ManagerBuffer Manager(Other)DBPL/I + SQL:EXEC SQL CREATE TABLE S ( S# CHAR(5), .)EXEC SQL SELECT SNAME INTO :SNAME FROM SIF SQLCODE 0 THEN .CAL

10、L CREATE( .)CALL SELECT( .)IF SQLCODE 10000 AND CUTOMER.C#=INVOICE.CInternal 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 fieldCalls to Access Method： OPEN SCAN on C with region index GET next tup

11、le . . .Calls to ： GET10th to 25th bytes from block #6 of Language ProcessorOptimizerOperator ProcessorAccess MethodFile System databaseLanguageProcessorAccessMethode.g.B-tree; Index; Hashing DBMS1-11Embedded SQL4B-12Wei-Pang Yang, Information Management, NDHUEmbedded SQL: Dual-mode Dual-mode princi

12、ple: 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)PLSQLcall : 4B-13Wei-Pang Yang, Information Management, NDHUEmbedded SQL: a Fragment Fragment of a PL/I program with embedded

13、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 = r

14、etrieved value */ 13 ELSE . ; /* some exception occurred */ 4B-14Wei-Pang Yang, Information Management, NDHUEmbedded 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).

15、3. SQL statements can reference host variable. (PL/I變數(shù))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 Communication Area). SQLSTATE = 0 success 0 warning 0If more than one record are sa

16、tisfied: SQLCODE 0How 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 */4B-16Wei-Pang Yang, Information Management, NDHUOperation: Mul

17、tiple SELECTMultiple SELECT:How to handle the cases that more than one record are satisfied? Cursor4B-17Wei-Pang Yang, Information Management, NDHUCursorA 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 =:

18、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 XS#SNAMEdefine cursorendS1 SmithOPEN:XFETCH:S4 ClarkXS1 SmithS4 ClarkS1PL/IS

19、#SNAMESmithvarvar4B-18Wei-Pang Yang, Information Management, NDHUEmbedded SQL: An ExampleEmbedded 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 s

20、uppliers 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

21、 listed on the printer, with an indication of how that particular supplier was handled by the program. 4B-19Wei-Pang Yang, Information Management, NDHUEmbedded SQL: An Example (cont.)SQLEX: PROC OPTIONS (MAIN) ; DCL GIVENP # CHAR(6) ; DCL GIVENCIT CHAR(15) ; DCL GIVENINC FIXED BINARY(15) ; DCL GIVEN

22、LVL 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

23、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.4B-20Wei-Pang Yang, Information Management, NDHUEmbedded SQL: An Example (cont.) EXEC SQL DECLARE Z CURSOR FOR SELECT S#, SNAME, STATUS, CITY FROM S WHERE EXISTS ( SELECT * FR

24、OM 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 ;4B-21We

25、i-Pang Yang, Information Management, NDHUEmbedded 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 ) ;主程式成功=04B-22Wei-Pang Yang, Information Management,