СУБД / УМК СУБД

Зачем нужна нормализация.

 Главное, чего мы добьемся, проведя нормализацию базы данных - это устранение (или,

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

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

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

Тема 6. Уровни моделирования предметной области

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

1.информационно-логическом (инфологическом, или концептуальном);

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

3.физическом.

Этим уровням соответствуют инфологическая, даталогическая и физическая модели предметной области.

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

Внешние модели (индивидуальные представления пользователей)

	Модель 1				Модель 2			…	Модель n		Логический
											уровень
											представления
				Концептуальная модель БД
											данных
			(обобщенное представление пользователей)
											Физический
											уровень
				Внутренняя модель БД							представления
				(представление в памяти ЭВМ)							данных
	Рисунок 19. Многоуровневое представление данных БД

55

Концептуальной, или инфологической (infological), моделью (ИЛМ) называется формализованное описание предметной области, выполненное без жесткой ориентации на программные и технические средства. Концептуальная модель отражает специфику предметной области, а не структуру БД.

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

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

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

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

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

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

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

Внешние схемы БД конструируются на стадии разработки приложений.

Взаимосвязь этапов проектирования БД показана на рис. 20.

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

56

	Предметная	Инфологическое моделирование
	область
	Предварительная	Даталогическое
	логическая модель	проектирование

Анализ

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

Анализ

Описание БД, (схемы, схемы

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

Рисунок 20. Взаимосвязь этапов проектирования

Тема 7. Концептуальное проектирование баз данных

Одним из средств формализованного представления предметной области является

модель «сущность-связь» (ERM – Entity-Relationship Model), предложенная Питером Ченом в 1976 г. Моделирование предметной области базируется на использовании ER-

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

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

Сущность (Entity) – реальный или абстрактный объект, имеющий существенное значение для предметной области. Сущность должна иметь наименование, выраженное существительным в единственном числе (рис. 18). Примерами могут быть такие классы объектов, как Поставщик, Сотрудник, Заказ.

Сотрудник

Рис. 18. Графическое представление сущности Неформальный способ идентификации сущностей – это поиск абстракций,

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

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

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

57

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

·иметь уникальное имя;

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

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

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

Атрибут (Attribute) – характеристика сущности, значимая для рассматриваемой предметной области и предназначенная для идентификации, классификации,

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

Сущность Сотрудник может иметь атрибуты: Табельный номер, Фамилия, Имя,

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

определяющего сущность (рис. 19). Существуют следующие виды атрибутов:

·простой – состоит из одного элемента данных;

·составной – состоит из нескольких элементов данных;

·однозначный – содержит одно значение для одной сущности;

·многозначный – содержит несколько значений для одной сущности;

·необязательный – может иметь пустое (неопределенное) значение;

·производный – значение, производное от значения другого атрибута.

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

	Сотрудник		Сотрудник
	Табельный номер		Табельный номер
	Фамилия		Фамилия
	Имя		Имя
	Отчество		Отчество
	Должность		Должность
	Зарплата		Зарплата
Рис. 19. Сущность с атрибутами		Рис. 20. Сущность с уникальным
		идентификатором

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

Связь (Relationship) – поименованная ассоциация между сущностями, значимая для рассматриваемой предметной области. Степенью связи называется количество сущностей,

участвующих в связи. Связь степени 2 называется бинарной, степени N – N-арной. Связь,

в которой одна сущность участвует в разных ролях, называется рекурсивной (унарной).

58

Пары чисел на диаграмме отражают две важные характеристики связи – класс принадлежности (первое число) и мощность связи (второе число).

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

Мощность связи – число экземпляров сущности, участвующих в связи. Мощность связи может быть равна 1, N или конкретному числу. Мощности связи на рис. 21

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

В зависимости от значения мощности связь может иметь один из трех типов:

один-ко-многим(обозначается 1:N или 1:∞) , показана на рис. 21.

многие-ко-многим(обозначается M:N), показана на рис. 22.

один-к-одному (обозначается 1:1), показана на рис. 23.

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

Связь осуществляется путем сопоставления данных в ключевых столбцах; обычно это столбцы, имеющие в обеих таблицах одинаковые названия.

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

Связи "один ко многим" (1:∞)

При такой связи каждой строке таблицы А может соответствовать множество строк таблицы Б, однако каждой строке таблицы Б может соответствовать только одна строка таблицы А.

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

В Microsoft Access сторона связи "один ко многим", которой соответствует первичный ключ, обозначается символом ключа.

Сторона связи, которой соответствует внешний ключ, обозначается символом бесконечности.

59

		0,N		1,1	Отдел
	Сотрудник		Работает_в
					Номер_отдела
	Табельный_номер
	ФИО

Рисунок 21. Связь 1:N (Один ко многим (1:∞)).

Связи "многие ко многим" (∞ : ∞)

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

Такая связь создается при помощи третьей таблицы, называемой соединительной,

первичный ключ которой состоит из внешних ключей, связанных с таблицами А и Б.

Например: Чтобы узнать ставку сотрудника, нужно знать не только его табельный номер, но и номер отдела (рис. 23). Ставка является атрибутом обеих сущностей

Сотрудник и Отдел. Это – атрибут связи.

			0,M	Работает_в	1,N
	Сотрудник					Отдел
	Табельный_номер			Cтавка		Номер_отдела
	ФИО
Рисунок 22. Связь типа M:N (Многие ко многим (∞ : ∞)).
Связи "один к одному"			(1:1)

При установлении связи "один к одному" каждой строке таблицы А может соответствовать только одна строка таблицы Б и наоборот.

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

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

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

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

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

 В Microsoft Access сторона связи "один к одному", которой соответствует первичный

ключ, обозначается символом ключа.

60

 Сторона связи, которой соответствует внешний ключ, также обозначается символом

ключа.

		0,1	Руководит	1,1
	Сотрудник				Отдел
	Табельный_номер				Номер_отдела
	ФИО

Рисунок 23. Связь типа 1:1 (Один к одному)

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

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

При этом у них должен быть один и тот же тип данных, если только поле, являющееся первичным ключом, не относится к типу "Счетчик".

Поле типа "Счетчик" можно связать с полем типа "Числовой" только в том случае, если для свойства FieldSize (размер поля) каждого из них задано одно и то же значение.

Например, можно связать столбцы типов "Счетчик" и "Числовой", если для свойства

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

В связи «супертип-подтип» общие атрибуты типа определяются в сущности-

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

УЧредитель образуют подтипы юридическое лицо и физическое лицо (рис. 24). Свойство,

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

Существуют следующие виды идентификаторов:

· первичный / альтернативный – сущность может иметь несколько идентификаторов

(рис. 25). Один должен быть основным (первичным), другие – альтернативными.

Первичный идентификатор на диаграмме подчеркивается. Альтернативные идентификаторы предваряются символами <1> для первого, <2> для второго и т. д.;

·простой / составной – идентификатор, состоящий из одного атрибута, является простым, из нескольких атрибутов – составным;

·абсолютный / относительный – если атрибуты, составляющие идентификатор,

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

61

Учредитель

Реестровый номер

0,1 0,1

1,1					1,1
	Юридическое лицо			Физическое лицо
Код организационно правовой формы				Номер паспорта
Код вида деятельности				Серия паспорта
Наименование
Адрес
Рисунок 24. Связь «супертип-подтип»
				Составной
	Сотрудник
			альтернативный
	Табельный номер
			идентификатор
	<1>Фамилия
	<1>Дата рождения
	Имя
	Адрес

Рисунок 25. Составной альтернативный идентификатор

На рис. 26 идентификатор сущности Строка_заказа является относительным. Он

включает идентификатор сущности Заказ, что показывается подчеркиванием 1,1.

Строка_заказа	1,1		1,N	Заказ
Номер строки				Номер заказа
Номер продукта				Дата заказа
Описание продукта				Общая сумма
Количество продукта

Рисунок 26. Относительный идентификатор

При построении ER-модели необходимо ответить на вопросы:

·что следует считать сущностью, а что – атрибутом?

·когда следует делить класс на подклассы?

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

имеющие более одного идентификатора;

для которых фиксируются какие-либо свойства;

62

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

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

Количественные характеристики всегда являются атрибутами и никогда – сущностями. Они никогда не являются идентификаторами и не входят в состав идентификаторов. Следует стремиться отобразить информацию как можно более детально. Так, например, если Адрес, ФИО являются составными характеристиками, то желательно отразить это в модели.

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

а для женщин – нет, подклассы следует выделить.

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

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

7.1.Даталогическое проектирование

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

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

Проектирование логической структуры РБД предполагает:

разбиение всей информации по отношениям (таблицам);

определение состава полей (атрибутов) каждого отношения;

определение ключа каждого отношения;

определение связей и обеспечение целостности по связям.

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

63

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

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

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

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

(связывающие) соответствующие данные.

2) существенное влияние оказывает характер обработки. Частые обращения к совместно обрабатываемым данным предполагают их совместное хранение, а данные, к

которым обращаются редко, целесообразно хранить отдельно.

Рассмотрим по шагам общий подход к построению РБД на основе инфологической модели, представленной ER-диаграммой.

1.Каждая простая сущность превращается в таблицу (отношение). Имя сущности становится именем таблицы. Каждый простой атрибут становится столбцом таблицы с тем же именем: R1 (И1 , А1 , А2 , А3 ).

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

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

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

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

Некоторые СУБД (Access, Paradox и др.) позволяют автоматически генерировать в качестве ключа таблицы поле типа «счетчик». Этот искусственный код можно использовать для простых объектов, если в предметной области не предполагается применение другой системы кодирования (ОКПО, ОКОНХ, ИНН).

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

находящейся на дальнем конце связи (процесс может продолжаться рекурсивно).

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

64