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

Министерство образования и культуры РФ Государственное образовательное учреждение высшего профессионального образования Уссурийский государственный педагогический институт Кафедра информатики и вычислительной техники Специальность 50 202 информатика с доп. специальностью 50 203 физика ДИПЛОМНАЯ РАБОТА Проектирование баз данных методом нормализации Крюков Роман Сергеевич Руководитель — ст. преподаватель Кляченко И.Г.

Уссурийск 2011

• Оглавление
• Введение
• Глава 1. Проектирование базы данных
• Основные понятия баз данных
• Архитектура базы данных
• Проектирование базы данных
• Глава 2. Нормализация
• Принципы нормализации
• Теорема Фейджина
• Глава 3. Создание структуры БД «Классный журнал» методом нормализации
• Описание предметной области
• Создание структуры БД «Классный журнал» методом нормализации
• Создание приложения для работы с базой данных TTable и TQuery
• Заключение
• Список литературы
• Приложение

Управление информацией всегда было основной сферой применения компьютеров и, надо думать, будет играть еще большую роль в будущем. Базы данных и системы управления ими (СУБД, DBMS — Database Management System) на протяжении всего пути развития компьютерной техники совершенствовались, поддерживая все более сложные уровни абстрактных данных, заданных пользователем, и обеспечивая взаимодействие компонентов, распределенных в глобальных сетях и постепенно интегрирующихся с телекоммуникационными системами.

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

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

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

Цель моей дипломной работы: создание структуры базы данных на примере «Школьного журнала» с использованием метода нормализации.

Задачи:

· Изучение литературы по теме дипломной работы

· Изучение принципов нормализации

· Изучение предметной области БД

· Создание концептуальной модели

· Создание приложения для работы с БД

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

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

Во второй главе рассматривается понятие нормализации, а так же ее принципы.

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

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

Основные понятия Баз данных

Развития вычислительной техники осуществлялось по двум основным направлениям:

· применение вычислительной техники для выполнения численных расчетов;

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

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

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

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

Существуют 4 основные модели данных — списки (плоские таблицы), реляционные базы данных, иерархические и сетевые структуры.

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

Архитектура Базы Данных

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

В составе BDE поставляются стандартные драйверы, обеспечивающие доступ к СУБД Paradox, dBASE, FoxPro и текстовым файлам. Локальные драйверы (рис.1) устанавливаются автоматически совместно с ядром процессора. Один из них можно выбрать в качестве стандартного драйвера, который имеет дополнительные настройки, влияющие на функционирование процессора БД.

Рис 1

Основная работа с BDE производится посредством внешнего интерфейса IDAPI (IDAPI32.DLL). Формат данных выбирается в псевдониме (alias) соединения, и в принципе дальше работа с разными форматами ничем не отличается. В том числе и неважно, как работает приложение с BDE — через компоненты VCL DB, которые используют функции BDE, или напрямую (все равно компоненты используют те же функции BDE).

Дальше функции IDAPI транслируют вызовы в функции соответствующего драйвера. Если это драйвер локального формата (dBase, Paradox, FoxPro), то драйвер формата сам работает с соответствующими файлами (таблицами и индексами). Если это SQL Link, то вызовы транслируются в вызовы функций API клиентской части конкретного SQL-сервера. Для каждого сервера SQL Link свой. IDAPTOR (соединитель с ODBC) и интерфейс к DAO работает точно также как и SQL Link, т. е. просто транслирует вызовы BDE в вызовы ODBC или DAO, непосредственно к формату не имея никакого отношения.

Если посмотреть на файлы BDE, то можно подробно рассмотреть его составные части.

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

· Система обработки запросов обеспечивает выполнение запросов SQL или QBE от приложения к любым базам данных, для которых установлен драйвер, даже если сама СУБД не поддерживает прямое использование запросов SQL.

· Система сортировки является запатентованной технологией и обеспечивает очень быстрый поиск по запросам SQL и через стандартные драйверы аля Paradox и dBASE.

