1 Базы данных § 12. Информационные системы § 13. Таблицы § 14. Многотабличные базы данных § 15. Реляционная модель данных § 16. Работа с таблицей § 17. Создание однотабличной базы данных § 18. Запросы § 19. Формы § 20. Отчёты § 21. Работа с многотабличной базой данных § 22. Нереляционные базы данных § 23. Экспертные системы
§ 12. Информационные системы 2
3 Информационная система (ИС) в широком смысле — это аппаратные и программные средства, предназначенные для того, чтобы своевременно обеспечить пользователей нужной информацией. Задачи : хранение данных доступ к данным
4 База данных (БД) — это специальным образом организованная совокупность данных о некоторой предметной области, хранящаяся во внешней памяти компьютера. Система управления базой данных (СУБД ) — это программные средства, которые позволяют выполнять все необходимые операции с базой данных. БД + СУБД = и нформационная система
5 поиск данных редактирование данных выполнение несложных расчетов обеспечение целостности (корректности, непротиворечивости) данных восстановление данных после сбоев Задачи СУБД : БД СУБД прикладная программа
6 ИС локальные удалённые БД и СУБД на компьютере пользователя БД на удалённом компьютере (в сети) автономность нужно обновлять БД на каждом компьютере как учесть изменения, внесённые пользователями все работают с одной БД зависимость от сети
7 Текстовый формат : Собственный формат программы : Универсальный формат : БД : метаданные данные «данные о данных» Иванов ; Иван ; Петрович ; 1968 ; « Audi » ; 20 000 р. Петров ; Пётр ; Иванович ; 1975 ; «Лада» ; 3 0 000 р. Сидоров ; Семён ; Иванович ; 1975 ; «Нива» ; 15 000 р. Что плохо? ? нет переносимости (БД можно использовать только с этой программой) Переносимость – это возможность использовать БД в других информационных системах.
8 сервер рабочие станции БД СУБД СУБД рабочие станции должны быть мощными высокая нагрузка на сеть слабая защита данных ненадежность при большом количестве пользователей Решение – перенести СУБД на сервер! !
9 сервер рабочие станции (клиенты) БД СУБД прикладная программа прикладная программа запрос ответ Задачи клиента : Задачи сервера : отправить серверу запрос на языке SQL принять ответ сервера вывести результаты при получении запроса поставить его в очередь выполнить запрос отправить ответ клиенту SQL = Structured Query Language – язык структурных запросов для управления данными
10 обработка данных на сервере рабочие станции могут быть маломощными проще модернизация надежная защита данных (на сервере) снижается нагрузка на сеть надежная работа при большом количестве пользователей нужен мощный сервер высокая стоимость коммерческих СУБД MS SQL Server Firebird ( www.firebirdsql.org ) PostgreSQL ( www. postgresql. org ) MySQL ( www. mysql. com ) бесплатно!
11 Транзакция – это группа операций, которая представляет собой одно законченное действие. Транзакция должна быть выполнена целиком или не выполнена вообще. Задача : перевести 100 000 рублей со счета 12345 на счет 54321. прочитать сумму на счету 12345 уменьшить ее на 100 000 рублей прочитать сумму на счету 54321 увеличить ее на 100 000 рублей сбой питания! журналирование создание новых записей (помечаются как действительные после завершения транзакции)
§ 13. Таблицы 12
13 Фамилия Имя Адрес Телефон Иванов Петр Суворовский пр., д. 32, кв. 11 275-75-75 Петров Василий Кутузовский пр., д. 12, кв. 20 276-76-76 Васильев Иван Нахимовский пр., д. 23, кв. 33 277-77-77 Примеры : записная книжка каталог в библиотеке Иванов Пётр Суворовский пр., д. 32, кв. 11 275-75-75 поля записи целые числа вещественные числа денежные суммы логические значения текстовые данные время, дата произвольные двоичные данные (рисунки, звук, видео) Типы полей :
14 Ключ – это поле или комбинация полей, однозначно определяющие запись. Могут ли эти данные быть ключом? фамилия имя номер паспорта номер дома регистрационный номер автомобиля город проживания адрес электронной почты дата выполнения работы марка стиральной машины ? Может ли быть несколько ключей в таблице? ?
15 Первичный ключ – это ключ, выбранный в качестве основного. Простой ключ – состоит из одного поля. Составной ключ – состоит из нескольких полей. Дата Время Температура Влажность Скорость ветра 21.07.2012 12:00 25 75 4 21.07.2012 15 :00 23 70 3 … … … … … Составной ключ Дата + Время Какой ключ? ? Свойства ключа: уникальность несократимость (Дата + Время + Влажность)
16 Номер Фамилия Имя Адрес Телефон 1 Иванов Петр Суворовский пр., д. 32, кв. 11 275-75-75 2 Петров Василий Кутузовский пр., д. 12, кв. 20 276-76-76 3 Васильев Иван Нахимовский пр., д. 23, кв. 33 277-77-77 Какой ключ? ? Суррогатный ключ – это дополнительное поле, которое служит первичным ключом. операции с числами выполняются быстрее уникальность поддерживается СУБД (поле-счётчик)
17 Линейный поиск – это перебор всех записей до тех пор, пока не будет найдена нужная. Код Фамилия 1 Сидоров 2 Ветров … 1024 Померанцев Иванов? 1024 сравнения! данные не надо предварительно готовить низкая скорость поиска
18 Разделить область поиска на две равные части. Определить, в какой половине находится нужный объект. Перейти к шагу 1 для этой половины. Повторять шаги 1-3 пока объект не будет «пойман».
19 1 Андреев 2 Барсуков … 512 Ковалев … 1023 Юрьев 1024 Яшин требует предварительной сортировки. Иванов? 1 Андреев … 255 Журов … 512 Ковалев … 1024 Яшин … 255 Журов … 383 Игнатьев … 512 Ковалев … Сколько сравнений? ? записи надо отсортировать по нужному полю можно использовать только для одного поля быстрый поиск 1 1 сравнений!
20 Индекс – это вспомогательная таблица, которая служит для ускорения поиска в основной таблице. Фамилия Номера записей Васильев 3 Иванов 1 Петров 2 Номер Фамилия Имя Адрес Телефон 1 Иванов Петр Суворовский пр., д. 32, кв. 11 275-75-75 2 Петров Василий Кутузовский пр., д. 12, кв. 20 276-76-76 3 Васильев Иван Нахимовский пр., д. 23, кв. 33 277-77-77 отсортированы! индексы занимают место на диске при изменении таблицы надо перестраивать все индексы (в СУБД – автоматически) двоичный поиск по всем столбцам, для которых построены индексы
21 Целостность базы данных означает, что она содержит полную и непротиворечивую информацию и удовлетворяет всем заданным ограничениям. Физическая целостность – сохранность данных в случае отказа оборудования: транзакции резервное копирование RAID- массивы жёстких дисков (дублирование) Логическая целостность – непротиворечивость: типы полей обязательные поля уникальные индексы ограничения на значения шаблоны ввода (###) ###-##-## условия на несколько полей (увольнение позже приёма)
22 Постройте индексы по всем полям: Номер Дата Заказ Товар Количество, т 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
§ 14. Многотабличные базы данных 23
24 Альбомы Код Название Группа Год Число композиций 1 Реки и мосты Машина времени 1987 16 2 В круге света Машина времени 1988 11 3 Группа крови Кино 1988 11 4 Последний герой Кино 1989 10 дублирование данных при изменении каких-то данных, возможно, придется менять несколько записей нет защиты от ошибок ввода (опечаток) Что плохо? ?
25 Группы Код Название Год создания 1 Машина времени 1969 2 Кино 198 1 Альбомы Код Название Код группы Год Число композиций 1 Реки и мосты 1 1987 16 2 В круге света 1 1988 11 3 Группа крови 2 1988 11 4 Последний герой 2 1989 10 Что улучшилось? ?
26 Группы Код Название Год создания Альбомы Код Название Код группы Год Число композиций Внешний ключ – это неключевое поле таблицы, связанное с первичным ключом другой таблицы. усложнение структуры ( > 40-50 таблиц – много!) при поиске нужно «собирать» данные разных таблиц убрано дублирование изменения нужно делать в одном месте некоторая защита от опечаток (выбор из списка)
27 Удаление группы: что делать с альбомами? ? запретить удаление записи выполнить каскадное удаление (удалить все связанные с ней записи в других таблицах) разрешить внести изменения нарушится ссылочная целостность! СУБД :
28 Группы Код Название Год создания Альбомы Код Название Код группы Год Число композиций 1 N Связь 1: N – с одной записью в первой таблице могут быть связаны сколько угодно записей во второй таблице. ключ не ключ
29 Связь 1:1 – с одной записью в первой таблице связана ровно одна запись во второй таблице. ключ Сотрудники Секретно Код Фамилия Имя Отчество Код Зарплата 1 Иванов Петр Сидорович 1 20 000 р. 2 Петров Сидор Иванович 2 30 000 р. 3 Сидоров Иван Петрович 3 40 000 р. Сотрудники Код Фамилия Имя Отчество Секретно Код Зарплата 1 1 ключ
30 Заказы Номер Дата Блюда Код Название Цена N N Может ли быть несколько одинаковых блюд в заказе? ? Может ли быть одно блюдо в нескольких заказах? ? Связь N : N – с одной записью в первой таблице могут быть связаны сколько угодно записей во второй таблице, и наоборот. в СУБД не поддерживаются
31 Заказы Номер Дата Заказано Код Номер заказа Код блюда Блюда Код Название Цена 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 р. Состав заказов? ?
32 Сколько заказчиков располагаются в Перми? Заказчики Города Код Название Код города Код Название 1 ООО «Альфа» 3 1 Москва 2 ЗАО «Бета» 2 2 Санкт-Петербург 3 ООО «Гамма» 3 3 Пермь 4 ОАО «Дельта» 2 4 Воронеж 5 ООО «Каппа» 1 5 Липецк
33 Заказчики Города Код Название Код города Код Название 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 руб. Определите: какие товары отправлены в каждый из городов; сколько бумаги отправлено в каждый из городов; общую стоимость товаров, отправленных в каждый из городов.
34 Персоны Код ФИО Пол 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 … … Определить фамилию и инициалы а) бабушки А.И.Ивановой б) родного брата И.А. Черненко в) прадеда Т.М. Иванова г) внука И.Т. Пановко
35 Персоны Код ФИО Пол 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 … … Определить фамилию и инициалы а) племянника Н.И. Симоняка б) всех родных братьев и сестёр Н.И. Симоняка в) бабушки А.Т. Малых г) дедушки А.Т. Сидорова
36 Персоны Код ФИО Пол 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 … … Определить фамилию и инициалы а) всех внуков и внучек И.А. Левитина; б) родной сестры П.А. Кузнецова; в) родного брата С.А. Муриной; г) бабушки Р.Б. Левитиной.
§ 15. Реляционная модель данных 37
38 Требуется: описание БД, независимое от способа хранения данных методы управления этими данными Эдгар Кодд Реляционная модель данных (Э. Кодд, 1970): в се данные – свойства некоторых объектов объекты делятся на классы ( сущности ) данные об объекте – набор свойств ( атрибутов ): ( Название: «Кино», Лидер: «В. Цой » ) порядок свойств не определён отношение ( relation ) – множество кортежей, описывающих объекты одного класса в отношении нет одинаковых кортежей порядок кортежей в отношении не определен кортеж
39 Реляционная БД – это БД, которая основана на реляционной модели, то есть представляет собой набор отношений. Удобно хранить в виде таблицы: Группы Название Лидер Год создания Машина времени А. Макаревич 1969 Кино В. Цой 1981 Аквариум Б. Гребенщиков 1972 отношение атрибуты сущность кортеж
40 Реляционная БД – это база данных, которая представлена в виде набора таблиц и управляется с помощью языка SQL. SQL : обращение к данным по названиям таблиц (отношений) и полей (атрибутов). Основные операции : создание и удаление таблиц добавление, изменение и удаление записей выборка данных На языке таблиц : каждая таблица описывает один класс объектов порядок расположения полей в таблице не имеет значения все значения одного поля относятся к одному и тому же типу данных в таблице нет двух одинаковых записей порядок записей в таблице не определён
41 Рейс От До Самолет Дата ZX 001 Москва Берлин Boeing 737 11.12.2013 ZX 002 Москва Санкт-Петербург Airbus A321 12.12. 2013 ZX 003 Санкт-Петербург Берлин Boeing 737 13.12. 2013 Есть дублирование! ! Нормализация – это изменение структуры базы данных, которое устраняет избыточность и предотвращает возможные нарушения целостности. Что плохо? ?
42 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 Что плохо? ?
43 2. Любое неключевое поле должно зависеть от ключа : Номер Автомобиль Владелец Телефон А123АА47 «Лада-Калина» Иванов 155-77-23 Т234ТТ78 «Ока» Петров 277-34-67 В345ВВ98 «Мерседес» Васильев 322-98-44 А345 CC 47 «Ауди» Иванов 155-77-23 Автомобили Владельцы Номер Автомобиль Владелец Код Фамилия Телефон А123АА47 «Лада-Калина» 1 1 Иванов 155-77-23 Б234ББ78 «Ока» 2 2 Петров 277-34-67 В345ВВ98 «Мерседес» 3 3 Васильев 322-98-44 А345 CC 47 « Ауди » 1 N 1 Что плохо? ?
44 3. Не должно быть одинаковых по смыслу полей : Дата Бананы Апельсины Яблоки 21.05.2013 120 78 101 22.05.2013 153 99 65 23.05.2013 87 55 123 Продажи Товары Дата Товар Продано Код Название 21.05.2013 1 120 1 Бананы 21.05.2013 2 78 2 Апельсины 21.05.2013 3 101 3 Яблоки 22.05.2013 1 153 … … … Проблема: добавить новый товар! ! N 1 Что плохо? ?
45 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 прибыль вычисляется через запрос! Что плохо? ?
46 усложнение структуры ( > 40-50 таблиц – много!) замедляется поиск (нужно «собирать» данные из разных таблиц) устранено дублирование все изменения делаются в одном месте некоторая защита от опечаток (выбор из списка) СУБД поддерживает ссылочную целостность
47 Выполните нормализацию: Год Животные Район Количество 2009 белки Нижняя Балка 12 2009 бурундуки Верхняя Балка 5 2010 еноты Нижняя Балка 7 2010 еноты Овраг 3 2010 белки Верхняя Балка 10
48 Выполните нормализацию: Год спуска на воду Название Проект Экипаж 1980 Удалой 1155 220 чел. 1985 Адмирал Трибуц 1155 220 чел. 1987 Североморск 1155 220 чел. 1982 Москва 1164 510 чел. 1983 Варяг 1164 510 чел.
49 Выполните нормализацию: Год Изготовитель Город Модель Скорость Цена 2007 ВАЗ Тольятти 1119 165 км/ч 120000 р. 1995 ВАЗ Тольятти 11113 130 км/ч 50000 р. 1992 КАМАЗ Набережные Челны 5320 90 км / ч 200000 р. 2006 КАМАЗ Набережные Челны 55102 90 км / ч 450000 р. 2007 БелАЗ Жодино 75600 64 км / ч 1200000 р.
50 Выполните нормализацию: Страна Фамилия Инструмент Автор произведения Место Россия Иванов фортепьяно Рахманинов 1 Россия Петров флейта Лист 2 Германия Шмидт скрипка Моцарт 3 США Смит скрипка Рахманинов 4 США Браун гобой Моцарт 5
