1 of 149

Базы данных�(MS Access)

1

2 of 149

Базы данных �(MS Access)

§ 13. Введение

2

3 of 149

Что такое информационная система?

3

Информационная система (ИС) в широком смысле — это аппаратные и программные средства, предназначенные для того, чтобы своевременно обеспечить пользователей нужной информацией.

Задачи:

хранение данных
доступ к данным

Базы данных, 11 класс (Access)

4 of 149

БД и СУБД

4

База данных (БД) — это специальным образом организованная совокупность данных о некоторой предметной области, хранящаяся во внешней памяти компьютера.

Система управления базой данных (СУБД) — это программные средства, которые позволяют выполнять все необходимые операции с базой данных.

БД + СУБД = информационная система

Базы данных, 11 класс (Access)

5 of 149

БД и СУБД

5

поиск данных
редактирование данных
выполнение несложных расчетов
обеспечение целостности (корректности, непротиворечивости) данных
восстановление данных после сбоев

Задачи СУБД:

БД

СУБД

прикладная программа

Базы данных, 11 класс (Access)

6 of 149

Классификация ИС

6

ИС

локальные

удалённые

БД и СУБД на компьютере �пользователя

БД на удалённом компьютере �(в сети)

автономность

нужно обновлять БД на каждом компьютере
как учесть изменения, внесённые пользователями

все работают с одной БД

зависимость от сети

Базы данных, 11 класс (Access)

7 of 149

Как хранить данные?

7

Текстовый формат:

Собственный формат программы:

Универсальный формат:

БД:

метаданные	данные

«данные о данных»

Иванов;Иван;Петрович;1968;«Audi»;20 000 р.

Петров;Пётр;Иванович;1975;«Лада»;30 000 р.

Сидоров;Семён;Иванович;1975;«Нива»;15 000 р.

Что плохо?

?

нет переносимости (БД можно использовать только с этой программой)

Переносимость – это возможность использовать БД в других информационных системах.

Базы данных, 11 класс (Access)

8 of 149

Файл-серверные СУБД

8

сервер

рабочие станции

БД

СУБД

рабочие станции должны быть мощными
высокая нагрузка на сеть
слабая защита данных
ненадежность при большом количестве пользователей

Решение – перенести СУБД на сервер!

!

Базы данных, 11 класс (Access)

9 of 149

Клиент-серверные СУБД

9

сервер

рабочие станции (клиенты)

БД

СУБД

прикладная

программа

прикладная

программа

запрос

ответ

Задачи клиента:

Задачи сервера:

отправить серверу запрос на языке SQL
принять ответ сервера
вывести результаты

при получении запроса поставить его в очередь
выполнить запрос
отправить ответ клиенту

SQL = Structured Query Language – язык структурных запросов для управления данными

Базы данных, 11 класс (Access)

10 of 149

Клиент-серверные СУБД

10

обработка данных на сервере
рабочие станции могут быть маломощными
проще модернизация
надежная защита данных (на сервере)
снижается нагрузка на сеть
надежная работа при большом количестве пользователей

нужен мощный сервер
высокая стоимость коммерческих СУБД

MS SQL Server

Firebird (www.firebirdsql.org)

PostgreSQL (wwwwww.www.postgresqlwww.postgresql.www.postgresql.org)

MySQL (wwwwww.www.mysqlwww.mysql.www.mysql.com)

бесплатно!

Базы данных, 11 класс (Access)

11 of 149

Транзакции

11

Транзакция – это группа операций, которая представляет собой одно законченное действие. Транзакция должна быть выполнена целиком или не выполнена вообще.

Задача: перевести 100 000 рублей � со счета 12345 на счет 54321.

прочитать сумму на счету 12345
уменьшить ее на 100 000 рублей
прочитать сумму на счету 54321
увеличить ее на 100 000 рублей

сбой питания!

журналирование
создание новых записей (помечаются как действительные после завершения транзакции)

Базы данных, 11 класс (Access)

12 of 149

Таблицы

12

Фамилия	Имя	Адрес	Телефон
Иванов	Петр	Суворовский пр., д. 32, кв. 11	275-75-75
Петров	Василий	Кутузовский пр., д. 12, кв. 20	276-76-76
Васильев	Иван	Нахимовский пр., д. 23, кв. 33	277-77-77

Примеры:

записная книжка
каталог в библиотеке

Иванов Пётр�Суворовский пр., д. 32, кв. 11

275-75-75

поля

записи

целые числа
вещественные числа
денежные суммы
логические значения
текстовые данные

время, дата
произвольные двоичные данные (рисунки, звук, видео)

Типы полей:

Базы данных, 11 класс (Access)

13 of 149

Ключ

13

Ключ – это поле или комбинация полей, однозначно определяющие запись.

Могут ли эти данные быть ключом?

фамилия
имя
номер паспорта
номер дома
регистрационный номер автомобиля
город проживания
адрес электронной почты
дата выполнения работы
марка стиральной машины

?

Может ли быть несколько ключей в таблице?

?

Базы данных, 11 класс (Access)

14 of 149

Ключ

14

Первичный ключ – это ключ, выбранный в качестве основного.

Простой ключ – состоит из одного поля.

Составной ключ – состоит из нескольких полей.

Дата	Время	Температура	Влажность	Скорость ветра
21.07.2012	12:00	25	75	4
21.07.2012	15:00	23	70	3
…	…	…	…	…

Составной ключ ← Дата + Время

Какой ключ?

?

Свойства ключа:

уникальность
несократимость (Дата + Время + Влажность)

Базы данных, 11 класс (Access)

15 of 149

Суррогатный ключ

15

