Цель курса - предоставить слушателям знания и навыки, необходимые для построения базовых запросов и программирования на SQL для Firebird SQL Server, Microsoft SQL Server и Oracle. 

      Авторский дистанционный курс  поставлен по одноименному учебнику пособию Маркина А.В. с грифом УМО вузов по университетскому политехническому образованию.

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

       Курс предназначен для начинающих администраторов и разработчиков баз данных и приложений.

Требуемая предварительная подготовка:

• Умение работать в Windows или Linux
• Знание основ программирования
• Базовые знания терии реляционных баз данных

По окончании курса Вы будете уметь:

• Использовать современные базы данных
• Создавать запросы к базам данных на языке SQL
• Использовать сложные конструкции процедурного SQL для программирования хранимых процедур и триггеров
• Управлять доступом к объектам базы данных
• Работать с транзакциями

Здесь можно проверить полученные знания.

СОДЕРЖАНИЕ

Предисловие

Введение

1.Реляционная модель данных

1.1. Определение реляционной модели

1.2. Таблицы

1.3. Первичные ключи

1.4. Внешние ключи

1.5. Связи между таблицами

1.6. Нормализация отношений

1.7. Модели базы данных на логическом и физическом уровнях

1.8. Целостность данных

1.9. Архитектура «клиент-сервер»

Контрольные вопросы

2. Введение в SQL

2.1. Объекты структуры базы данных

2.2. Функции SQL

2.3. Достоинства SQL

2.4. Формы использования SQL

2.5. Firebird SQL Server

2.6. Правила синтаксисаи основные запросы SQL

2.7. Имена объектов в SQL. Константы,отсутствующие данные

2.8. Выражения

2.9. Типы данных

Контрольные вопросы

3. Язык выборки данных

3.1. Синтаксис запроса SELECT

3.2. Запросы к одной таблице

3.2.1. Предложения SELECT и FROM

3.2.2. Предложение WHERE

3.2.2.1. Простое сравнение

3.2.2.2. Проверка на принадлежность диапазону значений

3.2.2.3. Проверка на соответствие шаблону

3.2.2.4. Проверка на наличие последовательности символов

3.2.2.5. Проверка на совпадение с началом строки

3.2.2.6. Проверка на членство во множестве

3.2.2.7. Проверка значения на NULL

3.2.2.8. Проверка двух значений на отличие

3.2.2.9. Проверка на соответствие регулярному выражению

3.2.2.10. Составные условия поиска

3.2.3. Функции в SQL

3.2.3.1. Классификация функций

3.2.3.2. Скалярные функции

3.2.3.3. Агрегатные функции

3.2.3.4. Функции для списка значений

3.2.3.5. Дополнительные возможности вывода в предложении SELECT

3.2.4. Предложение GROUP BY

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

3.2.6. Сортировка результатов запроса

3.3. Многотабличные и вложенные запросы

3.3.1. Соединения таблиц

3.3.1.1. Неявное соединение таблиц

3.3.1.2. Явное соединение таблиц

3.3.1.3. Стандартныесоединения (объединения) таблиц

3.3.1.4. Соединение таблицы со своей копией

3.3.2. Запросы с вложенными запросами

3.3.2.1. Виды вложенных запросов

3.3.2.2. Запросы с простыми подзапросами

Простые подзапросы в предложениях WHERE и HAVING

3.3.2.3. Запросы со связанными подзапросами

3.3.2.4. Предикаты ANY и ALL

3.3.2.5. Предикат SINGULAR

3.3.2.6. Предикат EXISTS

3.3.3. Объединение результатовнескольких запросов

3.3.4. Рекурсивные подзапросыв предложении WITH

3.4. Оптимизация запросов на выборку данных

3.4.1. Обработка запроса в реляционных СУБД

3.4.2. Основные направления анализа «медленных» SQL-запросов

3.4.3. Рекомендации по разработке и реструктуризации запросов конкретных типов

Контрольные вопросы

4. Язык определения данных

4.1. Домены

