Курс лекций Базы данных часть 1

Базы данных

преподаватель Никитин М.Е.

§ 12. Информационные системы § 13. Таблицы § 14. Многотабличные базы данных § 15. Реляционная модель данных § 16. Работа с таблицей

Базы данных

§ 12. Информационные системы

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

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

Задачи :

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

БД и СУБД

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

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

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

БД и СУБД

Задачи СУБД :

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

БД

СУБД

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

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

ИС

локальные

удалённые

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

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

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

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

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

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

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

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

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

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

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

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

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

?

Что плохо?

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

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

БД :

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

метаданные

данные

7

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

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

сервер

СУБД

БД

СУБД

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

!

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

8

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

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

сервер

запрос

прикладная

программа

БД

ответ

СУБД

прикладная

программа

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

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

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

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

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

9

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

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

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

бесплатно!

MS SQL Server

Firebird ( www.firebirdsql.org )

PostgreSQL ( www . postgresql . org )

MySQL ( www . mysql . com )

10

Транзакции

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

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

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

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

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

§ 13. Таблицы

Базы данных

Таблицы

Примеры :

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

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

275-75-75

поля

Фамилия

Имя

Иванов

Адрес

Петров

Петр

Телефон

Васильев

Суворовский пр., д. 32, кв. 11

Василий

Иван

Кутузовский пр., д. 12, кв. 20

275-75-75

Нахимовский пр., д. 23, кв. 33

276-76-76

277-77-77

записи

Типы полей :

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

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

13

Ключ

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

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

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

?

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

?

14

Ключ

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

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

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

Дата

Время

21.07.2012

12:00

21.07.2012

Температура

15:00

Влажность

…

25

…

Скорость ветра

23

75

70

4

…

3

…

…

?

Какой ключ?

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

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

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

15

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

Номер

Фамилия

1

2

Имя

Иванов

Петров

3

Петр

Адрес

Телефон

Суворовский пр., д. 32, кв. 11

Васильев

Василий

275-75-75

Кутузовский пр., д. 12, кв. 20

Иван

276-76-76

Нахимовский пр., д. 23, кв. 33

277-77-77

?

Какой ключ?

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

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

16

Поиск в БД

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

Код

Фамилия

1

Сидоров

2

Ветров

…

1024

Померанцев

Иванов?

1024 сравнения!

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

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

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

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

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

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

Иванов?

…

255

Журов

…

383

Игнатьев

…

512

Ковалев

…

1

Андреев

…

255

Журов

…

512

…

Ковалев

1024

Яшин

1

2

Андреев

Барсуков

…

512

Ковалев

…

1023

Юрьев

1024

Яшин

?

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

11 сравнений!

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

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

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

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

Номер

Фамилия

1

Имя

2

Иванов

Адрес

Петров

3

Петр

Василий

Васильев

Телефон

Суворовский пр., д. 32, кв. 11

Кутузовский пр., д. 12, кв. 20

Иван

275-75-75

276-76-76

Нахимовский пр., д. 23, кв. 33

277-77-77

Фамилия

Васильев

Номера записей

Иванов

3

1

Петров

2

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

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

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

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

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

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

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

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

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

Задача

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

Номер

1

Дата

2

12.09.13

Заказ

12

Товар

12.09.13

3

Количество, т

13.09.13

Ананасы

13

4

13.09.13

Апельсины

14

12

5

Ананасы

12

14

13.09.13

15

Бананы

15

13

Апельсины

11

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

Базы данных

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

Альбомы

Код

Название

1

Группа

Реки и мосты

2

В круге света

Год

Машина времени

3

Машина времени

Группа крови

1987

Число композиций

4

16

1988

Кино

Последний герой

11

1988

Кино

11

1989

10

?

Что плохо?

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

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

Группы

Код

Название

1

Год создания

Машина времени

2

1969

Кино

1981

Альбомы

Код

Название

1

Реки и мосты

Код группы

2

3

В круге света

1

Год

1987

Группа крови

4

1

Число композиций

16

Последний герой

2

1988

1988

11

2

11

1989

10

?

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

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

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

Группы

Альбомы

Код

Код

Название

Название

Код группы

Год создания

