Βάσεις δεδομένων

Click here to load reader

  • date post

    14-Nov-2014
  • Category

    Documents

  • view

    22
  • download

    1

Embed Size (px)

Transcript of Βάσεις δεδομένων

1

..................................................................................... 6 1.1 ............................................................................................................ 6 1.2 - .............................................................. 7 1.3 .............................................................................. 8 1.3.1 ......................................................................................... 9 1.3.2 ................................................ 9 1.3.3 ....................................................................... 10 1.4 ........................................................................................................ 12 1.5 ................................................................ 14 1.6 .......................................... 15 1.7 ............................................................. 16

2

20 2.1 ....................................................................... 20 2.2 , ............................................ 21 2.2.1 ............................................................ 22 2.2.2 , ................... 23 2.3 ............................................ 26 2.4 .................................................................................... 27 2.5 ............................................................................... 28 2.6 .................................................................................... 29 2.7 ................... 29 2.8 - .............................................................................................. 31 2.9 ........................................................................................................ 31 2.10 .......................................................................... 32 2.11 (DDL)............................................................. 33 2.12 (DML).............................................................. 34

3

-..................................................... 36 3.1 ....................................................................................................... 36 3.2 ................................................................... 37 3.3 ...................................................................................................... 39 3.4 .......................................................................................... 42 3.5 E-R. ........................................................ 43 3.5.1 (relationship degree) .................................... 43 3.5.2 . ................................. 43 3.5.3 (cardinality) .............................................................. 44 3.5.4 : .................................................... 45 3.6 ..................................................................... 46 3.7 .......................................................................... 47 3.8 E-R .......................................................... 48 3.8.1 ........................................................................... 48 3.8.2 ..................................................................... 48

4 4.1 5

.............................................................. 50 .......................................................................................................... 50 ................................................................................... 52

5.1 ................................................................ 52 5.2 ............................................................... 55 5.3 ........................................................ 56 5.4 ................................................................................................. 57 5.5 .............................................................................. 57 5.6 ................................................................................... 58 5.7 ................................................................................................... 59 5.8 .................................. 60 5.8.1 ................................................................................ 60 5.8.2 - .................................... 61 5.8.3 Null ............................................................................................................ 62 5.8.4 null- ..................................................... 63 5.8.5 ...................................................................................... 64 5.9 E-R ................ 65 5.9.1 .................................................................. 65 5.9.2 ............................................................... 65 5.9.3 ............................................................................................... 66 6 ...................................................................... 68 6.1 .......................................................................................................... 68 6.2 ............................................................................. 68 6.3 ............................................................................... 69 6.4 ........................................................................ 69 6.5 - ............................................................. 70 6.5.1 (1NF)................................................................... 70 6.5.2 (2NF)................................................................. 71 6.5.3 (3NF) ..................................................................... 72 7 7.1 7.2 7.3 7.4 7.5 7.6 7.7 7.8 7.9 7.10 7.11 7.12 7.13 7.14 7.15 7.16 7.17 7.18 7.19 7.20 7.21 7.22 7.23 7.24 7.25 7.26 ..................................................................................... 74 ........................................................................ 74 :........................................................ 76 ............................................................................... 77 .................................................................................................... 77 ........................................................................ 78 - ............................................................................ 78 ....................................................................................... 79 ......................................................................................... 80 ................................................................ 81 ................................................................................ 82 ........................................................................................... 84 ......................................................................... 85 ................................................................................... 87 ............................................. 88 ......................... 90 ................................................................... 91 .............................................................................. 91 () ..................................................................... 92 ........................................................................................ 94 ........................................................................................ 95 ................................................................................... 96 ......................................................................... 97 () ................................................................... 100 NULL ..................................................................................................... 102 (TRUE-UNKNOWN-FALSE) .............................................. 102 ............................................................ 103 . 2