Номер	Фамилия	Имя	Адрес	Телефон
1	Иванов	Петр	Суворовский пр., д. 32, кв. 11	275-75-75
2	Петров	Василий	Кутузовский пр., д. 12, кв. 20	276-76-76
3	Васильев	Иван	Нахимовский пр., д. 23, кв. 33	277-77-77

Какой ключ?

?

Суррогатный ключ – это дополнительное поле, которое служит первичным ключом.

операции с числами выполняются быстрее
уникальность поддерживается СУБД (поле-счётчик)

Базы данных, 11 класс (Access)

16 of 149

Поиск в БД

16

Линейный поиск – это перебор всех записей до тех пор, пока не будет найдена нужная.

Код	Фамилия
1	Сидоров
2	Ветров
…
1024	Померанцев

Иванов?

1024 сравнения!

данные не надо предварительно готовить

низкая скорость поиска

Базы данных, 11 класс (Access)

17 of 149

Двоичный поиск

17

Разделить область поиска на две равные части.
Определить, в какой половине находится нужный объект.
Перейти к шагу 1 для этой половины.
Повторять шаги 1-3 пока объект не будет «пойман».

Базы данных, 11 класс (Access)

18 of 149

Двоичный поиск в БД

18

1	Андреев
2	Барсуков
…
512	Ковалев
…
1023	Юрьев
1024	Яшин

требует предварительной сортировки.

Иванов?

1	Андреев
…
255	Журов
…
512	Ковалев
…
1024	Яшин

…
255	Журов
…
383	Игнатьев
…
512	Ковалев
…

Сколько сравнений?

?

записи надо отсортировать по нужному полю
можно использовать только для одного поля

быстрый поиск

11 сравнений!

Базы данных, 11 класс (Access)

19 of 149

Индексный поиск в БД

19

Индекс – это вспомогательная таблица, которая служит для ускорения поиска в основной таблице.

Фамилия	Номера записей
Васильев	3
Иванов	1
Петров	2

Номер	Фамилия	Имя	Адрес	Телефон
1	Иванов	Петр	Суворовский пр., д. 32, кв. 11	275-75-75
2	Петров	Василий	Кутузовский пр., д. 12, кв. 20	276-76-76
3	Васильев	Иван	Нахимовский пр., д. 23, кв. 33	277-77-77

отсортированы!

индексы занимают место на диске
при изменении таблицы надо перестраивать все индексы (в СУБД – автоматически)

двоичный поиск по всем столбцам, для которых построены индексы

Базы данных, 11 класс (Access)

20 of 149

Целостность БД

20

Целостность базы данных означает, что она содержит полную и непротиворечивую информацию и удовлетворяет всем заданным ограничениям.

Физическая целостность – сохранность данных в случае отказа оборудования:

транзакции
резервное копирование
RAID-массивы жёстких дисков (дублирование)

Логическая целостность – непротиворечивость:

