Нормализация отношений (таблиц) и обеспечение целостности данных в реляционной базе данных
Системы управления базами данных. СУБД Access
Основные понятия
База данных – это совокупность структурированных данных, относящихся к некоторой предметной области.
Предметная область – область конкретной практической деятельности. В крупных организациях обычно выделяют ряд предметных областей в рамках основных служб, в каждой из которых создаются свои базы данных для решения своих задач.
Структурирование – это введение соглашений о способах представления данных. Это понятие близко к понятиям модель данных и формализация данных. В реляционных базах данных используются три структуры данных: таблица, запись, поле. Каждая из этих структур имеет свои свойства, описываемые параметрами. Таблица имеет имя и состоит из записей. Запись имеет номер в таблице и состоит из полей. У каждого поля есть имя, тип (текстовый, числовой и т.п.), длина в байтах. Поясним эти структуры на примере построения информационной модели конкретной предметной области.
Пусть нас интересует проблема учета всех затрат предприятия, например, строящего фабрику. Затраты должны учитываться по объектам, по видам работ и по организациям-подрядчикам. В соответствии с нашими интересами построим таблицы:
Объекты [Код об, Объект],
Работы [Код раб, Работа],
Организации [Код орг, Организация, Индекс, Город, Адрес, Телефон, Факс, Эл почта]
и собственно таблицу для учета затрат
Затраты [Код затр, Затрата, Код об, Код раб, Код орг, Дата, Стоимость].
Каждая из этих таблиц имеет имя, выделенное полужирным курсивом, и состоит из записей - строк, состав которых (перечень полей) указан в квадратных скобках. Имена полей – это имена столбцов таблицы. Курсивом выделены имена ключевых полей. Значение ключевого поля (ключа) однозначно определяет запись в таблице. По возрастанию значений ключа СУБД сортирует записи в таблицах.
Ключевые поля служат также для связывания таблиц. Например, таблица Затраты может содержать множество записей, в которых указан код одной и той же организации-подрядчика. Предположим, одна и та же организация проектировала и строила мост, подъездную дорогу, трансформаторную подстанцию и, возможно, другие объекты. При обработке записей таблицы Затраты может потребоваться факс этой организации – его легко найти в таблице Организации, которая должна содержать единственную запись с требуемым нам кодом организации в поле Код орг. Связь между этими таблицами называется связью «один ко многим» (1 ® ¥): ссылка на одну запись в таблице Организации содержится во многих записях таблицы Затраты. Если бы мы ввели еще одну таблицу – Банковские реквизиты, в которой для каждой организации-подрядчика указали бы ее код, название банка, номера счетов и другие данные, используемые при оформлении платежей, то связь между этой таблицей и таблицей Организации была бы связью «один к одному» (1 ® 1), т.к. в этих таблицах есть только по одной записи с одним и тем же значением ключевого поля Код орг. В некоторых ситуациях ключ может состоять из двух-трех полей и тогда он называется составным. Например, подразделение может идентифицироваться номером цеха и номером бригады в данном цехе.
Для ключевого поля СУБД строит индекс – вспомогательную таблицу, содержащую для каждого значения ключа адрес записи в основной таблице. Поэтому, если требуется запись с определенным значением ключа, то она легко находится по индексу. Если же в таблицу вставляется новая запись, то сортировке подвергается только индекс, но не записи таблицы.
Итак, в нашем примере база данных охватывает несколько взаимосвязанных таблиц «объекты-свойства». Такие базы данных называются реляционными. Это понятие (relation – отношение) было введено известным американским специалистом в области систем управления базами данных И.Ф.Коддом. В 1994 г. отмечалась 25 годовщина с того момента, как И.Ф.Кодд (тогда научный сотрудник корпорации IBM) предложил реляционную модель. Тем не менее первая коммерческая реляционная СУБД, названная Oracle [10], появилась только в 1979 г. Она была разработана небольшой компанией Silicon Valley. Сегодня это Oracle Corporation – крупнейший в мире поставщик реляционных СУБД и сопутствующих программных продуктов. Первой СУБД клиент/сервер стал выпущенный в 1985 г. Oracle 5. В настоящее время широкое распространение получили более поздние реляционные СУБД, созданные корпорациями Oracle, Sybase, Microsoft и некоторыми другими. Современные ведущие реляционные СУБД сочетают реляционную модель данных с технологией клиент/сервер и с объектно-ориентированным подходом к созданию программных средств.
Важнейшим достоинством концепции баз данных (в отличие, например, от обработки данных в автономных файлах) является введение набора стандартных структур, в которые, как в контейнеры, вкладываются данные. Планируя работу с данными в конкретной предметной области, после уяснения основных задач решают вопросы организации данных: как сгруппировать данные в таблицы, какие поля и каких типов, предусмотреть в каждой таблице, как связать таблицы друг с другом и т.п. Решение этого комплекса вопросов называют построением информационно-логической (инфологической) модели, которая отражает предметную область в виде совокупности информационных объектов и их структурных связей.
Только после решения вопросов организации данных приступают к разработке приложений – многофункциональных программ, осуществляющих преобразования данных путем их извлечения из одних таблиц, проведения расчетов и размещения результатов в других таблицах базы данных. Такой подход, во-первых, гарантирует, что каждый новый фрагмент данных, полученный предприятием, окажется «на своем месте» - в конкретной таблице конкретной базы данных, а, во-вторых, отпадает необходимость в разработке огромного числа процедур обработки данных. Последнее объясняется тем, что типовые операции над содержимым структур данных (таблиц, записей, полей) уже запрограммированы и входят в состав СУБД – ведь системы управления базами данных как раз и предназначены для создания баз данных и последующего манипулирования этими данными. СУБД, работающую со структурами данных, можно сравнить с техническими средствами на современном транспорте – они работают с контейнерами, не зависимо от того, что в этих контейнерах перевозится в конкретном случае.
Нормализация отношений (таблиц) и обеспечение целостности данных в реляционной базе данных
В терминологии реляционных баз данных таблицы называют отношениями – ведь, включая в записи таблицы определенные поля, мы устанавливаем отношения между информационными объектами. Поскольку одни и те же данные можно по-разному сгруппировать в таблицы, то требуются некоторые правила, оптимизирующие группировку полей - свойств объектов. Такие правила были сформулированы применительно к реляционным базам данных. Это правила нормализации отношений.
Нормализация отношений – формальный аппарат ограничений на формирование отношений (таблиц), который позволяет устранить дублирование, обеспечивает непротиворечивость хранимых данных, уменьшает трудозатраты на их ввод и корректировку. И.Ф.Коддом выделены три нормальные формы отношений и предложены методы преобразования отношений к третьей, самой совершенной нормальной форме.
Отношение считается нормализованным, или приведенным к первой нормальной форме, если все его атрибуты (свойства объектов, описываемые в полях записей) простые, т.е. далее неделимы. Отношение Организации (см. подраздел 5.1) можно считать приведенным к первой нормальной форме. Единственный его атрибут, который теоретически еще можно разделить на части, - это Адрес. Но практически этот атрибут уже не делим, так как улица и дом, где расположена каждая организация, нам не могут потребоваться в отдельности. А такие атрибуты, как Город, уже отделены от адреса. Так что, если нам потребуется какая-нибудь сводка по организациям-подрядчикам, расположенным в определенном городе, то мы легко сможем отобрать соответствующие записи.
Отношение находится во второй нормальной форме, если оно приведено к первой нормальной форме, и его каждый неключевой атрибут функционально зависит от ключа. Функциональная зависимость от ключа означает, что в экземпляре информационного объекта (в записи таблицы) конкретному значению ключа соответствует определенное значение описательного атрибута. Так, в таблице Затраты нашего примера коду (например, номеру) каждой затраты соответствует ее название, код объекта, в который вложены средства, коды вида работ и организации-подрядчика, дата и сумма платежа. Перечисленные атрибуты функционально зависят от ключа Код затр. Если бы ключ был составным, то для приведения отношения ко второй нормальной форме потребовалась бы функционально полная зависимость атрибутов от ключа. Она заключается в том, что каждый неключевой атрибут функционально зависит от ключа, но не находится в функциональной зависимости ни от какой части составного ключа.
Понятие третьей нормальной формы основывается на понятии нетранзитивной зависимости [6]. Транзитивная зависимость наблюдается, если один из атрибутов зависит от ключа, а другой – от этого атрибута. Например, если в таблицу Затраты включить не только код организации, но и город, в котором она расположена, то получится, что атрибут Код орг функционально зависит от ключа Код затр , а атрибут Город зависит, в свою очередь, от атрибута Код орг и, следовательно, транзитивно зависит от ключа.
Отношение находится в третьей нормальной форме, если оно находится во второй нормальной форме, и каждый неключевой атрибут нетранзитивно зависит от первичного ключа. Если бы мы включили в записи таблицы Затраты не только код организации-подрядчика, но и ее атрибуты (название, адрес и др.), то отношение уже не находилось бы в третьей нормальной форме, а это резко осложнило бы работу с базой данных. Во-первых, во все записи таблицы Затраты, где в качестве подрядчика выступает эта организация, пришлось бы вводить не только ее код, но и длинные названия, дублируя их многократно. Во-вторых, в случае изменения адреса, факса или другой характеристики организации пришлось бы вносить коррективы не в единственную запись таблицы Организации, а во множество записей таблицы Затраты.
Приведенный пример показывает, что казалось бы теоретическое понятие нормализации отношений играет важную практическую роль, позволяя устранить дублирование данных, облегчить их ввод и корректировку в базе данных. Другое важное понятие – обеспечение целостности данных в базе данных. Этот термин подразумевает, что в СУБД должны иметься средства, не позволяющие нарушать корректность и полноту хранимой информации. Например, СУБД обычно содержат средства поддержания ссылочной целостности. Так, если мы попытаемся в запись таблицы Затраты ввести код объекта 777, а в таблице Объекты еще нет объекта с кодом 777, то СУБД должна воспрепятствовать нашему намерению, если, конечно, мы выбрали соответствующий режим ее работы. Кроме того, когда мы вводим новую запись, СУБД проверяет уникальность ее ключа, обеспечивая целостность таблицы. Наконец, СУБД проверяет целостность домена. Домен – это множество допустимых значений столбца. Так в столбец Код орг могут входить только целые числа. Если при вводе записи введем в поле Код орг хотя бы одну букву или действительное число, запись не будет включена в таблицу.
Предшествующее изложение основных сведений о реляционных базах данных иллюстрировалось на примере базы данных Затраты предприятия. Предлагаемая далее лабораторная работа посвящена созданию и использованию этой базы данных с помощью СУБД Access.
5.3. Лабораторная работа: создание и использование базы данных «Затраты»
Создание базы данных
Сначала создадим пустую базу данных “Затраты”. Для этого откроем приложение Access, выберем пункт меню Файл/Создать, в открывшемся диалоговом окне подтвердим желание создать новую БД нажатием кнопки Ok. После этого откроется стандартное диалоговое окно Сохранить как. В нем надо выбрать каталог для размещения новой базы данных и дать имя файлу, например, Затраты.mdb. Этот файл будет содержать описание структуры таблиц, сами таблицы, формы, запросы и отчеты, которые будут созданы в рамках базы данных “Затраты”. На рис. 5.1 показано окно приложения Access. В этом окне после создания пустой базы данных “Затраты” появится окно базы данных с заголовком “Затраты: база данных”, только в этом окне еще не будет перечня таблиц и, конечно, в окне приложения Access еще не будут открыты окна таблиц для просмотра и корректировки наших четырех таблиц (см. подраздел 5.1). На рисунке эти таблицы показаны заранее, чтобы читатель яснее представлял то, что еще предстоит создать.