??????????? ???????????? ?????????? ?? ?????

1
??????????? ???????????? ?????????? ?? ?????
?????????????? ?????????????? ?????????????
??????

• ????? ???????? ?.?.

2
?????? 1. ???? 3

• ????????? ????????? ??? ????????? ????????????
  ??????????, ????????? ?????????, ????-???????.

3

• ????????? ? ______??_________200_?
• ????????? __________________
  ???_______________
• 
  ????????????
• ?????????? _________________
  ???________________
• ????????????
• ????? ___________________
  
• ????????????
• ????????-????????? ________________
  ?? ____________200_?.
• 
  ????? ?????????
• 
  ????
  ??????____________

???????????? ?????? ??. ????????? ?????? ??? ???? ?? ??. (???) ?????????? ??? ??? ??? ????? ? ???



4

• ????-???????
• (???????? ??????????? ?? ????????? ?????????
  ????????? ?????????)
• ????-??????? ? __________________ ?? _________?
  ????????? _______________
• ????????? _____________________
  ?????????? ____________________
• ????? ________________________
  ????? ________________________
• ????????? ???? ? _______________
  ????????? ???? ? _______________
• ? _____________________________
  ? _____________________________
• ??? __________ ???.???? __________
  ??? __________ ???.???? __________
• ????? ____________ ??? ___________
  ????? ____________ ??? __________
• ????????????????_________
  ???????????????_________
• (?????)
• ????? ___________________________
  ????? _________________________

???????????? ?????? ??? ?????? ??. ????????? ?????? ??? ???? ?? ??. (???) ?????????? ??? ??? ??? ????? ? ???



5
?????? 2. ???? 1

• ????? ????????? ????????????? ???

???
?????????? 1
?????????? 2
?????????? k
?????????? 1
?????????? 2
?????????? 3
?????????? 4
?????????? 5
?????????? 6
?????????? 7
?????????? n
??
6
???????? ??????????? ERP-???????, ??????????????
?? ?????????? ?????
7
???????? ?????????? ERP-???????, ??????????????
?? ?????
8
?????????????-??????????? ????? ???????
?????????? ?????
9
?????? 3 ???? 1.

• ?????? ?????????? ????? ?? ? ????????? ISO/IES
  15288

10
?????? 3 ???? 2.

• ?????? ??????? ???????

????????????? ?????????? ?????????
?????????? ???????????
?????????? ????????????? ????????
???????????? ????????? ????????
?????? ??????? ???????
????????????? ???????? ???????????????? ?????????
?????????? ????????? ????????
????????????? ?????????? ??????????? ?????????
???????
11

• ??????????? ????????? ??? ????????? ?????????
  ?????????????
• ????? ???????
  ??????-?????????
• ?????????????

?????????? ?????? ?? ??????? ??????
12
????????? ????????????? ??? ?????????? ???????
??????? ?? ??????

???????????? ??????? ??????
????? ?????? ?? ??????
?????????? ?????? ?? ??????? ??????
?????????? ?????? ?? ??????? ????????????
?????????????? ???????? ???????
13
????????? ??????? ??? ?????????? ?????? ??
????????? ??????????

14
?????? 4. ????1.

• ?????? ?????? ?????? ??? ??

15
????????????? ??????????? ??
16
????? ????????????? ????????? ?? ??????????? ?.
?????
????????
????????
?????
??????????????
???????????
??????????
17
??????????? ????????? ER-????????
- ???????,
- ???????? ??? ????????? ????????
- ??????????
- ??????????
- ??????????????
- ???????????
18
??????? ER-????????
?
1
????????
????????
?????
M
N
????????
?????
???????
19
ER-????????? ??? ??????? ??
20
????????? ????? ??????????????? ?????????????
(???)

• ???????? E (A1, A2,AN)
• ?????????? R E1S1, E2S2,, EMSM (A1, A2,,
  AK)
• ?????????????? C (A1, A2,) E1 E2
• ??????????? N (A1, A2,) E1 E2
• ?????
• E, E1, E2,, EM ????? ????? ?????????
• R ??? ??????????
• C, N ?????????????? ????? ?????????????? ???
  ???????????
