1.1.1 Реляционная модель

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

Реляционная модель данных имеет следующие свойства:

Каждый элемент таблицы - один элемент данных.

Все поля в таблице являются однородными, т.е. имеют один тип.

Каждое поле имеет уникальное имя.

Одинаковые записи в таблице отсутствуют.

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

Реляционная таблица -- множество отношений содержащих всю необходимую информацию о предметной области.

Отношение реляционной БД в зависимости от содержания подразделяется на два класса:

объектные;

связанные.

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

Таблица связи хранит ключи двух или более таблиц объектов и таким образом устанавливаются связи между таблицами объектов, каждая строка связи содержит внешние ключи и характеристики связей.

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

Отличительный признак объекта:

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

Пример:

СТУДЕНТ (СТ#, ФИО, Группа, Адрес);

ПРЕДМЕТ (П#, Название, Вид контроля);

ИЗУЧАЕТ (СТ#, П#, Оценка).

Допустимые и недопустимые связи.

Пример:

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

Три таблицы:

Связи:

Сотрудники - Проекты. Сотрудники - Отдел.

Для логической завершенности не хватает третьей связи "отдел - проекты", которая отражает какое подразделение над чем работает. Связь содержится в таблице, но косвенно через сотрудников, выделим её явно. С появлением 6-ой таблицы появляется противоречие. Причиной служит то, что один и тот же факт хранится в двух разных местах, косвенно -- через исполнителя и явно (С - П) и явно в дополнительной шестой таблице. Вывод: связи между объектами не могут замыкаться. Недоразумение в этом примере вызвано нечеткой формулировкой задачи: отделы как таковые не работают над проектом, они работают над проектом тогда и только тогда, когда над проектом работает хотя бы один сотрудник. Две связи допустимы, а третья запрещена.

Если убрать связь СОТРУДНИКИ - Проекты, то изменится постановка задачи, тогда проекты

закрепляются за отделами, и все сотрудники должны будут работать над проектом.

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

Реляционные модели связей не могут замыкаться, какие связи запрещены, а какие разрешены, зависит от постановки задачи. Для двух объектов возможна одна связь, для трех - две, а для n - (n-1). Для модели из n объектов необходимо (2n-1) объектов.

Работа с БД подразумевает задание и выполнение запросов.

Пример:

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

реляционной алгебре;

реляционном исчислении.

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

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

Реляционная модель кроме РА может также включать операции реляционного присваивания Target := Source, где левые и правые части -- реляционные выражения, представляющие совместимые по типу отношения. Target -- базовое отношение.

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

Замечание: язык РА не применяется. Используется язык реляционного исчисления.

Исчисление -- это знаковая система, в которой имеется некоторое количество исходных объектов и некоторое количество правил построения объектов из исходных и уже построенных. Дедуктивная система.

Были созданы различные разновидности языков исчисления предикатов, оно называлось реляционным исчислением.

Рассмотрим два языка, широко используемых в СУБД: SQL и QBE.

Язык Структурных Запросов (SQL)

SQL разработан фирмой IBM. Одобрен в качестве стандарта для больших и малых ЭВМ. Если D-Base ориентирована на операции с данными в виде записи, то SQL - на операции с данными, в виде таблиц. Кроме обычных таблиц SQL позволяет создавать особый тип таблиц - выборку (подмножество строк и столбцов из одной или нескольких таблиц). Часто выборку называют виртуальной таблицей.

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

средства запросов;

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

средства определения данных;

средства контроля данных;

средства встраивания в основной язык.

Средства запросов

Большинство запросов на извлечение данных из БД строится на основе команды SELECT.

SELECT <список атрибутов и/или функций от атрибутов>

FROM <список отношений>

WHERE <условие>

GROUP BY <список колонок>

HAVING <условие>

ORDER BY <список сортировки>

TO FILE | TO PRINTER | TO SCREEN | INTO

Принципиальная схема выполнения запроса:

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

для каждой строки декартового произведения вычисляется значение логического выражения, заданного в WHERE, строки с ложным значением - удаляются;

если заданно GROUP BY, то оставшиеся строки делятся на группы соответственно значениям указанных в ней колонок;

для каждой группы или строки вычисляется выражение, заданное во фразе SELECT;

для каждой группы производится проверка условий заданного фразой HAVING, варианты с false удаляются;

результат сортируется по колонкам из ORDER BY, в соответствии с заданным порядком сортировки.

В строке SELECT указываются через запятую имена столбцов в выходной таблице. Символ "*" означает, что выбираются все столбцы, указанные в предложении FROM. Если в нескольких таблицах имеются колонки с одинаковым именем, то перед этими именами указываются имена таблицы, разделённые ".". Чтобы вывести все столбцы таблицы, можно указать <имя таблицы>.*. Для того, чтобы вывести только уникальные столбцы таблицы, используют слово DISTINCT <столбцы>. Предложением SELECT можно задавать вывод символьного выражения и так же исчисляемую колонку (виртуальную).

Пример:

SELECT Tab_No, Fam, Oklad + Prem AS "оклад + премия".

В команде SELECT можно использовать специальные агрегатные функции:

COUNT () - количество отображаемых строк.

SUM () - суммирует значения числовых столбцов

MIN () - находит минимум числового столбца

MAX () - находит максимум числового столбца

AVG() - находит среднее значение числовой колонки, в скобках имя столбца.

Пример:

SELECT SUM(Oklad);

SELECT Name, SUM(Cena * Kol-vo) AS "Сумма";

SELECT COUNT(*);

SELECT MAX(Oklad), MIN(Oklad), AVG(Oklad).

Для отбора строк по заданному критерию используют предложение WHERE:

Пример:

SELECT имя покупателя

FROM заказ

WHERE Изд# = 139

Предикат IN в неявном виде заменяет квантор существования.

Пример:

WHERE X IN P(X) (эквивалентно xP(x))

WHERE X NOT IN P(X) (эквивалентно xP(x))

Множество, задающееся в предложении IN(), можно определить не только перечислением его элементов, но и косвенно, используя вложенный подзапрос:

Пример:

WHERE X IN (SELECT ... FROM ...)

Предикат LIKE осуществляет выбор на включаемые надстройки, задаваемой переменной или константой. Подстрока определяется заданными символами, замещёнными "-" (замещает один символ) и "%" (любое число символов):

Пример:

WHERE ФИО LIKE "ПЕТ%"

В предложении WHERE предикаты BETWEEN, IN, LIKE могут объединятся связями AND, OR, NOT.

Два дополнительных предложения GROUP BY и HAVING позволяют располагать строки по группам. Затем можно выполнять операции с этими группами. Например, использовать операцию агрегирования.

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

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

SELECT <номер>, SUM(<количество>), <стоимость>, <название>

FROM <склад>

WHERE <стоимость> > 300

GROUP BY <название>, <номер>, <стоимость>

HAVING SUM(<количество>) > 10

ORDER BY <название>

Если HAVING используется без GROUP BY, то его действие распространяется на всю таблицу и эквивалентно WHERE.

ORDER BY `<колонка | целое число [ASC, DESC]>

Вместо имени колонки допустимо использование целого числа, определяющего её позицию в таблице SELECT.

Особенности языка SQL

фактический - стандарт обращения к современным БД;

ориентирован на операции с данными, представленными в виде совокупности таблиц (DBASE работает с записями);

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

Объекты современных реляционных БД

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

Представление

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

CREATE VIEW <имя представления>

[(<имя поля>[,<имя поля>…])]

AS SELECT

FROM

WHERE

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

Синонимы

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

CREATE TABLE Primer,

тогда Петр может использовать таблицу Ivan.Primer. чтобы избежать длинных обращений, Петр может создать синоним

CREATE SYNONYM P1 FOR Ivan.Primer

и обращаться к таблице через P1.

Имя Р1 является частным для пользователя Петра. Другой пользователь может создать собственный синоним. Описание всех таблиц, представлений, синонимов хранится в специальном каталоге.

Каталог

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

SYS TABLES -- таблицы и представления. Обычно содержат поля:

Name - имя таблицы;

Creator - имя пользователя;

ColCount - количество столбцов.

SYS COLUMNS -- колонки БД. В каждой строке этой таблицы содержится информация о столбце какой-либо таблицы. Для этого служат поля

Name - имя столбца;

TBName - имя таблицы;

ColType - тип данных для столбца.

SYS INDEX -- любому индексу в системе отводится одна строка в этой таблице. Для каждого индекса в ней указано его имя, имя индексной таблицы TBName, имя пользователя и т.д.

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

Пример:

SELECT FROM SYSIBM.SYS COLUMNS WHERE Creator=

Хранимые программы и процедуры

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

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

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

Использование хранимых команд и процедур преследует следующие цели:

Повышение производительности;

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

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

Триггеры

Триггеры -- определяемое пользователем действие, которое выполняется, когда над таблицей, к которой подключен триггер, выполняется операция INSERT, UPDATE или DELETE.

Триггеры используются для решения трех основных задач:

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

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

Регистрация изменений данных. Создатель таблицы, или администратор БД может пожелать иметь информацию о времени каждого изменения данных в таблице, а также какой пользователь… Для этого он создает триггер, который поступает в системное время операции UPDATE имя пользователя. Процедура триггера затем вводит эту информацию в специальную таблицу регистрации изменений. Триггер может быть определен для выполнения либо перед операцией (BEFORE), либо после (AFTER) INSERT, UPDATE, DELETE.

Формат команды

CREATE TRIGGER ON <имя таблицы>

FOR DELETE | UPDATE | INSERT

AS <логическое выражение>

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