Проектирование баз данных

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

2. Этапы разработки базы данных

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

· Реляционная модель данных - удобный способ представления данных предметной области.

· Язык SQL - универсальный способ манипулирования такими данными.

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

· Сама предметная область.

· Модель предметной области.

· Инфологическая модель данных.

Рекомендуемые материалы

· Даталогическая модель данных.

· Собственно база данных и приложения.

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

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

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

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

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

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

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

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

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

3. Критерии оценки качества логической модели данных

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

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

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

· Легкость разработки и сопровождения базы данных

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

· Скорость выполнения операций выборки данных

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

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

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

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

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

4. Алгоритм нормализации (приведение к 3НФ)

Шаг 1. (Приведение к 1НФ). На первом шаге задается одно или несколько отношений, отображающих понятия предметной области.

Первая нормальная форма - это обычное отношение. Отношение в 1НФ обладает следующими свойствами:

· В отношении нет одинаковых кортежей.

· Кортежи не упорядочены.

· Атрибуты не упорядочены.

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

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

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

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

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

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

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

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

На практике, при создании логической модели данных, как правило, не следуют прямо приведенному алгоритму нормализации. Опытные разработчики обычно сразу строят отношения в 3НФ. Кроме того, основным средством разработки логических моделей данных являются различные варианты ER-диаграмм. Особенность этих диаграмм в том, что они сразу позволяют создавать отношения в 3НФ. Тем не менее, приведенный алгоритм важен по двум причинам. Во-первых, этот алгоритм показывает, какие проблемы возникают при разработке слабо нормализованных отношений. Во-вторых, как правило, модель предметной области никогда не бывает правильно разработана с первого шага. Эксперты предметной области могут забыть о чем-либо упомянуть, разработчик может неправильно понять эксперта, во время разработки могут измениться правила, принятые в предметной области, и т.д. Все это может привести к появлению новых зависимостей, которые отсутствовали в первоначальной модели предметной области. В этом случае необходимо использовать алгоритм нормализации хотя бы для того, чтобы убедиться, что отношения остались в 3НФ, и логическая модель не ухудшилась.

5. Элементы модели "сущность-связь"

Моделирование структуры базы данных при помощи алгоритма нормализации, описанного в предыдущих главах, имеет серьезные недостатки:

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

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

В реальном проектировании структуры базы данных применяются другой метод - так называемое, семантическое моделирование. Семантическое моделирование представляет собой моделирование структуры данных, опираясь на смысл этих данных. В качестве инструмента семантического моделирования используются различные варианты диаграмм сущность-связь (ER - Entity-Relationship).

Первый вариант модели сущность-связь был предложен в 1976 г. Питером Пин-Шэн Ченом. В дальнейшем многими авторами были разработаны свои варианты подобных моделей (нотация Мартина, нотация IDEF1X, нотация Баркера и др.). Кроме того, различные программные средства, реализующие одну и ту же нотацию, могут отличаться своими возможностями. По сути, все варианты диаграмм сущность-связь исходят из одной идеи - рисунок всегда нагляднее текстового описания. Все такие диаграммы используют графическое изображение сущностей предметной области, их свойств (атрибутов), и взаимосвязей между сущностями.

Основные понятия ER-диаграмм

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

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

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

Примерами сущностей могут быть такие классы объектов как "Поставщик", "Сотрудник", "Накладная".

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

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

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

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

Атрибут сущности - это именованная характеристика, являющаяся некоторым свойством сущности.

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

Примерами атрибутов сущности "Сотрудник" могут быть такие атрибуты как "Табельный номер", "Фамилия", "Имя", "Отчество", "Должность", "Зарплата" и т.п.

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

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

Сущность может иметь несколько различных ключей.

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

Связь - это некоторая ассоциация между двумя сущностями. Одна сущность может быть связана с другой сущностью или сама с собою.

Связи позволяют по одной сущности находить другие сущности, связанные с нею.

Например, связи между сущностями могут выражаться следующими фразами - "СОТРУДНИК может иметь несколько ДЕТЕЙ", "каждый СОТРУДНИК обязан числиться ровно в одном ОТДЕЛЕ".

Графически связь изображается линией, соединяющей две сущности:

Рисунок 6

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

Каждая связь может иметь один из следующих типов связи:

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

Связь типа один-ко-многим означает, что один экземпляр первой сущности (левой) связан с несколькими экземплярами второй сущности (правой). Это наиболее часто используемый тип связи. Левая сущность (со стороны "один") называется родительской, правая (со стороны "много") - дочерней. Характерный пример такой связи приведен на рисунке 6.

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

Каждая связь может иметь одну из двух модальностей связи:

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

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

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

<Каждый экземпляр СУЩНОСТИ 1> <МОДАЛЬНОСТЬ СВЯЗИ> <НАИМЕНОВАНИЕ СВЯЗИ> <ТИП СВЯЗИ> <экземпляр СУЩНОСТИ 2>.

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

Слева направо: "каждый сотрудник может иметь несколько детей".

Справа налево: "Каждый ребенок обязан принадлежать ровно одному сотруднику".

Пример разработки ER-модели

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

· сбор информации об объектах, выставляемых на продажу;

· представление данных в общую БД;

· организация просмотра объектов потенциальными покупателями;

· составление договоров на продажу недвижимости.

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

При разработке ER-моделей мы должны получить следующую информацию о предметной области:

· Список сущностей предметной области.

· Список атрибутов сущностей.

· Описание взаимосвязей между сущностями.

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

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

Выделим объекты предметной области:

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

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

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

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

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

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

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

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

· В каждом отделении компании работает, по крайней мере, 5 сотрудников, а максимальное их количество не ограничено.

· Каждый сотрудник может отвечать не более чем за 10 объектов недвижимости одновременно.

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

· Создание и корректировка записей с данными о сотрудниках каждого отделения.

· Обновление сведений о зарплате некоторого сотрудника.

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

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

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

· Поиск всех объектов недвижимости, удовлетворяющих требованиям покупателя.

· Поиск возможного покупателя для вносимого в базу данных объекта недвижимости.

· Создание и корректировка записей со сведениями об осмотре объектов недвижимости.

· Создание и корректировка записей со сведениями о заключённых договорах.

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

Описание сущностей и типов связей

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

Отделение (BRANCH)

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

Сотрудник (STAFF)

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

Объект недвижимости для продажи (PROPERTY_FOR_SALE)

Характеризуется такими атрибутами как: номер объекта недвижимости, дата регистрации, полный адрес (почтовый индекс, город, улица, дом и квартира), тип объекта (N-этажный панельный или кирпичный), этаж, количество комнат, площадь (общая, жилая, площадь кухни), балкон (балкон, лоджия, застеклён, их количество или отсутствие), наличие телефона (есть, нет) и отпускная цена.

Владелец (PRIVATE_OWNER)

Владелец имеет атрибуты: номер владельца, фамилия, имя, адрес и номер телефона.

Покупатель (BUYER)

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

Определим типы связей, которые существуют между основными сущностями.

Между сущностями Отделение и Сотрудник существует связь 1:М, обязательная только с одной стороны. Связь обязательна только с одной стороны, т.к. каждое из отделений компании имеет несколько штатных сотрудников, но не все сотрудники компании работают в отделениях. В обратном направлении, каждый из сотрудников отделений работает только в одном из них. Ключевой атрибут сущности Отделение: Номер отделения (Branch_no). Ключевой атрибут сущности Сотрудник: Номер сотрудника (Staff_no).

Из описания предметной области известно, что каждый объект недвижимости, выставленный на продажу, закрепляется за конкретным отделением компании. В каждом отделении компании есть сотрудник, отвечающий за работу с выставленными на продажу объектами недвижимости. Для отражения этой ситуации необходимо провести связь между сущностями Объект недвижимости для продажи и Отделение (ключевым атрибутом сущности Объект недвижимости для продажи является Номер объекта недвижимости (Property_no)). Для того чтобы узнать, какой объект недвижимости обслуживается каким сотрудником и, с другой стороны, какой сотрудник отвечает за данный объект, вводится дополнительная связь между сущностями Объект недвижимости для продажи и Сотрудник. Между сущностями Отделение и Объект недвижимости для продажи установлена связь 1:М, обязательная с 2-х сторон. Между сущностями Сотрудник и Объект недвижимости для продажи – связь 1:М, необязательная с 2-х сторон, так как из всех сотрудников компании только торговые агенты занимаются продажей недвижимости и отвечают за работу с ними. В обратном направлении, объект может быть не связан ни с одним из сотрудников. Например, когда объект впервые регистрируется в компании.

Теперь необходимо отразить связь между сущностями Владелец и Объект недвижимости для продажи. Если рассмотреть эту связь с одной стороны, то можно заметить, что один владелец может владеть несколькими объектами недвижимости. С другой стороны, каждый объект принадлежит только одному владельцу. Следовательно, связь между сущностями - 1:М. Поскольку каждый владелец владеет, по крайней мере, одним объектом недвижимости, а каждый объект должен иметь одного владельца, связь является обязательной с обеих сторон.

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

Если клиент согласен купить некоторый объект, то он заключает с компанией договор на покупку. Сотрудник компании должен оформить это соглашение. Каждый объект может быть продан единственному клиенту, и каждый клиент может купить один или более объектов в одно и то же время. Образуется необязательная с двух сторон связь 1:М между сущностями Покупатель и Объект недвижимости для продажи. Но так как, всякий раз, при покупке клиент заключает договор с компанией, мы определим две связи. Связь 1:М между сущностями Покупатель и Договор на покупку, а также связь 1:М между сущностями Объект недвижимости для продажи и Договор на покупку. Связи обязательны со стороны сущности Договор на покупку. Ключевым атрибутом для сущности Покупатель является Код покупателя (Buyer_no), а для сущности Договор на покупку – атрибут Номер договора (Sale_no). Кроме этого сущность Договор на покупку (CONTRACT_ON_SALE) имеет атрибуты: название нотариальной конторы, дата заключения договора, стоимость услуг. Инфологическая модель данных приведена на рисунке 7.

Переход к реляционной модели

Преобразование ER-модели в реляционную схему  осуществляется в соответствии  со следующими правилами:

1. каждая простая сущность превращается в отношение. Имена отношений могут отличаться от имен сущностей, так как могут быть ограничены требованиями конкретной СУБД;

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

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

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

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

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

Для связи М:М между сущностями Покупатель и Объект недвижимости для продажи введем дополнительное связующее отношение, которое связано с каждым исходным связью 1:М.. Атрибутами этого связующего отношения, помимо даты осмотра и комментарии, будут первичные ключи связываемых отношений, т.е. Property_no и Buyer_no. Для нового отношения они являются внешними ключами, а вместе они образуют первичный ключ новой связующей сущности Осмотр (VIEWING).

ОСМОТР (VIEWING)

Дата осмотра (Date_View)

Комментарии (Comments)

Property_no

Buyer_no

6. Концептуальные и физические ER-модели

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

7. КОНТРОЛЬНЫЕ ВОПРОСЫ

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

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

3. Какими свойствами должно обладать отношение, находящееся в 1 НФ?

4. Какие отношения требуют перевода в 2 НФ?

5. В чем заключается  алгоритм перевода отношения в 2 НФ?

6. В каких случаях требуется перевод отношений в 3 НФ?

7. Как перевести отношение 3 НФ?

Вам также может быть полезна лекция "22 - Топографические изыскания".

8. Перечислите основные понятия ER-модели.

9. Что понимают под ключом сущности?

10.  Какие типы связей возможны между сущностями?

11.  В чем смысл понятия модальности связи,  и каким образом задается модальность связи на ER-диаграмме?

12. Перечислите правила преобразования инфологической модели в реляционную. Каким образом выполняется  преобразование связей?