типы полей
обязательные поля
уникальные индексы
ограничения на значения
шаблоны ввода (###) ###-##-##
условия на несколько полей (увольнение позже приёма)

Базы данных, 11 класс (Access)

21 of 149

Задача

21

Постройте индексы по всем полям:

Номер	Дата	Заказ	Товар	Количество, т
1	12.09.13	12	Ананасы	12
2	12.09.13	13	Апельсины	12
3	13.09.13	14	Ананасы	15
4	13.09.13	14	Бананы	13
5	13.09.13	15	Апельсины	11

Базы данных, 11 класс (Access)

22 of 149

Базы данных �(MS Access)

§ 14. Многотабличные базы данных

22

23 of 149

Однотабличная БД

23

Альбомы
Код	Название	Группа	Год	Число композиций
1	Реки и мосты	Машина времени	1987	16
2	В круге света	Машина времени	1988	11
3	Группа крови	Кино	1988	11
4	Последний герой	Кино	1989	10

дублирование данных
при изменении каких-то данных, возможно, придется менять несколько записей
нет защиты от ошибок ввода (опечаток)

Что плохо?

?

Базы данных, 11 класс (Access)

24 of 149

Многотабличная БД

24

Группы
Код	Название	Год создания
1	Машина времени	1969
2	Кино	1981

Альбомы
Код	Название	Код группы	Год	Число композиций
1	Реки и мосты	1	1987	16
2	В круге света	1	1988	11
3	Группа крови	2	1988	11
4	Последний герой	2	1989	10

Что улучшилось?

?

Базы данных, 11 класс (Access)

25 of 149

Многотабличная БД

25

Группы
Код
Название
Год создания

Альбомы
Код
Название
Код группы
Год
Число композиций

Внешний ключ – это неключевое поле таблицы, связанное с первичным ключом другой таблицы.

усложнение структуры (> 40-50 таблиц – много!)
при поиске нужно «собирать» данные разных таблиц

убрано дублирование
изменения нужно делать в одном месте
некоторая защита от опечаток (выбор из списка)

Базы данных, 11 класс (Access)

26 of 149

Ссылочная целостность

26

Удаление группы: что делать с альбомами?

?

запретить удаление записи
выполнить каскадное удаление (удалить все связанные с ней записи в других таблицах)
разрешить внести изменения

нарушится ссылочная целостность!

СУБД:

Базы данных, 11 класс (Access)

27 of 149

Типы связей между таблицами

27

Группы
Код
Название
Год создания

Альбомы
Код
Название
Код группы
Год
Число композиций

1

N

Связь 1:N – с одной записью в первой таблице могут быть связаны сколько угодно записей во второй таблице.

ключ

не ключ

Базы данных, 11 класс (Access)

28 of 149

Типы связей между таблицами

28

Связь 1:1 – с одной записью в первой таблице связана ровно одна запись во второй таблице.

ключ

Сотрудники				Секретно
Код	Фамилия	Имя	Отчество	Код	Зарплата
1	Иванов	Петр	Сидорович	1	20 000 р.
2	Петров	Сидор	Иванович	2	30 000 р.
3	Сидоров	Иван	Петрович	3	40 000 р.

Сотрудники
Код
Фамилия
Имя
Отчество

Секретно
Код
Зарплата

1

ключ

Базы данных, 11 класс (Access)

29 of 149

Типы связей между таблицами

29

Заказы
Номер
Дата

Блюда
Код
Название
Цена

N

Может ли быть несколько одинаковых блюд в заказе?

?

Может ли быть одно блюдо в нескольких заказах?

?

Связь N:N – с одной записью в первой таблице могут быть связаны сколько угодно записей во второй таблице, и наоборот.

в СУБД не поддерживаются

Базы данных, 11 класс (Access)

30 of 149

Типы связей между таблицами

30

Заказы
Номер
Дата

Заказано
Код
Номер заказа
Код блюда

Блюда
Код
Название
Цена

N

1

Связь N:N

N

1

Пример:

Заказы
Номер	Дата
1	11.12.12
2	12.12.12

Заказано
Код	Номер заказа	Код блюда
1	1	1
2	1	3
3	1	4
4	2	1
5	2	2
6	2	2
7	2	5

Блюда
Код	Название	Цена
1	борщ	80 р.
2	бифштекс	110 р.
3	гуляш	70 р.
4	чай	10 р.
5	кофе	50 р.

Состав заказов?

?

Базы данных, 11 класс (Access)

31 of 149

Задачи

31

Сколько заказчиков располагаются в Перми?

Заказчики			Города
Код	Название	Код города	Код	Название
1	ООО «Альфа»	3	1	Москва
2	ЗАО «Бета»	2	2	Санкт-Петербург
3	ООО «Гамма»	3	3	Пермь
4	ОАО «Дельта»	2	4	Воронеж
5	ООО «Каппа»	1	5	Липецк

Базы данных, 11 класс (Access)

32 of 149

Задачи

32

Заказчики			Города
Код	Название	Код города	Код	Название
1	ООО «Альфа»	3	1	Москва
2	ЗАО «Бета»	2	2	Санкт-Петербург
3	ООО «Гамма»	3	3	Пермь
4	ОАО «Дельта»	2	4	Воронеж
5	ООО «Каппа»	1	5	Липецк

Заказы
Накладная	Код заказчика	Артикул	Кол-во упаковок
1011	3	7576	10
1012	5	7576	20
1013	4	3889	25
1014	1	7825	30
1015	3	7576	10

Товары
Артикул	Название	Цена за упаковку
7576	Бумага	150 руб.
2325	Карандаши	200 руб.
3889	Фломастеры	350 руб.
2987	Дневники	400 руб.
7825	Пеналы	250 руб.

Определите:

какие товары отправлены в каждый из городов;
сколько бумаги отправлено в каждый из городов;
общую стоимость товаров, отправленных в каждый из городов.

Базы данных, 11 класс (Access)

33 of 149

Задачи

33

Персоны
Код	ФИО	Пол
71	Иванов Т.М.	М
85	Пановко И.Т.	М
13	Черненко И.А.	Ж
42	Пановко А.И.	Ж
23	Иванова А.И.	Ж
96	Пановко Н.Н.	Ж
82	Черненко А.Н.	M
95	Фукс Т.Н.	Ж
10	Фукс Н.А.	М
…	…	…

Дети
Код родителя	Код ребенка
23	71
13	23
85	23
82	13
95	13
85	42
82	10
95	10
…	…

Определить фамилию и инициалы

а) бабушки А.И.Ивановой

б) родного брата И.А. Черненко

в) прадеда Т.М. Иванова

г) внука И.Т. Пановко

Базы данных, 11 класс (Access)

34 of 149

Задачи

34

Персоны
Код	ФИО	Пол
86	Сизых И.Т.	М
83	Сизых А.И.	М
50	Малых А.Т.	Ж
79	Сидоров Т.М.	М
23	Сидоров А.Т.	М
13	Малых И.И.	Ж
98	Симоняк Т.Н.	Ж
11	Симоняк Н.И.	М
…	…	…

Дети
Код родителя	Код ребенка
98	83
86	13
79	50
86	83
13	50
79	23
13	23
98	13
86	11
…	…

Определить фамилию и инициалы

а) племянника Н.И. Симоняка

б) всех родных братьев и сестёр Н.И. Симоняка

в) бабушки А.Т. Малых

г) дедушки А.Т. Сидорова

Базы данных, 11 класс (Access)

35 of 149

Задачи

35

Персоны
Код	ФИО	Пол
11	Косарева Л.П.	Ж
12	Левитин И.А.	М
24	Шумахер А.Ф.	Ж
45	Бланш А.А.	М
56	Васильева М.А.	Ж
83	Левитин Б.И.	М
94	Левитина В.И.	Ж
115	Кузнецов А.П.	М
140	Левитина Р.Б.	Ж
162	Левитин Л.Б.	М
171	Гайдарова З.Н.	Ж
186	Мурина С.А.	Ж
201	Кузнецов П.А.	М
…	…	…

Дети
Код родителя	Код ребенка
11	83
11	94
12	83
12	94
24	115
56	140
56	162
83	140
83	162
94	186
94	201
115	186
115	201
…	…

Определить фамилию и инициалы

а) всех внуков и внучек И.А. Левитина;

б) родной сестры П.А. Кузнецова;

в) родного брата С.А. Муриной;

г) бабушки Р.Б. Левитиной.

Базы данных, 11 класс (Access)

36 of 149

Базы данных �(MS Access)

§ 15. Реляционная модель данных

36

37 of 149

Математическое описание БД

37

Требуется:

описание БД, независимое от способа хранения данных
методы управления этими данными

Эдгар Кодд

Реляционная модель данных (Э. Кодд, 1970):

все данные – свойства некоторых объектов
объекты делятся на классы (сущности)
данные об объекте – набор свойств (атрибутов):

(Название: «Кино», Лидер: «В. Цой»)

порядок свойств не определён
отношение (relation) – множество кортежей, описывающих объекты одного класса
в отношении нет одинаковых кортежей
порядок кортежей в отношении не определен

кортеж

Базы данных, 11 класс (Access)

38 of 149

Реляционные БД

38

Реляционная БД – это БД, которая основана на реляционной модели, то есть представляет собой набор отношений.

Удобно хранить в виде таблицы:

Группы
Название	Лидер	Год создания
Машина времени	А. Макаревич	1969
Кино	В. Цой	1981
Аквариум	Б. Гребенщиков	1972

отношение

атрибуты

сущность

кортеж

Базы данных, 11 класс (Access)

39 of 149

Реляционные БД

39

Реляционная БД – это база данных, которая представлена в виде набора таблиц и управляется с помощью языка SQL.

SQL: обращение к данным по названиям таблиц (отношений) и полей (атрибутов).

Основные операции:

создание и удаление таблиц
добавление, изменение и удаление записей
выборка данных

На языке таблиц:

каждая таблица описывает один класс объектов
порядок расположения полей в таблице не имеет значения
все значения одного поля относятся к одному и тому же типу данных
в таблице нет двух одинаковых записей
порядок записей в таблице не определён

Базы данных, 11 класс (Access)

40 of 149

Нормализация

40

Рейс	От	До	Самолет	Дата
ZX 001	Москва	Берлин	Boeing 737	11.12.2013
ZX 002	Москва	Санкт-Петербург	Airbus A321	12.12. 2013
ZX 003	Санкт-Петербург	Берлин	Boeing 737	13.12. 2013

Есть дублирование!

!

Нормализация – это изменение структуры базы данных, которое устраняет избыточность и предотвращает возможные нарушения целостности.

Что плохо?

?

Базы данных, 11 класс (Access)

41 of 149

Нормализация на примерах

41

1. Любое поле должно быть неделимым:

Сотрудник	Телефоны
Иванов Петр Сидорович	123-45-67, (901) 111-22-33
Петров Сидор Иванович	345-67-89, (902) 222-33-44

Фамилия	Имя	Отчество	Телефон-Дом	Телефон-Моб
Иванов	Петр	Сидорович	123-45-67	(901) 111-22-33
Петров	Сидор	Иванович	345-67-89	(902) 222-33-44

Что плохо?

?

Базы данных, 11 класс (Access)

42 of 149

Нормализация на примерах

42

2. Любое неключевое поле должно зависеть от ключа:

Номер	Автомобиль	Владелец	Телефон
А123АА47	«Лада-Калина»	Иванов	155-77-23
Т234ТТ78	«Ока»	Петров	277-34-67
В345ВВ98	«Мерседес»	Васильев	322-98-44
А345CC47	«Ауди»	Иванов	155-77-23

Автомобили			Владельцы
Номер	Автомобиль	Владелец	Код	Фамилия	Телефон
А123АА47	«Лада-Калина»	1	1	Иванов	155-77-23
Б234ББ78	«Ока»	2	2	Петров	277-34-67
В345ВВ98	«Мерседес»	3	3	Васильев	322-98-44
А345CC47	«Ауди»	1

N

1

Что плохо?

?

Базы данных, 11 класс (Access)

43 of 149

Нормализация на примерах

43

3. Не должно быть одинаковых по смыслу полей:

Дата	Бананы	Апельсины	Яблоки
21.05.2013	120	78	101
22.05.2013	153	99	65
23.05.2013	87	55	123

	Продажи			Товары
Код	Дата	Товар	Продано	Код	Название
1	21.05.2013	1	120	1	Бананы
2	21.05.2013	2	78	2	Апельсины
3	21.05.2013	3	101	3	Яблоки
4	22.05.2013	1	153
…	…	…	…

Проблема: добавить новый товар!

!

N

1

Что плохо?

?

Базы данных, 11 класс (Access)

44 of 149

Нормализация на примерах

44

4. Не нужно хранить то, что может быть вычислено:

Прибыль можно вычислить!

!

Дата	Доходы	Расходы	Прибыль
03.2013	155	128	27
02.2013	178	105	73
01.2013	194	159	35

Дата	Доходы	Расходы
03.2013	155	128
02.2013	178	105
01.2013	194	159

прибыль вычисляется через запрос!

Что плохо?

?

Базы данных, 11 класс (Access)

45 of 149

Нормализация: «за» и «против»

45

усложнение структуры (> 40-50 таблиц – много!)
замедляется поиск (нужно «собирать» данные из разных таблиц)

устранено дублирование
все изменения делаются в одном месте
некоторая защита от опечаток (выбор из списка)
СУБД поддерживает ссылочную целостность

Базы данных, 11 класс (Access)

46 of 149

Задача

46

Выполните нормализацию:

Год	Животные	Район	Количество
2009	белки	Нижняя Балка	12
2009	бурундуки	Верхняя Балка	5
2010	еноты	Нижняя Балка	7
2010	еноты	Овраг	3
2010	белки	Верхняя Балка	10

Базы данных, 11 класс (Access)

47 of 149

Задача

47

Выполните нормализацию:

Год спуска на воду	Название	Проект	Экипаж
1980	Удалой	1155	220 чел.
1985	Адмирал Трибуц	1155	220 чел.
1987	Североморск	1155	220 чел.
1982	Москва	1164	510 чел.
1983	Варяг	1164	510 чел.