• E1, E2, - ????? ??????? ?????????
• A1, A2 - ???????? ???????? ??? ?????
• S1, S2, , SM ??????? ?????.
• ????????, ???????? ? ????, ??????? ???????? ???
  ????????????.

21
???????? ?? ??? ?????????????? ?????? ??????? ??

• ???????.
• ????? (???_??, ????_??, ???)
• ??????? (???_??, ????_??, ?????)
• ????? (????_??????, ??????)
• ?????.
• ????????? (???_???, ???, ????_??????) ?????
• ?????? ????? N ,??????? M(???_??, ???_??,
  ???(?))
• ???????? ????????? N, ??????? M(???_???,
  ???_??, ???(??), ????, ????_?)
• ?????? (???_??, ??????_??) ?????
• ?????????? (???_??, ?????, ????_?) ?????

22
?????? 4. ???? 2.

• ???????????? ??????????? ?????? ??? ??????? ??

23
?????? 4. ???? 2.????????????? ????????? ???????
??
24
?????? 4. ???? 3.???????????? 1 ??? ?? ???????
??
25

• ????????? ???????????? ??????????? ???????
• ????????????? ??, ?????????? ????????? ?????,
  ??????? PK.
• ???? ???? ?? ????? ??????????? ??????
• ???????????? ????? ??????? ?? ???????
  2
• ???? ???? ?? ?? ????? ?????????? ?????
• ???????????? ????? ??????? ?? ???????
  1
• ???? ???? ??????????? ????????????
• ??? ???????, ?????????? ?? ????????
  ????? ???????? ????????? ????????? ??????????
  ???????? ?? ?? ????????????? ?, ???? ?????, ???
  ?????????????.
• ??? ?????? ???????? ? ????????????
  ??????? ????????? ?????? 2 4.
• ???? ?????, ??? ?????? ?????????? ???????
• ?????? ???????? ??? PK.

26

• ????????? ?????????????? ???.
• ??????????? ?????? ???????? ? ???? ???????
  ???????, ?????? ??? ??????? ??? ???????? ???????,
  ???? ????? ??????? ? ???????? (?????? ???
  ????????? ? ??? ?????????? ??? ??????????????
  ?????????????? ??????!). ??????????????? PK ???
  ??????? ???????.
• ??????????? ?????? ?????????? ? ??????????????
  ??????? ???????? ??, ?????? ??? ??????? ??
  ???????? ???????, ???? ????? ??? ?????? ???????,
  ? ?????
• - ??????????????? ??????? ?????
  (FK) ??? PK ??????????????? ??????? ?????????
• - ??????????????? ??????????? ??
  ?????? FK (??? ???????????)
• - ??????????????? PK ??? FK ?,
  ????? ????, ??? ????????? ??????????????
  ????????.
• ??????????? ?????? ??????????? ??? ???????
  ???????, ?????? ??? ??????? ?? ???????? ???????,
  ???? ?????, ? ?????
• - ??????????????? ??????? ???? (FK)
  ??? PK ??????? ????????
• - ??????????????? ??????????? ??
  FK
• - ??????????????? PK.
• ????????? ??? ??????? ?????? ?????????
  ???????????? ?? ????.
• ???? ? ???????? ???????????? ???? ?????????
  ???????????? ?????-???? ???????, ?? ???????
• - ??????????????? ???????
  ?????????????? ?????????????? ??????
• - ??? ????? ??? ??????????
  ????????? ????????? ?????? 1) - 5).
• ??????? ??? ??????????? ??????????? ???
  ????????????? ??, ? ??? ????? ?????? ???????????
  ???????????, ???????????? ??????????????? ???
  ?????????? ?????????, ????????, ??????????
  ???????? ?????, ????????? (???????? ???????????),
  ???????????? ????????, ??????????? NULL-????????
  ? ?. ?.
• ???? ??????? ???????? ?????????? ?????? ? ??
  ?????. ???? ????? ?? ???????? ????????????, ??
  ?????? ????????? ? ???????, ??? ???????????.

27

• ???????? ?????? ??????? ??? (?????? 27, 28, 29)
• ??????? ??????? ?????
  --????????
• ???? (???_?? ?????,
• ????_??
  ?????(40) NULL-???????? ?? ?????????,
• ???
  ?????(15) NULL-???????? ?? ?????????)