4.2. Создание, изменение и удаление базовых таблиц БД

4.2.1. Создание таблицы

4.2.2. Определение ограничений столбца

4.2.3. Определение ограничений на таблицу

4.2.4. Удаление таблицы БД

4.2.5. Изменение определения таблицы

4.3. Индексы

4.4. Временные таблицы

4.5. Представления

4.6. Комментарии к объектам базы данных

Контрольные вопросы

5. Язык манипулирования данными

5.1. Добавление новых данных

5.1.1. Однострочный запрос INSERT

5.1.2. Многострочный запрос INSERT

5.2. Обновление существующих данных

5.2.1. Простой запрос UPDATE

5.2.2. Запрос UPDATE с подзапросом

5.3. Обобщенное обновлениеи добавление данных

5.4. Слияние данных

5.5. Удаление существующих данных

5.5.1. Простой запрос DELETE

5.5.2. Запрос DELETE с подзапросом

5.6. Обновление представлений

Контрольные вопросы

6. Процедурный язык

6.1. Основы разработки модулей на PSQL

6.1.1. Переменные

6.1.2. Условные операторы

6.1.2.1. Оператор ветвления IF

6.1.2.2. Оператор WHILE

6.1.3. Курсоры в PSQL

6.1.3.1. Неявный курсор

6.1.3.2. Явный курсор

6.1.4. SQL-сценарии

6.1.5. Генераторы

6.1.6. Исключительные ситуации

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

6.2.1. Определение хранимых процедур

6.2.2. Процедуры выбора

6.2.3. Выполняемые процедуры

6.3. Триггеры

6.3.1. Триггеры DML

6.3.1.1. Определение триггера

6.3.1.2. Примеры поддержания ссылочной целостности

6.3.1.3. Модификация и удаление триггера

6.3.2. Триггеры базы данных

6.4. Выполняемые блоки

Контрольные вопросы

7. Защита данных

7.1. Управление доступом к данным

   7.1.1. Требования к безопасности данных

   7.1.2. Привилегии доступа и передача привилегий

   7.1.3. SQL роли

   7.1.4. Привилегии на представления

   7.1.5. Отмена привилегий

   7.1.6. Автоматизированное управление доступом

7.2. Транзакции

       7.2.1. Понятие транзакции

       7.2.2. Восстановление данных

       7.2.3. Параллелизм

Контрольные вопросы

Лабораторный практикум

Лабораторная работа № 1. IBExpert инструмент разработчика и администратора баз данных Firebird. Учебная база данных

Лабораторная работа № 2. Построение простых запросов на чтение данных

Лабораторная работа № 3. Построение многотабличных и вложенных запросов на чтение данных

Лабораторная работа № 4. Построение запросов на определение данных

Лабораторная работа № 5. Построение запросов на модификацию данных

Лабораторная работа № 6. Программирование хранимых процедур

Лабораторная работа № 7. Программирование триггеров

Лабораторная работа № 8. Построение запросов управления доступом

Библиографический список

Приложение А. Описание учебной базы данных

Приложение Б. СИСТЕМНЫЙ КАТАЛОГ БАЗЫ ДАННЫХ

Б.1. Общие сведения о базе данных

Б.2. Домены

Б.3. Отношения (таблицы и представления)

Б.4. Процедуры

Б.5. Триггеры

Б.6. Зависимости

Приложение В. Скрипт для создания учебной базы данных (Firebird SQL Server)

 

Приложение А. Описание учебной БД

         Жилищно-коммунальное хозяйство (ЖКХ) является одной из основных отраслей народного хозяйства Российской Федерации, охватывающей многоотраслевой производственно-технический комплекс, потребность в продукции которого практически не ограничена. Система ЖКХ представлена жилыми, общественными зданиями, эксплуатационными, управляющими, ремонтно-строительными, транспортными, ресурсоснабжающими и другими предприятиями, составляющими сложную социально-экономическую систему, от результативности, функционирования которой зависит развитие городских объектов и состояние среды обитания жителей городов и других населеных пунктов.

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

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

     Учебная БД представляет собой очень упрощенный пример информационной модели расчетно-аналитической составляющей расчетно-платежного комплекса «Абонент+», которая используется для информационного обеспечения деятельности организаций по оказанию населению жилищно-коммунальных услуг и расчетам за них [3]. Учебная БД состоит из 8-ми таблиц: 5-ти таблиц-справочников и 3-х информационных таблиц.

В учебной БД используются такие таблицы-справочники:

• Street. Справочник улиц, в домах которых проживают абоненты. Поля таблицы:
• StreetCD - уникальный код улицы (первичный ключ);
• StreetNM - название улицы, расшифровывающее код улицы.
• Abonent. Справочник абонентов. Поля таблицы:
• AccountCD - номер лицевого счета абонента, уникальным образом идентифицирующий каждого из абонентов (первичный ключ);
• StreetCD - код улицы, на которой проживает абонент (внешний ключ, ссылающийся на первичный ключ таблицы Street);
• HouseNo - номер дома, в котором проживает абонент;
• FlatNo - номер квартиры;
• Fio - фамилия, имя и отчество абонента в формате «Фамилия И.О.»;
• Phone - номер телефона.
• Services. Справочник услуг, оказываемых абонентам жилищно-коммунальными организациями. Поля таблицы:
• ServiceCD - код услуги (первичный ключ);
• ServiceNM - наименование услуги.
• Disrepair. Справочник типовых неисправностей газового оборудования абонентов. Поля таблицы:
• FailureCD - код неисправности газового оборудования (первичный ключ);
• FailureNM - наименование неисправности газового оборудования.
• Executor. Справочник исполнителей заявок, поданных абонентами на ремонт газового оборудования. Исполнителями являются работники ремонтной службы газораспределительной организации, оказывающей соответствующие услуги. Поля таблицы:
• ExecutorCD - уникальный код, идентифицирующий исполнителей ремонтных заявок (первичный ключ);
• Fio - фамилия, имя и отчество исполнителя в формате «Фамилия И.О.».

В качестве информационных таблиц учебной БД выделены следующие таблицы:

• NachislSumma. Таблица для хранения информации о размерах ежемесячных начислений плат абонентам за оказанные им услуги (которые расшифровываются в справочнике услуг Services). Оплата за ремонт газового оборудования производится по факту оказания услуги, и начисление за него не производится. Поля таблицы:
• NachislFactCD - уникальный идентификатор факта начисления (первичный ключ);
• AccountCD - номер лицевого счета абонента, которому было сделано начисление (внешний ключ, ссылающийся на первичный ключ таблицы Abonent);
• ServiceCD - код услуги, за которую выполнено начисление (внешний ключ, ссылающийся на первичный ключ таблицы Services);
• NachislSum - значение начисленной суммы;
• NachislMonth - номер месяца, за который произведено начисление;
• NachislYear - год, за месяц которого выполнено начисление.
• PaySumma. Таблица для хранения значений оплат, внесенных абонентами за оказанные им услуги. Для каждого факта оплаты по какой-либо услуге указывается дата осуществления оплаты, оплачиваемые месяц и год. Поля таблицы:
• PayFactCD - уникальный идентификатор факта оплаты по услуге Request (первичный ключ);
• AccountCD - номер лицевого счета абонента, оплатившего оказанную ему услугу (внешний ключ, ссылающийся на первичный ключ таблицы Abonent);
• ServiceCD - код оплаченной услуги (внешний ключ, ссылающийся на первичный ключ таблицы Services);
• PaySum - значение оплаченной суммы;
• PayDate - дата оплаты;
• PayMonth - номер оплачиваемого месяца;
• PayYear - оплачиваемый год.

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

• Request. Таблица для хранения информации о заявках абонентов на ремонт газового оборудования. Каждая ремонтная заявка характеризуется номером лицевого счета абонента (расшифровка в справочнике Abonent), заявившего определенную неисправность газового оборудования (расшифровка в справочнике Disrepair) в его доме или квартире, исполнителем ремонтной работы (справочник Executor), датой регистрации заявки, датой выполнения ремонта и признаком погашения (1/0). Поля таблицы:
• RequestCD - уникальный код ремонтной заявки (первичный ключ);
• AccountCD - номер лицевого счета абонента, подавшего данную ремонтную заявку (внешний ключ, ссылающийся на первичный ключ таблицы Abonent);
• FailureCD - код неисправности газового оборудования, заявленной абонентом в данной ремонтной заявке (внешний ключ, ссылающийся на первичный ключ таблицы Disrepair);
• ExecutorCD - код исполнителя, ответственного за выполнение данной ремонтной заявки (внешний ключ, ссылающийся на первичный ключ таблицы Executor);
• IncomingDate - дата поступления заявки;
• ExecutionDate - дата выполнения заявки;
• Executed - поле логического типа, признак погашения заявки

В общем случае зарегистрированная ремонтная заявка может быть:

• не назначена ни одному из исполнителей, а следовательно не выполнена и не погашена;
• назначена одному из исполнителей, но не выполнена им, а следовательно и не погашена;
• назначена одному из исполнителей, выполнена им, но не погашена;
• назначена одному из исполнителей, выполнена им и погашена

ER-модели учебной БД на логическом и физическом уровнях представлены соответственно на рис. 1.5 и  рис. 1.6.

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

Td_Abonent. Триггер запускается после удаления строки в таблице Abonent. Если в таблицах NachislSumma или PaySumma имеются записи с внешним ключом AccountCD, ссылающимся на удаляемую строку таблицы Abonent, то триггер вызывает исключение Del_Restrict и операция удаления прерывается.

Td_Services. Триггер запускается после удаления строки в таблице Services. Если в таблицах PaySumma или NachislSumma имеются записи с внешним ключом ServiceCD, ссылающимся на удаляемую строку в таблице Services, то триггер вызывает пользовательское исключение Del_Restrict и прерывает выполнение операции.

Текст определения описанных триггеров приведен в скрипте по созданию учебной БД.

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

Таблица А.1. Данные таблицы Street

Таблица А.2. Данные таблицы Abonent

Таблица А.3. Данные таблицы Services

Таблица А.4. Данные таблицы Disrepair

 

 

Таблица А.5. Данные таблицы Executor

Таблица А.6. Данные таблицы NachislSumma

Окончание табл. А.6.

Таблица А.7. Данные таблицы PaySumma

Продолжение табл. А.7.

Окончание табл. А.7.

Таблица А.8. Данные таблицы Request

RequestCD	AccountCD	ExecutorCD	FailureCD	IncomingDate	ExecutionDate	Executed
1	005488	1	1	17.12.2011	20.12.2011	True
2	115705	3	1	07.08.2011	12.08.2011	True
3	015527	1	12	28.02.2012	08.03.2012	False
5	080270	4	1	31.12.2011	NULL	False
6	080613	1	6	16.06.2011	24.06.2011	True
7	080047	3	2	20.10.2012	24.10.2012	True
9	136169	2	1	06.11.2011	08.11.2011	True
10	136159	3	12	01.04.2011	03.04.2011	False
11	136160	1	6	12.01.2013	12.01.2013	True
12	443069	5	2	08.08.2011	10.08.2011	True
13	005488	5	8	04.09.2010	05.12.2010	True
14	005488	4	6	04.04.2013	13.04.2013	True
15	115705	4	5	20.09.2010	23.09.2010	True
16	115705	NULL	3	28.12.2011	NULL	False
17	115705	1	5	15.08.2011	06.09.2011	True
18	115705	2	3	28.12.2012	04.01.2013	True
19	080270	4	8	17.12.2011	27.12.2011	True
20	080047	3	2	11.10.2011	11.10.2011	True
21	443069	1	2	13.09.2011	14.09.2011	True
22	136160	1	7	18.05.2011	25.05.2011	True
23	136169	5	7	07.05.2011	08.05.2011	True