1.1 7.26.1 (deletion)................................................................................. 103 7.26.2 (insertion) ............................................................................... 103 7.26.3 (updating)......................................................................... 104 8 SQL ................................................... 105 8.1 ........................................................................................................ 105 8.2 () ................... 108 8.2.1 ............................................................................................ 108 8.2.2 SELECT - ......................................................... 111 8.2.3 SELECT DISTINCT - ........................ 111 8.2.4 SELECT - ......................................... 112 8.2.5 where ................................................................................... 113 8.2.6 where - between .. and.. ......................................... 113 8.2.7 from ...................................................................................... 114 8.2.8 () ............................................ 116 8.2.9 - order by ............................................................. 116 8.2.10 ..................................................................... 117 8.2.11 ............................................................................. 118 8.2.12 ................................. 119 8.2.13 Null ................................................................................................. 120 8.2.14 Null ....................................................... 121 8.2.15 Null ............................................. 121 8.2.16 ................................................................... 121 8.2.17 - (group by) .......................... 123 8.2.18 having ...................................... 124 8.2.19 ............................................................... 127 8.2.20 - in (not in).............................. 128 8.2.21 - some (any)........................... 130 8.2.22 - all ......................................... 131 8.2.23 - exists (not exists) ................. 133 8.2.24 - unique (not unique).............. 136 8.2.25 .............................................................................. 138 8.2.26 .............................................................................. 139 8.3 ......................................... 141 8.3.1 SQL ........................................................... 141 8.3.2 .................................................................................. 142 8.3.3 ..................................................... 143 8.3.4 ............................................. 144 8.3.5 ................................................................................ 146 8.3.6 .......................................................................... 146 8.4 (X) .... 147 8.4.1 ................................................................................................ 147 8.4.2 ................................................................................................. 148 8.4.3 ........................................................................................... 149 9 ............................................................................................... 151 9.1 ..................................................................................... 152 9.2 ACID ............................................................. 154 9.3 .................................................................................... 155 9.4 ......................................................................................... 155 9.5 ................................................... 157 9.6 ............................................................................ 157 9.7 ....................................................................... 158 9.7.1 : ............................................................ 158 9.7.2 ........................................................... 159 . 3

9.8 .................................. 159 9.8.1 : ................................................ 159 9.8.2 ........................................................... 160 10 10.1 10.2 10.3 10.4 11 ................................................................................................. 161 ............................................................................................. 161 ............................................................................................ 162 ................................................................................. 164 SQL ............................................................................. 164 ..................................................................................... 166

11.1 ...................................................................................... 168 11.2 ........................................................ 168 11.3 ........................................................................................ 169 11.4 (LOCKING) ...................................................... 170 11.5 (COMPATIBILITY MATRIX) .................................. 171 11.6 (DATA ACCESS PROTOCOL) ............. 172 11.7 (DEADLOCK) ............................................................................. 172 11.7.1 .................................................. 173 11.7.2 .......................... 173 11.7.3 ....................................................... 174 11.7.4 ................................................................. 175 11.7.5 ......................................................................... 175 11.7.6 ............................................................................ 176 11.8 (SERIALIABILITY) ............................................................. 177 11.9 ....................................................... 177 11.10 ............................................................................. 178 12 .................................................................................................... 179 12.1 ........................................................................................................ 179 12.2 .................................................................................................. 179 12.3 ...................................................................................... 180 12.4 ................................................................................ 181 12.5 ......................................................................................... 181 12.6 , . ............................................................................................................ 183 12.7 ................................................................................................ 184 12.8 ................................ 185 12.8.1 ..... 185 12.8.2 (Access Matrix Model) .......................... 185 12.8.3 GRANT .................................................................................... 186 12.8.4 REVOKE ................................................................................. 187 12.8.5 .......................................................................................... 187 12.8.6 ...................................... 188 12.8.7 SECURITY RULE .................................................................................. 188 12.9 ............................... 189 12.9.1 .................................................................................. 189 12.9.2 .......................................................................................... 190 12.9.3 .......................................................................................... 191 13 13.1 13.2 13.3 13.4 ...................................................................................... 192 .......................................................... 192 DES (DATA ENCRYPTION STANDARD)............................ 193 ............................................................. 194 M RSA .......................................................................................... 195 . 4

1.1 13.4.1 RSA ....................................................................................... 195 13.4.2 RSA..................................................................................... 196 13.4.3 .............................................. 197 14 .......................................... 198 14.1 ........................ 198 14.1.1 ........................................ 199 14.2 ...................................................... 200 14.2.1 .................................................................................................... 201 14.2.2 ...................................................................................................... 202 14.2.3 .................................................................................................. 202 14.2.4 ....................................................................................................... 203 14.3 ........................................... 203 14.3.1 ...................................................................................... 204 14.3.2 ..................................... 205 14.3.3 ............................................... 207 14.3.4 . ..................................... 208

