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

Правительство Российской Федерации

Национальный исследовательский университет

ВЫСШАЯ ШКОЛА ЭКОНОМИКИ

ПЕРМСКИЙ ФИЛИАЛ

Кафедра информационных технологий в бизнесе

Информационные технологии в офисной работе

Разработка информационной системы предприятия с помощью системы управления базами данных Access 2007

Учебно-методическое пособие

Пермь 2011

Информационные технологии в офисной работе. Разработка информационной системы предприятия с помощью системы управления базами данных Access 2007. Учебно-методическое пособие. НИУ ВШЭ ПФ, 2011 г., 40 ст.

Составители: Викентьева Ольга Леонидовна, Лебедев Валерий Викторович.

Учебно-методическое пособие составлено в соответствии с Государственным образовательным стандартом, учебной программой и концепцией дисциплины «Информационные технологии в экономике». Пособие предназначено для студентов и преподавателей ПФ ГУ ВШЭ и содержит серию практических занятий, раскрывающих возможности современных информационных технологий по созданию систем хранении, поиска и представления данных.

Рецензент: доцент кафедры информатики Пермского регионального института педагогических информационных технологий, кандидат педагогических наук, член-корреспондент Академии информатизации образования Кушев В.О.

Занятие 1. Проектирование реляционной базы данных

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

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

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

Внешних описаний данных, хранящихся в БД, следовательно, может быть множество. Их необходимо свести в единое концептуальное представление , описывающее данные на уровне всей информационной системы в целом. Представление этих данных на внутреннем уровне определяется способом их хранения во внешней памяти.

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

Рисунок 1. Формирование представления данных

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

Для сотрудника, который работает с платежными формами, необходима другая информация о клиентах (рис.3).

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

Рисунок 2. Данные для первого сотрудника

Рисунок 3. Данные для второго сотрудника

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

Тогда концептуальное описание определяется следующей информацией

Данные, описанные концептуальной схемой, должны быть записаны во внешней памяти, на ВЗУ, предназначенных для хранения информации, находящейся в БД. Внутреннее описание данных характеризует способ хранения данных во внешней памяти.

Правила описания данных определяются выбранной моделью данных (в данном случае рассматривается только реляционная модель – самая распространенная на настоящее время).

Если взять описание данных о клиентах фирмы, занимающейся поставками товаров в магазины города, из приведенного выше примера, представленное на рис 4, то данные, описанные этой таблицей не могут в таком виде быть представлены в реляционной БД, так как не все значения являются атомарными (компоненты строк «Владелец» и «Адрес» состоят из нескольких значений, т.е. значения этих атрибутов заменяются другими отношениями, расшифровываются ими; в отношении, описывающем владельца, поля «Адрес» и «Паспорт» также не являются атомарными, следовательно строится иерархия отношений).

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

Рисунок 4. Общее представление данных

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

В плохо спроектированной БД вся информация может храниться в одной таблице. Для описанного выше примера такая таблица могла бы содержать следующие столбцы:

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

Какие недостатки имеет такое представление?

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

Требование атомарности выполнено – составные столбцы «Адрес» и «Владелец» (а для владельца «Адрес» и «Паспорт») разбиты на компоненты, которые включены в общую таблицу. Но у одного магазина может быть несколько владельцев, а один человек может владеть несколькими магазинами. Это приводит к тому, что в таблицу нужно будет включать все строки, представляющие «комбинации» магазинов и их владельцев, т.е. в различных строках будут повторяться группы данных (несколько раз будут повторяться данные о магазине – для каждого его владельца, а данные владельца будут повторяться для каждого его магазина). Такое представление данных ведет к огромной избыточности, к тому, что неэффективно будет расходоваться память на ВЗУ. Кроме того, дублирование информации может привести к проблемам при ее обработке: чтобы внести изменения в информацию о магазине (например, если у него изменится счет в банке) нужно изменить эти данные в нескольких записях, соответствующих разным владельцам.

При определении того, какие таблицы должны входить в БД, и того, какая информация в них должна храниться, следует учитывать следующее правило: каждая таблица описывает объект , существующий самостоятельно, обладающий собственными свойствами. Построение БД следует начать с создания представления каждого объекта в виде строк, содержащих его атрибуты, в соответствующей таблице; определения моделей взаимосвязи объектов. В рассматриваемом примере в БД фактически должна храниться информация об объектах двух типов: о магазинах и об их владельцах. Эту информацию следует поместить в две различные таблицы («Магазины» и «Владельцы»), имеющие следующие столбцы:

«Магазины»

«Владельцы»

Каждая строка таблицы «Магазины» будет описывать экземпляр соответствующего объекта (один магазин). А в каждой строке таблицы «Владельцы» будет находиться информация об одном владельце магазина.

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

