Этапы проектирования базы данных. Связь «один-к одному»

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

Руководство по проектированию баз данных.

1. Вступление.

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

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

База данных создается для хранения в ней информации и получения этой информации при необходимости. Это значит, что мы должны иметь возможность помещать, вставлять (INSERT ) информацию в базу данных и мы хотим иметь возможность делать выборку информации из базы данных (SELECT ).
Язык запросов к базам данных был придуман для этих целей и был назван Структурированный язык запросов или SQL. Операции вставки данных (INSERT) и их выборки (SELECT) – части этого самого языка. Ниже приведен пример запроса на выборку данных и его результат.

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

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

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

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

Примеры.

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

РСУБД.

РСУБД, которую я использовал для создания таблиц примеров – MySQL. MySQL – наиболее популярная РСУБД и она бесплатна.

Утилита для администрирования БД.

После установки MySQL вы получаете только интерфейс командной строки для взаимодействия с MySQL. Лично я предпочитаю графический интерфейс для управления моими базами данных. Я часто использую SQLyog. Это бесплатная утилита с графическим интерфейсом. Изображения таблиц в данном руководстве взяты оттуда.

Визуальное моделирование.

Существует отличное бесплатное приложение MySQL Workbench. Оно позволяет спроектировать вашу базу данных графически. Изображения диаграмм в руководстве сделаны в этой программе.

Проектирование независимо от РСУБД.

Важно знать, что хотя в данном руководстве и приведены примеры для MySQL, проектирование баз данных независимо от РСУБД. Это значит, что информация применима к реляционным базам данных в общем, не только к MySQL. Вы можете применить знания из этого руководства к любым реляционным базам данных, подобным Mysql, Postgresql, Microsoft Access, Microsoft Sql or Oracle.

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

2. История.

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

Так выглядели профессионалы в сфере информационных технологий в 70-е. (Слева внизу находится Билл Гейтс).

Текстовые файлы и сегодня все еще используются для хранения малых объемов простой информации. Comma-Separated Values (CSV) - значения, разделённые запятыми, очень популярны и широко поддерживаются сегодня различным программным обеспечением и операционными системами. Microsoft Excel – один из примеров программ, которые могут работать с CSV–файлами. Данные, сохраненные в таком файле могут быть считаны компьютерной программой.

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

Таблицы баз данных.

Чтение файла строчка за строчкой не является очень эффективным. В реляционной базе данных данные хранятся в таблицах. Таблица ниже содержит те же самые данные, что и файл. Каждая строка или “запись” содержит один урок. Каждый столбец содержит какое-то свойство урока. В данном случае это заголовок (title) и его категория (category).

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

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

История реляционной модели.

Реляционная модель баз данных была изобретена в 70-х Эдгаром Коддом (Ted Codd), британским ученым. Он хотел преодолеть недостатки сетевой модели баз данных и иерархической модели. И он очень в этом преуспел. Реляционная модель баз данных сегодня всеобще принята и считается мощной моделью для эффективной организации данных.

Сегодня доступен широкий выбор систем управления базами данных: от небольших десктопных приложений до многофункциональных серверных систем с высокооптимизированными методами поиска. Вот некоторые из наиболее известных систем управления реляционными базами данных (РСУБД):

- Oracle – используется преимущественно для профессиональных, больших приложений.
- Microsoft SQL server – РСУБД компании Microsoft. Доступна только для операционной системы Windows.
- Mysql – очень популярная РСУБД с открытым исходным кодом. Широко используется как профессионалами, так и новичками. Что еще нужно?! Она бесплатна.
- IBM – имеет ряд РСУБД, наиболее известна DB2.
- Microsoft Access – РСУБД, которая используется в офисе и дома. На самом деле – это больше, чем просто база данных. MS Access позволяет создавать базы данных с пользовательским интерфейсом.
В следующей части я расскажу кое-что о характеристиках реляционных баз данных.

3. Характеристики реляционных баз данных.

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

Использование ключей.

Каждая строка данных в таблице идентифицируется уникальным “ключом”, который называется первичным ключом. Зачастую, первичный ключ это автоматически увеличиваемое (автоинкрементное) число (1,2,3,4 и т.д). Данные в различных таблицах могут быть связаны вместе при использовании ключей. Значения первичного ключа одной таблицы могут быть добавлены в строки (записи) другой таблицы, тем самым, связывая эти записи вместе.

Используя структурированный язык запросов (SQL), данные из разных таблиц, которые связаны ключом, могут быть выбраны за один раз. Для примера вы можете создать запрос, который выберет все заказы из таблицы заказов (orders), которые принадлежат пользователю с идентификатором (id) 3 (Mike) из таблицы пользователей (users). О ключах мы поговорим далее, в следующих частях.

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

Отсутствие избыточности данных.

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

Ограничение ввода.

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

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

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

Эти ограничения дают вам контроль над целостностью ваших данных и предотвращают ситуации, подобные следующим:

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

Поддержание целостности данных.

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

Назначение прав.

Большинство РСУБД предлагают настройку прав доступа, которая позволяет назначать определенные права определенным пользователям. Некоторые действия, которые могут быть позволены или запрещены пользователю: SELECT (выборка), INSERT (вставка), DELETE (удаление), ALTER (изменение), CREATE (создание) и т.д. Это операции, которые могут быть выполнены с помощью структурированного языка запросов (SQL).

Структурированный язык запросов (SQL).

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

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

Переносимость.

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

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

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

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

Анализ предметной области
Инфологическое (концептуальное) описание данных;
Логическое проектирование баз данных;
Физическое проектирование баз данных.

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

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

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

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

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

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

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

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

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

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

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

Существует три основных типа отношений:

1) «один-к-одному». Такая связь означает, что каждому значению реквизита А соответствует одно и только одно значение связанного с ним реквизита В, и наоборот. Например, каждому значению реквизита Номер паспорта соответствует единственное значение реквизита ФИО гражданина страны, и наоборот. Такую связь обозначают 1:1, графически в инфологических моделях эта связь изображается одинарными стрелками.

2)«один-ко-многим». Эта связь означает, что каждому значению реквизита А соответствует одно или несколько значений связанного с ним реквизита В, а каждому значению реквизита В соответствует одно и только одно значение реквизита А. Например, для аэропорта , из которого осуществляется множество рейсов, характерна следующая связь между описывающими этот объект реквизитами: одному значению реквизита Название аэропорта вылета соответствует несколько значений реквизита Номер рейса, а каждому значению Номер рейса соответствует только одно Название аэропорта вылета.

3)«многие-ко-многим». Такая связь означает, что каждому значению реквизита А соответствует несколько значений связанного с ним реквизита В, и наоборот. Например, турагентство может работать с несколькими туроператорами, а туроператор обычно имеет разветвленную сеть турагентов. Такую связь обозначают М: М, а графически изображают двойными стрелками.

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

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

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

На этом этапе осуществляется выбор подходящей системы управления базами данных и представление инфологической модели предметной области в форме структуры базы данных конкретной СУБД.

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

Файловая модель. Представляет собой совокупность не связанных между собой файлов из однотипных записей с линейной (одноуровневой) структурой.

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

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

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

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

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

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

Файлы прямого доступа;

Файлы последовательного доступа;

Индексные файлы.

Похожая информация.

Руководство по проектированию баз данных.

1. Вступление.

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

Примеры.

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

РСУБД.

Утилита для администрирования БД.

Визуальное моделирование.

Проектирование независимо от РСУБД.

2. История.

Так выглядели профессионалы в сфере информационных технологий в 70-е. (Слева внизу находится Билл Гейтс).

Таблицы баз данных.

История реляционной модели.

3. Характеристики реляционных баз данных.

Использование ключей.

Отсутствие избыточности данных.

Ограничение ввода.

Поддержание целостности данных.

Назначение прав.

