Страница учебника А.В. Маркина "Программирование на SQL" для бакалавриата и магистратуры (издательство ЮРАЙТ, 2023)

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

  Лабораторный практикум нацелен на изучение основ декларативного и процедурного языков SQL, освоение инструментальных средств, используемых при программировании и отладке запросов и команд, получение практических навыков работы в конкретной СУБД в процессе выполнения лабораторных работ, каждая из которых посвящена построению и отладке определенных запросов.           Практикум содержит восемь лабораторных работ по всем разделам настоящего учебника. Каждая работа предполагает выполнение в интерактивном режиме общей части и индивидуального задания. По итогам каждой лабораторной работы оформляется отчет, а защита  производится под контролем информационной системы https://rgrty.ru/sqltest/ [32-34]. Для защиты работ №№ 1, 6, 7 и 8 необходимо результативно ответить на определенное число контрольных вопросов и заданий (в виде традиционного тестирования), а для защиты работ №№ 2, 3, 4 и 5 — построить и ввести SQL-запросы согласно определенному числу предлагаемых заданий различной сложности.

    Описание лабораторной установки. Компьютер с локально установленными сервером БД Oracle Database и клиентским приложением dbForge Studio for Oracle. Адреса для их копирования, а также инструкции по установке и настройке приведены на странице настоящего курса на портале https://rgrty.ru. Здесь же выложен скрипт учебной базы данных.

Лабораторная работа № 1.

Основы реляционных баз данных. Учебная база данных (Oracle Database)

Лабораторное задание. Изучить теоретическую часть, изложенную в гл. 1 и 2 учебника [1]. Ознакомиться с предметной областью (учебной БД) по описанию в приложении А. При необходимости установить на используемый компьютер СУБД Oracle Database Express Edition и утилиту dbForge Studio for Oracle. Создать файл учебной БД интерфейсными средствами dbForge Studio for Oracle. С помощью SQL - запросов создать необходимые типы данных и таблицы, заполнить последние данными, взятыми из одноименных таблиц учебной БД. В качестве примеров выборки данных выполнить запросы к таблицам БД. Выполнить резервное копирование и восстановление учебной БД. Составить отчет и защитить лабораторную работу.

Порядок выполнения работы.

 1. Загрузить приложение dbForge Studio for Oracle, предварительно убедившись, что сервер СУБД Oracle  загружен.

 2. В окне «Свойства соединения базы данных» в поле «Сервер» выбрать экземпляр сервера. В поле «Тип соединения» выбрать, например, протокол TNS (Transparent Network Substrate), позволяющий на сервере аутентифицировать  пользователя с помощью имени и пароля.

   В поле «Имя» указать имя пользователя. Для встроенной учетной записи администратора существуют два имени: system и  sys. В первом случае подключение к серверу осуществляется с правами Normal, во втором – с правами SYSDBA.

    В поле «Пароль» необходимо указать пароль для учетной записи. Следует отметить, что пароль для встроенной учетной записи администратора указывался при установке СУБД Oracle.

    Значение поля «Название соединения» и «Категория документа» можно оставить без изменений. Для подключения необходимо нажать на кнопку «ОК».

----

    Следует отметить важную особенность СУБД Oracle Express Edition (XE): в отличии от полной версии Oracle, в XE можно подключиться только к одной (системной) базе данных. Возможности создавать другие БД попросту нет. В качестве альтернативы по разделению объектов (таблиц, триггеров, процедур и т.д.) в зависимости от пользователя, подключившегося к БД, рекомендуется использовать так называемые табличные пространства (tablespace). Они представляют собой именованную область на диске, хранящую объекты и данные. По умолчанию, пользователи sys/system работают с табличным пространством SYSTEM, где одних только системных таблиц насчитывается свыше 920. Ко всему прочему, в данном пространстве имен нельзя создавать триггеры на пользовательские объекты.

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

    Сделать это можно следующим образом:

а) необходимо подключиться к серверу от имени sys/system;

б) выбрать пункт «Новый SQL» на панели инструментов «Стандартные»,  после чего создается новый документ SQL.sql, куда следует скопировать и выполнить следующий скрипт:

/*Создание табличного пространства в директории 'C:/NNNN/SQL/XX.dbf'*/
CREATE TABLESPACE Abonent DATAFILE 'C:/NNNN/SQL/XX.dbf' 
/*Первоначальный размер 128Кб с дальнейшим увеличением на 10Кб, максимальный размер не ограничен*/
SIZE 128K REUSE AUTOEXTEND ON NEXT 10K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
/*Создание пользователя USER1 с паролем 123456, данному пользователю предоставляются главное (Abonent) и временное (Temp) табличные пространства*/  
CREATE USER USER1 IDENTIFIED BY 123456 DEFAULT TABLESPACE Abonent TEMPORARY TABLESPACE temp;
/*Выдача прав пользователю на подключение, доступ к ресурсам и созданий сессий*/
GRANT CONNECT, RESOURCE, CREATE SESSION TO USER1;

где NNNN - номер группы, XX - номер варианта, Abonent - имя табличного пространства, USER1 - имя создаваемого пользователя, 123456 - пароль.

   в) удалить созданное табличное пространство (доступно только от имени sys/system) можно с помощью запроса:

DROP TABLESPACE Abonent INCLUDING CONTENTS AND DATAFILES;

После удаления табличного пространства Abonent пользователь USER1 и назначенные ему права доступа к данному табличному пространству сохраняются в БД.

    г)  повторно создать табличное пространство, выполнив первые две строки предыдущего скрипта:

