Текст книги "Базы данных. Курс лекций. Учебное пособие"

Лекция 5
Нормализация таблиц БД

При создании БД необходимо выполнить анализ предметной области, для которой разрабатывается БД. Процесс разработки БД является циклическим, т. е. на разных этапах происходят возвраты на более ранние этапы с целью коррекции. Субъективные взгляды разработчика всегда могут найти отражение в БД, но есть ряд объективных требований, соблюдение которых всегда может принести пользу. К таким требованиям относится нормализация БД. Процесс нормализации позволяет устранить избыточность данных и ускорить доступ к ним.

В основе нормализации лежит одна основная идея: поля таблицы должны зависеть только от ключа таблицы и ни от чего другого. Если это не так, то следует разбить таблицу на отдельные таблицы [1].

Общие требования нормализации формулируются в виде пяти нормальных форм (НФ), к которым последовательно приводятся таблицы БД. На практике наиболее часто применяются только первые три НФ [10].

Рассмотрим первую нормальную форму (1НФ).

Таблица в 1НФ должна удовлетворять следующим требованиям:

1. В таблице не должно быть повторяющихся записей;

2. Каждое поле таблицы должно быть неделимым (атомарным), т. е. на пересечении строки и столбца должен быть атомарный объект;

3. В таблице должны отсутствовать повторяющиеся группы полей.

Рассмотрим пример нормализации таблицы «Продажи», в которой содержится 21 поле (табл. 3).

Таблица 3

Продажи

Номер

Поле

Тип поля

1

Фамилия

Текст

2

Имя

Текст

3

Отчество

Текст

4

Телефон

Текст

5

Факс

Текст

6

Индекс

Текст

7

Страна

Текст

8

Город

Текст

9

Адрес

Текст

10

Название предприятия

Текст

11

Руководитель предприятия

Текст

12

Web-сайт предприятия

Текст

13

E-mail предприятия

Текст

14

Код товара

Числовой

15

Дата заказа

Дата/время

16

Заказано

Числовой

17

Дата продажи

Дата/время

18

Продано

Числовой

19

Цена

Денежный

20

Категория товара

Числовой

21

Наименование товара

Текстовый

В табл. 3 каждое поле неделимое, и никакое из полей не является уникальным.

Таблица с такой структурой может иметь повторяющиеся группы полей, в которых будут записаны данные об одном и том же покупателе (поля с 1-го по 13-е). Чтобы привести таблицу к 1НФ, она разбивается на две таблицы: «Клиенты» и «Заказы», находящиеся в отношении «один-ко-многим».

Поскольку ни одно из полей исходной таблицы не было уникальным, здесь в качестве первичного ключа таблицы «Клиенты» лучше ввести новое поле – «Код клиента». Это поле будет внешним ключом в таблице «Заказы» (рис. 11).

Рис. 11. Разбиение со связью «один-ко-многим»

В таблице «Заказы» ни одно из полей не является уникальным, поэтому в качестве первичного ключа можно добавить поле «Код заказа» или использовать комбинацию трех полей – «Код клиента», «Код заказа» и «Дата заказа» – в качестве составного ключа (если один клиент делает один заказ в день). Рассмотрим второй вариант – таблицу с составным первичным ключом. Такая таблица должна удовлетворять требованиям 2-й нормальной формы (2НФ).

Таблица находится во 2НФ, если удовлетворены два условия:

1. Таблица удовлетворяет условиям 1НФ;

2. Любое неключевое поле однозначно идентифицируется полным набором ключевых полей.

Очевидно, что в таблице «Заказы» второе условие не выполняется, поскольку поля «Категория» и «Наименование товара» зависят только от поля «Код товара». Чтобы привести таблицу к 2НФ, нужно выделить эти поля в отдельную таблицу с наименованием «Товар» (рис. 12).

Рис. 12. Разбиение со связью «один-к-одному»

Рассмотрим далее таблицу «Клиенты». Здесь нет составного ключа, поэтому требования 2НФ выполнены автоматически и требуется рассматривать третью нормальную форму (3НФ).

Таблица находится в 3НФ, если она удовлетворяет условиям 2НФ и ни одно из неключевых полей таблицы не идентифицируется с помощью другого неключевого поля.

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