Что можно выбрать в качестве первичного ключа для описанных выше таблиц?

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

Определим ключ для таблицы «Магазины».

Если выбрать в качестве ключа атрибут «Название магазина», будет ли он удовлетворять указанному требованию? Нет, если в одном городе может быть несколько магазинов с одинаковыми названиями, расположенных в разных частях города. Чтобы гарантировать однозначность следует дополнить название магазина его адресом (по названию магазина и его адресу можно однозначно выбрать нужную строку в таблице), тогда ключ отношения будет составным.

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

Выберем в качестве первичного ключа атрибут «ИНН». Далее этот атрибут будет использоваться для организации связей между таблицами «Магазины» и «Владельцы» (эти связи должны отражать реальные взаимосвязи между магазинами и их владельцами).

Определимся с ключами и для таблицы «Владельцы».

Если бы можно быль предположить, что среди владельцев магазинов нет однофамильцев, то в качестве ключа можно было бы выбрать атрибут «Фамилия» рассматриваемого отношения. Но, к сожалению, владельцы магазинов могут быть не только однофамильцами, но и полными тесками (маловероятно, но вполне возможно). Поэтому в качестве ключа можно выбрать паспортные данные владельца, т.е. использовать для его идентификации составной ключ, включающий атрибуты «Серия» и «Номер». Этот ключ будем считать первичным. С его помощью установим связь между владельцем и его магазинами.

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

Определим тип связи между таблицами «Магазины» и «Владельцы».

Если предположить, что один человек может владеть несколькими магазинами, но у каждого магазина есть единственный владелец, то следовало бы установить между этими таблицами связь «один-ко-многим». Для организации такой связи в БД можно было бы в строку таблицы «Магазины», содержащую информацию о магазине, включить внешний ключ , идентифицирующий владельца магазина, т.е. данные его паспорта – атрибуты «Серия» и «Номер». Организовать связь, включив ключ «ИНН», идентифицирующий магазины, в качестве внешнего ключа в таблицу «Владельцы», в данном случае нельзя, так как в этом случае информацию о владельце пришлось бы дублировать для каждого магазина.

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

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

«Магазины-Владельцы»

ИНН

Серия

Номер

Эта таблица позволит по атрибуту «ИНН» магазина найти всех его владельцев (через данные их паспортов), а по составному атрибуту, включающему атрибуты «Серия» и «Номер» паспорта владельца найти в БД все магазины, которыми он владеет.

Для этого следует, создав таблицу «Магазины-Владельцы», установить связи «один-ко-многим» между таблицей «Магазины» и таблицей «Магазины-Владельцы», а также между таблицами «Владельцы» и «Магазины-Владельцы»:

ИНН Серия Номер

«Магазины-Владельцы»

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

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

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

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

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

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

Например: в БД необходимо хранить информацию о товарах, которые поставлены в магазины. Эта информация включает атрибуты «Наименование», «Код» и «Цена» товара, а также «Количество» поставленного товара. Если включить эту информацию в таблицу «Поставки» в следующем представлении:

«Поставки»

(здесь «ИНН» идентифицирует магазин, в который выполнена поставка (это внешний ключ, используемый для создания связи «один-ко-многим» таблицы «Магазины» с данной таблицей), «Наименование» – название товара, «Код» – его уникальный код (товары с разными характеристиками и, следовательно, разными ценами могут иметь одно наименование, но коды будут разными), «Цена» – отпускная цена товара, «Количество» – количество поставленного товара), то может возникнуть избыточность.

Для определения строки, представляющей поставку товара в конкретный магазин, можно задать составной ключ, включающий атрибуты «ИНН» и «Код». Эта информация дает возможность определить цену товара и его количество, поставленное в данный магазин, а также вычислить общую стоимость товара. Если предположить, что товар поставляется во все магазины по одной и той же цене, и цена не изменяется со временем, то неключевой атрибут «Цена» определяется не только составным ключом «ИНН» + «Код», но и его частью – атрибутом «Код». Таким образом, одна и та же цена повторяется во всех строках таблицы, где содержится информация о поставке одного и того же товара. Это ведет к избыточности. Наименование товара также определяется его кодом. Поэтому информацию, относящуюся только к товару и не зависящую от магазина, можно вынести в отдельную таблицу:

Здесь ключевое поле «Код» позволит связать данные, находящиеся в таблице «Поставки», с данными из таблицы «Товары»

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

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

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

Избавиться от избыточности можно было бы, разбив таблицу «Товары» на две таблицы (одна включала бы атрибуты «Код» и «Наименование», а вторая «Наименование» и «Цена»).

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

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

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

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

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

В настоящее время широко используются специальные средства, облегчающие процесс разработки информационных систем (CASE-средства – Computer-Aided Software/System Engineering).