· Система пакетной обработки представляет собой механизм преобразования данных из одного формата в другой при выполнении операций над целыми таблицами. Эта система использована в качестве основы для компонента TBatcMove и утилиты DataPump (автоматического переноса структур данных между базами данных), входящей в стандартную поставку BDE.

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

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

· Транслятор данных обеспечивает преобразование форматов данных для различных типов БД.

· Кэш BLOB используется для ускорения работы с данными в формате BLOB.

· SQL-генератор транслирует запросы в формате QBE в запросы SQL.

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

· Система поддержки драйверов SQL повышает эффективность механизма поиска при выполнении запросов SQL.

· Таблицы в памяти. Этот механизм позволяет создавать таблицы непосредственно в оперативной памяти. Используется для ускорения обработки больших массивов данных, сортировки, преобразования форматов данных.

· Связанные курсоры обеспечивают низкоуровневое выполнение межтабличных соединений. Позволяют разработчику не задумываться над реализацией подобных связей при работе на уровне VCL — для этого достаточно установить значения нескольких свойств.

· Менеджер конфигурации обеспечивает разработчику доступ к информации о конфигурации драйверов.

· Таким образом, при установке BDE «лишние» файлы можно без проблем выкинуть.

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

Отдельное место в архитектуре BDE и среди упомянутых файлов занимают Local SQL и QBE Engine. Эти механизмы запросов будут рассмотрены чуть дальше.

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

При разработке БД можно выделить следующие этапы работы:

I этап

Постановка задачи:

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

II этап

Анализ объекта:

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

III этап

Синтез модели:

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

IV этап

Выбор способов представления информации и программного инструментария: После создания модели необходимо, в зависимости от выбранного программного продукта, определить форму представления информации. В большинстве СУБД данные можно хранить в двух видах:

· с использованием форм

· без использования форм

Форма — это созданный пользователем графический интерфейс для ввода данных в базу.

V этап

Синтез компьютерной модели объекта:

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

Стадия 1 Запуск СУБД, создание нового файла базы данных или открытие созданной ранее базы

Стадия 2 Создание исходной таблицы или таблиц

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

При проектировании таблиц, рекомендуется руководствоваться следующими основными принципами:

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

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

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

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

Стадия 3 Создание экранных форм

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

Стадия 4 Заполнение БД

Процесс заполнения БД может проводиться в двух видах: в виде таблицы и в виде формы. Числовые и текстовые поля можно заполнять в виде таблицы, а поля типа МЕМО и OLE — в виде формы.

VI этап

Работа с созданной базой данных:

Работа с БД включает в себя следующие действия:

· поиск необходимых сведений;

· сортировка данных;

· отбор данных;

· вывод на печать;

· изменение и дополнение данных.

Формализация реляционной модели

Основные определения:

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

Теоретической основой этой модели стала теория отношений, основу которой заложили два логика — американец Чарльз Содерс Пирс (1839−1914) и немец Эрнст Шредер (1841−1902). В руководствах по теории отношений было показано, что множество отношений замкнуто относительно некоторых специальных операций, то есть образует вместе с этими операциями абстрактную алгебру. Это важнейшее свойство отношений было использовано в реляционной модели для разработки языка манипулирования данными, связанного с исходной алгеброй. Американский математик Э. Ф. Кодд в 1970 году впервые сформулировал основные понятия и ограничения реляционной модели, ограничив набор операций в ней семью основными и одной дополнительной операцией. Предложения Кодда были настолько эффективны для систем баз данных, что за эту модель он был удостоен престижной премии Тьюринга в области теоретических основ вычислительной техники.

Основной структурой данных в модели является отношение, именно поэтому модель получила название реляционной (от английского relation — отношение).

N-арным отношением R называют, подмножество декартова произведения Dx, D2x … Dnx множеств D1, D2, …, Dn (n > 1), необязательно различных. Исходные множества D1, D2, …, Dn называют в модели доменами.

R

D1x, D2x… Dxn