В таблице «Клиенты» неключевые поля «Руководитель фирмы», «Web-сайт фирмы» и «E-mail фирмы» определяются неключевым полем «Название фирмы», поэтому необходимо создать новую таблицу с названием «Фирма» (рис. 13).

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

В целом нормализация не только предоставляет преимущества, но и несет некоторые недостатки.

Рис. 13. Устранение зависимости неключевых полей

Главное достоинство состоит в том, что после нормализации в таблицах нет избыточных данных, поэтому экономится память ЭВМ (хотя появляются поля связи, присутствующие одновременно у главной и подчиненной таблиц).

В качестве недостатков могут быть названы следующие:

1. Чем шире предметная область, тем больше получается набор таблиц после нормализации. Для крупного предприятия БД может содержать сотни взаимосвязанных таблиц, что превышает возможности человеческого восприятия. Поэтому функционирование БД может стать труднообъяснимым;

2. При считывании связанных данных необходимо объединять записи в связанных таблицах, что приводит к необходимости выполнения поисковых операций. В сложных БД это может вызывать временные издержки.

Таким образом, нормализация является желательной, но в сложных БД могут появляться другие критерии, мешающие полной нормализации таблиц БД [9].

Вопросы для самопроверки

1. Каковы два основных направления использования вычислительной техники?

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

3. Когда появились первые информационные системы?

4. Когда информационные системы стали использоваться для поддержки принятия решений?

5. Какие задачи могут решать современные информационные системы?

6. Сколько уровней можно выделить в структуре управления организацией?

7. Какие задачи решаются на операционном уровне управления?

8. Какие задачи решаются на тактическом уровне управления?

9. Какие задачи решаются на стратегическом уровне управления?

10. Что такое формализуемые и неформализуемые задачи?

11. Какие задачи решают базы данных?

12. Какие задачи решают экспертные системы?

13. Что такое иерархические базы данных?

14. Что такое сетевые базы данных?

15. Что такое реляционные базы данных?

16. Каковы основные достоинства реляционных БД?

17. Как называются элементы таблицы реляционной БД?

18. Какие принципы поддерживают реляционные БД по отношению к пользователю?

19. Какие принципы используются при построении реляционных БД?

20. Что такое формы в реляционной БД?

21. Что такое отчеты в реляционной БД?

22. Что такое запросы в реляционной БД?

23. Что такое макросы в реляционной БД?

24. Какую роль играют CASE-средства при разработке реляционных БД?

25. С какой целью был разработан и используется язык SQL?

26. Чем отличаются локальные БД от серверных?

27. Какие существуют архитектуры серверных БД?

28. Чем отличается архитектура «файл-сервер» от архитектуры «клиент-сервер»?

29. Что такое первичный ключ?

30. Что такое потенциальный ключ?

31. Какие типы связей возможны между таблицами реляционной БД?

32. Какие типы связей наиболее распространены в реляционной БД?

33. В каких случаях возможно нарушение ссылочной целостности?

34. Какие механизмы могут быть использованы для обеспечения ссылочной целостности?

35. Что такое внешний ключ?

36. Зачем используется индексирование полей таблицы?

37. Зачем выполняется нормализация таблиц БД?

38. Каким требованиям должна соответствовать таблица в 1НФ?

39. Что такое составной первичный ключ?

40. Каким требованиям должна соответствовать таблица в 2НФ?

41. В каком случае требования 2НФ выполняются автоматически?

Лекция 6
Концептуальное проектирование баз данных

Существуют два подхода к проектированию реляционных БД. При первом подходе сразу создается реляционная схема БД, состоящая из определений реляционных таблиц. Затем эти таблицы нормализуются. Такой подход применяется для сравнительно простых задач.

Второй подход основан на концептуальной (семантической) модели данных, создаваемой на этапе концептуального проектирования. Затем эта модель механически преобразуется в реляционную модель, и нормализация при этом гарантируется. Такой подход применяется при проектировании достаточно сложных БД. Рассмотрим подробнее основные приемы концептуального проектирования.

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