Год

Число композиций

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

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

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

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

?

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

СУБД :

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

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

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

ключ

Группы

Альбомы

Код

Код

Название

Название

Год создания

Код группы

Год

Число композиций

1

N

не ключ

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

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

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

Сотрудники

Код

1

Фамилия

Иванов

Имя

2

Петров

Отчество

Петр

3

Сидорович

Сидоров

Секретно

Сидор

Иван

Иванович

Код

1

Петрович

Зарплата

20 000 р.

2

3

30 000 р.

40 000 р.

ключ

Секретно

Сотрудники

Код

Код

Фамилия

Зарплата

Имя

Отчество

1

1

ключ

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

Заказы

Блюда

Номер

Код

Дата

Название

Цена

N

N

?

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

?

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

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

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

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

Связь N:N

Заказы

Заказано

Блюда

Номер

Код

Код

Дата

Номер заказа

Название

Цена

Код блюда

1

1

N

N

Пример :

Заказы

Номер

Заказано

Блюда

Код

1

Код

Дата

1

2

11.12.12

Название

Номер заказа

1

Цена

Код блюда

2

2

борщ

12.12.12

1

3

3

80 р.

бифштекс

1

1

4

4

1

110 р.

гуляш

3

4

2

5

чай

5

70 р.

1

10 р.

6

кофе

2

7

50 р.

2

2

2

2

5

?

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

Задачи

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

Заказчики

Код

Название

1

2

Код города

ООО «Альфа»

Города

3

ЗАО «Бета»

3

2

Код

4

ООО «Гамма»

1

Название

5

ОАО «Дельта»

3

Москва

2

2

ООО «Каппа»

Санкт-Петербург

3

1

Пермь

4

Воронеж

5

Липецк

Задачи

Заказчики

Код

Название

1

2

Код города

ООО «Альфа»

Города

ЗАО «Бета»

3

3

2

Код

ООО «Гамма»

4

Название

1

5

ОАО «Дельта»

3

Москва

2

ООО «Каппа»

2

Санкт-Петербург

3

1

Пермь

4

Воронеж

5

Липецк

Заказы

Накладная

Код заказчика

1011

1012

3

Артикул

Кол-во упаковок

5

1013

7576

10

7576

4

1014

20

1

1015

3889

3

25

7825

30

7576

10

Определите:

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

Товары

Артикул

Название

7576

Цена за упаковку

2325

Бумага

3889

150 руб.

Карандаши

2987

200 руб.

Фломастеры

350 руб.

Дневники

7825

400 руб.

Пеналы

250 руб.

Задачи

Дети

Персоны

Код родителя

23

Код ребенка

Код

13

71

71

ФИО

85

Иванов Т.М.

85

23

Пол

13

Пановко И.Т.

М

82

23

Черненко И.А.

М

42

95

13

85

13

23

Ж

Пановко А.И.

42

Ж

Иванова А.И.

82

96

Ж

82

10

Пановко Н.Н.

95

Ж

10

95

…

Черненко А.Н.

M

Фукс Т.Н.

…

10

Фукс Н.А.

…

Ж

…

М

…

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

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

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

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

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

Задачи

Дети

Код родителя

Персоны

Код ребенка

Код

98

ФИО

86

83

86

79

Сизых И.Т.

83

Пол

13

Сизых А.И.

М

50

86

50

М

Малых А.Т.

79

13

83

79

50

Сидоров Т.М.

Ж

23

Сидоров А.Т.

23

М

13

13

М

98

23

Малых И.И.

98

13

86

11

Симоняк Т.Н.

Ж

11

…

…

Симоняк Н.И.

Ж

…

М

…

…

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

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

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

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

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

Задачи

Дети

Код родителя

Персоны

Код

11

Код ребенка

83

ФИО

11

11

12

94

12

Косарева Л.П.

Пол

83

12

Левитин И.А.

Ж

24

24

Шумахер А.Ф.

М

45

94

56

Бланш А.А.

Ж

56

115

Васильева М.А.

М

140

56

83

Ж

162

83

94

Левитин Б.И.

Левитина В.И.

140

115

83

М

162

Ж

Кузнецов А.П.

140

94

186

94

М

Левитина Р.Б.

162

Ж

171

201

115

Левитин Л.Б.

М

Гайдарова З.Н.

186

115

186

…

201

201

Мурина С.А.

Ж

…

Кузнецов П.А.

Ж

…

…

М

…

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

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

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

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

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

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

Базы данных

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

Требуется:

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

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

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

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

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

кортеж

Эдгар Кодд

38

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

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

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

отношение

сущность

атрибуты

Группы

Название

Лидер

Машина времени

Год создания

Кино

А. Макаревич

Аквариум

В. Цой

1969

Б. Гребенщиков

1981

1972

кортеж

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

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

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

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

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

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

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

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

Рейс

От

ZX 001

ZX 002

До

Москва

Москва

ZX 003

Самолет

Берлин

Boeing 737

Дата

Санкт-Петербург

Санкт-Петербург

11.12.2013

Берлин

Airbus A321

12.12. 2013

Boeing 737

13.12. 2013

?

!

Что плохо?

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

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

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

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

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

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

Номер

Автомобиль

А123АА47

Т234ТТ78

Владелец

«Лада-Калина»

В345ВВ98

«Ока»

Иванов

Телефон

155-77-23

Петров

А345CC47

«Мерседес»

«Ауди»

277-34-67

Васильев

322-98-44

Иванов

155-77-23 

?

Что плохо?

1

N

Автомобили

Номер

А123АА47

Автомобиль

Б234ББ78

Владелец

«Лада-Калина»

В345ВВ98

Владельцы

«Ока»

1

Код

А345CC47

2

«Мерседес»

Фамилия

«Ауди»

1

3

1

2

Телефон

Иванов

3

Петров

155-77-23

277-34-67

Васильев

322-98-44

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

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

Дата

21.05.2013

Бананы

Апельсины

22.05.2013

120

Яблоки

78

153

23.05.2013

99

87

101

55

65

123

!

?

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

Что плохо?

N

1

Продажи

Дата

21.05.2013

Товар

21.05.2013

Продано

1

Товары

21.05.2013

120

2

Код

22.05.2013

78

3

…

1

101

1

Название

…

153

Бананы

2

3

…

Апельсины

Яблоки

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

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

Дата

Доходы

03.2013

Расходы

155

02.2013

Прибыль

128

178

01.2013

27

105

194

159

73

35

!

?

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

Что плохо?

Дата

03.2013

Доходы

Расходы

155

02.2013

01.2013

128

178

105

194

159

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

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

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

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

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

Задача

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

Год

Животные

2009

Район

белки

2009

2010

Количество

Нижняя Балка

бурундуки

2010

еноты

Верхняя Балка

12

5

Нижняя Балка

еноты

2010

7

Овраг

белки

3

Верхняя Балка

10

Задача

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

Год спуска на воду

Название

1980

Проект

Удалой

1985

1987

Экипаж

1155

Адмирал Трибуц

1982

Североморск

1155

220 чел.

220 чел.

1155

Москва

1983

220 чел.

1164

Варяг

510 чел.

1164

510 чел.

Задача

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

Год

Изготовитель

2007

1995

Город

ВАЗ

Модель

1992

Тольятти

ВАЗ

КАМАЗ

Тольятти

1119

Скорость

2006

165 км/ч

11113

Цена

Набережные Челны

2007

КАМАЗ

130 км/ч

120000 р.

5320

Набережные Челны

БелАЗ

90 км/ч

50000 р.

55102

Жодино

200000 р.

90 км/ч

75600

450000 р.

64 км/ч

1200000 р.

Задача

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

Страна

Россия

Фамилия

Россия

Иванов

Инструмент

фортепьяно

Автор произведения

Петров

Германия

Место

Шмидт

Рахманинов

флейта

США

Смит

Лист

скрипка

1

США

Моцарт

2

скрипка

Браун

3

Рахманинов

гобой

4

Моцарт

5

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

Базы данных

Базы данных в OpenOffice.org Base

«Форк» (ответвление): LibreOffice ( www.libreoffice.org )

Файлы *.odb

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

Базы данных OO Base

2×ЛКМ

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

выделено

новая запись

общее количество

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