🔽 Развернуть 🔽
Подумать над выбором предметной области для выполнения финальной (экзаменационной) работы. Выбирайте предметную область, которая вам интересна и в которой вы разбираетесь или хотите разобраться.
Сделать краткое описание выбранной предметной области (1-2 страницы). Если описание получится более объемным, не беда. Ведь это описание затем войдет в финальный отчет.
Попытаться сформулировать требования к будущей базе данных.
MAI STORE - официальный интернет-магазин Московского авиационного института (МАИ). Маркетплейс реализует разработку дизайна и продажу фирменных товаров МАИ (мерча).
Производители мерча создали линейки продукции, подходящие совершенно разным людям, но все они безусловно объединены духом традиций МАИ. На сегодняшний день маркетплейсом выпущено уже 6 уникальных коллекций товаров, в том числе коллекция Priority, разработанная совместно с брендом Henderson. Основная целевая аудитория интернет-магазина - аббитуриенты, студенты, выпускники и преподаватели университета.
Автоматизированная информационная система для учета товаров интернет-магазина MAI STORE (АИС «MAI STORE») — это прикладное программное обеспечение, предназначенное для помощи сотрудникам интернет-магазина «MAI STORE» производить автоматическое внесение, редактирование и поиск информации о товарах, поставщиках, складах, производителях и поставщиков.
Актуальность разработки системы определяется необходимостью автоматического хранения, вывода, изменения и удаления информации о товарах, продажи которых реализуются в интернет-магазине. Наличие подобной системы дает возможность оперировать с имеющиеся информацией пользователям (сотрудникам интернет магазине), не владеющим технологиями и спецификой работы с базами данных.
Цели создания ИС «MAI STORE» можно представить в следующем виде:
- Обеспечение единой базы хранения информации о товарах в интернет-магазине «MAI STORE»;
- Оптимизация обработки сведений о товарах интернет-магазина «MAI STORE».
Для выполнения поставленных целей необходимо решить следующие задачи:
- Произвести анализ предметной области разрабатываемой системы. Под предметной областью будем понимать функционирование склада интернет-магазина.
- Спроектировать базу данных и отношения между сущностями в ней.
- Разработать дизайн пользовательского интерфейса.
- Реализовать подсистемы ввода, вывода данных, редактирования, учета данных, предусматривающих интерфейс работы пользователя с ними.
Интернет-магазин – сайт, торгующий товарами в сети Интернет. Позволяет пользователям сформировать заказ на покупку, выбрать способ оплаты и доставки заказов в сети интернет. Совокупность отобранных товаров, способ оплаты и доставки представляют собой законченный заказ, который оформляется на сайте путем сообщения минимально необходимой информации о покупателе. Информация о покупателе может храниться в базе данных магазина если бизнес-модель магазина рассчитана на повторные покупки, или же отправляться разово. В интернет-магазинах, рассчитанных на повторные покупки, также ведется отслеживание возвратов посетителя и история покупок. Часто при оформлении заказа предусматривается возможность сообщить некоторые дополнительные пожелания от покупателя продавцу.
Как правило, интернет-магазин реализует именно продажу брендовых товаров, некоторые маркетплейсы также берут на себя функцию разработки дизайна продукции. Для производства продукции юридическое лицо, представляющее интернет-магазин, заключает договор с производителями. Под производителями понимаются юридические лица, осуществляющие производство продукции по заказам от клиентов.
Когда товар произведен, его необходимо доставить от места производства на склад интернет-магазина. Операцию транспортной перевозки осуществляют поставщики – юридические лица, реализующие функцию доставки товаров с производства на склад заказчика.
Склад - помещение или группа помещений, закрепленных за юридическим лицом, представляющим интернет-магазин, в виде договора аренды или покупки, предназначенное для хранения товара.
На складе происходит движение материальных и информационных потоков. Первые представлены движением товара от производителя на склад или со склада к покупателям, а информационные потоки представлены документацией, необходимой для этих операций. Единицей операции перемещения товара является поставка. В процессе реализации поставки склад принимает и складирует готовую продукцию, эта операция сопровождается формированием юридическим документом, именнуемым приходной накладной.
Нужды сотрудников интернет-магазина в складском, торговом, бухгалтерском и налоговом учете должны поддерживаться невидимой посетителям частью интернет-магазина — бэк-офисом. В рамках бэк-офиса требуется вести учет поступающих и списываемых с хранения товаров, поставщиков и производителей, формировать накладные, а также производить операции добавления, редактирования и поиска информации о поступающих товарах, их производителях, поставщиках, складах и поставках. Для реализации этих функций необходимо наличие специализированной базы данных (БД).
Из приведенного описания можно сделать вывод, что база данных склада интернет-магазина должна содержать следующие сущности:
- Производители
- Поставщики
- Поставки
- Товары (характеристики товара)
- Наличие товаров
- Склады
Экономически и эргономически эффективной практикой создания интернет-магазинов является применение специализированных информационных систем учета, которые берут на себя реализацию операций с базой данных и управления материальными и информационными потоками в виде пользовательского интерфейса.
Конечными пользователями информационной системы являются сотрудники, которые относятся к категории пользователей неосведомлённых в вопросах ведения, администрирования баз данных и поддержании их в актуальном состоянии. Пользователю таких систем должны быть предоставлены удобные интерфейсы взаимодействия с данными обо всех упомянутых ранее сущностях.
Для формирования требований к описанной базе данных и информационной системе в целом используем методологию описания прецедентов, которая заключается в описании и анализе типичных процессов, происходящих в рамках предметной области.
На основе исследования предметной области можно выделить следующий список прецедентов: o Внесение информации о сущностях в базу данных o Редактирование информации о сущностях o Удаление информации о сущностях o Поиск информации о сущностях по заданным пользователем параметрам o Сортировка выводимой информации о сущностях по заданным пользователем параметрам
Приведем описание полученных прецедентов.
| Имя прецедента | Внесение информации о сущностях в базу данных |
|---|---|
| Исполнители | Сотрудник интернет-магазина |
| Описание | Основной поток: пользователь делает попытку добавить информацию об объекте (товаре, поставщике, поставке, складе, производителе) с помощью кнопку «Добавить» в отведенном для работы с определенной сущностью окне. Система открывает диалоговое окно для добавления объекта соответствующей сущности.Пользователь вводит обязательные и необязательные атрибуты объекта. После чего он нажимает кнопку «OK» для подтверждения операции добавления объекта в базу данных. Альтернативный поток: если пользователь попытается добавить информацию о уже существующем объекте – выводится соответствующая ошибка. Если пользователь при добавлении информации об объекте не указывает его обязательные атрибуты – выводится соответствующее предупреждение об ошибке. |
| Предусловия | Пользователь запустил программу. |
| Постусловия | В случае успешного выполнения операции добавления, будет сделана запись соответствующей добавляемой сущности в таблицу базы данных. |
| Имя прецедента | Редактирование информации о сущностях в базе данных |
|---|---|
| Исполнители | Сотрудник интернет-магазина |
| Описание | Основной поток: пользователь делает попытку изменить информацию об объекте (товаре, поставщике, поставке, складе, производителе) с помощью кнопку «Изменить» в отведенном для работы с определенной сущностью окне. Система определяет идентификатор записи, которая подлежит изменению и открывает диалоговое окно для изменения объекта соответствующей сущности. Пользователю выводится форма с текущими атрибутами объекта, которые он изменяет с помощью графического интерфейса. После чего он нажимает кнопку «OK» для подтверждения операции изменения объекта в базу данных. Альтернативный поток: если измененная пользователем информация об объекте дублируется в базе данных - выводится соответствующая ошибка.Если пользователь при изменении информации об объекте не указывает его обязательные атрибуты – выводится соответствующее предупреждение об ошибке. |
| Предусловия | Пользователь запустил программу. |
| Постусловия | В случае успешного выполнения операции изменения, будет обновлена запись соответствующей изменяемой сущности в таблице базы данных. |
| Имя прецедента | Удаление информации о сущностях в базе данных |
|---|---|
| Исполнители | Сотрудник интернет-магазина |
| Описание | Основной поток: пользователь делает попытку удалить информацию об объекте (товаре, поставщике, поставке, складе, производителе) с помощью кнопку «Удалить» в отведенном для работы с определенной сущностью окне. Система определяет идентификатор записи, которая подлежит удалению. Пользователю выводится предупредительное диалоговое окно для попытки удаления. После чего нажимает кнопку «OK» для подтверждения операции удаления объекта из базы данных. Альтернативный поток: если удаляемая пользователем запись в базе данных принадлежит имеет ссылку на внешнюю дочернюю таблицу, то удаление записи является невозможным без предварительного удаления соответствующей записи в дочерней таблице – выводится соответствующее предупреждение об ошибке |
| Предусловия | Пользователь запустил программу. |
| Постусловия | В случае успешного выполнения операции удаления, будет удалена запись соответствующей удаляемой сущности в таблице базы данных. |
| Имя прецедента | Сортировка информации о сущностях в базе данных |
|---|---|
| Исполнители | Сотрудник интернет-магазина |
| Описание | Основной поток: пользователь делает попытку сортировать информацию об объектах (товарах, поставщиках, поставках, складах, производителях) с помощью кнопку «Сортировать» в отведенном для работы с определенной сущностью окне. Для сортировки пользовать выбирает критерий сортировки – столбец таблицы. Система определяет заданный критерий и производит сортировку по нему и выводит отсортированную информацию в табличном представлении. Альтернативный поток: отсутствует. |
| Предусловия | Пользователь запустил программу. Пользователь выбрал критерий сортировки – столбец таблицы, подлежащей сортировке. |
| Постусловия | В случае успешного выполнения операции сортировки исходная таблица сортируемой сущности будет отсортирована по заданному пользователем критерию. |
| Имя прецедента | Поиск информации о сущностях в базе данных |
|---|---|
| Исполнители | Сотрудник интернет-магазина |
| Описание | Основной поток: пользователь делает попытку поиска информации об объектах (товарах, поставщиках, поставках, складах, производителях) с помощью кнопку «Поиск» в отведенном для работы с определенной сущностью окне. Для поиска пользовать выбирает критерий поиска – столбец таблицы и вводим искомое значение в специально отведенном поле. Система определяет заданный критерий и производит поиск по полному совпадению в случае, если указанный критерий является числовым, и полному и частичному совпадению в случае, если указанный критерий является текстовым. Система выводит найденную информацию Альтернативный поток: в случае отсутствия искомой информации система выводит пользователю пустую таблицу. |
| Предусловия | Пользователь запустил программу. Пользователь выбрал критерий поиска – столбец таблицы, по которому будет производиться поиск. Пользователь указал искомое значение столбца, на основе которого будет производится поиск по полному или частичному совпадению |
| Постусловия | В случае успешного выполнения операции поиска исходная таблица фильтруемой сущности будет изменена на таблицу, содержащую только те записи, которые удовлетворяют условию полного или частичного совпадения с заданным пользователем значением. |
На основе описанния предметной области и прецедентов формируется первоначальная концептуальная модель предметной области:
.jpeg)
🔽 Развернуть 🔽
Дан запрос:
INSERT INTO aircrafts
VALUES ( 'SU9', 'Sukhoi SuperJet-100', 3000 );Запрос выполняется с ошибкой:
ОШИБКА: повторяющееся значение ключа нарушает ограничение
уникальности "aircrafts_pkey"
ПОДРОБНОСТИ: Ключ "(aircraft_code)=(SU9)" уже существует.
Подумайте, почему появилось сообщение.
Ответ: указанная операция не выполниться, так как атрибут
aircraft_codeв таблицеaircraftsявляется первичным ключем и по определению должен быть уникальным. Строка с индексом 'SU9' уже содержиться в таблице.
Самостоятельно напишите команду для выборки всех строк из таблицы aircrafts, чтобы строки были упорядочены по убыванию значения атрибута «Максимальная дальность полета, км» (range).
Ответ: команда для выборки всех строк из таблицы
aircraftс сортировкой по убыванию относительно атрибута range:
SELECT *
FROM bookingaircrafts
ORDER BY aircraft_code DESC;Результат запроса:
aircraft_code | model | range
---------------+---------------------+-------
SU9 | Sukhoi SuperJet-100 | 3500
CR2 | Bombardier CRJ-200 | 2700
CN1 | Cessna 208 Caravan | 1200
773 | Boeing 777-300 | 11100
763 | Boeing 767-300 | 7900
733 | Boeing 737-300 | 4200
321 | Airbus A321-200 | 5600
320 | Airbus A320-200 | 5700
319 | Airbus A319-100 | 6700
Самостоятельно напишите команду UPDATE полностью, при этом не забудьте, что увеличить дальность полета нужно только у одной модели — Sukhoi SuperJet, поэтому необходимо использовать условие WHERE. Затем с помощью команды SELECT проверьте полученный результат.
Ответ: команда для увеличения значения
rangeв два раза у моделиSukhoi SuperJet-100следующая:
UPDATE aircrafts SET range = range * 2
WHERE model = 'Sukhoi SuperJet-100';
SELECT range
FROM aircrafts
WHERE model = 'Sukhoi SuperJet-100';Самостоятельно смоделируйте описанную ситуацию, подобрав условие, которому гарантированно не соответствует ни одна строка в таблице «Самолеты» (aircrafts).
Ответ: пример SQL запроса на данной БД который не удалит не одной строки в таблице:
DELETE FROM aircrafts WHERE range < 0;🔽 Развернуть 🔽
Ответ: создадим таблицу test_numetic и заполним ее числами numeric с различной точностью:
CREATE TABLE test_numeric(
measurement numeric,
description text
);
INSERT INTO test_numeric
VALUES (1234567890.0987654321, 'Точность 20 знаков, масштаб 10 знаков'),
(1.5, 'Точность 2 знака, масштаб 1 знак' ),
(0.12345678901234567890, 'Точность 21 знак, масштаб 20 знаков'),
(1234567890, 'Точность 10 знаков, масштаб 0 знаков (целое число)');
SELECT *
FROM test_numeric;
DROP TABLE test_numeric;Результат запроса:
measurement | description
------------------------+----------------------------------------------------
1234567890.0987654321 | Точность 20 знаков, масштаб 10 знаков
1.5 | Точность 2 знака, масштаб 1 знак
0.12345678901234567890 | Точность 21 знак, масштаб 20 знаков
1234567890 | Точность 10 знаков, масштаб 0 знаков (целое число)
Ответ: посмотрим поведение PostgreSQL на верхних границах допустимых значений типов real и double precision
/* Границы типа double precision 1E-307 до 1E+308 с точностью 15. Для очень больших (на границе) принимается в расчет только первые 16 старших десятичных разрядов */
SELECT '1e+308'::double precision + '1e+89'::double precision = '1e+308'::double precision;
-- True
-- В данном случае 17 старший разряд обрезается, поэтому числа считаются равными
/* У типа real границы следующие 1E-37 до 1E+37, а точность 6 на них поведение идентично типу double precision */
SELECT '1e+38'::real + '1e+31'::real = '1e+38'::real;
-- False
-- В данном случае старший десятичный разряд учитывается следовательно числа не равны
SELECT '1e+38'::real + '1e+30'::real = '1e+38'::real;
-- True
-- В данном случае вторая единица не учитывается, поэтому числа будут считаться равнымиОтвет: создадим таблицу test_serial и поупражняемся в работе со столбцом типа series, являющимся первичным ключом.
CREATE TABLE test_serial(
PRIMARY KEY (id),
id serial,
name text
);
INSERT INTO test_serial (name)
VALUES ('Вишневая');
-- Для данной записи будет присвоено id=1, для следующего id=2
INSERT INTO test_serial (id, name)
VALUES ( 2, 'Прохладная' );
-- В данном случае мы явным образом указываем id, при этом обновление последовательности для id не происходит, поэтому при добавлении следующей записи id по прежнему 2, что нарушает условие уникальности первичного ключа
INSERT INTO test_serial (name)
VALUES ('Грушевая');
-- Ошибка, так как запись с id=2 уже существует, однако последовательность serial уже обновилась (обновление последовательности происходит раньше, чем проверка условия уникальности первичного ключа)
INSERT INTO test_serial (name)
VALUES ('Грушевая');
-- Запрос выполняется успешно, так как последовательность обновилось несмотря на ошибку при прошлом запросе. Текущее значение последовательность id=3.
INSERT INTO test_serial (name)
VALUES ('Зеленая');
--Запрос выполняется успешно. Текущий id=4.
DELETE
FROM test_serial
WHERE id = 4;
--Удаляем строку с id=4, однако значение последовательности при этом остается неизменным.
INSERT INTO test_serial (name)
VALUES ('Луговая');
-- Запрос выполняется успешно. Запись добавлена с id=5.
SELECT *
FROM test_serial;
DROP TABLE test_serial;Результат запроса:
id | name
----+------------
1 | Вишневая
2 | Прохладная
3 | Грушевая
5 | Луговая
Ответ: поэкспериментируем с форматом даты в PostgreSQL (параметр datestyle) - используем традиционный стиль и региональный стиль German.
-- Запрос для установки формата в традиционный стиль
SET datestyle TO DEFAULT;
-- Результат: 17.12.1997
SELECT '17.12.1997'::date;
-- Ошибка так как вторым значением по формату даты DMY является месяц
SELECT '12.17.1997'::date;
/*
Поменяем формат даты на 'German, MDY' и теперь данный запрос успешно выполниться
*/
-- Запрос для установки формата в региональный стиль
SET datestyle TO 'German, MDY';
-- Результат: 17.12.1997
SELECT '12.17.1997'::date;
/* В качестве эксперимента повторим то же самое с форматом даты SQL*/
SET datestyle TO 'SQL, DMY';
-- Результат: 17/12/1997
SELECT '17/12/1997'::date;
-- Ошибка
SELECT '12/17/1997'::date;
-- Изменим формат даты SQL
SET datestyle TO 'SQL, MDY';
-- Теперь запрос отрабатывает без ошибок
SELECT '12/17/1997'::date;
Ответ: поэкспериментируем с форматированием метки времени в строку с помощью функции to_char:
-- Вывод в формате 'минута:секунда' (например, 47:29)
SELECT to_char(current_timestamp, 'mi:ss');
-- Вывод в формате 'номер дня в месяце' (например, 12)
SELECT to_char(current_timestamp, 'dd');
-- Вывод текущей даты в численном формате 'год-месяц-день' (например, 2022-10-12)
SELECT to_char(current_timestamp, 'yyyy-mm-dd');
-- Вывод текущей даты в численном формате 'год-месяц-день:число секунд с начала суток' (например, 2022-10-12:75005)
SELECT to_char( current_timestamp, 'yyyy-mm-dd:SSSS' );
-- Вывод текущей даты в численном формате 'год месяц(текстом) день' (например, 2022 OCTOBER 12)
SELECT to_char( current_timestamp, 'yyyy MONTHdd' ); Ответ:: при добавлении интервала PostgreSQL учитывает различное число дней в месяцах, так, например, при добавлении к дате, соответствующей концу какого либо месяца, СУБД автоматически просматривает число дней в следующем месяце и, в случае если оно меньше, то в качестве результата запроса используется последнее число следующего месяца. Проверим это на примерах.
/* Добавляем интервал в 1 месяц к 31 января. Февраль в 2016 году содержит 29 дней, поэтому результатом запроса является 29 февраля 2016 года.*/
SELECT to_char(('2016-01-31'::date + '1 mon'::interval) :: timestamp, 'yyyy-mm-dd') AS new_date;Результат запроса:
new_date
---------------------
2016-02-29 00:00:00
/* Добавляем интервал в 1 месяц к 29 февраля. По итогу запроса должны получить дату 29 марта 2016 года.*/
SELECT to_char(('2016-02-29'::date + '1 mon'::interval) :: timestamp, 'yyyy-mm-dd') AS new_date;Результат запроса:
new_date
---------------------
2016-03-29 00:00:00
(1 row) */
Ответ: поэкспериментируем с типом данных boolean и проверим достимые значения столбца этого типа на примере таблицы test_bool.
CREATE TABLE test_bool(
a boolean,
b text
);
/* Допустимые boolean значения:
TRUE, true, 't', 'true', 'y', 'yes', 'on', '1'
FALSE, false, 'f', 'false', 'n', 'no', 'off', '0'
*/
-- Запрос корректен: TRUE является ключевым словом типа boolean
INSERT INTO test_bool
VALUES (TRUE, 'yes');
-- Запрос некорректен: токен yes не зарезервирован под boolean
INSERT INTO test_bool
VALUES (yes, 'yes');
-- Запрос корректен: второй аргумент неявным образом преобразуется в строку
INSERT INTO test_bool
VALUES ('yes', true);
-- Запрос корректен: строка 'yes' зарезервирована под тип boolearn и неявным образом преобразуется в true, в свою очередь TRUE неявным образом преобразуется в строку
INSERT INTO test_bool
VALUES ('yes', TRUE);
-- Запрос корректен: строка '1' зарезервирована под тип boolearn и неявным образом преобразуется в true
INSERT INTO test_bool
VALUES ('1', 'true');
-- Запрос некорректен: токен 1 не зарезервирован под boolean
INSERT INTO test_bool
VALUES (1, 'true');
-- Запрос корректен: строка 't' зарезервирована под тип boolean и неявным образом преобразуется в true
INSERT INTO test_bool
VALUES ('t', 'true');
-- Запрос некорректен: токен truth не зарезервирован под boolean
INSERT INTO test_bool
VALUES ('t', truth);
-- Запрос корректен: true неявным образом преобразуется в строку
INSERT INTO test_bool
VALUES (true, true);
-- Запрос корректен: конвертация любого числа, кроме 0, в boolean дает TRUE
INSERT INTO test_bool
VALUES (1::boolean, 'true');
-- Запрос корректен: аналогично предыдущему
INSERT INTO test_bool
VALUES (111::boolean, 'true');
SELECT *
FROM test_bool;
DROP TABLE test_bool;Ответ: создадим таблицу pilots с полями pilot_name (имя пилота), schedule (раписание полетов) и meal(обеды). При этом столбцы schedule и meal будут является массивом чисел и двумерным тестовым массивом соответственно. Поэкспериментируем в работе с массивами, выполнив несколько запросов на выборку и обновление.
CREATE TABLE pilots(
pilot_name text,
schedule integer[],
meal text[][]
);
/*Добавим строки в созданную таблицу:*/
INSERT INTO pilots
VALUES( 'Ivan', '{ 1, 3, 5, 6, 7 }'::integer[],
'{
{ "сосиска", "макароны", "кофе" },
{ "куриное филе", "пюре", "какао" },
{ "рагу", "сэндвич с семгой", "морс ягодный" },
{ "шарлотка яблочная", "гречка", "компот вишевый" },
{ "омлет с овощами", "бекон", "кофе" }
}'::text[][]
),
(
'Petr', '{ 1, 2, 5, 7 }'::integer[],
'{
{ "котлета", "каша", "кофе" },
{ "куринная отбивная", "рис", "компот" },
{ "манная каша", "билины с мясом", "компот" },
{ "мясо запеченное", "пюре", "какао" }
}'::text[][]
),
(
'Pavel', '{ 2, 5 }'::integer[],
'{
{ "сосиска", "каша", "кофе" },
{ "мясо запеченное", "пюре", "какао" }
}'::text[][]
),
(
'Boris', '{ 3, 5, 6 }'::integer[],
'{
{ "котлета", "каша", "чай" },
{ "куринная отбивная", "рис", "компот" },
{ "сосиска", "макароны", "кофе" }
}'::text[][]
);
SELECT *
FROM pilots;Результат запроса:
pilot_name | schedule | meal
Ivan | {1,3,5,6,7} | {{сосиска,макароны,кофе},
{"куриное филе",пюре,какао},
{рагу,"сэндвич с семгой","морс ягодный"},
{"шарлотка яблочная",гречка,"компот вишевый"},
{"омлет с овощами",бекон,кофе}}
Petr | {1,2,5,7} | {{котлета,каша,кофе},
{"куринная отбивная",рис,компот},
{"манная каша","билины с мясом",компот},
{"мясо запеченное",пюре,какао}}
Pavel | {2,5} | {{сосиска,каша,кофе},
{"мясо запеченное",пюре,какао}}
Boris | {3,5,6} | {{котлета,каша,чай},
{"куринная отбивная",рис,компот},
{сосиска,макароны,кофе}}
Выведем имена пилотов которые в первый день их работы едят макароны или рис:
SELECT pilot_name, meal
FROM pilots
WHERE meal[1][1] IN('макароны','рис')
OR meal[1][2] IN('макароны','рис')
OR meal[1][3] IN('макароны','рис');Результат запроса:
pilot_name | meal
Ivan | {{сосиска,макароны,кофе},
{"куриное филе",пюре,какао},
{рагу,"сэндвич с семгой","морс ягодный"},
{"шарлотка яблочная",гречка,"компот вишевый"},
{"омлет с овощами",бекон,кофе}}
Изменим расписание полетов пилота Boris и его меню в первый день работы:
UPDATE pilots
SET schedule[1] = 2,
meal[1][:] = '{"груша", "куриная грудка", "чай"}' :: text[]
WHERE pilot_name='Boris';
SELECT *
FROM pilots
WHERE pilot_name='Boris';
DROP TABLE pilots;Результат запроса:
pilot_name | schedule | meal
Boris | {2,5,6} | {{груша,"куриная грудка",чай},
{"куринная отбивная",рис,компот},
{сосиска,макароны,кофе}}
Ответ: продемонстрируем функции для работы с JSON в PostreSQL из документации.
/* Функция to_json() преобразует типы PostgreSQL в строку json*/
SELECT to_json('Hello world!'::text);
/*
to_json
---------------------
"Hello world!"
(1 row)*/
SELECT to_json('{"sports": "хоккей", "trips": 5 }'::text);
/*
to_json
---------------------
"{\"sports\": \"хоккей\", \"trips\": 5 }"
*/
/*Функция json_build_object() предназначена для построения json строки из кортежа формата (ключ_1, значение_1, ключ_2, значение_2,..., ключ_n, значение_n)*/
SELECT json_build_object('sports', 'хоккей', 'trips', 25);
/*
json_build_object
------------------------
{"sports":"хоккей","trips":25}
*/
/* Функция json_object_keys() предназначена для получения ключей JSON строки */
SELECT json_object_keys('{"apple": 150, "banana": 25, "pineapple": 10}');
/*
json_object_keys
------------------
apple
banana
pineapple
*/🔽 Развернуть 🔽
Приведем запросы для создания таблиц "Студенты" (students) и "Успеваемость" (progress) , с которыми мы будем работать при выполнении заданий:
/* Создаем таблицу для хранения данных о студентах students */
CREATE TABLE students(
PRIMARY KEY (record_book),
record_book numeric(5) NOT NULL, -- номер зачетной книжки
name text NOT NULL, -- ФИО студента
doc_ser numeric(4), -- серия документа
doc_num numeric(6) -- номер документа
);
/*Создаем таблицу для хранения данных об успеваемости студентов progress */
CREATE TABLE progress(
FOREIGN KEY (record_book)
REFERENCES students (record_book)
ON DELETE CASCADE
ON UPDATE CASCADE
record_book numeric(5) NOT NULL, -- номер зачетной книжки
subject text NOT NULL, -- название предмета
acad_year text NOT NULL, -- академический год
term numeric(1) NOT NULL -- номера семестра
CHECK (term = 1 OR term = 2),
mark numeric(1) NOT NULL -- оценка
CHECK (mark >= 2 AND mark <= 5) DEFAULT 5,
);Ответ: Посмотрим, какие ограничения уже наложены на атрибуты таблицы «Успеваемость» (progress).
edu=# \d progress
Таблица "public.progress"
Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию
-------------+--------------+--------------------+-------------------+--------------
record_book | numeric(5,0) | | not null |
subject | text | | not null |
acad_year | text | | not null |
term | numeric(1,0) | | not null |
mark | numeric(1,0) | | not null | 5
Ограничения-проверки:
"progress_mark_check" CHECK (mark >= 2::numeric AND mark <= 5::numeric)
"progress_term_check" CHECK (term = 1::numeric OR term = 2::numeric)
Ограничения внешнего ключа:
"progress_record_book_fkey" FOREIGN KEY (record_book) REFERENCES students(record_book) ON UPDATE CASCADE ON DELETE CASCADE
Добавим в таблицу progress еще один атрибут — «Форма проверки знаний» (test_form), который можетпринимать только два значения: «экзамен» или «зачет». Тогда набор допустимых значений атрибута «Оценка» (mark) будет зависеть от того, экзамен или зачет предусмотрены по данной дисциплине. Если предусмотрен экзамен, тогда допускаются значения 3, 4, 5, если зачет — тогда 0 (не зачтено) или 1 (зачтено).
-- Добавим в таблицу progress колонку test_form
ALTER TABLE progress
ADD COLUMN test_form text;
-- А также дополнительное условие
ALTER TABLE progress
ADD CHECK ((test_form = 'экзамен' AND mark IN (3,4,5))
OR
(test_form = 'зачет' AND mark IN (0, 1))
);Проверим, как будет работать новое ограничение в модифицированной таблице progress. Для этого выполним команды INSERT, как удовлетворяющие ограничению, так и нарушающие его.
-- Добавим данные
INSERT INTO students
VALUES (24014, 'Rysistov A.V', 4524, 153335);
SELECT * FROM students;
-
-- Запись с экзаменом добавляется корректно
INSERT INTO progress
VALUES (24014, 'Математический анализ', '2022-2023', 1, 5, 'экзамен');
SELECT * FROM progress;
/* Запись с зачетом добавляется с ошибкой, так как срабатывает проверка progress_mark_check, которое мы создавали при инициализации таблицы*/
INSERT INTO progress
VALUES (24014, 'Психология', '2021-2022', 1, 1, 'зачет');
/* ERROR: new row for relation "progress" violates check constraint "progress_mark_check"*/
-- Удалим проверку progress_mark_check, так как ее полностью покрывает проверка test_form
ALTER TABLE progress
DROP CONSTRAINT progress_mark_check;
-- Произведем добавление данных повторно, чтобы удостовериться в том, что запрос отработает корректно
INSERT INTO progress
VALUES (24014, 'Психология', '2021-2022', 1, 1, 'зачет');
SELECT * FROM progress;Добавим новое ограничение в таблицу progress на атрибут acad_year (академический год). Ограничим возможные значения столбца acad_year: теперь значения из этого столбца должны представлять собой два учебных года, написанных через дефис, причем возможные значения года лежат в диапазоне от 2000 до 2099 года включительно.
-- Добавляем заявленную проверку
ALTER TABLE progress
ADD CHECK (acad_year ~ $$^20[0-9]{2}\-20[0-9]{2}$$);
-- Протестируем установленное ограничение, добавив в таблицу корректные и некорректные примеры:
-- Корректный пример значения столбца acad_year:
INSERT INTO progress
VALUES (24014, 'Проектирование баз данных', '2021-2022', 1, 5, 'экзамен');
SELECT * FROM progress;
-- Некорректный пример значения столбца acad_year:
INSERT INTO progress
VALUES (24014, 'Управление IT-проектами', '2021--2022', 1, 1, 'зачет');
SELECT * FROM progress;
/*ERROR: new row for relation "progress" violates check constraint "progress_acad_year_check"*/Добавленное ограничение работает корректно.
В таблице «Студенты» (students) есть текстовый атрибут name, на который наложено ограничение NOT NULL. Проверим, что будет, если при вводе новой строки в эту таблицу дать атрибуту name в качестве значения пустую строку.
/* При добавлении пустых строчек в колонках типа text NOT NULL никаких ошибок не возникает
Следующий запрос выполнится без ошибок и строка будет добавлена в таблицу:*/
INSERT INTO students
VALUES (83515, ' ', 5353, 98373); Исправим это, добавив ограничение на имя студента.
-- Удалим все записи из таблицы students, в которых имя является пустой строкой
DELETE *
FROM students
WHERE TRIM(name) = '';
-- Добавим новую проверку в таблицу
ALTER TABLE students
ADD CHECK (TRIM(name) <> '');
-- Попробуем произвести вставку. Теперь запрос выполнится с ошибкой.
INSERT INTO students
VALUES (83515, ' ', 5353, 98373); Отметим, что такими же "слабыми местами" обладает и таблица progress, в которой также есть текстовые поля. Исправим этот недостаток, наложив ограничение на текстовые столбцы таблицы progress
ALTER TABLE progress
ADD CHECK (TRIM(subject) <> '');
ADD CHECK (TRIM(acad_year) <> '');Подумаем, какие представления было бы целесообразно создать для нашей базы данных «Авиаперевозки». Необходимо учесть наличие различных групп пользователей, например: пилоты, диспетчеры, пассажиры, кассиры. Создайте представления и проверьте их в работе.
Создадим представление с вылетами из Москвы. Представление будет содержать следующие столбцы:
- номер рейса (flight_no);
- город отправления (departure_city)
- аэропорт отправления (departure_airport);
- город отправления (arrival_city)
- аэропорт прибытия (arrival_airport);
- время вылета по расписанию (scheduled_departure)
- время посадки по расписанию (scheduled_arrival)
- статус рейса (status)
- код самолета (aircraft_code)
- реальное время отправления (actual_departure)
- реальное время посадки (actual_arrival)
CREATE OR REPLACE VIEW flights_from_Moscow AS
SELECT
temp.flight_no,
temp.departure_city,
temp.departure_airport,
aa.city as arrival_city,
aa.airport_name as arrival_airport,
temp.scheduled_departure,
temp.scheduled_arrival,
temp.status,
temp.aircraft_code,
temp.actual_departure,
temp.actual_arrival
FROM (
SELECT
f.flight_no,
a.airport_name as departure_airport,
f.arrival_airport,
a.city as departure_city,
f.scheduled_departure,
f.scheduled_arrival,
f.status,
f.aircraft_code,
f.actual_departure,
f.actual_arrival
FROM
bookings.flights as f
LEFT JOIN bookings.airports as a
ON f.departure_airport = a.airport_code
WHERE a.city = 'Москва'
) as temp
LEFT JOIN bookings.airports as aa
ON temp.arrival_airport = aa.airport_code;Проверим полученное представление в действии. Выполним несколько запросов:
-- Выберем первые 10 строк представления. Для экономии размера выводимой таблицы ограничим количество столбцов
SELECT flight_no, departure_city, departure_airport, arrival_city, arrival_airport
FROM flights_from_Moscow
LIMIT 10;Результат запроса:
flight_no | departure_city | departure_airport | arrival_city | arrival_airport
-----------+----------------+-------------------+-----------------+-----------------
PG0405 | Москва | Домодедово | Санкт-Петербург | Пулково
PG0404 | Москва | Домодедово | Санкт-Петербург | Пулково
PG0405 | Москва | Домодедово | Санкт-Петербург | Пулково
PG0402 | Москва | Домодедово | Санкт-Петербург | Пулково
PG0405 | Москва | Домодедово | Санкт-Петербург | Пулково
PG0404 | Москва | Домодедово | Санкт-Петербург | Пулково
PG0403 | Москва | Домодедово | Санкт-Петербург | Пулково
PG0402 | Москва | Домодедово | Санкт-Петербург | Пулково
PG0405 | Москва | Домодедово | Санкт-Петербург | Пулково
PG0402 | Москва | Домодедово | Санкт-Петербург | Пулково
Посчитаем количество рейсов из Москвы в Санкт-Петербург:
SELECT count(*)
FROM flights_from_Moscow
WHERE departure_city = 'Москва' AND arrival_city = 'Санкт-Петербург'
-- 732Посчитаем количество вылетов из каждого московского аэропорта
SELECT count(*), departure_airport
FROM flights_from_Moscow
GROUP BY departure_airport;Результат запроса:
count | depature_airport
-------+------------------
1719 | Внуково
2981 | Шереметьево
3217 | Домодедово
Подумаем, какие еще таблицы было бы целесообразно дополнить столбцами типа json/jsonb. Вспомните, что, например, в таблице «Билеты» (tickets) уже есть столбец такого типа — contact_data. Выполните модификации таблиц и измените в них одну-две строки для проверки правильности ваших решений.
Добавим в таблицу bookings в качестве json поля информамцию о периоде действия брони: начало действия брони и ее окончание
-- Добавляем столбец booking_period в таблицу bookings
ALTER TABLE bookings.bookings
ADD COLUMN booking_period jsonb;
-- Обновим одну из строк таблицы:
UPDATE bookings.bookings
SET booking_period = '{"booking_start": "06.10.2020",
"booking_end": "16.10.2020"}'
WHERE book_ref='000181';
SELECT *
FROM bookings.bookings
WHERE book_ref='000181';Результат запроса:
book_ref | book_date | total_amount | booking_period
----------+------------------------+--------------+-------------------------------------------------------------
000181 | 2016-10-08 12:28:00+03 | 131800.00 | {"booking_end": "16.10.2020", "booking_start": "06.10.2020"}
🔽 Развернуть 🔽
Глава 6 (упражнения 2, 7, 9, 13, 19, 21, 23)Этот запрос выбирает из таблицы «Билеты» (tickets) всех пассажиров с именами, состоящими из трех букв (в шаблоне присутствуют три символа «_»):
SELECT passenger_name
FROM tickets
WHERE passenger_name LIKE '___ %';Предложите шаблон поиска в операторе LIKE для выбора из этой таблицы всех пассажиров с фамилиями, состоящими из пяти букв.
Решение:
SELECT passenger_name
FROM tickets
WHERE passenger_name LIKE '% _____';Результаты запроса:
passenger_name
------------------
ILYA POPOV
VLADIMIR POPOV
PAVEL GUSEV
LEONID ORLOV
EVGENIY GUSEV
NIKOLAY FOMIN
EKATERINA ILINA
ANTON POPOV
ARTEM BELOV
VLADIMIR POPOV
ALEKSEY ISAEV
...
Дополнительно подсчитаем количество имен, удовлетворяющих шаблону:
SELECT count(passenger_name)
FROM tickets
WHERE passenger_name LIKE '% _____';Результаты запроса:
count
-------
14272
(1 row)
Самые крупные самолеты в нашей авиакомпании — это Boeing 777-300. Выяснить, между какими парами городов они летают, поможет запрос:
SELECT DISTINCT departure_city, arrival_city
FROM routes r
JOIN aircrafts a
ON r.aircraft_code = a.aircraft_code
WHERE a.model = 'Boeing 777-300'
ORDER BY 1;Модифицируйте запрос таким образом, чтобы каждая пара городов была выведена только один раз
Решение:
SELECT DISTINCT r.departure_city, r.arrival_city
FROM routes r
JOIN routes rr
ON r.arrival_city = rr.departure_city
AND rr.arrival_city = r.departure_city
AND r.arrival_city > rr.arrival_city
JOIN aircrafts a ON r.aircraft_code = a.aircraft_code
WHERE a.model = 'Boeing 777-300'
ORDER BY 1;Результаты запроса:
departure_city | arrival_city
----------------+--------------
Екатеринбург | Москва
Москва | Новосибирск
Москва | Пермь
Москва | Сочи
(4 rows)
Для ответа на вопрос, сколько рейсов выполняется из Москвы в Санкт-Петербург, можно написать совсем простой запрос:
SELECT count(*)
FROM routes
WHERE departure_city = 'Москва'
AND arrival_city = 'Санкт-Петербург'А с помощью какого запроса можно получить результат в таком виде?
departure_city | arrival_city | count
----------------+-----------------+-------
Москва | Санкт-Петербург | 12Решение:
SELECT departure_city, arrival_city, count(*)
FROM routes
WHERE departure_city = 'Москва'
AND arrival_city = 'Санкт-Петербург'
GROUP BY departure_city, arrival_city;Результаты запроса:
departure_city | arrival_city | count
----------------+-----------------+-------
Москва | Санкт-Петербург | 12
(1 rows)
Ответить на вопрос о том, каковы максимальные и минимальные цены билетов на все направления, может такой запрос:
SELECT
f.departure_city,
f.arrival_city,
max(tf.amount),
min(tf.amount)
FROM flights_v f
JOIN ticket_flights tf
ON f.flight_id = tf.flight_id
GROUP BY 1, 2
ORDER BY 1, 2;А как выявить те направления, на которые не было продано ни одного билета?
Решение:
SELECT
f.departure_city,
f.arrival_city,
max(tf.amount),
min(tf.amount)
FROM flights_v f
LEFT JOIN ticket_flights tf
ON f.flight_id = tf.flight_id
GROUP BY 1, 2
ORDER BY 1, 2;Результаты запроса:
departure_city | arrival_city | max | min
--------------------------+--------------------------+-----------+----------
Абакан | Архангельск | |
Абакан | Грозный | |
Абакан | Кызыл | |
Абакан | Москва | 101000.00 | 33700.00
Абакан | Новосибирск | 5800.00 | 5800.00
Абакан | Томск | 4900.00 | 4900.00
Анадырь | Москва | 185300.00 | 61800.00
Анадырь | Хабаровск | 92200.00 | 30700.00
Анапа | Белгород | 18900.00 | 6300.00
Анапа | Москва | 36600.00 | 12200.00
В разделе 6.4 мы использовали рекурсивный алгоритм в общем табличном выражении. Изучите этот пример, чтобы лучше понять работу рекурсивного алгоритма:
WITH RECURSIVE ranges (min_sum, max_sum)
AS (
VALUES (0, 100000),
(100000, 200000),
(200000, 300000)
UNION ALL
SELECT min_sum + 100000, max_sum + 100000
FROM ranges
WHERE max_sum < (
SELECT max( total_amount )
FROM bookings
)
)
SELECT * FROM ranges;Модифицируйте запрос, добавив в него столбец level (можно назвать его и iteration). Этот столбец должен содержать номер текущей итерации, поэтому нужно увеличивать его значение на единицу на каждом шаге. Не забудьте задать начальное значение для добавленного столбца в предложении VALUES.
Решение:
WITH RECURSIVE ranges (min_sum, max_sum, level)
AS (
VALUES (0, 100000, 1),
(100000, 200000, 1),
(200000, 300000, 1)
UNION ALL
SELECT min_sum + 100000, max_sum + 100000, level + 1
FROM ranges
WHERE max_sum < (
SELECT max( total_amount )
FROM bookings
)
)
SELECT * FROM ranges;Результат выполнения запроса:
min_sum | max_sum | iter
---------+---------+------
0 | 100000 | 1
100000 | 200000 | 1
200000 | 300000 | 1
100000 | 200000 | 2
200000 | 300000 | 2
300000 | 400000 | 2
200000 | 300000 | 3
300000 | 400000 | 3
400000 | 500000 | 3
300000 | 400000 | 4
400000 | 500000 | 4
500000 | 600000 | 4
400000 | 500000 | 5
500000 | 600000 | 5
600000 | 700000 | 5
Для завершения экспериментов замените UNION ALL на UNION и выполните запрос. Сравните этот результат с предыдущим, когда мы использовали UNION ALL.
WITH RECURSIVE ranges (min_sum, max_sum)
AS (
VALUES (0, 100000),
(100000, 200000),
(200000, 300000)
UNION
SELECT min_sum + 100000, max_sum + 100000
FROM ranges
WHERE max_sum < (
SELECT max( total_amount )
FROM bookings
)
)
SELECT * FROM ranges;Результат выполнения запроса:
min_sum | max_sum
---------+---------
0 | 100000
100000 | 200000
200000 | 300000
300000 | 400000
400000 | 500000
500000 | 600000
600000 | 700000
700000 | 800000
800000 | 900000
900000 | 1000000
1000000 | 1100000
1100000 | 1200000
1200000 | 1300000
(13 rows)
В тексте главы был приведен запрос, выводящий список городов, в которые нет рейсов из Москвы.
SELECT DISTINCT a.city
FROM airports a
WHERE NOT EXISTS (
SELECT * FROM routes r
WHERE r.departure_city = 'Москва'
AND r.arrival_city = a.city
)
AND a.city <> 'Москва'
ORDER BY city;Можно предложить другой вариант, в котором используется одна из операций над множествами строк: объединение, пересечение или разность. Вместо знака «?» поставьте в приведенном ниже запросе нужное ключевое слово — UNION, INTERSECT или EXCEPT — и обоснуйте ваше решение.
SELECT city
FROM airports
WHERE city <> 'Москва'
?
SELECT arrival_city
FROM routes
WHERE departure_city = 'Москва'
ORDER BY city;Решение:
Так как нам требуется исключить города, в которых нет рейсов из Москвы, то мы воспользуемся оператором EXCEPT:
(SELECT city
FROM airports
WHERE city <> 'Москва')
EXCEPT
(SELECT arrival_city
FROM routes
WHERE departure_city = 'Москва')
ORDER BY city;Оператор UNION объединил бы в одну таблицу результат выборки всех городов за исключением Москвы и всех городов, в которые прилетают рейсы из Москвы.
Оператор INTERSECT выдал бы список тех городов, в которые летают рейсы из Москвы.
В тексте главы мы рассматривали такой запрос: получить перечень аэропортов в тех городах, в которых больше одного аэропорта.
SELECT
aa.city,
aa.airport_code,
aa.airport_name
FROM (
SELECT
city,
count(*)
FROM airports
GROUP BY city
HAVING count(*) > 1
) AS a
JOIN airports AS aa
ON a.city = aa.city
ORDER BY aa.city, aa.airport_name;Как вы думаете, обязательно ли наличие функции count в подзапросе в предложении SELECT или можно написать просто SELECT city FROM airports?
Решение:
Нет, не обязательно, так как достаточно использовать функцию count() вместе с оператором HAVING. Результаты будут идентичны. Проверим это:
SELECT
aa.city,
aa.airport_code,
aa.airport_name
FROM (
SELECT city
FROM bookings.airports
GROUP BY city
HAVING count(*) > 1
) AS a
JOIN bookings.airports AS aa
ON a.city = aa.city
ORDER BY aa.city, aa.airport_name;Результаты запроса:
city | airport_code | airport_name
--------------+--------------+---------------------
Москва | VKO | Внуково
Москва | DME | Домодедово
Москва | SVO | Шереметьево
Ульяновск | ULV | Баратаевка
Ульяновск | ULV | Ульяновск-Восточный
Результат совпадает с результатом, получаемым первоначальным запросом.
Предположим, что департамент развития нашей авиакомпании задался вопросом: каким будет общее число различных маршрутов, которые теоретически можно проложить между всеми городами? Если в каком-то городе имеется более одного аэропорта, то это учитывать не будем, т. е. маршрутом будем считать путь между городами, а не между аэропортами. Здесь мы используем соединение таблицы с самой собой на основе неравенства значений атрибутов.
SELECT count( * )
FROM ( SELECT DISTINCT city FROM airports ) AS a1
JOIN ( SELECT DISTINCT city FROM airports ) AS a2
ON a1.city <> a2.city;Перепишите этот запрос с общим табличным выражением.
Решение:
WITH city_from AS (
SELECT DISTINCT city
FROM airports
)
SELECT count(*)
FROM city_from AS a1
JOIN city_from AS a2
ON a1.city <> a2.city;🔽 Развернуть 🔽
Глава 7 (упражнения 1, 2, 4)
Добавьте в определение таблицы aircrafts_log значение по умолчанию current_timestamp и соответствующим образом измените команды INSERT, приведенные в тексте главы.
-- DROP TABLE aircrafts_log;
-- TRUNCATE TABLE aircrafts_tmp;
CREATE TEMP TABLE aircrafts_log AS
SELECT * FROM aircrafts WITH NO DATA;
ALTER TABLE aircrafts_log
ADD COLUMN log_timestamp TIMESTAMP DEFAULT (current_timestamp);Изменим запрос на добавление новых строк в таблицу логов:
WITH add_row AS(
INSERT INTO aircrafts_tmp
SELECT * FROM aircrafts
RETURNING *
)
INSERT INTO aircrafts_log
SELECT
add_row.aircraft_code,
add_row.model,
add_row.range
FROM add_row;
SELECT * FROM aircrafts_logРезультат выполнения запроса:
---------------+---------------------+-------+----------------------------
773 | Boeing 777-300 | 11100 | 2022-11-06 23:09:54.005097
763 | Boeing 767-300 | 7900 | 2022-11-06 23:09:54.005097
320 | Airbus A320-200 | 5700 | 2022-11-06 23:09:54.005097
321 | Airbus A321-200 | 5600 | 2022-11-06 23:09:54.005097
319 | Airbus A319-100 | 6700 | 2022-11-06 23:09:54.005097
733 | Boeing 737-300 | 4200 | 2022-11-06 23:09:54.005097
CN1 | Cessna 208 Caravan | 1200 | 2022-11-06 23:09:54.005097
CR2 | Bombardier CRJ-200 | 2700 | 2022-11-06 23:09:54.005097
SU9 | Sukhoi SuperJet-100 | 6000 | 2022-11-06 23:09:54.005097
(9 rows)
В предложении RETURNING можно указывать не только символ «∗», означающий выбор всех столбцов таблицы, но и более сложные выражения, сформированные на основе этих столбцов. В тексте главы мы копировали содержимое таблицы «Самолеты» в таблицу aircrafts_tmp, используя в предложении RETURNING именно «∗». Однако возможен и другой вариант запроса:
WITH add_row AS
( INSERT INTO aircrafts_tmp
SELECT * FROM aircrafts
RETURNING aircraft_code, model, range,
current_timestamp, 'INSERT'
)
INSERT INTO aircrafts_log
SELECT ? FROM add_row;Что нужно написать в этом запросе вместо вопросительного знака?
Решение:
Можно прописать *, чтобы добавить все содержимое таблицы add_row.
WITH add_row AS(
INSERT INTO aircrafts_tmp
SELECT *
FROM aircrafts
RETURNING
aircraft_code, model,
range, current_timestamp,
'INSERT'
)
INSERT INTO aircrafts_log
SELECT *
FROM add_row;В тексте главы в предложениях ON CONFLICT команды INSERT мы использовали только выражения, состоящие из имени одного столбца. Однако в таблице «Места» (seats) первичный ключ является составным и включает два столбца. Напишите команду INSERT для вставки новой строки в эту таблицу и предусмотрите возможный конфликт добавляемой строки со строкой, уже имеющейся в таблице. Сделайте два варианта предложения ON CONFLICT: первый — с использованием перечисления имен столбцов для проверки наличия дублирования, второй — с использованием предложения ON CONSTRAINT. Для того чтобы не изменить содержимое таблицы «Места», создайте ее копию и выполняйте все эти эксперименты с таблицей-копией.
Решение: Для выполнения задания сделаем еще одну такую же таблицу
CREATE TEMP TABLE seats_tmp AS
SELECT * FROM SEATS;
ALTER TABLE seats_tmp
ADD PRIMARY KEY (aircraft_code, seat_no);Выведем информацию о полученной таблице:
demo=# \d seats_tmp
Таблица "pg_temp_3.seats_tmp"
Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию
-----------------+-----------------------+--------------------+-------------------+
aircraft_code | character(3) | | not null |
seat_no | character varying(4) | | not null |
fare_conditions | character varying(10) | | |
Индексы:
"seats_tmp_pkey" PRIMARY KEY, btree (aircraft_code, seat_no)
Реализуем добавление новых строк с конфликтом. Первый вариант - ничего не делать при попытке добавить строки с существующими первичными ключами.
INSERT INTO seats_tmp
SELECT aircraft_code,
seat_no,
fare_conditions
FROM seats
ON CONFLICT
DO NOTHING;Второй вариант - обновить значения строк.
INSERT INTO seats_tmp
VALUES (319, '2C', 'Business')
ON CONFLICT
ON CONSTRAINT seats_tmp_pkey
DO UPDATE SET
aircraft_code = excluded.aircraft_code,
seat_no = excluded.seat_no
RETURNING *;
SELECT *
FROM seats_tmp
WHERE aircraft_code = '319'
AND seat_no = '2C';Результат выполнения запроса:
aircraft_code | seat_no | fare_conditions
---------------+---------+-----------------
319 | 2A | Business
(1 rows)
🔽 Развернуть 🔽
Глава 8 (упражнения 1, 3)
Предположим, что для какой-то таблицы создан уникальный индекс по двум столбцам: column1 и column2. В таблице есть строка, у которой значение атрибута column1 равно ABC, а значение атрибута column2 - NULL. Мы решили добавить в таблицу еще одну строку с такими же значениями ключевых атрибутов, т.е. column1 - ABC, а column2 - NULL.
Как вы думаете, будет ли операция вставки новой строки успешной или завершится с ошибкой? Объясните ваше решение.
Решение:
Описанная в задании операция завершится ошибкой, по причине того, что нулевые значения (NULL) не будут распознаны как соответствующие каким либо существующим значениям.
Обратимся к таблице «Перелеты» (ticket_flights). В ней имеется столбец «Класс обслуживания» (fare_conditions), который отличается от остальных тем, что в нем могут присутствовать лишь три различных значения: Comfort, Business и Economy.
Выполните запросы, подсчитывающие количество строк, в которых атрибут fare_conditions принимает одно из трех возможных значений. Каждый из запросов выполните три-четыре раза, поскольку время может немного изменяться, и подсчитайте среднее время.
SELECT count( * )
FROM ticket_flights
WHERE fare_conditions = 'Comfort';В результате выполнения запроса получаем 17291. Произведем несколько запусков запроса:
- 76 мс
- 63 мс
- 57 мс
- 56 мс
- 57 мс
Среднее время по 5 измерениям: 61.8 мс
SELECT count( * )
FROM ticket_flights
WHERE fare_conditions = 'Business';В результате выполнения запроса получаем 107642, время выполнения:
- 56 мс
- 53 мс
- 58 мс
- 54 мс
- 55 мс
Среднее время по 5 измерениям: 55.2 мс
SELECT count( * )
FROM ticket_flights
WHERE fare_conditions = 'Economy';В результате выполнения запроса получает 920793, время выполнения:
- 63 мс
- 59 мс
- 63 мс
- 67 мс
- 59 мс
Среднее время по 5 измерениям: 62.2 мс
Создадим индекс к полю fare_conditions.
CREATE INDEX
ON ticket_flights ( fare_conditions );Проделаем те же эксперименты с таблицей ticket_flights. Будет ли различаться среднее время выполнения запросов для различных значений атрибута fare_conditions? Почему это имеет место?
SELECT count( * )
FROM ticket_flights
WHERE fare_conditions = 'Comfort';В результате выполнения запроса получаем 17291. Произведем несколько запусков запроса:
- 3 мс
- 3 мс
- 2 мс
- 2 мс
- 3 мс
Среднее время по 5 измерениям: 2.6 мс
SELECT count( * )
FROM ticket_flights
WHERE fare_conditions = 'Business';
В результате выполнения запроса получаем 107642, время выполнения:
- 9 мс
- 8 мс
- 8 мс
- 6 мс
- 7 мс Среднее время по 5 измерениям: 7.6 мс
SELECT count( * )
FROM ticket_flights
WHERE fare_conditions = 'Economy';В результате выполнения запроса получаем 920793, время выполнения:
- 40 мс
- 68 мс
- 43 мс
- 40 мс
- 43 мс
Среднее время выполнения по 5 наблюдениям: 46.8 мс
Вывод:
Время для выборки по условиям fare_conditions = Comfort и fare_conditions = Business при добавлении индекса сократилось более чем в 10 раз.
Однако но время для выборки по условию fare_conditions = Economy сократилось лишь в 1.3 раза.
Это может быть связано с тем, что записей, соответствующих Economy (полетов эконом-класса) значительно больше, чем строк, соответствующих другим категориям. Если быть точнее, то более 88% записей в таблице ticket_flights соответствуют эконом-классу.
Таким образом, индекс имеет преимущество только при выборе небольшой части от общего числа строк в таблице.
🔽 Развернуть 🔽
Глава 9 (упражнения 2, 3)
Модифицируйте сценарий выполнения транзакций: в первой транзакции вместо фиксации изменений выполните их отмену с помощью команды ROLLBACK и посмотрите, будет ли удалена строка и какая конкретно.
DELETE
FROM aircrafts_tmp
WHERE range < 2000;
SELECT *
FROM aircrafts_tmp;Решение:
DELETE
FROM aircrafts_tmp
WHERE range < 2000;
-- DELETE 1
SELECT *
FROM aircrafts_tmp;Результат выполнения запроса:
aircraft_code | model | range
---------------+---------------------+-------
773 | Boeing 777-300 | 11100
763 | Boeing 767-300 | 7900
SU9 | Sukhoi SuperJet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
733 | Boeing 737-300 | 4200
CR2 | Bombardier CRJ-200 | 2700
773 | Boeing 777-300 | 11100
763 | Boeing 767-300 | 7900
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
733 | Boeing 737-300 | 4200
CR2 | Bombardier CRJ-200 | 2700
SU9 | Sukhoi SuperJet-100 | 6000
(16 rows)
Вывод:
Изменения, проведенные в первой транзакции не зафиксировались, и вторая транзакция произошла независимо от первой. Поэтому была удалена строка, подходящая условию
range < 2000, которая была в изначальном состоянии таблицы.
Когда говорят о таком феномене, как потерянное обновление, то зачастую в качестве примера приводится операция UPDATE, в которой значение какого-то атрибута изменяется с применением одного из действий арифметики. Например:
UPDATE aircrafts_tmp
SET range = range + 200
WHERE aircraft_code = 'CR2';При выполнении двух и более подобных обновлений в рамках параллельных транзакций, использующих, например, уровень изоляции Read Committed, будут учтены все такие изменения (что и было показано в тексте главы). Очевидно, что потерянного обновления не происходит. Предположим, что в одной транзакции будет просто присваиваться новое значение.
Очевидно, что сохранится только одно из значений атрибута range. Можно ли говорить, что в такой ситуации имеет место потерянное обновление? Если оно имеет место, то что можно предпринять для его недопущения? Обоснуйте ваш ответ.
Решение:
С точки зрения пользовательской стороны приложения, потерянные обновления происходят. Это эквивалентно последовательным транзакциям.
Для избежания потерь следует использовать более высокий уровень изоляции.
🔽 Развернуть 🔽
Глава 10 (упражнения 3, 6, 8)
Самостоятельно выполните команду EXPLAIN для запроса, содержащего общее табличное выражение (CTE). Посмотрите, на каком уровне находится узел плана, отвечающий за это выражение, как он оформляется. Учтите, что общие табличные выражения всегда материализуются, т. е. вычисляются однократно и результат их вычисления сохраняется в памяти, а затем все последующие обращения в рамках запроса направляются уже к этому материализованному результату.
EXPLAIN
WITH city_from AS (
SELECT DISTINCT city
FROM airports
)
SELECT count(*)
FROM city_from AS a1
JOIN city_from AS a2
ON a1.city <> a2.city;Результат выполнения запроса:
QUERY PLAN
-------------------------------------------------------------------------
Aggregate (cost=262.11..262.12 rows=1 width=8)
CTE a
-> HashAggregate (cost=3.30..4.31 rows=101 width=17)
Group Key: airports.city
-> Seq Scan on airports (cost=0.00..3.04 rows=104 width=17)
-> Nested Loop (cost=0.00..232.55 rows=10100 width=0)
Join Filter: (a1.city <> a2.city)
-> CTE Scan on a a1 (cost=0.00..2.02 rows=101 width=32)
-> CTE Scan on a a2 (cost=0.00..2.02 rows=101 width=32)
Выполните команду EXPLAIN для запроса, в котором использована какая-нибудь из оконных функций. Найдите в плане выполнения запроса узел с именем WindowAgg. Попробуйте объяснить, почему он занимает именно этот уровень в плане.
Решение:
EXPLAIN
SELECT airport_name,
city,
round( latitude::numeric, 2 ) AS ltd,
timezone,
rank() OVER (
PARTITION BY timezone
ORDER BY latitude DESC
)
FROM airports
WHERE timezone IN ( 'Asia/Irkutsk', 'Asia/Krasnoyarsk' )
ORDER BY timezone, rank;Результат выполнения запроса:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Sort (cost=4.11..4.14 rows=13 width=97)
Sort Key: timezone, (rank() OVER (?))
-> WindowAgg (cost=3.54..3.87 rows=13 width=97)
-> Sort (cost=3.54..3.57 rows=13 width=57)
Sort Key: timezone, latitude DESC
-> Seq Scan on airports (cost=0.00..3.30 rows=13 width=57)
Filter: (timezone = ANY ('{Asia/Irkutsk,Asia/Krasnoyarsk}'::text[]))
Замена коррелированного подзапроса соединением таблиц является одним из способов повышения производительности. Предположим, что мы задались вопросом: сколько маршрутов обслуживают самолеты каждого типа? При этом нужно учитывать, что может иметь место такая ситуация, когда самолеты какого-либо типа не обслуживают ни одного маршрута. Поэтому необходимо использовать не только представление «Маршруты» (routes), но и таблицу «Самолеты» (aircrafts). Это первый вариант запроса, в нем используется коррелированный подзапрос.
EXPLAIN ANALYZE
SELECT f.flight_id, avg(tf.amount)
FROM flights_v f
LEFT OUTER JOIN ticket_flights tf
ON f.flight_id = tf.flight_id
WHERE f.flight_id<100
GROUP BY 1
ORDER BY 1;Результат выполнения запроса:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=22308.36..22333.04 rows=99 width=36) (actual time=105.340..105.861 rows=99 loops=1)
Group Key: f.flight_id
-> Sort (cost=22308.36..22316.18 rows=3126 width=10) (actual time=105.324..105.456 rows=3988 loops=1)
Sort Key: f.flight_id
Sort Method: quicksort Memory: 283kB
-> Nested Loop (cost=12.56..22126.89 rows=3126 width=10) (actual time=0.743..104.828 rows=3988 loops=1)
-> Nested Loop (cost=12.41..22028.40 rows=3126 width=14) (actual time=0.738..103.839 rows=3988 loops=1)
-> Hash Right Join (cost=12.26..21929.91 rows=3126 width=18) (actual time=0.728..102.815 rows=3988 loops=1)
Hash Cond: (tf.flight_id = f.flight_id) Hash Cond: (tf.flight_id = f.flight_id)
-> Seq Scan on ticket_flights tf (cost=0.00..19172.26 rows=1045726 width=10) (actual time=0.007..43.244 rows=1045726 loops=1)
-> Hash (cost=11.02..11.02 rows=99 width=12) (actual time=0.032..0.032 rows=99 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Index Scan using flights_pkey on flights f (cost=0.29..11.02 rows=99 width=12) (actual time=0.007..0.020 rows=99 loops=1)
Index Cond: (flight_id < 100) -> Memoize (cost=0.15..0.33 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=3988)
Cache Key: f.departure_airport
Hits: 3987 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using airports_pkey on airports dep (cost=0.14..0.32 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: (airport_code = f.departure_airport)
Heap Fetches: 1Memoize (cost=0.15..0.33 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=3988)
Cache Key: f.arrival_airport
Hits: 3987 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using airports_pkey on airports arr (cost=0.14..0.32 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (airport_code = f.arrival_airport)
Heap Fetches: 1
Planning Time: 0.683 ms
Execution Time: 105.936 ms
А в этом варианте коррелированный подзапрос раскрыт и заменен внешним соединением:
EXPLAIN ANALYZE
SELECT f.flight_id,
(
SELECT avg(tf.amount)
FROM ticket_flights tf
WHERE f.flight_id = tf.flight_id
)
FROM flights_v f
WHERE f.flight_id<100
GROUP BY 1
ORDER BY 1;Результат выполнения запроса:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Group (cost=0.59..2156946.84 rows=99 width=36) (actual time=61.092..5852.073 rows=99 loops=1)
Group Key: f.flight_id
-> Nested Loop (cost=0.59..57.10 rows=99 width=4) (actual time=0.024..1.009 rows=99 loops=1)
-> Nested Loop (cost=0.44..34.06 rows=99 width=8) (actual time=0.018..0.779 rows=99 loops=1)
-> Index Scan using flights_pkey on flights f (cost=0.29..11.02 rows=99 width=12) (actual time=0.008..0.300 rows=99 loops=1)
Index Cond: (flight_id < 100)
-> Memoize (cost=0.15..0.33 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=99)
Cache Key: f.departure_airport
Hits: 98 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using airports_pkey on airports dep (cost=0.14..0.32 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: (airport_code = f.departure_airport)
Heap Fetches: 1
-> Memoize (cost=0.15..0.33 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=99)
Cache Key: f.arrival_airport
Hits: 98 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using airports_pkey on airports arr (cost=0.14..0.32 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)
Index Cond: (airport_code = f.arrival_airport)
Heap Fetches: 1
SubPlan 1
-> Aggregate (cost=21786.75..21786.76 rows=1 width=32) (actual time=59.097..59.097 rows=1 loops=99)
-> Seq Scan on ticket_flights tf (cost=0.00..21786.58 rows=70 width=6) (actual time=44.712..59.075 rows=40 loops=99)
Filter: (f.flight_id = flight_id)
Rows Removed by Filter: 1045686
Planning Time: 0.390 ms
25
Execution Time: 5852.158 ms
Исследуйте планы выполнения обоих запросов. Попытайтесь найти объяснение различиям в эффективности их выполнения. Чтобы получить усредненную картину, выполните каждый запрос несколько раз.
Из планов выполнения запросов видно, что операция JOIN выполняется значительно быстрее, чем подзапрос.
🔽 Развернуть 🔽
Программирование на стороне сервера.
Выполняется на основе презентации и главы 4 учебного пособия "Администрирование информационных систем". Учебную базу данных можно наполнить информацией, функциями и триггерами с помощью команд, выполняемых в командной строке Debian:
createdb ais -U postgres
psql -d ais -f adj_list.sql -U postgresФайл adj_list.sql находится в составе архива исходных текстов, прилагаемых к учебному пособию.
Они находятся здесь
Нужно проделать упражнения 12-18 на стр. 86-88 учебного пособия "Администрирование информационных систем". Почти все эти упражнения являются простыми, ознакомительными, не требующими программирования.
SELECT * FROM personnel;
emp_nbr | emp_name | address | birth_date
---------+----------+-------------------------+------------
0 | вакансия | | 2014-05-19
1 | Иван | ул. Любителей языка C | 1962-12-01
2 | Петр | ул. UNIX гуру | 1965-10-21
3 | Антон | ул. Ассемблерная | 1964-04-17
4 | Захар | ул. им. СУБД PostgreSQL | 1963-09-27
5 | Ирина | просп. Программистов | 1968-05-12
6 | Анна | пер. Перловый | 1969-03-20
7 | Андрей | пл. Баз данных | 1945-11-07
8 | Николай | наб. ОС Linux | 1944-12-01
(9 rows)
SELECT * FROM personnel_org_chart;
emp_nbr | emp | boss_emp_nbr | boss
---------+---------+--------------+-------
1 | Иван | ø | ø
2 | Петр | 1 | Иван
3 | Антон | 1 | Иван
4 | Захар | 3 | Антон
5 | Ирина | 3 | Антон
6 | Анна | 3 | Антон
7 | Андрей | 5 | Ирина
8 | Николай | 5 | Ирина
(8 rows)
SELECT * FROM create_paths;
level1 | level2 | level3 | level4
--------+--------+--------+---------
Иван | Антон | Ирина | Андрей
Иван | Антон | Ирина | Николай
Иван | Петр | ø | ø
Иван | Антон | Захар | ø
Иван | Антон | Анна | ø
(5 rows)
SELECT * FROM org_chart;
job_title | emp_nbr | boss_emp_nbr | salary
---------------------+---------+--------------+-----------
Президент | 1 | ø | 1000.0000
Вице-президент 1 | 2 | 1 | 900.0000
Вице-президент 2 | 3 | 1 | 800.0000
Архитектор | 4 | 3 | 700.0000
Ведущий программист | 5 | 3 | 600.0000
Программист C | 6 | 3 | 500.0000
Программист Perl | 7 | 5 | 450.0000
Оператор | 8 | 5 | 400.0000
(8 rows)Выполните проверку структуры дерева на предмет отсутствия циклов с помощью функции tree_test().
UPDATE org_chart
SET boss_emp_nbr = 4 WHERE emp_nbr = 3;
SELECT * FROM tree_test();
tree_test
-----------
Cycles
(1 row)
UPDATE org_chart
SET boss_emp_nbr = 8 WHERE emp_nbr = 3;
SELECT * FROM tree_test();
tree_test
-----------
Cycles
(1 row)Выполните обход дерева организационной структуры снизу вверх, начиная с конкретного узла, можно с помощью функции up_tree_traversal() либо функции up_tree_traversal2().
Решение:
SELECT * FROM up_tree_traversal(6);
emp_nbr | boss_emp_nbr
---------+--------------
6 | 3
3 | 1
1 | ø
(3 rows)
SELECT * FROM up_tree_traversal2(6) as (emp int, boss int);
emp | boss
-----+------
6 | 3
3 | 1
1 | ø
(3 rows)
SELECT * FROM up_tree_traversal ((
SELECT emp_nbr FROM personnel WHERE emp_name = 'Захар'
)
);
emp_nbr | boss_emp_nbr
---------+--------------
4 | 3
3 | 1
1 | ø
(3 rows)Выполните операцию удаления поддерева с помощью функции delete_subtree(). Параметром функции является код работника Аналогично работе с функцией up_tree_traversal() используйте подзапрос для получения кода работника по его имени. После удаления поддерева посмотрите, что стало с организационной структурой,с помощью двух представлений Personnel_org_chart и Create_paths.
SELECT * FROM create_paths;
level1 | level2 | level3 | level4
--------+--------+--------+---------
Иван | Антон | Ирина | Андрей
Иван | Антон | Ирина | Николай
Иван | Петр | ø | ø
Иван | Антон | Захар | ø
Иван | Антон | Анна | ø
(5 rows)
SELECT * FROM personnel_org_chart;
emp_nbr | emp | boss_emp_nbr | boss
---------+---------+--------------+-------
1 | Иван | ø | ø
2 | Петр | 1 | Иван
3 | Антон | 1 | Иван
4 | Захар | 3 | Антон
5 | Ирина | 3 | Антон
6 | Анна | 3 | Антон
7 | Андрей | 5 | Ирина
8 | Николай | 5 | Ирина
(8 rows)
SELECT * FROM delete_subtree(4);
delete_subtree
----------------
(1 row)
SELECT * FROM create_paths;
level1 | level2 | level3 | level4
--------+--------+--------+---------
Иван | Антон | Ирина | Андрей
Иван | Антон | Ирина | Николай
Иван | Петр | ø | ø
Иван | Антон | Анна | ø
(4 rows)
SELECT * FROM personnel_org_chart;
emp_nbr | emp | boss_emp_nbr | boss
---------+---------+--------------+-------
1 | Иван | ø | ø
2 | Петр | 1 | Иван
3 | Антон | 1 | Иван
5 | Ирина | 3 | Антон
6 | Анна | 3 | Антон
7 | Андрей | 5 | Ирина
8 | Николай | 5 | Ирина
(7 rows)
SELECT * FROM delete_subtree((
SELECT emp_nbr
FROM personnel
WHERE emp_name = 'Анна'
)
);
delete_subtree
----------------
(1 row)
SELECT * FROM create_paths;
level1 | level2 | level3 | level4
--------+--------+--------+---------
Иван | Антон | Ирина | Андрей
Иван | Антон | Ирина | Николай
Иван | Петр | ø | ø
(3 rows)
SELECT * FROM personnel_org_chart;
emp_nbr | emp | boss_emp_nbr | boss
---------+---------+--------------+-------
1 | Иван | ø | ø
2 | Петр | 1 | Иван
3 | Антон | 1 | Иван
5 | Ирина | 3 | Антон
7 | Андрей | 5 | Ирина
8 | Николай | 5 | Ирина
(6 rows)Если в таблице «Организационная структура» осталось мало данных, то дополните ее данными и выполните удаление элемента иерархии и продвижение дочерних элементов на один уровень вверх (т. е. к «бабушке»).
Аналогично работе с функцией up_tree_traversal() используйте подзапрос для получения кода работника по его имени. После удаления элемента иерархии посмотрите, что стало с организационной структурой, с помощью двух представлений Personnel_org_chart и Create_paths.
SELECT * FROM delete_and_promote_subtree((
SELECT emp_nbr
FROM personnel
WHERE emp_name='Антон'
)
);
delete_and_promote_subtree
----------------------------
(1 row)
SELECT * FROM create_paths;
level1 | level2 | level3 | level4
--------+--------+---------+--------
Иван | Петр | ø | ø
Иван | Ирина | Николай | ø
Иван | Ирина | Андрей | ø
(3 rows)
SELECT * FROM personnel_org_chart;
emp_nbr | emp | boss_emp_nbr | boss
---------+---------+--------------+-------
1 | Иван | ø | ø
2 | Петр | 1 | Иван
7 | Андрей | 5 | Ирина
8 | Николай | 5 | Ирина
5 | Ирина | 1 | Иван
(5 rows)Представление Create_paths позволяет отобразить только четыре уровня иерархии. Модифицируйте его так, чтобы оно могло работать с пятью уровнями иерархии.
CREATE VIEW create_paths_5 (level1, level2, level3, level4, level5) AS
SELECT a1.emp AS e1, a2.emp AS e2, a3.emp AS e3, a4.emp AS e4, a5.emp AS e5
FROM personnel_org_chart as a1
LEFT OUTER JOIN personnel_org_chart as a2
ON a1.emp = a2.boss
LEFT OUTER JOIN personnel_org_chart as a3
ON a2.emp = a3.boss
LEFT OUTER JOIN personnel_org_chart AS a4
ON a3.emp = a4.boss
LEFT OUTER JOIN personnel_org_chart as a5
ON a4.emp = a5.boss
WHERE a1.emp = 'Иван';
SELECT * FROM create_paths_5;
level1 | level2 | level3 | level4 | level5
--------+--------+---------+--------+--------
Иван | Ирина | Андрей | ø | ø
Иван | Ирина | Николай | ø | ø
Иван | Петр | ø | ø | ø
(3 rows)Самостоятельно ознакомьтесь с таким средством работы с таблицами базы данных, как курсоры (cursors). Воспользуйтесь технической документацией на PostgreSQL, глава «PL/pgSQL – SQL Procedural Language». Напишите небольшую функцию с применением курсора.
CREATE TEMP TABLE aircrafts_tmp AS
SELECT * FROM aircrafts WITH DATA;
ALTER TABLE aircrafts_tmp
ADD COLUMN bin_column text;
CREATE OR replace FUNCTION test_function()
RETURNS void AS $$
DECLARE curs CURSOR
FOR SELECT * FROM aircrafts_tmp
WHERE range > 5000;
BEGIN
OPEN curs;
MOVE curs;
WHILE FOUND LOOP
UPDATE aircrafts_tmp
SET bin_column='yes'
WHERE current of curs;
MOVE curs;
END loop;
CLOSE curs;
END
$$
LANGUAGE plpgsql;
SELECT * FROM test_function();
SELECT * FROM aircrafts_tmp;
aircraft_code | model | range | bin_column |
0 | SU9 | Sukhoi SuperJet-100 | 3000 | NULL
1 | 733 | Boeing 737-300 | 4200 | NULL
2 | CN1 | Cessna 208 Caravan | 1200 | NULL
3 | CR2 | Bombardier CRJ-200 | 2700 | NULL
4 | 773 | Boeing 777-300 | 11100 | yes
5 | 763 | Boeing 767-300 | 7900 | yes
6 | 320 | Airbus A320-200 | 5700 | yes
7 | 321 | Airbus A321-200 | 5600 | yes
8 | 319 | Airbus A319-100 | 6700 | yes
(9 rows)🔽 Развернуть 🔽
Задание выполняется на основе презентации 10 "Полнотекстовый поиск" и главы 12 документации на PostgreSQL
Придумать и реализовать пример использования полнотекстового поиска, аналогичный (можно более простой или более сложный) тому примеру с библиотечным каталогом, который был приведен в презентации. Можно использовать исходные тексты, приведенные в презентации.
Решение:
Создадим таблицу books в базе данных demo (схема public)
CREATE TABLE books
(book_id integer primary key, book_description text);Заполним таблицу из файла books3.txt
demo=# \copy books from 'texts/books3.txt' encoding 'UTF-8'
Выполним несколько запросов на полнотекстовый поиск.
ALTER TABLE books
ADD COLUMN ts_description tsvector;
UPDATE books
SET ts_description = to_tsvector('russian', book_description);Выведем идентификаторы и описания книг, в которых упоминается язык программирования Python:
SELECT book_id, book_description
FROM books
WHERE ts_description @@ to_tsquery('Python');Результаты запроса:
book_id | book_description
1 | book_id book_id book_id Любанович, Билл. Простой Python [Текст] : современный стиль программирования / Билл Любанович. - Санкт-Петербург ; Москва ; Екатеринбург : Питер, 2018. - 476 с.
2 | Силен, Дэви. Основы Data Science и Big Data. Python и наука о данных [Текст] / Дэви Силен, Арно Мейсман, Мохамед Али ; [пер. с англ. Е. Матвеева]. - Санкт-Петербург : Питер, 2017. - 334 с.
250 | Доусон, Майкл. Программируем на Python [Текст] / Майкл Доусон ; [пер. с англ.: В. Порицкий]. - Москва [и др.] : Питер, 2016. - 414 с.
767 | Лутц, Марк. Изучаем Python [Текст] / Марк Лутц ; [пер. с англ. А. Киселева]. - Санкт-Петербург ; Москва : Символ : Символ-Плюс, 2011. - 1272 с.
(rows 4)
Ищем авторов с именем Эдуард:
SELECT book_id, book_description
FROM books
WHERE ts_description @@ plainto_tsquery('Эдуард');Результаты выполнения запроса:
book_id | book_description
125 | Дадян, Эдуард Григорьевич. Основы языка программирования 1С 8.3 [Текст] : учебное пособие : [для студентов высших учебных заведений по всем направлениям и специальностям] / Э. Г. Дадян. - Москва : Вузовский учебник : ИНФРА-М, 2017. - 132, [1] с.
858 | Минько, Эдуард Викентьевич. Методы прогнозирования и исследования операций [Текст] : [учебное пособие для студентов высших учебных заведений, обучающихся по специальностям 080301 "Коммерция (торговое дело)" и 080111 "Маркетинг"] / Э. В. Минько, А. Э. Минько ; под ред. А. С. Будагова. - Москва : Финансы и статистика : ИНФРА-М, 2010. - 477, [1] с.
(rows 2)
Ищем количество книг по базам данных:
SELECT count(*)
FROM books
WHERE ts_description @@ plainto_tsquery('Базы данных');Результаты выполнения запроса:
count
-----------
16
(1 row)