1
ACCESS
1.1
1.2
1.3
.
1. "" (Northwind).
2. MS Access.
3. "".
4. .
5. , .
6. , ( ' ').
7. Access.
8. .
. STUDY
1. STUDY.
, , , .
2. STUDY.
STUDY :
Group (ID_Group, Dekanat)
Subject (ID_Sub, Name_Sub)
Student (ID_Stud, Fam_Stud, ID_Group, Sex, Bdate, Zach_nom, Address, Phone, Photo)
Mark (ID_Stud, ID_Sub, Semestr, Mark, Data)
Group . Subject , . Student Mark . (.1.1-1.8).
(.1.1).
, , . , , , . .
: , . , , , .
1.1
: , (. 1.2).
2.1 Group Subject, 1.1 1.2 , (.1.3, 1.4).
1.1 Group
|
|
|
| ID_Group | (50) | ||
| Dekanat | (50) |
1.2 Subject
| ID_Sub | |||
| Name_Sub | (50) |
1.3 - Group 1.4 - Subject
| ||||||||||||||||||||||
| ||||||||||||||||||||||
2.2 Student Mark ( , , ).
Student Mark 1.5 1.6 .
1.5 Student
| ID_Stud | ||||
| Fam_Stud | (50) | |||
| ID_Group | (50) | ( ID_Group Group) | : - ; ; - SELECT Group.ID_Group FROM [Group]; -1; -1; - 2,54; - 2,54 | |
| Sex | (50) | (/) | : '' Or '' : - ; ; - "";"" -1; -1; - 2,54; - 2,54 | |
| Bdate | / | : 00.00.0000 : >#01.01.1900# And <Now() | ||
| Zach_nom | (50) | |||
| Address | (50) | |||
| Phone | (50) | |||
| Photo | OLE |
1.6 Mark
| ID_Stud | ( ID_Stud Student) | : - ; ; - SELECT Student.ID_Stud FROM Student; -1; -1; - 2,54; - 2,54 | ||
| ID_Sub | ( ID_Sub Subject) | : - ; ; - SELECT Subject.ID_Sub, Subject.Name_Sub FROM Subject; -1; -2; 0; 4,54; - 4,54 | ||
| Semestr | : >=1 And <=10 | |||
| Mark | : Between 0 And 100 | |||
| Data | / | : Now() |
|
|
|
(.1.2)
1.2
, , , , .
. . , , . , .
Student Mark :
) ID_Group Student ID_Group Group. :
- ID_Group (.1.3);
- , (.1.4);
- Group;
- ID_Group (.1.5);
- , , (.1.6), .
ID_Group , 1.7, Group Student, , / . .
1.3
 |
1.4
 |
1.5
1.6
1.7
) ID_Stud Mark ID_Stud Fam_Stud Student (. 1.8) ( , (.1.9), , .. ID_Stud);
 |
1.8
 |
