untitled

Size: px

Start display at page:

Download "untitled"

지오 선
6 years ago
Views:

1 (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

2 - : ( ) ( ) 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 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

4 - -, -,,,, - : 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

5 IS-A.,. IS-A 1 N N M N P,,, M () () ()

6 ( 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

7 (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 ) ( (Selection) : <><>< > C R.. where <and, or, not> (Join) (. R<>S : <> AND <> AND <>. :, ( (Division) () RS RD1 RD2 RD3 SD2 SD3 R S D1 D2 D3 D2 D3 D RD1 RD2 RD3 SD2 SD SD SD <> : 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

8 (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 { } { } SQL SELECT (mapping) SELECT FROM WHERE. SELECT < > : FROM < > : WHERE <> : < > : < > : <> : () 8

9 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

10 (5) (1) (2) (3) (1) (1) 10

11 / ( ),. ( ). / -SQL, (,, ) , ( ) 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

12 1. ( 1 ) 2.. ( 2, 4, 6, 10 ) 3.. ( 9 ) ( 3, 4, 7, ) 6..,,,,,.,,, (1) (2),. (anomalies) (insertion anomalies) -, - (deletion anomalies) -, (modification anomalies) -,. (1) FD. (FD). FD (constraints). 12

13 1 31, NF B1 C1 D1 A1 B2 D2 B1 C1 D1 A1 B2 D (1) 31 B1 C1 D1 A1 B2 D BCNF - (1)., 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

14 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# P SP, PJ, JS J# J2 J# J2 S# S (a) (b)...,., ;.,.,.,.. - -,. 14

15 ... : : 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 : T i. 2. : T i. 3. : T i, ;, T i., 3. 15

16 . 2 write.. read(q), Q. read.,.. :. ( ). ( ). wait-die -.,.. wound-wait

MS-SQL SERVER 대비 기능

MS-SQL SERVER 대비 기능 Business! ORACLE MS - SQL ORACLE MS - SQL Clustering A-Z A-F G-L M-R S-Z T-Z Microsoft EE : Works for benchmarks only CREATE VIEW Customers AS SELECT * FROM Server1.TableOwner.Customers_33 UNION ALL SELECT