Вопросы для самоконтроля :

1. Что представляет собой база данных?

2. Что такое внешнее представление данных?

3. В чем сущность концептуального представления данных?

4. Что такое модель данных?

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

6. Что такое ключ отношения?

7. Какой ключ называется внешним?

8. Какие связи могут быть организованы в базе данных?

9. В чем сущность каждой из пяти нормальных форм?

Задание для самостоятельной работы:

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

Второй сотрудник собирает сведения об исполнителях и его интересует:

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

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

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

Перечислим этапы концептуального проектирования :

· изучение предметной области для формирования общего пред­ставления о ней;

· выделение и анализ функций и задач разрабатываемой ИС;

· определение основных объектов-сущностей предметной области и отношений между ними;

· формализованное представление предметной области.

При проектировании схемы реляционной БД можно выделить сле­дующие процедуры :

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

· определение перечня полей, типов полей, ключевых полей каж­дой таблицы (схемы таблицы), установление связей между таб­лицами через внешние ключи;

· установление индексирования для полей в таблицах;

· разработка списков (словарей) для полей с перечислительными данными;

· установление ограничений целостности для таблиц и связей;

· нормализация таблиц, корректировка перечня таблиц и связей.

Проектирование БД осуществляется на физическом и логическом уровнях. Проектирование на физическом уровне реализуется сред­ствами СУБД и зачастую автоматизировано.

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

Одной из важнейших задач логического проектирования БД явля­ется структуризация данных . Выделяют следующие подходы к проек­тированию структур данных :

· объединение информации об объектах-сущностях в рамках одной таблицы (одного отношения) с последующей декомпозицией на несколько взаимосвязанных таблиц на основе процедуры норма­лизации отношений;

· формулирование знаний о системе (определение типов исходных данных и взаимосвязей) и требований к обработке данных, полу­чение с помощью CASE-системы готовой схемы БД или даже го­товой прикладной информационной системы;

· осуществление системного анализа и разработка структурных моделей.

Первый подход - класси­ческий.

Процесс проектирования начинается с выделения объектов-сущно­стей, информация о которых будет храниться в БД, и определения их атрибутов. Выделенные атрибуты объединяются в одной таблице (от­ношении).

Полная информация о сущности (таблица) дает избыточность (повторение) , Þ требуется преобразование, т.е. декомпозиция, т.е. разбиение на несколько таблиц, т.е. нормализация.

Þ Полученное отношение подвергается нормализации . Процедура нормализации является итерационной и заключается в последовательном переводе отношений из первой нормальной фор­мы в нормальные формы более высокого порядка.

Выделяют следующую последовательность нормальных форм:

· первая нормальная форма (1НФ);

· вторая нормальная форма (2НФ=1НФ+нечто);

· третья нормальная форма (ЗНФ=2НФ+нечто);

· усиленная третья нормальная форма, или нормальная форма Бойса-Кодда (БКНФ);

· четвертая нормальная форма (4НФ);

· пятая нормальная форма (5НФ).

(Требования к 2НФ)

соотносится только с первичным ключом. Þ Каждое данное хранится в БД только в 1ом месте. Þ Дублируемые данные выносятся в др. таблицу, вместо них – внешние ключи.

(Требования к 3НФ)

Каждое неключевое поле не должно зависеть от другого неключевого поля (например, связь преподаватель-кафедра, или предмет-кафедра). Чтобы избежать, необходимо детально знать предметную область. Þ Убираем «факультет» из «Расписания», если там есть «Специальност».

3НФ обеспечивает декларативную ссылочность (данные из справочников).

(Требования к 4НФ)

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

Вместе с тем, следует различать неизбыточное и избыточное дубли­рование данных. Наличие первого из них в базах данных допускается. Приведем примеры обоих вариантов дублирования .

Пример неизбыточного дублирования данных представляет отно­шение «ТЕЛЕФОНЫ» (рис. 5.6) . Предположим, что в одной ком­нате установлен только один телефон, тогда номера телефонов сотруд­ников, находящихся в одном помещении, совпадают. Номер телефона 24212 встречается несколько раз. В этом состоит дублирование. Однако для каждого сотрудника номер уникален и при удалении одного из номе­ров будет утеряна информация о том, по какому номеру можно до­звониться до того или иного сотрудника. В этом состоит неизбыточность.

Рис. 5.6. Неизбыточное дублирование данных

Избыточное дублирование данных имеет место в отношении «КОМ­НАТЫ», в которое добавлен атрибут «Номер комнаты» (рис. 5.7) .

Рис. 5.7. Избыточное дублирование данных

Сотрудники Белкин, Синицын и Медведев находятся в одной ком­нате и, следовательно, имеют одинаковые номера. То есть номер телефона Синицына и Медведева можно узнать из кортежа со сведени­ями о Белкине. В этом и состоит избыточность дублирования данных.

