Инфологическая модель «сущность-связь»

ОГЛАВЛЕНИЕ

Введение. 6

Глава 1 Представление данных. 8

1.1 Уровни представления данных. 8

1.2 Инфологическая модель «сущность-связь». 10

1.2.1 Основные понятия. 10

1.2.2 Характеристика связей. 10

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

Глава 2 Реляционные базы данных. 12

2.1 Основные понятия. 12

2.2 Объекты реляционной структуры.. 12

2.3 Операции реляционной алгебры.. 15

2.4 Неопределенные значения. 16

2.5 Ограничения целостности. 17

2.6 Разработка реляционной базы данных. 18

2.6.1 Основные предпосылки. 18

2.6.2 Нормализация. 19

2.6.3 Нормальные формы.. 20

2.6.4 Правила нормализации. 22

2.6.5 Алгоритм нормализации. 26

2.7 Нормализация в примерах. 27

2.8 Заключение. 41

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

Глава 3 Язык структурированных запросов. 42

3.1 Основные понятия. 42

3.2 Типы данных. 43

3.3 Операции над данными и NULL. 44

3.4 Выбор данных из базы.. 45

3.5 Выбор данных из базы – оператор JOIN.. 53

3.6 Выбор данных из базы – источник данных запрос. 55

3.7 Управление структурой базы данных. 56

3.7.1 Типы команд управления структурой. 56

3.7.2 Типы объектов структуры.. 56

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

3.7.4 Удаление таблицы.. 59

3.7.5 Создание представления. 59

3.7.6 Удаление представления. 60

3.7.7 Изменение представления. 60

3.7.8 Создание триггера. 61

3.7.9 Удаление триггера. 65

3.7.10 Изменение триггера. 65

3.8 Манипулирование данными. 65

3.8.1 Ввод данных. 65

3.8.2 Изменение данных. 66

3.8.3 Удаление данных. 66

3.8.4 Ограничения целостности при манипулировании данными 67

3.9 Пример создания базы данных. 68

3.10 Заключение. 71

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

Глава 4 Задание к выполнению лабораторных работ. 72

4.1 Лабораторная работа №1. Изучение команды SELECT – простые запросы.. 72

4.2 Лабораторная работа №2. Изучение команды SELECT – запрос из нескольких источников. 79

4.3 Лабораторная работа №3. Разработка структуры базы данных. Первая часть 83

4.4 Лабораторная работа №3. Разработка структуры базы данных. Вторая часть 89

4.5 Лабораторная работа №3. Разработка системы протоколирования операций над данными реляционной таблицы с использованием триггеров 90

4.6 Лабораторная работа №2. Разработка пользовательских функций и процедур. 92

4.7 Лабораторная работа №2. Импорт данных. 94

Глава 5 Курсовая работа. 97

Библиографический список. 104

Введение

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

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

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

Например, ГОСТ 24.104 «Автоматизированные системы управления. Общие требования» [1] в подразделе «требования к информационному обеспечению АСУ» среди прочих устанавливает следующее требование: совокупность информационных массивов АСУ должна быть организована в виде баз данных на машинных носителях.

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

Понятие база данных не имеет единого общепринятого определения [2, 3, 4]. Однако, можно выделить три составляющие определения базы данных: компьютерная ориентированность данных, логическая структурированность данных и хранение как самих данных, так и описания их структуры.

Понятие база данных в современном его понимании прочно ассоциировано с понятием системы управления базами данных, которое, помимо самих данных, включает в себя еще и задачу управления доступом к ним и их структурой. Такой ассоциации предшествовало эволюционное развитие программно-аппаратных средств информационных систем.

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

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

Естественным направлением развития систем стали попытки унификации систем хранения и доступа к данным. В результате, информационные системы фактически разделились на две составляющие: собственно подсистема хранения и управления данными и подсистема пользовательского интерфейса. В настоящий момент эти подсистемы разделены и по используемому для их разработки программному обеспечению.

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

Глава 1
Представление данных

Уровни представления данных

В процессе разработки информационной системы данные, которые требуется хранить в системе, проходят несколько уровней представлений, каждый из которых соответствует определенной степени формализации описания и привязки к программно-аппаратным средствам разработки системы [2].

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

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

Слабой стороной инфологической модели является исходное ориентирование на неподготовленного человека, что собственно и приводит к снижению степени ее формализации. Модель, например, может быть сформулирована в виде словесного описания. В настоящее время для инфологического моделирования широко используются искусственные формализованные языки. В первую очередь распространены графические языки, представляющие модель в виде набора связанных между собой графических объектов, кодирующих объекты предметной области. Фактически этап разбивается на два: словесное описание системы и построение инфологической модели. В настоящий момент актуальной и повсеместно используемой является инфологическая модель сущность-связь («Entity-Relationship»), предложенная П. Ченом в 1976 г. [5, 6].

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

В результате проектирования получается полностью формализованная, описанная на языке конкретной системы управления базами данных структура. Однако, и эта модель все еще не имеет аппаратной привязки. Эту модель также называют логической моделью базы данных [2], т.к. она описывает доступ к данным по логическим именам объектов без учета их физического размещения.

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

Основные понятия

Основными элементами модели являются: сущность, атрибут, ключ и связь.

Сущность – это любой различимый объект, информацию о котором необходимо хранить в базе данных.

Атрибут – это поименованная характеристика сущности.

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

В задаче по учету автомобилей цвет будет являться атрибутом сущности автомобиль. А в базе данных завода по производству лакокрасочных покрытий цвет будет сущностью, имеющей свои атрибуты, например: рецепт.

Для сущностей и атрибутов различают тип и экземпляр. Тип определяет набор однотипных объектов, а экземпляр – это конкретный объект из этого набора. Например: для типа сущности столицы стран Москва и Париж будут являться экземплярами.

Ключ – это минимальный набор атрибутов сущности, по значениям которых можно однозначно идентифицировать любой её экземпляр. Минимальность означает, что исключение из набора любого атрибута не позволяет идентифицировать сущность по оставшимся.

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

Характеристика связей

Между двумя сущностями, например, А и В, возможны четыре вида связей.

Связь один к одному (1:1) – в каждый момент времени каждому экземпляру сущности А соответствует 1 или 0 экземпляров сущности В.

Связь один ко многим (1:М) – в каждый момент времени каждому экземпляру сущности А соответствует 1, 0 или несколько экземпляров сущности В.

Поскольку связь может быть не только в направлении от А к В, но и обратно, то появляются еще связи многие к одному (М:1) и многие ко многим (М:М).

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

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

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

1. Назовите три модели данных применяемые при разработке базы данных.

2. Дайте определение инфологической модели данных.

3. Для каких элементов инфологической модели «сущность-связь» определены тип и экземпляр?

4. Дайте определение понятия сущность.

5. Дайте определение понятия атрибут.

6. Дайте определение понятия сущность.

7. Дайте определение понятия ключ.

8. Дайте определение понятия связь.

9. Дайте определение связи многие к одному.

10. Дайте определение связи один к одному.

Глава 2
Реляционные базы данных

Основные понятия

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

Реляционная модель предложена в 60-е года двадцатого столетия. Одним из основателей данного подхода является Э. Кодд, в статьях которого изложены основные положения реляционного подхода и их математическое обоснование. В основе реляционного подхода лежат теория множеств и аппарат реляционной алгебры.

Основу реляционного подхода составляют: объекты структуры данных, операции над ними и ограничения целостности.

Неопределенные значения

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

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

В реляционном подходе для неопределенных значений любых типов данных введен специальный заменитель – NULL-значение. Для обработки таких значений используются специальные операции.

Ограничения целостности

Целостность понимается как правильность хранимых данных в любой момент времени. Задача контроля правильности хранимых данных решается частично. Системы управления базами данных обеспечивают контроль непротиворечивости вводимых значений, связанности данных и их принципиальную корректность. Так можно проверить, чтобы сотрудник был обязательно принят в существующий отдел, но нельзя проверить, что в связи указан именно тот отдел из существующих, которому реально принадлежит сотрудник. Или можно проверить, что вводимый номер дня недели, при составлении учебного расписания должен быть в диапазоне от 1 до 7. Однако проверить, что в данной записи должен быть четверг, а не вводимая пятница невозможно.

Основные ограничения целостности, определенные Э. Коддом:

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

· Ограничение ссылочной целостности или ограничение внешнего ключа – значение внешнего ключа для любой строки дочерней таблицы должно либо в точности соответствовать значению первичного ключа одной из строк родительской таблицы, либо быть полностью неопределенным. Полностью неопределенное означает, что все поля внешнего ключа принимают неопределенное значение NULL.

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

Ограничения целостности, определяемые пользователем:

· принадлежность значения заданному диапазону значений;

· принадлежность значения заданному множеству значений.

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

Основные предпосылки

Разработку реляционных баз данных традиционно делят на два этапа [2]:

· Логическое проектирование, при котором решается проблема наилучшего отображения предметной области в абстрактные понятия модели данных.

· Физическое проектирование, при котором решается задача по оптимизации выполнения основных операций над данными в выбранной системе управления данными: физическое размещение данных, создание индексов и других дополнительных структур.

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

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

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

· аномалия добавления – невозможность добавления новых данных о части объектов из-за нарушения целостности первичного ключа. «Вводить необходимо всё и сразу»;

· аномалия изменения – потенциальная возможность появления различных значений одного и того же понятия предметной области. «Изменяя, не забываем изменить всё»;

· аномалия удаления – невозможность удаления информации о части объектов из-за нарушения целостности первичного ключа. «Удаляя часть – удаляем всё».

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

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

Реляционная база данных может состоять из одной таблицы, содержащей все данные предметной области, такую таблицу называют универсальным отношением. Однако универсальное отношение практически для любой предметной области будет пронизано аномалиями. Поскольку основная причина аномалий – это смешивание в одной таблице информации о разных объектах-понятиях предметной области, то естественным путем улучшения базы является разделение этой информации по разным таблицам.

Разбиение таблицы на две и более, обладающие лучшими свойствами при основных операциях манипулирования данными называют нормализацией [2, 3, 4]. Разбиение данных на новые таблицы не должно приводить к потерям информации, т.е. нормализация – это преобразование без потерь. Как и любое преобразование без потерь, нормализация имеет обратное преобразование – денормализацию – позволяющее получить исходную таблицу.

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

Столбец Y реляционной таблицы функционально зависит от столбца X, если в любой момент времени каждому значению столбца X соответствует единственное значение столбца Y. Определение остается в силе и для более общего вида, если X и Y являются некоторым множеством (набором) столбцов таблицы. Функциональную зависимость обозначают оператором – стрелкой, где левый операнд – это определяющий столбец, а правый – зависимый: X → Y.

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

Транзитивная зависимость определяется следующими соотношениями: если X → Y и Y → Z, то X → Z транзитивно, т.е. если столбец Y функционально зависит от столбца X, а столбец Z функционально зависит от столбца Y, то столбец Z функционально зависит от столбца X, причем транзитивно через Y. Столбцы X, Y, Z могут быть составными.

Нормальные формы

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

Определено шесть нормальных форм: 1-5 нормальные формы (1НФ, 2НФ,… 5НФ) и нормальная форма Бойса-Кодда (НФБК).

НФБК является уточнением 3НФ и вводит более строгие требования к таблицам, чем 3НФ, не отменяя ее.

На практике для получения хорошего проекта обычно достаточно привести таблицы к 3НФ или к НФБК.

Первая нормальная форма – 1НФ: любое отношение с учетом ограничений целостности находится в 1НФ, таблица эквивалентная отношению с учетом ограничений целостности – реляционная таблица, также находится в 1НФ.

Вторая нормальная форма – 2НФ.

Определение 1 [4, 12]: отношение (таблица) находится во 2НФ тогда и только тогда, когда она находится в 1НФ, и каждый ее неключевой атрибут (столбец) минимально (полностью) зависит от каждого ключа.

Определение 2 [10]: отношение (таблица) находится во 2НФ тогда и только тогда, когда она находится в 1НФ, и нет неключевых атрибутов (столбцов), зависящих от части составного ключа.

Оба определения 2НФ по сути своей идентичны. Первое опирается на определение полной или минимальной функциональной зависимости, а второе включает это определение в себя.

Третья нормальная форма – 3НФ.

Определение 1 [4, 12]: отношение (таблица) находится в 3НФ тогда и только тогда, когда она находится во 2НФ, и каждый ее неключевой атрибут (столбец) нетранзитивно функционально зависит от каждого ключа.

Определение 2 [10]: отношение (таблица) находится в 3НФ тогда и только тогда, когда она находится в 2НФ, и все неключевые атрибуты (столбцы) взаимно независимы.

Оба определения 3НФ по сути своей идентичны. Первое опирается на определение транзитивной функциональной зависимости, а второе включает это определение в себя.

Нормальная форма Бойса-Кодда – НФБК.

Отношение (таблица) находится в НФБК в том и только в том случае, если любая функциональная зависимость между его полями (столбцами) сводится к минимальной (полной) функциональной зависимости от возможного ключа.

Все определения приведены в общем виде, где рассматриваются зависимости от всех возможных ключей таблицы. Практические примеры и определения нормальных форм обычно даются для частного случая – наличия единственного ключа, он же первичный ключ таблицы. Например: отношение (таблица) находится во 2НФ тогда и только тогда, когда она находится в 1НФ, и каждый ее неключевой атрибут (столбец) минимально (полностью) зависит от первичного ключа.

Формализация алгоритма нормализации при рассмотрении определений нормальных форм с учетом возможных ключей достаточно затруднена. Если использовать в определениях зависимости только от первичного ключа, можно сформулировать алгоритм приведения универсального отношения к 3НФ или НФБК [2, 10].

Правила нормализации

В основе алгоритма нормализации лежат два правила.

Правило №1 приведения таблицы, находящейся в 1НФ и не удовлетворяющей условиям 2НФ, к 2НФ.

Пусть задана таблица T, находящаяся в 1НФ со столбцами K1, K2, F пара столбцов K1 и K2 являются составным первичным ключом. В таблице имеется функциональная зависимость K2→F. Наличие K2→F говорит о том, что таблица T не находится в 2НФ. Рекомендуется создать новую таблицу T1, состоящую из атрибутов K2 и F и первичным ключом K2. Из исходной таблицы необходимо удалить поле F. В результате обе таблицы будут находиться в 2НФ.

В краткой форме:

Пусть T(K1,K2,F), где первичный ключ – (K1, K2) и K2→F.

Необходимо сделать:

T1(K2,F), где первичный ключ – K2 и K2→F,

T(K1,K2), где первичный ключ – (K1, K2).

Любой из столбцов и/или все K1, K2, F могут являться наборами столбцов.

Некоторые частные случаи правила.

Если таблица содержит столбец, полностью функционально зависящий от первичного ключа, то столбец остается в исходной таблице:

Пусть T(K1,K2,F1,F2), где первичный ключ – (K1, K2) и K2→F2, (K1,K2) →F1.

Необходимо сделать:

T1(K2,F2), где первичный ключ – K2 и K2→F2,

T(K1,K2, F1), где первичный ключ – (K1, K2) и (K1,K2) →F1.

Если таблица содержит столбец, функционально зависящий от той же части первичного ключа, то оба столбца перемещаются в новую таблицу:

Пусть T(K1,K2,F1,F2), где первичный ключ – (K1, K2) и K2→ (F1, F2).

Необходимо сделать:

T1(K2, F1,F2), где первичный ключ – K2 и K2→ (F1,F2),

T(K1,K2), где первичный ключ – (K1, K2) .

Если таблица содержит столбец, функционально зависящий от другой части первичного ключа, то правило применяется последовательно для каждой зависимости:

Пусть T(K1,K2,F1,F2), где первичный ключ – (K1, K2) и K1→ F1, K2→ F2.

Необходимо сделать:

T1(K1, F1), где первичный ключ – K1 и K1→ F1,

T2(K2, F2), где первичный ключ – K2 и K2→ F2,

T(K1,K2), где первичный ключ – (K1, K2).

Правило №2 приведения таблицы, находящейся в 2НФ и не удовлетворяющей условиям 3НФ, к 3НФ.

Пусть задана таблица T, находящаяся в 2НФ со столбцами K, F1,F2, где K – первичный ключ. В таблице имеется следующая функциональная зависимость F1→F2 и F1 не является возможным ключом. Наличие F1→F2 говорит о том, что таблица T не находится в 3НФ (K→F2 транзитивно). Рекомендуется создать новую таблицу T1, состоящую из атрибутов F1 и F2 и первичным ключом F1. Из исходной таблицы необходимо удалить поле F2. В результате обе таблицы будут находиться в 3НФ.

В краткой форме:

Пусть T(K,F1,F2), где первичный ключ – K и F1→F2.

Необходимо сделать:

T1(F1,F2), где первичный ключ – F1 и F1→F2,

T(K,F1), где первичный ключ – K.

Любой из столбцов и/или все K, F1,F2 могут являться наборами столбцов.

Некоторые частные случаи правила.

Если таблица содержит столбец, полностью функционально зависящий от первичного ключа, то столбец остается в исходной таблице:

Пусть T(K,F1,F2,F3), где первичный ключ – K и F1→F2, K→F3.

Необходимо сделать:

T1(F1,F2), где первичный ключ – F 1 и F 1→F2,

T(K1,K2, F1,F3), где первичный ключ – (K1, K2) и (K1,K2) →F1.

Если таблица содержит столбец, функционально зависящий от того же неключевого поля, оба столбца перемещаются в новую таблицу:

Пусть T(K,F1,F2,F3), где первичный ключ – K и F1→ (F2, F3).

Необходимо сделать:

T1(F1, F2,F3), где первичный ключ – F1 и F1→ (F2, F3),

T(K, F1), где первичный ключ – K.

Если таблица содержит столбец, функционально зависящий от другого неключевого поля, то применяется последовательно для каждой зависимости:

Пусть T(K,F1,F2,F3,F4), где первичный ключ – K и F1→ F2, F3→ F4.

Необходимо сделать:

T1(F1, F2), где первичный ключ – F1 и F1→ F2,

T2(F3, F4), где первичный ключ – F3 и F3→ F4,

T(K, F1,F3), где первичный ключ – K.

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

Пусть T(K,F1,F2,F3), где первичный ключ – K и F1→F2, F2→F3.

Необходимо сделать:

Первый шаг.

T1(F1, F2,F3), где первичный ключ – F1 и F1→F2, F2→F3,

T(K, F1), где первичный ключ – K.

Второй шаг.

T2(F2,F3), где первичный ключ – F2 и F2→F3,

T1(F1, F2), где первичный ключ – F1 и F1→F2,

T(K, F1), где первичный ключ – K.

Алгоритм нормализации

Рассмотрим алгоритм приведения структуры базы данных от универсального отношения к НФБК, с добавлением практических аспектов.

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

Шаги нормализации:

1. Определение первичного ключа универсального отношения.

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

3. Формирование новых таблиц по зависимостям из предыдущего пункта по правилу №1 перевода из 1НФ в 2НФ.

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

5. Формирование новых таблиц по зависимостям из предыдущего пункта по правилу №2 перевода из 2НФ в 3НФ.

6. Введение нумерации ключевых полей. В таблицах, где первичный ключ состоит из одного поля строкового типа, добавляется фиктивное поле целочисленного типа, в котором осуществляется «нумерация» строк таблицы. Исходное строковое поле исключается из состава ключа, а вновь введенное целочисленное поле вводится в состав ключа.

7. Выделение справочников. Рассматриваются строковые поля, не обрабатывавшиеся в предыдущем пункте. Поле, в рамках рассматриваемой задачи, должно принимать значения из ограниченного множества, и значения поля могут повторяться в различных строках таблицы, в которой находится рассматриваемое поле. Для таких полей формируется новая таблица, состоящая из двух столбцов. Само строковое поле и целочисленное поле, вводящее нумерацию строк таблицы. Это поле является первичным ключом. В таблицу вносятся только уникальные значения рассматриваемого строкового поля. В исходной таблице строковое поле заменяется на целочисленное. Целочисленное поле выступает в роли внешнего ключа для связи с новой таблицей.

2.7 Нормализация в примерах.

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

Универсальное отношение для рассматриваемой задачи приведено на рис. 1.

Краткая инфологическая модель.

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

Шаг №1.

Первичный ключ универсального отношения «Авиабилеты» составной и состоит из столбцов: «№ Рейса», «Дата вылета», «Паспортные_данные». Первичный ключ определяется из семантики прикладной области, которая сформулирована в инфологической модели. При описании универсального отношения были выделены три понятия прикладной области: рейс, пассажир и связь между ними – билет. Столбцы, определяющие уникальность рейса и пассажира, входят в первичный ключ. Если бы пассажир мог воспользоваться конкретным рейсом только однажды, то первичный ключ должен был бы быть «№ Рейса», «Паспортные_данные». Однако пассажир может неоднократно летать одним и тем же рейсом, поэтому «Дата вылета», дополнительно характеризующая билет должна быть включена в первичный ключ. Далее первичный ключ таблицы будем помечать подчеркиванием сплошной линией наименований столбцов, входящих в его состав.

