метод лабы. Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего образования

• Название: Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего образования
• Дата: 30.12.2020
• Размер: 1.67 Mb.
• Имя файла: метод лабы.pdf
• Тип: Лабораторная работа; #165363
• страница: 2 из 3

Лабораторная работа № 3
Основы SQL. Создание баз данных.
Цель работы: Изучить операторы DDL и DML SQL. Научиться соз- давать базы данных в СУБД MySQL, PostgreSQL и SQLite.
Теоретический материал, необходимый для выполнения работы приведен в слайдах лекций и литературе [2, 4].
3.1 Задание
1) На основе, созданной в л. р. № 2 физической модели данных создать базу данных в СУБД MySQL (MariaDB) или PostgreSQL. Заполнить её дан- ными.
2) Используя операторы DML SQL cоставить запросы на ввод данных, запросы на обновление и удаление данных для проверки установленных пра- вил поддержки ссылочной целостности.
3) Наложить check-ограничения на столбцы таблиц базы данных. Про- верить работоспособность ограничений путем добавления в таблицы данных, удовлетворяющих и не удовлетворяющих условиям ограничений.
3.2 Отчет по лабораторной работе должен содержать:
- титульный лист;
- цель работы;
- задание;
- описание процесса создания базы данных (описание создания пустой базы данных, описание создания таблиц базы данных и заполнения их данными);
- заполненные данными таблицы базы данных;
- операторы DML SQL на ввод, обновление и удаление данных, ис- пользуемые для проверки правил поддержки ссылочной целостности; описание инструментальных средств, используемых для создания и выполнения этих DML SQL операторов.
3.3.
Пример создания базы данных в MySQL
Рассмотрим физическую модель базы данных предметной области
«Учебные заведения, абитуриенты и экзамены» (рис. 3.1).
Между таблицами «ВУЗ» и «ВУЗ_Специальность», «Специальность» и «ВУЗ_Специальность», «Абитуриент» и «Абитуриент_ ВУЗ_Специаль- ность», «ВУЗ_Специальность» и «Абитуриент_ВУЗ_Специальность»,
«Предмет» и «Экзамен», «Абитуриент_ВУЗ_Специальность» и «Экзамен» установлены связи «один ко многим». Все связи являются идентифицирую-

21 щими. Для поддержки ссылочной целостности данных между этими табли- цами установлены правила каскадного удаления и каскадного обновления.
Создадим базу данных на основе физической модели базы данных предметной области «Учебные заведения, абитуриенты и экзамены». Для создания базы данных необходимо установить СУБД MySQL.
Рисунок 3.1 – Физическая модель базы данных
«Учебные заведения, абитуриенты и экзамены»
Можно создать базу данных, пользуясь клиентом командной строки, ко- торый устанавливается вместе с СУБД MySQL (MySQL Command Line
Client). Можно установить утилиту EMS SQL Manager for MySQL и вос- пользоваться ей для создания базы данных. EMS SQL Manager for MySQL предоставляет программы-мастера, которые предоставляют удобный диало- говый интерфейс для создания базы данных и работы с ней. Запустим мастер создания базы данных ( База данных  Создать базу данных).

22
В диалоговых окнах (рис. 3.2) вводится информация, необходимая для создания базы данных. Раздел «Хост» предназначен для выбора места соз- дания файла базы данных. Раздел «Пользователь» служит для ввода имени администратора базы данных.
Рисунок 3.2 - Диалоговые окна создания базы данных
После создания базу данных необходимо регистрировать. Регистрация базы данных осуществляется с помощью пункта меню База данных  Заре-

23
гистрировать базу данных. В диалоговых окнах (рис. 3.3) вводится инфор- мация, необходимая для регистрации базы данных.
Рисунок 3.3 - Диалоговые окна регистрации базы данных
Доступ к основным компонентам базы данных в EMS SQL Manager for
MySQL осуществляется посредством окна «Базы данных» (рис. 3.4).

24
Рисунок 3.4 Окно «Базы данных»
EMS SQL Manager for MySQL предоставляет программы-мастера, ко- торые предоставляют удобный диалоговый интерфейс для создания таблиц базы данных. Мы же воспользоваться другим инструментом (Инструменты
 SQL-скрипт). С помощью инструмента «SQL- скрипт» выполним SQL- скрипт с описанием таблиц базы данных «Учебные заведения, абитуриенты и экзамены» и связей между ними.