Избыточное дублирование данных приводит к проблемам обработ­ки кортежей отношения, названным Э. Коддом «аномалиями обнов­ления отношения».

Аномалии - такие ситуации в таблицах БД, которые приводят к противоречиям в БД или существенно усложняют обра­ботку данных .

Выделяют три основных вида аномалий:

· аномалии модификации (редактирования);

· аномалии удаления;

· аномалии добавления.

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

Так, изменение номера телефона в комнате 325 (рис. 5.7) по­требует пересмотра всей таблицы «КОМНАТЫ» и изменения значе­ний атрибута «Номер телефона» в записях, в которых встречается этот номер.

Аномалии удаления проявляются в том, что при удалении какого-либо значения атрибута исчезнет другая информация, которая не свя­зана напрямую с удаляемым значением.

Так, удаление записи о сотруднике Волкове (например, по причине увольнения) приводит к исчезновению информации о номере телефо­на, установленного в 320-й комнате (см. рис. 5.7).

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

Например, в таблице «КОМНАТЫ» (см. рис. 5.7) невозможно от­разить информацию о комнате с установленным в ней телефоном до тех пор, пока в нее не помещен ни один сотрудник (при условии, что поле «ФИО» является ключевым).

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

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

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

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

Перечислим этапы концептуального проектирования:

* изучение предметной области для формирования общего пред­ставления о ней;

* выделение и анализ функций и задач разрабатываемой ИС;

* определение основных объектов-сущностей предметной области и отношений между ними;

* формализованное представление предметной области.

При проектировании схемы реляционной БД можно выделить сле­дующие процедуры:

*определение перечня таблиц и связей между ними;

*определение перечня полей, типов полей, ключевых полей каж­дой таблицы (схемы таблицы), установление связей между таб­лицами через внешние ключи;

*установление индексирования для полей в таблицах;

* разработка списков (словарей) для полей с перечислительными данными;

* установление ограничений целостности для таблиц и связей;

* нормализация таблиц, корректировка перечня таблиц и связей. Проектирование БД осуществляется на физическом и логическом уровнях. Проектирование на физическом уровне реализуется сред­ствами СУБД и зачастую автоматизировано.

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

Одной из важнейших задач логического проектирования БД явля­ется структуризация данных. Выделяют следующие подходы к проек­тированию структур данных:

*объединение информации об объектах-сущностях в рамках одной таблицы (одного отношения) с последующей декомпозицией на несколько взаимосвязанных таблиц на основе процедуры норма­лизации отношений;

* формулирование знаний о системе (определение типов исходных данных и взаимосвязей) и требований к обработке данных, полу­чение с помощью СА5Е-системы готовой схемы БД или даже го­товой прикладной информационной системы;

* осуществление системного анализа и разработка структурных

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

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

Возможности человека в обработке информации резко возросли с использованием компьютеров. В применении ЭВМ для решения задач информационного обслуживания можно выделить два периода:

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

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

Информационными системами (ИС) называют большие массивы данных вместе с программно-аппаратными средствами для их обработки. Различают следующие виды ИС: фактографические, документальные и экспертные системы.

Фактографическая ИС - это массив фактов - конкретных значений данных об объектах реального мира.

Информация в фактографической ИС хранится в четко структурированном виде, поэтому она способна давать однозначные ответы на поставленные вопросы, например: «Кто является победителем Чемпионата России по гимнастике в 1999 году?», «Кому принадлежит автомобиль марки AUDI 80 с регистрационным номером РА899Р77?», «Какой номер телефона в бухгалтерии МГУ?», «Кто стал Президентом России на выборах в марте 2002 года?» и т. д. Фактографические ИС используются буквально во всех сферах человеческой деятельности - в науке, материальном производстве, на транспорте, в медицине, государственной и общественной жизни, торговле, криминалистике, искусстве, спорте.

Документальные информационные системы обслуживают принципиально иной класс задач, которые не предполагают однозначного ответа на поставленный вопрос. Базу данных таких систем образует совокупность неструктурированных текстовых документов (статьи, книги, рефераты, тексты законов) и графических объектов, снабженная тем или иным формализованным аппаратом поиска. Цель системы, как правило, - выдать в ответ на запрос пользователя список документов или объектов, в какой-то мере удовлетворяющих сформулированным в запросе условиям. Например: выдать список всех статей, в которых встречается слово «Пушкин». Принципиальной особенностью документальной системы является ее способность, с одной стороны, выдавать ненужные пользователю документы (например, где слово «Пушкин» употреблено в ином смысле, чем предполагалось), а с другой - не выдавать нужные (например, если автор употребил какой-то синоним или ошибся в написании). Документальная система должна уметь по контексту определять смысл того или иного термина, например, различать «ромашка» (растение), «ромашка» (тип печатающей головки принтера).

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

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

КОНСПЕКТ ОБЗОРНОЙ ЛЕКЦИИ

Для студентов специальности
Т1002 «Программное обеспечение информационных технологий»

(Л.В. Рудикова, к.ф.-м.н., доцент)

Вопрос 31. АРХИТЕКТУРА СУБД. РЕЛЯЦИОННАЯ МОДЕЛЬ ДАННЫХ

1. Понятие базы данных.

2. Трехуровневая архитектура базы данных.

3. Жизненный цикл базы данных.

4. Архитектура СУБД.

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

6. Проектирование реляционных баз данных.

7. Нормальные формы отношений.

8. Реляционная алгебра.

1. Понятие базы данных.

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

Информационная система – автоматическая система, организующая данные и выдающая информацию.

Информационно-управляющая система – система, обеспечивающая информационную поддержку менеджмента.

Данные – разрозненные факты.

Информация – организованные и обработанные данные.

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

Каждая СУБД должна удовлетворять следующим требованиям:

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

· позволять «запрашивать » данные и изменять их с помощью языка запросов , или языка манипулирования данными ; допускать интеграцию и совместное использование данных различными приложениями;

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

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

Система с базой данных состоит из следующих компонентов:

· Пользователи, т.е. люди, которые используют данные.

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

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

· Данные, т.е. строки, хранящиеся в файлах.

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

Таким образом, систему с БД можно представить в виде следующей последовательности уровней:

На самом нижнем уровне находятся данные, хранящиеся в физических файлах (физическая память БД). На верхнем уровне – приложений с их собственными представлениями одних и тех же физических данных. Каждой представление БД – это определенная логическая структура, построенная из лежащих в основе физических данных. Чтобы обеспечить интерфейс между физической памятью БД и ее разнообразными логическими версиями (множеством поддерживаемых представлений) СУБД, в свою очередь должна состоять из нескольких уровней.

2. Трехуровневая архитектура базы данных.

Различие между логическим и физическим представлением данных официально признано в 1978 году, когда комитет ANSI / SPARC предложил обобщенную структуру систем баз данных. Эта структура получила название трехуровневой архитектуры. Три уровня архитектуры следующие: внутренний, концептуальный и внешний.

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

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

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

Представления пользователей и приложений	Внешний уровень
Отображения
Концептуальная схема	Концептуальный уровень
Отображение	Внутренний уровень
Система-хост
Хранящиеся данные

Рис. Уровни СУБД

3. Жизненный цикл базы данных.

Процесс проектирования, реализации иподдержания системы базы данных называется жизненным циклом базы данных (ЖЦБД). Процедура создания системы называется жизненным циклом системы (ЖЦС).

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

ЖЦБД состоит из следующих этапов:

1. Предварительное планирование – планирование БД, выполняемое в процессе разработки стратегического плана БД. В процессе планирования собирается следующая информация:

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

· какие файлы связаны с каждым из этих приложений;

· какие новые приложения и файлы находятся в процессе работы.

Данная информация помогает определить, как используется информация приложений, определить будущие требования к системе БД.

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

2. Проверка осуществимости . Здесь определяется технологическая, операционная и экономическая осуществимость плана создания БД, т. е.:

· технологическая осуществимость – есть ли технология для реализации запланированной БД?

· операционная осуществимость – есть ли средства и эксперты, необходимые для успешного осуществления плана создания БД?

· экономическая целесообразность – можно ли определить выводы? Окупится ли запланированная система? Можно ли оценить издержки и выгоду?

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

· Определяются цели системы путём анализа информационных потребностей. Здесь также обязательно указывается, какую именно БД следует создавать (распределённую, целостную) и какие коммуникационные средства необходимы. Выходной документ – комментарий, описывающий цели системы.

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

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

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

4. Концептуальное проектирование – создание концептуальной схемы БД. Спецификации разрабатываются в той степени, которая необходима для перехода к реализации.

Основным выходным документом является единая инфологическая модель (или схема БД на концептуальном уровне ). При разработке данной модели используются информация и функции, которые должна выполнить система, определённые на этапе сбора и определения требований к системе. На данном этапе желательно также определить: 1) правила для данных; 2) правила для процессов; 3) правила для интерфейса.

5. Реализация – процесс превращения концептуальной модели в функциональную БД. Он включает в себя следующие этапы.

1) Выбор и приобретение необходимой СУБД.

2) Преобразование концептуальной (инфологической) модели БД в логическую и физическую модель данных:

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

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

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

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

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

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

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

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

4) Заполнение базы данных.

5) Создание прикладных программ, контроль управления.

6) Обучение пользователей.

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

Таким образом, ЖЦБД включает в себя:

· Изучение предметной области и представление соответствующей документации (1-3).

· Построение инфологической модели (4).

· Реализация (5).

· Оценка работы и поддержка БД (6).

4. Архитектура СУБД.

Рис. Главные компоненты СУБД

Данные, метаданные - содержат не только данные, но и информацию о структуре данных (метаданные ). В реляционной СУБД метаданные включают в себя системные таблицы (отношения), имена отношений, имена атрибутов этих отношений и типы данных этих атрибутов.

Часто СУБД поддерживает индексы данных. Индекс - это структура данных, которая помогает быстро найти элементы данных при наличии части их значения (например, индекс, который находит кортежи конкретного отношения, имеющие заданное значение одного из атрибутов). Индексы - часть хранимых данных, а описания, указывающие, какие атрибуты имеют индексы - часть метаданных.

Менеджер памяти -получает требуемую информацию из места хранения данных и изменяет в нем информацию по требованию расположенных выше уровней системы.

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

· Менеджер файлов контролирует расположение файлов на диске и получает блок или блоки, содержащие файлы, по запросу менеджера буфера (диск в общем случае делится на дисковые блоки - смежные области памяти, содержащие от 4000 до 16000 байт).

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

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

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

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

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

Как правило, система БД поддерживает одновременно множество транзакций. Именно правильное выполнение всех таких транзакций и обеспечивает менеджер транзакций . Правильное выполнение транзакций обеспечивается ACID -свойствами (atomicity , consistency , isolation , durability ):

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

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

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

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

Рассмотрим также 3 типа обращения к СУБД:

1. Запросы - вопросы по поводу данных могут генерироваться двумя способами:

a) с помощью общего интерфейса запросов (например, реляционная СУБД допускает запросы SQL , которые передаются процессору запросов, а также получает ответы на них);

б) с помощью интерфейсов прикладных программ - запросы передаются через специальный интерфейс (через этот интерфейс нельзя передавать произвольные запросы);

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

3. Модификации схемы - это команды администраторов БД, которые имеют право изменять схему БД или создавать новую БД.

Архитектура клиент/сервер. Во многих вариантах современного ПО реализуется архитектура клиент/сервер : один процесс (клиент) посылает запрос для выполнения другому процессу (серверу). Как правило, БД часто разделяется на процесс сервера и несколько процессов клиента.

В простейшей архитектуре клиент/сервер вся СУБД является сервером, за исключением интерфейсов запроса, которые взаимодействуют с пользователем и посылают запросы или другие команды на сервер. Например, реляционная СУБД часто использует язык SQL для представления запросов от клиента к серверу. Затем сервер БД предоставляет клиенту ответ в виде таблицы (отношения). Существует тенденция увеличения нагрузки на клиента, т. к. при наличии множества одновременно работающих пользователей БД с сервером могут возникнуть проблемы.

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

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

Отношение представляет собой двумерную таблицу, содержащую некоторые данные. Математически под N -арным отношением R понимают множество декартова произведения D 1 D 2 … D n множеств (доменов ) D 1, D 2 , …, D n (), необязательно различных:

R D 1 D 2 … D n ,

где D 1 D 2 … D n – полное декартово произведение, т.е. набор всевозможных сочетаний из n элементов каждое, где каждый элемент берется их своего домена.

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

· Домен имеет уникальное имя (в пределах базы данных).

· Домен определен на некотором простом типе данных или на другом домене.

· Домен может иметь некоторое логическое условие , позволяющее описать подмножество данных, допустимых для данного домена.

· Домен несет определенную смысловую нагрузку .

Атрибут отношения есть пара вида <Имя_атрибута: Имя_домена>. Имена атрибутов должны быть уникальны в пределах отношения. Часто имена атрибутов отношения совпадают с именами соответствующих доменов.

Отношение R , определенное на множестве доменов, содержит две части: заголовок и тело.

Заголовок отношения – это фиксированное количество атрибутов отношения:

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

Тело отношения содержит множество кортежей отношения. Каждый кортеж отношения представляет собой множество пар вида <Имя_атрибута: Значение_атрибута>:

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

Отношение обычно записывается в виде:

или короче

,

или просто

Число атрибутов в отношении называют степенью (или -арностью ) отношения. Мощность множества кортежей отношения называют мощностью отношения.

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

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

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

Пусть – схема отношения . – схема отношения после упорядочения имен атрибутов. Тогда

~

Таким образом, для эквивалентных отношений выполняются следующие условия:

· Таблицы имеют одинаковое количество столбцов.

· Таблицы содержат столбцы с одинаковыми наименованиями.

· Столбцы с одинаковыми наименованиями содержат данные из одних и тех же доменов.

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

Все такие таблицы есть различные изображения одного и того же отношения.

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

· В отношении нет одинаковых кортежей .

· Кортежи не упорядочены (сверху вниз) .

· Атрибуты не упорядочены (слева направо) .

· Все значения атрибутов атомарны .

Рис. Схематическое изображение отношения

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

6. Проектирование реляционных баз данных.

При проектирование реляционной БД должны быть решены следующие проблемы:

1) С учетом семантики предметной области необходимо наилучшим способом представить объекты предметной области в виде абстрактной модели данных (даталогическое проектирование). Т.е. - определиться со схемой БД: из каких отношений должны состоять БД, какие атрибуты должны быть у этих отношений, каковы связи между отношениями.

2) Обеспечить эффективность выполнения запросов к базе данных (физическое проектирование БД).

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

· Построение корректной схемы данных ориентируясь на реляционную модель данных.

· Описание схемы БД в терминах выбранной СУБД.

· Описание внешних моделей в терминах выбранной СУБД.

· Описание декларативных правил поддержки целостности БД.

· Разработка процедур поддержки семантической целостности БД.

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

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

Проектирование схемы БД можно выполнить двумя методами:

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

· Метод синтеза – компоновка схемы БД из заданных исходных элементарных зависимостей между объектами предметной области.

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

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

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

В теории реляционных БД обычно выделяют следующие нормальные формы:

первая нормальная форма (1 NF );

· вторая нормальная форма (2 NF );

· третья нормальная форма (3 NF );

· нормальная форма Байса-Кодда ( BCNF );

· четвертая нормальная форма (4 NF );

· пятая нормальная форма или форма проекции - соединения (5 NF или PYNF ).

Основные свойства нормальных форм:

· каждая следующая нормальная форма в некотором смысле лучше предыдущей;

· при переходе к следующей нормальной форме свойства предыдущих нормальных свойств сохраняются.

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

7. Нормальные формы отношений.

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

· Аномалии вставки (INSERT) – хранение в одном отношении разнородной информации.

· Аномалии обновления (UPDATE) –избыточность данных отношения из-за хранения разнородной.

· Аномалии удаления (DELETE) – хранение разнородной информации в одном отношении.

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

Нормализация – разбиение таблицы на несколько, которые обладают лучшими свойствами при обновлении, вставке и удалении данных. Т.е. нормализация представляет собой процесс последовательной замены таблицы ее полными декомпозициями до тех пор, пока все они не будут находиться в 5НФ, однако, на практике достаточно привести таблицы к НФБК.

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

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

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

Заменить , первичный ключ , ФЗ

на , первичный ключ

и , первичный ключ .

2. Таблица имеет первичный (возможный) ключ , поле , которое не является возможным ключом, но функционально зависит от , а также – другое неключевое поле , функционально зависящее от : . Рекомендуется сформировать таблицу содержащую и ( - первичный ключ), и – удалить из первоначальной таблицы: Следует заметить, что для проведения таких операций первоначально следует иметь, в качестве входных данных некоторые «большие» (универсальные) отношения.

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

По опр.1, любое отношение будет находиться в 1НФ, т.е. отношение, удовлетворяющее свойствам отношений: в отношении нет одинаковых кортежей; кортежи не упорядочены; атрибуты не упорядочены и различаются по наименованию; все значения атрибутов атомарны.

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

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

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

Атрибуты отношения называются взаимно-независимыми , если ни один из них не является функционально зависимым от другого.

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

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

При приведении отношений при помощи алгоритма нормализации к отношениям в 3НФ предполагается, что все отношения содержат один потенциальный ключ. Это не всегда верно. Бывают случаи, когда отношение может содержать несколько ключей.

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

Если отношение находится в НФБК, то оно автоматически находится в 3НФ, что следует из определения 4. Чтобы устранить зависимость от детерминантов, не являющихся потенциальными ключами, следует провести декомпозицию, вынося эти детерминанты и зависимые от них части в отдельное отношение.

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

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

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

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

Опр.6. Отношение находится в пятой нормальной форме (5НФ) тогда и только тогда, когда любая имеющаяся зависимость соединения является тривиальной.

Опр.6. тождественно также следует определению.

Опр.7. Отношение не находится в 5НФ, если в отношении найдется нетривиальная зависимость соединения.

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

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

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

Взаимно-независимые атрибуты – это атрибуты, не зависящие один от другого. Если в отношение существует несколько ФЗ, то каждый атрибут или набор атрибутов, от которого зависит другой атрибут, называется детерминантом отношения.

9. Реляционная алгебра.

Реляционная алгебра представляет собой основу доступа к реляционным данным. Основная цель алгебры – обеспечить запись выражений. Выражения могут использоваться для:

· определения области выборки , т.е. определения данных для их выбора, как результата операции выборки;

· определения области обновления , т.е. определения данных для их вставки, изменения или удаления, как результата операции обновления;

· определение (именованных) виртуальных отношений , т.е. представление данных для их визуализации через представления;

· определение снимка, т.е. определение данных для сохранения в виде «мгновенного снимка» отношения;

· определение правил безопасности, т.е. определение данных, для которых осуществляется контроль доступа;

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

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

В реализациях конкретных реляционных СУБД сейчас не используется в чистом виде ни реляционная алгебра, ни реляционное исчисление. Фактическим стандартом доступа к реляционным данным стал язык SQL (Structured Query Language).

Реляционная алгебра, определенная Коддом состоит из 8 операторов, составляющих 2 группы:

• традиционные операции над множествами (объединение, пересечение, вычитание, декартово произведение);
• специальные реляционные операции (выборка, проекция, соединение, деление).

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

Краткий обзор операторов реляционной алгебры.

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

Проекция – возвращает отношение, содержащее все кортежи (т.е. - под кортежи) определенного отношения после исключения из него некоторых атрибутов.

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

Объединение – возвращает отношение, содержащее все кортежи, которые принадлежат или одному из двух определенных отношений, или обоим.

Пересечение – возвращает отношение, содержащее все кортежи, которые принадлежат одновременно двум определенным отношениям.

Вычитание – возвращает отношение, содержащее все кортежи, которые принадлежат первому из двух определенных отношений и не принадлежат второму.

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

Деление – для двух отношений, бинарного и унарного, возвращает отношение, содержащее все значения одного атрибута бинарного отношения, которые соответствуют (в другом атрибуте) всем значениям в унарном отношении.

ЛИТЕРАТУРА

1. Дейт К.Дж. Введение в системы баз данных, 6-е издание: Пер. с англ. – К.; М.; СПб.: Издательский дом «Вильямс», 2000. – 848 с.

2. Конноли Т., Бегг К., Страчан А. Базы данных: проектирование, реализация и сопровождение. Теория и практика, 2-е изд.: Пер. с англ. – М.: Издательский дом «Вильямс», 2000. – 1120 с.

3. Карпова Т.С. Базы данных: модели, разработка, реализация. – СПб.: Питер, 2001. – 304 с.

4. Фаронов В.В., Шумаков П.В. Delphi 4. Руководство разработчика баз данных. – М.: «Нолидж», 1999. – 560 с.

5. Дж. Грофф, П.Вайнберг. SQL: Полное руководство: Пер. с англ. – К.: Издательская группа BHV, 2001. – 816 с.

6. Кен Гетц, Пол Литвин, Майк Гилберт. Access 2000. Руководство разработчика. Т.1, 2. Пер. с англ. – К.: Издательская группа BHV, 2000. – 1264 с, 912 c.

7. Маклаков С.В BPwin и EPwin. CASE-средства разработки информационных систем. – М.: ДИАЛОГ-МИФИ, 2001. – 304 с.

8. Ульман Д., Уидом Д. Введение в системы баз данных / Пер. с англ. – М.: «Лори», 2000. – 374 с.

9. Хомоненко А.Д., Цыганков В.М., Мальцев М.Г. Базы данных: Учебник для высших учебных заведений / Под ред. Проф. А.Д.Хомоненко. – Спб.: КОРОНА принт, 2000. – 416 с.

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

Введение

При проектировании базы данных решаются две основные проблемы.

• Каким образом отобразить объекты предметной области в абстрактные объекты модели данных, чтобы это отображение не противоречило семантике предметной области и было, по возможности, лучшим (эффективным, удобным и т. д.)? Часто эту проблему называют проблемой логического проектирования баз данных.
• Как обеспечить эффективность выполнения запросов к базе данных, т. е. каким образом, имея в виду особенности конкретной СУБД, расположить данные во внешней памяти, создания каких дополнительных структур (например, индексов) потребовать и т. д.? Эту проблему обычно называют проблемой физического проектирования баз данных.

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

Более того, мы не будем касаться очень важного аспекта проектирования – определения ограничений целостности общего вида (за исключением ограничений, задаваемых функциональными и многозначными зависимостями , а также зависимостями проекции/соединения). Дело в том, что при использовании СУБД с развитыми механизмами ограничений целостности (например, SQL-ориентированных систем) трудно предложить какой-либо универсальный подход к определению ограничений целостности. Эти ограничения могут иметь произвольно сложную форму, и их формулировка пока относится скорее к области искусства, чем инженерного мастерства. Самое большее, что предлагается по этому поводу в литературе, это автоматическая проверка непротиворечивости набора ограничений целостности.

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

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

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

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

• первая нормальная форма (1NF) ;
• вторая нормальная форма (2NF) ;
• третья нормальная форма (3NF) ;