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

ЛАБОРАТОРНАЯ РАБОТА 1.

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

Цель: спроектировать БД для выбранной предметной области согласно примеру,

представленном в методическом указании. Провести нормализацию (до 3 нормальной формы).

ЗАДАНИЕ

1) Описать предметную область

2) Выделить ключевые объекты системы

3) Провести инфологическое проектирование

a. Составить и прокомментировать ER-диаграмму

b. Составить и прокомментировать уточненную ER-диаграмму (с атрибутами)

4) Провести логическое проектирование

5) Провести нормализацию (до 3 нормальной формы)

6) Описать ключевые ограничения

Примечание:

Для проектирования рекомендуется использовать приложение Oracle SQL Developer

Data Modeler

(http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html )

или Astah Professional

(http://astah.net/features/er-diagram ).

Также вы можете осуществить проектирование при помощи векторного графического

редактора, редактора диаграмм и блок-схем - Microsoft Visio. Детальное описание установки

редактора вы можете найти в данном методическом указании.

После нормализации количество таблиц должно не превышать 7, желательно 5

таблиц.

ХОД РАБОТЫ

1. Выбрать вариант задания

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

согласно варианту задания. Разработать ER-диаграмму сущностей

3. Осуществить процесс логического проектирования, подробно расписав процесс

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

нормализации отношений.

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

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

представленные в табличном виде (как в примере таблицы с 1.8 по 1.17).

5. Подготовить отчет о проделанной работе. Структура отчета:

 титульный лист;

 задание;

 описание процесса проектирования (инфологическое проектирование и ER-модель,

аналогично примеру, представленному в данном методическому указании.);

 заключение;

Номер варианта задания определяется по последним двум цифрам номера

зачетной книжки. Если образуемое ими число больше 24, то следует взять сумму этих

цифр

ВАРИАНТЫ ЗАДАНИЙ

1 музей;

2 минимаркет;

3 поликлиника;

4 пиццерия;

5 прокат;

6 гостиница;

7 документооборот;

8 строительная компания;

9 спортивный клуб;

10 завод по изготовлению

11 транспортная компания;

12 туристическая компания;

автомобильных деталей;

13 картинная галерея;

14 товары-почтой;

15 автомастерская;

16 книжный склад;

17 авиакомпания;

18 аудио коллекция;

19 компания по сбыту

20 фирма по ремонту;

21 касса театра.

лекарственных препаратов;

22 кулинария;

23 деканат;

24 поликлиника;

ОГЛАВЛЕНИЕ

ЗАДАНИЕ

ХОД РАБОТЫ

ВАРИАНТЫ ЗАДАНИЙ

1. КРАТКАЯ ТЕОРИЯ

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

1.2. Этапы проектирования базы данных:

1.3. Инфологическое проектирование

1.3.1. Entity-Relationship Diagrams

1.3.2. Логическое проектирование

1.3.3. Физическое проектирование

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

1.4.1. Инфологическое проектирование

1.4.2. Логическое проектирование реляционной БД

1.5. Нормализация полученных отношений (до 3НФ)

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

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

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

1.6. Нормализация отношений для БД «Издательская компанияª

1.1.1.

1 НФ (нормальная форма)

1.6.1.

2 НФ (нормальная форма)

1.6.2.

3 НФ (нормальная форма)

1.6.3. Окончательные схемы отношений

1.7. Пример нормализации отношение №3

1.7.1.

1 НФ (нормальная форма)

1.7.2. Определение функциональной зависимости

1.7.3.

2 НФ (нормальная форма)

1.7.4.

3НФ (нормальная форма)

1.7.5. Вывод

1.7.6. Определение дополнительных ограничений целостности

2. ЗАКЛЮЧЕНИЕ

1. КРАТКАЯ ТЕОРИЯ

Перед созданием базы данных разработчик должен определить, из каких таблиц

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

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

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

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

Перед созданием базы данных необходимо располагать описанием выбранной

предметной области, которое должно охватывать реальные объекты и процессы,

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

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

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

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

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

предметной области может отображаться информационно-логической моделью. На основе

этой модели легко создается реляционная база данных.

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

Понятие реляционный

(англ. Relation

- отношение) связано с разработками

известного английского специалиста в области систем баз данных Эдгара Кодда (Edgar

Codd).

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

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

аппарата алгебры отношений и реляционного исчисления для обработки данных.

Реляционная модель ориентирована на организацию данных в виде двумерных

таблиц. Каждая реляционная таблица представляет собой двумерный массив и обладает

следующими свойствами:

 каждый элемент таблицы - один элемент данных;

 все ячейки в столбце таблицы однородные, то есть все элементы в столбце имеют

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

 каждый столбец имеет уникальное имя;

 одинаковые строки в таблице отсутствуют;

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

Основными понятиями реляционных баз данных являются тип данных, домен,

атрибут, кортеж, первичный ключ и отношение.

Для начала покажем смысл этих понятий на примере отношения СОТРУДНИКИ,

содержащего информацию о сотрудниках некоторой организации как показано на рисунке

1.1.

Типы данных

Целые

Строки

Целые

Деньги

числа

символов

числа

Домены

Номера

Размер

Номера

Имена

пропусков

выплат

отделов

Первичный

ключ

Атрибуты

СОТР_НОМЕР

СОТР_ИМЯ

СОТР_ЗАРП

СОТР_ОТД_НОМЕР

2934

Иванов

112 000

310

Отношение

2935

Петров

144 000

310

сотрудники

2936

Сидоров

92 000

313

Кортежи

2937

Федоров

110 000

310

2938

Иванова

112 000

315

Рисунок 1.1 - Схема реляционного отношения

Тип данных в реляционной модели данных полностью адекватно понятию типа

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

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

данных (таких как "деньги"), а также специальных "темпоральных" данных (дата, время,

временной интервал).

Понятие домена более специфично для баз данных, хотя и имеет некоторые аналогии

с подтипами в некоторых языках программирования. В самом общем виде домен

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

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

Если вычисление этого логического выражения дает результат "истина", то элемент данных

является элементом домена.

Наиболее правильной интуитивной трактовкой понятия домена является понимание

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

домен "Имена" в схеме, показанной выше, определен на базовом типе строк символов, но в

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

частности, такие строки не могут начинаться с мягкого знака).

Кортеж, соответствующий данной схеме отношения, - это множество пар {имя

атрибута, значение}, которое содержит одно вхождение каждого имени атрибута,

принадлежащего схеме отношения. "Значение" является допустимым значением домена

данного атрибута (или типа данных, если понятие домена не поддерживается). Тем самым,

степень или "арность" кортежа, т.е. число элементов в нем, совпадает с "арностью"

соответствующей схемы отношения. Попросту говоря, кортеж - это набор именованных

значений заданного типа.

Отношение - это множество кортежей, соответствующих одной схеме отношения.

Иногда, чтобы не путаться, говорят "отношение-схема" и "отношение-экземпляр", иногда

схему отношения называют заголовком отношения, а отношение как набор кортежей -

телом отношения. На самом деле, понятие схемы отношения ближе всего к понятию

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

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

схемой.

В свою очередь отношения имеют два важных свойства:

арность - число атрибутов в отношении;

мощность - это кардинальное число отношения, т.е. число кортежей (строк) в

отношении.

Каждое реляционное отношение соответствует одной сущности (объекту предметной

области) и в него вносятся все атрибуты сущности. Для каждого отношения необходимо

определить первичный ключ и внешние ключи.

Ключ или потенциальный ключ - это минимальный набор атрибутов, по значениям

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

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

сущность по оставшимся. Каждая сущность должна, но не обязана обладать хотя бы одним

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

идентифицирующий запись.

Значение первичного ключа в таблице БД должно быть уникальным, то есть в таблице

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

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

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

таблицы.

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

суррогатный первичный ключ, который не несёт смысловой нагрузки и служит только

для идентификации записей (например ID записи).

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

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

или является составным (не менее трёх атрибутов).

Потенциальными ключами отношения АВТОРЫ являются атрибуты Паспортные

данные и ИНН. Первый хранится как длинная строка, а последний по условиям предметной

области не является обязательным. Поэтому для авторов необходимо ввести суррогатный

ключ - A_id. Книги можно идентифицировать по атрибуту Контракт: его номер обязателен

и уникален. Потенциальные ключи отношения СОТРУДНИКИ

- атрибуты ИНН,

Паспортные данные, Табельный номер, причём все они обязательные. Табельный

номер занимает меньше памяти, чем ИНН, поэтому он и будет первичным ключом. Кортежи

отношения ЗАКАЗЫ можно идентифицировать ключом Номер заказа.

Потенциальными ключами вспомогательных отношений являются комбинации

первичных ключей соответствующих базовых отношений.

Отношения приведены в таблице 1.1-1.7. Для каждого отношения указаны атрибуты

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

числовой, C - символьный, D - дата (последний имеет стандартную длину, зависящую от

СУБД, поэтому она не указывается).

1.2. Этапы проектирования базы данных:

 инфологическое проектирование;

 определение требований к операционной обстановке, в которой будет

функционировать информационная система;

 выбор системы управления базой данных (СУБД) и других инструментальных

программных средств;

 логическое проектирование БД;

 физическое проектирование БД.

1.3. Инфологическое проектирование

Инфологическое проектирование - построение семантической модели предметной

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

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

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

формальным аппаратом. Обычно используют графические нотации, подобные ER-

диаграммам.

Чаще всего инфологическая (концептуальная) модель базы данных включает в себя:

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

ними;

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

данных и к связям между ними.

Пример инфологического проектирования показан на рисунке 1.

Рисунок 1 - Инфологическая модель

Entity-Relationship Diagrams

Имеется целый ряд методик создания информационно-логических моделей. Одна из

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

(Entity-Relationship Diagrams). В русскоязычной литературе эти диаграммы называют

«объект - отношениеª либо «сущность - связьª. Модель ERD была предложена Питером

Пин Шен Ченом в 1976 г. К настоящему времени разработано несколько ее разновидностей,

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

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

они широко используются в CASE-средствах (Computer Aided Software Engineering).

Рассмотрим используемую терминологию и обозначения.

Сущность (Entity) - реальный либо воображаемый объект, имеющий существенное

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

хранению.

Каждая сущность должна обладать уникальным идентификатором. Каждый

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

экземпляров данного типа (сущности).

Каждая сущность должна обладать некоторыми свойствами:

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

та же интерпретация (определение сущности). И наоборот: одна и та же интерпретация не

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

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

сущности, либо наследуются ею через связь;

 обладать одним или несколькими атрибутами, которые однозначно

идентифицируют каждый экземпляр сущности.

Связь

(Relationship)

- поименованная ассоциация между двумя сущностями,

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

сущностей - независимая, называется родительской сущностью, другая - зависимая,

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

родительской сущности ассоциирован с произвольным (в том числе нулевым) количеством

экземпляров дочерней сущности. Каждый экземпляр сущности-потомка ассоциирован в

точности с одним экземпляром сущности-родителя. Таким образом, экземпляр сущности-

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

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

возле линии связи.

Атрибут

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

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

количественной характеристики или выражения состояния сущности. Атрибут

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

абстрактных объектов (людей, мест, событий, состояний, идей, пар предметов и т. д.)

Уникальный идентификатор - это атрибут или совокупность атрибутов и/или

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

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

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

участвуют также атрибуты другой сущности - родителя.

При построении ER-модели используются следующие принципы:

 сущности на диаграмме представляются прямоугольниками;

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

 каждой сущности должно быть присвоено уникальное имя;

 имена сущностей необходимо задавать в единственном числе;

 связи на диаграмме представляются линиями, идущими от одной

 сущности (таблицы) к другой;

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

 связанные таблицы разделяют на родительские и дочерние;

 родительские таблицы отображаются прямоугольниками с прямыми углами,

дочерние - со скругленными.

Логическое проектирование

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

базы данных, например, реляционной модели данных. Для реляционной модели данных

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

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

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

осуществляется по формальным правилам. Этот этап может быть в значительной степени

автоматизирован.

На этапе логического проектирования учитывается специфика конкретной модели

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

Пример логической модели представлен на рисунке 2.

Рисунок 2 - Логическая модель

Физическое проектирование

Физическое проектирование - создание схемы базы данных для конкретной СУБД.

Специфика конкретной СУБД может включать в себя ограничения на именование объектов

базы данных, ограничения на поддерживаемые типы данных и т. п. Кроме того, специфика

конкретной СУБД при физическом проектировании включает выбор решений, связанных с

физической средой хранения данных

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

разделение БД по файлам и устройствам, методов доступа к данным), создание индексов и т.

д.

Результатом физического проектирования, например, может стать скрипт на языке SQL.

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

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

деятельностью.

Инфологическое проектирование

Анализ предметной области

База данных создаётся для информационного обслуживания редакторов, менеджеров

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

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

разнообразные отчёты.

В соответствии с предметной областью система строится с учётом следующих

особенностей:

 каждая книга издаётся в рамках контракта;

 книга может быть написана несколькими авторами;

 контракт подписывается одним менеджером и всеми авторами книги;

 каждый автор может написать несколько книг (по разным контрактам);

 порядок, в котором авторы указаны на обложке, влияет на размер гонорара;

 если сотрудник является редактором, то он может работать одновременно над

несколькими книгами;

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

редактор;

 каждый заказ оформляется на одного заказчика;

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

Для инфологического проектирования воспользуемся методом «сущность-связьª. Для

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

объектов реального мира

(главная проблема что считать объектом). Объект

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

мира. Сущность реального мира - объекты - не обязательно материальны - важно понятие

существенно и различимо для других. Между объектами могут возникать связи трех видов:

 один к одному 1:1 (пациент: место в палате);

 один к многим 1:n и многие к одному n:1;

 многие ко многим n:n (пациент : хирург).

При построении моделей используются следующие геометрические фигуры:

Элемент ER-модели

Условно графическое представление

Объект

Связь

Атрибут

В настоящее время существует большое множество прикладных программ для

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

как специализированные средства

(например, Visio), так и средства построения

графических образов

(Umbrello, OO Draw). Либо использовать облачный сервис

https://www.draw.io/.

Выделим базовые сущности этой предметной области:

 Сотрудники компании. Атрибуты сотрудников - ФИО, табельный номер, пол, дата

рождения, паспортные данные, ИНН, должность, оклад, домашний адрес и телефоны. Для

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

о подписанных контрактах.

 Авторы. Атрибуты авторов

- ФИО, ИНН

(индивидуальный номер

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

необходимо хранить сведения о написанных книгах.

 Книги. Атрибуты книги - авторы, название, тираж, дата выхода, цена одного

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

 Контракты будем рассматривать вместе с книгами, т.к. каждая книга связана

непосредственно с одним контрактом.

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

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

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

экземпляров.

ER-диаграмма сущностей издательской компании приведена на рисунке 1.1

Авторы

Написать

Книги

Редактировать

Выполнить

Заказать

Контракт

Заключить

Заказы

Сотрудники

Рисунок 1.1 - ER-диаграмма издательской компании

Логическое проектирование реляционной БД

Преобразование ER-диаграммы в схему базы данных

Связь типа 1:n (один-ко-многим) между отношениями реализуется через внешний

ключ. Ключ вводится для того отношения, к которому осуществляется множественная

связь (КНИГИ).

Связь редактировать между отношениями КНИГИ и ЗАКАЗЫ принадлежит к типу

n:m (многие-ко-многим). Этот тип связи реализуется через вспомогательное отношение,

которое является соединением первичных ключей соответствующих отношений.

Для создания логической модели необходимо

«уточнитьª созданную ранее

инфологическую модель. Уточнить - значит конкретизировать, добавить атрибуты в ранее

созданные сущности. Уточняя, мы отмечаем в каждой сущности какие именно данные,

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

Уточнённая схема реляционной БД издательской компании приведена на рисунке 1.2.

Заказы

Номер заказа

Заказчик

Строки_заказа

Дата поступ.заказа

НомерЗаказа

Адрес заказчика

КодКниги

Дата выполн. Заказа

Количество

Адрес заказчика

Дата выполн. заказа

Номер контракта

Авторы

Дата подпис. Контракта

КодАвтора

Авторы_Книги

Менеджер

Книги_Редакторы

ФИО

Название книги

ПаспортныеДанные

Номер в списке

Цена

КодКниги

ИНН

Гонорар

Затраты

КодРедактора

Адрес

Авторский. Гонорар

Телефон

Дата выхода

Тираж

Ответств. редактор

Сотрудники

Табельный номер

ФИО

Дата рождения

Пол

Паспорт. Данные

ИНН

Должность

Оклад

Адрес

Телефон

Рисунок 1.3 - Логическая модель БД

В этой модели были добавлены дополнительные таблицы:

 Книги_Редакторы. Эта сущность включена в диаграмму, во-первых, для

устранения связи многие-ко-многим между редакторами и книгами, т.к. каждую книгу

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

множество книг. Во-вторых, т.к. и редакторы входят в число сотрудников и менеджеры, то

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

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

 Строки_заказа. Эта сущность определена для устранения связи многие-ко-многим

между заказами и книгами т.к. в одном заказе может быть множество книг. Также в этой

сущности добавлен атрибут «Количествоª для обозначения количества книг.

 Авторы_Книги. Эта сущность определена для устранения связи многие-ко-

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

Составление реляционных отношений

Понятие реляционный (англ. relation - отношение) связано с разработками известного

английского специалиста в области систем баз данных Эдгара Кодда (Edgar Codd).

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

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

отношений и реляционного исчисления для обработки данных.

Реляционная модель ориентирована на организацию данных в виде двумерных таблиц.

Каждая реляционная таблица представляет собой двумерный массив и обладает следующими

свойствами:

 каждый элемент таблицы - один элемент данных;

 все ячейки в столбце таблицы однородные, то есть все элементы в столбце имеют

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

 каждый столбец имеет уникальное имя;

 одинаковые строки в таблице отсутствуют;

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

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

кортеж, первичный ключ и отношение.

Для начала покажем смысл этих понятий на примере отношения СОТРУДНИКИ,

содержащего информацию о сотрудниках некоторой организации как показано на рисунке 1.4.

Рисунок 1.4 - Схема реляционного отношения