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

Лекция.6-7 Реляционная модель данных Реляционная модель данных. Структура и операции над данными. Правила получения реляционных отношений из ER-диаграмм. Процесс проектирования реляционной модели данных. Понятие функциональной зависимости. Нормализация реляционных отношений. Алгоритм декомпозиции. 4ч. 1 online.mirea.ru Процесс проектирования БД 2 online.mirea.ru Даталогическая модель Под даталогической понимается модель, отражающая логические взаимосвязи между элементами данных безотносительно их содержания и физической организации. Даталогическая модель разрабатывается с учетом конкретной реализации СУБД, также с учетом специфики конкретной предметной области на основе ее инфологической модели. 3 online.mirea.ru Фактографические даталогические модели Модель данных – совокупность правил порождения структур данных в БД, операций над ними, а также ограничений целостности, определяющей допустимые связи и значения данных, последовательности их изменения. Классификация даталогических моделей (правил порождения структур данных) : • Теоретико-графовые: иерархические; сетевые • Теоретико-множественные: реляционные; бинарных отношений • Объектно-ориентированные. Модель базы данных. • Модель БД (МБД) является средством интеграции содержимого БД и реализации требуемых операций по обработке и управлению данными. • МБД описывается схемой БД, которая определяет ее структуру и ограничения целостности и управления доступом. 4 online.mirea.ru Понятие реляционной модели данных 5 online.mirea.ru Понятие реляционного отношения Таблица удовлетворяющая требованиям ❖Каждый элемент таблицы представляет собой элемент данных, повторяющиеся группы отсутствуют. ❖Все столбцы в таблице однородны. ❖Столбцам присвоены имена. ❖В таблице нет двух одинаковых строк. ❖В операциях с таблицами ее строки и столбцы могут просматриваться в любом порядке и в любой последовательности безотносительно к их информационному содержанию называется реляционным отношением 6 online.mirea.ru Математически определение реляционного отношения Пусть даны N множеств D1,D2,…,DN, тогда R есть отношение над этими множествами, если R есть множество упорядоченных nкортежей вида d1,d2,…,dN , где d1 – элемент из D1, d2 – элемент из D2, dN – элемент из DN. D1, D2,…,DN называются доменами отношения R. Запись: R (D1, D2,…,DN ) D1 – множество символьных строк (Имя домена – Фамилия); D2 – множество символьных строк, (Имя домена – Имя); D3 – множество символьных строк, (Имя домена – Отчество); D4 – множество символьных строк, (Имя домена – Группа). 7 online.mirea.ru Реляционные отношения в базах данных Отношение принимает вид таблицы или файла, где кортежи – строки таблицы или записи в файле. Имена столбцов называются атрибутами, а индивидуальное значение, появляющееся в отдельных кортежах – значением атрибута. Число столбцов в отношении называется степенью. Число кортежей – мощностью. Реляционная база данных определяется как совокупность отношений, содержащих всю информацию хранимую в БД 8 online.mirea.ru Отношения в обработке данных ❖Отношение имеет имя, которое отличает его от имён всех других отношений. ❖ Атрибутам реляционного отношения назначаются имена, уникальные в рамках отношения. ❖Обращение к отношению происходит по его имени, а обращение к атрибуту – по имени отношения и имени атрибута. 9 online.mirea.ru Классификация атрибутов Атрибут может быть ❖Обязательным ❖необязательным. Значение обязательного атрибута должно быть определено в момент внесения данных в БД. Если атрибут необязательный (его значение не определяется в момент ввода), то для таких случаев предусмотрено специальное значение – NULL, которое можно интерпретировать как «неизвестное значение». Значение NULL не привязано к определённому типу данных, т.е. может назначаться данным любых типов. Пример: необязательные воля для заполнения в форме регистрации. 10 online.mirea.ru Первичный ключ реляционного отношения Первичный ключ (Primary Key) – атрибут или набор атрибутов, которые могут быть использованы для однозначной идентификации конкретного кортежа. Значение первичного ключа должно быть уникальным (unique) и обязательным (not null). Первичные ключи выбираются исходя их семантики предметной области. Аналогично первичным ключам сущностей использование ID не рекомендуется. Альтернативные (возможные) ключи – атрибут или набор атрибутов, которые могут быть использованы для однозначной идентификации конкретного кортежа, но не выбраны в качестве первичного. 11 online.mirea.ru Внешний ключ реляционного отношения Для связей между отношениями используются внешние ключи. Внешний ключ (foreign key) – это атрибут подчинённого (дочернего) отношения, который является копией первичного (primary key) или уникального (unique) ключа родительского отношения. Фактически внешние ключи логически связывают экземпляры сущностей разных типов (родительской и подчинённой сущностей). Внешний ключ – это ограничение целостности, в соответствии с которым множество значений внешнего ключа является подмножеством значений первичного или уникального ключа родительской таблицы. 12 online.mirea.ru Ограничение целостности по внешнему ключу Ограничение целостности по внешнему ключу проверяется в двух случаях: • при добавлении записи в подчинённую таблицу СУБД проверяет, что в родительской таблице есть запись с таким же значением первичного ключа; • при удалении записи из родительской таблицы СУБД проверяет, что в подчинённой таблице нет записей с таким же значением внешнего ключа. Ограничения целостности должны поддерживаться СУБД. Для соблюдения целостности сущностей достаточно гарантировать отсутствие в отношении кортежей с одним и тем же значением первичного ключа. 13 online.mirea.ru Ссылочная целостность Необходимо следить за тем, чтобы не появлялись некорректные значения внешних ключей при обновлении отношений (например, заказы несуществующих клиентов). При удалении кортежа существует три подхода, позволяющие поддерживать ссылочную целостность: − запрещается производить удаление кортежа, на который существуют ссылки (либо сначала удалить ссылающиеся кортежи, либо изменить значения их внешнего ключа); − при удалении кортежа, на который имеются ссылки, во всех ссылающихся кортежах значение внешнего ключа становится неопределённым; − при удалении кортежа из отношения, на которое ведётся ссылка, из ссылающегося отношения автоматически удаляются все ссылающиеся кортежи (каскадное удаление). 14 online.mirea.ru Результат проектирования БД Название БД: ____________________________ Спецификация отношений: Имя отношения (перечень атрибутов) Спецификация атрибутов: Имя атрибута Назначение атрибута Тип Допустимые значения Примечание В спецификации атрибутов отмечаются первичный, альтернативные и внешние ключи. Возможно представление диаграммы реляционной модели БД. 15 online.mirea.ru Пример оформления Название БД: «Учет данных о студентах» Спецификация отношений: СТУДЕНТ (ШИФР СТУДЕНТА, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, МЕСТО РОЖДЕНИЯ, АДРЕС, ГРУППА) Первичный ключ отношения: ШИФР СТУДЕНТА Внешние ключи отношения: ГРУППА Спецификация атрибутов: Курсивный фрагмент оформляется для каждой16таблицы БД online.mirea.ru Пример оформления. Спецификация атрибутов Имя атрибута Назначение атрибута ШИФР СТУДЕНТА Хранит шифр студента (номер зачетной книжки) Хранит фамилию. студента Хранит имя студента Строка Char (7) Строка Char (60) Строка Char (30) Хранит отчество студента ПОЛ Хранит код пол по ОКИН ДАТА РОЖДЕНИЯ Хранит дату рождения Строка Char (30) Числ. INT(1) Дата Data Хранит место рожения Строка Char (90) Хранит шифр группы, в которой учится студент Строка Char (10) ФАМИЛИЯ ИМЯ ОТЧЕСТВО МЕСТО РОЖДЕНИЯ ГРУППА Тип Формат Допустимые значения Примечание Первичный ключ 1 – мужской 2 - женский Частично структурированное Внешний ключ 17 online.mirea.ru Получение реляционных отношений из ER – диаграмм 18 online.mirea.ru Принцип получения реляционных отношений из ER – диаграмм ❖Процесс получения реляционных отношений основывается на учете связей между сущностями диаграммы в схеме отношения. ❖Связанность сущность обеспечивается включением в реляционное отношение первичных ключей связанных сущностей. ❖Правила формируются в зависимости от вида бинарной связи и класса принадлежности сущности. 19 online.mirea.ru Бинарная связь 1:1, класс принадлежности сущностей обязательный A (a1 , a2 , … , an , b1 , b2 , … , bm ) a1 – первичный ключ b1 – возможный ключ 20 online.mirea.ru Правило 1 Если степень бинарной связи равна 1:1 и класс принадлежности обеих сущностей является обязательным, то требуется только одно отношение, объединяющее атрибуты двух сущностей. Первичным ключом этого отношения может быть ключ любой из двух сущностей. РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, МЕСТО РОЖДЕНИЯ, НОМЕР, СЕРИЯ, ДАТА ВЫДАЧИ, КЕМ ВЫДАН) Первичный ключ: ТАБ.НОМЕР При реализации этого правила закладывается аномалия в схеме реляционного отношения по третьей нормальной форме. Такие реляционные отношения должны быть более внимательно проанализированы на этапе нормализации. 21 online.mirea.ru Бинарная связь 1:1, класс принадлежности сущностей обязательный и необязательный 1 вариант: A (a1 , a2 , … , an , b1 ) a1 – первичный ключ b1 – возможный ключ и внешний ключ В (b1 , b2 , … , bm ) b1 – первичный ключ Для третьего кортежа реляционного отношения А значение внешнего ключа b1 не определено. Что противоречит понятию внешнего ключа НЕПРАВИЛЬНО 2 вариант: A (a1 , a2 , … , an ) a1 – первичный ключ В (b1 , b2 , … , bm , a1 ) b1 – первичный ключ а1 – внешний ключ Значение внешнего ключа а1 всегда определено. ПРАВИЛЬНО 22 online.mirea.ru Правило 2 Если степень бинарной связи равна 1:1 и класс принадлежности одной сущности является обязательным, а другой необязательным , то необходимо построение двух отношений. Под каждую сущность необходимо выделить по одному отношению, при этом ключ сущности должен служить первичным ключом для соответствующего отношения. Ключ сущности, для которой класс принадлежности является необязательным, добавляется в качестве атрибута в отношение, выделенное для сущности с обязательным классом принадлежности и описывается как внешний ключ. 23 online.mirea.ru Пример выполнения правила 2 РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, МЕСТО РОЖДЕНИЯ) Первичный ключ: ТАБ.НОМЕР АТТЕСТАТ (НОМЕР, СЕРИЯ, ДАТА ВЫДАЧИ, КЕМ ВЫДАН, ТАБ.НОМЕР) Первичный ключ: НОМЕР, СЕРИЯ Внешний ключ: ТАБ.НОМЕР 24 online.mirea.ru Бинарная связь 1:1, класс принадлежности сущностей необязательный 1 вариант: A (a1 , a2 , … , an , b1 ) a1 – первичный ключ b1 – внешний ключ В (b1 , b2 , … , bm , a1 ) b1 – первичный ключ а1 – внешний ключ Для третьих кортежей реляционных отношений А и В значение внешних ключей а1 и b1 не определено. Что противоречит понятию внешнего ключа НЕПРАВИЛЬНО 2 вариант: A (a1 , a2 , … , an ) a1 – первичный ключ В (b1 , b2 , … , bm) b1 – первичный ключ С (a1 , b1 ) a1 – первичный ключ и внешний ключ b1 – возможный ключ и внешний ключ 25 online.mirea.ru ПРАВИЛЬНО Правило 3 Если степень бинарной связи равна 1:1 и класс принадлежности обеих сущностей является необязательным, то необходимо построение трех отношений: по одному для каждой сущности, ключи которых служат в качестве первичных ключей соответствующих отношений, и одно для связи. Среди своих атрибутов отношение, выделенное для связи, будет иметь ключи обеих сущностей, также они будут специфицироваться как внешние ключи. Первичным ключом третьего отношения может быть ключ любой из двух сущностей. 26 online.mirea.ru Пример выполнения правила 3 ГРАЖДАНИН (НОМЕР СЕРИИ, НОМЕР ПАСПОРТА, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО) Первичный ключ: НОМЕР ПАСПОРТА, СЕРИЯ ПАСПОРТА БЛАНКИ ОМС (НОМЕР, СТРАХОВАЯ КОМПАНИЯ) Первичный ключ: НОМЕР ОМС ВЫДАН (НОМЕР, НОМЕР СЕРИИ, НОМЕР ПАСПОРТА, ДАТА ВЫДАЧИ) Первичный ключ: НОМЕР Внешний ключ: НОМЕР СЕРИИ, НОМЕР ПАСПОРТА 27 online.mirea.ru Бинарная связь 1:М, класс принадлежности М-связанной сущностей обязательный A (a1 , a2 , … , an , b1 ) a1 – первичный ключ b1 – внешний ключ В (b1 , b2 , … , bm ) b1 – первичный ключ Значение внешнего ключа b1 всегда определено. 28 online.mirea.ru Правило 4 При построении отношений для связи 1:М учитывается класс принадлежности М-связанной сущности. Класс принадлежности 1связанной сущности на конечный результат не влияет. Если степень бинарной связи 1:М и класс принадлежности Мсвязанной сущности является обязательным, то достаточным является использование двух отношений, по одному на каждую сущность. При условии, что ключ каждой сущности служит в качестве первичного ключа для соответствующего отношения. Дополнительно ключ 1-связанной сущности должен быть добавлен как атрибут в отношение, отведенной для М-связанной сущности в качестве внешнего ключа. 29 online.mirea.ru Пример выполнения правила 4 РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, МЕСТО РОЖДЕНИЯ) Первичный ключ: ТАБ.НОМЕР ДИПЛОМ (НОМЕР, СЕРИЯ, УЧЕБНОЕ ЗАВЕДЕНИЕ, ГОД ВЫДАЧИ, КВАЛИФИКАЦИЯ, КОД ОКСО, ТАБ. НОМЕР) Первичный ключ: НОМЕР, СЕРИЯ Внешний ключ: ТАБ. НОМЕР 30 online.mirea.ru Бинарная связь 1:М, класс принадлежности М-связанной сущностей необязательный 1 вариант: A (a1 , a2 , … , an , b1 ) a1 – первичный ключ b1 – внешний ключ В (b1 , b2 , … , bm , a1 ) b1 – первичный ключ а1 – внешний ключ Для кортежей реляционных отношений А и В значение внешних ключей а1 и b1 не всегда определено. Что противоречит понятию внешнего ключа НЕПРАВИЛЬНО 2 вариант: A (a1 , a2 , … , an ) a1 – первичный ключ В (b1 , b2 , … , bm) b1 – первичный ключ С (a1 , b1 ) a1 – первичный ключ и внешний ключ b1 – внешний ключ 31 online.mirea.ru ПРАВИЛЬНО. Правило 5 Если степень бинарной связи 1:М и класс принадлежности Мсвязанной сущности является необязательным, то достаточным является использование трех отношений, по одному на каждую сущность, причем ключ каждой сущности служит первичным ключом соответствующего отношения, и одного отношения для связи. Отношение для связи должно иметь среди своих атрибутов первичный ключ каждой сущности (внешние ключи). Первичным ключом третьего отношения выбирается первичный ключ Мсвязанной сущности. 32 online.mirea.ru Пример выполнения правила 5 РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, МЕСТО РОЖДЕНИЯ) Первичный ключ: ТАБ.НОМЕР ПРИКАЗ (НОМЕР ПРИКАЗА, ДАТА, ТЕМА) Первичный ключ: НОМЕР ПРИКАЗА РАБОТА РАБ-КА (НОМЕР ПРИКАЗА, ТАБ.НОМЕР) Первичный ключ: НОМЕР ПРИКАЗА 33 Внешний ключ: ТАБ. НОМЕР online.mirea.ru Бинарная связь М:М A (a1 , a2 , … , an ) a1 – первичный ключ В (b1 , b2 , … , bm) b1 – первичный ключ С (a1 , b1 ) a1 , b1– первичный СОСТАВНОЙ ключ a1 – внешний ключ b1 – внешний ключ 34 online.mirea.ru Правило 6 При построении отношений для связи М:М класс принадлежности сущностей на конечный результат не влияет. Если степень бинарной связи равна М:М, то необходимо три отношения: по одному на каждую сущность с первичными ключами от соответствующих сущностей, и одно отношение для связи. Отношение для связи должно иметь среди своих атрибутов ключ каждой сущности как внешние ключи. Первичный ключ третьего отношения составной из первичных ключей двух сущностей. 35 online.mirea.ru Пример выполнения правила 6 РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, МЕСТО РОЖДЕНИЯ) Первичный ключ: ТАБ.НОМЕР СТРАНА (КОД СТРАНЫ, НАИМЕНОВАНИЕ СТРАНЫ) Первичный ключ: КОД СТРАНЫ ГРАЖДАНСТВО (ТАБ.НОМЕР, КОД СТРАНЫ, ПАСПОРТ ИНОЙ СТРАНЫ, ДАТА ПОЛУЧЕНИЯ) Первичный ключ: ТАБ.НОМЕР, КОД СТРАНЫ Внешний ключ: ТАБ.НОМЕР КОД СТРАНЫ 36 online.mirea.ru Нормализация реляционных отношений 37 online.mirea.ru Аномалии в БД. Определение избыточности данных Пример избыточного отношения по атрибуту ТЕЛЕФОН. Достаточно хранить один экземпляр телефона для каждого исполнителя. В этом случае для устранения избыточности необходимо получить два отношения. ТЕМА (ШИФР_ТЕМЫ, ИСПОЛНИТЕЛЬ) ТЕЛ._ИСПОЛНИТЕЛЯ (ИСПОЛНИТЕЛЬ, ТЕЛЕФОН) 38 online.mirea.ru Аномалии вставки, удаления и обновления Вставка. При добавлении новых экземпляров отношения может возникнуть ситуация, когда некоторые из атрибутов не имеют значения ( они могут принимать значение 0 для арифметических данных или пробелы для символьных данных, если событие еще не наступило). При обработке запросов по таким данным будут выдаваться искаженные результаты. Обновление. Если имеет место большая избыточность данных, то к одному и тому же объекту может относиться несколько кортежей. В этом случае изменения должны вноситься во все записи, относящиеся к этому объекту. Удаление. Аномалия сводится к тому, что при необходимости удаления некоторых значение атрибутов в случае определенной избыточности данных, удаляется вся запись. 39 online.mirea.ru Нормализация Процесс разбиения отношения с целью уменьшения вероятности возникновения аномалий манипулирования данными называется нормализацией. Нормализация осуществляется методом декомпозиции. Логической и методической основой декомпозиции является концепция функциональной зависимости между атрибутами в рассматриваемом отношении. 40 online.mirea.ru Функциональная зависимость Если даны два атрибута А и В, то говорят, что В функционально зависит от А, если для каждого значения А существует ровно одно связанное с ним значение В в любой момент времени: А → В При этом А и В могут быть не только атомарными, но и составными. На практике это сводится к тому, что функциональная зависимость В от А означает, что если в любой момент времени известно значение А, то можно одновременно найти значение атрибута В. Примером функциональной зависимости может служить первичный ключ, который определяет значения неосновных атрибутов. Отсутствие функциональной зависимости 41 online.mirea.ru Пример функциональной зависимости РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, МЕСТО РОЖДЕНИЯ) ТАБ.НОМЕР → ФАМИЛИЯ ИМЯ ОТЧЕСТВО ПОЛ ДАТА РОЖДЕНИЯ МЕСТО РОЖДЕНИЯ Первичный ключ является классическим примером функциональной зависимости 42 online.mirea.ru Определение функциональной зависимости через термины реляционной алгебры Пусть R (A1, A2, …, AN) – схема отношения, X и Y – подмножества (A1,A2,…,AN). Говорят, что Х функционально определяет Y (X→Y) , если в любом отношении R, являющемся текущим значением R, не может содержаться два кортежа, компоненты которых совпадают по всем атрибутам, принадлежащим множеству Х, но не совпадают по одному или более атрибутам принадлежащим множеству Y. 43 online.mirea.ru Полная функциональная зависимость Если А → В есть функциональная зависимость и В не зависит функционально от любого подмножества А, то говорят что В функционально полно зависит от А . А представляет собой детерминант А →→ В. ГРАЖДАНСТВО (ТАБ.НОМЕР, КОД СТРАНЫ, ПАСПОРТ ИНОЙ СТРАНЫ, ДАТА ПОЛУЧЕНИЯ) ТАБ.НОМЕР, КОД СТРАНЫ→→ ПАСПОРТ ИНОЙ СТРАНЫ ДАТА ПОЛУЧЕНИЯ Т.е. первичный ключ реляционного отношения функционально полно определяет неосновные атрибуты отношения. 44 online.mirea.ru Способ определения функциональной зависимости Единственный способ определения функциональной зависимости для схемы отношения заключается в тщательном анализе семантики атрибутов этого отношения. В этом смысле зависимости являются фактически отображением связей, существующих в реальном мире. Исходя из функциональной зависимости можно определить как первичные, так и возможные ключи. 45 online.mirea.ru Нормальные формы Наличие тех или иных зависимостей в схеме отношения определяет степень ее нормализации. Для реляционных моделей данных выделяют наиболее значимые четыре нормальные формы (1НФ, 2НФ, 3НФ, 4НФ) и еще одну дополнительную нормальную форму Бойса-Кодда (НФБК). Другие более высокие нормальные формы являются значимыми для объектно-ориентированных моделей. Для приведения реляционного отношения к нормальной форме требуется выполнить его декомпозицию в соответствии в выявленными функциональными зависимостями. Практически выявленные аномалии свидетельствуют о недостаточно внимательной нормализации сущностей на этапе разработки модели предметной области типа «Сущность-Связь». 46 online.mirea.ru Определение 1 НФ Отношение находится в 1 НФ, если каждый его элемент имеет и всегда будет иметь атомарное строение. В этом случае реляционное отношение представлено в виде множества неповторяющихся кортежей. Эта форма является необходимой для работы языков запросов СУБД. Формально само определение реляционного отношения подразумевает удовлетворение 1 нормальной форме. Представление атрибута как частично структурированного, например АДРЕС, не является аномалией по 1НФ. 47 online.mirea.ru Определение 2 НФ Отношение задано во 2НФ, если оно является отношением в 1НФ, и каждый атрибут не являющийся первичным атрибутом в этом отношении, функционально полно зависит от первичного и любого возможного (альтернативного) ключа этого отношения. Первичным называется атрибут, которые входят хотя бы в один из возможных ключей. Если все возможные ключи отношения содержат по одному атрибуту, т.е. являются несоставными, то это отношение задано во 2НФ, т.к. в этом случае все атрибуты не являющиеся первичными полностью зависят от возможных ключей. Если ключи составные, отношение заданное в 1НФ может не быть отношением во 2НФ. 48 online.mirea.ru Анализ приведения реляционного отношения к 2НФ 1 случай – первичный ключ несоставной РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, МЕСТО РОЖДЕНИЯ) Первичный ключ является несоставным, возможных ключей нет, следовательно отношение находится во 2НФ. 49 online.mirea.ru Анализ приведения реляционного отношения ко 2НФ 2 случай – первичный ключ составной ЗНАНИЕ ИН.ЯЗЫКОВ (ТАБ.НОМЕР, КОД ИН.ЯЗЫКА, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, НАЗВАНИЕ ЯЗЫКА, СТЕПЕНЬ ЗНАНИЯ) Первичный ключ: ТАБ.НОМЕР, КОД ИН.ЯЗЫКА ТАБ.НОМЕР → ФАМИЛИЯ ИМЯ ОТЧЕСТВО КОД ИН.ЯЗЫКА→ НАЗВАНИЕ ЯЗЫКА АБ.НОМЕР, КОД ИН.ЯЗЫКА→→ СТЕПЕНЬ ЗНАНИЯ В отношении ЗНАНИЕ ИН.ЯЗЫКОВ выявлены зависимости неосновных атрибутов от элементов первичного ключа. Следовательно, реляционное отношения не находится во 2НФ. 50 online.mirea.ru Приведение реляционного отношения ко 2НФ Для приведения реляционного отношения к нормальной форме требуется выполнить его декомпозицию в соответствии в выявленными функциональными зависимостями. РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО) Первичный ключ: ТАБ.НОМЕР КОДИФИКАТОР ИН.ЯЗЫКОВ (КОД ИН.ЯЗЫКА, НАЗВАНИЕ ЯЗЫКА) Первичный ключ: КОД ИН.ЯЗЫКА ЗНАНИЕ ИН.ЯЗЫКОВ (ТАБ.НОМЕР, КОД ИН.ЯЗЫКА, СТЕПЕНЬ ЗНАНИЯ) Первичный ключ: ТАБ.НОМЕР, КОД ИН.ЯЗЫКА Внешний ключ: ТАБ.НОМЕР 51 online.mirea.ru КОД ИН.ЯЗЫКА Определение 3 НФ Отношение задано в 3 НФ, если оно задано во 2 НФ и каждый атрибут из отношения, не являющийся первичным, нетранзитивно зависит от каждого возможного ключа. Пусть А, В, С – атрибута реляционного отношения. Если С зависит от В, а В – от А, то С зависит от А. Если при этом обратное соответствие неоднозначно (т.е. А не зависит от В или В не зависит от С), то говорят, что С транзитивно зависит от А. Преобразование к 3НФ состоит в разбиении исходного отношения на два: А→ В В →С Функциональная зависимость А→ С исключается. Третья нормальная форма является достаточной для нормализации реляционной модели данных. 52 online.mirea.ru Анализ приведения реляционного отношения к 3НФ РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, МЕСТО РОЖДЕНИЯ, КОД ОКАТО МЕСТА РОЖДЕНИЯ) ТАБ.НОМЕР → ФАМИЛИЯ ИМЯ ОТЧЕСТВО ПОЛ ДАТА РОЖДЕНИЯ КОД ОКАТО МЕСТА РОЖДЕНИЯ КОД ОКАТО МЕСТА РОЖДЕНИЯ→ МЕСТО РОЖДЕНИЯ Таким образов выявлены транзитивные зависимости ТАБ.НОМЕР → КОД ОКАТО МЕСТА РОЖДЕНИЯ→ МЕСТО РОЖДЕНИЯ 53 online.mirea.ru Приведение реляционного отношения к 3НФ Приведение реляционного отношения к 3НФ РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, КОД ОКАТО) Первичный ключ: ТАБ.НОМЕР Внешний ключ: КОД ОКАТО КОДИФИКАТОР ОКАТО (КОД ОКАТО, МЕСТО РОЖДЕНИЯ) Первичный ключ: КОД ОКАТО 54 online.mirea.ru Определение НФ Бойса-Кодда Отношение находится в НФБК, если каждый детерминант отношения является возможным ключом. НФБК является дополнительной проверкой правильной нормализации по предыдущим нормальным формам. Также используется для нормализации универсального реляционного отношения. 55 online.mirea.ru Анализ приведения реляционного отношения к НФБК РАБОТНИК (ТАБ.НОМЕР, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ПОЛ, ДАТА РОЖДЕНИЯ, КОД ОКАТО) Первичный ключ: ТАБ.НОМЕР Внешний ключ: КОД ОКАТО Первичные и альтернативные ключи ТАБ.НОМЕР Детерминанты ТАБ.НОМЕР Реляционное отношение находится в нормальной форме 56 online.mirea.ru Бойса-Кодда Определение 4 НФ: Схема реляционного отношения задана в 4 НФ, если при существовании многозначной зависимости Х определяет Y, где Y не является пустым множеством, Y не принадлежит Х, причем ХY состоит не из всех атрибутов отношения, также существует зависимость Х → А для любого атрибута А отношении. Первичный ключ: Служащий, Ин.язык, Должность Реляционное отношение находится в НФБК, но не в 4НФ 57 online.mirea.ru Приведение к 4НФ Декомпозиция реляционного отношения Первичный ключ: Служащий, ИН.язык Первичный ключ: Служащий, Должность При разработки реляционной модели данных исходя из инфологической модели предметной области типа «Сущность-Связь» аномалии по 4НФ быть не может. Т.К. рассматриваемая множественность вытекает из логики бинарной связи М:М. Как рассматривалось выше, по правилу 6, эта бинарная связь преобразуется к трем реляционным отношениям, которые исключат 58 online.mirea.ru ненормальность по 4НФ. Концепция Алгоритма декомпозиции без потерь Предлагаемый алгоритм целесообразно использовать, если в БД учитывается порядка 20-30 атрибутов. Для баз данных используемых в реальных информационных системах такое практически невозможно. В основу алгоритма полагается удовлетворение НФБК. 1. Разработка универсального отношения для БД. Универсальное реляционное отношение представляет собой совокупность атомарных атрибутов составляющих БД. В случае повторяющихся групп, они оформляются путем введения избыточности данных. 2. Определение всех функциональных зависимостей между атрибутами отношения. 3. Определение того, что находится ли отношение в НФБК. Если «ДА» проектирование завершается, если «НЕТ», отношение должно быть разложено на отношения. 59 online.mirea.ru Алгоритм декомпозиции без потерь Пусть отношение R (A, B, C, D, E…) не приведено к нормальной форма Бойса- Кодда. Определяется функциональная зависимость C → D, про которую известно, что она является причиной того, что отношение не находится в НФБК. С является детерминантом, но не является возможным ключом. Создаются два новых отношения. R1 ( A, B, C, E…) R2 ( C,D ) Отношение R2 называется проекцией отношения R. 60 online.mirea.ru Проектирование баз данных 61 online.mirea.ru Спасибо за внимание! 62 online.mirea.ru