Базы данных, 11 класс (Access)

48 of 149

Задача

48

Выполните нормализацию:

Год	Изготовитель	Город	Модель	Скорость	Цена
2007	ВАЗ	Тольятти	1119	165 км/ч	120000 р.
1995	ВАЗ	Тольятти	11113	130 км/ч	50000 р.
1992	КАМАЗ	Набережные Челны	5320	90 км/ч	200000 р.
2006	КАМАЗ	Набережные Челны	55102	90 км/ч	450000 р.
2007	БелАЗ	Жодино	75600	64 км/ч	1200000 р.

Базы данных, 11 класс (Access)

49 of 149

Задача

49

Выполните нормализацию:

Страна	Фамилия	Инструмент	Автор произведения	Место
Россия	Иванов	фортепьяно	Рахманинов	1
Россия	Петров	флейта	Лист	2
Германия	Шмидт	скрипка	Моцарт	3
США	Смит	скрипка	Рахманинов	4
США	Браун	гобой	Моцарт	5

Базы данных, 11 класс (Access)

50 of 149

Базы данных �(MS Access)

§ 16. Работа с таблицей

50

51 of 149

Базы данных Microsoft Access

51

Файлы *.accdb, *.mdb

таблицы с данными
формы – диалоговые окна, с помощью которых пользователь вводит и изменяет данные
запросы – команды по управлению данными
отчеты – шаблоны документов для вывода на печать
макросы – средства автоматизации работы
модули – программы на языке Visual Basic

Базы данных, 11 класс (Access)

52 of 149

Базы данных Microsoft Access

52

Пуск – Программы – Microsoft Office – �Microsoft Access

шаблоны

создание новой базы данных

открыть базу с диска

последние документы

Базы данных, 11 класс (Access)

53 of 149

Таблица

53

2xЛКМ

последняя запись

поля

записи

текущая запись

область выделения

новая запись

на 1-ую запись

предыдущая запись

номер текущей записи

следующая запись

новая запись

текущее поле

последняя�запись

Базы данных, 11 класс (Access)

54 of 149

Поиск и сортировка

54

Поиск и замена:

текущее поле или все поля

целиком, �с любой частью, �с началом

всё, вверх, вниз

Сортировка по текущему полю (столбцу):

по возрастанию

по убыванию

сброс

Базы данных, 11 класс (Access)

55 of 149

Задача

55

Фамилия	Пол	Математика	Русский язык	Химия	Информа-тика	Биология
Сомов	м	75	65	70	90	58
Кротов	м	83	75	59	87	60
Белочкина	ж	55	92	64	65	86
Окунев	м	75	68	72	70	56
Судакова	ж	68	70	56	58	60
Щукина	ж	76	58	78	80	85

Какой по счету будет запись с фамилией Белочкина, если отсортировать таблицу по полю:

а) Фамилия (по алфавиту)

б) Математика (по убыванию)

в) Русский язык (по убыванию)

г) Химия (по возрастанию)

д) Информатика (по возрастанию)

е) Биология (по убыванию)

Базы данных, 11 класс (Access)

56 of 149

Фильтрация

56

Фильтр – это условие для отбора записей.

Остальные записи временно скрываются!

!

Базы данных, 11 класс (Access)

57 of 149

Фильтрация

57

Фильтр – это условие для отбора записей.

Остальные записи временно скрываются!

!

быстрый фильтр (фильтр по выделенному)

Базы данных, 11 класс (Access)

58 of 149

Сложные фильтры

58

Сложные условия

одновременно

(операция И)

начинается �с буквы «С»

новое условие, связанное через ИЛИ

Расширенный фильтр – можно

переставлять столбцы
выводить не все столбцы
устанавливать порядок сортировки

точное совпадение

применить/сбросить фильтр

Базы данных, 11 класс (Access)

59 of 149

Фильтрация

59

с таблицей хранится только один фильтр
не все условия можно записать

какие нельзя?

Для сложных случаев – запросы!

!

Базы данных, 11 класс (Access)

60 of 149

Задача

60

В чём разница между фильтрами:

а) Предмет = 'Математика' AND Класс = 2 �OR Год издания > 2009

б) Предмет = 'Математика' OR Класс = 2 �AND Год издания > 2009

в) Предмет = 'Математика' OR Год издания > 2009 AND Класс = 2

г) Предмет = 'Математика' AND Год издания > 2009 OR Класс = 2

Какие фильтры дают одинаковый результат?

?

Базы данных, 11 класс (Access)

61 of 149

Задача

61

Фамилия	Пол	Математика	Русский язык	Химия	Информа-тика	Биология
Сомов	м	75	65	70	90	58
Кротов	м	83	75	59	87	60
Белочкина	ж	55	92	64	65	86
Окунев	м	75	68	72	70	56
Судакова	ж	68	70	56	58	60
Щукина	ж	76	58	78	80	85

Сколько записей будет отобрано:

а)Пол = 'ж' AND Химия > Биология

б)Пол = 'ж' OR Химия > Биология

в)Пол = 'м' AND Математика > Информатика

г)Пол = 'м' OR Математика > Информатика

д)Пол = 'ж' AND Русский язык > 70 OR Информатика > 80

е)Пол = 'ж' OR Русский язык > 70 AND Информатика > 80

ж)Пол = 'м' AND Информатика > 80 OR Русский язык > 60

з)Пол = 'м' OR Информатика > 80 AND Русский язык > 60

Базы данных, 11 класс (Access)

62 of 149

Однотабличная БД

62

Базы данных, 11 класс (Access)

63 of 149

Создание таблицы

63

2xЛКМ

поле-счетчик

ввод значения