. 5

1 1.1 . , , , ' , . . , text . . , video . (GIS) . on-line (OLAP) . WEB . , , , , , . . (database) . . . , . . ( - - .), ( - - .), ( - - - .), ( - - .). . 6

1.2 - . , , , . , .

1.2 - 1950 ( ). . 1960 1960 . 1964, Charles Bachman (1st Turing Award, 1973) General Electric (network data model) . . 1965 IBM North American Aviation (hierarchical data model). , . (Information Management System - IMS) 1969 . IMS . 1970 (Relational data model) 1970 . Edgar Codd (Turing Award, 1981) IBM "System R4 Relational". . 1979 . System R (IBM) INGRESS (Berkley) SEQUEL, QBE, QUEL. 1976 - Chen

. 7

1979 Relational Software Incorporated (RSI) Oracle V.2. IBM Query Management Facility 1983. 1980 SQL ( System R) QUEL INGRES. (transaction management) Jim Gray (Turing Award, 1999). , , . 1980 : Oracle, SQL Server, Sybase, Informix, DB2. : DBase II (Ashton Tate) Paradox, Microsoft ACCESS. 1990: , , (HTML, XML, ASP )

1.3 (data) / . - (database) . . () . - (Database Management System - DBMS) () , . , - (Database System) DBMS .

. 8

1.3 ,

1.3.1

:

(miniworld) . . , . . , ' .

: . ,

1.3.2

" " , , : , format, . ( )

. 9

( . .. 1.000). ( . .. .) (.. , .) ( formats) ( ) (.. 10.000 2.000 3.000 )

.

() (database management system-DBMS) . , (general-purpose) , . (definition) , . ' . (manipulation) (queries) , .

1.3.3

, DBMS, (database system). O .

:

. 10

1.3 : , , (sharing) , (database administration) (database administrator DBA) ( , ), ( ), ( ) (concurrency control) (recovery) .

( PC) mainframe. ,

. 11

( " ") , ( " ") . (single user system) (multiuser system) .

1.4 , : Integer Real Char String Date bytes 2 4 1 8

, . : , , . . , , , , , . , : _ _ _

_

: , , (records).

. 12

1.4 PASCAL , C++ "struct" "class" COBOL Data Division . , , , (catalog) - (meta-data).

- , (integrated) (shared ). , , . (data integration) , , . , , , , , .. , , ( ). , , . , , () , . . .

(data sharing) , ( ). , (" "). ( ) .

. 13

, , , , .

1.5 , . : - - - (--) (, , , ) - (, , , ) (, -, ) (Servers, , Administrators, , , , ) (, , , ) Software / Hardware - - , ) (, ,

. (, , , ) (, , , , ) (, , , , ) ( / ) (, , , , , ) . , (decision support). , , . (.. ), ( , ) .

. 14

1.6

1.6 . (redundancy) .

. () , . .. . . . , . , 3 8 . DBMS ' "" (, ). , . , (inconsistent). . . ( ) . , , , . . , (DBA) . , , , , . (data interchange), . . , DBA

. 15

, . (, , , ..) . , , , ( ) , . . (integrity) . "" , . , 400 , 40, 9 . ( DBA ) , , . . DBA " ". , ( ).

1.7 1970 (relational approach) , 25 . , " " . , , ; , : 1. ( ).

. 16

1.7 2. ( , ) . , , . "" (relational) "" (relation) . (Student) - (Course) - (Section) (Grade Report) - (Prerequisite)

. : . , , . , , . , (hierarchic system) (),

. 17

.

1

2

3

1

2

3

4

. , . ( -) , (hierarchical system), (network system). 1

2

1

2

3

1

2

3

4

. 1970 1980. , .

. 18

1.7 053 109 027 212 310 . 45893

. 28 . . . .

45 97155 9 66202 12 6 70359 32078

. CA-DATACOM/DB Computer Associates International Inc. IMS IBM Corporation CA-IDMS/DB Computer Associates International Inc. DB2 IBM Corporation INGRES Ingres Inc. ORACLE Oracle Corporation SYBASE Sybase Inc.

1980

Access (Microsoft) Paradox (Borland), Q&A (Symantec), FileMaker Pro (Claris Corp.), Approach (Lotus). - (open-source), , ( ). , . PostgreSQL , MySQL, BerkeleyDB

. 19

2 2.1 . . . ' : () () : . . .. , -, . . : . . .

() : . . . .. , . 2 N

1

. 20

2.2 ,

2.2 , . - . employees : record name: char[30]; manager: char[30] end : Employees(name, manager) employees name manager . , . (basic operations) . : (1) , (2) (3) . 1. (structures): , .. 2. (operations) : .. , , , . : , / / . 3. (constraints) : , . (inherent), (explicit) (implicit), .

. 21

, ( ) . . "___" . , , , .

2.2.1 . (high-level) (conceptual) . (low-level) (physical) . . (representational) (implementation), . ' . , , (semantics) . :

, . (entity) , . (attribute) , . (relationship) "-"

. 22

2.2 , . -, .- -

-

-

: - (ER).

- - - . ' (record-based) . 192-83-7465 254-56-1480 147-25-1023 187-25-2558 312-512-9641 142-878-117 - - 25 47 7 55 24 11 -

- -141 -541 -142 -355 -369 -212 2000 1500 1800 2200 1500 3000

: , .

2.2.2 , (database instance) (database schema) . .

. 23

(instance) . .

(database schema) . . (schema diagram). - - . . ().

. , . , , . . , " COSC1310 " . . , . (state) (set of instances)

. 24

2.2 , . . . , . , " " . " " .

:

. 25

2.3 (ANSI/SPARC) 3- . 3 - "": , /, . , . 3- : , , :

. (internal level) (external level) ( ). (conceptual level) " " ( ). . :

. 26

2.4 , , ( ). , , , , "", ' ( , , , ..). : University . T ) (View) : Course_info (cid:string, enrollment:integer) ) : students (sid:string, name:string, login:string, age:integer, gpa:real) courses (cid:string, cname:string, credits:integer) Enrolled (sid:string, cid:string, grade:string) ) : 1 students , .

2.4 . . , , . : , , . " " (4th generation language, 4GL) , (query language) ,

. 27

: ANSI/SPARC, . , , (, ' , ). , , .

2.5 , ( ). , , " ", , .., . . , , , , , ...

. 28

2.6 , . , . . , , , , ..

2.6 . ' . , , , , ...

SQL CREATE VIEW ATHENS_SUPPLIERS AS SELECT S_No, S_Name, S_City FROM SUPPLIERS WHERE S_City="ATHENS" CREATE VIEW ALL_SUPPLIERS AS SELECT * FROM SUPPLIERS

CREATE TABLE SUPPLIERS ( S_No NUMBER(4) NOT NULL, S_Name CHAR(25),S_City CHAR(20) );

CREATE INDEX Supplier_Name ON SUPPLIERS(S_Name); CREATE UNIQUE INDEX Supplier_No ON SUPPLIERS(S_No);

2.7 (data independence), ' . :

. 29

(.. ) . . . ' . , . .

( .. -access path -indexing field-) . , " 1998". .

. (abstract data types) . , . . , .

. 30

2.8 -

2.8 - - . " " . 1. , (format)

2. - , , . - ( ) : ; ; ; ; ;

2.9 (index) () access path ( ) / : block

( ) block .

. 31

2.10 (Database language) SQL , . . : -Storage Definition Language (SDL) -Data Definition Language (DDL) -View Definition Language (VDL) -Data Manipulation Language (DML)

, . , , (data definition language -DDL-), . DDL . , . , (storage definition language -SDL-) . . , (view definition language -VDL-), , . , . , , . (data manipulation language -DML-) . . , , . 32

2.11 (DDL) . SQL, , . SQL .

2.11 (DDL) (Data Definition Language -DDL) DDL. 1. DDL . 2. . 3. DDL : . DDL-SQL , . CREATE | DROP | ALTER TABLE (base table) CREATE | DROP | ALTER VIEW (virtual table) CREATE | DROP | ALTER INDEX (index table)

DDL SQL create table (_ int, char(6), int, char(3), char(3)); create index for on _; ( ), () . _, , ( -hash table- _ )

. 33

2.12 (DML) :

(Data Manipulation Language -DML) . : (low-level) (procedural): (high-level) (nonprocedural):

(query language) . , . ' DML . . . - . , , . , , , (host language) (data sublanguage). , SQL, DML (setat-a-time) (set-oriented).

. 34

2.12 (DML) , . DML-SQL SELECT INSERT DELETE UPDATE

DML SQL update set = -4 where _ = 123 and = AUG 20 SQL 4 123 20 . insert into values(171,AUG 31, 100, , ) SQL 171 100 31 . select * from where _ = 123 SQL 123.

. 35

-

3 -3.1 - (entity-relationship model) P.P. Chen 1976 , . . , , . (conceptual design) . (conceptual data model) , , , . - ( "", "", "", " " .), () ( "" "", "" "", "" " " .). , (.. , , ). (entity sets / types). , (relationship sets / types).

. 36

3.2

- (entity): / (entity type) : (attribute): , : , , /, , : : : .

3.2 - : (entity type) - , , , , , , -, . (attributes) . , , , , . (relationship type) . , . "" "" "". (attributes), . , (, "" "" "20/10/2000"}. : , , , . : , , . . 37

- , , , , , , . (-) (-) (- )

/

( ) - - (E-R diagram). . . . . .-

-

-

- .

. 38

3.3

3.3 : , , . () . (composite attributes) . , , , , , _ . (simple) (atomic attributes). .

. . ..

. (single-valued). , . - . . , , , . (multivalued).

. 39

-

. ( ) - . . (derived attribute). , _ ( ) .

_

_

- . (key constraint) (uniqueness constraint) . . - (key attribute) .. , - .

, , . ,

. 40

3.3 - . , - . -. , , _ _ . _

' ( ). _ , . ( ) ' .

( ) . . (domain), .

Null . . , _ . , _ . null. null _ null _. . 41

-

3.4 (relationship type) - - . . . .. , .

(multiple relationships). ,., --, --. (, - recursive relationship type) .., ( ) ( )

.

N

1 1

1

1

1

N

1

M

N

N

N

: E-R

. 42

3.5 E-R.

3.5 E-R. () () .

3.5.1 (relationship degree) (degree) . 2 , 3 , (N-ary relationship). , . (binary), (ternary). ,

: SUPPLY SUPPLIER, PART, PROJECT .

3.5.2 . (total) (partial). , ' . , ' , . , , . (existence dependency). , .

. 43

-

3.5.3 (cardinality) . : 1:1 : ( --) 1: : 1 ( --). :1. : : . ( --)

. 44

3.5 E-R. 1,

1 : (--), , ( ) .

:1

1

1:1 . .

1:1

1

1

:, .

:

3.5.4 : (keys): ( ) (, ) (), () , - (superkey). . 45

- , ( ) - . { , } () ' , { , , _} ( ) -, ( -) (candidate keys) (keys). , { , } . , (primary key) . (identifier) . , . -

: ( ) / / - (super-key). - ( ) (candidate key). (primary key)

3.6 . , ' . . 1:1 1: . ,

. 46

3.7 ( ). 1:1 , . 1:, . , ' , . 1:, . : , ' . . : , ' - .

3.7 - (weak entity types).

, . (.. ) (.. ) , . (owner entity type) (identifying relationship). ( ) , -. , _ , (__) . . 47

-

3.8 E-R 3.8.1 is-A (is-A generalization) (generic) . , . ( ). (inheritance) . , (.. ) . , . . (complete) . . , (disjoint) . .

3.8.2 E-R , , ' 2 . (aggregation). ( ) "" , , ,

(aggregation). MONITORS . 48

3.8 E-R

-

2 R

1

R

2

1:

1

1

R

2

-

. 49

4 4.1 . Ted Codd IBM Research 1970 [Codd 1970] INGRES System/R. . () . () 1:1 . 1:1. (relation). . (relational database management system, RDBMS), : 1. (). ' . , , , , , , .. , , . 2. .. () . ( - ) . , , . 3. , ( ). , , . (). . . . 50

4.1 . (). 1. R , . (1, 2,) 2. (, , ) (, ) (, , ) (, , , ) (, , , ) (, , , ) : . . . . (predicate). , . (Data Definition Language -DDL) ( SQL). 1 2 3 () ( ) create table R(A1 D1, A2, D2, ) insert/delete/update select - from - where

, SQL. SQL . SQL (DML) . , (DDL) (), ( ), (.. ). , SQL " ". GRANT, ' ' . . 51

5 5.1 . Ted Codd IBM Research 1970 . - - . ( ) (). ( ). : (1) , (2) (3) . 1. (structures): () , .. 2. (constraints) : .. . (inherent) (explicit) 3. (operations) : .. (), , , . : , / / . - : 1. ( ) . 2. ( ). 3. ( ). 4. . 5. () . 6. () . . . . 52

5.1

(Domain): . / . . . . R1 R2.

:1. : ( ) 2. : Null ( ). 3. : R1 R2. A .

13 15 17 18 1 2 4 2 1000 1200 2300 2200

1 2 3 4 / /

.

. : 1. : dom(A) (.. "small integers" "integers between 1

. 53

and 10" ). (data types), char, number, date . 2. (user-defined) : , . , . .. . , (triggers) (assertions) .

(relational algebra), , JOIN, : , , , , ( ). (restrict), (project), (join), (divide).

. "___". , , , .

- - -

- -

- -

- - -

.

. 54

5.2

5.2 ( ) (structure) ( ), (integrity) ( ), ( ). . " . , , , , , , . , , : (relation) . (tuple) , (attribute) . (cardinality) (degree). (primary key) , . , (domain) .

-

25 7 24

47

. ()

. 55

"" , "" .

5.3 : R , . , . R(A1 ,A2 , ... An). S R1, R2, ,Rm.. S ={R1, R2, ,Rm}. (tuple) t R(A1 ,A2 , ... An). () t = , v, D(Ai). (relation instance) (relation) r(R) r(R) = { t1 ,t2 , ... tk }. (cardinality) R. r(R) CARDR.

: () () . , : (insert), (delete) (modify). , (queries) . , .

. 56

5.4 - (E-R) : E-R ... .. .

5.4 " ", . ( ). (domain) D , . . " " , . (data type) (format) . , _ (ddd)ddd-dddd, d () . _ 15 80. _, .

5.5 (): R D1 ,D2 , ... Dn : . ( , .) : 1. , , < -:-- > { , , ... } Ai Di. . 57

2. (tuples). ( ) (). .

5.6 , , : . . . . 1. ( ), . . 2. ( ) , . , . , " " "97 " " " , " " , . 3. ( ) (). , , .. , , , . , . , " " " " (..), " " , . 4. , , ( ). ( ): . 58

5.7 , . : . (normalized) , , . , . , "" " " .

5.7 , : 1. ( , ) (DBMS). CREATE TABLE CREATE VIEW CREATE SNAPSHOT . , , . 2. ' . 3. (base relation) . . 4. (derived relation) , , .. . 4. (view) . , . 5. (snapshot) , ( ). , , , , . 6. (query result) . ( ).

. 59

5.8 " " , . DBMS . , . , ( ) , ( ) , . : 1. , 2. 3. . :

5.8.1 , E-R, . (primary key) . . SK R r(R) - (super-key). , t1 t2, t1[SK] t2[SK]. (candidate key) - (, -). (primary key) PK, () . R . (_, , ) {_} - {_, } - {_, } , - {_} {_, }

. 60

5.8 {} -,

{_} .

, . , , ' , .

5.8.2 - . . (foreign key) FK, R1 R2 R1 R2. ( t1 r(R1) t2 r(R2), t1[FK] = t2[PK]) : 1. , . . 2. , . 3. . 4. . 5. (' , . , : (referential integrity):

. . 61

" " . , : . . , " " " ", . , , , .

, : . (DELETE) , . , . , : RESTRICTED ( ) "" (, ). CASCADES ( ) - " ", . . , . , . , : RESTRICTED ( ) "" (, ). CASCADES ( ) " ", . , , (, ' ).

5.8.3 Null . , . 62

5.8 , , " " null. , null . . , " ", " ", " ". , null. , . , " " , IS NULL ( null ) IS NOT NULL ( null ). null, null .

5.8.4 null- , , ( entity integrity): :

null. , , , null. . - R, (null) r(R). , t[] null , t r(R). . ( R null , )

: , (, , , ). , , . . 63

, (). ( ) , .

: , .

5.8.5 . : : dom(A) (.. "small integers" "integers between 1 and 10" ). (data types), char, number, date . check SQL: .. create domain numeric(5, 2) constraint - check( >= 4.00) .. create domain - char(10) constraint -- check value in (", ") , . (user-defined) : , . , . .. . , (triggers) (assertions) . SQL create assertion _ check (not exists (select * from as , as , as where . > . and ._ = ._ and . = ._) )

. 64

5.9 E-R

5.9 E-R , E-R ( ) , , , ( ). E-R ( ). . , ,

5.9.1 : - () R - , R . - R : () , () .

5.9.2 : : , 1:1 , 1: ( :1) . 1:1 1: , , , ' . : R. S1, S2, , Sn R : () R, , () Si, R. 1-1 ( ) R S, R S, . : () R, , () S, . S, : , ( , ) ( S). , null , . 65

. 1- ( ) R S, S 1 , R : () R, , () S, . 1:1, 1: :1 ( ) , , ( , ), . , . , .

5.9.3 , ( , ). . = {1, 2,, n} 1, 2,, n , . , R : ( , ) ( ) .

: E-R : . .

. 66

5.9 E-R ' . . . .

E-R : (_, , _, , , , _, , _, _) (_, , __, __) _ (_, ) (_, _, _, _) (__, _, ) (__, _, , _,

! . . 67

6 6.1 , , , . . 1. . 2. ( ). 3. ( ). 4. .. 5. () . 6. () . - . : 1. . 2. - 3. - () . 4. -- . 5. .

6.2 ( ) . :

.

. 68

6.3 ( ). - - - . - ( ). ( ") - "

, , ,

, , -

, -,

. .

6.3 -: ( ) . . , . . .

6.4 - . : : . : - . . . 69

6.5 - ( ) (normal forms). : . :o o o o o o

(1NF) (2NF) (3NF) Boyce-Codd (BCNF) (4NF) (5NF)

. .

(1, 2, 3) Codd. 1. 1 2, 2 3. . Fagin ( ). Fagin (projection join), (5). ' -BC 4, 4 5.

6.5.1 (1NF) .

:1. () . 2. ( ). 3. ( ). 4. . 5. () . 6. () . . 70

6.5 - ( ). ( 1 ). : (, , , _) IBM IBM IBM Netscape Netscape IBM IBM IBM NETS NETS 01/05/99 01/06/99 01/07/99 01/05/99 01/06/99 _ 101.00 100.50 102.00 33.00 112.00

6.5.2 (2NF) ( ).

. . : _ , , (, , _, , _) IBM IBM IBM Netscape Netscape IBM IBM IBM NETS NETS _ Armonk, NY Armonk, NY Armonk, NY Sunyvale, CA Sunyvale, CA 01/05/99 01/06/99 01/07/99 01/05/99 01/06/99 _ 101.00 100.50 102.00 33.00 112.00

. 71

, ,

_ _

, _ , _

, _. , . ( ) _ ( _). .

: : (, , _) (, , _) IBM Netscape IBM NETS _ Armonk, NY Sunyvale, CA

, _ , _

IBM IBM IBM NETS NETS

01/05/99 01/06/99 01/07/99 01/05/99 01/06/99

_ 101.00 100.50 102.00 33.00 112.00

,

_

6.5.3 (3NF) .

. 72

6.5 - R , . . .. : _ _ , : .

. : IBM AT&T Putnam Bergen _ 28% 26%

_ _

AT&T ? 2 ( Bergen). : : IBM AT&T Putnam Bergen Putnam Bergen _ 28% 26%

_

. 73

7 , , ( ), (.. ) . (relational algebra) . , . , : , (), - (.. READ/WRITE). , ( ). . . . .

7.1 . "" , , , : Codd,

1. , , , , ( ). 2. (restrict), (project), (join), (divide).

. 74

7.1

.

. 75

7.2 :1. (selection) C (R). () R C. . ( (restrict) (select). 2. (projection) AL(R). AL (attribute list) R. . 3. (cartesian product) R1 R2. R1 R2 () , R1 R2. 4. (set union) R1 R2 . R1(A1 ,A2 , ... An), R2(A1 ,A2 , ... An), . R1 R2 R1 R2 . 5. (difference) R1 R2 . R1(A1 ,A2 , ... An), R2(A1 ,A2 , ... An), . R1 R2 R1 R2 . . 6. (intersection) R1 R2 . R1(A1 ,A2 , ... An), R2(A1 ,A2 , ... An), . R1 R2 R1 R2 7. (join) R1 >< c R2 R1 R2 C. , , C ('=') , , (natural join) R1 >< R2 ( , , ) 8. (division) R1 R2 . R1(A1 ,A2 , ... An), R2(A1 ,A2 , ... An), T= A1, , An(R1) . R1 R2 R1 R2 . , , ( ) . . 76

7.3

7.3 -, , , , , . , . , , . . . ( ), ( ). , , . ( ), ( ), ( ). , ( ). , . (, ).

7.4 . . ( ), . , , , , , , ... , (nested expressions) , , .

. 77

7.5 : 1. 2. :

, ,

, ,

: R R(A1 ,A2 , ... An). (tuple) t R(A1 ,A2 , ... An). () t = , v, D(Ai).

7.6 - ( ) (project, unary) (select, unary) (cartesian product, binary) (rename, unary) (union, binary) (set-difference, binary)

. 78

7.7 .

, . (set-intersection) (natural join) (division) (assignment) (generalized projection) (Aggregate functions) (outer join)

, , , () , , , .

7.7 () . . (). ().

1,2,, k(R) 1, 2 R . k .

() .

. 79

.. - :

-,() 10 20 30 40 1 1 1 2

R

1 1 1 2

1 1 2

,(R)

=

SQL:SELECT DISTINCT , FROM R ( SQL () DISTINCT )

7.8 . (), ( ) (). (). :

P(r) :

P(r) = {t|tr and p(t)} () r P () : (and),

P(r)

(or), (not). 80

7.9 =,

, ,

P

(and), (or), (not) T F T F T T F T F F F T F T T T F F T F

:

-='' ()

1 5 12 23

7 7 3 10

R

1 23

7 10

=^>5 (R)

SQL:SELECT DISTINCT , FROM R WHERE = AND >5

7.9 . : r s, r n s m . r s n m .

rs. 81

:

r s = {tq | tr qs} r(R) s(S) ( RS=) r(R) s(S) , .

R , S

1 2

a r

10 10 20 10

c c b b

A R x S

B 1 1 1 1 2 2 2 2

r r

10 10 20 10 10 10 20 10

c c b b c c b b

SQL:SELECT * FROM R, S

7.10 (). (). . , , . :

(R) R . 82

7.10

SQL:SELECT * FROM R AS X R n, (1, 2, , n)(R) R , 1, 2, , n.

SQL:SELECT * FROM R AS X(1,

2, , n)

. .. (-, , ) "Smith". ( "Smith", ) 1. "Smith"

, (-="Smith" ())( SQL: SELECT , FROM WHERE -="Smith") 2.

P( (, (-="Smith" ())) P . (R) ( (, (-="Smith" (2 ()))) 3. - .=2. .=2..

.- ( .=2. .=2. ( (, (-="Smith" (2 ()))) ) ). 83

SQL:SELECT .- FROM , AS 2 WHERE 2.-="Smith" AND .=2. AND .=2.

7.11 U . ( ) . :

rs :

r s = {t | t r t s} r s (r s) : r, s () (.., 2 r s) ..

_() -() :A B 1 2 1 A B 2 3

R

S

R S :A B 1 2 1 3

. 84

7.12

SQL:(SELECT , FROM R) UNION (SELECT , FROM S)

7.12 (). () . ( : )

rs :

rs = {t|tr ts} rs r s. r s (r s) : r, s () (.., 2 r s)

A

B 1 2 1

R

A

B 2 3

S

R - S: 1 1

. 85

SQL2:(SELECT , FROM R) EXCEPT (SELECT , FROM S)

SQL1:(SELECT , FROM R) MINUS (SELECT , FROM S)

. .. (, , ) ( ) . ( )

() 3000 5000 6000

.

(). 3000 3000 3000 5000 5000 5000 6000 6000 6000 . 3000 5000 6000 3000 5000 6000 3000 5000 6000

() .