Замечание. SQL-скрипт с описанием таблиц и связей создан в Toad Data
Modeller на основе физической модели (Model ->Generate DDL Script).
После выполнения SQL-скрипта получим семь таблиц (рис. 3.4).
// SQL-скрипт с описанием таблиц базы данных «Учебные заведения,
// абитуриенты и экзамены» и связей между ними
CREATE TABLE `vuz`
(
`Name_vuza` Char(10) NOT NULL,
`Adres` Char(20)
)
;
ALTER TABLE `vuz` ADD PRIMARY KEY (`Name_vuza`)
;
CREATE TABLE `specialnost`
(
`kod_spec` Int NOT NULL,
`Regim_obuch` Char(10),
`Srok_obuch` Int
)
;
ALTER TABLE `specialnost` ADD PRIMARY KEY (`kod_spec`)

25
;
CREATE TABLE `vuz_spec`
(
`Name_vuza` Char(10) NOT NULL,
`Kod_spec` Int NOT NULL,
`Kol_mest` Int
)
;
ALTER TABLE `vuz_spec` ADD PRIMARY KEY (`Name_vuza`,`Kod_spec`)
;
CREATE TABLE `Abiturient`
(
`Nom_pasp` Int NOT NULL,
`FIO` Char(30)
)
;
ALTER TABLE `Abiturient` ADD PRIMARY KEY (`Nom_pasp`)
;
CREATE TABLE `abiturient_vuz_spec`
(
`Nom_pasp` Int NOT NULL,
`Name_vuza` Char(10) NOT NULL,
`Kod_spec` Int NOT NULL
)
;
ALTER
TABLE
`abiturient_vuz_spec`
ADD
PRIMARY
KEY
(`Nom_pasp`,`Name_vuza`,`Kod_spec`)
;
CREATE TABLE `Predmet`
(
`Nom_predm` Int NOT NULL,
`Name_predm` Char(20)
)
;
ALTER TABLE `Predmet` ADD PRIMARY KEY (`Nom_predm`)
;
CREATE TABLE `examen`
(
`Ocenka` Int,

26
`Nom_pasp` Int NOT NULL,
`Name_vuza` Char(10) NOT NULL,
`Kod_spec` Int NOT NULL,
`Nom_predm` Int NOT NULL
)
;
ALTER
TABLE
`examen`
ADD
PRIMARY
KEY
(`Nom_pasp`,`Name_vuza`,`Kod_spec`,`Nom_predm`)
;
ALTER TABLE `vuz_spec` ADD CONSTRAINT `Relationship1` FOREIGN
KEY (`Name_vuza`) REFERENCES `vuz` (`Name_vuza`) ON DELETE CASCADE
ON UPDATE CASCADE
;
ALTER TABLE `vuz_spec` ADD CONSTRAINT `Relationship2` FOREIGN
KEY (`Kod_spec`) REFERENCES `specialnost` (`kod_spec`) ON DELETE
CASCADE ON UPDATE CASCADE
;
ALTER TABLE `abiturient_vuz_spec` ADD CONSTRAINT `Relation-
ship4`
FOREIGN
KEY
(`Nom_pasp`)
REFERENCES
`Abiturient`
(`Nom_pasp`) ON DELETE CASCADE ON UPDATE CASCADE
;
ALTER TABLE `abiturient_vuz_spec` ADD CONSTRAINT `Relation-
ship5` FOREIGN KEY (`Name_vuza`, `Kod_spec`) REFERENCES `vuz_spec`
(`Name_vuza`, `Kod_spec`) ON DELETE CASCADE ON UPDATE CASCADE
;
ALTER TABLE `examen` ADD CONSTRAINT `Relationship6` FOREIGN KEY
(`Nom_pasp`,
`Name_vuza`,
`Kod_spec`)
REFERENCES
`abiturient_vuz_spec` (`Nom_pasp`, `Name_vuza`, `Kod_spec`) ON DE-
LETE CASCADE ON UPDATE CASCADE
;
ALTER TABLE `examen` ADD CONSTRAINT `Relationship7` FOREIGN KEY
(`Nom_predm`) REFERENCES `Predmet` (`Nom_predm`) ON DELETE CASCADE
ON UPDATE CASCADE
;
EMS SQL Manager for MySQL предоставляет удобный интерфейс для заполнения таблиц данными. Чтобы воспользоваться им необходимо в окне
«Базы данных» (рис. 3.4) щелкнуть два раза левой кнопкой мыши по табли- це, перейти в появившемся окне на вкладку «Данные» и в панели инструмен-

27 тов нажать кнопку «+» (вставить запись). Заполнить запись данными и на- жать флажок «Фиксировать транзакцию».
Сначала заполняются таблицы не содержащие внешних ключей, а по- том таблицы с внешними ключами. Для базы данных «Учебные заведения, абитуриенты и экзамены» таблицы можно заполнять в следующей последо- вательности: vuz, specialnost, vuz_spec, abiturient, abiturient_vuz_spec, predmet, examen.
Также для заполнения таблиц данными можно воспользоваться опера- тором INSERT. Примеры операторов insert:
insert into vuz (Name_vuza, Adres) values ("АГАКИ", "пр. Ленина 56");
insert into vuz (Name_vuza, Adres) values ("АГУ", "пр. Ленина 65");
Для выполнения операторов INSERT, UPDATE, DELETE в EMS SQL
Manager for MySQL используется SQL-скрипт (Инструменты  SQL- скрипт).
На рисунках 3.5 – 3.7 представлены заполненные таблицы базы данных
«Учебные заведения, абитуриенты и экзамены».