где D1, D2x … Dnx — полное декартово произведение.

Полное декартово произведение — это набор всевозможных сочетаний из n элементов каждое, где каждый элемент берется из своего домена. Например, имеем три домена: D1 содержит три фамилии, D2 — набор из двух учебных дисциплин и D3 — набор из трех оценок. Допустим, содержимое доменов следующее:

D1 = (Иванов, Крылов, Степанов)

D2 = (Теория алгоритмов, Базы данных}

D3 = (3, 4, 5)

Тогда полное декартово произведение содержит набор из 18 троек, где первый элемент — это одна из фамилий, второй — это название одной из учебных дисциплин, а третий — одна из оценок.

<�Иванов. Теория алгоритмов. 3>: <�Иванов. Теория алгоритмов. 4>: <�Иванов. Теория алгоритмов. 5>; <�Крылов. Теория алгоритмов. 3>: <�Крылов. Теория алгоритмов. 4>: <�Крылов. Теория алгоритмов. 5>; <�Степанов. Теория алгоритмов. 3>: <�Степанов. Теория алгоритмов. 4>: <�Степанов. Теория алгоритмов. 5>; <�Иванов, Базы данных. 3>: <�Иванов. Базы данных. 4>: <�Иванов. Базы данных. 5>; <�Крылов. Базы данных. 3>: <�Крылов. Базы данных. 4>: <�Крылов. Базы данных. 5>; <�Степанов. Базы данных. 3>: <�Степанов, Базы данных. 4>: <�Степанов, Базы данных. 5>;

Отношение R моделирует реальную ситуацию, и оно может содержать, допустим, только 5 строк, которые соответствуют результатам сессии (Крылов экзамен по «Базам данных» еще не сдавал):

<�Иванов. Теория алгоритмов. 3>; <�Крылов. Теория алгоритмов. 4>; <�Степанов. Теория алгоритмов. 5>; <�Иванов. Базы данных. 3>; <�Степанов. Базы данных. 4>;

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

Данная таблица обладает рядом специфических свойств:

· В таблице нет двух одинаковых строк.

· Таблица имеет столбцы, соответствующие атрибутам отношения.

· Каждый атрибут в отношении имеет уникальное имя.

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

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

· Количество атрибутов в отношении называется степенью, или рангом, отношения.

Следует заметить, что в отношении не может быть одинаковых кортежей, это следует из математической модели: отношение — это подмножество декартова произведения, а в декартовом произведении все n-ки различны. В соответствии со свойствами отношений два отношения, отличающиеся только порядком строк или порядком столбцов, будут интерпретироваться в рамках реляционной модели как одинаковые, то есть отношение R и отношение R1, изображенное далее, одинаковы с точки зрения реляционной модели данных.

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

Схемой отношения R называется перечень имен атрибутов данного отношения с указанием домена, к которому они относятся:

SR = (А1, А2, Аn) Аi

Di

Если атрибуты принимают значения из одного и того же домена, то они называются Q-сравнимыми, где Q — множество допустимых операций сравнения, заданных для данного домена. Например, если домен содержит числовые данные, то для него допустимы все операции сравнения, тогда Q = {=, <>,>=,<-,<,>}. Однако и для доменов, содержащих символьные данные, могут быть заданы не только операции сравнения по равенству и неравенству значений. Если для данного домена задано лексикографическое упорядочение, то он имеет также полный спектр операций сравнения.

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

SR1 = (A1, A2, …, An) — схема отношения R1.

SR2 = (Bi1, Bi2,…, Bin) — схема отношения R2 после упорядочения имен атрибутов.

Тогда:

sR1~sR2<=>1. n=m, или 2. Аj, Bij

Dj

Как уже говорилось ранее, реляционная модель представляет базу данных в виде множества взаимосвязанных отношений. В отличие от теоретико-графовых моделей в реляционной модели связи между отношениями поддерживаются неявным образом. Какие же связи между отношениями поддерживаются в реляционной модели? В этой модели, так же как и в остальных, поддерживаются иерархические связи между отношениями. В каждой связи одно отношение может выступать как основное, а другое отношение выступает в роли подчиненного. Это означает, что один кортеж основного отношения может быть связан с несколькими кортежами подчиненного отношения. Для поддержки этих связей оба отношения должны содержать наборы атрибутов, по которым они связаны. В основном отношении это первичный ключ отношения (PRIMARY KEY), который однозначно определяет кортеж основного отношения. В подчиненном отношении для моделирования связи должен присутствовать набор атрибутов, соответствующий первичному ключу основного отношения. Однако здесь этот набор атрибутов уже является вторичным ключом, то есть он определяет множество кортежей подчиненного отношения, которые связаны с единственным кортежем основного отношения. Данный набор атрибутов в подчиненном отношении принято называть внешним ключом (FOREIGN KEY).

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

Рис. 2 Связь между основным и подчиненным отношениями

PRIMARY KEY отношения Сотрудник атрибут Паспорт является FOREIGHN KEY для отношения «карьера».

Глава 2. Нормализация

Принципы нормализации

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

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

· Описание концептуальной схемы БД в терминах выбранной СУБД.

· Описание внешних моделей в терминах выбранной СУБД.

· Описание декларативных правил поддержки целостности базы данных.

· Разработка процедур поддержки семантической целостности базы данных.

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

· Мы должны построить корректную схему БД, ориентируясь на реляционную модель данных.

Корректной назовем схему БД, в которой отсутствуют нежелательные зависимости между атрибутами отношении.

Процесс разработки корректной схемы реляционной БД называется логическим проектированием БД.

Проектирование схемы БД может быть выполнено двумя путями:

· путем декомпозиции (разбиения), когда исходное множество отношений, входящих в схему БД заменяется другим множеством отношений (число их при этом возрастает), являющихся проекциями исходных отношений;

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

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

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

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

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

· первая нормальная форма (1NF);

· вторая нормальная форма (2NF);

· третья нормальная форма (3NF);

· нормальная форма Бойса—Кодда (BCNF);

· четвертая нормальная форма (4NF);

· пятая нормальная форма, или форма проекции-соединения (5NF или PJNF).

Основные свойства нормальных форм:

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

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

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

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

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

Функциональные зависимости определяют не текущее состояние БД, а все возможные ее состояния, то есть они отражают те связи между атрибутами, которые присущи реальному объекту, который моделируется с помощью БД.

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

Приведем ряд основных определений

Функциональной зависимостью набора атрибутов В отношения R от набора атрибутов, А того же отношения, обозначаемой как R. A -> R. B или, А -> В называется такое соотношение проекций R[A] и R[B], при котором в каждый момент времени любому элементу проекции R[A] соответствует только один элемент проекции R[B], входящий вместе с ним в какой-либо кортеж отношения R.

Функциональная зависимость R. A -> R. B называется полной, если набор атрибутов В функционально зависит от, А и не зависит функционально от любого подмножества А, то есть R. A -> R. B называется полной, если: любое А1 с А=> R. A -/-> R. B, что читается следующим образом: для любого А1, являющегося подмножеством A, R. B функционально не зависит от R. A, в противном случае зависимость R. A -> R. B называется неполной.

Функциональная зависимость R. A -> R. B называется транзитивной, если существует набор атрибутов С такой, что:

· С не является подмножеством А.

· С не включает в себя В.

· Существует функциональная зависимость R. A -> R.C.

· Не существует функциональной зависимости R. C -> R.A.

· Существует функциональная зависимость R. C -> R.B.

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

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

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

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

Взаимно-независимые атрибуты — это такие атрибуты, которые не зависят функционально один от другого.

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

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

1) Рефлексивность: если В является подмножеством А, то А->В

2) Дополнение: если. А->В, то АС->ВС

3) Транзитивность: если А->В и В->С, то А->С.

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

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