При построении концептуальных моделей в настоящее время наиболее часто применяется объектно-ориентированный подход, т. е. главными в концептуальной модели являются объекты и отношения между ними. Часто также используется термин «ER-диаграммы» (от англ. Essence – Relation, т. е. модели «сущность – связь»).

Объект – это обозначение множества вещей одного типа: автомобилей, книг, домов и т. д.

Каждый объект принимает значения на определенной области определения (объектном множестве). Например, объект «дерево» определяется с помощью множества «сосна», «береза», «тополь» и т. п.

Иногда выделяют лексические объектные множества и абстрактные объектные множества.

Под лексическим объектным множеством понимается такое объектное множество, которое можно напечатать. Если же объектное множество напечатать нельзя, то его называют абстрактным.

Например, объект «имя» определяется с помощью лексического объектного множества, а для объекта «человек» объектное множество будет абстрактным, поскольку напечатать человека нельзя. У человека есть идентификаторы: имя, номер паспорта и т. д., но это не сам человек.

Когда концептуальная модель получает внутреннее компьютерное представление, элементы лексических объектов становятся строками символов, а элементы абстрактных объектов представляются внутренними номерами – идентификаторами. Это также называется суррогатный ключ. Такой ключ не имеет смысла вне системы [3].

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

Связь между элементами двух объектных множеств называется отношением (рис. 14).

Рис. 14. Пример отношения

Прямоугольник означает объектное множество, его составляют отдельные элементы.

Отношение может иметь имя. Отношение также является объектным множеством.

Например, пусть есть два объектных множества:

Студенты = {Иванов, Петров, Сидоров}.

Оценки = {3, 4, 5}.

С помощью этих множеств можно сформулировать отношение:

Экзаменационная оценка = ({Иванов, 3}, {Петров, 4}, {Сидоров, 5}).

Если отношение рассматривается как объектное множество, то его называют составным объектным множеством.

Рассмотрим составное множество «семейная пара». Его можно определить как отношение с именем «состоит в браке» между объектными множествами «мужчина» и «женщина» (рис. 15).

Рис. 15. Составное объектное множество

Для отношения вводится понятие мощности. Мощность отношения – максимальное количество элементов одного объектного множества, связанных с одним элементом другого объектного множества. Для приведенного выше примера мощность отношения равна 0 либо 1 в обоих направлениях. Рассмотрим отношение «Экзаменационная оценка» (рис. 16).

Рис. 16. Пример отношения с мощностью «один-ко-многим»

Здесь с одним студентом связывается только одна оценка, но одну и ту же оценку могут получить многие студенты.

Часто рассматривается максимальная мощность отношения, но в общем случае отношение может не иметь конкретной максимальной мощности.

Мощность отношения включения всегда одна и та же. Каждый элемент надмножества связан с одним или нулем элементов подмножества. Каждый элемент подмножества связан ровно с одним элементом надмножества. Например, всякий мужчина непременно является человеком, но человек может быть не только мужчиной, но и женщиной (рис. 17).

Рис. 17. Пример отношения включения

Максимальная мощность, равная 1 в одном из направлений, устанавливает соотношение «один-к-одному» или «многие-к-одному» между множествами.

Если в каком-то направлении мощность равна 1, то в этом направлении отношение называется функциональным.

Пример приведен на рис. 18.

Рис. 18. Пример функционального отношения

В направлении от студента к преподавателю это отношение является функциональным, т. е. у студента один руководитель. В другом направлении это отношение не функционально, так как один преподаватель руководит несколькими студентами.

Если максимальная мощность отношения равна 1 в обоих направлениях, то это отношение «один-к-одному».

Если максимальная мощность в одном направлении равна 1, а в другом – многим, то это отношение «один-ко-многим».

Если максимальная мощность в обоих направлениях равна многим, то это отношение «многие-ко-многим».

С каждым объектом обычно связывается несколько атрибутов, т. е. характеристик объекта.

Атрибут объекта – функциональное отношение одного объектного множества к другому объектному множеству. Иначе говоря, значение атрибута однозначно определяется для каждого объекта или не определено, т. е. максимальная мощность отношения со стороны атрибута равна 1 или 0.

Пример: у каждого человека есть только одна дата рождения и только один номер паспорта.