• ????????? ???? (???_??)
• ??????????? (1.???????? ???_?? ??????
  ???? ?????????, NULL-???????? ?? ?????????.
  2.???????? ???? ??? ?????? ????????????
  ?????????? ?????????????,???,???????,??????,
  ???????)
• ??????? ??????? ???????
  --????????
• ???? (???_?? ?????,
• ????_??
  ?????(20) NULL-???????? ?? ?????????,
• ????? ?????
  NULL-???????? ?? ?????????)
• ????????? ???? (???_??)
• ??????????? (1.???????? ???_?? ??????
  ???? ?????????, NULL-???????? ?? ????????? )
• ??????? ??????? ?????
  --????????
• ???? (????_??????
  ?????(25) ,
• ??????
  ?????(15) NULL-???????? ?? ?????????)
• ????????? ???? (????_??????)
• ??????????? (1.???????? ????_?????? ??????
  ???? ?????????, NULL-???????? ?? ?????????.)

28

• ??????? ??????? ?????????
  --??????????? ?????
• ???? (???_??? ?????,
• ???
  ?????(20) NULL-???????? ?? ?????????,
• ????_??????
  ?????(25))
• ????????? ???? (???_???)
• ??????? ???? (????_?????? ?? ?????,
• 
  NULL-???????? ?? ?????????,
• On DELETE
  ?? ????? RESTRICT,
• On UPDATE
  ?????. ????_?????? CASCADE)
• ??????????? (1.???????? ????
  ????_?????? ?????? ???????????? ?????????
  ???????? ?? ???????????????? ???? ??????? ?????.)
• ??????? ??????? ???????? -- ??????????-?????
  ????? ????????? ? ???????
• ???? (???_??? ?????,
• ???_??
  ????? ,
• ???(??)
  ????? NULL-???????? ?? ?????????,
• ????
  ???????? NULL-???????? ?? ?????????,
• ????_?
  ????)
• ????????? ???? (???_???, ???_??, ????_? )
• ??????? ???? (???_??? ?? ?????????,

29

• ??????? ??????? ?????????? --
  ??????????? ?????
• ???? (???_?? ?????,
• ?????
  ????? NULL-???????? ?? ?????????,
• ????_?
  ???? )
• ????????? ???? (???_??, ????_?)
• ??????? ???? (???_?? ?? ?????,
• 
  NULL-???????? ?? ?????????,
• On DELETE
  ?? ????? RESTRICT,
• On UPDATE
  ?????.???_?? CASCADE)
• ??????????? (1. ???????? ????? ???_??
  ?????? ???????????? ????????? ???????? ??
  ???????????????? ???? ??????? ?????. 2. ????????
  ???? ????? ?????? ???? ?????? ????)
• ??????? ??????? ?????? --
  ?????????????? ?????
• ???? (???_?? ?????,
• ??????_??
  ?????(255) NULL-???????? ?? ?????????)
• ????????? ???? (???_??)
• ??????? ???? (???_?? ?? ?????,
• 
  NULL-???????? ?? ?????????,
• On DELETE
  ?? ????? CASCADE,
• On UPDATE
  ?????.???_?? CASCADE)

30
?????? 4. ???? 4????????? ????? SQL
31
???? ?????? SQL
???? ?????? SQL
C????????? ??????
?????
?????????
- Char(n) - Varchar(n)
- Bit(n) - Bit varying(n) - Number(n,m) -
Decimal(n,m) - Integer - Smallint - Float(p) -
Real(s) - Double presicion(p)

• Date (year, month, day)
• Time (hour, min, sec)
• Interval
• Timestamp

32
??????? ? ??????? ?? (?????? 32 - 35)

• 1). SELECT COUNT ()
  2). SELECT COUNT
  (DISTINCT ?????)
• FROM ????????
  FROM ?????????
  
• 3).SELECT SUM(???(??))
  4).SELECT
  SUM(???(??)????) AS ????? ?????????
• FROM ????????
  FROM ????????
• 5). SELECT MAX(???(??)), MIN(???(??))
  6). SELECT ????_??, ?????
• FROM ????????
  FROM
  ???????
• 7). SELECT DISTINCT ???_??
  8). SELECT ???_??, ????_?,
  ???(??)????
• FROM ??????
  FROM
  ????????