Базы данных, 11 класс (Access)

64 of 149

Конструктор таблицы

64

Базы данных, 11 класс (Access)

65 of 149

Конструктор таблицы

65

текущее �поле

ключ

тип поля (выбор из списка)

свойства текущего поля

ПКМ

Базы данных, 11 класс (Access)

66 of 149

Типы полей

66

Текстовый
Поле MEMO (многострочный текст)
Числовой
Денежный
Поле объекта OLE (рисунок, звук, видео)
Логический
Дата / Время
Счётчик (номер записи)

Зачем нужны типы полей?

?

Зачем?

?

Базы данных, 11 класс (Access)

67 of 149

Свойства полей

67

Размер поля: байт, целое, вещественное, …

Формат поля: как выводить на экран.

Маска ввода: шаблон (ввод телефона).

Подпись: как называется столбец при выводе на экран; можно использовать скобки, знаки и т.д. �(«Население, млн. чел.»)

Значение по умолчанию (вписывается автоматически).

Условие на значение: защита от ошибок ввода («>18»).

Сообщение об ошибке �(«Возраст должен быть > 18 лет!»)

Обязательное поле (да/нет)

Индексированное поле (да/нет)

Базы данных, 11 класс (Access)

68 of 149

Конструктор таблицы

68

режим таблицы

сделать поле ключевым

изменить индексы

Базы данных, 11 класс (Access)

69 of 149

Индексы

69

ПКМ

Базы данных, 11 класс (Access)

70 of 149

Многотабличная БД

70

Заказы
Номер
Дата

Заказано
Код
Номер заказа
Код блюда

Блюда
Код
Название
Цена

N

1

N

1

Пример:

Заказы
Номер	Дата
1	11.12.12
2	12.12.12

Заказано
Код	Номер заказа	Код блюда
1	1	1
2	1	3
3	1	4
4	2	1
5	2	2
6	2	2
7	2	5

Блюда
Код	Название	Цена
1	борщ	80 р.
2	бифштекс	110 р.
3	гуляш	70 р.
4	чай	10 р.
5	кофе	50 р.

Базы данных, 11 класс (Access)

71 of 149

Создание таблиц

71

Заказы
Номер
Дата

Заказано
Код
Номер заказа
Код блюда

Блюда
Код
Название
Цена

Числовой

Денежный

Базы данных, 11 класс (Access)

72 of 149

Установка связей между таблицами

72

добавить все

Как определить, где 1 и N?

?

Работа с базами данных – Схема данных

перетащить ЛКМ

Базы данных, 11 класс (Access)

73 of 149

Заполнение таблиц

73

Заказы
Номер	Дата
1	11.04.13
2	12.04.13
3	12.04.13

Заказано
Код	Номер заказа	Код блюда
1	1	1
2	1	3
3	1	4
4	2	1
5	2	2
6	2	2
7	2	5
8	3	1
8	3	5

Блюда
Код	Название	Цена
1	борщ	80 р.
2	бифштекс	110 р.
3	гуляш	70 р.
4	чай	10 р.
5	кофе	50 р.

Базы данных, 11 класс (Access)

74 of 149

Базы данных �(MS Access)

§ 17. Запросы

74

75 of 149

Что такое запрос?

75

Запрос – это обращение к СУБД для отбора записей или выполнения других операций с данными.

SQL (англ. Structured Query Language – язык структурных запросов).

выборка данных
создание таблиц
редактирование таблиц и записей
изменение записей
удаление записей
удаление таблиц

Базы данных, 11 класс (Access)

76 of 149

Конструктор запросов

76

ЛКМ

Базы данных, 11 класс (Access)

77 of 149

Конструктор запросов

77

перетащить ЛКМ

все поля

выполнить (F5)

Базы данных, 11 класс (Access)

78 of 149

Режим SQL

78

ЛКМ

SELECT Футбол.Команда, Футбол.Победы, Футбол.Зарплата

FROM Футбол;

выбрать

из таблицы

Базы данных, 11 класс (Access)

79 of 149

Сортировка

79

Сортировка

Базы данных, 11 класс (Access)

80 of 149

Запуск запроса

80

2×ЛКМ

Запросы с параметрами

86

Параметры – это данные, которые пользователь вводит при выполнении запроса.

параметр

Базы данных, 11 класс (Access)

87 of 149

Вычисляемые поля

87

Очки = Ничьи + 3*Победы

вычисляемое поле

Базы данных, 11 класс (Access)

88 of 149

Вычисляемые поля (SQL)

88

Очки = Ничьи + 3*Победы

вычисляемое поле

SELECT Футбол.Команда, Футбол.Победы, Футбол.Зарплата,

[Ничьи]+3*[Победы] AS Очки

FROM Футбол;

Базы данных, 11 класс (Access)

89 of 149

Другие типы запросов

89

Создать пустой запрос.
Перейти в режим SQL.

UPDATE Футбол

SET Зарплата = 2*Зарплата

WHERE Ничьи + 3*Победы > 40;

Базы данных, 11 класс (Access)

90 of 149

Запрос данных из нескольких таблиц

90

Заказы

Блюда

Базы данных, 11 класс (Access)

91 of 149

Запрос данных из нескольких таблиц

91

ЛКМ

добавить все

Зачем добавлять все таблицы?

?

Базы данных, 11 класс (Access)

92 of 149

Запрос данных из нескольких таблиц

92

ПКМ – Свойства

Подпись: Заказ

перетащить нужные поля

ПКМ – Свойства

Подпись: Блюдо

Базы данных, 11 класс (Access)

93 of 149

Запрос данных из нескольких таблиц

93

ПКМ – Режим SQL

SELECT Заказы.Номер AS Заказ, Заказы.Дата,

