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

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

Разработку реляционных баз данных традиционно делят на два этапа [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.

Условиям данного шага – таблица с простым первичным ключом строкового типа – удовлетворяют таблицы «Пассажиры» и «Марки». В таблице «Рейсы» поле «№Рейса» считаем целочисленным. В результате получим модифицированную структуру базы данных (рис. 5). При этом функциональные зависимости, рассмотренные ранее, остались в силе:

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

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

· «Марка_самолета» → «Кол-во_мест».

Определяющие столбцы в зависимостях на данном шаге перестали быть первичными ключами, т.е. все три таблицы перестали удовлетворять условиям 3НФ. Однако эти столбцы являются возможными ключами своих таблиц и в этом случае они удовлетворяют 3НФ в общем виде. Если же мы пользуемся определениями с учетом только первичного ключа, то «нормальность» этих таблиц будет обоснована НФБК, где уже однозначно прописываются зависимости от всех возможных ключей. Аналогично в случае введения функциональной зависимости «ФИО» → «Скидка%» столбец «ФИО» должен являться возможным ключом таблицы «Пассажиры».

Шаг №7.

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

Для столбца «ФИО» не будем строить справочник, столбец «ФИО» мы проанализируем ниже.

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

Полученное решение удовлетворяет НФБК.

Если же при приведении к 2НФ сразу учесть наличие возможных ключей, то изменятся промежуточные решения, однако конечное решение совпадет с полученным ранее.

Допустим, что столбец «ФИО» имеет уникальные значения для каждого пассажира. Тогда универсальное отношение имеет два возможных ключа:

· «№ Рейса», «Дата вылета», «Паспортные_данные» – первичный ключ;

· «№ Рейса», «Дата вылета», «ФИО» – возможный ключ.

При этом к зависимости «Паспортные_данные» → «ФИО» добавляется зависимость «ФИО» → «Паспортные_данные». Но столбец «ФИО» не попадает в перечень анализируемых на шаге №2, так как он является ключевым рис. 7.

Приведение таблицы «Рейсы» к 3НФ будет без изменений.

А вот таблица «Авиабилеты» теперь имеет избыточность, хотя и удовлетворяет 3НФ. Повторение значений поля «ФИО» – избыточность. Таблица не удовлетворяет НФБК, т.к. в зависимостях «Паспортные_данные» → «ФИО» и ФИО» → «Паспортные_данные» определяющие столбцы не являются ключами таблицы. Дальнейшая нормализация таблицы «Авиабилеты» заключается в выносе поля «ФИО». В результате должна появиться таблица, состоящая из полей «Паспортные_данные» и «ФИО», где «Паспортные_данные» –первичный ключ. Новую таблицу можно объединить с уже существующей таблицей «Пассажиры». В результате получится схема, представленная на рис. 4. Далее структура базы данных модифицируется по указанному выше алгоритму.

Рис. 2. Структура базы данных на третьем шаге. После избавления от первой зависимости

Рис. 3. Структура базы данных на третьем шаге. После избавления от второй зависимости

Рис. 4. Структура базы данных на пятом шаге

Рис. 5. Структура базы данных на шестом шаге

Рис. 6. Структура базы данных на шестом шаге

Рис. 7. Структура базы данных на третьем шаге. Вариант с возможными ключами

Полученную структуру базы данных обычно описывают даталогической схемой. Схема включает в себя описание всех таблиц с указанием по каждой таблице названия, перечня столбцов с указанием их имен, типов данных, принадлежность к первичному и/или внешнему ключу. Указываются связи между таблицами с указанием полей, по которым осуществляется связь – первичный и внешний ключи, образующие связи. Существует множество программных продуктов, позволяющих автоматизировать разработку таких схем. На рис. 8 приведена даталогическая схема рассматриваемой базы данных, разработанная в пакете MS Visio. Принадлежность столбца таблицы первичному и/или внешнему ключу помечается, соответственно: PK – первичный ключ и FK – внешний ключ. Стрелка связи показывает направление от дочерней таблицы к родительской таблице.

Рис. 8. Даталогическая схема базы данных

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

Заключение

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

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

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

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

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

1. Какими свойствами должна обладать таблица эквивалентная отношению?

2. Какие виды ключей определены в реляционных базах данных?

3. Укажите отличия между первичным и возможным ключом.

4. Какие операции определены над реляционными структурами?

5. Какие ограничения целостности вы знаете?

6. назовите основную причину "плохого" качества базы данных.

7. Какие аномалии баз дынных вы знаете?

8. Что такое нормализация?

9. Какие ограничения накладывает на таблицу первая нормальная форма?

10. Какая из нормальных форм накладывает ограничения на функциональную связь между неключевыми полями?

11. Какое минимальное количество столбцов должно быть в реляционной таблицу, чтобы она могла не удовлетворять второй нормальной форме (2НФ)?

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

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

В настоящее время основным инструментом взаимодействия с базами данных является язык SQL (Structure Query Language). Этот язык развивается с 70-х годов прошлого столетия. Он претерпевал несколько модификаций, направленных в первую очередь на расширение его функциональных возможностей. Изменения фиксировались в стандартах. Одними из последних были стандарты 1999 и 2003 годов.

Основные функции, реализуемые SQL [3, 4, 11]:

· выбор данных из базы;

· манипулирование данными;

· управление структурой базы данных:

o управление таблицами;

o управление ограничениями целостности;

o управление дополнительными структурами: триггерами, представлениями, функциями и т.п.

· управление авторизацией доступа к объектам структуры и данным;

· управление транзакциями – системой фиксации и отмены вносимых изменений.

Типы данных

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

В SQL определен широкий спектр различных типов данных, ниже приведены наиболее часто употребляемые категории типов:

· точные числовые типы;

· приближенные числовые типы;

· типы символьных строк;

· типы даты и времени;

Точные числовые типы данных – это либо целочисленные типы данных, либо типы, хранящие дробные числа с заданной точностью. Примерами целочисленных типов являются такие, как Integer, SmallInt. Типы, обеспечивающие точное представление дробных чисел – это NUMERIC и DECIMAL. В аргументах таких типов указывают одно или два числа, задающих соотношение между общим числом хранимых цифр и числом цифр, отведенных под дробную часть. Например: DECIMAL(10, 4).

Приближенные числовые типы – это типы, в которых числа представляются парой чисел: мантисса, порядок. Такие типы определены во всех языках программирования, и в SQL они имеют аналогичные названия и смысл: REAL, FLOAT – одинарной точности, DOUBLE – двойной точности.

Символьные строки в SQL задаются двумя типами. Тип строка заданной длины (обычно CHARACTER или CHAR) имеет один параметр, определяющий ее длину. При этом, для поля такого типа, не зависимо от реальной длины данных, всегда выделяется заданное число символов. Альтернативой служит тип строка переменной длины. Этот тип также имеет параметр, определяющий длину строки, но здесь определяется максимальная длина строки. Если же строка содержит меньшее число символов, то под нее выделяется требуемое число символов. Отличие этих типов связано, в первую очередь, с оптимизацией работы системы управления базой данных, балансом между скоростью обработки строк фиксированной длины и объемом хранимой информации.

Дата и время в SQL могут храниться либо типами данных, хранящими только время (TIME) и только дату (DATE), либо типами хранящими дату и время одновременно (TIMESTAMP, DATETIME). Типы данных для хранения даты и времени не являются строковыми типами, а эквивалентны одному из числовых типов данных.

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

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

Для строковых типов определены операции сравнения, конкатенации (соединения) и ряд встроенных функций, позволяющих определить длину строки, выделить подстроку и т.п.

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

Операции над значениями NULL имеют свою специфику:

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

· аналогично, если хотя бы дин из операндов любой операции сравнения имеет неопределенное значение NULL, то результат операции всегда принимает неопределенное значение Unknown;

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

Для корректной проверки значения на равенство его неопределенному значению NULL в SQL определена специальная операция сравнения с NULL.

Выбор данных из базы

Получение практически любого количества данных из одной или нескольких таблиц выполняется с помощью единственной команды SELECT. В общем случае, результатом реализации команды SELECT является другая таблица. К этой новой (рабочей) таблице может быть снова применена операция SELECT и т.д., т.е. такие операции могут быть вложены друг в друга. Результаты отдельных команд SELECT могут быть объедены в одну таблицу операциями объединения (UNION), пересечения (INTERSECT) и вычитания (MINUS). Важно помнить, что при использовании операций объединения нескольких одиночных запросов сортировка результатов задается только для результирующей таблицы.

При описании синтаксиса SQL‑команд, используются следующие обозначения [13]:

· звездочка (*) для обозначения «все» – употребляется в обычном для программирования смысле, т.е. «все случаи, удовлетворяющие определению»;

· квадратные скобки ([]) – означают, что конструкции, заключенные в эти скобки, являются необязательными (т.е. могут быть опущены);

· фигурные скобки ({}) – означают, что конструкции, заключенные в эти скобки, должны рассматриваться как целые синтаксические единицы;

· многоточие (...) – указывает на то, что непосредственно предшествующая ему синтаксическая единица факультативно может повторяться один или более раз;

· прямая черта (|) – означает наличие выбора из двух или более возможностей. Например обозначение ASC|DESC указывает, можно выбрать один из терминов ASC или DESC; когда же один из элементов выбора заключен в квадратные скобки, то это означает, что он выбирается по умолчанию (так, [ASC]|DESC означает, что отсутствие всей этой конструкции будет восприниматься как выбор ASC);

· точка с запятой (;) – завершающий элемент предложений SQL;

· запятая (,) – используется для разделения элементов списков;

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

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

При вызове команды SELECT используются ключевые слова, которые позволяют указать условия для выбора нужных данных и (если требуется) их обработки:

SELECT

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

FROM

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

WHERE

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

GROUP BY

(группируя по) указанному перечню столбцов с тем, чтобы получить для каждой группы единственное агрегированное значение, используя во фразе SELECT SQL-функции SUM (сумма), COUNT (количество), MIN (минимальное значение), MAX (максимальное значение) или AVG (среднее значение)

HAVING

(где) группы, сформированные в разделе GROUP BY, должны удовлетворять указанному перечню отбора групп

ORDER BY

(упорядочить по) указанному перечню столбцов результаты выбора данных. При этом упорядочение можно производить в порядке возрастания – ASC (ASCending) или убывания DESC (DESCending), а по умолчанию принимается ASC.

Команда SELECT имеет формат:

SELECT [[ALL] | DISTINCT]{ * | элемент_SELECT [,элемент_SELECT] ...}
FROM источник_данных [псевдоним]
[,источник_данных [псевдоним]] ...
[WHERE фраза]
[GROUP BY фраза]

[HAVING фраза]
[ORDER BY столбец_ORDER_BY [ASC|DESC]
[,столбец_ORDER_BY [ASC|DESC]] ...];

Элемент_SELECT – это одна из следующих конструкций:

[таблица.]* | значение | SQL_функция | системная_переменная

где значение – это:

[таблица.]столбец | (выражение) | константа | переменная

Синтаксис выражений имеет вид

( {[ [+] | - ] {значение | функция_СУБД} [ + | - | * | ** ]}... )

а синтаксис SQL_функций – одна из следующих конструкций:

{SUM|AVG|MIN|MAX|COUNT} ( [[ALL]|DISTINCT][таблица.]столбец )
{SUM|AVG|MIN|MAX|COU