• 9). SELECT ????_??, ?????
  10). SELECT SUM(???(??))
• FROM ???????
  FROM
  ????????
• WHERE ?????lt1000
  WHERE ???_??5
• 11). SELECT ???_???, ???_??, ????_?, ???(??)????
  12). SELECT ???_???, ????_? , ???(??)
• FROM ????????
  FROM
  ????????
• WHERE (???(??)gt100) and (????_? LIKE
  .10.07) ORDER BY ???_???, ????_?
  DESC
• 13). SELECT ???_??, ???(??), ???(??)????
• FROM ????????
• WHERE ???(??)gt200
• ORDER BY 3
• 14) SELECT ???_??, ????_?
  14) SELECT ???_??, ????_?

33
??????? ? ??????? ??

• 16). SELECT ???_??, SUM(???(?))
  17). SELECT ???_???,
  ?VG(???(??))
• FROM ??????
  FROM
  ????????
• GROUP BY ???_??
  GROUP BY
  ???_???
• 
  
  HAVING SUM(???(??))gt500
• 18).SELECT ???, ????_??????
  19). SELECT ???, ????_??????
  
• FROM ?????????
  FROM
  ?????????
• WHERE ????_?????? ltgt ??????
  WHERE ????_??? IS NULL
• 20).SELECT ????_??, ?????
  21). SELECT ????_??, ???
• FROM ???????
  FROM
  ?????
• WHERE ????? BETWEEN 100 AND 1000
  WHERE ??? IN (??????,
  ???????)
• 22). SELECT
  23). SELECT
  ????_??, ???
• FROM ??????????
  FROM ?????
• WHERE ?????gt100 AND ????_? LIKE .10.07
  WHERE ??? NOT IN (???,
  ???????)
• 24). SELECT ??.????_??, ??.?????, ?.????_?
• FROM ???????? AS ? INNER JOIN ??????? AS
  ?? ON ?.???_?? ??.???_??
• WHERE ??.????? gt 1000
• 25). SELECT ?.???_??, ?.???_???, ?.???(??),
  ?.????_?
• FROM ???????? AS ?, ?????????? AS ?
• WHERE ?.????_??.????_? AND ?.???_??3
  AND ?.?????gt50

34
??????? ? ??????? ??

• 27).SELECT ??.????_??, ??.????_??, ?.???_???,
  ????_?
• FROM ???????? AS ? INNER JOIN (???????
  AS ?? INNER JOIN
• (?????? AS ? INNER JOIN ????? AS ?? ON
  ?.???_????.???_??)
• ON ??.???_???.???_?? ?) ON
  ?.???_????.???_??
• WHERE ??.??????????
• 28). SELECT ?.???_??, ??.????_??, ?.???(??),
  ?.????_?
• FROM (??????? AS ?? LEFT JOIN ????????
  AS ? ON
• ??.???_?? ?.???_??) INNER
  JOIN ?????? AS ? ON ?.???_?? ??.???_??
• 29). SELECT ??.????_??, ?.????_?, ??.????_??
• FROM (??????? AS ?? INNER JOIN ????????
  AS ? ON
• ??.???_?? ?.???_??) LEFT
  JOIN ( ????? AS ?? RIGHT JOIN ?????? AS ?
• ON ??.???_?? ?.???_??) ON
  ??.???_?? ?.???_??
• 30). SELECT ????_??
  31). SELECT ???_??
• FROM ???????
  FROM
  ??????????
• WHERE ?????gt( SELECT ?????
  WHERE ?????gt( SELECT
  AVG(?????)
• FROM
  ???????
  FROM ?????????? )
• WHERE
  ????_????????)
• 32). SELECT ???
• FROM ?????????

35
??????? ? ??????? ??

• 34).SELECT ???_???, ???_??, ????, ????_?
• FROM ????????
• WHERE ???? lt ALL( SELECT ????
• FROM
  ????????
• WHERE
  ????_? CURRENTDATE)
• 35). SELECT ???, ????_??????
• FROM ????????? AS ???
• WHERE NOT EXISTS ( SELECT
• 
  FROM ???????? AS ?
• 
  WHERE (???.???_??? ?.???_???) AND
• 
  ( ????_? LIKE .09.) AND
• 
  (???_?? (SELECT ???_??
• 
  FROM ???????
• 
  WHERE ????_??
  ????)) )