Блюда.Название AS Блюдо, Блюда.Цена

FROM Заказы

INNER JOIN

(Блюда INNER JOIN Заказано ON Блюда.Код = Заказано.[Код блюда])

ON Заказы.Номер = Заказано.[Номер заказа];

подпись

связи

Базы данных, 11 класс (Access)

94 of 149

Итоговый запрос

94

Почему?

?

Как сменить подписи?

?

Базы данных, 11 класс (Access)

95 of 149

Итоговый запрос

95

Считать сумму цен

Групповая операция

ЛКМ

Базы данных, 11 класс (Access)

96 of 149

Базы данных �(MS Access)

§ 18. Формы

96

97 of 149

Зачем нужны формы?

97

БД

СУБД

прикладная программа

SQL

формы

Форма – это диалоговое окно, с помощью которого пользователь

просматривает
изменяет
добавляет
удаляет

данные из базы.

Базы данных, 11 класс (Access)

98 of 149

Создание формы

98

источник данных

разные способы создания форм

Базы данных, 11 класс (Access)

99 of 149

Форма на одну запись

99

переход по записям

Базы данных, 11 класс (Access)

100 of 149

Ленточная форма

100

Базы данных, 11 класс (Access)

101 of 149

Разделенная форма

101

Базы данных, 11 класс (Access)

102 of 149

Режимы работы с формами

102

Режим формы – просмотр и редактирование данных.

Режим макета – просмотр данных, изменение оформления, перемещение элементов.

Конструктор – изменение структуры и оформления, добавление новых элементов, …

Базы данных, 11 класс (Access)

103 of 149

Конструктор форм

103

заголовок формы

область данных

примечание формы

изменение размеров

Базы данных, 11 класс (Access)

104 of 149

Свойства формы и ее элементов

104

или ПКМ – Свойства

выбор элемента

Макет = оформление

Данные: источник (таблица или запрос), фильтр, сортировка

События:

клавиши, мышь
открытие, закрытие
изменение записи

Базы данных, 11 класс (Access)

105 of 149

Некоторые свойства формы (Макет)

105

подпись

кнопки перехода (да/нет)

область выделения

(да/нет)

изменяемая граница

(да/нет)

Базы данных, 11 класс (Access)

106 of 149

Свойства элементов

106

Макет

размеры
оформление
полосы прокрутки

Данные:

название поля таблицы
маска ввода (телефон)
значение по умолчанию
условие на значение
сообщение об ошибке

События:

клавиши, мышь
изменение значения

Другие:

всплывающая подсказка

Базы данных, 11 класс (Access)

107 of 149

Связанные элементы

107

надпись (текст можно менять)

поле (название поля таблицы)

маркеры (изменение размеров)

независимое перемещение

щелкнуть внутри, чтобы изменить текст

щелкнуть на рамке, чтобы выделить элемент

Базы данных, 11 класс (Access)

108 of 149

Группировка по столбцам (Макет)

108

общая граница

переместить весь столбец

«Оторвать» от столбца: ПКМ – Макет – Удалить

Создать новый столбец: ПКМ – Макет – Столбец

Базы данных, 11 класс (Access)

109 of 149

Оформление элементов

109

Выделение элементов:

ЛКМ на рамке элемента
+ Shift = выделить несколько элементов

условное �форматирование

толщина, стиль и цвет�контура

Базы данных, 11 класс (Access)

110 of 149

Добавление новых полей

110

перетащить

Базы данных, 11 класс (Access)

111 of 149

Добавление новых элементов

111

Рисунок

Свободная рамка объекта (объект редактируется)

Гиперссылка

Присоединенная рамка объекта (объект из базы)

Подчиненная форма

Разрыв страницы

Базы данных, 11 класс (Access)

112 of 149

Форма с подчинённой (субформой)

112

Откуда брать данные?

?

Таблица Заказы

ЗапросЗаказы

ЗапросКОплате

Задача: объединить данные из трёх связанных источников!

!

Базы данных, 11 класс (Access)

Добавление подчинённых форм

116

Перетащить ЛКМ в режиме Конструктор

ЗапросЗаказы

ЗапросКОплате

Связи по номеру заказа настроены автоматически!

!

Базы данных, 11 класс (Access)

117 of 149

Кнопочные формы

117

Базы данных, 11 класс (Access)

118 of 149

Связать кнопку с действием

118

Макрос (макрокоманда) – это подпрограмма для автоматизации действий пользователя.

Microsoft Office: Visual Basic for Applications (VBA)

ЛКМ

Базы данных, 11 класс (Access)

119 of 149

Макрос

119

Базы данных, 11 класс (Access)

120 of 149

Базы данных �(MS Access)

§ 19. Отчёты

120

121 of 149

Что такое отчёт?

121

Отчет – это документ, предназначенный для вывода данных на печать.

Базы данных, 11 класс (Access)

122 of 149

Создание отчёта

122

Источник данных – таблица или запрос, несколько связанных таблиц/запросов (мастер).

простой отчет

конструктор

Конструктор отчётов

126

Заголовок отчета – один раз в начале отчета.

Верхний колонтитул – в начале каждой страницы.

Область данных – информация из БД.

Нижний колонтитул – в конце каждой страницы.

Примечание отчета – один раз в конце отчета.

="Страница " & [Page] & " из " & [Pages]

=Date()

=Time()

Базы данных, 11 класс (Access)

127 of 149

Редактирование отчётов

127

– свойства выбранного � элемента

ЛКМ – выделить элемент

Добавление элементов:

Так же, как в конструкторе форм:

Базы данных, 11 класс (Access)

128 of 149

Отчёт с группировкой

128

Запрос Заказы

группировка по номеру заказа

группировка по дате