Отношение находится в первой нормальной форме тогда и только тогда, когда на пересечении каждого столбца и каждой строки находятся только элементарные значения атрибутов. [9]

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

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

Для приведения отношения «Расписание» к первой нормальной форме необходимо дополнить каждую строку фамилией преподавателя.

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

Рассмотрим отношение, моделирующее сдачу студентами текущей сессии. Структура этого отношения определяется следующим набором атрибутов:

(ФИО. Номер зач. кн. Группа. Дисциплина. Оценка)

Так как каждый студент сдает целый набор дисциплин в процессе сессии, то первичным ключом отношения может быть

(Номер, зач. кн. Дисциплина),

который однозначно определяет каждую стоку, отношения. С другой стороны, атрибуты ФИО и Группа зависят только от части первичного ключа — от значения атрибута Номер зач, кн., поэтому мы должны констатировать наличие неполных функциональных зависимостей в данном отношении. Для приведения данного отношения ко второй нормальной форме следует разбить его на проекции, при этом должно быть соблюдено условие восстановления исходного отношения без потерь. Такими проекциями могут быть два отношения:

(ФИО, Номер.зач.кн. Группа) (Номер зач. кн. Дисциплина. Оценка)

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

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

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

Отношение находится в третьей нормальной форме тогда и только тогда, когда оно находится во второй нормальной форме и не содержит транзитивных зависимостей. 9]

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

(ФИО. Номер зач.кн. Группа. Факультет, Специальность,

Выпускающая кафедра)

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

Номер зач. кн. -> ФИО

Номер зач. кн. -> Группа

Номер зач. кн. -> Факультет

Номер зач. кн. -> Специальность

Номер зач. кн. -> Выпускающая кафедра

Группа -> Факультет

Группа -> Специальность

Группа -> Выпускающая кафедра

Выпускающая кафедра -> Факультет

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

(Номер. зач. кн., ФИО. Специальность. Группа) (Группа. Выпускающая

кафедра) (Выпускающая кафедра, Факультет)

Первичные ключи отношений выделены.

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

(ФИО. Номер зач.кн. Группа. Факультет, Специальность,

Выпускающая кафедра)

Полученный набор отношений находится в третьей нормальной форме.

Отношение находится в нормальной форме Бойса—Кодла, если оно находится в третьей нормальной форме, и каждый детерминант отношения является возможным ключом отношений. 9]

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

(Номер зач. Кн. Идентификатор студента. Дисциплина. Дата. Оценка)

Возможными ключами отношения являются

Номер зач. Кн. Дисциплина, Дата

Идентификатор студента, Дисциплина, Дата.

Какие функциональные зависимости у нас имеются?

Номер зач. Кн. Дисциплина. Дата -> Оценка;

Идентификатор студента, Дисциплина. Дата -> Оценка;

Номер зач. кн. -> Идентификатор студента;

Идентификатор студента -> Номер зач. кн.

Откуда взялись две последние функциональные зависимости? Но ведь мы предварительно описали, что каждому студенту ставится в соответствие один номер зачетной книжки и один Идентификатор студента, поэтому по значению Номер зач. кн. можно однозначно определить Идентификатор студента (это третья зависимость) и обратно (и это четвертая зависимость). Оценим это отношение,

Это отношение находится в третьей нормальной форме, потому что неполных функциональных зависимостей не первичных атрибутов от атрибутов возможного ключа здесь не присутствует и нет транзитивных зависимостей. А, как же третья и четвертая зависимости, разве они не являются неполными? Нет, потому что зависимым не является не первичный атрибут, то есть атрибут, не входящий ни в один возможный ключ. Поэтому придраться к этому мы не можем. Но вот под четвертую нормальную форму наше отношение не подходит, потому что у нас есть два детерминанта Номер зач. кн. и Идентификатор студента, которые не являются возможными ключами отношения. Для приведения отношения к нормальной форме Бойса—Кодла надо разделить отношение, например, на два со следующими схемами:

(Номер зач. кн. идентификатор студента. Дисциплина. Дата. Оценка)

