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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

· формулирование знаний о системе (определение типов исходных данных и взаимосвязей) и требований к обработке данных, полу­чение с помощью 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) невозможно от­разить информацию о комнате с установленным в ней телефоном до тех пор, пока в нее не помещен ни один сотрудник (при условии, что поле «ФИО» является ключевым).

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

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

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

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

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

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

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

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

Разработка информационной системы предприятия с помощью системы управления базами данных 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. В чем сущность каждой из пяти нормальных форм?

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

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

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

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

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

Введение

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

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

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

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

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

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

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

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

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