Если значение некоторого атрибута объекта не меняется, то его можно использовать в качестве ключа.

Ключ – значение, которое всегда можно использовать для однозначного определения элемента объектного множества.

Например, номер паспорта может использоваться в качестве ключа, а дата рождения – нет.

Важным является понятие наследования. Если объект является конкретизацией другого объекта, то он наследует все атрибуты и отношения обобщенного объекта.

Например, рассмотрим объект «человек» с атрибутами «номер паспорта», «дата рождения» и отношением «живет в».

Если ввести объект «студент» как конкретизацию объекта «человек», то «студент» наследует атрибуты и отношения объекта «человек» (рис. 19).

Если в каком-то отношении участвуют два объектных множества, то оно называется бинарным. Отношения между N объектными множествами называются N-арными.

С помощью отношений высокого порядка описываются составные объекты. Составной объект – отношение, рассматриваемое как объектное множество.

Рис. 19. Пример наследования

Отношения могут обладать атрибутами и участвовать в других отношениях [8].

Например, пусть нужно рассматривать объем продаж отдельных товаров некоторой фирмы в отдельных странах. Получается конструкция, приведенная на рис. 20.

Рис. 20. Пример составного объекта с атрибутом

Два объектных множества образуют показанный пунктиром составной объект, с которым связан атрибут «Количество», зависящий и от товара, и от страны.

Если нужно учитывать количество товара, проданного в конкретной стране за конкретный день, то можно использовать трехстороннее отношение (рис. 21).

Рассмотрим концептуальную модель для задачи «Фруктовый сад» (рис. 22).

Рис. 21. Пример трехстороннего отношения с атрибутом

Объект «сад» описывает отдельные экземпляры садов. У него есть атрибут «владелец», которому он принадлежит.

Рис. 22. Пример концептуальной модели

Каждый сад связан с объектным множеством «дерево». Каждое дерево растет в одном саду, но в саду множество деревьев. Каждое дерево имеет атрибут «год посадки», но может еще не иметь атрибута «год гибели». Каждое дерево всегда может быть только одного вида (яблоня, груша), но в саду много деревьев одного и того же вида. Каждый сорт относится только к одному виду (джонатан – сорт яблок), но один вид имеет множество сортов.

Если считать, что дерево может нести несколько сортов, то между сортами и деревьями в оба направления – отношение «один-ко-многим».

При описании той или иной предметной области может использоваться понятие концептуальных объектов, которые отличаются от физических объектов тем, что означают тип предмета, а не сам реальный предмет [6].

Например, есть концептуальный объект – книга Н. В. Гоголя «Мертвые души». Этому объекту может соответствовать физическое объектное множество, состоящее из книг с одним и тем же автором и названием, изданных в разные годы разными издательствами (рис. 23).

Множество справа оказывается зависимым объектным множеством, поскольку каждый его элемент должен быть связан с каким-то элементом левого объектного множества.

Издание здесь не является атрибутом концептуальной книги, но может входить в более широкое описание объекта «физическая книга» (рис. 24).

Рис. 23. Пример концептуального объекта

Рис. 24. Взаимосвязь концептуального и физического объекта

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

Сложные БД проектируются по частям, затем эти части соединяются вместе за счет образования новых отношений.

Лекция 7
Логическое проектирование

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

На данном этапе выполняются следующие действия:

1. Удаление связей типа «многие-ко-многим»;

2. Удаление сложных связей;

3. Удаление рекурсивных связей;

4. Удаление связей с атрибутами;

5. Удаление множественных атрибутов;

6. Перепроверка связей типа 1:1;

7. Удаление избыточных связей.

Рассмотрим эти действия подробнее.

1. Удаление связей типа «многие-ко-многим». Если в концептуальной модели присутствуют связи «многие-ко-многим» (M: N), то их следует устранить путем определения некоторой промежуточной сущности. Связь типа M: N заменяется двумя связями типа «один-ко-многим» (1:M), устанавливаемыми со вновь созданной сущностью. Пример показан на рис. 8, 9.