1.9
) ID_Sub Mark ID_Sub, Name_Sub Subject (.1.10), ID_Sub Subject Mark, Name_Sub;
1.10
) Sex Student : (.1.11).
1.11
3. ( ).
, :
- / . ;
- ;
- (.1.12);
- , :
- . , , , ;
- . .
|
|
|
1.12
1.13.
1.13
:
- / . . . , , / ;
- , , 
, ;
- ( ), . . ;
- , . , , , . , ;
- , , , .
- 
. . 
.
4. Student Mark (.1.7,1.8.).
, , .
1.7 Student
| ID_Stud | Fam_Stud | ID_Group | Sex | Bdate | Zach_nom | Address | Phone | Photo |
| -06-1 | 22.05.1990 | 1 | 8-050-123-45-55 | |||||
| -06-4 | 24.04.1991 | 2 | 8-050-567-657-34 | |||||
| -06-3 | 02.07.1990 | 3 | 8-067-555-77-99 | |||||
| -06-1 | 22.05.1990 | 4 | 8-067-345-76-77 | |||||
| -06-1 | 07.08.1991 | 5 | 8-050-888-56-44 |
1.8 Mark
| ID_Stud | ID_Sub | Semestr | Data | |
| 20.05.2007 | ||||
| 27.05.2007 | ||||
| 01.06.2007 | ||||
| 22.05.2007 | ||||
| 27.05.2007 | ||||
| 01.06.2007 | ||||
| 22.05.2007 | ||||
| 27.05.2007 | ||||
| 22.05.2007 | ||||
| 27.05.2007 |
5. , :
- ( ):
o Student, ;
o Student;
o Mark , ;
o ;
- ( ):
o Student, ;
|
|
|
o ID_Stud;
o ;
- ( ):
o Student, ;
o ;
o ;
- ( ):
o Student, ;
o Mark 120;
o .
6. STUDY QBE (Query by example).
.
ccess :
- ( Query by example);
- ( SQL );
- SQL ( SQL ).
( ) / .
6.1 -.
1. , : , , , "". .
:
- . , , - 
;
- 1: , , Student , , ;
- 1: , 2- :
o , ;
o QBE (Query by example - );
- . :
o Shift Ctrl QBE. QBE , ;
o ;
o , ;
o bdate ;
o , ;
o fam_Stud Like *. . Like -;
o 1.14, SQL 1.15;
- fam_bdate . fam_bdate.
1.14
1.15
.
fam_bdate, 
. , 2 , A, .
SQL ! .
2. fam_bdate:
- , Ȓ ( Like '*');
- , : , .
3. , . , 1990 . (Like '*1990').
6.2 c
, ().
, , .
|
|
|
:
4. Student (, , ) ( ) (.1.16). , SQL (. 1.17).
1.16
1.17
5. , .
6. ( , , , ) , .
7. ( , , , ), .
6.3
, , , , QBE.
8. : , , , , (.1.18).
1.18
6.4 ( )
SQL : COUNT, SUM, AVG, MAX, MIN. SUM AVG . COUNT, MAX, MIN
9. , : (.1.19).
/ 
.
 |
1.19
-: . SQL , 1.20, 1.21.
SELECT Subject.Name_Sub, Count(Mark.Mark) AS [-]
FROM Subject INNER JOIN Mark ON Subject.ID_Sub = Mark.ID_Sub
GROUP BY Subject.Name_Sub;
1.20
 |
1.21
10. , (.1.22).
1.22
11*. CountCroupStud, .
 |
1.23
12*. , , , , , .
 |
1.24
6.5
. QBE.
, , , , .
. , .
. , , , , .
13. , , 1.25, .
1.25
, , : , , (.1.26). 1.27.
1.26
1.27
FamSubMark :
- , (.1.28);
1.28
- 1.29-1.33., (.1.32).
 |
1.29
 |
1.30
 |
1.29
 |
1.30
 |
1.33
7 .
, . , , , .
, . , . / .
, --.
7.1 Group (.1.34):
- ;
- Group;
- ;
1.34
7.2 (.1.35) ( ):
- Group;
- ;
- , Student;
- 
/ (.1.35) Group, .
;
1.35
- , 1.36.
 |
1.36
7.3 Student (.1.37):
- Student ;
- / ;
- Photo. , , , ;
- ( );
- Zach_nom;
- ( ).
1.37
7.4 (.1.38):
 |
.
1.38
8 STUDY.
, , . - . Access 55 , .
(.1.39). .
:
- ( );
- ( ).
1.39
(.1.40).
 |
1.40
9 STUDY (.1.9).
1.9 STUDY
| () | |||
| , | Group | ||
| , | Student | ||
| , | Subject | ||
| , | Mark | ||
| FamAvgMark | |||
| FamSubMark_ | |||
| MarkSubCount | |||
:
) ( ):
- / / ;
- , ;
- , ;
- : ;
) :
- / ;
- ;
- ;
- (, );
- - / , ( ), ;
- , ;
:
- ( ). , , , , ;
- ( ).
) :
: , , ( ).
10*
, Group, .
:
- Group ;
- 11 CountCroupStud , , Group (.1.41).
Forms!. , [ ] ID_Group
Forms! Group! ID_Group.
CountCroupStud .
 |
1.41
! Group, .. , .
CountOneCroupStud.
- 12 Stud_MarkMaxAvgMinGroup , , Group (.1.42). Stud_MarkMaxAvgMinOneGroup.
1.42
- GroupDopInf, CountOneCroupStud, Stud_MarkMaxAvgMinOneGroup. . : CountOneCroupStud (.1.43). : Stud_MarkMaxAvgMinOneGroup. GroupDopInf.
 |
1.43
- Group. ( ) , (1.44). GroupDopInf. . , 1.45. (.1.46).
1.44
1.45
1.46
:
