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

Вид материала

Содержание

Подобный материал:

1 ... 5 6 7 8 9 10 11 12 ... 23

Эти формы подчиняются правилу вложенности по возрастанию номеров: если отношение находится в 4НФ, то оно будет соответствовать 3НФ, 2НФ и 1НФ. И наоборот: если отношение находится в 1НФ, но не в 2НФ, то оно не будет соответствовать ни 3НФ, ни 4НФ.

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

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

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

Определение 1. Функциональная зависимость

В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y: R.X (r) R.Y.

Определение 2. Полная функциональная зависимость

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

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

Определение 3. Транзитивная функциональная зависимость

Функциональная зависимость R.X  R.Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости R.X  R.Z и R.Z  R.Y и отсутствует функциональная зависимость R.Z --> R.X. (При отсутствии последнего требования мы имели бы "неинтересные" транзитивные зависимости в любом отношении, обладающем несколькими ключами.)

Транзитивная зависимость возникает при дублировании данных в одной таблице. Если А, В, С- атрибуты одной таблицы и С зависит от В, а В.от А, то говорят, что С транзитивно зависит от А. (А-уникальный ключ)

А1

А2

А) б)

А) связи между объектами с транзитивной зависимостью

Б) ) связи между объектами без транзитивной зависимости

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

Т

.е если А В и В С, то А С.

Для преобразования из 2НФ в 3НФ таблицу-отношение разделяют на две или более проекции так, чтобы конечные поля атрибуты в цепочках транзитивной зависимости вынести в отдельные таблицы, связав разделившиеся части таблицы внешними ключами по полям-атрибутам, находящимися внутри цепочек транзитивной зависимости.

ПРИМЕР

( Ф.И.О., № зач.книжки, Группа, Факультет, Специальность, Кафедра)

Первичным ключом отношения является № зач.книжки.

Рассмотрим ФЗ.

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

Кроме того, выпускающая кафедра однозначно определяет факультет, на котором обучается студенты, выпускаемые на данной кафедре.

Но если предположить, что одну специальность могут выпускать несколько кафедр, то специальность не определяет выпускающую кафедру.В этом случае у нас есть следующие Ф.З.

№

зач. Книжки ФИО

№

зач. Книжки Группа

№

зач. Книжки Факультет

№

зач. Книжки Специальность

№

зач. Книжки Кафедра

Г

руппа Факультет

Г

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

Г

руппа Кафедра

Кафедра Факультет

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

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

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

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

Определение 4. Неключевой атрибут

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

Определение 5. Взаимно независимые атрибуты

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

1 НФ

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

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

Вторая нормальная форма

Рассмотрим следующий пример схемы отношения:

СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ

(СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН)

Первичный ключ:

СОТР_НОМЕР, ПРО_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР  СОТР_ЗАРП

СОТР_НОМЕР  ОТД_НОМЕР

ОТД_НОМЕР  СОТР_ЗАРП

СОТР_НОМЕР, ПРО_НОМЕР  СОТР_ЗАДАН

Как видно, хотя первичным ключом является составной атрибут СОТР_НОМЕР, ПРО_НОМЕР, атрибуты СОТР_ЗАРП и ОТД_НОМЕР функционально зависят от части первичного ключа, атрибута СОТР_НОМЕР. В результате мы не сможем вставить в отношение СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ кортеж, описывающий сотрудника, который еще не выполняет никакого проекта (первичный ключ не может содержать неопределенное значение). При удалении кортежа мы не только разрушаем связь данного сотрудника с данным проектом, но утрачиваем информацию о том, что он работает в некотором отделе. При переводе сотрудника в другой отдел мы будем вынуждены модифицировать все кортежи, описывающие этого сотрудника, или получим несогласованный результат. Такие неприятные явления называются аномалиями схемы отношения. Они устраняются путем нормализации.

Определение 6. Вторая нормальная форма (в этом определении предполагается, что единственным ключом отношения является первичный ключ)

Отношение R находится во второй нормальной форме (2NF) в том и только в том случае, когда находится в 1NF, и каждый неключевой атрибут полностью зависит от первичного ключа.

