Реферат: Методические указания к курсовому проектированию по курсу "Базы данных" Москва

Министерство образования и науки Российской Федерации

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

Московский государственный институт электроники и математики

(Технический университет)


Кафедра вычислительных систем и сетей


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


Методические указания

к курсовому проектированию

по курсу "Базы данных"


Москва

2010


Составитель к.т.н., доцент И.П. Карпова


УДК 681.3


Проектирование реляционных баз данных: Метод. указания к курсовому проектированию по курсу "Базы данных" / Московский государственный институт электроники и математики; Сост.: И.П. Карпова. – М., 2010. – 32 с.


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

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


ISBN 5-230-16273-2

СОДЕРЖАНИЕ


^ ЦЕЛИ РАБОТЫ 5

1. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ 5

1.1. Общие положения 5

1.2. Последовательность проектирования базы данных 6

1.3. Особенности проектирования реляционной базы данных 9

^ 2. ПРИМЕР ПРОЕКТИРОВАНИЯ РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ 11

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

2.2. Определение требований к операционной обстановке 14

2.3. Выбор СУБД и других программных средств 14

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

2.5. Реализация проекта базы данных 28

3. ВЫПОЛНЕНИЕ КУРСОВОГО ПРОЕКТА 33

4. ВАРИАНТЫ ЗАДАНИЙ НА КУРСОВОЕ ПРОЕКТИРОВАНИЕ 33

Библиографический список 34



^ ЦЕЛИ РАБОТЫ
Цель курсового проектирования – применение на практике знаний, полученных в процессе изучения курса "Базы данных" [1], и получение практических навыков создания автоматизированных информационных систем (АИС), основанных на базах данных.
^ 1. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ 1.1. Общие положения
Проектирование базы данных (БД) является одной из наиболее сложных и ответственных задач, связанных с созданием АИС.

Проектирование базы данных – это процесс, который подразумевает использование определённой технологии. Никто не сомневается в том, что в случае нарушения технологии изготовления печатной платы, например, эта плата либо вообще не будет работать, либо не будет соответствовать заявленным характеристикам. Но почему-то считается, что соблюдать технологию проектирования БД (и вообще программного обеспечения) совершенно необязательно. И начинают работу по реализации реляционной БД с создания таблиц. Получившаяся в ходе такого "проектирования" база данных будет ненадёжной, неэффективной и сложной в сопровождении. (Исключением могут быть случаи простых предметных областей, которые можно отразить в базе данных, состоящей из 3-4 таблиц). Поэтому при создании базы данных необходимо придерживаться определённой технологии проектирования БД.

Опишем вкратце процесс проектирования реляционной базы данных. (Более подробно этот процесс изложен в [1, 2]).

База данных – это, фактически, модель предметной области (ПрО). Значит, для создания БД надо сначала проанализировать ПрО и создать её модель (это называется инфологическим проектированием).

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

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

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

Модель ПрО может быть описана любым удобным для разработчика способом (словесное описание, набор формул, диаграмма потоков данных и т.п.). Но, если при проектировании баз данных используется метод сущность–связь, то схема ПрО выполняется в виде ER–диаграммы (entity-relation diagram, диаграмма «сущность-связь»).

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

На следующем этапе – этапе логического проектирования – ER-диаграмма формальным способом преобразуется в схему реляционной базы данных (РБД). На основании схемы РБД и описания сущностей ПрО составляются отношения (таблицы) базы данных. Потом выполняется нормализация отношений. Это необходимо сделать для того, чтобы исключить нарушения логической целостности данных и повысить таким образом надёжность и достоверность данных. В отдельных случаях после нормализации может выполняться денормализация, но причина для этого может быть только одна: повышение эффективности выполнения критических запросов.

В результате всех этих операций создаётся концептуальная схема БД – основной документ для базы данных.

Далее, на этапе физического проектирования полученные отношения описываются на языке DDL (Data definition language) – языке определения данных, который поддерживается выбранной СУБД. Также необходимо определить способы хранения данных (кластеризация, хеширование) и способы доступа к данным (индексирование) и создать соответствующие индексы и кластеры (если нужно). Если пользователей АИС можно разделить на группы по характеру решаемых задач, то для каждой группы создаётся свой набор прав доступа к объектам БД.
^ 1.2. Последовательность проектирования базы данных
Итак, процесс проектирования включает в себя следующие шаги:

Определение задач, стоящих перед базой данных.

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

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

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

Определение групп пользователей и перечня задач, стоящих перед каждой группой.

Выбор аппаратной и программной платформы для реализации БД.

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

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

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

