Data Management Database ICT Information and Communication Technology 2009/2/ SQL SQL DB DB XMLDB R. Ramakrishna

Size: px

Start display at page:

Download "Data Management Database ICT Information and Communication Technology 2009/2/ SQL SQL DB DB XMLDB R. Ramakrishna"

いとはさくいし
5 years ago
Views:

1 Data Management Database ICTInformation and Communication Technology SQL SQL DBDBXMLDB R. Ramakrishnan and J. Gehrke Database Management Systems McGraw-Hill

2 S A B C D morimo@hiroshima-u.ac.jp Subject [08F-Shimane] [] [08F-Shimane] ,, etc. Relational Model

3 Relational Model Schema Relational Model rowcolumn relation rowcolumn Students sname login age gpa ueto Courses cid cname credit AM Students( char(5), sname char(20), login char(40), age integer, gpa real) Courses(cid char(6), cname char(20), credits integer) Students ( CHAR(n) VARCHAR(n) INTEGER REAL char(5), name char(6), login char(32), age integer, gpa real ) Enroll cid grade AM Enroll( char(5), cid char(6), grade varchar(3))

4 Relational Model rowcolumn cardiality degree A B C C Cardinarity A A B A Key AB A B C D E IBM DB2, Informix, Microsoft ACCESS, Oracle, Sybase, etc. Degree Degree Relational Model Relational Algebra

5 Relational Algebra Selection Projection Cross-Product Direct Product, Cartesian Product Set-Difference Union U Relational Algebra Intersection I Join Division Selection 3 lubber rusty Projection 3 lubber rusty rating S rating 8 2) 58 rusty sname,rating ( ) sname, rating sname rating yuppy 9 lubber 8 guppy 5 rusty lubber rusty age ( ) age lubber rusty S rating 8 ( 2) 58 rusty ( ( S )) sname, rating rating 8 2 sname rating yuppy 9 rusty 0 5

6 Cross-Product S 3 lubber rusty R SR S. R lubber lubber rusty rusty union-compatible S 3 lubber rusty lubber rusty S 3 lubber rusty S 3 lubber rusty lubber rusty S S 3 lubber rusty S-(S-) Join S >< c R c( S R) S 3 lubber rusty R RSσ S. R lubber lubber rusty rusty Join S >< c R c( S R) Join S >< c R c( S R) S 3 lubber rusty R S 3 lubber rusty R S >< S R. R. S. R rusty S >< S R. R. S. R. 3 lubber rusty

7 Join S > < c R S S 3 lubber rusty >< S R. R. R S. R rusty Equi-Join S >< S R. R. S. bid day rusty S >< R S >< S R. R Natural Join S 3 lubber rusty S > < R R Natural Join S. bid day rusty Selection Projection Cross-Product Set-Difference Union Intersection Join Division U I SQL Division Division A AB x x, y A y B B AB 42 7

8 sno s s s s s2 s2 s3 s4 s4 pno p p3 p4 p p4 A pno B sno s s2 s3 s4 pno p4 B2 sno s s4 pno p p4 B3 sno s AB AB2 AB3 sno pno dno s p d s d s p3 d s p4 d2 s2 p d s2 d2 s3 d2 s4 d2 s4 p4 d2 A pno dno d2 B sno s2 s3 s4 AB pno B2 sno dno s d s2 d2 s3 d2 s4 d2 AB B A x y B y AB x AB B x( A) B x X y x y 45 A x( A) B x y x y x( A) BA x y B A x( x( A) BA) x AB B A AB: x x ( A) x x x( x( A) BA) 29 Brutus Lubber Rusty Zorba Interlake blue 02 Interlake red 03 Clipper green 04 Marine red Boats

9 Brutus Lubber Rusty Zorba sname ( () > < ) bid Brutus Lubber Rusty Zorba sname ( () > < ) bid Brutus Lubber Rusty Zorba bid day Lubber sname ( () > < ) bid 03 5 bid day Lubber sname Dustin Lubber Horatio sname ( () > < ) bid Brutus Lubber Rusty Zorba sname( (Re serves >< )) bid 03 bid day Lubber Lubber Lubber sname 9898 rating age bid day Dustin Lubber sname( (Re serves >< )) bid

10 29 Brutus Lubber Rusty Zorba Interlake blue 02 Interlake red 03 Clipper green 04 Marine red Boats Interlake blue 02 Interlake red 03 Clipper green 04 Marine red sname ( ( Boats) >< > ) color' red' < Boats 02 Interlake red 04 Marine red Interlake red 04 Marine red bname color Interlake red Marine red Interlake red Marine red Interlake red sname ( ( Boats) >< > ) color' red' < 57 bname color Interlake red Marine red 29 Brutus Interlake red 3 Lubber Marine red Interlake red 58 Rusty bname color sname rating age 7 Zorba Interlake red Dustin Marine red Dustin Interlake red Lubber Marine red Lubber Interlake red Horatio sname ( ( Boats) >< > ) color' red' < Interlake blue 02 Interlake red 03 Clipper green 04 Marine red Boats 02 Interlake red 04 Marine red 04 sname ( ( ( ( Boats)) >< Res) > ) bid color ' red' < bid Lubber Brutus Lubber Rusty Zorba sname ( ( ( ( Boats)) >< Res) > ) bid color ' red' < 60 0

11 ( Tempboats, ( Boats)) color' red' or color' green' sname ( Tempboats>< Re serves>< ) 02 Interlake red 03 Clipper green 04 Marine red Tempboats Interlake red 03 Clipper green 04 Marine red Tempboats day Interlake red Interlake red Interlake red Clipper green Clipper green Clipper green Marine red Marine red sname ( Tempboats>< Re serves>< ) 63 day 29 Brutus Interlake red Lubber Interlake red Interlake red Rusty Clipper green Clipper green Zorba Clipper green Marine red Marine red day sname rating age 02 Interlake red Dustin Interlake red Lubber sname ( Tempboats>< Re serves>< ) 64 day 29 Brutus Interlake red Lubber Interlake red Interlake red Rusty Clipper green day sname 64 rating Horatioage Clipper green 3 02 Interlake red Dustin 7 7Zorba Clipper green Interlake red Lubber 74 8Horatio Marine Interlake red red Horatio 85 7Art Marine red 03 Clipper 3 green Dustin 95 7Bob Clipper green Lubber Clipper green Horatio Marine red Dustin Marine red Lubber sname ( Tempboats>< Re serves>< )

情報の構造とデータ処理

情報の構造とデータ処理 mizutani@ic.daito.ac.jp 2014 SQL information system input process output (information) (symbols) (information structure) (data) 201411 ton/kg m/feet km 2 /m 2 (data structure) (integer) (real) (boolean)