Можно произвести следующую декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ в два отношения СОТРУДНИКИ-ОТДЕЛЫ и СОТРУДНИКИ-ПРОЕКТЫ:

СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР)

Первичный ключ:

СОТР_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР  СОТР_ЗАРП

СОТР_НОМЕР  ОТД_НОМЕР

ОТД_НОМЕР  СОТР_ЗАРП

СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН)

Первичный ключ:

СОТР_НОМЕР, ПРО_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР, ПРО_НОМЕР  CОТР_ЗАДАН

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

Третья нормальная форма

Рассмотрим еще раз отношение СОТРУДНИКИ-ОТДЕЛЫ, находящееся в 2NF. Заметим, что функциональная зависимость СОТР_НОМЕР  СОТР_ЗАРП является транзитивной; она является следствием функциональных зависимостей СОТР_НОМЕР  ОТД_НОМЕР и ОТД_НОМЕР  СОТР_ЗАРП. Другими словами, заработная плата сотрудника на самом деле является характеристикой не сотрудника, а отдела, в котором он работает (это не очень естественное предположение, но достаточное для примера).

В результате мы не сможем занести в базу данных информацию, характеризующую заработную плату отдела, до тех пор, пока в этом отделе не появится хотя бы один сотрудник (первичный ключ не может содержать неопределенное значение). При удалении кортежа, описывающего последнего сотрудника данного отдела, мы лишимся информации о заработной плате отдела. Чтобы согласованным образом изменить заработную плату отдела, мы будем вынуждены предварительно найти все кортежи, описывающие сотрудников этого отдела. Т.е. в отношении СОТРУДИКИ-ОТДЕЛЫ по-прежнему существуют аномалии. Их можно устранить путем дальнейшей нормализации.

Определение 8. Третья нормальная форма. (Снова определение дается в предположении существования единственного ключа.)

Отношение R находится в третьей нормальной форме (3NF) в том и только в том случае, если находится в 2NF и каждый неключевой атрибут нетранзитивно зависит от первичного ключа.

Можно произвести декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ в два отношения СОТРУДНИКИ и ОТДЕЛЫ:

СОТРУДНИКИ (СОТР_НОМЕР, ОТД_НОМЕР)

Первичный ключ:

СОТР_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР  ОТД_НОМЕР

ОТДЕЛЫ (ОТД_НОМЕР, СОТР_ЗАРП)

Первичный ключ:

ОТД_НОМЕР

Функциональные зависимости:

ОТД_НОМЕР  СОТР_ЗАРП

Каждое из этих двух отношений находится в 3NF и свободно от отмеченных аномалий.

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

Нормальная форма Бойса-Кодда

Рассмотрим следующий пример схемы отношения:

СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, СОТР_ИМЯ, ПРО_НОМЕР, СОТР_ЗАДАН)

Возможные ключи:

СОТР_НОМЕР, ПРО_НОМЕР

СОТР_ИМЯ, ПРО_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР  CОТР_ИМЯ

СОТР_НОМЕР  ПРО_НОМЕР

СОТР_ИМЯ  CОТР_НОМЕР

СОТР_ИМЯ  ПРО_НОМЕР

СОТР_НОМЕР, ПРО_НОМЕР  CОТР_ЗАДАН

СОТР_ИМЯ, ПРО_НОМЕР  CОТР_ЗАДАН

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

В соответствии с определением 7~ отношение СОТРУДНИКИ-ПРОЕКТЫ находится в 3NF. Однако тот факт, что имеются функциональные зависимости атрибутов отношения от атрибута, являющегося частью первичного ключа, приводит к аномалиям. Например, для того, чтобы изменить имя сотрудника с данным номером согласованным образом, нам потребуется модифицировать все кортежи, включающие его номер.

Определение 10. Детерминант - любой атрибут, от которого полностью функционально зависит некоторый другой атрибут.

Определение 11. Нормальная форма Бойса-Кодда

Отношение R находится в нормальной форме Бойса-Кодда (BCNF) в том и только в том случае, если каждый детерминант является возможным ключом.

Очевидно, что это требование не выполнено для отношения СОТРУДНИКИ-ПРОЕКТЫ. Можно произвести его декомпозицию к отношениям СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ:

СОТРУДНИКИ (СОТР_НОМЕР, СОТР_ИМЯ)

Возможные ключи:

СОТР_НОМЕР

СОТР_ИМЯ

Функциональные зависимости:

СОТР_НОМЕР  CОТР_ИМЯ

СОТР_ИМЯ  СОТР_НОМЕР

СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН)

Возможный ключ:

СОТР_НОМЕР, ПРО_НОМЕР

Функциональные зависимости:

СОТР_НОМЕР, ПРО_НОМЕР  CОТР_ЗАДАН

Возможна альтернативная декомпозиция, если выбрать за основу СОТР_ИМЯ. В обоих случаях получаемые отношения СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ находятся в BCNF, и им не свойственны отмеченные аномалии.

Четвертая нормальная форма

Рассмотрим пример следующей схемы отношения:

ПРОЕКТЫ (ПРО_НОМЕР,ПРО_СОТР, ПРО_ЗАДАН)

Отношение ПРОЕКТЫ содержит номера проектов, для каждого проекта список сотрудников, которые могут выполнять проект, и список заданий, предусматриваемых проектом. Сотрудники могут участвовать в нескольких проектах, и разные проекты могут включать одинаковые задания.

Каждый кортеж отношения связывает некоторый проект с сотрудником, участвующим в этом проекте, и заданием, который сотрудник выполняет в рамках данного проекта (мы предполагаем, что любой сотрудник, участвующий в проекте, выполняет все задания, предусмотренные этим проектом). По причине сформулированных выше условий единственным возможным ключем отношения является составной атрибут ПРО_НОМЕР, ПРО_СОТР, ПРО_ЗАДАН, и нет никаких других детерминантов. Следовательно, отношение ПРОЕКТЫ находится в BCNF. Но при этом оно обладает недостатками: если, например, некоторый сотрудник присоединяется к данному проекту, необходимо вставить в отношение ПРОЕКТЫ столько кортежей, сколько заданий в нем предусмотрено.

Определение 12. Многозначные зависимости

В отношении R (A, B, C) существует многозначная зависимость R.A   R.B в том и только в том случае, если множество значений B, соответствующее паре значений A и C, зависит только от A и не зависит от С.

В отношении ПРОЕКТЫ существуют следующие две многозначные зависимости:

ПРО_НОМЕР   ПРО_СОТР

ПРО_НОМЕР   ПРО_ЗАДАН

Легко показать, что в общем случае в отношении R (A, B, C) существует многозначная зависимость R.A   R.B в том и только в том случае, когда существует многозначная зависимость R.A   R.C.

Дальнейшая нормализация отношений, подобных отношению ПРОЕКТЫ, основывается на следующей теореме:

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

Отношение R (A, B, C) можно спроецировать без потерь в отношения R1 (A, B) и R2 (A, C) в том и только в том случае, когда существует MVD A   B | C.

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

Определение 13. Четвертая нормальная форма

Отношение R находится в четвертой нормальной форме (4NF) в том и только в том случае, если в случае существования многозначной зависимости A   B все остальные атрибуты R функционально зависят от A.

В нашем примере можно произвести декомпозицию отношения ПРОЕКТЫ в два отношения ПРОЕКТЫ-СОТРУДНИКИ и ПРОЕКТЫ-ЗАДАНИЯ:

ПРОЕКТЫ-СОТРУДНИКИ (ПРО_НОМЕР, ПРО_СОТР)

ПРОЕКТЫ-ЗАДАНИЯ (ПРО_НОМЕР, ПРО_ЗАДАН)

Оба эти отношения находятся в 4NF и свободны от отмеченных аномалий.

Пятая нормальная форма

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

Рассмотрим, например, отношение

СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМЕР, ОТД_НОМЕР, ПРО_НОМЕР)

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

Поэтому отношение находится в 4NF. Однако в нем могут существовать аномалии, которые можно устранить путем декомпозиции в три отношения.

Определение 14. Зависимость соединения

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

Определение 15. Пятая нормальная форма

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

Введем следующие имена составных атрибутов:

СО = {СОТР_НОМЕР, ОТД_НОМЕР}

СП = {СОТР_НОМЕР, ПРО_НОМЕР}

ОП = {ОТД_НОМЕР, ПРО_НОМЕР}

Предположим, что в отношении СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ существует зависимость соединения:

* (СО, СП, ОП)

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

СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, ОТД_НОМЕР)

СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР)

ОТДЕЛЫ-ПРОЕКТЫ (ОТД_НОМЕР, ПРО_НОМЕР)

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

Реляционная алгебра

Предложив реляционную модель данных, Э.Ф.Кодд создал и инструмент для удобной работы с отношениями – реляционную алгебру. Каждая операция этой алгебры использует одну или несколько таблиц (отношений) в качестве ее операндов и продуцирует в результате новую таблицу, т.е. позволяет "разрезать" или "склеивать" таблицы (рис. 4.3).

Рис. 4.3. Некоторые операции реляционной алгебры

Созданы языки манипулирования данными, позволяющие реализовать все операции реляционной алгебры и практически любые их сочетания. Среди них наиболее распространены SQL (Structured Query Language – структуризованный язык запросов) и QBE (Quere-By-Example – запросы по образцу). Оба относятся к языкам очень высокого уровня, с помощью которых пользователь указывает, какие данные необходимо получить, не уточняя процедуру их получения.

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

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

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

объединения отношений;
пересечения отношений;
взятия разности отношений;
прямого произведения отношений.

Специальные реляционные операции включают:

ограничение отношения;
проекцию отношения;
соединение отношений;
деление отношений.

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

5.1.1. Общая интерпретация реляционных операций

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

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

UNION (объединение). Объединяет все строки из двух таблиц. Чтобы оператор UNION можно было использовать, таблицы должны иметь одинаковые свойства по атрибутам (столбцы и домены должны быть идентичны). Когда одна или более таблиц совместно используют одни и те же столбцы и домены, говорят, что они совместимы по объединению (union-compatible). Операция объединения отношений – выполняется над двумя односхемными таблицами-отношениями. Результатом объединения является построенная по той же схеме таблица-отношение, содержащая все кортежи первой таблицы и все кортежи второй таблицы-отношения. При этом кортежи-дубликаты в итоговой таблице устраняются

Операция пересечения двух отношений производит отношение, включающее все кортежи, входящие в оба отношения-операнда.

INTERSECT (пересечение). Выводит только строки, которые встречаются в обеих таблицах. Таблицы должны быть совместимы по объединению. Например, вы не можете применить оператор INTERSECT, если один из атрибутов имеет числовой тип, а другой - символьный тип. Операция пересечения отношений – выполняются также над двумя односхемными таблицами-отношениями. Результатом является таблица-отношение, построенная по той же схеме и содержащая только те кортежи первого отношения, которые входят в состав кортежей второй таблицы

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

DIFFERENCE (разность). Выводит все строки первой таблицы, которые отсутствуют в другой, т. е. производит вычитание одной таблицы из другой. Таблицы [ должны быть совместимы по объединению. Операция взятия разности отношений – выполняется над двумя односхемными отношениями. Результатом является отношение, построенное по той схеме и содержащее только те кортежи первой таблицы, которых нет в составе кортежей второй таблицы;

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

PRODUCT (произведение). Выводит все возможные пары строк из двух таблиц — этот оператор называется также декартовым произведением. Следовательно, если в одной таблице имеется 6 строк, а в другой 3 строки, то оператор выдаст список, состоящий из 6*3=18 строк Операция прямого произведения отношений – выполняется над отношениями с разными схемами. Результатом является отношение, схема которого включает все поля первой и все поля второй таблицы-отношения. Количество кортежей результирующей таблицы равно произведению количества кортежей первой таблицы на количество кортежей второй таблицы.

Вид обследования	Дата
Грипп	10.03
ОРЗ	11,03

Пример: Первая вторая таблица

таблица

	Ф.И.О.
	Иванов И.И.
	Петров П,П.

Ф.И.О.	Вид обследования	Дата
Иванов И.И.	Грипп	10.03
Иванов И.И.	ОРЗ	11,03
Петров П,П.	Грипп	10.03
Петров П,П.	ОРЗ	11,03

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

PROJECT (проекция). Выводит все значения выбранных атрибутов. Иначе говоря , оператор выводит вертикальное подмножество таблицы.

проекция отношения также выполняется над одной таблицей отношением, путем операций реляционной алгебры создается новая таблица в результате исключения столбцов из существующей таблицы ( в отличие от других операций реляционной алгебры, операция создания проекций не требует специального ключевого слова или символа. Мы просто указываем исходную таблицу, а после нее в квадратных скобках перечисляем те столбцы, которые мы хотим оставить SELECT [Имя старой таблицы].[Наименование столбца] INTO Имя новой таблицы

FROM [Имя старой таблицы];

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

JOIN (соединение) подразделяются на запросы на основе внутреннего соединения (INNER JOIN) (айнер джойн) и запросы на основе правого или левого внешнего соединения (RIGHT JOIN и LEFT JOIN). Запросы на выборку, строящиеся на основе внутреннего соединения, реализуют операцию соединения реляционных таблиц. При реализации операции соединения двух таблиц выделяется поле соединения, которое должно быть одинакового типа в соединяемых таблицах. Результатом соединения является новая таблица, содержащая все поля, или часть полей первой таблицы и все или часть полей второй таблицы.

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

Сотрудники

Фамилия	Должность	Кабинет	Телефон
Егорова	Секретарь	101-а	1101
Иванов	Начальник	101	1101
Петров	Инженер	110	1110
Сидоров	Экономист	110	1110
Фетисов	Контроллер	105	1105

Исполнение

Фамилия	Должность	Кабинет	Фамилия
Егорова	Секретарь	101-а	Андреев
Иванов	Начальник	101	Сидоров
Петров	Инженер	110	Сергеев
Сидоров	Экономист	110	Иванов
Фетисов	Контроллер	105	Иванов
Фамилия	Должность	Кабинет	Иванов

Фактическое исполнение (внутреннее соединение)

SELECT Сотрудники. Исполнение. Пер.№. Исполнение. Наименование_дата. Исполнение. Дата

FROM Сотрудники INNER JOIN Исполнение ON Сотрудники. Фамилия=Исполнение.Фамилия;

FROM –перечисление таблиц и запросов, на которых основывается оператор SELECT

Фамилия	Должность	Кабинет	Телефон	Пер.№	Наименование д-та	Дата
Сидоров	Экономист	110	1101	15/нс	Отчет	3003,98
Иванов	Начальник	101	1110	22/3	Приказ	01,02,03
Иванов	Начальник	101	1105	23/2	Приказ о наказании	01,05,03
Иванов	Начальник	101	1110	И-23	Приказ о назначении	01,02,03

Левое внешнее соединение

SELECT Сотрудники. Исполнение. Пер.№. Наименование_д-та. Исполнение. Дата

FROM Сотрудники LEFT JOIN Исполнение ON Сотрудники Фамилия=Исполнение. Фамилия;

Фамилия	Должность	Кабинет	Телефон	Пер.№	Наименование д-та	Дата
Егорова	Секретарь	101-а	1101
Иванов	Начальник	101	1101	22/3	Приказ	01,02,03
Иванов	Начальник	110	1110	23/2	Приказ о наказании	01,05,03
Иванов	Начальник	110	1110	И-23	Приказ о назначении	01,02,03
Петров	Инженер	101	1110
Сидоров	Экономист	110	1101	15/нс	отчет	30,02,02
Фетисов	Контроллер	105	1105

Правое внешнее соединение SELECT Исполнение. Пер.№. Наименование_д-та. Исполнение. Дата. Сотрудники.

FROM Сотрудники RIGHT JOIN Исполнение ON Сотрудники Фамилия=Исполнение. Фамилия;

Пер.№	Наименование д-та	Дата	Фамилия	Должность	Кабинет	Телефон
15/нс	Отчет	3003,98	Сидоров	Экономист	110	1101
22/3	Приказ	01,02,03	Иванов	Начальник	101	1110
23/2	Приказ о наказании	01,05,03	Иванов	Начальник	101	1105
И-23	Приказ о назначении	01,02,03	Иванов	Начальник	101	1110

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

Для правого объединения упор делается на анализ связей от второй таблицы (от Исполнения, чтобы просмотреть , какие документы исполнены и какими сотрудниками, зпаписи о которых находятся в таблице Сотрудники)

DIVIDE (деление) (дивайд). Требует использования таблицы, состоящей из одного столбца, и второй таблицы, состоящей из двух столбцов. деление отношений – выполняется над двумя таблицами-отношениями, первая из которых называется делимым, а вторая делителем. Схема итоговой таблицы содержит только те поля таблицы-делимого, которых нет во второй таблице делителе

.

Операторы языка SQL

При записи операторов рекомендуется придерживаться следующих правил:

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

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

Опрератор	Пояснение
GREATE TABLE	Создать таблицу
DROPTABLE	Удалить таблицу
ALTER TABLE	Изменить таблицу
GREATE VIEW	Создать представление
ALTER VIEW	Изменить представление
DROP VIEW	Удалить представление

Операторы манипулирования данными

DELETE	Удаляет одну или несколько строк, соответствующих условиям фильтрации, из базовой таблицы
INSERT	Вставляет одну строку в базовую таблицу
UPDATE	Обновляет значения одного или нескольких столбцов в одной или нескольких строках, соответствующим условиям фильтрации

Язык запросов, для выборки информации

SELECT

Выбирает строки, оператор, позволяющий сформировать результирующую таблицу, соответствующему запросу

Управление транзакциями

COMMIT	Завершить транзакцию – завершить обработку информации, объединенную в транзакцию
ROLLBACK	Откатить транзакцию – отменить изменения, приведенные в ходе выполнения транзакции
SAVEPOINT	Сохранить промежуточную точку выполнения транзакции – сохранить промежуточное состояние БД, пометить его для того, чтобы можно было в дальнейшем к нему вернуться

Администрирование данных

ALTER DATABASE	Изменит набор основных объектов в базе данных, ограничений, касающихся всей
ALTER DBAREA	Изменить ранее созданную область хранения
ALTER PASSWORD	Изменить пароль для всей базы данных
CREATE DATEBASE	Создать новую базу данных
CREATE DBAREA	Создать новую область хранения и сделать ее доступной для размещения данных
DROP DATEBASE	удалить существующую БД
DROP DBAREA	Удалить существующую область хранения (если в ней на настоящий момент не располагаются активные данные)
GRANT	Предоставить права доступа на ряд действий над некоторым объектом БД
REVOKE	Лишить прав доступа к некоторому объекту или некоторым действиям над объектом

Инструкция SQL начинается с команды – ключевого слова, описывающего действие выполняемое инструкцией. Типичными являются команды CREATE (создать), INSERT (добавить), SELECT (выбрать), DELETE(удалить). Следом за командой указывается одно или несколько предложений. Предложение описывает данные с которыми должна работать инструкция, или уточняет действие, выполняемое инструкцией. Предложения в инструкции делятся на обязательные и необязательные. Каждое предложение начинается с ключевого слова, например – WHERE (где), FROM (откуда), INTO (куда). У каждого объекта в базе данных есть уникальное имя. Имена используются в инструкциях SQL и указывают, над каким объектом должно выполняться действие. Имена могут содержать от 1 до 18 символов, начинаться с буквы и не должно включать пробелы.

Типы данных

Целые числа (INT (INTEGER), SMALLINT) – в столбцах такого типа данных хранятся данные о количестве и возрасте сотрудников, идентификаторы

Десятичные числа (NUMERIC, DECIMAL) – хранятся числа, имеющие дробную часть с фиксированным количеством знаков после запятой, например курсы валют и проценты.

Числа с плавающей запятой (REAL, FLOAT). Числа с плавающей запятой представляют больший диапазон действительных значений, чем десятичные числа.

Строки символов постоянной длины (CHAR) – хранятся имена и фамилии, адреса и т.д.

Строки символов переменной длины (VARCHAR) – хранят символьные строки, длина которых изменяется в заданном диапазоне – названия фирм может быть разной.

Денежные величины (MONEY, SMALLMONEY)

Дата и время (DATETIME, SMALLDATETIME)

Булевы величины (BIT) – столбцы такого типа данных позваляют хранить логические значения True (1) и False (0)

Длинный текст (TEXT) – позволяет хранить в БД целые документы

Значения NULL

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

Ограничение целостности

Первичный и внешний ключи таблицы

Если по столбцу строится первичный ключ - столбцу должен быть приписан атрибут PRIMARY KEY, причем столбец не может хранить NULL

Дисциплина INTEGER NOT NULL PRIMARY KEY или PRIMARY KEY (Дисциплина, Студент)

Столбцы внешнего ключа (FOREIGN KEY)могут содержать значения типа NULL . задать внешний ключ можно как при создании, так и при изменении таблиц,

Внешний ключ можно ввести 2 способами:

А) Если внешний ключ единственный атрибут, за его именем и типом вводится описание того, что он «ссылается» на другой атрибут (который должен быть первичным ключом) какой-то таблицы

REFERENCES<таблица>(<атрибут>)

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

FOREIGN KEY <атрибуты> REFERENCES<таблица>(<атрибуты>)

Запросы в SQL

Команда SELECT

Пример. Сформулируем запрос к отношению

Фильмы (название, год, продолжительность, цвет, студия, продюсер)

Обо всех фильмах выпущенных студией Дисней в 1990г

SELECT*

FROM Фильмы

WHERE студия = «Дисней» AND год = 1990;

Звучит так “выбрать из ….., где….»

SELECT – показывает, какие атрибуты кортежей, удовлетворяющих условию, становятся частью ответа. Знак * указывает, что порождается полный кортеж.

FROM указывает на отношение, к которому принадлежит запрос

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

Проекция в SQL

Некоторые компоненты выбранных кортежей можно устранить т.е. проецировать порожденное запросом отношение на некоторые из его атрибутов.

Пример: Нужно получить только название фильма и его продолжительность

SELECT название, продолжительность

FROM Фильмы

WHERE студия = «Дисней» AND год = 1990;

Иногда нужно построить отношение, заголовки столбцов которого отличаются от атрибутов отношения, упомянутого в пункте FROM. Тогда за именем атрибута ставится слово AS и псевдоним, представляющий имя. Которое появится в результирующем отношении. AS применяется по выбору

SELECT название AS имя, продолжительность AS время

FROM Фильмы

WHERE студия = «Дисней» AND год = 1990;

Выбор в SQL

Применяются логические операторы AND, OR, NOT

Пример: Выбрать черно-белые фильмы, снятые после 1970г

SELECT название

FROM Фильмы

WHERE год = 1990 AND NOT Цвет

Сравнение строк

SQL позволяет сравнивать строки на основании их совпадения с простым образцом. Формой сравнение может быть выражение s LIKE p где s-строка, а p- образец

Пример: Название фильма начинается со слова «Звезда» и 2-го слова состоящего из 4 букв

SELECT название

FROM Фильмы

WHERE название LIKE «звезда__ _ _ _»

Этот запрос выявляет атрибут названия фильма, имеющий значение длиной в 11 символов. 7- это звезда и пробел, а последние 4 могут быть любыми, т.л. любая последовательность из 4 символов соответствует ----

Упорядочение вывода

Порождаемые запросом кортежи можно упорядочить на основе значения любого атрибута при помощи оператора ORDER BY

Пример: Получить список фильмов студии Дисней, выпуска 1990 г. В котором они упорядочены по длительности, а равные по длительности фильма – по алфавиту

SELECT *

FROM Фильмы

WHERE студия = «Дисней» AND год = 1990

ORDER BY продолжительность, название

Произведение и объединение

В SQL есть простой способ соединения отношений в одном запросе – их нужно перечислить в пункте FROM. Тогда пункты SELECT и WHERE могут ссылаться на любой атрибут этих отношений.

Пример. Узнать имя продюсера фильма «Звезда». Для этого необходимо 2 отношения

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

Киноработники (фамилия, адрес, сертификат)

Используя два отношения, имеем:

SELECT Фамилия

FROM Фильмы, киноработники

WHERE название = «Звезда» AND сертификат = № сертификата продюсера

Этот запрос требует рассмотреть все пары кортежей, удовлетворяющие условию, содержащееся в WHERE:

1 атрибут Название должен иметь значение «Звезда»

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

Объединение, пересечение и разность

Найти имена и адреса всех женщин- кинозвезд, которые являются администраторами и имеют доход не менее 10 млн. дол. Используя отношения

Киноработники (фамилия, адрес, сертификат, доход)

Кинозвезды (фамилия, пол, дата)

Пример

(SELECT Фамилия, адрес

FROM Кинозвезды

WHERE пол = «ж»)

INTERSECT

(SELECT Фамилия, адрес

FROM Киноработники

WHERE доход>1000000)

Т.к. обе схемы совпадают, пересечения можно вычислить с помощью оператора INTERSECT

Пример

Разность между множествами людей дает имена и адреса кинозвезд, не являющихся администраторами, независимо от их дохода

(SELECT Фамилия, адрес FROM Кинозвезды)

EXCEPT

SELECT Фамилия, адрес FROM Киноработники

Пример

Найти все названия и годы выпуска фильмов, входящих в отношения.

Фильмы (название, год, продолжительность, цвет, студия, № сертификата продюсера

Звездные фильмы( Название фильма, год выпуска, имя звезды)

(SELECT Название, год FROM фильмы)

UNION

(SELECT Название фильма AS название, Год выпуска AS год FROM Звездные фильмы)

Результатом будут все фильмы, упомянутые в любом из этих отношений с атрибутами результирующего отношения Название и Год

Операторы агрегации

1 SUM

2 AVG

3 MIN

4 MAX

5 COUNT – число значений (включая дубликаты)

Пример: Посчитать число кортежей в отношении Киноработники

SELECT COUNT (*)

FROM Киноработники

Группирование

Найти сумму продолжительности всех фильмов каждой студии в отношении Фильмы (название, год, продолжительность, цвет, студия, № сертификата продюсера)

SELECT студия, SUM(продолжительность)

FROM Фильмы

GROUP BY студия

Предложение HAVING

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

Пример: определим общую продолжительность фильмов только для продюсеров, выпустивщих хотя бы один свой фильм до 1930 г.

SELECT название SUM(продолжительность)

FROM Фильмы, киноработники

WHERE сертификат = № сертификата продюсера

GROUP BY название

HAVING MIN (год)<1930;

Операция удаления

Предложение удаления состоит из следующих компонентов:

ключевого слова DELETE FROM
имени отношения, например R
ключевого слова WHERE
условия

Форма записи удаления DELETE FROM R <условие>

Пример Удалите из отношения Справка информацию о том, что Иванов играл в фильме «война» в 1970 г.

DELETE FROM Справка

WHERE Название = «война» AND

Год выпуска = 1970 AND

Имя звезды = «Иванов»

Операции обновления

Ключевое слово UPDATE
имя отношения, например R
ключевого слова SET
Список формул, каждая из которых устанавливает равенство атрибута отношения R со значением выражения или постоянной
ключевое слово WHERE
условие

Форма записи операции обновления:

UPDATE R SET <приписывание новых значений> WHERE<условие>

Blog

Содержание

5.1.1. Общая интерпретация реляционных операций