/*Создание табличного пространства в директории 'C:/NNNN/SQL/XX.dbf'*/
CREATE TABLESPACE Abonent DATAFILE 'C:/NNNN/SQL/XX.dbf' 
/*Первоначальный размер 128Кб с дальнейшим увеличением на 10Кб, максимальный размер не ограничен*/
SIZE 128K REUSE AUTOEXTEND ON NEXT 10K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

----

3. Ознакомиться с правилами работы и структурой меню, а также  со всеми доступными пунктами меню приложения dbForge Studio for Oracle.

4.  Создать и заполнить таблицы БД «Абоненты». Для этого необходимо подключиться к серверу БД от имени пользователя, созданного в пункте 2. Далее необходимо выбрать пункт «Новый SQL», куда следует скопировать скрипт и выполнить его, нажав на соответствующую кнопку «Выполнить». Если всё сделано правильно, после выполнения скрипта можно увидеть сообщение об успешной обработке строк в окне вывода. Для того, чтобы посмотреть созданную БД в проводнике объектов необходимо обновить его. Сделать это можно, открыв контекстное меню проводника правой кнопкой мыши и выбрав пункт «Обновить», либо через соответствующую иконку в окне проводника. После обновления проводника в списке баз данных текущего подключения будет отображена созданная БД.

5. Просмотреть на экране всю информацию о созданных объектах БД. В случае успешного создания БД необходимо в SQL-редакторе выполнить следующий запрос, который извлекает из таблицы NachislSumma все записи о начислениях абоненту с ФИО Тимошкина Н. Г. (номер лицевого счета '080270'): 

      SELECT * FROM NachislSumma WHERE AccountCD = '080270'; 

6. Выполнить запрос, который извлекает из таблицы PaySumma всю информацию об оплатах услуг, произведенных абонентами за период позже 2019 г.: 

SELECT * FROM PaySumma WHERE PayYear > 2019;

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

      SELECT AccountCD, SUM(PaySum) AS Summa_Abonent  FROM PaySumma GROUP BY AccountCD; .

8. Проверить действие всех ограничений ссылочной целостности (запрещающее, каскадное, обновление), путем внесения разрешенных и запрещенных изменений в данные родительской(их) таблице(ах) учебной БД, заданной(ых) вариантом индивидуального задания. Изменять данные в открытой таблице следует из пункта меню "Данные" с помощью кнопок: + - добавить строку;  – - удалить строку; ^ - редактировать строку.

9. Проверить действие всех ограничений ссылочной целостности (запрещающее, обновление), путем внесения разрешенных и запрещенных изменений в данные дочерней(их) таблице(ах) учебной БД, заданной(ых) вариантом индивидуального задания. Изменять данные в открытой таблице следует из пункта меню "Данные" с помощью кнопок: + - добавить строку;  ^ - редактировать строку.

 10. В отчете зафиксировать размер (в байтах) файла БД. Создать резервную копию БД с сжатием и без сжатия. Для создания резервной копии необходимо вызвать мастер утилиты экспорта. Сделать это можно через пункт «Экспорт и импорт – Утилита экспорта» в контекстном меню базы данных в проводнике или через главное меню: «База данных – Экспорт и импорт – Утилита экспорта». Мастер предлагает ряд шагов по созданию различных вариантов экспорта, они подробно расписаны в методическом указании DbForge Studio for Oracle Database. В рамках настоящей лабораторной работы для создания резервной копии рекомендуется выбрать следующие параметры:

• режим экспорта – «Пользователь»;
• в качестве пользователя выбрать того, от чьего имени велась работа на предыдущих шагах настоящей лабораторной работы;
• дополнительные объекты для экспорта – выбрать, например, записи таблицы, ограничения, триггеры, индексы и привилегии;
• настройки - выбрать сжатие;
• пути к файлам экспорта задать как С:\GGGG\SQL\, где GGGG соответствует номеру группы.

 В отчете зафиксировать размер (в байтах) файла резервной копии БД с сжатием. 

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

11. Восстановить БД из резервной копии. Для восстановления БД из резервной копии необходимо вызвать мастер утилиты импорта. Сделать это можно через пункт «Экспорт и импорт – Утилита импорта» в контекстном меню базы данных в проводнике или через главное меню: «База данных – Экспорт и импорт – Утилита импорта». Мастер предлагает ряд шагов для различных вариантов импорта, которые подробно описаны в методическом указании DbForge Studio for Oracle Database.

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

• путь к файлам – выбрать тот, который был указан на 9-м шаге текущей лабораторной работы;
• режим импорта – «Полный»;
• импортировать в – «Текущая схема»;
• инкрементный импорт – «Восстановить»;
• дополнительные объекты для импорта» - выбрать все категории.
•  в контейнере «Настройки» - оставить значения по умолчанию:

«Использовать существующие файлы данных», «Игнорировать ошибки создания объектов». Скопировать резервную копию БД на внешний носитель данных для использования ее в последующих лабораторных работах. 

12. В отчете зафиксировать снимок экрана с размером файла восстановленной БД (в байтах). Сравнить его с размерами  исходной БД, ее резервной копии с сжатием и без сжатия.

13. Составить отчет согласно правил. В отчете представить результаты выполнения пп. 1 - 11, с подробными комментариями и снимками экрана, а также описание одного из пунктов главного меню приложения dbForge Studio for SQL Oracle. Все задания должны быть пронумерованы.

14. Защитить лабораторную работу, положительно ответив под контролем информационной системы [2] на заданное число тестовых вопросов по теме настоящей лабораторной работы. 

БИБЛИОГРАФИЧЕСКИЙ СПИСОК
1. Маркин А.В. Программирование на SQL. В 2 ч. Часть 1. -  М.: Издательство Юрайт, 2019. - 403 с.
2. Дистанционное обучение и контроль знаний. [Электронный ресурс]. URL: https://rgrty.ru/sqltest/ 

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