• 36). SELECT DISTINCT ???
• FROM ????????? AS ???
• WHERE NOT EXISTS (SELECT
• 
  FROM ??????? AS ??
• 
  WHERE NOT EXISTS (SELECT

36
?????? 5. ???? 1.

• ?????? ???????? VBA
• ??????????(Application), - ??? ??????,
  ??????????? ?? ??????? ?????? ???????? ????????.
  ?? ??????? ?? ????????? ?????????
• ????? (Forms) - ??? ????????? ????????????
  (Form)
• ???????(Queries) - ????????? ????????????????
  (Query)
• ?????? (Reports) - ????????? ???????????????
  (Report)
• ???????? ?????????? ????????? (Controls) -
  ????????? ???????? ????????? ??????????
  (Control)
• ???????? ???????? (Properties) - ?????????
  ???????? ????????? (?????) ???????? (Property)
• ?????? (References), - ??? ????????? ?????? ??
  ?????????? ????? ?????? ??????????, ?????????????
  ? ??????? ?????? ??? ????????? ??????????.)
• ??????(Modules), - ????????? ????????
  ???????, ?. ?. ???????? ?? VBA (Module).
• ???????????? (Docmd), - ??? ???? ??????,
  ??????????????? ??? ?????????? ???????????.
• ?????? Screen, - ???? ??????, ???????
  ???????????? ??? ?????? ?? ??????? ????????
  ?????, ????? ??? ??????? ??????????.

37
?????? 5. ???? 1.

• ?????? ??????? ? ???????? VBA

38
?????? 5. ???? 1.??????? ??????? VBA
39
?????? 5. ???? 1.?????? 1 ????????? ?? VBA

• Private Sub ?????_??_??????????_Click()
• ?????????? ????????? ??????????
• Dim MyDb As DataBase
• Dim My?????????? As DAO.RecordSet
• ?????????? ?????????? ??? ????? ????????,
  ???????? ? ?? (???_?? ? ????_?)
• Dim My???_?? As Integer
• Dim My????_? As Date
• ?????? ? ????????? ?????????? ?????? ?? ???????
  ?? ? ??????? ?????????? ??????? VBA
• Set MyDb CurrentDb()
• ???????? ?????? ??????? ??? ??????? ??????????
  ? ?????? ?????? ?? ???? ? ?????????
• ??????????. DbOpenTable - ?????????? ?????????,
  ???????? ??? ???????????? ??????
• Set My?????????? MyDb. OpenRecordSet(???????
  ???, dbOpenTable )
• ?????????? ???????? Index ?????? ???????
  ?????????? ???????? - ????????? ???? ???????.
• ???? ?????? ????? ??????????? ??? ??????
  ??????? Seek
• My??????????. Index PrimaryKey
• ???? ?????????? ?????? (???????? ??????????
  My???_?? ? My????_?) ????? ?????????? ???? ?
• ?????????????? ??????? InputBox.
• My???_?? InputBox (??????? ??? ?????, ????
  ?????????? ??????)
• My????_? InputBox (??????? ????, ????
  ?????????? ??????)

40
?????? 5. ???? 1.?????? 2 ????????? ?? VBA
(?????? 40, 41)

• ?????? ???????? (????????? ??????)
• Dim kollobio As Integer
  ??????? ??? ?????
• Dim kolHARCHO As Integer ??????? ???
  ?????
• Dim kolSHASHLIK As Integer ??????? ???
  ???????
• Dim kolKOFE As Integer ???????
  ??? ????
• Dim firstupdate As Integer
• Dim KonDt, Nachdt As Date ????????
  ? ????????? ???? ???????
• ????????? ????????? ???????
• ????????? ????????? ??????? ??????
  ?????_?_???????
• Private Sub ?????_?_???????_Click()
• Dim otvet As String
• On Error GoTo Err_?????_?_???????_Click
• ????? ??????????? ???? ? ????????? ????????????
  ? ???????? ?? ? Cancel
• otvet MsgBox("?? " Nachdt " ?? " KonDt
  " "
• "????? ?????????? ?????? ?????"
  kollobio
• " ????? ?????????? ??????
  ?????" kolHARCHO

41
?????? 5. ???? 1.?????? 2 ????????? ?? VBA

• ????????? ????????? ??????? ????? ??????????
  ???? ????_?
• Private Sub ????_?_AfterUpdate()
• If firstupdate 0 Then
• firstupdate 1
• KonDt ????_?
• Nachdt ????_?
• End If
• If ????_? lt Nachdt Then
• Nachdt ????_?
• End If
• If ????_? gt KonDt Then
• KonDt ????_?
• End If
• End Sub
• ????????? ????????? ??????? ????? ??????????
  ???? ?????
• Private Sub ?????_AfterUpdate()
• Select Case ???_??
• Case 1

42
?????? 6. ???? 1.

• ??????????? ?????? ??????

?????? ??
??
???? ????
SQL-??????
?????????? ???????
?? ???????
?? ???????
?????????? ???????????????? ?-??? ????
?????????? ???????????????? ?-??? ????
43
?????? 6. ???? 1.??????????? ?????
?????????????? ???????
???? (???? 1)
???? (???? 2)
???????????????? ????
???? (???? 3)
???? (???? 4)
44
?????? 6. ???? 2.??????????-?????????????? ?????
?????????
45
?????? 6. ???? 2.??????????? ?????? ??????
(?????? 45, 46)
46
?????? 6. ???? 2.??????????? ?????? ??????
???????
??????????
?????
??????
??????? ??????? ???
???????
? ? ? ? ? ? ? ?
14060 14000 23700
12260 26120 22100 30150
20120 20200 20100
20250 18200 60386 56387
60500 50325
??????? 1 ??????? 2 ??????? 3 ??????? 4

?????
47
?????? 6. ???? 3.OLAP-?????????? ? OLAP
???????????(?????? 47, 48, 49)

• ????? ???????????????? MOLAP -????????????

??????????? ??????? ???????? ?????????????

?????? MOLAP
??????
??????
???????????? ??
?????????? ???????
????????
????????? ??????
48
?????? 6. ???? 3.

• ????? ???????????????? ROLAP- ????????????

??????????? ??????? ????????? ????????????
SQL-??????
ROLAP- ??????
?????? ???
?????? ??????
????????????? ??????????? ???????????
?????????
??
??????????? ????????????? ??????
????????????? ??????????
49
?????? 6. ???? 3.

• ????? ???????????????? MQE- ????????????

??????????? ??????? ????????? ????????????

SQL-??????
?????? ??? (?????)
??????????
??
???
50
?????? 6. ???? 3.

• ?????????? ????? SQL ??? ????????? OLAP-
  ?????????? (MQE)
• ?????????????? ????? ??? ????????? (???????????)
• 1. GROUPING SETS (?????? ???????? ???
  ???????????) - ????????? ? ????? ???????
  ???????? ???????? ?????????? ?? ??????????
  ?????????????? ??????????.
• ??????. SELECT ?????, ????_?, SUM(?????) AS ?????
  ???-??
• FROM ??????????
• GROUP BY GROUPING SETS ((?????),
  (????_?))
• 2. ROLLUP (??????? ?????????) - ?????????
  ??????????? ?????? ?????????????? ?????????.
• ??????. SELECT ?????, ????_?, SUM(?????) AS ?????
  ???-??
• FROM ??????????
• GROUP BY ROLLUP (?????, ????_?)
• ??? ????? ???????????? ??????????? GROUP BY
  GROUPING SETS ((?????, ????_?), (?????), ())
• 3. CUBE (??????? ?????????) ????????? ????????
  ??? ???????? ??????????.
• ??????. SELECT ?????, ????_?, SUM(?????) AS ?????
  ???-??
• FROM ??????????
• GROUP BY CUBE (?????, ????_?)
• ??? ????? ???????????? ??????????? GROUP BY
  GROUP BY GROUPING SETS ((?????, ????_?), (?????),
  (????_?), ())

51
?????? 6. ???? 4.

• ?? ?????????? ?????????? ????

??????
??????????????
?????????? ???????
???????????? ??????????
???????????? ?? ?? - ??????
????? ??????
???????????????? ?????????
??
?????????? ????
52
?????? 6. ???? 5.????? ????????????????
CGI-??????????