Базы данных Тема 1. 2 Проектирование реляционных баз данных
Реляционная модель данных Понятие отношения (relation), домена, кортежа и атрибута Домены: Фамилии ={'Иванов', 'Петров', 'Сидоров', 'Михайлова'} Телефоны ={'567 56 33', '678 44 12', '456 65 79'} Декартово произведение доменов: Фамилии х Телефоны ={('Иванов', '567 56 33'), ('Иванов', '678 44 12'), ('Иванов', '456 65 79'), ('Петров', '567 56 33'), ('Петров', '678 44 12'), ('Петров', '456 65 79'), ('Сидоров', '567 56 33'), ('Сидоров', '678 44 12'), ('Сидоров', '456 65 79'), ('Михайлова', '567 56 33'), ('Михайлова', '678 44 12'), ('Михайлова', '456 65 79')} Отношение: Фамилии х Телефоны = {('Иванов', '567 56 33'), '678 44 12'), (‘Михайлова', '456 65 79')} ('Петров',
n Базовой структурой реляционной модели данных является отношение (relation). n Отношение – это подмножество декартово произведения доменов. n n n Домен – это множество значений, которое может принимать элемент данных (например, множество целых чисел, множество дат, множество всех названий улиц России, множество всех телефонных номеров какого либо города и т. п. Домен является важнейшим понятием, на котором строится вся реляционная теория, хотя, далеко не все СУБД полноценно поддерживают это понятие. В СУБД в основном домен задается типом данных и семантическими ограничениями целостности (например, перечислением элементов, указанием диапазона значений и т. д. ). Пусть имеется множество (домен) фамилий сотрудников некоторой фирмы, а также множество (домен) домашних телефонов сотрудников фирмы. Декартово произведение этих доменов (Фамилии х Телефоны) позволяет получить все возможные комбинации элементов доменов Фамилии и телефоны. Комбинации элементов доменов, представленные в декартовом произведении, в большинстве своем не отражают действительности — соответствие фамилии сотрудника и телефона, но можно выделить отношение, которое как раз и будет отражать такое соответствие. В результирующем отношении отсутствует фамилия ‘Сидоров‘, так как у него нет домашнего телефона. Чтобы в результирующем отношении не потерять сотрудников, у которых нет телефонов, можно дополнить множество телефоны элементом ' ' — пустая строка, означающей отсутствие телефона (или "пустой" телефон). Тогда к результирующему отношению добавится пара ( ' Сидоров ' , ' ' ). Элементы отношения называют кортежами. Каждый кортеж отношения соответствует одному экземпляру сущности определенного типа. Элементы кортежа принято называть атрибутами.
Свойства отношений n Отношение Сотрудники
n Отношение обладает двумя основными свойствами: 1. 2. n n n В отношении не должно быть одинаковых кортежей, так как это множество; Порядок кортежей в отношении не существенен. Таким образом в кортеже не бывает первого, второго или последнего кортежа: при выводе данных отношения кортежи выводятся в произвольном порядке, если не задано упорядочивание по значениям атрибутов. Отношение удобно представлять в виде таблицы, где строка является кортежем, а столбец соответствует домену (см. на слайде отношение Сотрудники). Количество строк в таблице (кортежей в отношении) называют мощностью отношения, количество столбцов (атрибутов) – арностью. Отношение имеет имя, которое отличает его от имен всех других отношений. Атрибутам отношения назначаются имена, уникальные в рамках отношения. Обращение к отношению происходит по его имени, а обращение к атрибуту – по имени отношения и имени атрибута. Каждый атрибут определен на некотором домене. Атрибут может быть обязательным (NOT NULL) и необязательным (NULL). Перечень имен столбцов таблицы (или атрибутов отношения) называется схемой отношения. Для таблицы Сотрудники схемой отношения будет множество {Фамилии, телефоны}. Cовокупность всех схем отношений базы данных называется схемой или каталогом базы данных.
Понятие первичного ключа
Ключи n n n Так как строки в реляционной таблице не упорядочены, то нельзя выбрать строку по ее номеру. Для этого используют ключи. Ключом называют такой элемент данных, по которому можно определить значения других элементов данных. В реляционной базе данных в каждой таблице есть один или несколько столбцов, значения в которых во всех строках разные. Этот столбец (столбцы) называется первичным ключом таблицы. Первичный ключ (primary key) – это атрибут или группа атрибутов, которые единственным образом идентифицируют каждую строку в таблице. Ключи, состоящие всего из одного столбца, принято называть простыми ключами, в противном случае их называют составными ключами. Атрибуты, которые могут быть выбраны в качестве первичного ключа называют возможными (или потенциальными) ключами. Выбор составного первичного ключа влечет за собой ряд неудобств: q q n составной первичный ключ может оказаться громоздким, и работа с ним может быть неудобной. Операции, в которых система использует первичный ключ, будут выполняться медленнее, чем если бы первичный ключ был простым; составной первичный ключ неудобно использовать в связях между таблицами, т. к. все его столбцы придется дублировать во второй таблице. При этом любое изменение в столбцах первичного ключа первой таблицы должно быть продублировано в соответствующих столбцах второй связанной таблицы. Ключи на основе атрибутов, реально отражающих некоторые свойства предметной области. называют естественными. Естественные ключи обладают двумя существенными недостатками.
Ключи При изменении полей, составляющих такой ключ, приходится вносить изменения во все таблицы, связанные по этому ключу с данной таблицей (каскадные изменения). Изменение первичного ключа — дело весьма обыкновенное, поскольку это реальная характеристика, а характеристика может меняться. • ü Уникальность естественных ключей может опровергаться существующими реалиями, поскольку: на момент внесения информации он может быть неизвестен ( NULL); в результате ошибки или каких нибудь нестыковок могут оказаться записи с одинаковыми значениями таких ключей. n n n Преимуществом перед естественными ключами обладают суррогатные ключи. Суррогатные ключи не имеют никакого отношения к предметной области и генерируются автоматически при добавлении новой записи к таблице. Главным преимуществом суррогатного ключа является то, что для конкретной записи таблицы он никогда не изменяется, а, следовательно, все связи данной записи с записями других таблиц гарантированно не будут нарушены. Как правило, суррогатный ключ состоит из одного столбца, который специально добавляется для хранения такого ключа. В простейшем случае суррогатный ключ представляет собой просто счетчик добавляемых строк, значения которого генерируются СУБД. Итак, доступ к конкретной записи таблицы осуществляется по значению первичного ключа. Следовательно: он обязательно, при любых обстоятельствах должен быть уникальным; значение первичного ключа всегда должно быть однозначно определено (не может быть неопределенным). И то и другое требование к первичному ключу, как правило, гарантированно обеспечивается СУБД. Основным же механизмом поддержки требований к первичному ключу является индекс.
Связи между таблицами. Понятие внешнего ключа n n n «один – к – одному» «один – ко – многим» «многие – к – одному» «многие – ко – многим» Рекурсивные связи
Связи между таблицами n n Для организации связи между таблицами в одной таблице (главной) используется первичный ключ, а в другой таблице (подчиненной) внешний ключ (foreign key). Внешний ключ подчиненной таблицы – это копия первичного ключа главной таблицы. Связь между таблицами организуется, если значение первичного ключа главной таблицы совпадает со значением соответствующего внешнего ключа подчиненной таблицы.
Проектирование баз данных Пример физической модели базы данных в нотации IE (Information Engineering)
Правила соответствия бинарных связей сущностей и элементов реляционной базы данных
Правила соответствия бинарных связей сущностей и элементов реляционной базы данных
Правила преобразования из ER-диаграммы реляционной базы данных n n n Процесс порождения из ER диаграммы реляционной базы данных можно легко формализовать, довести до автоматизма. Почти всегда есть взаимно однозначное соответствие между сущностью и таблицей. При этом атрибуты сущности переходят в атрибуты (столбцы) таблицы, а первичный ключ сущности — в первичный ключ таблицы. В табл. представлены правила соответствия бинарных связей сущностей и элементов реляционной базы данных. Правила порождения позволят легко перейти от логической модели данных к физической модели. При тщательном анализе предметной области для выявления сущностей при переходе к реляционной базе данных дополнительная нормализация таблиц, скорее всего, не понадобится. Зависимости внутри таблицы часто означают, что мы в одной таблице пытаемся вместить несколько сущностей. Возникает вопрос: как супертип и подтипы будут преобразовываться к реляционным структурам? Здесь возможно два решения. Ø Каждый супертип и подтипы будут преобразованы в таблицы. Связи устанавливаются между таблицей, представляющей супертип, и каждой из таблиц, представляющих подтипы. Связи имеют тип "один к одному" и осуществляются посредством первичного ключа таблицы, представляющей супертип. Ø Преобразование осуществляется в одну таблицу, состоящую из столбцов перешедших от супертипа, и столбцов, перешедших от подтипов.
Проблемы NULL «значений» q Как выполнять арифметические действия, если хотя бы один из операндов имеет неопределенное значение (NULL) q Сравнение столбцов, помеченных как null. q Сортировка по столбцам, содержащим значение null. q Поддержка СУБД работы с null.
О «значении» NULL n n n В теории РБД При вводе строки таблицы необходимо ввести значения всех ее столбцов. На практике, однако, не все значения могут быть известны. Это случается совсем не редко, более того, в определенных ситуациях это является правилом. Например, если человек устраивается на работу, то какие то данные (отдел, должность, оклад и т. п. ) могут заполняться, лишь через некоторое время. Кодд предложил, что в таких случаях следует использовать неопределенное значение. Такое значение принято обозначать как NULL. Использование NULL кроме очевидных выгод приносит и определенные проблемы. Как выполнять арифметические действия, если хотя бы один из операндов имеет неопределенное значение (NULL). Здесь возможны Два решения проблемы: Ø Ø n арифметическое значение принимает значение NULL, если хотя бы один из операндов имеет неопределенное значение; если хотя бы один из операндов в арифметическом выражении принимает значение NULL, система должна сгенерировать ошибку (исключение). Сравнение столбцов, помеченных как NULL. Если требуется сравнить два столбца, при этом хотя бы один из столбцов принимает значение NULL, TO ясно, что результат не может быть равен "истина" или "ложь". Мы приходим к выводу, что надо вводить третье логическое значение. Другими словами, мы приходим к новой (трехзначной) логике. Назовем такое значение UNKNOWN (неизвестное).
О «значении» NULL n Сортировка по столбцам, содержащим значение NULL. Обычно предлагается использовать четыре возможных сценария: Ø Ø § § значения NULL считаются самыми большими, т. е. при выполнении сортировки по возрастанию они оказываются в начале списка; значения NULL считаются самыми маленькими, т. е. при выполнении сортировки по возрастанию они оказываются в конце списка; при любом виде сортировки значения NULL ДОЛЖНЫ ИДТИ В начале списка; при любом виде сортировки значения NULL должны идти в конце списка. Поддержка СУБД работы с NULL. ДЛЯ ТОГО чтобы работать со значениями NULL, СУБД должно предоставлять средства их обработки. Типичным примером такого средства является оператор is null, используемый в условных конструкциях языка SQL (предусмотрен стандартом SQL 92) и его расширениях. Воздействуя на операнд, он возвращает TRUE, если операнд имеет неопределенное значение, и FALSE В остальных случаях. Используя возможности СУБД работы с NULL, следует отсекать NULL от использования в арифметических и логических выражениях. Особо следует отметить, что значение NULL неприемлемо для первичного ключа, поскольку по определению первичный ключ призван однозначно идентифицировать строки таблицы. Для возможных же ключей (при условии, что первичный ключ уже выбран) значение NULL вполне допустимо и не противоречит требованию отсутствия дублирующих значений.
Базовые требования целостности n Требование целостности сущностей: любое отношение должно обладать первичным ключом n Требование целостности по ссылкам: для каждого значения внешнего ключа подчиненного отношения в основном отношении должен найтись кортеж с таким же значением первичного ключа, иначе значение внешнего ключа подчиненного отношения должно быть неопределенным (то есть ни на что не указывать)
Реляционная модель данных: три составляющие n n n n Реляционная модель данных состоит из трех частей, описывающих разные аспекты реляционного подхода: структурной части, манипуляционной части и целостной части. В структурной части модели фиксируется, что единственной структурой данных, используемой в реляционных БД, является нормализованное n арное отношение. В манипуляционной части модели вводится два фундаментальных механизма манипулирования реляционными БД – реляционная алгебра и реляционное исчисление. Первый механизм базируется на классической теории множеств, а второй – на классическом логическом аппарате исчисления предикатов первого порядка. Эта часть реляционной модели вводит меру реляционности любого конкретного языка РБД: язык называется реляционным, если он обладает не меньшей мощностью, чем реляционная алгебра или реляционное исчисление. В целостной части модели фиксируются два базовых требования целостности, которые должны поддерживаться в любой реляционной СУБД. Первое – требование целостности сущностей и второе – требование целостности по ссылкам (см. слайд). Для соблюдения целостности сущности достаточно гарантировать отсутствие в любом отношении кортежей с одним и тем же значением первичного ключа. С целостностью по ссылкам дела обстоят несколько более сложно. При обновлении ссылающегося отношения (вставке новых кортежей или модификации значения внешнего ключа в существующих кортежах) достаточно следить за тем, чтобы не появились некорректные значения внешнего ключа. Но как быть при удалении кортежа (обновлении значения первичного ключа кортежа) из отношения, на которое ведет ссылка? Здесь существует три подхода, каждый из которых поддерживает целостность по ссылкам (см. следующий слайд).
Способы поддержки целостности по ссылкам n n n Запрещается производить удаление кортежа (обновление значения первичного ключа кортежа), если на него существуют ссылки; RESTRICT При удалении кортежа (обновлении значения первичного ключа кортежа), на который имеются ссылки, во всех ссылающихся кортежах подчиненного отношения значения внешнего ключа автоматически становится неопределенным ; SET NULL Каскадное удаление (обновление) связанных кортежей (при удалении кортежа из основного отношения автоматически удаляются все ссылающиеся кортежи из подчиненного отношения) ; CASCADE
Нисходящее и восходящее проектирование реляционных баз данных
Нормализация n n n Исходя из практики, можно выделить два исходных состояния, из которых начинается проектирование базы данных. Ø Проектирование начинается с чистого листа: имеется предметная область, требования заказчика, некоторое видение будущей информационной системы. Такой подход к проектированию баз данных называют еще проектированием "сверху вниз“ (с использованием модели «сущность связь» ). Ø В исходном состоянии имеется некоторое количество уже используемых (заполненных) таблиц, которые использовались различными приложениями и, возможно, различными пользователями, вероятно, даже работающими в разных отделах. Есть смысл попытаться на основе уже существующих таблиц и единых требований построить единую базу данных. Такой подход называется проектированием "снизу вверх“ (на основе нормализации. ) Возникает законный вопрос: что является критерием создания хорошей реляционной базы данных? Иногда можно услышать рассуждения о правильно и неправильно спроектированных базах данных. Это весьма условные оценки. Основным критерием, которым мы будем руководствоваться при изложении принципов нормализации, является удобство модификации данных (вставки строк, удаления строк, обновления строк). Но есть и другой критерий: структура базы данных должна быть оптимизирована для выполнения сложных запросов. Во многих системах на первый план выходят именно запросы, а операции модифицирования выполняются не часто.
Нормальные формы
Нормальные формы n Процесс нормализации был впервые предложен Коддом (в статье "Further normalization of the data base relational model", 1972). Им были предложены три нормальные формы. В дальнейшем была предложена нормальная форма Бойса — Кодда, а затем 4 я и 5 я нормальные формы. Нормальная форма — это некоторый набор требований. Причем эти требования вбирают в себя требования нормальных форм, имеющих более низкий номер. Процесс нормализации — это последовательный процесс приведения реляционной базы данных к требованиям нормальных форм. Такой процесс осуществляется последовательно: вначале добиваются требований первой нормальной формы, затем второй и т. д.
Нормальные формы Избыточность данных и аномалии модификации Поставщик_поставка
Нормальные формы n § § § Избыточность данных — хорошо это или плохо? Во первых, почему это может быть плохо? Тут есть две причины: 1. база данных, содержащая избыточные данные, требует больше места во внешней памяти; 2. избыток данных приводит к определенным проблемам при модификации данных (аномалии модификации). Далее мы подробно разберем эту проблему. Наличие одних и тех же данных в разных таблицах требует, чтобы была гарантия их идентичности. Во вторых. Почему избыточность данных может оказаться полезной? И здесь можно выделить две причины: 1. избыток или дублирование данных является основным способом их защиты от повреждения или потери; 2. избыточность данных в некоторых случаях является платой за увеличение производительности системы. Рассмотрим таблицу Поставщик_поставка, содержащую информацию, о поставках некоторых комплектующих на предприятие. В таблице содержится номер поставки, который, очевидно, следует принять в качестве первичного ключа. Также имеется информация о дате поставки, объеме поставки, поставщике и городе, откуда эта поставка была произведена. Прежде всего, бросается в глаза, что информация о поставщиках дублируется. И можно быть уверенным, она будет дублироваться и далее, т. к. один и тот же поставщик, как правило, работает с предприятием на постоянной основе. Посмотрим, какие проблемы могут возникнуть при выполнении операций модификации.
Нормальные формы n § § Аномалии вставки: Ø при вставке данных о новой поставке мы должны добавить информацию о поставщике. Причем данные о поставщике должны соответствовать тем данным о нем, которые уже есть в таблице. Если вдуматься, то задача не такая уж простая, т. к. ошибка в фамилии поставщика или города, откуда поставка произошла, может привести к появлению нового поставщика; Ø поскольку в таблице хранится информация и о поставках и поставщиках, то может возникнуть необходимость добавить информацию о поставщике. И тут сразу возникает проблема: что нужно ставить в столбцах, где хранится информация о поставке? "Конечно NULL", — скажете вы. Да, но вот тут возникает еще одна проблема. Номер_поставки — первичный ключ, а первичному ключу нельзя присваивать значение NULL. Таким образом, для того чтобы ввести нового поставщика, придется ждать поставки от него. Аномалии обновления. Если есть необходимость изменить атрибуты поставщика (Фамилия, Город), то придется сделать это во всех поставках, которые были осуществлены этим поставщиком. Если это сделать не во всех строках, то база данных будет содержать противоречивые сведения. Аномалии удаления. Самое неприятное, чем грозит попытка удалить из таблицы сведения о какой либо поставке, — это одновременное удаление сведений и о поставщике. В нашей таблице Поставщик_Поставка при удалении поставки за номером 1320 будет удалена информация о поставщике Сидоров из Барнаула.
Нормальные формы Декомпозиция
Нормальные формы n n n Декомпозиция — это обратимое разбиение таблицы на две или более таблиц. Разбиение осуществляется по столбцам таблицы. Процесс, обратный декомпозиции, называется композицией. Поскольку декомпозиция — обратим операция, то композиция должна привести к исходной таблице. Обратимость — это очень важное требование. Оно предполагает сохранение внутренних зависимостей, которые существовали в исходной таблице. Если в процессе разбиения мы пропустим такую зависимость, то часть информации будет нами утеряна. Зависимости же, которые существовали в исходной таб ице, полностью или частично должны перейти в связи между таблицами. Рассмотрим таблицу {Преподаватель, Читаемый курс, Учебник}, которая содержит информацию о преподавателях, читаемых ими курсах и учебниках. Поставим предварительно условие, что преподаватели и учебники никак не связаны друг с другом. Произведем вертикальное разбиение на две таблицы по столбцу Читаемый курс. В результате получим две таблицы. Из табл. {Преподаватель, Читаемый курс} удалена дублирующая строка (<Иванов, история>) в соответствии с требованием теории реляционных баз данных. Декомпозиция на две таблицы — это, по сути, взятие двух проекций, а композиция представляет собой естественное соединение.
Нормальные формы Функциональная зависимость в отношении A > В – функциональная зависимость атрибута В от А Табельный номер > Фамилия
Нормальные формы n n n Атрибут В таблицы функционально зависит от атрибута А в том и только в том случае, если каждому значению атрибута А соответствует одно определенное значение атрибута В. Если каждому значению атрибута А ставится в соответствие только одно значение атрибута В, то двум разным значениям атрибута А могут соответствовать одно и то же значение атрибута В. Если атрибут В функционально зависит от атрибута А, то атрибут А называется детерминантом атрибута В. Рассмотрим табл. Сотрудники. Какова зависимость атрибута Фамилия от атрибута Табельный номер? Атрибут Табельный номер является первичным ключом таблицы. Значение табельного номера должно однозначно определять сотрудника, а следовательно, однозначно определять и его фамилию. Налицо функциональная зависимость атрибута Фамилия от атрибута Табельный номер. Рассмотрим таблицу Поставшик_поставка. При внимательном рассмотрении можно увидеть, что все атрибуты таблицы функционально зависят от атрибута Номер_поставки. Но другие функциональные зависимости в таблице не просматриваются, во всяком случае, без дополнительных предположений. Так предположив, что в течение суток не может быть более одной поставки комплектующих, мы придем к естественному выводу, что атрибуты таблицы функционально зависят и от атрибута Число. Сделаем один очевидный, но важный вывод: атрибуты таблицы функционально зависят от первичного ключа. Наши рассуждения о функциональной зависимости можно распространить на составные атрибуты. Обратите внимание, что в качестве первичного ключа также может быть выбран составной атрибут.
Нормальные формы n n n В стороне пока остается причина появления функциональной зависимости в таблицах. Большинство функциональных зависимостей отражают зависимости, существующие в предметной области. Например, номер паспорта гражданина связан с его индивидуальным номером налогоплательщика по причине того, что они относятся к одному и тому же человеку. При добавлении к таблице суррогатного ключа мы получаем полноценную функциональную зависимость других столбцов таблицы от этого ключа. Зависимость эта, однако, не имеет никакого отношения к рассматриваемой предметной области. Рассматривая зависимости в конкретной таблице, можно выявить случайные зависимости, не являющиеся в действительности функциональными. Примером может служить мнимая функциональная зависимость столбца Оклад от столбца Фамилия. Но фамилии могут и совпадать у различных людей, и при появлении в отделе однофамильца все наше представление о наличии функциональной зависимости рушится. Среди всех функциональных зависимостей выделяют полную функциональную зависимость. ОПРЕДЕЛЕНИЕ Атрибут В находится в полной функциональной зависимости от А, если он функционально зависит от А (А -> В) и не зависит ни от какой части атрибута А. Из определения, в частности, следует, что если атрибут А состоит всего из одного столбца, то все функциональные зависимости от него других атрибутов являются функционально полными.
Нормальные формы Первая нормальная форма n n {ФИО, Зарплата, Должность} {Фамилия, Имя, Отчество, Зарплата, Должность}
Нормальные формы n n n Таблица удовлетворяет требованиям первой нормальной формы, если на пересечении любой строки и столбца таблицы находятся простые атомарные элементы. Если в некотором столбце содержатся сложные элементы, состоящие из нескольких элементов, принадлежащих различным доменам, то, для того чтобы привести таблицу к первой нормальной форме, следует вместо указанного столбца добавить по столбцу на каждый элемент. Примером может служить уже столбец ФИО ИЛИ такой элемент, как адрес. Важно понять следующее: если элементы, составляющие сложный элемент, принадлежат разным доменам, то мы легко можем определить их максимальное количество, а следовательно, и количество добавляемых столбцов. Нарушение первой нормальной формы может быть и несколько иного рода, а именно, когда сложный элемент состоит из произвольного количества однородных элементов, т. е. элементов, принадлежащих одному домену. Например, столбец может содержать некоторый список, например, полученных сумм. В этом случае максимальное количество элементов трудно определить, и оно может быть велико. В этом случае для удовлетворения требованиям первой нормальной формы придется применить другой подход: выделить для каждого элемента строку. При этом значения других столбцов в пределах группы, относящейся к одному сложному элементу, должно остаться неизменным.
Нормальные формы Вторая нормальная форма
Нормальные формы n n n Требования второй нормальной формы относятся к таблицам с составными первичными ключами. Таким образом, все таблицы, у которых есть простые первичные ключи, автоматически находятся во второй нормальной форме. ОПРЕДЕЛЕНИЕ. Находящаяся в первой нормальной форме таблица будет находиться во второй нормальной форме, если любой ее атрибут, не входящий в состав первичного ключа, функционально полно зависит от этого первичного ключа. Рассмотрим пример. В табл. содержится информация о некоторых книгах и издательствах, где эти книги были выпущены. Совокупность столбцов Издательство и Название может быть выбрано в качестве первичного ключа. Соответственно, все атрибуты таблицы, не входящие в первичный ключ, будут функционально зависеть от этого первичного ключа. Но есть еще один момент. Издательство располагается в конкретном городе. Следовательно, столбцу Издательство соответствует строго один определенный город. На рис. схематически представлена зависимость столбца Город от первичного ключа и части первичного ключа. Стрелками обозначаются функциональные зависимости. Другие атрибуты табл. функционально зависят от первичного ключа, но ни от какой его части не зависят. Имеем, таким образом, одно нарушение второй нормальной формы. Нарушение второй нормальной формы приводит к некоторым аномалиям модификации таблицы. В частности, если потребуется обновлять название издательства, т. е. изменить его, то, возможно, придется менять и город.
Нормальные формы n n n Для решения проблемы, т. е. приведения таблицы ко второй нормальной форме, необходимо провести декомпозицию. При этом в первую таблицу войдут атрибуты, не зависящие от части первичного ключа, и сам первичный ключ. Во вторую таблицу войдет атрибут, который зависит от части первичного ключа, и соответствующая часть первичного ключа. Другими словами, декомпозицию следует проводить по атрибуту, который является частью первичного ключа и от которого зависят другие атрибуты. При этом при декомпозиции, как и полагается, следует удалить дублрующие строки. В нашем примере была удалена строка со значением "Земля" атрибута Издательство. Проверьте, что из полученных таблиц легко можно восстановить исходную таблицу. Мы сохранили функциональные зависимости. Если нарушение второй нормальной формы идет по нескольким атрибутам, другими словами, несколько атрибутов таблицы зависят от части первичного ключа, то декомпозицию придется проводить на три и более таблицы. Это можно сделать поэтапно, разбив вначале исходную таблицу на две таблицы, а затем в таблице, где нарушена вторая нормальная форма, провести еще одно разбиение и т. д.
Нормальные формы Третья нормальная форма Номер_поставки > Номер_поставщика > Город_поставщика Т. е. Номер_поставки >> Город_поставщика
Нормальные формы n n § § § Третья нормальная форма связана с таким понятием, как транзитивная зависимость. ОПРЕДЕЛЕНИЕ. Пусть для атрибутов А, В, С некоторой таблицы выполняются соотношения А -> В, В -> С. Тогда говорят, что атрибут С транзитивно (через атрибут В) зависит от атрибута А. Кроме этого, должно выполняться условие: атрибут А не должен функционально зависеть ни от атрибута В ни от атрибута С. Определение третьей нормальной формы: Таблица, находящаяся во второй нормальной форме, будет находиться в третьей нормальной форме, если все ее атрибуты, не входящие в первичный ключ, не зависят транзитивно от первичного ключа. Таблица содержит информацию о поставках некоторых товаров и поставщиках. Первичным ключом отношения, очевидно, может быть выбран столбец Номер_поставки. Поскольку первичный ключ простой, то таблица удовлетворяет требованиям второй нормальной формы (требования первой нормальной формы также выполняются, т. к. содержимое всех столбцов— это простые данные). Но в таблице есть некая проблема. Если вы хотите добавить туда поставщика, вам придется добавить также и данные о поставке. Попытка же удалить запись о поставке может привести к удалению данных о поставщике. Удалится не только его номер, но и город, откуда поставщик. Наконец, если изменится город поставщика, придется просматривать и изменять город для данного поставщика по всей таблице.
Нормальные формы § § § В таблице имеется нарушение третьей нормальной формы. А именно из того факта, что Номер_поставки — первичный ключ, вытекает, что Номер_поставки -> Номер_поставщика. С другой стороны, номер поставщика однозначно определяет и его адрес. Поэтому Номер_поставщика -> Город_поставщика. Налицо, таким образом, транзитивная зависимость атрибута Город_поставщика от атрибута Номер_поставки. Как и в случае со второй нормальной формой, для того чтобы добиться выполнения требований третьей нормальной формы, необходимо провести декомпозицию исходной таблицы. Проведем разбиение таблицы по столбцу Номер_поставщика.
Нормальные формы Четвертая нормальная форма {Поставщик, Заказчик, Магазин} Заказчик » Поставщик Заказчик » Магазин n В результате декомпозиции получим две таблицы: {Поставщик, Заказчик} {Заказчик, Магазин}
Нормальные формы n n n Четвертая нормальная форма связана с таким понятием, как множественная зависимость. Множественная зависимость является обобщением функциональной зависимости. Другими словами, функциональная зависимость является частным случаем многозначной зависимости. Рассмотрим в качестве примера таблицу со следующей схемой: {Поставщик, Заказчик, Магазин}. В таблице содержится информация о поставщиках, заказчиках и магазинах заказчика. Поставим условие, что атрибут поставщик никак не зависит от атрибута магазин, что вполне логично, т. к. поставка идет заказчику, а не в магазин. Предположим также, что данному заказчику может соответствовать произвольное количество поставщиков и магазинов. Первичным ключом данной таблицы является совокупность всех ее столбцов, а поэтому она находится в третьей нормальной форме. Несмотря на это, работать с таблицей неудобно. Действительно, если необходимо для данного заказчика добавить нового поставщика, то делать это придется, указав конкретный магазин. Удалить же поставщика можно, только удалив все записи, где он присутствует, что может привести к удалению и заказчика. В указанном примере причиной проблем является так называемая многозначная зависимость. Дело в том, что поле заказчик неявно определяет и множество поставщиков, и множество магазинов. Такое отношение называется многозначной зависимостью. Итак, в нашем случае налицо две многозначные зависимости. Записывается это так: Заказчик -» Поставщик И Заказчик -» Магазин. Таблица будет находиться в четвертой нормальной форме , если она находится в предыдущих нормальных формах и при наличии многозначной зависимости, например, атрибута В от атрибута А, другие атрибуты не будут многозначно зависеть от атрибута А. В нашем примере, очевидно, таблица не находится в четвертой нормальной форме. Для того чтобы устранить проблему, можно провести декомпозицию.
Нормальные формы Пятая нормальная форма {Поставщик, Заказчик, Магазин} {Поставщик, Заказчик} {Поставщик, Магазин} {Заказчик, Магазин}
Нормальные формы n n n Пятая нормальная форма связана с таким нарушением в таблице, которое не позволяет проводить декомпозицию на две других таблицы. Пятая нормальная форма является обобщением четвертой нормальной формы. Вернемся к отношению, рассматриваемому в предыдущем разделе: {Поставщик, Заказчик, Магазин}. Мы рассуждали из предположения, что поставщик никак не зависит от магазинов. Это позволило нам провести декомпозицию таблицы на две таблицы. Другими словами, мы провели обратимую операцию, учтя все зависимости в исходном отношении. Если мы допустим, что существует зависимость между атрибутами поставщик и магазин, то придется признаться, что обратимого разбиения таблицы на две таблицы провести невозможно. ОПРЕДЕЛЕНИЕ Если таблица не может быть разбита на две таблицы без потерь зависимости, то говорят о наличии в ней зависимости по соединению (или зависимости соединения). Таблица, в которой присутствует зависимость соединения, не удовлетворяет требованиям пятой нормальной формы. ОПРЕДЕЛЕНИЕ Таблица, в которой отсутствует зависимость соединения, находится в пятой нормальной форме. n В нашем случае, для того чтобы избавиться от зависимости соединения, придется проводить декомпозицию таблицы на три отношения. Итак, имеем в результате три таблицы: {Поставщик, Заказчик} {Поставщик, Магазин} {Заказчик, Магазин} § Полученные таблицы находятся в пятой нормальной форме, т. к. для них выполняется условие четвертой нормальной формы и в них отсутствует зависимость соединения.
Нормальные формы n n Выводы Заканчивая рассмотрение технологии приведения к нормальным формам, подведем некоторые итоги. q Основой для теории нормальных форм является понятие зависимости внутри таблицы: функциональная зависимость, множественная зависимость, зависимость соединения. Наличие таких зависимостей внутри таблицы означает избыточность данных, что приводит к аномалиям модификации. Основной задачей нормализации, таким образом, является перевод зависимостей внутри таблицы в связи между таблицами. q Технология приведения к нормальным формам востребована чаще всего в ситуации, когда у нас уже есть набор таблиц, которые желательно интегрировать в некую единую базу данных, удобную, прежде всего, для выполнения операций редактирования. Как вы помните, основным критерием для перехода к нормальной форме было неудобство выполнения операций добавления, удаления, обновления (аномалии модификации). q При аккуратном проектировании "сверху вниз", как правило, проблем с нормальными формами (кроме 1 НФ) не возникает. Причина здесь проста: тщательный анализ предметной области с выделением сущностей переводит все зависимости к связям между ними и в конечном итоге к связям между таблицами. Впрочем, признаки нарушения требований нормальных форм при проектировании сверху вниз могут послужить неплохим признаком того, что мы по ошибке объединили две или более сущности в одну.
Нормальные формы n n n Денормализация Мы рассмотрели процесс нормализации, но вполне допустим и обратный нормализации процесс, т. е. денормализация. Нормализация приводит к "размножению" таблиц и связей. Но это может привести в конечном итоге к замедлению выполнения сложных запросов, запросов сразу ко многим таблицам. Если в задачу информационной системы входит выполнение большого количества запросов, то может встать вопрос об оптимиза ии работы ИС. Под денормализацией мы будем понимать более широкий спектр действий, чем просто отступление от требований нормальных форм. Речь может идти и о нарушении некоторых других положений реляционной теории баз данных. Цель одна — оптимизировать работу информационной системы. Приступая к процессу денормализации, необходимо очень четко понимать суть проблемы и, в частности, ту ее часть, которая связана с программированием.
Контрольные вопросы 1. Базовые понятия реляционной модели данных : отношение, домен, кортеж, атрибут. Для чего служат NULL-значения в таблице? К каким трудностям приводит использование NULL-значений? 2. Понятие и назначение первичного ключа таблицы. Суррогатные и естественные первичные ключи: достоинства и недостатки. 3. Понятие и назначение внешнего ключа таблицы. Назовите типы связей между таблицами и дайте им характеристику 4. Правила преобразования модели «сущность-связь» в реляционную модель. 5. Базовые требования целостности реляционной модели и способы их поддержки. 6. Понятие и назначение нормализации. Нормальные формы : характеристика и примеры. Что такое денормализация и когда она используется?
Скачать презентацию Базы данных Тема 1 2 Проектирование реляционных баз
Тема 1 2 Проектирование реляционных баз данных.ppt