Лекция 5. Физическое моделирование реляционных баз данных. Базовая семантика языка SQL.

**1-ый учебный вопрос: Принципы работы SQL инструкции (запроса).** SQL инструкция запроса на выборку (SELECT), это инструкция языка SQL, позволяющая пользователю сформировать необходимую выборку из данных, находящихся на сервере БД. Это наиболее часто применяемая инструкция языка SQL при работе с данными. Приведем типовую развернутую структуру инструкции SELECT. SELECT [DISTINCT] target-list FROM relation-list WHERE qualification, где relation-list – список имен отношений. Через запятую перечисляются все реляционные отношения, данные из которых понадобятся для конечной выборки. Одно и то же отношение может быть использовано в выборке несколько раз, если это необходимо. В этом и иных случаях допускается замена имени отношения произвольной переменной, которая будет использоваться в теле запроса, как имя отношения. target-list – список атрибутов отношений, которые указаны в relation-list. Через запятую перечисляются все атрибуты, которые будут показаны в результатной выборке, в том порядке, в котором они были указаны в target- list. qualification – условие сравнения. Варианты сравнения для выборки: отношение-значение, отношение 1 – отношение 2, по условию (<, >, =, ≤, ≥), комбинированные с логическими операторами AND, OR, NOT). Сформированная инструкция запрос на выборку передается в СУБД, после чего СУБД формирует в качестве результата выборку данных. В стандартном виде (по умолчанию) последовательность действий СУБД при обработке инструкции SELECT выглядит следующим образом. 1. Рассчитать (получить данные) перечисленные отношения. 2. Исключить из результата п. 1 экземпляры отношения, которые не попадают в условия, указанные в qualification. 3. Исключить атрибуты, отсутствующие в target-list. 4. Если имеется группировка или иная сортировка данных, провести ее на последнем этапе. Принципы группировки данных для инструкции SELECT будут рассмотрены в лекции позднее. Приведем пример обработки СУБД инструкции на выборку данных. Предположим, что к некоторым отношениям Sailors (моряки) и Reserves (Забронировано) в рамках SQL инструкции применены некоторые условия. SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid = 103 Обратим внимание на то, что в relation-list данной инструкции для таблиц были созданы переменные, которые используются вместо имен таблиц в qualification. В условии qualification объединены два условия для формирования конечной выборки: массивы данных атрибутов s.sid и r.sid должны содержать идентичные значения (s.sid – первичный ключ отношения Sailors, r.sid – внешний ключ отношения Reserves), а также, должны быть выбраны экземпляры сущности Reserves, для которых значение атрибута r.bid равно 103. Рассмотрим последовательность действий с массивом данных со стороны СУБД, рис. 1.  Сперва СУБД соберет единый массив данных из двух отношений. В него войдут все атрибуты и их значения из таблиц Sailors и Reserves. Таблица на рис. 1, это результат объединения массивов данных двух таблиц. Далее, СУБД проверит условия, содержащиеся в qualification инструкции (WHERE S.sid=R.sid AND R.bid = 103). Единственный подходящий под оба условия сразу экземпляр выделен на рисунке рамкой (58 = 58 И bid = 103). Остальные экземпляры будут исключены из выборки. Последним будет проверет target-list. Результатом этой проверки будет выданная пользователю значение Sname rusty (значение, которое находилось в S.sname выборки с предыдущего этапа). Следует отметить, что не во всех случаях реализация инструкции на выборку SELECT по умолчанию (как указано выше) является оптимальным вариантом. Такого рода инструкции могут быть применены на очень большие массивы данных, и логика ограничений внутри самой инструкции может быть весьма сложной, что приведет к большим потерям по вычислительным мощностям и времени в том случае, если сама инструкция выполняется не оптимальным образом. Возможности оптимизации инструкций на выборку лежат в теории традиционной реляционной алгебры. **2-ый учебный вопрос: Операции реляционной алгебры.** Реляционная алгебра, набор операций для реляционных отношений, являющийся базой для инструкций языка SQL. Операции реляционной алгебры могут быть представлены в инструкциях SQL в естественном виде, в ином виде, или не представлены вовсе. На рис. 2 перечислены все операции реляционной алгебры, а также показан приоритет их выполнения в рамках инструкции языка SQL (чем выше приоритет, тем раньше выполнится эта операция).  Ниже будут даны краткие описания операций реляционной алгебры. RENAME - операция переименования (иногда, - присвоения). Для операции достаточно только одного операнда (отношения). Переименовывается не само отношение (таблица), а ее атрибуты. RENAME Atr1, Atr2... AS NewAtr1, NewAtr2... WHERE - выборка по условию. Для операции достаточно только одного операнда (отношения). В тоже время, могут участвовать несколько операндов (отношений). В результате строится новое отношение, в которое входят только те значения, которые при подстановке в условие where дают значение true. A WHERE c PROJECT - вертикальная выборка по выбранным атрибутам. Для операции достаточно только одного операнда (отношения). В результате строится новое отношение, в которое входят значения невычеркнуых в перечислении атрибутов. Результатом часто становится таблица с единственным выбранным атрибутом. TIMES - умножение одной таблицы (отношения) на другую. Для операции обязательно наличие двух операндов (отношений). Само перемножение называется конкатенацией. В результате строится новое отношение, в которое входят перемноженные друг на друга значения двух таблиц. A TIMES B. JOIN - комбинация произведения двух таблиц и заранее определенного условия. Для операции обязательно наличие двух операндов (отношений). В результате строится новое отношение, в которое входят перемноженные друг на друга значения двух таблиц ограниченные выбранным условием WHERE. (A TIMES B) WHERE P. INTERSECT - ищет в двух таблицах совпадающие значения. Для операции обязательно наличие двух операндов (отношений). В результате строится новое отношение, в которое входят кортежи, принадлежащие и первому и второму операнду. A INTERSECT B. DIVIDE BY - результатом операции деления является набор кортежей (строк) первого отношения, которые соответствуют комбинации всех кортежей второго отношения. Для этого нужно, чтобы во втором отношении была часть атрибутов (можно и один), которые есть в первом отношении. В результирующем отношении присутствуют только те атрибуты первого отношения, которых нет во втором. A DIVIDE BY B. Следует отметить, что операция деления в своем естественном виде в языке SQL не представлена. Ниже приведен пример инструкции, реализующей логику деления с использованием нескольких таблиц (используется учебная база данных СУБД MS SQL Server под названием AdventureWorks). -- Uses AdventureWorks SELECT s.BusinessEntityID AS SalesPersonID, FirstName, LastName, SalesQuota, SalesQuota/12 AS 'Sales Target Per Month' FROM Sales.SalesPerson AS s JOIN HumanResources.Employee AS e ON s.BusinessEntityID = e.BusinessEntityID JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID; UNION - оставит значения принадлежащие первой, второй таблице или обеим таблицам. Для операции обязательно наличие двух операндов (отношений). В результате строится новое отношение, в которое входят кортежи, первого и второго операндов без повторений. A UNION B. MINUS - оставит значения принадлежащие первой таблицы и не принадлежащие второй таблице. Для операции обязательно наличие двух операндов (отношений). В результате строится новое отношение, в которое входят кортежи первого операнда при условии, что их нет во втором операнде. A MINUS B. Для демонстрации разных вариантов построения инструкций запросов на выборку (традиционные SQL запросы и запросы с применением операций реляционной алгебры) рассмотрим три отношения с данными, рис. 3.  Решим задачу вывода моряков Sailors, которые брали в прокат красную или зеленую лодку. SQL код инструкции на выборку приведен ниже. SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’) В случае применения операции реляционной алгебры UNION (см. выше), код инструкции на выборку будет выглядеть несколько иначе. SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ UNION SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’ Обратите внимание на то, что действия СУБД во втором случае осуществляются по каждому условию B.color отдельно, после чего, результаты двух полученных массивов данных объединяются. Далее, решим задачу вывода моряков Sailors, которые брали в прокат и красную и зеленую лодку. SQL код инструкции на выборку приведен ниже. SELECT S.sid FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bid AND (B1.color=‘red’ AND B2.color=‘green’ Обратите внимание на то, насколько усложнилась логика qualification после команды WHERE. В случае применения операции реляционной алгебры INTERSECT (см. выше), код инструкции на выборку будет выглядеть несколько иначе. SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ INTERSECT SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’ Обратите внимание на то, что действия СУБД во втором случае осуществляются по каждому условию B.color отдельно, после чего, берется пересечение результатов этих двух массивов данных. **3-ий учебный вопрос: Агрегатные операторы, группировка, триггеры.** Агрегатные функции, набор функций, применяемых в основном на массивах числовых значениях, в результате дающий агрегатный числовой результат (одно число) в соответствии с выбранной пользователем функцией. Ниже перечислены основные агрегатные функции, чаще всего применяющиеся в инструкциях на выборку SELECT. * COUNT – показывает общее количество строк в выбранном атрибуте отношения, не NULL. * SUM - производит арифметическую сумму всех значений выбранного атрибута отношения. * AVG - производит усреднение всех значений выбранного атрибута отношения. * MAX – выводит экстремум по максимуму всех значений выбранного атрибута отношения. * MIN – выводит экстремум по минимуму всех значений выбранного атрибута отношения. Приведем примеры инструкций, составленных с помощью агрегатных функций. SELECT COUNT (*) FROM Sailors S Результат выполнения инструкции покажет количество экземпляров отношения Sailors. SELECT AVG (S.age) FROM Sailors S WHERE S.rating=10 Результат выполнения инструкции покажет среднее арифметическое значение возраста моряков, рейтинг которых равен 10. Группировка – еще одна процедура, возможная при реализации инструкции запроса к данным SELECT. Группировка позволяет разделить полученный результатный массив данных на группы по значениям атрибутов отношения, возможно с дополнительными условиями. Группировка всегда осуществляется последним действием и параметр qualification группировки всегда содержит только одно условие. Далее будет приведены операторы группировки в общем виде SQL инструкции. SELECT [DISTINCT] target-list FROM relation-list WHERE qualification GROUP BY grouping-list HAVING group-qualification, где grouping-list – атрибут таблицы, по которую будет осуществляться группировка group-qualification – условие фильтра группировки. Далее будут приведен пример, в котором в инструкции на выборку будет команда для группировки результатных значений с дополнительным условием. Так, предположим, в таблице Sailors (рис. 28) необходимо найти и вывести возраст самого юного моряка, которому больше 18 лет для рейтинга, которым обладают хотя бы 2 экземпляра сущности Sailors. Сама инструкция и результат ее выполнения показаны на рис. 4.  Последовательность выполнения СУБД инструкции на рис. 4 следующая: - по условию WHERE выбираются экземпляры, значение атрибута age которых больше или равно 18 (возраст). - из выборки исключаются атрибуты, не входящие в target-list (sid, sname). - применяется группировка по значению атрибута rating - исключаются все группы, в которых нет хотя бы 2 входящих экземпляров (COUNT > 1). - для группы с рейтингом 7 (она единственная осталась) определяется минимальное значение в массиве age (MIN (age)). Триггеры – специальные хранимые процедуры, написанные в языке SQL, срабатывающие автоматически при наступлении определенного события. Состоят из трех частей: - событие (помимо основной функции, запускает триггер на исполнение). Событие может быть одной из инструкций SQL UPDATE, INSERT, DELETE. - условие (проверяет, когда должен быть запущен триггер). Условие бывает или после события AFTER или вместо события INSTEAD OF. - действие (что произойдет, когда триггер сработает). Это любой валидный код инструкции языка SQL. Ниже приведен пример применения триггера. CREATE TRIGGER mod_t1 on t1 AFTER UPDATE AS BEGIN DECLARE @id_old INT DECLARE @id_new INT DECLARE @name_old VARCHAR(50) DECLARE @name_new VARCHAR(50) SELECT @id_old=(SELECT id FROM deleted) SELECT @id_new=(SELECT id FROM inserted) SELECT @name_old=(SELECT name FROM deleted) SELECT @name_new=(SELECT name FROM inserted) INSERT INTO t2 VALUES(@id_old,@id_new,@name_old,@name_new,GETDATE()) END Данный триггер, после применения инструкции UPDATE (модификация данных) к одной из таблиц БД в другую таблицу БД автоматически записывает старое значение до изменения, новое значение после изменения и системную дату выполнения инструкции UPDATE (упрощенная версия журнала транзакций). **Вопросы для самостоятельного изучения по итогам лекции.** 1. Можно ли вложить одну выборку SELECT в другую, в одном запросе? Если да, то есть ограничение на количество вложений? 2. Какие еще есть агрегатные функции у оператора SELECT приведите примеры кода с ними, на основании таблицы из лекции. 3. Какие есть события и условия для триггеров? Напишите код полезного триггера для БД из лекции. 4. Запишите операции реляционной алгебры в строгом алгебраическом виде. Тестовые задания для самопроверки. 1. Какие встроенные агрегатные функции применяются в языке SQL? А) SIN, COS, INTEG, DIFF2, SHIFT Б) COUNT, SUM, AVG, MAX, MIN В) ATAN, SUM, AVG, LOG, INC Г) DIFF, CONCAT, MIN, ATAN2, LOG2 Д) STRCPY, PRINTF, MAX, EXIT, COUNT 2. За объединения строк таблицы в группы в синтаксисе оператора SELECT отвечает предложение: А) GROUP BY Б) HAVING В) WHERE Г) ORDER BY 3. За сортировку строк в синтаксисе оператора SELECT отвечает предложение: А) ORDER BY Б) WHERE В) FROM Г) HAVING 4. Возможно ли выполнение инструкции, которая начинается с предложения: «SELECT ProductName, SUM(Price) as Total FROM Products” А) возможно, если далее следует предложение GROUP BY в котором указан столбец ProductName Б) возможно В) невозможно 5. Запрос, который размещён внутри другой инструкции SELECT, UPDATE или DELETE называется: А) приложенным Б) дополнительным В) уточняющим Г) вложенным 6. Чтобы отфильтровать данные по нескольким столбцам, необходимо воспользоваться оператором … А) DESC Б) AND В) NOT Г) OR 7. Какая из агрегатных функций возвращает количество записей в запросе: А) COUNT Б) AVG В) SUM Г) MIN Д) MAX 8. Какая агрегатная функция используется для расчета суммы? А) SUM Б) AVG В) COUNT Г) CONCAT 9. Инструкция для изменения таблиц, триггеров, индексов и других объектов базы данных начинается с: А) INSERT Б) BEGIN В) ALTER Г) CREATE 10. Какая инструкция удалит строку (строки) из указанной таблицы: А) DROP Б) DELETE В) ERASE Г) ALTER 11. Для удаления столбца таблицы следует выполнить инструкцию, начинающуюся с оператора: А) ALTER Б) UPDATE В) SELECT Г) DELETE 12. Запрос «SELECT CONCAT(‘Мой ответ’,’ ‘, 12)» вернёт: А) Вернет кортеж «Мой ответ 12» Б) Вернет кортеж «Мой ответ», « », «12» В) Запрос выполнится, но будет содержать пустой столбец Г) запрос не будет выполнен, так как отсутствуют обязательные предложения инструкции SELECT 13. При помощи какого оператора можно изменить или присвоить имя столбцу в запросе? А) IN Б) ON В) LIKE Г) AS 14. Какой оператор нужно подставить вместо пропуска, чтобы осуществить проверку значений столбца на принадлежность к заданному множеству? productType ____ (‘пирог’,’торт’ ‘кекс’) А) AS Б) LIKE В) IN Г) ON