28
Рисунок 3.5 – Таблицы базы данных «Учебные заведения, абитуриенты и экзамены»
Добавим в таблицы vuz и vuz_spec следующие данные:
insert into vuz (Name_vuza, Adres) values ("АГАКИ2", "пр. Ленина 56");
insert into vuz_spec (Name_vuza, Kod_spec, Kol_mest) values ("АГАКИ2",
111, 10);
Используя операторы UPDATE, DELETE, INSERT выполним проверку правил поддержки ссылочной целостности между таблицами vuz и vuz_spec.

29
Рисунок 3.6 – Таблицы базы данных «Учебные заведения, абитуриенты и экзамены»

30
Рисунок 3.7 – Таблицы базы данных «Учебные заведения, абитуриенты и экзамены»
Между таблицами vuz и vuz_spec со стороны таблицы vuz установлены правила: CASCADE UPDATE и CASCADE DELETE, т. е. при изменении значения первичного ключа в таблице vuz будут изменены соответствую- щие значения внешнего ключа в таблице vuz_spec и при удалении строки из таблицы vuz будут удалены те строки из таблице vuz_spec, у которых значение внешнего ключа совпадает со значением первичного ключа уда- ляемой строки. Примеры операторов UPDATE и DELETE:
update vuz set Name_vuza = "АГАКИ3" where Name_vuza = "АГАКИ2";
delete from vuz where Name_vuza = "АГАКИ3";
Замечание. В EMS SQL Manager for MySQL для просмотра в таблицах измененных данных нужно нажимать флажок «Фиксировать транзакцию».
Между таблицами vuz и vuz_spec со стороны таблицы vuz_spec дейст- вуют стандартные правила поддержки ссылочной целостности: вставка строки запрещена, если нет родительской строки; обновление внешнего ключа запрещено, если нет родительской строки; удаление строки всегда разрешено. Поэтому следующий оператор INSERT не будет выполнен:
insert into vuz_spec (Name_vuza, Kod_spec, Kol_mest) values ("АГАКИ4",
111, 10);

31
Лабораторная работа № 4
Основы SQL. Запросы. Представления.
Цель работы: Изучить возможности операторов SELECT и VIEW
SQL для создания запросов и представлений.
Теоретический материал, необходимый для выполнения работы приведен в слайдах лекций и литературе [2, 3, 4, 5].
4.1 Задание
1) Используя оператор SELECT языка SQL, создать для базы данных, которая была создана в лабораторной работе №3, следующие однотабличные и многотабличные запросы (МySQL (MariaDB) или PostgreSQL):

запрос, выводящий все строки таблицы;

запрос с порядком столбцов таблицы, отличным от исходного;

запрос, использующий WHERE с составным условием;

запросы с использованием IN, BETWEEN, LIKE в условии;

запрос с использованием какой-либо из агрегатных функций
COUNT, SUM, AVG, MAX, MIN;

Запросы, демонстрирующие использование команд ORDER BY,
GROUP BY и HAVING;

запросы, соединяющие таблицы с помощью JOIN и без него;

запросы с использованием подзапросов, возвращающих единствен- ное значение и множественные значения.
2) Создать для базы данных, которая была создана в лабораторной ра- боте №3, обновляемое и не обновляемое представления. Просмотреть хра- нимые данные, используя представления. Используя обновляемое представ- ление внести изменение в таблицу. Просмотреть полученный результат
(MySQL (MariaDB) или PostgreSQL).
4.2 Отчет по лабораторной работе должен содержать:
- титульный лист;
- цель работы;
- задание;
- формулировки запросов, описание запросов на языке SQL и результа- ты их выполнения;
- формулировки и SQL – коды представлений, результаты их выполне- ния;
- описание инструментальных средств, используемых для создания и выполнения SQL – запросов и SQL – представлений.
4.3.
Примеры создания запросов в MySQL

32
Для написания и отладки SQL-операторов можно использовать MySQL
Command Line Client или SQL-редактор EMS SQL Manager for MySQL (Ин- струменты  показать редактор SQL).
Для демонстрации возможностей оператора SELECT языка SQL cформулируем и выполним запросы к таблицам базы данных «Учебные заве- дения, абитуриенты и экзамены».
1) Вывод всех строк таблицы examen:
select * from examen;
2) Вывод всех строк таблицы examen с порядком столбцов отличным
от исходного:
select Kod_spec, Name_vuza, Nom_pasp, Nom_predm, Ocenka
from examen;
3) Запрос, использующий WHERE с составным условием:

