Реляционные базы данных

Дисциплина «Информационные технологии» Лекция №1 «РЕЛЯЦИОННЫЕ БАЗЫ ДАННЫХ» автор., ст. преп. П. А. Осипов ВВЕДЕНИЕ В процессе деятельности человека возникает необходимость хранения и обработки данных о различных объектах. Данные являются формой существования и представления информации и содержат некоторые сведения об объекте, полученные с помощью средств измерения, наблюдения, анализа, синтеза или какими-либо другими способами. Информация связана с энтропией и может быть оценена только в контексте взаимодействия объекта с другими объектами или процессами. Поэтому понятие «информация» не используется в учебном пособии. В отличие от информации данные являются объективной мерой свойств объекта, имеют размерность и могут быть сохранены и обработаны. Данные можно представить в упорядоченном взаимосвязанном виде, организованном в соответствии с определенной моделью данных. Тогда они образуют базу данных. Известно несколько моделей представления данных, однако самой распространенной является реляционная. Реляционная база данных основана на математическом аппарате реляционной алгебры, теории множеств и представляет данные в удобной для восприятия человеком форме связанных между собой таблиц. Обработка и хранение больших объемов данных производится на электронно-вычислительных машинах (компьютерах) с помощью систем управления базами данных. Система управления базой данных представляет собой программу с соответствующим языком для создания, администрирования базы данных в памяти компьютера и организации интерфейса пользователя. Освоение дисциплины «Компьютерные технологии» предполагает изучения студентами основ теории реляционных баз данных, методов проектирования и процесса создания реляционных баз данных на персональном компьютере в системе управления базами данных. Основы теории реляционных баз данных включают сведения об общих понятиях информации, данных, отношений на множествах, целостности данных, классификацию и модели баз данных. Целью проектирования реляционной базы данных является обеспечение возможности хранить все необходимые данные, исключить избыточность, аномалии обновления, добавления и удаления данных. Методика проектирования реляционных баз данных заключается в нормализации и декомпозиции отношения посредством функциональных зависимостей. Спроектированная реляционная база данных реализуется на персональном компьютере в системе управления базой данных Apache OpenOffice Base. Для закрепления полученных знаний студенты выполняют курсовую работу. Выполняя курсовую работу, студент должен глубже понять теоретические основы дисциплины и научиться проектировать реляционные базы данных. Курсовая работа представляет собой комплексное задание по проектированию реляционной базы данных, состоящее из двух разделов. Первый раздел содержит задание на нормализацию и декомпозицию отношения (таблицы) базы данных. Нормализация предполагает приведение базы данных к первой нормальной форме и нормальной форме Бойса-Кодда. Декомпозиция заключается в разбиении исходного отношения базы данных на несколько посредством концепции функциональных зависимостей. В итоге выполнения первого раздела исходное отношение базы данных, которое содержалось в задании к работе, приводится к нормальной форме Бойса-Кодда и представляется в виде нескольких отношений. Второй раздел носит практический характер и предполагает реализацию полученных отношений базы данных в нормальной форме Бойса-Кодда на персональном компьютере с помощью системы управления базами данных Apache OpenOffice Base. 3 1. РЕЛЯЦИОННЫЕ БАЗЫ ДАННЫХ 1.1. Проектирование реляционной базы данных В разделе рассматриваются вопросы проектирования реляционной базы данных (БД) для реализации на компьютере в системе управления базой данных (СУБД) с помощью методов нормализации и декомпозиции; изучение метода нормализации отношения БД в первую нормальную форму (1НФ) и нормальную форму Бойса-Кодда (НФБК); изучение метода декомпозиции исходного отношения БД на составляющие в соответствии с концепцией функциональных зависимостей (ФЗ). Общие понятия о базах данных Понятия «данные» и «информация» используются практически как синонимы, но между ними существует различие. Информация является понятием более высокого уровня и может быть определена с помощью анализа данных об измеряемой переменной с учетом вероятности наступления того или иного события. Фраза «данные несут информацию» может употребляться в разговорном языке, в отличие от фразы «информация несёт данные», которая является неправильной. Данные представляют совокупность значений параметров объектов, а информация – знания (сведения) об объектах, получаемые с помощью восприятия данных о них человеком. В связи с наличием большого числа данных об объектах возникает необходимость их хранения и обработки. Для этого данные должны быть формализованы (идеализированы) и иметь структуру. Дадим определения базы данных и модели данных. База данных (БД) – совокупность данных, которые организованы в соответствии с определенной моделью данных. Модель данных – теория представления и обработки данных, которая описывает структуру, методы манипулирования и поддержки целостности данных. Выделяют иерархическую, сетевую, реляционную и объектную модели данных. Первые БД были созданы на основе иерархической модели данных в виде древовидной структуры объектов различных 4 уровней. Иерархические БД в настоящее время не используются, но примером подобной организации данных служит файловая система операционной системы Windows (рис. 1.1а). Сетевые БД основаны на более строгой математической теории и являются расширением иерархического подхода. В сетевых БД, в отличие от иерархических, возможно, чтобы один объект относился к двум предкам (рис. 1.1б). Объектная БД представляет данные в виде объектов, их атрибутов, методов, классов и применяется для высокопроизводительной обработки данных, имеющих сложную структуру (рис. 1.1в). В чистом виде объектная модель, как правило, не используется, а является частью реляционной модели. Самыми распространенными БД являются реляционные, которые основаны на математическом понятии отношения и представлены в виде таблицы. а Счёт Объект Состояния Тип счёта Валюта Баланс Кредит Проверить Методы Снять Перевести в б Рис. 1.1. Структуры моделей данных: а – иерархическая; б – сетевая; в – объектная Реляционная модель данных Для понимания реляционной модели необходимо сопоставить представление основных понятий реляционной модели данных (отношение, кортеж, атрибут, значение атрибута) в табличной форме и форме СУБД на компьютере (табл. 1.1). Таблица 1.1 Термины реляционной модели данных в различных представлениях Реляционная модель данных Табличная форма Система управления базой данных Отношение Кортеж Атрибут (домен) Значение атрибута (домена) Таблица Строка Столбец Ячейка Файл Запись Поле Значение 5 Данные об объектах первоначально должны быть преобразованы в соответствующие домены. Доменом называют множество значений определенного типа данных, имеющих имя. Понятие домена идентично понятию массива в языках программирования высокого уровня. Домен, как и массив, может иметь только один тип данных. Например, домен с именем «Номер зачётной книжки» студента (рис. 1.2) относится к целым числам, а «Фамилия имя отчество» – к строке символов. Домены Номер зачётной книжки Фамилия имя отчество Пол Размер стипендии Номер текущего семестра Денежный Целые числа Типы данных Целые числа Строка символов Логический Атрибуты Название атрибута НомЗач Кортежи 4231 9532 1061 7394 ФИО Пол Иванов Иван Иванович Петров Петр Петрович Сидоров Сидор Сидорович Попова Любовь Петровна Отношение «Студент» Стип., руб. Семестр Мужской 1500,00 1 Мужской 2500,00 2 Мужской 1000,00 5 Женский 1500,00 2 Первичный ключ Рис. 1.2. Графическое представление основных понятий реляционной модели данных 6 Тип данных определяет диапазон допустимых значений, способ представления и набор операций над ними. Логический тип данных может принимать значения Истина (True) или Ложь (False), которые представлены в компьютере 1 или 0. Такой тип подходит для данных, принимающих одно или другое значение: пол (мужской или женский), семейное положение (холост/не замужем или женат/замужем), наличие стипендии (да или нет) и т. п. Целочисленный тип данных может содержать только целые числа, например номер текущего курса студента. Сравнивать значения или производить операции над ними возможно только в пределах одного домена. Не имеет смысла складывать номер зачётной книжки и номер текущего курса студента. Понятие отношения (relation) является ключевым в реляционной модели и основной структурой хранения данных. Отношение может быть представлено в виде таблицы и состоит из элементов: заголовок отношения, первичный ключ, тело отношения, атрибут, кортеж. Определения каждому элементу будут даны ниже. Математическое определение отношения: T есть отношение над множествами доменов D1 , D2 ,..., DN , если T – множество упорядоченных m-кортежей вида d1m , d2m , d Nm – элементы где доменов d1m , d2m ,..., d Nm , D1 , D2 ,..., DN соответственно. Отношение «Студент» (см. рис. 1.2) состоит из пяти доменов: «Номер зачётной книжки», «Фамилия имя отчество», «Пол», «Размер стипендии» и «Номер текущего семестра» – и четырех кортежей соответствующих каждому отдельному студенту:  4231; Иванов Иван Иванович; мужской; 1500,00 руб.; 1    9532; Петров Петр Петрович; мужской; 2500,00 руб.; 2    1061; Сидоров Сидор Сидорович; мужской; 1000,00 руб.; 5  .    7394; Попова Любовь Петровна; женский; 1500,00 руб.; 2    Число атрибутов (доменов) называется степенью отношения, а кортежей – мощностью. Заголовком (схемой) отношения (см. рис. 1.2) называется конечное множество упорядоченных пар вида A, D , где A – название домена, D – тип данных домена. 7 Заголовок, или схема, отношения «Студент» выглядит в виде множества упорядоченных пар НомЗач, Целые числа , ФИО, Строка символов , Пол, Логический , Стип, Денежный , Семестр, Целые числа . Кортеж – набор упорядоченных значений триплетов (троек) вида  A1 , D1 , d1m ; A2 , D2 , d 2m ;...; AN , DN , d Nm  , где A – название домена, D – тип данных домена, d1m , d2m , d Nm – элементы доменов D1 , D2 ,..., DN отношения T. Первый кортеж отношения «Студент» представлен в виде:  НомЗач, Целые числа, 4231    ФИО, Строка символов, Иванов Иван Иванович    Пол, Логический, мужской .    Стип., Денежный, 1500,00 руб.     Семестр, Целые числа, 1  Тело отношения T – множество m-кортежей этого отношения. Атрибут – набор упорядоченных значений триплетов A, D, d , где A – название домена, D – тип данных домена, d – элементы домена отношения T. Первый атрибут отношения «Студент» имеет вид:  НомЗач, Целые числа, 4231    НомЗач, Целые числа, 9532    НомЗач, Целые числа, 1061  .    НомЗач, Целые числа, 7394    Первичный ключ – атрибут, или набор атрибутов, однозначно идентифицирующий (определяющий) конкретный кортеж и не имеющий дополнительных атрибутов. Идентификация заключается в том, что первичный ключ является индексом для кортежа, значения которого уникальны и никогда не повторяются. Первичный ключ не должен иметь дополнительных или лишних атрибутов, т. е. являться минимальным 8 набором атрибутов для идентификации кортежа. Поэтому, если произвольный атрибут исключить из первичного ключа, оставшихся атрибутов станет недостаточно для однозначной идентификации кортежа, т. е. значения первичного ключа перестанут быть уникальными и станут повторяться. Для отношения «Студент» атрибут «Номер зачётной книжки» является первичным ключом и однозначно определяет данные по каждому отдельному студенту: фамилия имя отчество, пол, размер стипендии и номер текущего курса. Это значит, что в университете не может быть двух студентов с одинаковым номером зачётной книжки. Проектирование реляционной БД Проектируемая БД должна отвечать следующим условиям: 1. База данных должна хранить требуемые данные. Поэтому в БД должно быть точно определено число атрибутов, отношений, и типов данных (п. 1.1.1). Например, ограничив тип данных «Строка символов» атрибута «ФИО» отношения «Студент» (см. рис. 1.2) длиной в 25 символов, невозможно сохранить в БД студента с ФИО «Алексей Алексеевич Иванов-Петров» длиной в 30 символов. 2. В БД необходимо исключить избыточные данные, удаление которых минимизирует объём отношения и не приведет к потере или ошибке в кортежах. Повторяющиеся данные могут быть дублированными или избыточными. В отношении «Студент-Группа» (рис. 1.3а) при удалении дублированных значений о группах студентов произойдет потеря данных (рис. 1.3б), поэтому в БД допускается дублирование данных Отношение «Студент-Группа» НомЗач 4231 9532 1061 7394 Отношение «Студент-Группа» Группа ЭГП-15 ЭГП-12 ЭГП-15 ЭГП-12 НомЗач 4231 9532 1061 7394 а Группа ЭГП-15 ЭГП-12 б Рис. 1.3. Дублирование данных Избыточные данные возникают, если добавить в отношение «Студент-Группа» (см. рис. 1.3) атрибут «Староста» (рис. 1.4а). При 9 удалении избыточных значений (рис. 1.4б) остается возможность узнать данные о старостах групп ЭГП-15 и ЭГП-12. Недостатком способа исключения избыточных данных удалением являются неопределенности результатов запросов и аномалии. Результат запроса старосты группы ЭГП-15 содержит два значения «Сидоров С. С.» и « – », поэтому он становится неопределённым. Аномалия удаления данных проявляется при исключении студента с номером зачётной книжки 4231 и приводит к потере данных о старосте группы ЭГП-15. Отношение «Студент-Группа» НомЗач 4231 9532 1061 7394 Группа ЭГП-15 ЭГП-12 ЭГП-15 ЭГП-12 а Отношение «Студент-Группа» Староста Сидоров С.С. Петров П.П. Сидоров С.С. Петров П.П. НомЗач 4231 9532 1061 7394 Группа ЭГП-15 ЭГП-12 ЭГП-15 ЭГП-12 б Староста Сидоров С.С. Петров П.П. – – Рис. 1.4. Избыточность данных Исключить неопределенность и аномалии возможно с помощью декомпозиции, т. е. разбиения исходного отношения «СтудентГруппа» (см. рис. 1.4а) на два отношения «Студент-Группа» и «Группа-Староста» (рис. 1.5). Отношение «Студент-Группа» НомЗач 4231 9532 1061 7394 Отношение «Группа-Староста» Группа ЭГП-15 ЭГП-12 ЭГП-15 ЭГП-12 Группа ЭГП-15 ЭГП-12 а Староста Сидоров С.С. Петров П.П. б Рис. 1.5. Исключение избыточности данных 3. База данных должна содержать минимальное число отношений. В процессе исключения избыточности данных происходит разбиение исходного отношения на несколько, что неудобно для пользователя и замедляет работу с БД. Поэтому нельзя допускать необоснованного увеличения числа отношений БД. 10 4. Необходимо исключить из БД аномалии выборки, обновления, добавления и удаления данных. При поиске студентов с несданными экзаменами в таблице 1.4 «Успеваемость студентов» результат выборки содержит первокурсника, который еще не сдавал экзамены. Добавление, обновление и удаление данных об оценках студентов связано с поиском позиции в ячейке таблицы, что усложняет алгоритмы работы СУБД и приводит к ошибкам. Исключение аномалий достигается за счет нормализации и декомпозиции. Нормализация – это процесс приведения отношения к одной из нормальных форм. Существует восемь нормальных форм: первая нормальная форма (1НФ), вторая нормальная форма (2НФ), третья нормальная форма (3НФ), нормальная форма Бойса-Кодда (НФБК), четвёртая нормальная форма (4НФ), пятая нормальная форма (5НФ), доменно-ключевая нормальная форма (ДКНФ) и шестая нормальная форма (6НФ). С увеличением значения номера нормальной формы повышается строгость требований к отношению. В процессе нормализации неизбежна декомпозиция, т. е. исходное отношение разбивается на несколько в соответствии с требованиями нормальной формы. В реляционной модели данных отношение по определению должно находиться в первой нормальной форме. Поэтому на первом этапе нормализации таблицу приводят к 1НФ (п. 1.1.2). Далее отношение подвергается декомпозиции и приводится к НФБК (п. 1.1.4), в которой БД соответствует всем условиям проектирования. Требования к БД содержать минимальное число отношений и исключить аномалии противоречат друг другу. Приведение отношения к НФБК позволяет достигнуть компромисса и выполнить эти требования. Поэтому приведение отношения к нормальным формам более высоких порядков не требуется. 1.1.1. Определение типов данных Первоначальный этап проектирования реляционной БД начинают с составления таблицы типов данных (табл. 1.2). Типы данных атрибутов отношения выбираются в соответствии с типами данных СУБД OpenOffice Base (табл. 1.3). 11 Таблица 1.2 Типы данных отношения «Студент» Название столбца Номер зачётной книжки Фамилия имя отчество Имя атрибута Тип данных Целое [INTEGER] ФИО Текст [VARCHAR] Пол Логическое [BOOLEAN] 14 НомЗач Пол Размер стипендии Номер текущего семестра Стип Десятичное [DECIMAL] Семестр Короткое целое [TINYINT] Описание Целочисленное значение номера зачётной книжки, уникальное для каждого студента университета Фамилия имя отчество студента университета. Возможны совпадения ФИО среди студентов Пол студента имеет значение мужской или женский Размер академической стипендии студента университета. Студент может не получать академическую стипендию, но получать другие виды стипендии (социальную) Номер текущего семестра обучения студента в данной группе университета Обязательное Уникальное Фиксированное (список значений) Пример значения Да Да Нет 3215 Да Нет Нет Иванов Иван Иванович Да Нет Да (Мужской/ Женский) Мужской Нет Нет Да (1500,00; 1800,00; 2100,00 рублей) 2 100,00 рублей Да Нет Да (1,2,3,4,5) 4 Таблица 1.3 Типы данных СУБД OpenOffice Base Название типа данных Допустимые значения Текст [VARCHAR] Строка символов размером до 256 знаков (при записи строки в поле меньше заданного размера значение ячейки сжимается и достигается экономия памяти) Текст [VARCHAR IGNORECASE] Текст (фикс.) [CHAR] ФИО Адрес Группа Пример значений Иванов Иван Иванович Екатеринбург, 8 Марта, 82, 111 ЭЭТ-15 Строка символов, у которой строчные и прописные буквы не различаются Модель бытовой техники ABC11100 Строка символов с возможностью задания количества символов Группа ЭЭТ-15 Памятка [LONGVARCHAR] Большой объём текста, эквивалентный типу MEMO Логическое [BOOLEAN] Данные логического типа (принимает значения да/нет) Дата [DATE] Календарная дата Время [TIME] Картинка [LONGVARBINARY] Короткое целое [TINYINT] Короткое целое [SMALLINT] Пример атрибутов Время Иванов И. И. родился в Характеристиг. Екатеринка студента бурге, учился в школе №9 Пол Мужской Отличник Да Семейное Холост положение Дата 01.01.1990 рождения Время приёма 12:00 к врачу Большой двоичный объект переменного размера (картинка, Фото студента звукозапись, документ) Семестр Целое число от 0 до 255 Количество детей Целое число от -32768 до 32767 13 Температура воздуха 4 2 -30 Окончание табл. 1.3 Название типа данных Целое [INTEGER] Длинное целое [BIGINT] Десятичное [DECIMAL] С плавающей точкой [FLOAT] Двоичное [VARBINARY] Допустимые значения Целое число от -2147483648 до 2147483647 Целое увеличенной разрядности от -9223372036854775808 до 9223372036854775807 Число с плавающей запятой с возможностью указать количество знаков дробной части. Для создания денежного типа указывают денежный формат данных Число с плавающей запятой. Для создания денежного типа указывают денежный формат данных Двоичный объект с переменным размером с возможностью экономии памяти, если записи имеют различный размер Двоичное (фикс.) [BINARY] Двоичный объект фиксированного размера Число [NUMERIC] Натуральное число (диапазон чисел определяется разрядностью системы) Вещественное [REAL] Вещёственное число (32разрядные мантисса и порядок) С двойной точностью [DOUBLE] Вещёственное число двойной точности (32-разрядные мантисса и порядок) Дата/время [TIMESTAMP] Другое [OTHER] Число миллисекунд, прошедших с начала эры UNIX 12:00 31 декабря 1969 года Данные любого другого (неуказанного выше) типа 14 Пример атрибутов Пример значений Номер пропуска 1234567 Порядковый номер записи 22337203685 Заработная плата 21 000,00 рублей Цена 1 000,00 рублей Код элемента 10101110 Код товара 1101 Номер сотового телефона Масса элементарной частицы Масса планеты 8912345678 123456789112 345678921234 ,12345678 123456789112 345678921234 564789312345 678941234567 89512,123456 78911 Дата и время вылета самолёта 1453055471 - - Таблица типов данных (см. табл. 1.2) составлена для отношения «Студент» (см. рис. 1.2) и содержит: название столбца – название столбца таблицы задания; имя атрибута – сокращение названия столбца таблицы варианта задания, которое будет использоваться в БД; тип данных – соответствующий примерам значений задания тип данных атрибута, выбранный из табл. 1.3; описание – текстовое описание атрибута, раскрывающее его суть; обязательное – значение атрибута, которое обязательно должно быть указано в БД; уникальное – значение, которое может быть идентификатором; фиксированное (список значений) – значения атрибута, которые могут быть описаны постоянным списком; пример значения – одно значение атрибута из БД. Таким образом, таблица типов данных соотносит представление данных в атрибутах отношения и в СУБД Base на персональном компьютере. Рациональное использование разнообразия типов данных Base способствует экономии объёма жёстких дисков серверов БД и повышает быстродействие СУБД. Тип данных пола студента (см. табл. 1.2) можно выбрать текстовым, где буква кодируется одним байтом. Тогда для кодировки слова «мужской» потребуется семь байт или 56 бит. Если обратить внимание, что пол студента имеет только два возможных значения «мужской» или «женский», то уместен логический тип данных, который принимает значения 0 или 1. Поэтому использование логического типа экономит 55 бит объёма жестких дисков БД на одном значении атрибута. Контрольные вопросы и задания 1. Укажите принципиальные различия между информацией и данными. В БД хранятся данные или информация? 2. Дайте определение понятиям «база данных» и «модель данных». Перечислите существующие модели данных БД, их основные особенности и примеры. 3. Сравните понятия «данные» и «домен». Каким образом преобразовать данные об объекте в соответствующие домены? 15 4. Укажите соответствие терминам табличной формы представления (таблица, строка, столбец, ячейка) в реляционной модели данных (отношение, кортеж, атрибут, значение атрибута) и СУБД (файл, запись, поле, значение). 5. Дайте определение основным понятиям реляционной модели данных: «отношение», «тело отношения», «заголовок отношения», «кортеж», «атрибут отношения», «первичный ключ». 6. Для чего необходим первичный ключ отношения? 7. Перечислите основные условия, которым должна отвечать БД при проектировании. 8. Укажите основное отличие дублирования и избыточности данных. Для чего необходимо бороться с избыточностью данных? Как снизить избыточность данных в отношении? 1.1.2. Первая нормальная форма После составления таблицы типов данных (см. табл. 1.2) следующим этапом проектирования БД является нормализация. Нормализация заключается в приведении отношения к одной из нормальных форм с целью снижения избыточности и аномалий обновления, удаления и добавления данных. Сначала исходную таблицу приводят к 1НФ, т. е. к виду отношения. Таблица может считаться отношением в 1НФ, если: 1. Строки не повторяются – отношение является множеством кортежей, соответственно, математическое определение множества предполагает, что его элементы различны. Данное требование формально выполняется за счёт наличия первичного ключа, значения которого уникальны. 2. Порядок строк не имеет значения – поддержание упорядоченности строк постоянно обновляемой БД требует дополнительных технических ресурсов СУБД. 3. Порядок столбцов не имеет значения – упорядоченность атрибутов отношения связана с увеличением занимаемого объёма, времени доступа и обновления данных в БД. 4. Отсутствуют скрытые идентификаторы строк – для идентификации кортежа достаточно только первичного ключа. 16 5. Значения ячеек атомарные (неделимые) – это такие значения ячеек, деление которых на части приводит к потере ими смысла. Таблица 1.4 «Успеваемость студентов» соответствует требованиям пунктов 1-4, т. е. не содержит повторяющихся строк, порядок строк и столбцов не имеет значения, отсутствуют скрытые идентификаторы. Значения ячеек атрибутов «Дисциплина», «Семестр», «Дата сдачи» и «Оценка» являются множественными или не атомарными, т. е. содержат информацию об успеваемости студента по нескольким дисциплинам. Таблица 1.4 Успеваемость студентов Номер зачётной книжки 4231 9532 1061 7394 Дисциплина Семестр Дата сдачи Оценка Электропривод Автоматизация Электрификация Электропривод Электропривод Электрификация Автоматизация Информатика Философия Математика Информатика 5 6 5 6 5 5 6 2 2 1 1 11.01.2015 15.07.2015 15.01.2015 21.07.2015 11.01.2015 16.01.2015 21.07.2015 21.07.2015 26.07.2015 23.01.2015 - 4 5 5 5 4 5 5 4 5 4 - Требование атомарности значений отношения выполняется за счёт процесса разбиения множественных значений на атомарные составляющие и дублирования номеров зачётных книжек (табл. 1.5). Первичным ключом отношения в 1НФ будет считаться минимальный набор атрибутов «Номер зачётной книжки», «Дисциплина», «Семестр», «Дата сдачи», который однозначно определяет оценку. Номер зачётной книжки однозначно определяет студента. Дисциплина определяется названием и семестром, в течение которого она изучалась, и датой сдачи экзамена. Данный набор атрибутов является минимальным для однозначной идентификации кортежа, т. е. при удалении какого-либо атрибута становится невозможным однозначная идентификация кортежа. 17 Таблица 1.5 Первая нормальная форма отношения «Успеваемость студентов» Номер зачётной книжки Дисциплина Семестр Дата сдачи Оценка 4231 4231 4231 4231 9532 9532 9532 1061 1061 1061 7394 Электропривод Автоматизация Электрификация Электропривод Электропривод Электрификация Автоматизация Информатика Философия Математика Информатика 5 6 5 6 5 5 6 2 2 1 1 11.01.2015 15.07.2015 15.01.2015 21.07.2015 11.01.2015 16.01.2015 21.07.2015 21.07.2015 26.07.2015 23.01.2015 - 4 5 5 5 4 5 5 4 5 4 - В итоге требования 1НФ выполнены для отношения «Успеваемость студентов» (см. табл. 1.5). Далее отношения в 1НФ приводят к более высоким нормальным формам. Контрольные вопросы и задания 1. Дайте определение понятию «нормализация». 2. Перечислите требования к отношению в 1НФ. 3. Поясните, что означает атомарность значений атрибутов отношения. 4. Является ли таблица отношением, если она не находится в 1НФ? 1.1.3. Функциональные зависимости Функциональная зависимость (ФЗ) определяется следующим образом. Если даны два атрибута А и В, то В функционально зависит от А, если для каждого значения А существует ровно одно связанное с ним значение В в любой момент времени. Атрибут А называют детерминантом, а В – зависимым атрибутом. 18 Детерминант и зависимый атрибут могут быть составными и являться группой из двух и более атрибутов. Математическая форма записи ФЗ с одиночным A  B; и составным детерминантом: A  D  E  B, C, F , G. Графическая форма записи (диаграмма) ФЗ с одиночным В А и составным детерминантом: А+D+E В, C, F, G Декомпозиция исходного отношения уменьшает вероятность аномалий добавления, удаления и обновления данных. Функциональные зависимости отражают логическую структуру отношения и являются основой для его декомпозиции. Для определенного значения детерминанта существует только одно связанное с ним значение зависимого атрибута. Детерминантом может быть атрибут или набор атрибутов, значения которого уникальны. Например, в университете номер зачётной книжки студента уникален, поэтому с конкретным номером существует только один студент. В отношении «Университет» (табл. 1.6) номер зачётной книжки студента определяет его персональные данные: ФИО и телефон. Шифр дисциплины уникален и определяет название дисциплины. Оценку, полученную на экзамене студентом, определяет составной детерминант Номер зачётной книжки + Шифр дисциплины + Семестр + Дата сдачи, т. е. студент изучает дисциплину в течение семестра, сдает определённое число экзаменов и получает оценку. 19 Таблица 1.6 Отношение «Университет» Номер зачётной книжки 4231 4231 4231 4231 22 9532 9532 9532 1061 1061 1061 7394 ФИО Иванов Иван Иванович Иванов Иван Иванович Иванов Иван Иванович Иванов Иван Иванович Петров Петр Петрович Петров Петр Петрович Петров Петр Петрович Сидоров Сидор Сидорович Сидоров Сидор Сидорович Сидоров Сидор Сидорович Попова Любовь Петровна Телефон Дисциплина Шифр дисциплины 8900123456 Электропривод ЭП.5.1 5 11.01.2015 4 8900123456 Автоматизация АВТ.6.1 6 15.07.2015 5 5 15.01.2015 5 8900123456 Электрификация ЭЛКТ.5.1 Семестр Дата сдачи Оценка 8900123456 Электропривод ЭП.5.1 6 21.07.2015 5 8911654321 Электропривод ЭП.5.1 5 11.01.2015 4 5 16.01.2015 5 8911654321 Электрификация ЭЛКТ.5.1 8911654321 Автоматизация АВТ.6.1 6 21.07.2015 5 8922223344 Информатика ИНФ.1.2 2 21.07.2015 4 8922223344 Философия ФИЛ.2.3 2 26.07.2015 5 8922223344 Математика МТ.1.1 1 23.01.2015 4 8933445321 Информатика ИНФ.1.2 1 - - Этапы составления ФЗ 1. Из таблицы типов данных (см. табл. 1.3) выбирают атрибуты, значения которых уникальны (Номер зачётной книжки, Шифр дисциплины) и составляют с ними ФЗ: Номер зачётной книжки  ФИО, Телефон; Шифр дисциплины  Дисциплина. 2. Определяют комбинацию атрибутов составного детерминанта: Номер зачётной книжки + Шифр дисциплины + Семестр + Дата сдачи  Оценка. В итоге для отношения «Университет» составлены три ФЗ: 1) Номер зачётной книжки  ФИО, Телефон; 2) Шифр дисциплины  Дисциплина; 3) Номер зачётной книжки + Шифр дисциплины + Семестр + Дата сдачи  Оценка. Контрольные вопросы и задания 1. Дайте определение понятиям «функциональная зависимость», «детерминант» и «зависимый атрибут». 2. Какая основная особенность атрибута (совокупности атрибутов) делает его детерминантом? 3. Объясните, для чего необходимо составлять функциональные зависимости. 4. Каково назначение декомпозиции отношения? 5. Может ли быть детерминантом атрибут, если его значения повторяются? 6. Приведите примеры атрибутов, которые могут выступать детерминантами отношений. 7. В чём отличие одиночного от составного детерминанта? 1.1.4. Нормальная форма Бойса-Кодда Основоположник реляционных БД Эдгар Кодд доказал, что в отношении, находящемся в НФБК, отсутствуют аномалии добавления, обновления и удаления данных [2]. Определения НФБК, возможного и первичного ключа приведены ниже. Отношение находится в нормальной форме Бойса-Кодда, если каждый детерминант отношения является возможным ключом. Возможный ключ – атрибут или набор атрибутов, который может быть использован в качестве первичного ключа. Первичный ключ – атрибут или набор атрибутов, который используется для однозначной идентификации кортежа. В отношении, не находящемся в НФБК, может содержаться несколько детерминантов. В отношении «Университет» (табл. 1.7) содержится три детерминанта: Номер зачётной книжки, Шифр дисциплины, Номер зачётной книжки + Шифр дисциплины + Семестр + Дата сдачи. Детерминант становится возможным ключом, если его значения уникальны. Значения составного детерминанта Номер зачётной книжки + Шифр дисциплины + Семестр + Дата сдачи для отношения «Университет» уникальны, поэтому он является возможным ключом. Возможных ключей в отношении может быть несколько. Первичный ключ единственный в отношении, выбирается из возможных и используется для идентификации кортежей. Таблица 1.7 Возможные ключи и детерминанты отношения «Университет» Возможные ключи Детерминанты 1) Номер зачётной книжки Номер зачётной книжки + Шифр дисциплины + Семестр + Дата сдачи 2) Шифр дисциплины 3) Номер зачётной книжки + Шифр дисциплины + Семестр + Дата сдачи Последовательность приведения отношения в НФБК 1. Проверить, находится ли отношение в НФБК – составить таблицу возможных ключей и детерминантов (см. табл. 1.7). Отношение находится в НФБК, если все его детерминанты являются возможными ключами. Отношение «Университет» (см. табл. 1.6) не находится в НФБК, так как содержит три детерминанта и только один из них возможный ключ. 2. Выполнить декомпозицию отношения без потерь в соответствии с ФЗ. Декомпозиция без потерь обратима, т. е. возможно составить исходное отношение из декомпозированных без потери данных. Проведём декомпозицию без потерь для отношения «Университет» (см. табл. 1.6) на три отношения по количеству ФЗ: 1) Номер зачётной книжки  ФИО, Телефон (табл. 1.8); 2) Шифр дисциплины  Дисциплина (табл. 1.9); 3) Номер зачётной книжки + Шифр дисциплины + Семестр + Дата сдачи  Оценка (табл. 1.10). Таблица 1.8 Отношение «Студенты» Номер зачётной книжки ФИО Телефон 4231 9532 1061 7394 Иванов Иван Иванович Петров Петр Петрович Сидоров Сидор Сидорович Попова Любовь Петровна 8900123456 8911654321 8922223344 8933445321 Таблица 1.9 Отношение «Дисциплины» Шифр дисциплины Дисциплина ЭП.5.1 АВТ.6.1 ЭЛКТ.5.1 ФИЛ.2.3 МТ.1.1 ИНФ.1.2 Электропривод Автоматизация Электрификация Философия Математика Информатика Таблица 1.10 Отношение «Успеваемость студентов» Номер зачётной книжки Шифр дисциплины Семестр Дата сдачи Оценка 4231 4231 4231 4231 9532 9532 9532 1061 1061 1061 ЭП.5.1 АВТ.6.1 ЭЛКТ.5.1 ЭП.5.1 ЭП.5.1 ЭЛКТ.5.1 АВТ.6.1 ИНФ.1.2 ФИЛ.2.3 МТ.1.1 5 6 5 6 5 5 6 2 2 1 11.01.2015 15.07.2015 15.01.2015 21.07.2015 11.01.2015 16.01.2015 21.07.2015 21.07.2015 26.07.2015 23.01.2015 4 5 5 5 4 5 5 4 5 4 3. Проверить, находятся ли полученные в результате декомпозиции отношения в НФБК. Для этого необходимо составить таблицу возможных ключей и детерминантов (табл. 1.11). Отношение находится в НФБК, если все его детерминанты являются возможными ключами. В результате декомпозиции отношения «Университет», полученные отношения «Студенты», «Дисциплины», «Успеваемость студентов», исходя из таблицы 1.11, находятся в НФБК, так как каждый содержит один детерминант, который является возможным ключом. Таблица 1.11 Возможные ключи и детерминанты декомпозированных отношений Возможные ключи Детерминанты Отношение «Студенты» Номер зачётной книжки Номер зачётной книжки Отношение «Дисциплины» Шифр дисциплины Шифр дисциплины Отношение «Успеваемость студентов» Номер зачётной книжки + Шифр дисциплины + Семестр + Дата сдачи Номер зачётной книжки + Шифр дисциплины + Семестр + Дата сдачи Проектирование БД заканчивается приведением ее к НФБК. Далее БД в НФБК возможно создать на компьютере в СУБД без возникновения существенных аномалий. В отношении «Успеваемость студентов» (см. табл. 1.10) отсутствует «пустой» кортеж с данными об изучении информатики Поповой Любовью Петровной, так как экзамен по данной дисциплине она ещё не сдавала. Размер исходного отношения «Университет» составлял 88 ячеек с 19 избыточными, тогда как после декомпозиции и представления в НФБК суммарный размер трёх отношений составляет 74 ячейки. В итоге из 19 избыточных ячеек в 1НФ в процессе приведения в НФБК исключены 14. Контрольные вопросы и задания 1. Дайте определение нормальной форме Бойса-Кодда. 2. Объясните, в чём разница между детерминантом, возможным и первичным ключом отношения? 3. Каким образом детерминант становится возможным ключом отношения? 4. Как сделать возможный ключ отношения первичным? 5. Сколько в отношении может быть детерминантов, возможных и первичных ключей? 6. Дайте определение понятию декомпозиции отношения без потерь.