Базы данных, 11 класс (Access)

линия

поле: сумма заказа

поле

Базы данных, 11 класс (Access)

135 of 149

Базы данных �(MS Access)

§ 20. Нереляционные базы данных

135

136 of 149

Проблемы реляционных БД

136

данные нужно разбить на множество таблиц
человек мыслит не таблицами, а объектами
данные об одном объекте разбросаны по нескольким таблицами ⇒ замедление запросов
структура хранимых объектов строго определена и не может меняться
как хранить объекты с разными свойствами?
как распределить нагрузку между серверами в Интернете? (реляционные БД плохо масштабируются)

Базы данных, 11 класс (Access)

137 of 149

Базы данных NoSQL

137

NoSQL = Not Only SQL

базы данных «ключ-значение»
большие разреженные таблицы
документоориентированные БД
графовые БД

Базы данных, 11 класс (Access)

138 of 149

БД «ключ-значение»

138

добавление записи

поиск по ключу
удаление записи по ключу
изменение записи по ключу

Связи объектов не поддерживаются!

!

data(12345) = "Вася"

номер сервера:

12345 mod 100 = 45

Масштабируемость – возможность распределить нагрузку на несколько серверов.

Redis

DynamoDB

MemcacheMemcacheDB

Базы данных, 11 класс (Access)

139 of 149

БД «ключ-значение»

139

близость к человеческому восприятию
масштабируемость

ключи

1-1000

1-500

501-1000

не поддерживаются связи между объектами
нет стандарта на язык управления данными
запросы – только поиск по ключу (сортировка?)

Базы данных, 11 класс (Access)

140 of 149

Большие разреженные таблицы

140

два ключа – номера строки и столбца

	Имя	Дата рождения	Эл. почта	Телефон
1	Денис	12.11.1993	den@sql.ru	365-13-78
2	Кирилл			572-12-80
3	Василий		vasil@nosql.ru

не все ячейки заполнены

Строки и столбцы распределены по серверам!

!

BigTable

Cassandra (Facebook, Apple, Twitter)

HBase

Базы данных, 11 класс (Access)

141 of 149

Документо-ориентированные БД

141

{ ключ: 1231239786234762394769237,

автор: "solaris",

название: "Новая ОС",

дата: "15.12.2016",

текст: "Начал писать операционку...",

комментарии: [

{ автор: "linus",

дата: "16.12.2016",

текст: "Отлично! Держи в курсе."

}

]

}

хэш-код

набор полей произвольный
документы в одной базе могут иметь разный набор полей

MongoDB

CouchDB

бесплатно!

Базы данных, 11 класс (Access)

142 of 149

Базы данных на основе графов

142

код: 1

имя: Анна

телефон: 911-11-12

код: 2

имя: Борис

email: boris@mail.ru

код: 101

друг с: 15.12.2014

код: 102

друг с: 15.12.2014

объекты в базе – данные об узлах и связях

NeoNeo4Neo4j

Базы данных, 11 класс (Access)

143 of 149

Базы данных �(MS Access)

§ 21. Экспертные системы

143

144 of 149

Что такое экспертная система?

144

Экспертная система – это компьютерная программа, задача которой – заменить человека-эксперта при выработке рекомендаций для принятия решений в сложной ситуации.

искусственный интеллект

Эксперт

обладает глубокими знаниями
имеет опыт работы в своей области
может ответить на вопросы, на которые нельзя найти ответы в Интернете
может предложить решение плохо поставленных задач
может примерно оценить вероятность своей версии

Кто такой эксперт?

?

Базы данных, 11 класс (Access)

145 of 149

Состав экспертной системы

145

база знаний
блок получения решения («решатель»)
интерфейс с пользователем

База знаний

Факты

у окуня есть жабры
Иван – отец Марьи
Волга впадает в Каспийское море

Правила

если x – животное и x дышит жабрами, то x – рыба;
если x – отец y, и y – отец z, то x – дед z;
если x состоит из атомов углерода и обладает высокой твердостью, то x – алмаз.

эксперт

инженер по знаниям

Оценка степени уверенности!

!

Базы данных, 11 класс (Access)

146 of 149

Экспертная система: пример

146

есть перья ⇒ птица
дышит жабрами ⇒ рыба
кормит детенышей молоком ⇒ млекопитающее
млекопитающее и ест мясо ⇒ хищник

птица

да

рыба

?

да

нет

хищник

?

да

нет

да

млекопитающее

кормит детей молоком?

нет

дышит жабрами?

нет

имеет перья?

ест мясо?

Язык программирования Пролог!

!

Базы данных, 11 класс (Access)

147 of 149

Экспертные системы: итоги

147

ЭС применяется в достаточно узкой области
использует базу знаний, которая может расширяться
может применяться при неточных и противоречивых данных
выдает ответ в виде рекомендации
может показать, как получено решение

опыт и интуицию экспертов сложно свести к правилам
отладка и проверка ЭС очень сложна, трудно гарантировать правильность выводов
ЭС неспособны самообучаться, необходима работа инженера по знаниям

Базы данных, 11 класс (Access)

148 of 149

Конец фильма

148

ПОЛЯКОВ Константин Юрьевич

д.т.н., учитель информатики

ГБОУ СОШ № 163, г. Санкт-Петербург

kpolyakov@mail.ru

ЕРЕМИН Евгений Александрович

к.ф.-м.н., доцент кафедры мультимедийной дидактики и ИТО ПГГПУ, г. Пермь

eremin@pspu.ac.ru

Базы данных, 11 класс (Access)

149 of 149

Источники иллюстраций

149

server.tkat.ru
ru.wikipedia.org
иллюстрации художников издательства «Бином»
авторские материалы

Базы данных, 11 класс (Access)