Для вуза Алт.ГТУ вывести коды специальностей, номера предметов и номера паспортов абитуриентов, которые получили за экзамен оценки >= 4:
select Name_vuza, Kod_spec, Nom_pasp, Nom_predm, Ocenka
from examen
where Name_vuza = "Алт.ГТУ" AND Ocenka >= 4;
4) Запросы с использованием IN, BETWEEN и LIKE в условии:

вывести название вуза, коды специальностей, номера предметов и номера паспортов абитуриентов, которые получили за экзамен оцен- ки 4 или 5:
select Name_vuza, Kod_spec, Nom_predm, Nom_pasp, Ocenka
from examen
where Ocenka In (4,5);

вывести название вуза, коды специальностей, номера предметов и номера паспортов абитуриентов, которые получили за экзамен оцен- ки 3, 4 или 5:
select Name_vuza,Kod_spec, Nom_predm, Nom_pasp, Ocenka
from examen
where Ocenka between 3 AND 5;

33

вывести информацию об экзаменах для вузов, названия которых на- чинаются с “АГ”:
select * from examen
where Name_vuza Like "АГ%";
5) Запрос с использованием агрегатной функции COUNT:

подсчитать количество оценок “5”, полученных на экзаменах:
select COUNT(ocenka)from examen
where ocenka = 5;
6) Запрос, демонстрирующий использование команды GROUP BY:

подсчитать среднюю оценку для каждой специальности:
select Kod_spec, AVG(ocenka)
from examen
group by Kod_spec;
Результат выполнения запроса показан на рисунке 4.1.
Рисунок 4.1 7) Запрос с использованием команд HAVING и ORDER BY:

получить информацию о специальностях, на которых средний бал по экзаменам больше 3, упорядочив информацию по убыванию среднего балла:
select Kod_spec, AVG(ocenka)
from examen
group by Kod_spec
having AVG(ocenka)>3
order by AVG(ocenka) DESC;

34
Результат выполнения запроса показан на рисунке 4.2.
Рисунок 4.2 8) Соединение двух таблиц:

Получить информацию о предметах, специальностях, вузах и абиту- риентах, которые сдают экзамены.
Для выполнения этого запроса необходимо соединить таблицу predmet и таблицу examen по полю nom_predm.
Существует два способа внутреннего соединения таблиц – с помощью условия в разделе WHERE и с помощью оператора JOIN.
Первый способ:
select Name_predm, Kod_spec, Name_vuza, Nom_pasp
from predmet, examen
where predmet.Nom_predm = examen.Nom_predm;
Второй способ:
select Name_predm, Kod_spec, Name_vuza, Nom_pasp
from predmet join examen
on predmet.Nom_predm = examen.Nom_predm;
С помощью оператора JOIN можно выполнять и внешние соединения таблиц.
9) Запросы с подзапросами, возвращающими одно значение:

Вывести информацию об оценках, полученных абитуриентами на экзамене по дисциплине информатика:
select *
from examen
where Nom_predm =
(select Nom_predm
from predmet

35
where Name_predm ='Информатика');
Так как подзапрос стоит после знака равенства, он должен возвращать только одно значение. В случае, если подзапрос вернет более чем одно зна- чение, произойдет ошибка.

Вывести информацию о студентах, имеющих оценки по информати- ке ниже средней оценки по данной дисциплине:
select *
from examen
where Nom_predm = 3 AND Ocenka <
(select AVG(Ocenka)
from examen
where Nom_predm = 3);
Ограничение на подзапрос то же самое – он должен возвращать единст- венное значение. В случае, если подзапрос возвращает несколько значений, вместо операций сравнения нужно использовать, например, операцию IN.
10) Запросы с подзапросами, возвращающими несколько значений:

Получить информацию о вузах, в которых имеются специальности с количеством мест больше 20
select *
from vuz
where Name_vuza IN
(select Name_vuza
from vuz_spec
where Kol_mest > 20);
В запросах с подзапросами можно использовать операторы ANY и ALL.
Оператор ANY становится верным, если значение из запроса совпадает по крайней мере с одним значением из подзапроса.

Получить информацию о вузах, в которых имеется хотя бы одна специальность с количеством мест больше 20:
select *
from vuz
where Name_vuza = ANY
(select Name_vuza

36
from vuz_spec
where Kol_mest > 20);
Оператор ALL становится верным, если все значения из подзапроса равны значению-кандидату из внешнего запроса.

Вывести информацию о студентах, имеющих оценки по предметам ниже средних оценок по всем предметам: