FROM ORDERS
WHERE DATE_ORDER< '17.01.03'
Инструкция удаления строк имеет формат вида:
DELETE FROM <имя таблицы> [WHERE <условие>]
Результатом выполнения оператора DELETE является удаление из указанной таблицы строк, которые удовлетворяют условию, определенному предложением WHERE. Если необязательный операнд WHERE опущен, т. е. условие отбора удаляемых записей отсутствует, удалению подлежат все записи таблицы.
Пример 3.47. Удаление строк
Удалить из таблицы ORDERS сведения о старых заказах.
DELETE FROM ORDERS
WHERE DATE_ORDER<'17.01.03'
Подробнее с возможностями SQL можно ознакомиться в специальной литературе по базам данных, или в документации конкретной СУБД.
3.4. Проектирование реляционных баз данных
При проектировании реляционной базы данных разработчику необходимо решить вопрос о наиболее эффективной структуре данных. Основная цель проектирования БД – это сокращение избыточности хранимых данных, а следовательно, экономия объема используемой памяти, уменьшение затрат на многократные операции обновления избыточных копий и устранение возможности возникновения противоречий из-за хранения в разных местах сведений об одном и том же объекте.
Проектирование должно быть эффективным, т. е. обеспечивать минимальное дублирование данных, удобство их обработки и обновления. Для удовлетворения этих требований необходимо определить, из каких отношений должна состоять БД, какие атрибуты должны входить в эти отношения.
Например, рассмотрим отношение R (табл. 11).
Таблица 11
Отношение R
| Код_материала | Наименование | Тип | Код_модели |
| Ткань пальтовая "Ассоль" | п/ш | ||
| Ткань пальтовая "Ассоль" | п/ш | ||
| Ткань подкладочная "Фея" | п/э | ||
| Ткань подкладочная "Фея" | п/э | ||
| Ткань подкладочная "Фея" | п/э | ||
| Ткань подкладочная "Фея" | п/э | ||
| Драп пальтовый "Бриз" | ч/ш |
Можно заметить, что таблица спроектирована не совсем удачно. В четырех кортежах, соответствующих материалу с кодом 210 повторяется одна и та же информация о наименовании и типе ткани. Проблема возникает из-за того, что одна и та же ткань может использоваться в разных моделях. Такое дублирование данных называется избыточностью данных. Избыточность данных вызывает нежелательные явления, возникающие в процессе работы с базой данных, называемые аномалиями.
Предположим, что тип ткани подкладочной "Фея" указан неправильно. Тогда необходимо внести изменения не в один кортеж, а во все четыре (представьте, сколько может быть кортежей в реальной БД!). Такая ситуация, при которой изменение значения одного данного, может повлечь за собой просмотр и редактирование нескольких строк таблицы, называется аномалией модификации (редактирования).
Далее предположим, что ткани подкладочной в течение какого-либо времени не было на складе и модели, в которых она должна была участвовать уже пошиты. Если принимается решение об удалении всех сведений о пошитых моделях, то информация о подкладочной ткани тоже утрачивается. Ситуация, заключающаяся в том, что при удалении каких-либо данных из таблицы может исчезнуть и информация, напрямую не связанная с удаляемой, называется аномалией удаления.
Возможна и другая ситуация: приобретена ткань, но пока еще не решено, в какой модели она будет участвовать. Поэтому, если мы хотим избежать пустых (неопределенных) значений в кортежах, информацию о ней мы не можем ввести в таблицу. Такая ситуация, при которой невозможно ввести одни данные из-за отсутствия других данных, называется аномалией ввода.
Избавится от избыточности данных позволяет нормализация базы данных. Нормализация – это процесс преобразования отношения путем декомпозиции (разбиения) его на два или более отношений, имеющих лучшие свойства. Окончательная цель нормализации сводится к получению такого проекта базы данных, в котором каждый факт появляется лишь в одном месте, т. е. исключена избыточность информации.
Нормализации – это классический метод проектирования реляционной базы данных. Исходной точкой здесь является представление предметной области в виде одного или нескольких отношений, и на каждом шаге проектирования производится некоторый набор схем отношений, обладающих лучшими свойствами. Процесс проектирования представляет собой процесс нормализации схем отношений, причем каждая следующая нормальная форма обладает лучшими свойствами по сравнению с предыдущей.
Каждой нормальной форме соответствует некоторый набор ограничений. Отношение находится в определенной нормальной форме, если оно удовлетворяет набору ограничений этой формы. Основные свойства нормальных форм:
Ø каждая следующая нормальная форма в некотором смысле лучше предыдущей;
Ø при переходе к следующей нормальной форме свойства предыдущих нормальных форм сохраняются.
В теории реляционных баз данных обычно выделяется следующая последовательность нормальных форм:
Ø первая нормальная форма (1NF);
Ø вторая нормальная форма (2NF);
Ø третья нормальная форма (3NF);
Ø нормальная форма Бойса-Кодда (BCNF);
Ø четвертая нормальная форма (4NF);
Ø пятая нормальная форма, или нормальная форма проекции-соединения (5NF или PJ/NF).
Процесс нормализации основан на понятии функциональной зависимости. Функциональные зависимости позволяют накладывать определенные ограничения на реляционную схему. Идея состоит в том, что значение одного атрибута в кортеже определяет значение другого атрибута. Например, в каждом кортеже отношения R Код_материала определяет Наименование; Код_материала определяет Тип (табл. 11). Можно записать функциональные зависимости:
Код_материала ®Наименование
Код_материала ®Тип
Для дальнейшего изложения нам потребуется несколько определений.
Определение 1. Функциональная зависимость
Пусть A и B – атрибуты в отношении R. Атрибут В функционально зависит от атрибута А, если в любой момент времени каждому значению атрибута А соответствует в точности одно значение атрибута В. Функциональная зависимость записывается следующим образом: A®B. Данная запись означает, что если два кортежа в таблице R имеют одно и тоже значение атрибута A, то они имеют одно и тоже значение атрибута B. Атрибут в левой части называется детерминантом, т.к. его значение определяет значение атрибута в правой части. Ключи таблицы являются детерминантами.
Определение 2. Неключевой атрибут
Неключевым атрибутом называется любой атрибут отношения, не входящий в состав первичного ключа
Определение 3. Полная функциональная зависимость
Функциональная зависимость называется полной, если неключевой атрибут зависит от всего составного ключа и не зависит от его частей.
Определение 4. Транзитивная функциональная зависимость
Если атрибут B функционально зависит от атрибута A (A®B), а атрибут C функционально зависит от атрибута B (B®C), но обратная зависимость отсутствует, то говорят, что атрибут С зависит от А транзитивно.
Определение 5. Взаимно независимые атрибуты
Два или более атрибута взаимно независимы, если ни один из этих атрибутов не является функционально зависимым от других.
Определение 6. Отношение находится в первой нормальной форме (1NF), если значения его атрибутов атомарны. Исходное отношение строится таким образом, чтобы оно было в 1NF.
Определение 7. Отношение находится во второй нормальной форме (2NF), если выполняются ограничения первой нормальной формы и каждый неключевой атрибут функционально полно зависит от всего первичного ключа.
Определение 8. Отношение находится в третьей нормальной форме (3NF), если выполняются ограничения второй нормальной формы и все неключевые атрибуты взаимно независимы и полностью зависят от первичного ключа (т. е. в отношении отсутствуют транзитивные зависимости неключевых атрибутов от первичного ключа).
Проектирование базы данных рассмотрим на следующем примере. Пусть имеются сведения о поставках некоторой фирмой материалов. Сформируем исходное отношение Поставки (табл. 12).
Таблица 12
Таблица Поставки
| Код_зака-за | Код_ма-териала | Код_клиен-та | Город клиента | Количество | Дата поставки | ||||
| BN | Омск | 25.07.02 | |||||||
| BN | Омск | 25.07.02 | |||||||
| BR | Москва | 12.08.02 | |||||||
| BR | Москва | 12.08.02 | |||||||
| Окончание табл. 12 | |||||||||
| BR | Москва | 12.08.02 | |||||||
| BR | Москва | 12.08.02 | |||||||
| BR | Москва | 14.08.02 | |||||||
| BR | Москва | 14.08.02 | |||||||
| BR | Москва | 14.08.02 | |||||||
| BS | Тюмень | 26.08.02 | |||||||
| BS | Тюмень | 26.08.02 | |||||||
| BN | Омск | 4.09.02 | |||||||
| BN | Омск | 4.09.02 | |||||||
| AN | Улан-Удэ | 18.09.02 | |||||||
| AN | Улан-Удэ | 18.09.02 | |||||||
В качестве первичного ключа выберем составной ключ Код_заказа-Код_материала, однозначно определяющий каждый кортеж отношения. Данное отношение находится в 1NF, т. к. все значения столбцов являются атомарными. Эта таблица содержит избыточные данные. например, одни и те же сведения о клиенте повторяются в записи о каждом заказанном материале. Результатом избыточности являются следующие аномалии:
Ø адрес клиента можно ввести в базу данных тогда, когда он заказал хотя бы одну ткань;
Ø при удалении записи о заказанной ткани одновременно удаляются сведения о заказе и клиенте, его разместившем;
Ø при смене адреса клиента, необходимо обновлять все записи и заказанных им материалах.
Напомним, что отношение находится во второй нормальной форме, если оно находится в первой нормальной форме и его неключевые атрибуты полностью зависят от всего первичного ключа. В таблице Поставки неключевые атрибуты Код_клиента, Город_клиента, Дата поставки зависят от атрибута Код_заказа, являющегося частью составного ключа. Поэтому отношение Поставки не соответствует второй нормальной форме.
Для того чтобы перейти от первой ко второй нормальной форме, необходимо выполнить следующие шаги:
Ø определить, на какие части можно разбить первичный ключ, так, чтобы некоторые из неключевых атрибутов функционально полно зависели от одной из этих частей;
Ø создать новое отношение для каждой такой части ключа и группы зависящих от нее атрибутов и переместить их в это отношение (т. е. построить проекции на части составного первичного ключа и атрибуты, зависящие от этих частей). Часть бывшего ключа станет при этом первичным ключом нового отношения;
Ø удалить из исходной таблицы атрибуты, перемещенные в другие отношения, кроме тех из них, которые станут внешними ключами (построить проекцию без атрибутов, находящихся в частичной функциональной зависимости от первичного ключа).
Для приведения исходной таблицы ко второй нормальной форме поля Код_заказа, Код_клиента, Город_клиента, Дата_поставки перемещаются в новую таблицу Поставки1, при этом Код_заказа – первичный ключ новой таблицы.
Вторая таблица Заказы будет содержать составной первичный ключ Код_заказа-Код_материала и поле Количество.
В результате новые таблицы будут выглядеть следующим образом (табл. 13-14):
Таблица 13 Таблица 14
Поставки1 Заказы
| Код_за-каза | Код_кли-ента | Город клиента | Дата поставки | Код_за-каза | Код_ма-териала | Коли-чество | |
| BN | Омск | 25.07. 02 | |||||
| BR | Москва | 12.08. 02 | |||||
| BR | Москва | 14.08. 02 | |||||
| BS | Тюмень | 26.08. 02 | |||||
| BN | Омск | 4.09.02 | |||||
| AN | Улан-Удэ | 18.09. 02 | |||||
Проанализируем полученные таблицы. В таблице Заказы не наблюдается явная избыточность данных. Однако для таблицы Поставки1 можно указать следующие аномалии:
Ø адрес конкретного клиента может содержаться в базе только тогда, когда есть заказы;
Ø удаление сведений о заказе в таблице Поставки1 приведет к удалению сведений о клиентах;
Ø при изменении адреса заказчика придется обновить все кортежи в таблице Поставки1.
Устранить эти аномалии позволяет третья нормальная форма. Считается, что таблица соответствует третьей нормальной форме, если она находится во второй нормальной форме и ее неключевые атрибуты взаимно независимы и зависят только от первичного ключа. В отношении Поставки1 существует транзитивная зависимость между неключевыми атрибутами Город_клиента и Код_клиента (Код_клиента ® Город_клиента).
Для перехода от второй нормальной формы к третьей необходимо исключить транзитивные зависимости. Для этого требуется выполнить следующие шаги:
Ø определить все атрибуты (или группы атрибутов), от которых зависят другие атрибуты (выявить транзитивные зависимости);
Ø создать новое отношение для каждого такого атрибута и для группы зависящих от него атрибутов и переместить их в это отношение (т. е. построить проекцию отношения на атрибуты, являющиеся причиной транзитивной зависимости). Атрибут, от которого зависят все остальные перемещенные атрибуты, станет при этом первичным ключом нового отношения;
Ø удалить перемещенные атрибуты из исходного отношения, оставив лишь те, которые станут внешними ключами.
Для приведения таблицы Поставки1 к третьей нормальной форме создадим новую таблицу Клиенты (табл. 15) и переместим в нее атрибуты Код_клиента и Город_клиента. Атрибут Город_клиента изтаблицы Поставки1 удалим, а атрибут Код_клиента оставим в качестве внешнего ключа (табл. 17). Таблицу Заказы оставим без изменения (табл. 16).
Таблица 15 Таблица 16
Клиенты Заказы
| Код_кли-ента | Город клиента | Код_за-каза | Код_ма-териала | Количество | ||
| BN | Омск | |||||
| BR | Москва | |||||
| BS | Тюмень | |||||
| AN | Улан-Удэ | |||||
| Окончание табл. 16 | ||||||
Таблица 17
Поставки 2
| Код_заказа | Код_клиента | Дата_поставки |
| BN | 25.07.02 | |
| BR | 12.08.02 | |
| BR | 14.08.02 | |
| BS | 26.08.02 | |
| BN | 4.09.02 | |
| AN | 18.09.02 |
На практике, в большинстве случаев процесс проектирования заканчивается построением третьей нормальной формы. Например, для нашего примера, после проведения нормализации можно заметить следующие улучшения:
Ø сведения о клиенте можно хранить, если клиент не сделал ни одного заказа;
Ø сведения о заказанном материале можно удалить, не опасаясь удаления данных о клиенте и заказе;
Ø изменение адреса клиента или даты регистрации заказа теперь требуют изменения только одной записи.
Существуют нормальные формы более высокого порядка.
Подробнее с технологией нормализации можно ознакомиться в литературе по теории реляционных баз данных [5, 8, 10, 14, 18–20, 26–29, 31].
Контрольные вопросы и задания
1. Привести различия между иерархической и сетевой моделями данных.
2. Охарактеризовать реляционную модель данных.
3. Перечислить составные элементы реляционной модели.
4. Что такое первичный ключ?
5. Перечислить условия, при соблюдении которых таблицу можно считать отношением.
6. В чем суть целостности сущностей?
7. Определить условия ссылочной целостности.
8. Определить различие между первичным и внешним ключами.
9. Какое назначение внешних ключей?
10. В чем различия между реляционной алгеброй и реляционным исчислением?
11. Перечислить операции реляционной алгебры.
12. Назвать и охарактеризовать дополнительные операции реляционной алгебры, предложенные К. Дж. Дейтом.
13. Дать характеристику языку QBE.
14. Перечислить операторы языка SQL.
15. Выполнить сравнение языков QBE и SQL.
16. В чем заключается процесс нормализации?
17. Привести примеры аномалий ввода, модификации, удаления данных.
18. Что такое функциональные зависимости?
19. Перечислить требования нормальных форм.
20. Описать этапы перехода от первой нормальной формы ко второй.
21. Какая последовательность действий необходима для перехода к третьей нормальной форме?
22. Составить алгебраическое выражение (или последовательность реляционных операций), необходимое для выполнения следующих запросов к базе данных поставщиков и материалов:
Ø получить полную информацию обо всех поставках в Москве;
Ø получить номера материалов, поставляемых поставщиком из Тюмени;
Ø получить такие пары номеров материалов, которые одновременно поставляются одним поставщиком;
Ø получить общее количество товаров, поставляемых поставщиком S1;
Ø получить названия поставщиков, которые поставляют по крайней мере один материал типа п/ш;
Ø получить типы материалов, поставляемых поставщиком S1.
19. В компании есть несколько отделов, в каждом отделе есть несколько сотрудников, несколько проектов, несколько кабинетов. Каждый сотрудник имеет план работы (несколько заданий). Для таких заданий существует ведомость полученных вознаграждений. В каждом кабинете есть несколько телефонов. В базе данных должна содержится следующая информация:
Ø для каждого отдела: номер отдела, бюджет и номер сотрудника, который возглавляет этот отдел;
Ø для каждого сотрудника: номер сотрудника, номер текущего проекта, номер кабинета, номер телефона, название заданий вместе с датами и размерами всех оплат;
Ø для каждого проекта: номер проекта и бюджет;
Ø для каждого кабинета: номер кабинета, площадь, номера всех телефонов, установленных в кабинете.
Составить множество нормализованных отношений для представления этой информации.
20. Создать реляционную схему базы данных предприятия сферы сервиса (парикмахерской, мастерской по ремонту бытовой техники, компьютерной фирмы и т.п.). Схема должна содержать как минимум семь таблиц, приведенных к третьей нормальной форме. Обосновать выбор структур таблиц, их взаимосвязь. Описать процесс нормализации таблиц.
21. Дать определение данных на SQL для базы данных поставщиков и материалов.
22. Сформулировать на SQL для базы данных поставщиков и материалов следующий запрос: "Получить названия поставщиков, поставляющих материал M2".
23. Сформулировать на SQL следующее обновление базы данных поставщиков и материалов: "Изменить тип материала п/ш на ч/ш", "Удалить все проекты, для которых нет поставок".
24. Сформулировать на QBE следующий запрос: "Вывести список клиентов, чья общая сумма годовых заказов превышает 70000".
25. Сформулировать на QBE следующий запрос: "Перечислить название и цену товаров, поставка которых осуществляется после 1.03.03".
26. Сформулировать на QBE следующее изменение базы данных: "Удалить сведения о клиенте с номером 3101".
4. Семантическое моделирование
Разработка базы данных в терминах реляционной модели часто сводится к сложному и неудобному для проектировщика процессу, поскольку эти модели не содержат достаточно средств для представления смысла данных. Такое положение вещей приводит к замедлению процесса разработки БД и является источником потенциальных ошибок. Семантика реальной предметной области должна независимым от модели способом представляться в сознании ее создателя. Поэтому в последнее время получило развитие семантическое моделирование. Основная цель такого подхода – организация интерфейса проектировщика, а также конечного пользователя с информационной системой на уровне представлений в пределах предметной области, а не на уровне структур данных. Наиболее популярными в настоящее время инфологическими, или как их еще называют семантическими, моделями являются объектно-ориентированная модель и модель "сущность-связь". Сочетая в себе предметную наглядность и теоретическую обоснованность, они являются мощным инструментом проектирования баз данных. Данные подходы находят все большее воплощения в конкретных системах управления базами данных.
4.1. Объектно-ориентированное проектирование
Для определения структуры БД в объектно-ориентированных терминах многими специалистами используется язык ODL. Главное назначение ODL – обеспечить запись объектно-ориентированных проектов с последующим прямым переводом в выражения объектно-ориентированной СУБД (OODBMS). Как правило, основным языком таких систем является C++ или Object Pascal, поэтому ODL необходимо переводить в выражения этих языков. ODL соответствует им обоим (но больше C++), поэтому указанный перевод достаточно прост. Значительно сложнее осуществлять перевод из ODL или проектов, реализованных в терминах модели "сущность-связь", в выражения, систем управления реляционными базами данных (RDBMS).
4.1.1. Представление объектов
При объектно-ориентированном проектировании считается, что, мир состоит из объектов – определенных наблюдаемых сущностей. Например, люди, животные, машины, авиарейсы, факультеты в институте, здания и т. д. могут пониматься как объекты. При этом каждый объект можно уникальным образом идентифицировать, чтобы отличить его от любого другого объекта.
Для построения адекватной модели предметной области, как правило, необходимо определить классы объектов, в рамках которых объекты обладают сходными свойствами. Понятия "объект" и "класс" в БД по сути дела совпадают с этими понятиями в языках объектно-ориентированного программирования (ООП).
Элементы и понятия реального мира, представляемые объектами класса, должны быть сходными. Например, можно сгруппировать всех клиентов ателье в один класс, а все заказы в другой. Нет смысла объединять клиентов и заказы в одном классе, так как между ними практически нет ничего общего, и они играют разные роли в сфере деятельности ателье.
Все объекты одного класса должны иметь одинаковый набор свойств. При программировании на объектно-ориентированном языке можно представить объекты в виде записей, как показано на рис.45.
