Батин Н.В. Проектирование баз данных

Подождите немного. Документ загружается.

Министерство образования Республики Беларусь

Учреждение образования

“Белорусский государственный университет

информатики и радиоэлектроники”

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

Н.В. Батин

ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ

Учебное пособие

для студентов специальности

“Автоматизированные системы обработки информации”

дневной и дистанционной форм обучения

Минск 2007

СОДЕРЖАНИЕ

ВВЕДЕНИЕ.................................................................................................................. 3

1 ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ: ОСНОВНЫЕ ПОНЯТИЯ................... 4

1.1 Основные цели и этапы проектирования баз данных................................ 4

1.2 Нормальные формы....................................................................................... 5

1.3 Первая нормальная форма............................................................................ 8

1.4 Вторая нормальная форма .......................................................................... 10

1.5 Третья нормальная форма........................................................................... 13

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

1.7 Четвертая нормальная форма ..................................................................... 17

1.8 Другие нормальные формы ........................................................................ 19

2 ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ НА ОСНОВЕ ER-МОДЕЛЕЙ........... 20

2.1 Понятие ER-модели..................................................................................... 20

2.2 ER-модель объекта ...................................................................................... 20

2.3 ER-модели связей между объектами. Типовые инфологические и

даталогические модели............................................................................... 25

3 ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ НА ОСНОВЕ СЕМАНТИЧЕСКИХ

ОБЪЕКТНЫХ МОДЕЛЕЙ ................................................................................. 38

3.1 Понятие семантической объектной модели.............................................. 38

3.2 Семантический объект................................................................................ 38

3.3 Семантические объектные модели связей между объектами................ 40

3.4 Типы семантических объектов................................................................... 42

4 СИСТЕМА АВТОМАТИЗИРОВАННОГО ПРОЕКТИРОВАНИЯ БАЗ

ДАННЫХ ERwin ................................................................................................. 43

4.1 Назначение системы ERWin. Основные этапы проектирования базы

данных с использованием ERWin ............................................................. 43

4.2 Основные элементы интерфейса ERWin .................................................. 44

4.3 Создание логической модели данных ....................................................... 45

ЛИТЕРАТУРА........................................................................................................... 56

ВВЕДЕНИЕ

В пособии приводится теоретический и практический материал, связанный

с рядом задач, решаемых в ходе проектирования реляционных баз данных (БД).

Рассматриваются основные этапы проектирования БД и задачи каждого из

этапов, а также одна из задач, практически всегда решаемая в процессе проек-

тирования реляционных БД – нормализация отношений (глава 1, [1,2,5]); широ-

ко применяемые технологии проектирования реляционных БД – ER-модели

(глава 2, [1,2,3,8]) и СОМ-модели (глава 3, [2]), основные возможности про-

граммного пакета для автоматизированного проектирования БД на основе ER-

моделей – AllFusion ERWin Data Modeler (глава 4, [4,9]).

Рекомендуемая литература включает источники по основным задачам и

методам проектирования БД [1-3,5,7,8,16], специализированным программным

средствам автоматизации проектирования БД [4,9,10,12], теоретическим вопро-

сам проектирования БД [2,5,6,11,13-15].

1 ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ: ОСНОВНЫЕ ПОНЯТИЯ

1.1 Основные цели и этапы проектирования баз данных

В базе данных (БД) отражается информация об определенной предметной

области. Под предметной областью будем понимать часть реального мира, дан-

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

может представлять собой, например, предприятие, отрасль промышленности,

торговую фирму, учебное заведение, магазин, библиотеку и т.д. Информация,

необходимая для описания предметной области, зависит от типа самой пред-

метной области, а также от того, какую именно информацию об этой предмет-

ной области требуется хранить и обрабатывать. Эта информация может вклю-

чать сведения о предметах, событиях и их характеристиках (например, сведе-

ния о работниках предприятия, финансовых сделках, автомобилях, товарах,

учебных предметах и т.д.).

Основные цели проектирования баз данных (ПБД) следующие:

− обеспечение хранения в БД всех необходимых данных;

− обеспечение получения данных по всем необходимым запросам;

− сокращение избыточности и дублирования данных;

− обеспечение целостности данных: исключение потери данных, противо-

речий в содержании БД, нарушений смысла данных;

− сокращение времени доступа к данным и получения данных по запро-

сам.

В процессе проектирования баз данных выделяют три основных этапа.

1.1.1 Концептуальное (инфологическое) проектирование – анализ пред-

метной области и ее описание. Этот этап осуществляется без ориентации на ка-

кие-либо конкретные программные или технические средства. Результатом

данного этапа является формализованная модель предметной области, постро-

енная с использованием специальных языковых средств (обычно – графиче-

ских). Инфологическая модель, как правило, включает следующие основные

элементы:

− описание объектов предметной области;

− описание атрибутов (свойств) объектов;

− описание связей между объектами.

Пусть, например, проектируется база данных о промышленном предпри-

ятии. Объектами предметной области в данном случае могут являться работни-

ки предприятия, его подразделения, другие предприятия – поставщики сырья

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

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

(атрибуты) – фамилия, адрес и профессия работника, стоимость изделия, объем

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

друг с другом: работник работает в некотором подразделении, подразделения

выпускают продукцию, продукция поставляется по договорам другим предпри-

ятиям и т.д.

Для описания объектов предметной области, их атрибутов и связей между

ними обычно применяются стандартизированные системы графических обо-

значений. Чаще всего применяются ER-модели (ER-диаграммы), подробно рас-

сматриваемые в разделе 2, и семантические объектные модели (COM-модели),

рассматриваемые в [2].

Кроме того, инфологическая модель может включать:

− описание основных запросов к проектируемой БД;

− описание документооборота, т.е. документов, используемых в качестве

источников данных для БД или составляемых на основе БД;

− описание алгоритмических связей между данными (например, алгорит-

мы и формулы для вычисления каких-либо величин, хранящихся в БД или оп-

ределяемых на основе БД);

− описание ограничений целостности, т.е. правил, обеспечивающих акту-

альность и непротиворечивость данных. Ограничения целостности представ-

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

1.1.2 Даталогическое (логическое) проектирование – описание логиче-

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

(СУБД), для которой проектируется БД. Такое описание (даталогическая мо-

дель) строится на основе инфологической модели по определенным правилам.

Для реляционных БД (т.е. БД, где данные представлены в виде таблиц) датало-

гическая модель включает:

− описание таблиц;

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

− описание атрибутов.

1.1.3 Физическое проектирование – описание физической структуры БД,

т.е. ее размещения на запоминающем устройстве. Такое описание называется

физической моделью. Физическая модель включает:

− тип носителя;

− способы организации данных;

− способы управления свободной памятью;

− способы сжатия данных и т.д.

Этот этап, как правило, в основным скрыт от проектировщика БД, так как

реализуется средствами СУБД. Поэтому в данном пособии этот этап не рас-

сматривается.

Примечание - При использовании систем автоматизированного проектирования БД

этап инфологического проектирования обычно называют логическим проектированием, а

этапы даталогического и физического проектирования – физическим проектированием.

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

Обычно БД, разработанная по правилам инфологического и даталогиче-

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

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

Тем не менее, для БД, полученной по результатам даталогического проектиро-

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

Такая проверка тем более необходима, если БД проектируется “на глаз”, без

использования каких-либо строгих методик инфологического и даталогическо-

го проектирования.

Нормализация обеспечивает:

− минимальную избыточность данных (все данные хранятся один раз);

− минимальный физический объем данных;

− ускорение доступа к данным;

− сокращение риска потери данных или их искажения при внесении изме-

нений в БД.

Нормализация выполняется для каждой таблицы, входящей в БД. Выделя-

ется несколько уровней нормализации (нормальных форм): первая, вторая, тре-

тья и т.д. Нормализация каждой таблицы выполняется поэтапно: сначала таб-

лица приводится к первой нормальной форме, затем – ко второй, и. т.д. При пе-

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

(аномалии), присущие предыдущей нормальной форме.

На практике в большинстве случаев достаточным оказывается приведение

к третьей нормальной форме (см. подраздел 1.5). Иногда требуется приведение

к нормальной форме Бойса-Кодда (подраздел 1.6) и к четвертой нормальной

форме (1.7). Приведение к нормальным формам более высоких порядков (пятая,

шестая и т.д.) требуется редко.

Примечание - В данном пособии используется термин “нормализация баз данных” (или

“нормализация таблиц”, или просто “нормализация”). Более точным является термин “нор-

мализация отношений”, обычно применяемый в теории проектирования БД. Подробно тео-

ретические основы проектирования БД рассматриваются, например, в [5,6].

Процесс нормализации БД рассмотрим на следующем примере.

Пример – Проектируется БД организации, проектирующей здания и со-

оружения (проектно-строительная организация). БД должна содержать инфор-

мацию о сотрудниках организации, о выполняемых организацией проектах (т.е.

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

сотрудников над проектами. Для каждого сотрудника должна храниться сле-

дующая информация:

− табельный номер;

− фамилия;

− специальность;

− оклад.

Для каждого проекта должна храниться следующая информация:

− номер проекта;

− адрес проектируемого объекта;

− тип объекта (например, жилой дом, склад, заводской цех и т.д.);

− нормативный документ, в соответствии с которым проектируется объект

(например, некоторый стандарт, строительные нормы и правила и т.д.).

Кроме того, необходимо хранить информацию о работе сотрудников над

проектами: перечень проектов, над которыми работает каждый сотрудник (или,

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

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

Пусть по результатам анализа предметной области (проектно-строительной

организации) выявлены следующие особенности данных, которые требуется

хранить и обрабатывать в БД:

− табельный номер сотрудника является уникальным, т.е. все сотрудники

имеют разные табельные номера. Поэтому табельный номер может использо-

ваться в качестве идентификатора сотрудника. Аналогично, номер проекта мо-

жет использоваться в качестве идентификатора проекта;

− среди сотрудников могут быть однофамильцы, поэтому фамилия со-

трудника не может использоваться в качестве идентификатора;

− каждый сотрудник имеет только одну специальность;

− оклад сотрудника не зависит от его специальности, а также от проектов,

над которыми он в данный момент работает;

− каждый сотрудник может работать над несколькими проектами, но мо-

жет (по крайней мере, временно) не работать ни над одним проектом, напри-

мер, во время отпуска;

− над каждым проектом работает несколько сотрудников. В то же время

могут быть проекты, на которые не назначен ни один сотрудник (например,

сразу после заключения договора о выполнении данного проекта);

− по каждому проекту разрабатывается только один объект строительства;

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

Например, строительство всех жилых домов регламентируется некоторым од-

ним определенным документом, строительство всех складов – другим докумен-

том, и т.д.;

− работа сотрудника над проектом составляет один интервал времени. На-

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

том с 1 марта по 31 июля, а затем над тем же проектом – с 1 октября по 30 но-

ября.

Примечание – Выявление всех особенностей данных, которые должны храниться в БД

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

Приведем таблицу, содержащую все данные, которые требуется хранить в

БД проектно-строительной организации (таблица 1.1). В таблице также приве-

дены примеры таких данных.

Таблица 1.1 представляет собой просто набор данных в произвольной

форме. Она не является реляционной базой данных. Другими словами, данные в

таком виде, как они приведены в таблице 1.1, не могут быть введены ни в одной

СУБД, так как они фактически не разбиты на строки и столбцы. Например,

данные о сотруднике с табельным номером 15 содержатся в одной строке, од-

нако для описания проектов, над которыми он работает, требуются две строки.

Говоря более строго, атрибуты Номер проекта, Адрес, Тип объекта, Норма-

тивный документ, Дата начала и Дата окончания для сотрудника с табельным

номером 15 имеют не по одному, а по два значения (например, атрибут Номер

объекта для этого сотрудника имеет значения 2042 и 2140).

Таблица 1.1 – Данные для БД проектно-строительной организации

Работа Табель-

ный но-

мер

Фами-

лия

Специаль-

ность

Ок-

лад

Номер

проек-

та

Адрес Тип

объек-

та

Норматив-

ный доку-

мент

Начало Оконча-

ние

12 Андреев инженер-

электрик

500 2140 ул.Лесная,2 жилой

дом

СНиП 75 15.01.2007 25.02.2007

15 Иванов инженер-

строитель

400 2042 ул.Заводская,

склад СНиП 62 5.01.2007 30.01.2007

2140 ул.Лесная,2 жилой

дом

СНиП 75 10.01.2007 1.03.2007

27 Мишин архитек-

тор

600 2060 ул.Речная, 12 жилой

дом

СНиП 75 10.01.2007 1.03.2007

30 Яковлев инженер-

строитель

500 2080 ул.Красная, 7 мага-

зин

СНиП 40 1.02.2007 25.02.2007

Следует также обратить внимание, что столбец Работа разделяется на два

“подстолбца”, содержащих дату начала и окончания работы сотрудника над

проектом.

Недостатки, затрудняющие ввод данных в БД и работу с ними, будут по-

степенно устраняться в процессе нормализации.

1.3 Первая нормальная форма

Таблица находится в первой нормальной форме (1НФ), если все содер-

жащиеся в ней атрибуты имеют атомарные (одиночные) значения.

Для приведения к 1НФ строки и/или столбцы, содержащие неатомарные

атрибуты, разбиваются на несколько строк и/или столбцов. Например, в табли-

це 1.1 данные о сотруднике с номером 15 следует представить в виде двух

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

работает данный сотрудник. Столбец Работа необходимо разделить на два

столбца: Дата начала и Дата окончания. БД проектно-строительной организа-

ции, приведенная к 1НФ, показана в таблице 1.2.

Таблица 1.2 – БД проектно-строительной организации в первой нормальной форме

Табельный

номер

Фамилия Специаль-

ность

Оклад Номер

проекта

Адрес Тип

объекта

Нормативный

документ

Дата на-

чала

Дата

окончания

12 Андреев инженер-

электрик

500 2140 ул.Лесная,2 жилой

дом

СНиП 75 15.01.2007 25.02.2007

15 Иванов инженер-

строитель

400 2042 ул.Заводская,

склад СНиП 62 5.01.2007 30.01.2007

15 Иванов инженер-

строитель

400 2140 ул.Лесная,2 жилой

дом

СНиП 75 10.01.2007 1.03.2007

27 Мишин архитектор 600 2060 ул.Речная, 12 жилой

дом

СНиП 75 10.01.2007 1.03.2007

30 Яковлев инженер-

строитель

500 2080 ул.Красная, 7 магазин СНиП 40 1.02.2007 25.02.2007

Ключ БД, приведенной в таблице 1.2, является составным и состоит из

двух атрибутов: Табельный номер и Номер проекта, так как любая комбинация

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

обозначать такой ключ как (Табельный номер, Номер проекта). В таблицах бу-

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

Очевидно, что атрибуты Табельный номер и Номер проекта по отдельно-

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

то же значение табельного номера может встречаться в БД несколько раз. На-

пример, значение табельного номера 15 встречается в БД дважды, так как со-

трудник Иванов работает на двух проектах. Атрибут Номер проекта также сам

по себе не является ключевым, так как, например, номер проекта 2140 встреча-

ется в БД дважды (на этом проекте работают два сотрудника).

Примечание – Как отмечено выше, приведение к 1НФ необходимо, чтобы ввести дан-

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

вой нормальной форме, не существует. При этом “степень” разбиения атрибутов на атомар-

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

данной БД. Например, если станет известно, что может достаточно часто требоваться ин-

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

разбить столбец Адрес на столбцы Улица и Дом.

Легко видеть, что БД, приведенная в таблице 1.2, имеет целый ряд сущест-

венных взаимосвязанных недостатков:

− большая избыточность данных: многие данные хранятся многократно.

Например, данные о каждом сотруднике (табельный номер, фамилия, специ-

альность, оклад) хранятся столько раз, сколько имеется проектов, над которыми

работает данный сотрудник. Данные о каждом проекте (номер проекта, адрес,

тип объекта, нормативный документ) хранятся столько раз, сколько имеется со-

трудников, работающих над данным проектом. Информация о том, какой нор-

мативный документ соответствует каждому типу объекта, также хранится не-

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

− аномалия добавления (ввода): невозможность ввода данных о сотрудни-

ке, не назначенном ни на один проект (так как при этом одно из полей, состав-

ляющих ключ БД – номер проекта – оказывается пустым). Аналогичным обра-

зом невозможным оказывается ввод данных о проекте, пока на него не назначен

ни один сотрудник;

− аномалия удаления: при удалении данных о проекте могут быть потеря-

ны данные о работниках, занятых в данном проекте, если они не заняты в дру-

гих проектах. Аналогично, при удалении данных о сотруднике можно потерять

данные о проекте, если этот сотрудник был единственным, работавшим в дан-

ном проекте;

− аномалия обновления: если изменяется какой-либо атрибут, то его при-

ходится изменять во многих записях. Например, если у сотрудника изменится

оклад, то изменение потребуется вносить столько раз, сколько имеется проек-

тов, в которых работает данный сотрудник. Кроме того, при этом происходит

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

бута Оклад у одного и того же сотрудника оказываются разными.

Эти недостатки будут постепенно устраняться по мере приведения БД к

последующим нормальным формам.

1.4 Вторая нормальная форма

Прежде чем приступать к рассмотрению второй и последующих нормаль-

ных форм, приведем несколько необходимых определений.

Атрибут B находится в функциональной зависимости от атрибута (или

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

соответствует одно и только одно значение атрибута B.

Функциональная зависимость атрибута B от атрибута A обозначается сле-

дующим образом: A → B.

В любой БД все неключевые атрибуты находятся в функциональной зави-

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

функциональной зависимости от группы атрибутов (Табельный номер, Номер

проекта). Легко видеть, что любой конкретной комбинации этих атрибутов

всегда соответствует одно и только одно значение любого другого атрибута.

Например, комбинации (15, 2140), где 15 – табельный номер, 2140 – номер про-

екта, соответствует одна фамилия (Иванов), одна специальность (инженер-

строитель), один оклад (400), один адрес (ул. Лесная, 2), один тип объекта (жи-

лой дом), один нормативный документ (СНиП 75), одна дата начала

(10.01.2007), одна дата окончания (1.03.2007).

Атрибут B находится в полной функциональной зависимости от группы

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

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

тей этой группы (т.е. ни от каких из атрибутов, входящих в группу атрибу-

тов A).

Атрибут B находится в неполной функциональной зависимости от

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

ких-либо из частей этой группы (т.е. от некоторых из атрибутов, входящих в

группу атрибутов A).

В рассматриваемом примере атрибуты Фамилия, Специальность и Оклад

находятся в неполной функциональной зависимости от ключа (Табельный но-

мер, Номер проекта), так как находятся в функциональной зависимости от час-

ти этого ключа – атрибута Табельный номер. Из таблицы 1.2 видно, что любому

конкретному значению табельного номера всегда соответствует только одна

фамилия, одна специальность и один оклад. Например, табельному номеру 15

соответствует одна фамилия (Иванов), одна специальность (инженер-

строитель), один оклад (400). В то же время атрибуты Адрес, Тип объекта,

Нормативный документ, Дата начала и Дата окончания не находятся в функ-

циональной зависимости от табельного номера. Например, табельному номеру

15 соответствуют два адреса (ул.Заводская, 8 и ул. Лесная, 2), два типа объек-

тов (склад и жилой дом), два нормативных документа (СНиП 62 и СНиП 75),