2. Удаление сложных связей. Сложной называется связь, существующая между тремя и больше типами сущностей. Если в концептуальной модели присутствует сложная связь, ее следует устранить с помощью промежуточной сущности. Сложная связь заменяется необходимым количеством бинарных связей типа 1:М, устанавливаемых со вновь созданной сущностью. Например, тройная связь «Сдается внаем» (изображается ромбом) отражает отношения, существующие между оформляющим аренду работником компании, земельным участком и арендатором (рис. 25).

Рис. 25. Сложная связь

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

В нашем примере связь «Сдается внаем» можно устранить посредством введения новой слабой сущности с именем «Соглашение». Вновь созданная сущность будет связана с исходными сущностями тремя новыми бинарными связями (рис. 26).

Рис. 26. Упрощение сложной связи

3. Удаление рекурсивных связей. Рекурсивными называются такие связи, в которых сущность некоторого типа взаимодействует сама с собой. Если концептуальная модель содержит рекурсивные связи, они должны быть устранены посредством определения некоторой промежуточной сущности. Например, для отображения ситуации, когда один из работников руководит группой других работников, может быть установлена рекурсивная связь типа «один-ко-многим» (1:М).

4. Удаление связей с атрибутами. Если в концептуальной модели присутствуют связи, имеющие собственные атрибуты, они должны быть преобразованы путем создания новой сущности. Например, рассмотрим ситуацию, когда требуется фиксировать количество рабочих часов, отработанных временным персоналом каждого из отделений предприятия. Связь «Работает в» имеет атрибут с именем «Отработано часов». Преобразуем связь «Работает в» в сущность с именем «Распределение по отделам», которой назначим атрибут «Отработано часов», после чего создадим две новых связи типа 1:М.

5. Удаление множественных атрибутов. Множественными называют атрибуты, которые могут иметь одновременно несколько значений для одного и того же экземпляра сущности. Если в концептуальной модели присутствует множественный атрибут, его следует преобразовать путем определения новой сущности. Например, для отображения ситуации, когда одно и то же отделение компании имеет несколько телефонных номеров, в концептуальной модели был определен множественный атрибут «Телефонный номер», относящийся к сущности «Отделение компании». Этот множественный атрибут следует удалить, определив новую сущность «Телефон», имеющую единственный простой атрибут «Телефонный номер», и создав новую связь типа 1.

6. Перепроверка связей типа 1:1. В процессе определения сущностей могли быть созданы две различные сущности, которые на самом деле представляют один и тот же объект в предметной области приложения. Например, могли быть созданы две сущности «Отдел» и «Департамент», которые на самом деле представляют один и тот же тип объекта. Другими словами, имя «Отдел» является синонимом имени «Департамент». В подобном случае следует объединить эти две сущности в одну. Если первичные ключи объединяемых сущностей различны, выберите один из них в качестве первичного, а другой укажите как альтернативный ключ.

7. Удаление избыточных связей. Связь является избыточной, если одна и та же информация может быть получена не только через нее, но и с помощью другой связи. Всегда следует стремиться создавать минимальные модели данных, и поэтому, если избыточная связь не является очевидно необходимой, ее следует удалять. Установить, что между двумя сущностями имеется больше одной связи, довольно просто. Однако из этого еще не следует, что одна из двух связей обязательно является избыточной, поскольку обе они могут представлять различные объединения, реально существующие в организации.

При устранении избыточности доступа большое значение имеют временные показатели. Например, рассмотрим ситуацию, когда необходимо смоделировать связи между сущностями «Мужчина», «Женщина» и «Ребенок». Очевидно, что между сущностями «Мужчина» и «Ребенок» имеется два пути доступа: один – через непосредственную связь «Является отцом» и другой – через связи «Женат на» и «Является матерью». На первый взгляд, кажется, что связь «Является отцом» избыточна. Однако это утверждение может оказаться ошибочным по двум причинам. Во-первых, отец может иметь детей от предыдущего брака, а мы моделируем только текущий брак отца (через связь 1:1). Во-вторых, отец и мать могут быть вообще не женаты, или отец может быть женат на женщине, которая не является матерью данного ребенка (или же мать может быть замужем за мужчиной, который не является отцом ребенка). Поэтому все существующие взаимоотношения не могут быть смоделированы без использования связи типа «Является отцом» (рис. 27).

Рис. 27. Пример не избыточных связей