Нормализация отношений (до третьей или четвёртой нормальной формы).

Определение прав доступа пользователей к объектам БД.

Написание текста создания основных объектов базы данных на языке SQL в синтаксисе выбранной СУБД (пользователи, таблицы и др.).

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

Эти шаги можно объединить с 5 этапов:

Инфологическое проектирование (1-5).

Определение требований к операционной обстановке, в которой будет функционировать информационная система (6).

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

Логическое проектирование БД (8-11).

Физическое проектирование БД (12-13).

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

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

Основными подходами к созданию инфологической модели предметной области являются [1]:

Функциональный подход к проектированию БД ("от задач").

Предметный подход к проектированию БД ("от предметной области").

Метод "сущность-связь" (entity–relation, ER–method).

Мы будем использовать метод "сущность–связь" как наиболее распространённый. Приведём основные термины, которыми мы будем пользоваться:

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

Атрибут – свойство сущности. Различают:

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

Составные и простые атрибуты. Простой атрибут имеет неделимое значение. Составной атрибут является комбинацией нескольких элементов, возможно, принадлежащих разным типам данных (ФИО, адрес и др.).

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

^ Основные и производные атрибуты. Значение основного атрибута не зависит от других атрибутов; значение производного атрибута вычисляется на основе значений других атрибутов. Например, возраст вычисляется на основе даты рождения и текущей даты.

^ Обязательные и необязательные (первые должны быть указаны при размещении данных в БД, вторые могут не указываться).

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

Связь – это осмысленная ассоциация между сущностями. Для связи указывается название, тип (факультативная или обязательная), степень (1:1, 1:n или m:n) и кардинальность (унарная, бинарная, тернарная или n-арная).

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



Рис.1. Обозначения, используемые в ER-диаграммах
^ 1.2.2. Определение требований к операционной обстановке
На этом этапе производится оценка требований к вычислительным ресурсам, необходимым для функционирования системы, определение типа и конфигурации конкретной ЭВМ, выбор типа и версии операционной системы. Объём вычислительных ресурсов зависит от предполагаемого объёма проектируемой базы данных и от интенсивности их использования. Если БД будет работать в многопользовательском режиме, то требуется подключение её к сети и наличие соответствующей многозадачной операционной системы [1].
^ 1.2.3. Выбор СУБД и других программных средств
Выбор СУБД осуществляется на основании таких критериев, как тип модели данных и её адекватность потребностям рассматриваемой ПрО; характеристики производительности; набор функциональных возможностей; удобство и надежность СУБД в эксплуатации; стоимость СУБД и дополнительного программного обеспечения [1].
^ 1.2.4. Логическое проектирование реляционной БД
На этапе логического проектирования разрабатывается логическая (концептуальная) структура БД. Для реляционной модели существуют формальные правила, которые позволяют преобразовать инфологическую модель ПрО в виде ER-диаграммы в логическую схему базы данных. Кроме получения схемы БД в целом на этом этапе выполняют создание схем отношений и их нормализацию. Этот этап более подробно рассмотрен в п.2 "Пример проектирования реляционной базы данных".
^ 1.2.5. Физическое проектирование БД
Этап физического проектирования заключается в определении схемы хранения, т.е. физической структуры БД. Схема хранения зависит от той физической структуры, которую поддерживает выбранная СУБД. Физическая структура БД, с одной стороны, должна адекватно отражать логическую структуру БД, а с другой стороны, должна обеспечивать эффективное размещение данных и быстрый доступ к ним. Результаты этого этапа документируются в форме схемы хранения на языке определения данных (DDL, Data Definition Language) выбранной СУБД. Принятые на этом этапе решения оказывают огромное влияние на производительность системы.

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

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

Для решения подобных проблем проводится нормализация отношений.

Механизм нормализации реляционных отношений разработал Э.Ф. Кодд (E.F. Codd). Этот механизм позволяет по формальным признакам любое отношение преобразовать к третьей нормальной форме.

Нормализация схемы отношения выполняется путём декомпозиции схемы. Декомпозицией схемы отношения R называется замена её совокупностью схем отношений Аi таких, что

,

и не требуется, чтобы отношения Аi были непересекающимися.

Первая нормальная форма относится к понятию простого и сложного (составного или многозначного) атрибута (см. п.1.2.1).

Первая нормальная форма (1НФ).

Отношение приведено к 1НФ, если все его атрибуты простые.

Для того чтобы привести к 1НФ отношение, содержащее сложные атрибуты, нужно:

разбить составные атрибуты на простые,

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

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

Вторая нормальная форма основана на понятии функциональной зависимости. Пусть X и Y – атрибуты некоторого отношения. Если в любой момент времени каждому значению X соответствует единственное значение Y, то говорят, что Y функционально зависит от X (XY). Атрибут X в функциональной зависимости XY называется детерминантом отношения.

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

^ Вторая нормальная форма (2НФ).

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

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

Для того чтобы привести отношение ко 2НФ, нужно:

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

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

Третья нормальная форма основана на понятии транзитивной зависимости. Пусть X, Y, Z – атрибуты некоторого отношения. При этом XY и YZ, но обратное соответствие отсутствует, т.е. Z не зависит от Y или Y не зависит от X. Тогда говорят, что Z транзитивно зависит от X (XZ).


^ Третья нормальная форма (3НФ).

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

Для того чтобы привести отношение к 3НФ, нужно:

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

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

Исключение составляют случаи, когда для транзитивной зависимости XZ (XY и YZ) либо Z зависит от Y, либо Y зависит от X, т.е. между атрибутами X и Y, например, существует связь 1:1. В такой ситуации декомпозиция отношения не производится.

Четвертая нормальная форма основана на понятии многозначной зависимости. Многозначная зависимость существует, если заданным значениям атрибута X соответствует множество, состоящее из нуля (или более) значений атрибута Y (X–»Y).

Различают тривиальные и нетривиальные многозначные зависимости. Тривиальной называется такая многозначная зависимость X–»Y, для которой Y  X или X U Y = R, где R – рассматриваемое отношение. Тривиальная многозначная зависимость не нарушает 4НФ. Если хотя бы одно из двух этих условий не выполняется, то такая зависимость называется нетривиальной.

^ Четвертая нормальная форма (4НФ).

Отношение находится в 4НФ, если оно находится в 3НФ и в нём отсутствуют нетривиальные многозначные зависимости.

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

Описание этого метода достаточно подробно изложено в [1], поэтому, чтобы не повторяться, будем рассматривать этот метод на примере проектирования конкретной базы данных.
^ 2. ПРИМЕР ПРОЕКТИРОВАНИЯ РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ
В качестве примера возьмем базу данных проектной организации. Основной вид деятельности такой организации – выполнение проектов по договорам с заказчиками.
^ 2.1. Инфологическое проектирование 2.1.1. Анализ предметной области
База данных создаётся для информационного обслуживания руководства организации, руководителей проектов и участников проектов. БД должна содержать данные об отделах организации, сотрудниках и проектах.

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

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

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

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

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

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

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

Виды участия сотрудников в проектах: руководитель, консультант, исполнитель.

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

Примечание. Описания особенностей ПрО должно быть достаточно для того, чтобы создать ER–диаграмму.

Для создания ER-модели необходимо выделить сущности предметной области:

^ Отделы. Атрибуты: название, аббревиатура, комнаты, телефоны.

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

Примечания: 1. Логин потребуется нам для назначения дифференцированных прав доступа.

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

Проекты. Атрибуты: номер договора; полное название проекта; сокращённое название проекта; дата подписания договора; заказчик; контактные данные заказчика; дата начала проекта; дата завершения проекта; сумма по проекту; дата реальной сдачи проекта; сумма, полученная по проекту на текущую дату.

^ Этапы проекта. Атрибуты: номер по порядку, название, дата начала этапа, дата завершения этапа, форма отчетности, сумма по этапу, дата реальной сдачи этапа; сумма, полученная по этапу на текущую дату.

Исходя из выявленных сущностей, построим ER–диаграмму (рис. 2). Напомним, что пометки у линий означают степень связи: 1:1, 1:N и N:M.



Рис. 2. ER–диаграмма ПрО «Проектная организация»
^ 2.1.2. Анализ информационных задач и круга пользователей системы
Определим группы пользователей, их основные задачи и запросы к БД:

Руководители организации:

заключение новых договоров;

назначение руководителей проектов;

получение списка всех участников проектов;

изменение должностных окладов и штатного расписания;

получение полной информации о проектах;

внесение изменений в данные о проектах;

архивирование данных по завершённым проектам.

Примечание. Архивирование данных в этом пособии подробно не рассматривается. Это сделано для того, чтобы не перегружать схему БД.

Руководитель проекта:

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

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

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

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

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

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

Сотрудники отдела кадров:

приём/увольнение сотрудников;

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

Бухгалтеры:

получение ведомости на выплату зарплаты.

Сотрудники – участники проектов:

просмотр данных о других участниках проекта;

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

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

На основе результатов анализа ПрО можно приблизительно оценить объём памяти, требуемой для хранения данных. Примем ориентировочно, что:

одновременно осуществляется около десяти проектов, работа над проектом продолжается в среднем год (по 1К на каждый проект);

каждый проект состоит в среднем из четырёх этапов (по 0,5К на этап);

в компании работают 100 сотрудников (по 0,5К на каждого сотрудника);

в выполнении каждого проекта в среднем участвуют 10 сотрудников (по 0,2К);

устаревшие данные переводятся в архив (накапливаются в архиве БД).

Тогда объём памяти для хранения данных за первый год примерно составит:

Mд = 2(10*1+10*4*0,5+100*0,5+(10*10*0,2)) = 200 К,

Коэффициент 2 необходим для того, чтобы учесть необходимость выделения памяти под дополнительные структуры (например, индексы). Объём памяти будет увеличиваться ежегодно на столько же при сохранении объёма работы.

Требуемый объём оперативной памяти определяется на основании анализа интенсивности запросов и объёма результирующих данных. Для нашей БД требуемый объём памяти мал, поэтому никаких специальных требований к объёму внешней и оперативной памяти компьютера не предъявляется.
^ 2.3. Выбор СУБД и других программных средств
Анализ информационных задач показывает, что для реализации требуемых функций подходят почти все СУБД для ПЭВМ (MS Access, Firebird, MySQL и др.). Все они поддерживают реляционную модель данных и предоставляют разнообразные возможности для работы с данными.

Объём внешней и оперативной памяти, требующийся для функционирования СУБД, обычно указывается в сопроводительной документации.

Для того чтобы в учебном примере не привязываться к конкретной СУБД, выполним описание логической схемы БД на SQL-92.

Примечание. При выполнении курсового проекта необходимо обосновать выбор конкретной СУБД для реализации проекта и реализовать базу данных под управлением выбранной СУБД.
^ 2.4. Логическое проектирование реляционной БД 2.4.1. Преобразование ER–диаграммы в схему базы данных
База данных создаётся на основании схемы базы данных. Для преобразования ER–диаграммы в схему БД приведём уточнённую ER–диаграмму, содержащую атрибуты сущностей (рис. 3).



Рис. 3. Уточнённая ER–диаграмма проектной организации

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

Преобразование ER–диаграммы в схему БД выполняется путем сопоставления каждой сущности и каждой связи, имеющей атрибуты, отношения (таблицы) БД. Связь типа 1:n (один-ко-многим) между отношениями реализуется через внешний ключ. Ключ вводится для того отношения, к которому осуществляется множественная связь. Внешнему ключу должен соответствовать первичный или уникальный ключ основного (родительского) отношения.

Связь участвовать между ПРОЕКТАМИ и СОТРУДНИКАМИ принадлежит к типу n:m (многие-ко-многим). Этот тип связи реализуется через вспомогательное отношение Участие, которое содержит комбинации первичных ключей соответствующих исходных отношений.

Более подробно о принципах преобразования ER-диаграммы в схему БД рассказано в [1].

Для схемы БД будем использовать обозначения, представленные на рис. 4.



Рис. 4. Обозначения, используемые на схеме базы данных

Полученная схема реляционной базы данных (РБД) приведена на рис. 5.



Рис. 5. Схема РБД, полученная из ER–диаграммы проектной организации

Бинарная связь между отношениями не может быть обязательной для обоих отношений. Такой тип связи означает, что, например, прежде чем добавить новый проект в отношение ПРОЕКТЫ, нужно добавить новую строку в отношение ЭТАПЫ, и наоборот. Поэтому для такой связи необходимо снять с одной стороны условие обязательности. Так как все эти связи будут реализованы с помощью внешнего ключа, снимем условие обязательности связей для отношений, содержащих первичные ключи.

Схема на рис. 5 содержит три цикла: "сотрудники–проекты–участие–сотрудники", "отделы–сотрудники–проекты–отделы" и "отделы–сотрудники–участие–проекты–отделы". Цикл допустим только в том случае, если связи, входящие в него, независимы друг от друга. Например, для нашей ПрО справедливо такое правило: сотрудник любого отдела может быть участником (исполнителем или консультантом) проекта любого отдела. Эти связи независимы, поэтому цикл "отделы–сотрудники–участие–проекты–отделы" не будет приводить к нарушению логической целостности данных.

С другой стороны, только сотрудник отдела, отвечающего за выполнение проекта, может быть руководителем проекта. Но система не помешает нам назначить руководителем проекта сотрудника любого отдела. При добавлении проекта с внешним ключом Руководитель система проверит только, что такой человек есть в таблице СОТРУДНИКИ. А значение внешних ключей Отдел в таблицах СОТРУДНИКИ и ПРОЕКТЫ сравнивать не будет.

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

Рассмотрим эту ситуацию в общем случае. Сначала слегка упростим схему: реализуем связь "руководить" через таблицу УЧАСТИЕ – это позволит не отвлекаться на малозначительные детали.

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

Один из способов разрешения таких ситуаций – разорвать цикл, исключив одну из связей (рис. 6,б) или введя промежуточное отношение (рис. 6,в). В нашем случае можно было бы разорвать связь "сотрудники–проекты", если бы каждый сотрудник участвовал во всех проектах своего отдела. Промежуточное отношение можно было бы использовать, если бы существовала общая связь между сущностями, входящими в цикл. Например, если бы каждый сотрудник заключал договор с отделом на выполнение работ в рамках проекта, то отношение ДОГОВОРЫ отражало бы связь между отделом, сотрудником и проектом.

Другой способ разрешения цикла заключается в том, что в промежуточное отношение СОТРУДНИКИ – ПРОЕКТЫ, которое реализует связь многие-ко-многим, добавляются (мигрируют) внешние ключи Код отдела (D_id) из отношений СОТРУДНИКИ и ПРОЕКТЫ (рис. 6,г). Эти ключи проверяются на равенство друг другу с помощью соответствующего ограничения целостности (check). Использование этого способа возможно в том случае, когда соответствующие связи (отдел–проект и отдел–сотрудник) имеют тип один-ко-многим и являются обязательными.

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



Рис.6. Некоторые способы разрешения циклов в схеме базы данных

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

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

Отношения приведены в табл. 1-5. Для каждого отношения указаны атрибуты с их внутренним названием, типом и длиной. Типы данных обозначаются так: N – числовой, C – символьный тип фиксированной длины, V – символьный тип переменной длины, D – дата (этот тип имеет стандартную длину, зависящую от СУБД, поэтому она не указывается). О правилах выбора типов данных подробно рассказано в [1].

Потенциальными ключами отношения ОТДЕЛЫ являются атрибуты
Аббревиатура и Название отдела. Первый занимает меньше места, поэтому мы выбираем его в качестве первичного ключа.

Таблица 1. Схема отношения ОТДЕЛЫ (Departs)

^ Содержание поля

Имя поля

Тип, длина

Примечания

Аббревиатура отдела

D_ID

С(10)

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

Название отдела

D_NAME

V(100)

обязательное поле

Комнаты

D_ROOMS

V(20)

обязательное многозначное поле

Телефоны

D_PHONE

V(40)

обязательное многозначное поле

Потенциальными ключами отношения СОТРУДНИКИ являются поля Паспортные данные, ИНН и Номер страхового пенсионного свидетельства. Все они занимают достаточно много места, а паспортные данные кроме того могут меняться. Введём суррогатный первичный ключ Номер сотрудника.

Таблица 2. Схема отношения СОТРУДНИКИ (Employees)

^ Содержание поля

Имя поля

Тип, длина

Примечания

Номер

E_ID

N(4)

суррогатный первичный ключ

Фамилия, имя, отчество

E_NAME

V(50)

обязательное поле

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

E_BORN

D

обязательное поле

Пол

E_SEX

C(1)

обязательное поле, 'м' или 'ж'

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

E_PASP

V(50)

обязательное поле

ИНН

E_INN

С(12)

обязательное уникальное поле

Номер пенсионного страхового свидетельства

E_PENS

С(14)

обязательное уникальное поле

Отдел

E_DEPART

C(10)

внешний ключ (к Departs)

Должность

E_POST

V(30)

обязательное поле

Оклад

E_SAL

N(8,2)

обязательное поле, > 4500 руб.

Данные об образовании

E_EDU

V(200)

обязательное многозначное поле

Адреса

E_ADDR

V(100)

многозначное поле

Телефоны

E_PHONE

V(30)

многозначное поле

Логин

E_LOGIN

V(30)




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

В отношении ПРОЕКТЫ три потенциальных ключа: Номер проекта, Название проекта и Сокращённое названиие. Меньше места занимает первый из них, но он малоинформативен. Зато сокращённое название, используемое в качестве внешнего ключа в других таблицах, позволит специалисту идентифицировать проект без необходимости соединения с отношением ПРОЕКТЫ.

Таблица 3. Схема отношения ПРОЕКТЫ (Projects)

^ Содержание поля

Имя поля

Тип, длина

Приме