Полученная нами таблица с первичным ключом находится в 1НФ, т.к. все значения в таблице атомарные, и мы потребуем для нее ограничение целостности по первичному ключу.

Рис. 1. Универсальное отношение

Шаг №2.

Значения столбцов «Пункт_прибытия», «Время_вылета», «Марка_самолета», «Кол-во_мест» и «Цена_билета» всегда одинаковы для всех записей таблицы с одинаковым значением столбца «№ Рейса». Т.е. эти столбцы функционально зависят от столбца «№ Рейса», являющегося частью первичного ключа. Записываются эти функциональные зависимости в следующем виде:

«№ рейса» → «Пункт_прибытия», «Время_вылета», «Марка_самолета», «Кол-во_мест», «Цена_билета».

Аналогично значение столбцов «ФИО» и «Скидка%» функционально зависят от значения столбца «Паспортные_данные»:

«Паспортные_данные» → «ФИО», «Скидка%».

Таким образом, таблица «Авиабилеты» не удовлетворяет условиям 2НФ. Рассмотрим указанные выше аномалии, возникающие при выполнении операций над данными этой таблицы.

Таблица содержит одновременно данные по рейсам и пассажирам. Вытекающая избыточность видна на примере пункта прибытия: пункт прибытия 111 рейса указан четыре раза.

Аномалию добавления можно показать на примере появления нового рейса. В данную таблицу невозможно добавить информацию о рейсе, пока на него не продано ни одного билета, т.к. не будет соблюдаться ограничение целостности по первичному ключу.

Изменение времени вылета рейса 111, выполненное не для всех строк, может привести к противоречивости данных в разных билетах – аномалии изменения.

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

Шаг №3.

Для выявленных на предыдущем шаге зависимостей применяем правило №1 перевода таблиц из 1НФ в 2НФ. В результате применения правила для зависимости от «№ Рейса», получим структуру базы данных, представленную на рис. 2. Здесь новая таблица «Рейсы» будет удовлетворять 2НФ, а исходная таблица по-прежнему не будет удовлетворять 2 НФ. В результате применения правила для зависимости от столбца «Паспортные_данные», получим структуру базы данных, представленную на рис. 3. Здесь все три таблицы удовлетворяют 2НФ.

Прямую связь между таблицами будем показывать линией, соединяющей эти таблицы, а тип связи будем указывать маркировкой ее концов: «1» – со стороны один и «М» - со стороны многие.

Шаг №4.

Таблица «Авиабилеты» не имеет в своем составе неключевых полей, поэтому она точно удовлетворяет 3НФ.

Будем считать, что столбцы «ФИО» и «Скидка%» таблицы «Пассажиры» взаимонезависимые. Возможную зависимость «ФИО» → «Скидка%» рассмотрим позже.

Значение столбца «Кол-во_мест» всегда одинаковы для всех записей таблицы «Рейсы» с одинаковым значением столбца «Марка_самолета». «Кол-во_мест» функционально зависит от столбца «Марка_самолета»: «Марка_самолета» → «Кол-во_мест».

Таблица «Рейсы» не удовлетворяет условиям 3НФ. Избыточность связана с неоднократным повторением характеристики марки самолета.

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

Изменение количества пассажиров, выполненное не для всех строк таблицы «Рейсы» может привести к противоречивости характеристик одной и той же марки самолета – аномалии изменения.

Аномалия удаления может стать причиной потери информации о марке самолета и ее характеристиках. Если какой-либо маркой самолета летает единственный рейс или группа рейсов, то при его или их удалении потеряется вся информация о марке самолета.

Шаг №5.

Для выявленной на предыдущем шаге зависимости применим правило №2 перевода из 2НФ в 3НФ. В результате, получим новую структуру базы данных, состоящую из четырех таблиц, находящихся в 3НФ (рис. 4).

Шаг №6.

Условиям данного шага – таблица с простым первичным ключом строкового типа – удовлетворяют таблицы «Пассажиры»