(Номер зач. кн. Идентификатор студента)

или наоборот:

(Номер зач. кн., Дисциплина. Дата, Оценка)

(Номер зач. кн. Идентификатор студента)

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

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

В отношении R (А, В, С) существует многозначная зависимость (multi valid dependence, MVD) R. A -" R. B в том и только в том случае, если множество значений В, соответствующее паре значений, А и С, зависит только от, А и не зависит от С.

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

(Номер зач. кн. Группа. Дисциплина)

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

В данном отношении существуют следующие две многозначные зависимости:

Группа -" Дисциплина

Группа -" Номер зач. кн.

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

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

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

R.A -" R. B

в том и только в том случае, когда существует многозначная зависимость

R.A -" R.C.

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

Теорема Фейджина

Отношение R (А, В, С) можно спроецировать без потерь в отношения R1 (А, В) и R2 (А, С) в том и только в том случае, когда существует MVD, А — ВС (что равнозначно наличию двух зависимостей, А -" В и, А -" С).

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

Отношение R находится в четвертой нормальной форме (4NF) в том и только в том случае, если в случае существования многозначной зависимости, А -" В все остальные атрибуты R функционально зависят от А. [9]

В нашем примере можно произвести декомпозицию исходного отношения в два отношения:

(Номер зач.кн. Группа)

(Группа, Дисциплина)

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

Последней нормальной формой является пятая нормальная форма 5NF, которая связана с анализом нового вида зависимостей, зависимостей «проекции соединения» (project-join зависимости, обозначаемые как PJ-зависимости). Этот вид зависимостей является в некотором роде обобщением многозначных зависимостей.

Отношение R (X, Y, …, Z) удовлетворяет зависимости соединения (X, Y, …, Z) в том и только в том случае, когда R восстанавливается без потерь путем соединения своих проекций на X, Y, …, Z. Здесь X, Y, …, Z — наборы атрибутов отношения R.

Наличие PJ-зависимости в отношении делает его в некотором роде избыточным и затрудняет операции модификации.

Отношение R находится в пятой нормальной форме (нормальной форме проекции-соединения — PJ/NF) в том и только в том случае, когда любая зависимость соединения в R следует из существования некоторого возможного ключа в R. [9]

Рассмотрим отношение R1:

R1 (Преподаватель. Кафедра, Дисциплина)

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

Введем следующие обозначения наборов атрибутов:

ПК (Преподаватель, Кафедра)

ПД (Преподаватель, Дисциплина)

КД (Кафедра, Дисциплина)

Допустим, что отношение R1 удовлетворяет зависимости проекции соединения (ПК, ПД, КД). Тогда отношение R1 не находится в NF/PJ, потому что единственным ключом его является полный набор атрибутов, а наличие зависимости PJ связано с наборами атрибутов, которые не составляют возможные ключи отношения R1. Для того чтобы привести это отношение к NF/PJ, его надо представить в виде трех отношений:

R2 (Преподаватель, Кафедра)

R3 (Преподаватель, Дисциплина)

R4 (Кафедра, Дисциплина)

Пятая нормальная форма редко используется на практике. В большей степени она является теоретическим исследованием. Очень тяжело определить само наличие зависимостей «проекции—соединения», потому что утверждение о наличии такой зависимости делается для всех возможных состояний БД, а не только для текущего экземпляра отношения R1. Однако знание о возможном наличии подобных зависимостей, даже теоретическое, нам все же необходимо.

база данный школьный журнал

Глава 3. Создание структуры БД «Классный журнал» методом нормализации.

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

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

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

Школьный журнал работает по следующему принципу:

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

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

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

Список учащихся

Создание структуры БД «Классный журнал» методом нормализации

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

Фамилия ученика

Имя ученика

Отчество ученика

Дата рождения

Адрес

Урок (дисциплина)

Дата проведения занятия

Оценка

Тема занятия

Домашнее задание

С этими отношениями имеем следующую таблицу: