Цель курса - предоставить слушателям знания и навыки, необходимые для построения базовых запросов и программирования на 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. Системы управления базами данных

    2.5.1. Firebird

    2.5.2. Microsoft SQL Server

    2.5.3. Oracle Database  

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.3. Функции в SQL

    3.2.4. Секция GROUP BY

    3.2.5. Секция HAVING

    3.2.6. Секция ORDER BY

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

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

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

    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.3.1. Явный запрос DELETE

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

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

   5.4.1. Запрос UPDATE OR INSERT

   5.4.2/ Запрос MERGE 

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

Контрольные вопросы и задания

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

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

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

  6.1.2. Переменные

  6.1.3. Операторы ветвления, циклов и переходов

  6.1.4. Курсоры в PSQL

  6.1.5. Последовательности

  6.1.6. Обработка Исключительных ситуаций

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

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

   6.2.2. Виды хранимых процедур

   6.2.3. Селективные процедуры Firebird

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

   6.2.5. Динамический SQL

6.4. Процедурные пакеты

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

6.6. Триггеры

    6.6.1. Триггеры DML

    6.6.2. Триггеры DDL

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

Контрольные вопросы и задания

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

7.1. Программирование запросов управления доступом

   7.1.1. Принципы доступа к данным. Управление пользователями

   7.1.2. Схемы даны данным в Microsoft SQL Server

   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)

 

Приложение А. 

Описание учебной базы данных

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

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

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

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

Следует отметить существенное усложнение в последнее время алгоритмов расчета размера платы за коммунальные ресурсы и жилищно-коммунальные услуги. Прежде всего, это:

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

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

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

1) Street. Справочник улиц города, в домах которых проживают абоненты. Столбцы таблицы:

• StreetCD — уникальный код улицы (первичный ключ),
• StreetNM — название улицы, расшифровывающее код улицы;

2) Abonent. Справочник абонентов города. Столбцы таблицы:

• AccountCD — номер лицевого счета абонента, уникальным образом идентифицирующий каждого из абонентов (первичный ключ),
• StreetCD — код улицы, на которой проживает абонент (внешний ключ, ссылающийся на первичный ключ таблицы Street),
• HouseNo — номер дома, в котором проживает абонент,
• FlatNo — номер квартиры,
• Fio — фамилия, имя и отчество абонента в формате «Фамилия И.О.»,
• Phone — номер телефона;

3) Services. Справочник услуг, оказываемых абонентам жилищно-коммунальными организациями. Столбцы таблицы:

• ServiceCD — код услуги (первичный ключ),
• ServiceNM — наименование услуги;

4) Disrepair. Справочник типовых неисправностей газового оборудования абонентов. Столбцы таблицы:

• FailureCD — код неисправности газового оборудования (первичный ключ),
• FailureNM — наименование неисправности газового оборудования;

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

• ExecutorCD — уникальный код, идентифицирующий исполнителей ремонтных заявок (первичный ключ),
• Fio — фамилия, имя и отчество исполнителя в формате «Фамилия И. О.».

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

1) NachislSumma. Таблица для хранения информации о размерах ежемесячных начислений плат абонентам за оказанные им услуги (которые расшифровываются в справочнике услуг Services). Плата за ремонт газового оборудования производится по факту оказания услуги, и начисление за него не производится. Столбцы таблицы:

• NachislFactCD — уникальный идентификатор факта начисления (первичный ключ),
• AccountCD — номер лицевого счета абонента, которому было сделано начисление (внешний ключ, ссылающийся на первичный ключ таблицы Abonent),
• ServiceCD — код услуги, за которую выполнено начисление (внешний ключ, ссылающийся на первичный ключ таблицы Services),
• NachislSum — значение начисленной суммы,
• NachislMonth — номер месяца, за который произведено начисление,
• NachislYear — год, за месяц которого выполнено начисление;

2) PaySumma. Таблица для хранения значений оплат, внесенных абонентами за оказанные им услуги. Для каждого факта оплаты по какой-либо услуге указывается дата осуществления оплаты, оплачиваемые месяц и год. Столбцы таблицы:

• PayFactCD — уникальный идентификатор факта оплаты по услуге Request (первичный ключ),
• AccountCD — номер лицевого счета абонента, оплатившего оказанную ему услугу (внешний ключ, ссылающийся на первичный ключ таблицы Abonent),
• ServiceCD — код оплаченной услуги (внешний ключ, ссылающийся на первичный ключ таблицы Services),
• PaySum — значение оплаченной суммы,
• PayDate — дата оплаты,
• PayMonth — номер оплачиваемого месяца,
• PayYear — оплачиваемый год.

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

3) Request. Таблица для хранения информации о заявках абонентов на ремонт газового оборудования. Каждая ремонтная заявка характеризуется номером лицевого счета абонента (расшифровка в справочнике Abonent), заявившего определенную неисправность газового оборудования (расшифровка в справочнике Disrepair) в его доме или квартире, исполнителем ремонтной работы (справочник Executor), датой регистрации заявки, датой выполнения ремонта и признаком погашения (закрытия). Столбцы таблицы:

• RequestCD — уникальный код ремонтной заявки (первичный ключ),
• AccountCD — номер лицевого счета абонента, подавшего данную ремонтную заявку (внешний ключ, ссылающийся на первичный ключ таблицы Abonent),
• FailureCD — код неисправности газового оборудования, заявленной абонентом в данной ремонтной заявке (внешний ключ, ссылающийся на первичный ключ таблицы Disrepair),
• ExecutorCD — код исполнителя, ответственного за выполнение данной ремонтной заявки (внешний ключ, ссылающийся на первичный ключ таблицы Executor),
• IncomingDate — дата поступления заявки,
• ExecutionDate — дата выполнения заявки,
• Executed — столбец логического типа, признак погашения заявки.

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

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

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

Для обработки исключительных ситуаций созданы исключения: Del_ Restrict, Ins_ Restrict и Upd_ Restrict.

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

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

Таблица А.1

Данные таблицы Street

Таблица А.2

Данные таблицы Abonent

Таблица А.3

Данные таблицы Services

Таблица А.4

Данные таблицы Disrepair

Таблица А.5

Данные таблицы Executor

Таблица А.6

Данные таблицы NachislSumma

Таблица А.7

Данные таблицы PaySumma

Таблица А.8

Данные таблицы Request

 

23	136169	5	7