Структурированный язык запросов (SQL).

Переносимость.

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

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

I этап. Постановка задачи.

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

II этап. Анализ объекта.

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

III этап. Синтез модели.

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

IV этап. Выбор способов представления информации и программного инструментария.

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

В большинстве СУБД данные можно хранить в двух видах:

с использованием форм;
без использования форм.

Форма – это созданный пользователем графический интерфейс для ввода данных в базу.

V этап. Синтез компьютерной модели объекта.

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

Стадия 1. Запуск СУБД, создание нового файла базы данных или открытие созданной ранее базы.

Стадия 2. Создание исходной таблицы или таблиц.

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

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

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

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

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

Стадия 3. Создание экранных форм.

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

Стадия 4. Заполнение БД.

Процесс заполнения БД может проводиться в двух видах: в виде таблицы и в виде формы. Числовые и текстовые поля можно заполнять в виде таблицы, а поля типа МЕМО и OLE – в виде формы.

VI этап. Работа с созданной базой данных.

Работа с БД включает в себя следующие действия:

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

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

I. Системный анализ и словесное описание информационных объектов ПО . Существуют два подхода к выбору состава и структуры предметной области:

· Функциональный подход. Он реализует принцип движения «от задач» и применяется тогда, когда известны функции некоторой группы лиц и комплексов задач, для облуживания информационных потребностей которых создается БД. В этом случае можно четко выделить необходимый минимальный набор объектов, которые должны быть описаны.

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

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

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

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

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

Каким образом отобразить объекты предметной области в абстрактные объекты модели данных? Эту проблему называют проблемой логического проектирования баз данных.

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

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

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

из каких отношений должна состоять БД и

какие атрибуты должны быть у этих отношений

Можно выделить три основных подхода к проектированию БД:

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

2. переход от семантической (инфологической) модели второго этапа с помощью CASE-средств к готовой схеме БД или даже готовой прикладной информационной системе (ИС);

3. структурирование информации для использования в ИС в процессе проведения системного анализа на основе совокупности практических правил и рекомендаций.

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

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

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

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

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

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

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

пятая нормальная форма (5NF или PJ/NF).

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

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

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

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

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

Определение 1. Функциональная зависимость

В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y: R.X ->R.Y.

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

Функциональная зависимость R.X ->R.Y называется полной, если атрибут Y не зависит функционально от любого точного подмножества X.

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

Функциональная зависимость R.X -> R.Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости R.X -> R.Z и R.Z -> R.Y и отсутствует функциональная зависимость R.Z -/-> R.X.

Определение 4. Неключевой атрибут

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

Определение 5 . Взаимно независимые атрибуты

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

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

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

первичный ключ каждого расширенной таблицы состоит из первичного ключа подчиненной таблицы и добавленного родительского первичного ключа;

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

условие окончания алгоритма - атомарность всех атрибутов.

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

Сотрудники организации выполняют проекты.

Проекты состоят из нескольких заданий.

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

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

Над каждым заданием в проекте работает ровно один сотрудник.

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

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

В ходе дополнительного уточнения того, какие данные необходимо учитывать, выяснилось следующее:

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

Каждый отдел имеет уникальный номер.

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

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

Представим схему отношения (вся информация в одной таблице):

СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ

(СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН)

Первичный ключ:

СОТР_НОМЕР, ПРО_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР -> СОТР_ЗАРП

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

ОТД_НОМЕР -> СОТР_ЗАРП

СОТР_НОМЕР, ПРО_НОМЕР -> СОТР_ЗАДАН

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

Определение 6 . Вторая нормальная форма (в этом определении предполагается, что единственным ключом отношения является первичный ключ)

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

Можно произвести следующую декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ в два отношения СОТРУДНИКИ-ОТДЕЛЫ и СОТРУДНИКИ-ПРОЕКТЫ:

СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР)

Первичный ключ:

СОТР_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР -> СОТР_ЗАРП

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

ОТД_НОМЕР -> СОТР_ЗАРП

Первичный ключ:

СОТР_НОМЕР, ПРО_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН

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

Рассмотрим еще раз отношение СОТРУДНИКИ-ОТДЕЛЫ, находящееся в 2NF. Заметим, что функциональная зависимость СОТР_НОМЕР -> СОТР_ЗАРП является транзитивной; она является следствием функциональных зависимостей СОТР_НОМЕР -> ОТД_НОМЕР и ОТД_НОМЕР -> СОТР_ЗАРП. Другими словами, заработная плата сотрудника на самом деле является характеристикой не сотрудника, а отдела, в котором он работает (это не очень естественное предположение, но достаточное для примера).

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

Определение 7. Третья нормальная форма (определение дается в предположении существования единственного ключа.)

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

Можно произвести декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ в два отношения СОТРУДНИКИ и ОТДЕЛЫ:

СОТРУДНИКИ (СОТР_НОМЕР, ОТД_НОМЕР)

Первичный ключ:

СОТР_НОМЕР

Функциональные зависимости:

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

ОТДЕЛЫ (ОТД_НОМЕР, СОТР_ЗАРП)

Первичный ключ:

ОТД_НОМЕР

Функциональные зависимости:

ОТД_НОМЕР -> СОТР_ЗАРП

Каждое из этих двух отношений находится в 3NF и свободно от отмеченных аномалий.

Если отказаться от того ограничения, что отношение обладает единственным ключом, то определение 3NF примет следующую форму:

Определение 7*

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

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

Однако иногда полезно продолжить процесс нормализации.

Пример 5.2 Рассмотрим следующий пример схемы отношения:

СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, СОТР_ИМЯ, ПРО_НОМЕР, СОТР_ЗАДАН)

Возможные ключи:

СОТР_НОМЕР, ПРО_НОМЕР

СОТР_ИМЯ, ПРО_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР -> CОТР_ИМЯ

СОТР_НОМЕР -> ПРО_НОМЕР

СОТР_ИМЯ -> CОТР_НОМЕР

СОТР_ИМЯ -> ПРО_НОМЕР

СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН

СОТР_ИМЯ, ПРО_НОМЕР -> CОТР_ЗАДАН

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

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

Определение 8. Детерминант

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

Определение 9 . Нормальная форма Бойса-Кодда

Отношение R находится в нормальной форме Бойса-Кодда (BCNF) в том и только в том случае, если каждый детерминант является возможным ключом.

Очевидно, что это требование не выполнено для отношения СОТРУДНИКИ-ПРОЕКТЫ. Можно произвести его декомпозицию к отношениям СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ:

СОТРУДНИКИ (СОТР_НОМЕР, СОТР_ИМЯ)

Возможные ключи:

СОТР_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР -> CОТР_ИМЯ

СОТР_ИМЯ -> СОТР_НОМЕР

СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН)

Возможный ключ:

СОТР_НОМЕР, ПРО_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН

Возможна альтернативная декомпозиция, если выбрать за основу СОТР_ИМЯ. В обоих случаях получаемые отношения СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ находятся в BCNF, и им не свойственны отмеченные аномалии.

Пример 5.3 Рассмотрим пример следующей схемы отношения:

ПРОЕКТЫ (ПРО_НОМЕР, ПРО_СОТР, ПРО_ЗАДАН)

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

Каждый кортеж отношения связывает некоторый проект с сотрудником, участвующим в этом проекте, и заданием, который сотрудник выполняет в рамках данного проекта (мы предполагаем, что любой сотрудник, участвующий в проекте, выполняет все задания, предусмотренные этим проектом). По причине сформулированных выше условий единственным возможным ключом отношения является составной атрибут ПРО_НОМЕР, ПРО_СОТР, ПРО_ЗАДАН, и нет никаких других детерминантов. Следовательно, отношение ПРОЕКТЫ находится в BCNF. Но при этом оно обладает недостатками: если, например, некоторый сотрудник присоединяется к данному проекту, необходимо вставить в отношение ПРОЕКТЫ столько кортежей, сколько заданий в нем предусмотрено.

Определение 10 . Многозначные зависимости

В отношении R (A, B, C) существует многозначная зависимость R.A -> -> R.B в том и только в том случае, если множество значений B, соответствующее паре значений A и C, зависит только от A и не зависит от С.

В отношении ПРОЕКТЫ существуют следующие две многозначные зависимости:

ПРО_НОМЕР -> -> ПРО_СОТР

ПРО_НОМЕР -> -> ПРО_ЗАДАН

Легко показать, что в общем случае в отношении R (A, B, C) существует многозначная зависимость R.A -> -> R.B в том и только в том случае, когда существует многозначная зависимость R.A -> -> R.C.

Дальнейшая нормализация отношений, подобных отношению ПРОЕКТЫ, основывается на следующей теореме:

Теорема Фейджина

Отношение R (A, B, C) можно спроецировать без потерь в отношения R1 (A, B) и R2 (A, C) в том и только в том случае, когда существует MVD A -> -> B | C.

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

Определение 11 . Четвертая нормальная форма

Отношение R находится в четвертой нормальной форме (4NF) в том и только в том случае, если в случае существования многозначной зависимости A -> -> B все остальные атрибуты R функционально зависят от A.

В нашем примере можно произвести декомпозицию отношения ПРОЕКТЫ в два отношения ПРОЕКТЫ-СОТРУДНИКИ и ПРОЕКТЫ-ЗАДАНИЯ:

ПРОЕКТЫ-СОТРУДНИКИ (ПРО_НОМЕР, ПРО_СОТР)

ПРОЕКТЫ-ЗАДАНИЯ (ПРО_НОМЕР, ПРО_ЗАДАН)

Оба эти отношения находятся в 4NF и свободны от отмеченных аномалий.

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

Пример 5.4 Рассмотрим, например, отношение

СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМЕР, ОТД_НОМЕР, ПРО_НОМЕР)

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

Поэтому отношение находится в 4NF. Однако в нем могут существовать аномалии, которые можно устранить путем декомпозиции в три отношения.

Определение 12. Зависимость соединения

Отношение R (X, Y, ..., Z) удовлетворяет зависимости соединения * (X, Y, ..., Z) в том и только в том случае, когда R восстанавливается без потерь путем соединения своих проекций на X, Y, ..., Z.

Определение 13 . Пятая нормальная форма

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

Введем следующие имена составных атрибутов:

СО = {СОТР_НОМЕР, ОТД_НОМЕР}

СП = {СОТР_НОМЕР, ПРО_НОМЕР}

ОП = {ОТД_НОМЕР, ПРО_НОМЕР}

Предположим, что в отношении СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ существует зависимость соединения:

* (СО, СП, ОП)

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

СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, ОТД_НОМЕР)

СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР)

ОТДЕЛЫ-ПРОЕКТЫ (ОТД_НОМЕР, ПРО_НОМЕР)

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

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

Этапы проектирования базы данных. Связь «один-к одному»

Руководство по проектированию баз данных.

1. Вступление.

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

Примеры.

Проектирование независимо от РСУБД.

2. История.

Таблицы баз данных.

История реляционной модели.

3. Характеристики реляционных баз данных.

Использование ключей.

Отсутствие избыточности данных.

Ограничение ввода.

Поддержание целостности данных.

Назначение прав.

Структурированный язык запросов (SQL).

Переносимость.

Руководство по проектированию баз данных.

1. Вступление.

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

Примеры.

Проектирование независимо от РСУБД.

2. История.

Таблицы баз данных.

История реляционной модели.

3. Характеристики реляционных баз данных.

Использование ключей.

Отсутствие избыточности данных.

Ограничение ввода.

Поддержание целостности данных.

Назначение прав.

Структурированный язык запросов (SQL).

Переносимость.

Возможно вам будет интересно:

Категории

Последние статьи

Реклама