Основные принципы организации баз данных

Основные принципы организации баз данных 1. Понятие базы данных К организации данных в системах автоматизированной обработки информации возможны два подхода: 1. Каждый пользователь системы создает наборы данных, необходимых для решения его задач, и пишет программы обработки данных. Например, в рамках ВУЗа различные подразделения (деканат, отдел кадров, бухгалтерия и т.п.) могут создать свои подсистемы, предназначенные для решения определенных задач. 2. Вся информация, описывающая определенную предметную область, хранится, интегрировано, в единой базе данных (БД) и каждый из пользователей имеет доступ к тем данным, которые необходимы ему для решения его задач. Первый из подходов имеет ряд недостатков: • В различных подсистемах часто хранится одна и та же информация (сведения о студентах, о преподавателях и т.п.), т.е. данные дублируются, и возникает избыточность информации. При появлении изменений в данных необходимо обновлять многочисленные наборы данных и если отдельные наборы окажутся не скорректированы, возникнет противоречивость данных. • Обмен данными между отдельными подсистемами затруднен или невозможен, т.к. программы отдельных подсистем написаны на различных языках программирования, а данные представлены в различных форматах. • При появлении в подсистеме новых задач, а, следовательно, и новых данных придется вносить изменения в уже созданные программы, т.к. данные описаны в каждой из прикладных программ (описаны типы и форматы данных, типы файлов). В подобном случае говорят, что прикладные программы зависят от хранимых данных. При хранении данных в БД перечисленные недостатки снимаются. Однако в этом случае возникает другой недостаток: у данных нет единого хозяина. Из-за этого снижается ответственность за правильность хранимых данных и нарушается секретность. Для устранения этого недостатка в БД разрабатывается специальная система защиты. БД - это совокупность специальным образом организованных данных, описывающих определенный класс объектов или определенную предметную область и используемая различными пользователями и приложениями для решения своих задач. Слова "специальным образом организованные" означают то, что БД должна строиться по определенным правилам и должна удовлетворять ряду определенных требований. Комплекс программных и языковых средств, обеспечивающих создание и функционирование БД, называется Системой Управления Базой Данных - СУБД. Совокупность БД, СУБД, технических средств и обслуживающего персонала образует банк данных (БнД). Комплекс программных средств, управляющих БнД, называется Системой Управления Банком Данных (СУБнД). 2. Требования, предъявляемые к БД Правильно спроектированная БД должна удовлетворять следующим требованиям: 1. Минимальная избыточность. Непротиворечивость. 2. Целостность данных. 3. Независимость данных. 4. Возможность ведения (добавления и удаления) и актуализации (корректировки, модификации) данных. 5. Безопасность и секретность. 6. Высокая производительность. Минимальные затраты. 7. Соблюдение стандартов. 1. Минимальная избыточность означает то, что данные в БД не должны дублироваться. Избыточность данных, если она существует, влечет две опасности: -неоправданно большой расход памяти; -нарушение непротиворечивости данных, т.е. возникновение такой ситуации, когда в различных местах машинной памяти хранятся противоречивые данные. Возникновение противоречивости чрезвычайно опасно для БД. В ряде СУБД есть средства, предотвращающие дублирование и возникновение противоречивости данных. В противном случае такие средства разрабатывает системный программист. 2. Целостность данных означает то, что в БД должны храниться только правильные данные. Разрушение и искажение данных возможно в результате неосторожных действий пользователей, в результате ошибок в программах и сбоев оборудования. Для обеспечения целостности и предотвращения возможности ввода неправильных данных должны разрабатываться средства контроля правильности вводимых данных. Например, можно использовать процедуры, проверяющие принадлежность вводимых значений определенному диапазону допустимых значений. В БД должны быть предусмотрены средства защиты данных от нечаянных и предумышленных искажений, средства восстановления испорченных данных. Различные СУБД в той или иной мере располагают такими средствами. Существуют специальные методы и приемы обеспечения целостности. 3. Независимость данных означает то, что прикладные программы не должны зависеть от хранимых данных, т.е. от способа хранения данных в физической памяти. Это позволяет добавлять в БД новые данные, изменять структуры хранения данных, создавать на БД новые приложения. Ранее созданные программы при этом не должны "чувствовать" эти изменения. СУБД обычно обеспечивают это требование. 4. Структура БД должна позволять включать новые и удалять устаревшие данные, корректировать хранимые данные без разрушения логических связей, установленных в схеме БД. Для этого схема БД должна быть правильно разработана, а операции ведения БД не должны нарушать схему БД. 5. Безопасность и секретность означает защиту данных от несанкционированного доступа, преднамеренного и непреднамеренного разрушения данных, хищения данных. Система защиты должна иметь: -средства идентификации пользователей; -средства ограничения доступа к данным. Каждый пользователь должен работать только с теми данными, которые необходимы для решения его задач, остальные данные должны быть для него "невидимыми"; -средства, обеспечивающие секретность данных. Подобные средства содержатся в СУБД или разрабатываются системным программистом. 6. Организация БД и методы доступа к данным должны обеспечивать высокую скорость обработки данных так, чтобы пользователь мог работать с БД в диалоговом режиме. Стоимость обслуживания пользователей не должна быть высокой. Возможность выполнения этих требований определяется рядом факторов: объемом хранимых данных, быстродействием техники, способом организации данных в БД и во многом зависит от решений, принимаемых разработчиками на этапе создания БД. 7. Представление данных в БД, документация, способ взаимодействия пользователя с БД должны удовлетворять определенным стандартам. Стандарты могут быть корпоративными, ведомственными, промышленными, национальными и международными. Соблюдение стандартов совершенно необходимо для совместного использования данных и для организации обмена данными между отдельными системами. Например, без принятия определенных стандартов нельзя было бы организовать сеть Internet. 3. Уровни представления данных в БД В БД выделяют 5 уровней представления данных: уровень пользователя, внешний уровень, концептуальный уровень, уровень хранения и физический уровень. Два последних уровня часто рассматривают как единый уровень - внутренний. Для каждого уровня характерно определенное представление данных и определенный перечень выполняемых функций. Центральной частью БД является концептуальный уровень представления данных или концептуальная схема. Концептуальная схема (или схема БД) - это описание общей логической структуры данных. Схема строится на основании изучения той предметной области, в которой будет использоваться БД. В процессе обследования предметной области выделяются объекты предметной области и свойства (характеристики) объектов. Объектом может быть человек, предмет, документ, процесс, явление т.п. Выявляются логические связи между объектами, существующие в предметной области. При этом учитывается круг задач, решаемых на БД, выявляются информационные потребности пользователей, т.е. определяются возможные запросы к БД. На данные, подлежащие хранению в БД, накладываются определенные ограничения, определяются операции, которые будут выполняться над данными. После этого определяется модель данных и строится схема БД. Т. о. концептуальная схема - это логическая структура данных, ограничения, накладываемые на данные и операции с данными. Разработанная схема описывается на ЯОД (языке описания данных) той СУБД, которая будет использоваться. Описание БД на концептуальном уровне хранится в памяти машины наряду с самими данными и образует так называемые метаданные. Схема, содержащая конкретные данные, называется экземпляром схемы или текущим состоянием БД. С течением времени текущее состояние меняется, но схема остается неизменной. Так, например, табло в аэропорту можно рассматривать как схему данных. Данные на табло меняются, но схема табло остается неизменной. С БД будут работать люди, имеющие разный уровень компьютерной подготовки, т.е. пользователи разных уровней. Для пользователей - программистов, решающих конкретные прикладные задачи, разрабатывается внешний уровень представления данных или так называемые подсхемы. В каждую подсхему из схемы выделяется то подмножество данных, которое необходимо для решения задач данного пользователя. Такой пользователь имеет представление о схеме данных, знает, как построена его подсхема, может самостоятельно писать программы обработки данных. Но он не имеет доступа ко всей БД и не может изменять концептуальную схему БД. Работа с БД через подсхему - это эффективное средство защиты данных. В ряде СУБД есть специальные средства создания подсхем (view). Для пользователей-непрограммистов разрабатывается уровень пользователя. Такой пользователь (кассир в ж/д кассе, банковский служащий) может ничего не знать о базах данных и программировании. Он знает лишь определенную совокупность действий или простейших команд, которые позволяют ему выполнять свою работу. Такой пользователь имеет доступ только к подсхеме. Для него разрабатывается специальный дружественный интерфейс, обеспечивающий простое и удобное взаимодействие с системой, - интерфейс пользователя-непрограммиста. Внутренний уровень определяет способ размещения данных на внешних запоминающих устройствах. СУБД построена таким образом, что изменения на внутреннем и концептуальном уровнях не влияют на работающие прикладные программы, т.е. обеспечивается независимость прикладных программ от хранимых данных. Лицо или группа лиц, ответственных за всю БД в целом, за систему защиты и за все уровни представления данных называется Администратором Базы Данных (АБД). 4. Языки баз данных Основная часть СУБД, используемая программистом, это Язык Данных (ЯД). Существует следующие основные типы ЯД: ЯОД - язык описания данных, ЯМД - язык манипулирования данными, ЯЗ - язык запросов. ЯОД обязательно есть в любой СУБД, на нем описывается схема БД. ЯМД предназначен для доступа к данным и для выполнения манипуляций с данными: чтение или выборка данных, запись в БД, поиск, добавление, удаление, корректировка данных и т.п. (Операции добавления, удаления и корректировки данных называются операциями ведения БД). Различные СУБД содержат различные наборы операторов ЯМД и различные правила их использования. ЯЗ - это совокупность средств, предназначенных для организации интерфейса пользователя-непрограммиста. Такой язык имеется не во всех СУБД и часто пользовательский интерфейс приходится разрабатывать АБД. 5. Логическая структура данных Основой концептуальной схемы БД является логическая структура данных. Именно с ее разработки начинается создание любой БД. В логической структуре отображаются все объекты предметной области и их свойства, устанавливаются связи, существующие между объектами в предметной области. Различают два понятия: тип объекта и экземпляр объекта. Любой объект описывается следующей триадой: <Имя объекта, Свойства объекта, Значения свойств> Тип объекта характеризуется первыми двумя компонентами триады. Для свойств устанавливаются имена. Например, имя объекта - СТУДЕНТ, свойства объекта: ФИО, ГРУППА, СРЕДНИЙ БАЛЛ и т.п. В теории БД понятие "тип объекта" часто заменяют понятием "СУЩНОСТЬ". Следует помнить, что эти понятия относятся к предметной области. В СУБД понятию "тип объекта" соответствует "тип записи", при этом имя объекта часто рассматривается как имя записи. Свойства объекта являются полями записи, каждое поле имеет имя соответствующего свойства. Конкретные значения свойств определяют конкретный экземпляр объекта данного типа. Например: ФИО - Иванов И.И., ГРУППА - 037, СРЕДНИЙ БАЛЛ - 4,5. В СУБД экземпляру объекта соответствует экземпляр записи, значения каждого свойства хранятся в соответствующем поле. Совокупность всех экземпляров записей определенного типа образует файл данных. Между отдельными объектами, а также между свойствами объектов в предметной области могут существовать связи 1:1, 1:М, М:М. В концептуальной схеме эти связи отображаются соответствующей структурой данных (моделью данных). Связи 1:М отображаются иерархической (древовидной) моделью, а связи М:М - сетевой моделью. Существуют СУБД, поддерживающие эти модели данных: иерархические и сетевые СУБД. Однако такие СУБД разрабатывались для использования их на больших ЭВМ. Подавляющее большинство СУБД для персональных ЭВМ поддерживают реляционную модель данных. Существуют приемы, позволяющие преобразовать иерархическую и сетевую модели в реляционную. 5 Основные понятия реляционной модели данных Реляционная модель была предложена американским математиком Е. Коддом в 1970 г. Это единственная из моделей БД, основанная на специальном разделе математики - теории отношений. Математическая обоснованность позволила сформулировать, достаточно строгие правила построения модели, обеспечивающие ее работоспособность. Языки данных, основанные на математическом аппарате теории отношений, позволяют составлять любые запросы к БД и выполнять разнообразные операции манипулирования данными. В реляционной модели все данные представлены в табличной форме. Каждому типу объекта сопоставлена отдельная таблица, имеющая соответствующее имя. Свойствам объекта сопоставлены столбцы таблицы с соответствующими именами. Строки каждой таблицы содержат значения свойств и соответствуют экземплярам объектов данного типа. Проиллюстрируем вышесказанное небольшим примером. Рассмотрим фрагмент предметной области, связанной с обработкой информации о поставках на предприятие комплектующих изделий. В БД предстоит хранить сведения о поставщиках, изделиях и объемах поставок каждого изделия каждым из поставщиков В предметной области можно выделить два объекта: ПОСТАВЩИК и ИЗДЕЛИЕ. Каждый из объектов характеризуется рядом вполне определенных свойств. Анализ возможных запросов к БД и задач обработки данных позволяет установить, какие именно сведения о каждом из объектов следует хранить в БД. Пусть из БД необходимо получать сведения о том, какие изделия и в каком количестве поставляются определенным поставщиком; из какого города поставляются определенные изделия, в каком городе расположен определенный поставщик и т.п. Тогда определим для объектов следующие свойства. ПОСТАВЩИК (П#, Имя поставщика, Город) ИЗДЕЛИЕ (И#, Наименование, Вес) Каждому поставщику и каждому изделию присвоим свой уникальный номер. Символами П# и И# обозначены номера поставщиков и номера изделий. Таким образом, нами определены два типа объектов предметной области. Установим тип связей, существующих между объектами. Известно, что каждый поставщик поставляет несколько изделий, а каждое изделие, в сою очередь, поставляется различными поставщиками. Следовательно, между этими объектами существует логическая связь типа М:М. Каждой из связей можно поставить в соответствие определенное значение, например, определить количество поставляемых изделий. М ПОСТАВЩИК ИЗДЕЛИЕ. М Как отмечало выше, каждому из типов объектов в реляционной модели соответствует отдельная таблица. Имя таблицы соответствует имени объекта, имена столбцов таблицы - именам свойств объекта, в ячейках таблицы хранятся значения свойств. Каждая строка таблицы соответствует экземпляру объекта данного типа. Между таблицами реляционной модели должны быть установлены те же связи, которые существуют между объектами предметной области. Связи между таблицами устанавливаются через одинаковые значения одноименных столбцов. Связи типа 1:М обычно устанавливаются введением в связываемые таблицы дополнительных столбцов. Для установления связи М:М создается дополнительная связующая таблица, с которой каждая из связываемых таблиц связывается отношением 1:М. Если между таблицами должна устанавливаться связь 1:1, то эти таблицы можно объединить в одну. Создадим связующую таблицу ПОСТАВКИ (П#, И#, Количество). Эту таблицу можно рассматривать как третий объект предметной области. Между таблицами ПОСТАВЩИК и ПОСТАВКИ связь 1:М устанавливается по столбцу П#. Это значит, что каждой строке таблицы ПОСТАВЩИК (с определенным номером поставщика) соответствует несколько строк таблицы ПОСТАВКИ (это строки с тем же номером поставщика). Между таблицами ИЗДЕЛИЕ и ПОСТАВКИ также устанавливается связь 1:М по столбцу И#. Таблицы ПОСТАВЩИК и ИЗДЕЛИЕ связываются отношение М:М через таблицу ПОСТАВКИ. В связующей таблице присутствуют столбцы, уже имеющиеся в других таблицах. Для того, чтобы в БД не хранить многократно длинные символьные строки, поставщикам и изделиям были присвоены номера П# и И#. Такой прием позволяет уменьшить объем хранимых данных и несколько упростить процедуры установления связей между таблицами. ПОСТАВЩИК ИЗДЕЛИЕ П#. Имя поставщ. Город И# Наименование Вес П1 Восход Тула И1 Болт 12 П2 Заря Самара И2 Гайка 8 П3 Салют Тула И3 Гвоздь 6 И4 Винт 14 ПОСТАВКИ П# И# Количество П1 И1 300 П1 И2 200 П1 И3 200 П2 И1 200 П2 И2 500 П3 И4 80 Таблицы реляционной модели строятся по определенным правилам. Некоторые из них таковы: • в таблице не должно быть столбцов с одинаковыми именами; • в каждом столбце содержатся данные одного и того же типа и одинакового смысла; • в таблицах не должно быть повторяющихся строк; Ряд других очень важных правил рассматривается ниже. При соблюдении всех правил построения таблиц каждую из них можно рассматривать как математическое отношение (relation).В этом случае над таблицами можно выполнять математические операции реляционной алгебры и теории множеств. Именно эти операции положены в основу ЯМД реляционных СУБД. Для реляционной модели используются термины, принятые в теории отношений. Имя отношения - это имя таблицы, атрибут отношения - это столбец таблицы с определенным именем, кортеж отношения - это множество значений, содержащихся в строке таблицы. Отношение - это совокупность кортежей, т.е. таблица со всеми своими строками. Множество значений, которые может принимать атрибут в данном отношении, называется доменом (это множество всех значений, которые могут присутствовать в данном столбце таблицы). Количество атрибутов отношения (столбцов в таблице) определяет его степень или арность. Рассмотренная модель содержит три тернарных отношения (три таблицы с тремя столбцами). 6. Основные операции над отношениями Над отношениями можно выполнять операции теории множеств (объединение отношений, пересечение отношений, дополнение отношения и др.), а также операции реляционной алгебры (композиция отношений, сцепление отношений и проекция отношения). Рассмотрим основные операции реляционной алгебры, положенные в основу языков данных многих коммерческих систем управления реляционными БД. Пусть имеются два отношения: отношение с именем R и отношение с именем S. Каждое из отношений имеет арность 2 (бинарные отношения). Отношение R состоит из атрибутов А1 и А2, отношение S - из атрибутов А2 и А3. Сцепление отношений. Можно выполнить операцию сцепления двух отношений по одинаковым значениям одноименных атрибутов Ai. Атрибуты, по которым выполняется сцепление, должны быть определены на одном и том же домене. Операцию сцепления обозначим символом *. Сцеплением отношений R и S называется новое отношение R*S, состоящее из кортежей , для которых кортеж принадлежит R, а принадлежит S. Пример 1. Определим результат выполнения операции сцепления отношений R и S, атрибуты которого А2 определены на одном и том же домене. Это значит, что если в столбце с именем А2 таблицы R содержатся, например, названия городов, то и в столбце А2 таблицы S также содержатся названия городов. R S R*S R*M А1 А2 А2 А3 А1 А2 А3 А1 А2 a b b k a b k a b c b d f c b k c b c d d p c d f e q c d p В результате сцепления двух бинарных отношений получено новое тернарное отношение, кортежи которого оказались "сцеплены" по одинаковым значениям атрибута А2 Иными словами, в результате сцепления двух таблиц, состоящих из двух столбцов, получена новая таблица, состоящая из трех столбцов. Строки результирующей таблицы "сцеплены" по одинаковым значениям, находящимся в столбцах с именем А2.Таким образом, информация из одной таблицы оказалась "привязанной" к информации из другой таблицы. В общем случае, если отношение R имеет арность m, а отношение S - арность n , то R*S будет иметь арность m+n-1. Можно выполнить сцепление отношения с множеством. Пусть множество М является одноэлементным и содержит одно значение {b} из того же домена, что и значения атрибута А2 отношения R. Тогда результатом операции сцепления будет отношение R*M, состоящее из тех кортежей отношения R, у которых значение атрибута А2 равно b. Иными словами, в результате сцепления таблицы R с константой b из таблицы R оказались выбранными стоки, содержащие в столбце А2 значение b (см. Пример 1). Если в столбце А2 содержались, например, названия городов, то из таблицы отберутся строки с заданным названием города. Проекция отношения. Проекцией отношения R на его атрибут Аi называется множество, состоящее из различных значений этого атрибута. Операция проекции обозначается R [Аi]. Пример 2. Найдем проекцию отношения R на его атрибут А2. В результате получим множество {b, d, q}. Иными словами, результатом проекции является множество различных значений, образующих столбец А2 таблицы R. 7. Ключи отношений Внутри каждого отношения реляционной модели обязательно должен существовать атрибут, однозначно идентифицирующий каждый кортеж отношения. Такой атрибут называется первичным (основным) ключом отношения. Иными словами, в каждой таблице должен присутствовать столбец, значения в котором не повторяются. Каждое значение, присутствующее в таком столбце, определяет единственную строку в таблице. Поскольку каждый столбец соответствует определенному свойству объекта, то этот столбец соответствует такому свойству, каждое значение которого присуще одному единственному экземпляру объекта данного типа. Первичный ключ используется для доступа к записям в реляционных БД. В таблице ИЗДЕЛИЕ первичным ключом является атрибут И#, а в таблице ПОСТАВЩИК - П#. Если в отношении нет атрибута, который можно принять в качестве первичного ключа (т.е. среди свойств объекта не нашлось ни одного, однозначно идентифицирующего каждый объект), то в качестве первичного ключа можно использовать некоторую совокупность атрибутов. Например, в отношении ПОСТАВКИ в качестве первичного ключа выступает совокупность атрибутов П#, И#, т.к. сведения об объеме поставок конкретного изделия конкретным поставщиком можно получить только тогда, когда известен номер поставщика и номер поставляемого им изделия. Каждая комбинация значений этих атрибутов уникальна для каждого кортежа и однозначно его идентифицирует. Такой ключ называется составным. Первичный ключ никогда не должен принимать нулевого значения, а в составном ключе ни одна из компонент никогда не должна быть нулевой. Для реляционных БД очень важным является также понятие внешнего ключа отношения. Атрибут, являющийся первичным ключом одного отношения и входящий в составной первичный ключ другого отношения является внешним ключом этих отношений. Так, например, внешним ключом отношений ПОСТАВЩИК и ПОСТАВКИ является атрибут П#, а внешним ключом отношений ИЗДЕЛИЯ и ПОСТАВКИ является атрибут И#. Через внешний ключ можно выполнить операцию сцепления двух отношений. Так, например, можно определить все поставки, производимые поставщиком П1, выполнив сцепление отношения ПОСТАВЩИК и ПОСТАВКИ по П1 (напомним, что между этими отношениями существует логическая связь типа 1:М). Все отношения реляционной модели должны иметь внешние ключи, так как именно через внешние ключи осуществляются связи 1:М и М:М. На практике в качестве внешнего ключа отношения, находящегося на стороне М, может использоваться и не ключевой атрибут. 8. Нормализация отношений реляционной БД Отношения реляционной БД можно привести к такому виду, который обеспечил бы наилучшие свойства БД. Процесс приведения отношений к такому виду называется нормализацией. При работе с ненормализованными отношениями в процессе ведения БД (при выполнении операций добавления и удаления строк в таблицы) могут возникнуть так называемые аномалии включения и удаления. Аномалия включения проявляется в том, что некоторые нужные данные невозможно ввести в БД до тех пор, пока не будут известны некоторые связанные с ними дополнительные сведения. Аномалия удаления связана с возникновением таких ситуаций, когда при удалении устаревших данных вместе с ними из БД исчезают и нужные сведения. Известно 5 нормальных форм (НФ) отношений. Нормализация выполняется поэтапно. Сначала отношение приводится к первой НФ (1НФ), затем - ко 2НФ и т.д. вплоть до 5НФ. Однако для устойчивой работы БД часто оказывается достаточным, если ее отношения находятся в 3НФ. Между формами существует следующая связь. Если отношение находится в 3НФ, то это значит, что оно уже находится во 2НФ и 1НФ. Первая нормальная форма Считается, что отношение находится в 1НФ, если значение каждого его атрибута не структурировано. Это означает, что на пересечении каждого столбца и каждой строки таблицы должно находиться одно единственное значение. В рассмотренной нами выше предметной области о поставках на предприятие изделий поставщик П1 поставляет в определенных количествах изделия И1, И2 и И3, поставщик П2 - изделия И1 и И2, а поставщик П3 - изделие П4. Отобразим эти данные в таблице ПОСТАВКИ 1. Такой вид таблицы типичен для многих документов. Так, например, в одну колонку таблицы часто помещают фамилию, имя и отчество человека или адрес, содержащий почтовый индекс, город, улицу, номер дома, номер квартиры. Номера поставщиков П# уникальны, и этот атрибут можно принять в качестве первичного ключа отношения. ПОСТАВКИ 1 ПОСТАВКИ П# ПК И# Кол П1 И1 И2 И3 300 200 200 П2 И1 И2 200 500 П3 И4 80 П# И# Кол П1 П1 П1 П2 П2 П3 И1 И2 И3 И1 И2 И4 300 200 200 200 500 80 Можно заметить, что в отношении ПОСТАВКИ 1 значения ключа П# идентифицирует сразу несколько значений не ключевых атрибутов И# и Кол, что недопустимо. (В таблице на пересечении, например, строки П1и столбца И# содержится несколько значений номеров изделий ). Это отношение находится не в 1НФ. Преобразуем отношение так, чтобы устранить этот недостаток. В преобразованном отношении ПОСТАВКИ первичный ключ - составной П# И# и каждое значение первичного ключа идентифицирует единственное значение не ключевого атрибута Кол. Это отношение находится в 1НФ. Вторая нормальная форма Отношение, имеющее составной первичный ключ, находится во 2НФ, если каждый из не ключевых атрибутов функционально полно зависит от всего первичного ключа. Пусть в проектируемой БД помимо перечисленных ранее сведений о поставках необходимо хранить также сведения о стоимости перевозок грузов из тех городов, откуда производятся поставки. Для этого введем свойство Тариф, значение которого определяет стоимость перевозок единицы груза, например, одной тонны, из каждого города. Предположим, что на начальном этапе проектирования логической структуры данных не удалось выявить типы объектов предметной области и связи, существующие между ними, подобно тому, как это было сделано в п.5. Тогда все имеющиеся сведения представим в виде одной таблицы ПОСТАВКИ 2 (в эту таблицу для упрощения не включены наименования изделий, так как в дальнейших рассуждениях это свойство не будет иметь для нас значения). ПОСТАВКИ 2 П# И# Кол Имя поставщика Город Тариф П1 И1 300 Восход Тула 10 П1 И2 200 Восход Тула 10 П1 И3 200 Восход Тула 10 П2 И1 200 Заря Самара 15 П2 И2 500 Заря Самара 15 П3 И4 80 Салют Тула 10 Отметим, что у этого отношения первичный ключ составной - П# И#. Рассмотрим следующую ситуацию. Пусть ведутся переговоры о поставках с новым предприятием Победа, расположенном в городе Тамбове. Требуется включить в таблицу сведения об этом предприятии и сведения о тарифе по доставки грузов с этого предприятия. Мы не сможем этого сделать. Это предприятия еще ничего не поставляет и код поставляемых изделий неизвестен, т.е. И# =0, а у составного первичного ключа ни одна из компонент не может быть нулевой. В этом и состоит аномалия включения. Если же какое-либо предприятие, например, Заря перестанет поставлять нам изделия, то для него И# =0. Из таблицы придется исключить все сведения об этом предприятии, в том числе и те, которые мы хотели бы сохранить. В этом состоит аномалия удаления. Кроме того, отношение обладает избыточностью: названия предприятий и городов, а также тарифы повторяются многократно. Выясним, в чем причина таких недостатков. Для этого проанализируем характер зависимости не ключевых атрибутов от первичного ключа. Ключ отношения составной, но он, целиком, определяет лишь атрибут Кол. Атрибуты Имя поставщика, Город и Тариф зависят лишь от части первичного ключа, так как именно номер поставщика однозначно определяет имя предприятия, название города, а следовательно, и тариф. Это отношение находится не во 2НФ. Для приведения его ко 2НФ надо выделить группы атрибутов, зависящие от частей составного ключа, в отдельные таблицы. В таблицу ПОСТАВЩИК1 выделим атрибуты П#, Имя поставщика, Город, Тариф. Ключ этого отношения - П#. В таблицу ПОСТАВКИ выделим атрибуты П#, И#, Кол. Здесь первичный ключ составной - П #И#. Отношения связаны между собой через внешний ключ П#. Теперь полученные отношения находятся во 2НФ и в БД можно включать сведения о новых предприятиях даже в том случае, когда они не поставляют никаких изделий. Полученные отношения обладают меньшей избыточностью, чем исходное отношение. В новых таблицах не повторяются названия предприятий и городов, тарифные ставки. ПОСТАВЩИК1 ПОСТАВКИ П# Имя поставщика Город Тариф П1 П2 П3 Восход Заря Салют Тула Самара Тула 10 15 10 П# И# Кол П1 П1 П1 П2 П2 П3 И1 И2 И3 И1 И2 И4 300 200 200 200 500 80 Третья нормальная форма Отношение находится в 3НФ, если в нем нет функционально полной зависимости между не ключевыми атрибутами. Рассмотрим зависимости между атрибутами отношения ПОСТАВЩИК1. Каждый из не ключевых атрибутов полностью зависит от первичного ключа П#, но в то же время атрибут Тариф функционально полно зависит только от атрибута Город. Эти атрибуты могут существовать вне зависимости от первичного ключа. Следовательно, это отношение находится не в 3НФ. В таком отношении также могут возникнуть аномалии включения-удаления. Если потребуется включить в таблицу сведения о новом городе и стоимости перевозок из него, то мы не сможем этого сделать до тех пор, пока в этом городе не появится новый поставщик и не получит свой статус поставщика. До тех пор ему не присвоен код П#, т.е. П# =0 и кортеж, содержащий сведения об этом городе не будет иметь первичного ключа (аномалия включения). Если же предприятие Заря перестанет поставлять нам изделия и потеряет статус поставщика и код П#, то из таблицы придется исключить все сведения о нем, в том числе и те, которые мы хотели бы сохранить, например, стоимость доставки из Самары (аномалия удаления). Для приведения отношения к 3НФ необходимо выделить в отдельную таблицу не ключевые атрибуты, функционально полно зависящие друг от друга. Таблицу ПОСТАВЩИК1 разделим на две таблицы: ПОСТАВЩИК и ДОСТАВКА. ПОСТАВЩИК ДОСТАВКА П# Имя постащика Город П1 П2 П3 Восход Заря Салют Тула Самара Тула Город Тариф Тула Самара 10 15 Теперь эти отношения находятся в 3НФ и в БД можно включать сведения о новых городах и тарифах даже в том случае, когда из этих городов пока не делается никаких поставок. Между полученными отношениями существует связь типа 1:М (на стороне 1 находится отношение ДОСТАВКА). Связь осуществляется через атрибут Город. Таким образом, исходная таблица ПОСТАВКИ2 разделилась на три таблицы: ПОСТАВЩИК, ДОСТАВКА, ПОСТАВКИ. Каждая из таблиц находится в 3НФ. Эти таблицы можно рассматривать в качестве модели данных реляционной БД. 9. Операции на реляционной БД Для полученной модели данных, состоящей из трех нормализованных отношений, составим запросы, запишем их в виде формул реляционной алгебры (п.6) и получим результаты. При составлении запросов и вычислении формул следует помнить о том, что результатом выполнения любой операции над отношением является новое отношение, к которому, в свою очередь, можно применить любую операцию реляционной алгебры. Эта важная идея создания новых таблиц на основе старых позволяет составлять запросы к БД любой сложности. Запрос 1. Сообщить название фирмы-поставщика П1. Мы получим ответ на этот запрос, если из таблицы ПОСТАВЩИК выделим строку, содержащую сведения об имени поставщика П1. Это можно сделать, выполнив операцию сцепления отношения ПОСТАВЩИК с известной из запроса константой П1 (т.е. выполнив операцию сцепления отношения с одноэлементным множеством {П1}). ПОСТАВЩИК * {П1} В результате выполнения операции получим новое отношение, состоящее из единственного кортежа (т.е. таблицу, состоящую из одной строки). П1 Восход Тула Полученная строка содержит лишние сведения. Уточним ответ, применив к вновь полученному отношению операцию проекции на атрибут Имя поставщика. Полученная формула и является формулой запроса. ПОСТАВЩИК * {П1} [Имя поставщика] В результате будет выделен один столбец. Это и будет ответом на запрос. Восход Запрос 2. В каком количестве поставляются изделия И2? Искомые сведения находятся в таблице ПОСТАВКИ. Аналогично Запросу 1 применим к отношению ПОСТАВКИ операцию сцепления с известной константой И2, а затем спроектируем полученное отношение на атрибут Кол. ПОСТАВКИ * {И2} [Кол] Результаты выполнения операций. П1 П2 И2 И2 200 500 300 500 Запрос 3. Сколько изделий И2 поставляет фирма Заря? В этом запросе известными являются значения двух атрибутов: Имя поставщика - Заря и номер изделия - И2. Известные и искомые данные находятся в двух таблицах: ПОСТАВЩИК и ПОСТАВКИ, поэтому для ответа на запрос необходимо "привязать" сведения из одной таблицы к сведениям из другой таблицы. Это можно сделать, выполнив операцию сцепления отношений по атрибуту П#. Запишем формулу запроса. ПОСТАВЩИК * {Заря} * ПОСТАВКИ * {И2} [Кол] Определим результаты выполнения каждой из операций. 1.В результате выполнения операции сцепления отношения ПОСТАВЩИК с одноэлементным множеством {Заря} получим новое отношение, содержащее единственный кортеж (т.е. новую таблицу, состоящую из одной строки). П2 Заря Самара 2.Вторая операция выполняет сцепление полученного отношения, имеющего арность 3, с отношением ПОСТАВКИ, имеющим также арность 3, по одинаковому значению атрибута П#. В результате получим новое отношение арностью 5. В него войдут все кортежи сцепляемых отношений, у которых значение атрибута П# = П2. П2 Заря Самара И1 200 П2 Заря Самара И2 500 3.В результате выполнения третьей операции из полученного отношения выберутся строки, в которых значение атрибута И# равно И2. 4.И, наконец, с помощью операции проекции уточним ответ, выделив столбец Кол. П2 Заря Самара И2 500 500 Запрос 4 Какие поставщики и по какому тарифу поставляют изделие И2? Известное данное (номер изделия П2) находится в таблице ПОСТАВКИ, а искомые данные (имя поставщика и тариф) - в таблицах ПОСТАВЩИК и ДОСТАВКА. Поэтому для выработки ответа на запрос необходимо связать данные из трех таблиц. Из отношения ПОСТАВКИ по номеру поставщика с помощью операции сцепления отношения с одноэлементным множеством {И2} выделим кортежи, содержащие номера поставщиков, поставляющих изделие И2. Затем выполним сцепление полученного отношения с отношением ПОСТАВЩИК по атрибуту П#. Получим отношение, содержащее атрибут с названиями городов, из которых доставляются изделия И2. Операция сцепления полученного отношения с отношением ДОСТАВКА по атрибуту Город сформирует отношение, содержащее атрибуты с искомыми данными. Наконец, выполнив проекцию этого отношения на атрибуты Имя поставщика и Тариф, выделим искомые данные. Запишем формулу запроса. 1 2 3 4 ПОСТАВКИ * {И2} * ПОСТАВЩИК * ДОСТАВКА [Имя поставщика, Тариф] Определим результаты выполнения каждой операции (все операции в формуле и получаемые результаты пронумерованы). 1. 2. П1 И2 200 П1 И2 200 Восход Тула П2 И2 500 П2 И2 500 Заря Самара 3. 4. П1 И2 200 Восход Тула 10 Восход 10 П2 И2 500 Заря Самара 15 Заря 15 Можно заметить, что в результате последовательного выполнения нескольких операций сцепления арность отношений растет, т.е. в таблицах увеличивается количество столбцов. Рост объемов промежуточных результатов при выполнении такого запроса на ЭВМ может привести к увеличению времени выполнения каждой следующей операции, и общее время выполнения запроса может оказаться недопустимо большим. Рассмотрим другой вариант формулы запроса. Для уменьшения объемов промежуточных таблиц используем дополнительные операции проекции. Это позволит на каждом этапе отсекать ненужные столбцы и оставлять лишь те столбцы, которые необходимы для выполнения последующей операции сцепления или для вывода искомых данных. 1 2 3 4 5 6 ПОСТАВКИ * {И2} [П#] * ПОСТАВЩИК [Имя поставщика, Город] * ДОСТАВКА [Имя поставщика, Тариф] Результаты выполнения операций. 1. 2. 3. 4. П1 И2 200 П1 П1 Восход Тула Восход Тула П2 И2 500 П2 П2 Заря Самара Заря Самара 5. 6. Восход Тула 10 Восход 10 Заря Самара 15 Заря 15 Используя булевы операторы, можно строить формулы для более сложных запросов. С помощью теоретико-множественных операций объединения и вычитания множеств, можно записывать формулы для запросов на добавление и удаление кортежей отношений. 10. Языки реляционных БД 10.1. Язык, основанный на реляционной алгебре Язык данных, основанный на реляционной алгебре, был предложен Коддом. Это теоретический язык, позволяющий манипулировать данными в реляционных БД. Некоторые коммерческие языки БД основаны также на реляционной алгебре. Язык содержит девять операторов. Это операторы для выполнения операций теории множеств над отношениями: объединение, пересечение, произведение, разность отношений, а также операторы для выполнения операций, применимых только к отношениям и др. Это операции сцепления отношения с множеством (ее еще называют выборкой), сцепление двух отношений (или соединение) и операция проекции отношения на его компоненту. При рассмотрении предыдущего параграфа мы могли убедиться в том, что трех последних операций оказывается достаточно для составления разнообразных запросов к БД, связанных с поиском нужных сведений. Рассмотрим операторы языка, выполняющие эти операции. SELECT - оператор выборки, выполняет операцию сцепления отношения с множеством. Конструкция предложения SELECT такова: SELECT (выбрать) <имя таблицы> WHERE (где) <условие> GIVING TEMP (сформировать таблицу, содержащую результат). JOIN - оператор соединения отношений, выполняет операцию сцепления двух отношений. Конструкция предложения JOIN: JOIN (соединить) <Имя таблицы> AND (и) <имя таблицы> OVER (по) <имя атрибута, по которому выполняется сцепление> GIVING TEMP (сформировать таблицу, содержащую результат). PROJECT - оператор проекции, выполняет операцию проекции отношения на его компоненты. Конструкция предложения PROJECT: PROJECT (спроектировать) <имя таблицы> OVER (на) <имена атрибутов> GIVING TEMP (сформировать таблицу, содержащую результат). Предложения языка записываются в том порядке, в котором должны выполняться операции запроса. Сформированная каждым из предложений таблица (TEMP) является исходной для выполнения следующего предложения. Таблица, сформированная последним предложением запроса, содержит окончательный результат выполнения запроса (RESULT). Составленные ранее запросы запишем с помощью этих операторов. Предложения запросов следуют в том же порядке, в котором следуют операции в записанных ранее формулах. Запрос 1. Сообщить название фирмы-поставщика П1 SELECT ПОСТАВЩИК WHERE П# = 'П1' GIVING TEMP1 PROGECT TEMP1 OVER Имя поставщика GIVING RESULT Первое предложение формирует промежуточную таблицу TEMP1, второе предложение формирует результирующую таблицу RESULT. Результаты выполнения каждого предложения в точности соответствуют результатам выполнения операций, рассмотренным в предыдущем параграфе. Запрос 2. В каком количестве поставляются изделия И2? SELECT ПОСТАВКИ WHERE И# = 'И2' GIVING TEMP1 PROGECT TEMP1 OVER Кол GIVING RESULT Запрос 3. Сколько изделий И2 поставляет фирма Заря? SELECT ПОСТАВЩИК WHERE Имя поставщика = 'Заря' GIVING TEMP1 JOIN TEMP1 AND ПОСТАВКИ OVER П# GIVING TEMP2 SELECT TEMP2 WHERE И# = 'И2' GIVING TEMP3 PROGECT TEMP3 OVER Кол GIVING RESULT Запрос 4. Какие поставщики и по какому тарифу поставляют изделия И2? SELECT ПОСТАВКИ WHERE И# = 'И2' GIVING TEMP1 JOIN TEMP1 AND ПОСТАВЩИК OVER П# GIVING TEMP2 JOIN TEMP2 AND ДОСТАВКА OVER Город GIVING TEMP3 PROGECT TEMP3 OVER Имя поставщика, Тариф GIVING RESULT Мы рассмотрели лишь 3 оператора языка из 9. Рассмотренный язык обладает следующей особенностью: это процедурный язык обработки реляционных таблиц. Это означает, что в формуле запроса последовательно перечисляются все операции, которые надо выполнить над отношениями для выработки ответа. Пользователь, работающий с таким языком, должен иметь определенный уровень подготовки. Кроме того, один и тот же результат можно получить, составляя разные варианты запроса (см. п. 9, запрос 4). При этом скорость выполнения запроса и необходимые объемы памяти для хранения промежуточных результатов зависят от того, как именно составлена формула: "лучшим" или "не лучшим" способом. И здесь существенное значение имеет уровень подготовки пользователя, его опыт и навыки. Замечательным достоинством этого языка является его реляционная полнота. Это означает, что язык позволяет составить любые запросы на манипулирование данными в БД. Желание освободиться от процедурности языка, которую принято считать его недостатком, привело к созданию непроцедурных коммерческих языков реляционных БД, использующих несколько иной принцип составления запросов. Непроцедурные языки позволяют в запросе сформулировать, что нужно получить, а не как этого добиться. Тремя наиболее важными реляционными языками БД являются: SQL - Structured Query Language (структурированный язык запросов), QBE - Query By Example (запрос по образцу) и QUEL - Query Language (язык запросов). Наибольшее распространение получили первые два. 10.2. Язык SQL - общие сведения SQL был создан и впервые использован в 1981 г. С тех пор появились различные версии языка, однако основные принципы составления запросов, основные операторы и правила их выполнения практически одинаковы для всех СУБД. В настоящее время принят международный стандарт SQL - SQL-92. Этот стандарт представляет собой обобщение всех известных его реализаций. Это означает, что ядро стандарта содержит операции, применяемые практически во всех известных коммерческих версиях языка, а полный стандарт включает усовершенствования, которые некоторым производителям еще только предстоит реализовать. Из реляционной алгебры в этот стандарт включены операторы для некоторых операций над множествами, а также операторы, выполняющие над отношениями основные реляционные операции: выборку SELECT, соединение JOIN и проекцию. 10.3. Язык запросов QBE Язык SQL требует определенных навыков в составлении запросов и не может быть предложен пользователю- непрограммисту для общения с БД. Был разработан простой и наглядный графически-ориентированный язык запросов для пользователей - Query By Example (запрос по образцу), ставший важной частью коммерческих СУБД. В различных реляционных СУБД имеются свои варианты QBE, но везде запросы формулируются посредством графического представления таблиц БД. Помещая символы в определенные места в столбцах таблицы, пользователь определяет условия отбора строк из таблиц БД, формат и порядок вывода данных. Рассмотрим основные возможности QBE, опираясь на первоначально разработанный вариант языка. Пользователю предоставляются сведения об именах таблиц и именах столбцов, содержащихся в каждой из таблиц. Пользователь может вывести таблицу-образец для любой таблицы БД, указав ее имя. В первом столбце таблицы-образца выводится имя таблицы, за ним следуют имена столбцов. Под каждым именем столбца оставлено пустое пространство, позволяющее пользователю вводить запрос. Проиллюстрируем использование таблиц-образцов на примерах, составив на QBE два из ранее рассмотренных запросов. Запрос 1 Сообщить название фирмы-поставщика П1. Поскольку известные и искомые данные хранятся в одной таблице ПОСТАВЩИК, нужно вывести образец для этой таблицы. ПОСТАВЩИК П# Имя поставщика Город П1 Р. Условием отбора является равенство значения, находящегося в столбце П# базовой таблицы, введенному значению П1. В столбец П# помещаем условие отбора даных - номер поставщика П1. Для того, чтобы указать, что именно мы хотим вывести, в столбец Имя поставщика помещаем команду Р. (print - распечатать). Пустые столбцы можно удалить. Результат запроса будет представлен в виде так называемой ответной или целевой таблицы, содержащей все строки из базовой таблицы, в которых номер поставщика равен П1. ПОСТАВЩИК П# Имя поставщика П1 Восход Запрос 2 Сколько изделий И2 поставляет фирма Заря? Для ответа на этот запрос необходимо использовать данные из двух таблиц. Для того чтобы указать системе, по каким столбцам устанавливается связь между таблицами, в соответствующих столбцах таблиц-образцов надо напечатать так называемый элемент-пример. В качестве элемента-примера можно использовать любую последовательность символов, выделив их символом подчеркивания. Важно, чтобы в обеих таблицах был указан один и тот же элемент-пример. Этим мы обеспечим выполнение операции сцепления (соединения) этих таблиц. ПОСТАВЩИК П# Имя поставщика Город __П Заря ПОСТАВКИ П# И# Кол __П И2 Р. Ответная таблица будет содержать все необходимые данные, пустые столбцы не выводятся. Имя поставщика И# Кол Заря И2 500 В запросе можно указывать более сложные условия отбора, используя операторы сравнения <, >, <=, >=, <>. Условия, указанные в разных строках столбца таблицы-образца, воспринимаются системой как операнды логической операции ИЛИ, а условия, указанные в разных столбцах одной и той же таблицы воспринимаются системой как операнды логической операции И. Запрос 3 Какие изделия поставляются в количестве 200 или 300 единиц? ПОСТАВКИ П# И# Кол Р. 200 300 И# Кол И1 И2 И3 И1 300 200 200 200 Запрос 4 Какие изделия поставляются поставщиком П2 в количестве 500 единиц. ПОСТАВКИ П# И# Кол П# И# Кол П2 Р. 500 П2 И2 500 Языки QBE, реализованные в коммерческих СУБД, обладают многочисленными дополнительными средствами манипулирования данными.