- , .. , - . , ().
(, , , ), , .
- , , , , .. - , . - SQL.
:
1)
2)
3)
4)
5)
2 .
, , , , , , .
.
, , .
, , .
, - , (), .
.
.
- . .
3 .
. , . , , .
. Y X ( ), X Y. X →Y.
|
|
|
- , , , .
. , - .
. X, Y, Z - . X → Y Y → Z, , .. Z -/-> Y Y -/-> X. Z X.
. X. Y, Z - R. R R.X - R.Y , Y. X Z. X Z.
(5). ().
4 .
, : , .
, , , n- . , .
- . ( ), - . , , : , , .
, , . . .
5 . . , σaθb(R) σaθv(R), :a, b θ {<; ≤; =; ≥; >}v R ( relation, , , / ). σaθb(R) ( σaθv(R)) R, a θ b ( a θ v) . , πa1,,an(R) a1,,an , . R, , a1,,an -.
|
|
|
, . , A , B.
. , .
6 .
.
I . .
. , , , (, , , . ).
II . .
, , . , , , , . . . .
III . .
. . , . .
IV . .
V . .
1. , .
2. .
, :
1. . . , . , . , .
2. . , . , , , .
3. . . , , , , . , . , . . (, "" "", "").
|
|
|
4. . , , , .
3. .
, . , , , . , , . , . , . . , , .
4. .
VI . .
7 .
() , . - . , . , (, ), , .
. , ER-.
:
, .
, .. .
8 .
́ ́ ́ (database integrity) , . , , (integrity constraint). : ; 25; ..
.
, , , . , . ( ) , , . , , . . ( ) , . , , .
|
|
|
9 . (Consistency). , , , .. , . , . ( , . , .. , .)
10 1- . (1NF) : . ( ). , , , . 1NF :
| , | |
| 10.10.2008 | 10% | PHP for dummies | |
| 11.10.2008 | 20% | Acer | |
| 10.10.2008 | 10% | Windows XP |
| 10.10.2008 | 10% | |
| 11.10.2008 | 20% | |
| 10.10.2008 | 10% | |
| PHP for dummies | ||
| Acer | ||
| Windows XP |
| ID | |||
| 1 | |||
| 2 | |||
| 1 | |||
| 2 | |||
| 2 | |||
|
|
|
15 -.
- (. Boyce-Codd normal form; BCNF) .
- , () .
, , , -, (. Ian Heath) 1971 , .
BCNF , [1].
, - , .
BCNF .
R , X Y R. Y X , R, R X, Y. X , Y .
, () ().
, 3NF, BCNF, , , , ( ) , . , 3NF BCNF .
16 . ́ (. transaction) , . , , . , . (), . (, two-phase commit ). , , -, .
, .
1 (atomicity). , , .
2 , (consistency). , .. .
3 (isolation). , .. .
4 , , (durability). , , .
17 .
, , , , ; , - ; , , ; .. , . ( ) , . , . , .
, ( , ) :
.
: INSERT DELETE, DELETE INSERT, UPDATE
UPDATE, .
. , , , .
. .
18 . , . : A B, , A B. T t, T. r T ( ). r B : , A, . A , B r . A , B . , r ( ), B . B A , t(A) > t(B) (.. A "", B), A , , . B . t(A) < t(B) (A "" B), B , , . A . , ( ). , , . , . , . 19 .
, , .
, , - ( , ) , . . , , ( ) , . - .
, , .
20 .
? :
- .
- ( "" , ).
- .
.
, A B, . , . 
, 
- . 
.
21 .
, . , . :
- .
- .
- .
- ( , 2-16 . ).
- .
- .
, , .
, . , . , , , .
, , . ( Paradox .).
22 .
, , . . , , , , .
:
- . ROLLBACK, . - (, ) .
- ( ). . , . , , . , , .
- ( ). . , , .
, .
23 .
, .
, , . , - .
, ( ) , "" (dirty) . "" - dirty-. "" . , , :
- . . , , , , , .
- , ( ), , , .. . - - , .
, - .
24 SQL.
, , . 4 :
SELECT ()
INSERT ()
UPDATE ()
DELETE ()
- , , ( ), . :
CREATE DATABASE ( )
CREATE TABLE ( )
CREATE VIEW ( )
CREATE INDEX ( )
CREATE TRIGGER ( )
CREATE PROCEDURE ( )
ALTER DATABASE ( )
ALTER TABLE ( )
ALTER VIEW ( )
ALTER INDEX ( )
ALTER TRIGGER ( )
ALTER PROCEDURE ( )
DROP DATABASE ( )
DROP TABLE ( )
DROP VIEW ( )
DROP INDEX ( )
DROP TRIGGER ( )
DROP PROCEDURE ( )
25 SQL. , .
,
| SELECT rkz.nazv, rkz.litr, ts.mag, ts.tsena FROM ts,rkz WHERE rkz.nazv=ts.nazv |
:
rkz.nazv, rkz.litr - nazv litr rkz;
ts.mag, ts.tsena - mag tsena ts;
WHERE rkz.nazv=ts.nazv - - nazv .
26 SQL. . . .
- , , . .
| CREATE VIEW: |
| CREATE VIEW Londonstaff |
| AS SELECT * |
| FROM Salespeople |
| WHERE city = 'London'; |
. , . :
| CREATE PROCEDURE _ () |
| begin |
| end |
[ 259 ]. - SQL (Oracle, Microsoft SQL Server) .
PL/SQL : . , .
, . , , . , .
PL/SQL, .
27 .
, , .
1. (Oracle ..). . , , . ..
2. , . . InterBase, SQL-server , .. , . , , .
3. Desktop . : , . , . MS Access , , . . . .
28 .
( ), . , .
:
-;
, , ;
.
:
;
;
.
, - . :
( RAID-);
(, MS SQL Server).
, . .
29 .
: . , , , .
. : ().
, , . , , , , .
. . .
, .
. , . - , .
. (, MS SQL Server, Sybase).
SQL : GRANT REVOKE .
30 . .
- , .
. , , . . . , , , ( ) .
.
.
- .
, , .
- .
, , , . .
- .
, , , .
. - (DML). , .
SQL Server , :
- AFTER. . - , . , : , . AFTER- (INSERT, UPDATE, DELETE). AFTER-, sp_settriggerorder , , . SQL Server AFTER-.
- INSTEAD OF. . AFTER- INSTEAD OF- , . INSERT, UPDATE, DELETE INSTEAD OF-.
, .
:
- INSERT TRIGGER INSERT.
- UPDATE TRIGGER UPDATE.
- DELETE TRIGGER DELETE.
