(shared) (integrated) (stored) (operational) (data) : (DBMS) :, (database) :DBMS File & Database - : - : ( : ) - : - : - :, - DB - - -DBMScatalog meta-data -DBMS -DBMS - -DBMS concurrency control E-R, (entity) (relationship) : : : () : () : (,, ) E-R :, :,. :,,, :, (relation) - (tuple) : - (attribute) : - scheme : - schema : scheme SQL select ( ) from ( ) where () (oracle, ms-sql, sybase, db2 ) 1
- : ( ) ( ) - - - + -SQL - ( ) SQL - (owner) (member) (link) - 1:N - N:M - (tree) - (root) - parent-child (1:N) - end users casual end users parametric or naive users sophisticated end users stand-alone end user): system analysts / application programmers : DBA A1 A2 B1 B2 Host lang Host lang Host lang Host lang +DSL +DSL +DSL +DSL ( A) A ( ) ( ) / A ( B) B / / B ( ) (DBMS) 2
3 (1) 3- - Host Language :,, ( C,, ) - : (DDL) (DML) - :,, 3 (2),,,,,, (1) (I/O processor) -, (parser) - (precompiler) (authorization control) (2) (integrity checker) (transaction manager) (query processor) - (optimizer) - SQL DBA DBMS,,. - DB -,,, - - -,, - ( ) Process Process Process -driven Design Data -driven Design DB ( ) 3
- -, -,,,, - : E-R( ), (DB) - DBMS - DB DBMS DBMS ( ) ( ) ( ) ( DBMS ) ( DBMS ) ( ) - :. ( ( ) : - : - :. - :. - :. - : - : - :,, ( ),, e 1 ( 38,, 32, ) e 2 ( ( ) 07,, 35, ) e 3 ( 1012,, 41, ),, c 1 (e,, ) c 2 (,, ) c 3 (,, ) E-R.......... E1 R E2 E1 1 R N E2 R (, ) E E2R ER RE1:E2 1:N RE (, ) IS-A 4
IS-A.,. IS-A 1 N N M N P,,, M () () () 1 2 3 4 5 1 200234-070635 28 2 2000-050357 25 3 200121-001532 32 4 2001-000327 22 5 200123-050353 28 6 200034-001323 21 5
( t t[a i ] = v i (A i ) ) ( r(r) ). ( R (, ) R(A 1, A 2,..., A n ) t = <v 1, v 2,..., v n >. ( ) ( ) (atomic values). null., (Super Key) :. (Key) :,. (prime attribute) :. (non prime attribute) :. (Candidate Key) (Primary Key) (Alternate Key) (Foreign Key) (1). (domain constraints), (key constraints), (entity integrity constraints), (referential integrity constraints). - A A dom(a). -. -. - R 1.FK R 2. 6
(1) : R 1 R 2 : R 1 R 2 ( (Projection) : <>()... : R 1 -R 2 : R 1 R 2,,, R 1 (A 1,A 2,...,A n ) R 2 (B 1,B 2,...,B n )., i=1,2,...,n dom(a i ) = dom(b i ). (union compatibility).,, R 1. 2314 2336 2358 2384 26 29 31 21 ) 2314 2336 2358 2384 ( (Selection) : <><>< > C R.. where <and, or, not>. 200234-070635 28 2000-050357 25 2001-000327 22 200121-001532 32 200123-050353 28 200034-001323 21 (Join) (. R<>S : <> AND <> AND <>. :, 200121-001532 32 ( (Division) () RS RD1 RD2 RD3 SD2 SD3 R 1 2 3 2 S 3 1 3 D1 D2 D3 D2 D3 D4 2 2 3 1 1 2 3 2 3 4 2 3 7 8 6 7 8 2 3 5 6 7 8 2 3 9 7 8 7 8 10 6 7 8 2 3 6 7 8 7 8 9 7 8 2 3 9 7 8 2 3 9 7 8 7 8 RD1 RD2 RD3 SD2 SD3 2 3 2 3 1 1 1 6 9 2 2 7 7 3 8 2 7 7 3 3 8 8 8 7 8 SD4 4 5 10 10 10 SD4 4 5 10 4 5 10 4 5 10. <> : R S, ( R,S : ) S R. T := R S,T X S = R T S = R. < > R S T := R S T. R (1) S D 1 D 2 D 3 D1 a 1 A D3 D 2 D 3 b 1 A A a 1 A a 2 A b 1 B a 2 B a 2 b 1 B (3) S T (4) S a 2 C D a 2 D 3 D 1 D 2 A a 2 a 1 E B C D T (2) S T D 1 D 2 a 2 b 1 T D2 D 3 D 1 1 A a b 7
(aggregate functions) SUM, AVERAGE, MIN, MAX, COUNT. < > < > (R) < >. :. RESULT(,, ) COUNT,, () (1) (predicate calculus) : (). ( (procedural). (, (expressive power). :. :. (2) (1) A B A B A C A 500 B A 400 600 800 400 900 { } { } SQL SELECT (mapping) SELECT FROM WHERE. SELECT < > : FROM < > : WHERE <> : < > : < > : <> : () 8
CREATE SCHEMA CREATE SCHEMA ) CREATE SCHEMA; CREATE TABLE CREATE TABLE. : INTEGER, FLOAT, DECIMAL( i, j ), CHAR(n), VARCHAR(n) SQLNULL NULL NOT NULL. ) CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGR CHAR(9) MGRSTARTDATE CHAR(9) ) ; CREATE (1) (2) (3) (4) 9
(5) (1) (2) (3) (1) (1) 10
/ ( ),. ( ). / -SQL,.. -. - (,, ). -. -. -. 1. - 2. - -,. 3. - ( ) 4. -.,,.. SQL SELECT author_name FROM authors, titles, sales WHERE Sales.title_id=titles.title_id and titles.author_id =authors.author_id and authors.city= cheongju Π (σ Sales.title_id=titles.title_id and author_name titles.author_id =authors.author_id and authors.city= cheongju (authors (titles sales))) (authors (titles sales)) Π author_name σ Sales.title_id=titles.title_id and titles.author_id=authors.author_id and authors.city= cheongju σ Π authors σ Π titles sales
1. ( 1 ) 2.. ( 2, 4, 6, 10 ) 3.. ( 9 ) 4.. 5.. ( 3, 4, 7, ) 6..,,,,,.,,, (1) (2),. (anomalies) (insertion anomalies) -, - (deletion anomalies) -, (modification anomalies) -,. (1) FD. (FD). FD (constraints). 12
1 31, 31 31 1NF 2 2 31 31 B1 C1 D1 A1 B2 D2 B1 C1 D1 A1 B2 D2 3 2 4 15 3 4 3 2 4 15 3 4 31 3 3 (1) 31 B1 C1 D1 A1 B2 D2 3 2 4 15 BCNF - (1)., 2 1. 3 2. BCNF 3. 3 BCNF. BCNF( 3 ).. (lossless join) (dependency preservation) 4 (1),. (multivalued dependency),, X Y (set of values), X Y (multidetermine). (MVD) X Y. FD MVD r(r). 3 BCNF. 13
4 (5) 6 A, B, C R, (A, C) B A, C, R A B (Multi Valued Dependency : MVD).. 5 (1) B A. (multidependent)" A, B C. 9 JD ()., JD. S# S2 S2 S# S2 P# P1 P# P1 J# J2 J2 S# S2 P# P1 P# P1 J# J2 S# S2 J# J2 P# P1 7.16 SP, PJ, JS J# J2 J# J2 S# S2 7.17 (a) (b)...,., ;.,.,.,.. - -,. 14
... : : CPU. :. (1).. 1. (shared lock) :. LS. 2. (exclusive lock) :. LX.,. (read_lock, write_lock) 2. 2 (growing phase). (shrinking phase).. ( ). 2. (1). T i TS(T i ), T j TS(T j ), TS(T i ) < TS(T j ).., Q : W-(Q) write(q). R-(Q) read(q). (1) T i 3. 1. : T i. 2. : T i. 3. : T i, ;, T i., 3. 15
. 2 write.. read(q), Q. read.,.. :. ( ). ( ). wait-die -.,.. wound-wait -... 16