Познавательное развитие "Вода вокруг нас"

Министерство цифрового развития, связи и массовых коммуникаций Российской Федерации Ордена Трудового Красного Знамени федеральное государственное бюджетное образовательное учреждение высшего образования «Московский технический университет связи и информатики» (МТУСИ) Кафедра «Системное программирование»
КОНТРОЛЬНАЯ РАБОТА ПО ДИСЦИПЛИНЕ «Математические основы баз данных»
	Выполнил: студент гр. БСТ2352 ___________________Васильева А.А. «12» января 2025 г. Проверил: __________________Полищук Ю.В. «12» января 2025 г.
Москва, 2025 г.

Содержание

Задание № 1 3

Задание № 2 9

Задание № 3 12

Задание №4 13

Задание № 5 15

Номер в списке группы №3, вариант 3
Вариант 3:
БД лингвистических школ. Школы размещаются на территории города в различных районах («центральный район», «южный» и т.д.). В школе преподают один или несколько иностранных языков («английский», «немецкий» и т.д.).

Задание № 1

Разработать скрипт создания и заполнения БД. Реализовать для БД 5-ть различных запросов. В 2-х и более запросах должны быть задействованы все таблицы БД. Продемонстрировать работу сформированных скриптов.

Ход выполнения

Для демонстрации работы скриптов использован on-line сервис PostgreSQL https://aiven.io/tools/pg-playground

Формируем скрипт создания и заполнения БД:

-- Cоздаем таблицу Районы (Districts)

CREATE TABLE districts (

id INTEGER NOT NULL PRIMARY KEY,

district_name VARCHAR(50) NOT NULL UNIQUE

);

-- Cоздаем таблицу Языки (Languages)

CREATE TABLE languages (

id INTEGER NOT NULL PRIMARY KEY,

language_name VARCHAR(50) NOT NULL UNIQUE

);

-- Cоздаем таблицу Школы (Schools)

CREATE TABLE schools (

id INTEGER NOT NULL PRIMARY KEY,

school_name VARCHAR(100) NOT NULL,

district_id INTEGER NOT NULL,

FOREIGN KEY (district_id) REFERENCES districts(id) -- Связь с таблицей Районы

);

-- Cоздаем таблицу Школа-Языки (School_Languages) для связи многие-ко-многим

CREATE TABLE school_languages (

school_id INTEGER NOT NULL,

language_id INTEGER NOT NULL,

PRIMARY KEY (school_id, language_id), -- Составной первичный ключ

FOREIGN KEY (school_id) REFERENCES schools(id), -- Связь с таблицей Школы

FOREIGN KEY (language_id) REFERENCES languages(id) -- Связь с таблицей Языки

);

Мы получили нужные таблицы в онлайн компиляторе:

Теперь добавим в созданные таблицы несколько записей для демонстрации.

-- Добавляем записи в таблицу Районы

INSERT INTO districts (id, district_name) VALUES (1, 'центральный');

INSERT INTO districts (id, district_name) VALUES (2, 'южный');

INSERT INTO districts (id, district_name) VALUES (3, 'северный');

INSERT INTO districts (id, district_name) VALUES (4, 'западный');

-- Добавляем записи в таблицу Языки

INSERT INTO languages (id, language_name) VALUES (101, 'английский');

INSERT INTO languages (id, language_name) VALUES (102, 'немецкий');

INSERT INTO languages (id, language_name) VALUES (103, 'французский');

INSERT INTO languages (id, language_name) VALUES (104, 'испанский');

INSERT INTO languages (id, language_name) VALUES (105, 'китайский');

-- Добавляем записи в таблицу Школы

INSERT INTO schools (id, school_name, district_id) VALUES (1, 'LinguaPlus', 1); -- центральный

INSERT INTO schools (id, school_name, district_id) VALUES (2, 'Polyglot Center', 2); -- южный

INSERT INTO schools (id, school_name, district_id) VALUES (3, 'Global Speak', 1); -- центральный

INSERT INTO schools (id, school_name, district_id) VALUES (4, 'Modern Lang', 3); -- северный

INSERT INTO schools (id, school_name, district_id) VALUES (5, 'WestEnd School', 4); -- западный

-- Добавляем записи в таблицу Школа-Языки

-- LinguaPlus (центральный)

INSERT INTO school_languages (school_id, language_id) VALUES (1, 101); -- английский

INSERT INTO school_languages (school_id, language_id) VALUES (1, 102); -- немецкий

INSERT INTO school_languages (school_id, language_id) VALUES (1, 103); -- французский

-- Polyglot Center (южный)

INSERT INTO school_languages (school_id, language_id) VALUES (2, 101); -- английский

INSERT INTO school_languages (school_id, language_id) VALUES (2, 104); -- испанский

INSERT INTO school_languages (school_id, language_id) VALUES (2, 105); -- китайский

-- Global Speak (центральный)

INSERT INTO school_languages (school_id, language_id) VALUES (3, 101); -- английский

INSERT INTO school_languages (school_id, language_id) VALUES (3, 105); -- китайский

-- Modern Lang (северный)

INSERT INTO school_languages (school_id, language_id) VALUES (4, 101); -- английский

INSERT INTO school_languages (school_id, language_id) VALUES (4, 102); -- немецкий

-- WestEnd School (западный)

INSERT INTO school_languages (school_id, language_id) VALUES (5, 102); -- немецкий

INSERT INTO school_languages (school_id, language_id) VALUES (5, 104); -- испанский

Запрос №1

Вывести все школы, расположенные в 'центральном' районе. Этот запрос использует таблицы schools и districts.

SELECT

s.school_name,

d.district_name

FROM schools s

JOIN districts d ON s.district_id = d.id

WHERE d.district_name = 'центральный';

Результат:

Запрос №2

Посчитать количество языков, преподаваемых в школе 'Polyglot Center'. Этот запрос использует таблицы schools и school_languages.

SELECT

s.school_name,

COUNT(sl.language_id) AS number_of_languages

FROM schools s

JOIN school_languages sl ON s.id = sl.school_id

WHERE s.school_name = 'Polyglot Center'

GROUP BY s.school_name;

Результат:

Запрос №3

Вывести все языки, которые преподают в 'южном' районе. Этот запрос задействует все 4 таблицы.

SELECT DISTINCT

l.language_name

FROM languages l

JOIN school_languages sl ON l.id = sl.language_id

JOIN schools s ON sl.school_id = s.id

JOIN districts d ON s.district_id = d.id

WHERE d.district_name = 'южный';

Результат:

Запрос №4

Найти все школы, где преподают 'английский' язык, и показать их район. Этот запрос также задействует все 4 таблицы.

SELECT

s.school_name,

d.district_name,

l.language_name

FROM schools s

JOIN districts d ON s.district_id = d.id

JOIN school_languages sl ON s.id = sl.school_id

JOIN languages l ON sl.language_id = l.id

WHERE l.language_name = 'английский';

Результат:

Запрос №5

Вывести количество школ в каждом районе. Этот запрос показывает, сколько школ зарегистрировано в каждом из районов.

SELECT

d.district_name,

COUNT(s.id) AS school_count

FROM districts d

LEFT JOIN schools s ON d.id = s.district_id

GROUP BY d.district_name

ORDER BY school_count DESC;

Результат:

Задание № 2

Разработать хранимую функцию и процедуру. Продемонстрировать их работу.

Ход выполнения

Хранимые функции идеально подходят для вычислений и возврата одного значения. Мы создадим функцию get_school_count_in_district, которая будет принимать название района и возвращать количество школ в этом районе.

CREATE FUNCTION get_school_count_in_district(district_name_param VARCHAR(50))

RETURNS INTEGER AS $$

DECLARE

school_count INTEGER;

BEGIN

SELECT COUNT(*)

INTO school_count

FROM schools s

JOIN districts d ON s.district_id = d.id

WHERE d.district_name = district_name_param;

RETURN school_count;

END;

$$ LANGUAGE plpgsql;

Демонстрация работы функции:

SELECT get_school_count_in_district('центральный');

Результат:

2. Хранимая процедура

Назначение: Хранимые процедуры используются для выполнения набора SQL-команд. Они могут изменять данные, выполнять сложные операции и возвращать наборы данных. Мы создадим процедуру find_schools_by_language, которая будет принимать название языка и возвращать список всех школ (и их районов), где этот язык преподается.

CREATE PROCEDURE find_schools_by_language(IN language_name_param VARCHAR(50))

LANGUAGE SQL

AS $$

SELECT

s.school_name,

d.district_name

FROM schools s

JOIN districts d ON s.district_id = d.id

JOIN school_languages sl ON s.id = sl.school_id

JOIN languages l ON sl.language_id = l.id

WHERE l.language_name = language_name_param;

$$;

Демонстрация работы процедуры:
Вызов процедуры обычно осуществляется с помощью команды CALL.
Найдем все школы, где преподают 'немецкий'.

Задание № 3

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

Ход выполнения

1. Создать триггер, который при удалении школы из таблицы schools автоматически удаляет все связанные с ней записи о языках из таблицы school_languages. 1. Смотрим текущие данные (для проверки) Сначала посмотрим, какие языки преподаются в каких школах, чтобы иметь точку отсчета.

SELECT

s.id AS school_id,

s.school_name,

l.language_name

FROM schools s

JOIN school_languages sl ON s.id = sl.school_id

JOIN languages l ON sl.language_id = l.id

ORDER BY s.id;

2. Создание триггерной функции Эта функция будет выполняться перед удалением строки в таблице schools и удалять связанные данные из school_languages.

CREATE OR REPLACE FUNCTION trg_delete_school_languages()

RETURNS TRIGGER

LANGUAGE plpgsql

AS

$$

BEGIN

-- Удаляем все записи о языках для удаляемой школы

-- OLD.id содержит id школы, которую мы удаляем

DELETE FROM school_languages WHERE school_id = OLD.id;

RETURN OLD;

END;

$$;

3. Создание триггера Теперь привязываем созданную функцию к событию BEFORE DELETE (перед удалением) для каждой строки в таблице schools.

CREATE TRIGGER trig_before_delete_school

BEFORE DELETE ON schools

FOR EACH ROW

EXECUTE PROCEDURE trg_delete_school_languages();

4. Демонстрация работы триггера Проверим, как триггер сработает.

• Удалим школу 'Modern Lang' (ее id = 4).
  Таблица school_languages до удаления

SELECT * FROM school_languages ORDER BY school_id, language_id;

(Результат покажет записи для всех школ, включая school_id = 4)

• Удаляем школу с id = 4

DELETE FROM schools WHERE id = 4; (Эта команда запускает триггер)

• Таблица school_languages после удаления

SELECT * FROM school_languages ORDER BY school_id, language_id;

(Теперь в результате отсутствуют строки, где school_id = 4)

• Таблица schools после удаления

SELECT * FROM schools ORDER BY id;

(Теперь в результате отсутствует школа с id = 4)

Результат:

После выполнения команды DELETE FROM schools WHERE id = 4; триггер trig_before_delete_school успешно сработал. Он автоматически удалил из таблицы school_languages все записи, связанные с удаленной школой (где school_id = 4), обеспечив тем самым целостность данных в базе. Проверка таблиц после операции подтверждает, что и сама школа, и связанные с ней данные были корректно удалены.

Задача 4 — Пример использования представления (View)

Цель:

Создать представление, которое показывает название школы, ее район и общее количество языков, преподаваемых в ней. Это позволит быстро оценивать "ассортимент" каждой школы.

Ход выполнения 1.

Создание представления Создадим представление v_school_summary, которое будет агрегировать данные.

CREATE OR REPLACE VIEW v_school_summary AS

SELECT

s.id AS school_id,

s.school_name,

d.district_name,

COUNT(sl.language_id) AS languages_count

FROM

schools s

JOIN

districts d ON s.district_id = d.id

JOIN

school_languages sl ON s.id = sl.school_id

GROUP BY

s.id, s.school_name, d.district_name;

2. Демонстрация работы представления Теперь для получения сводной информации достаточно выполнить простой запрос к нашему новому представлению.

SELECT * FROM v_school_summary ORDER BY school_name;

Результат:

Результат:

• JOIN связывает таблицы schools, districts и school_languages для сбора полной информации.

• COUNT(sl.language_id) вычисляет общее количество языков для каждой школы.

• GROUP BY s.id, s.school_name, d.district_name позволяет корректно посчитать количество языков для каждой уникальной школы.

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

Задание № 5

1. Цель и выбор поля для индексации

Цель: Ускорить поиск школ по их названию в таблице schools.

Проблема: Представьте, что в таблице schools не 5 записей, а 5 миллионов. Когда вы выполняете запрос SELECT * FROM schools WHERE school_name = 'LinguaPlus';, базе данных без индекса приходится делать полное сканирование таблицы (Full Table Scan / Sequential Scan). Это похоже на поиск одного слова в книге, читая ее от корки до корки. Это очень медленно.

Решение: Создать индекс на столбце school_name. Индекс — это специальная структура данных (похожая на алфавитный указатель в конце книги), которая позволяет базе данных мгновенно находить строки с нужным значением, не просматривая всю таблицу.

2. Ход выполнения

Шаг 1. Демонстрация медленного запроса (без индекса)

В PostgreSQL, MySQL и других СУБД есть команда EXPLAIN (или EXPLAIN ANALYZE), которая не выполняет запрос, а показывает план его выполнения. Давайте посмотрим, как база данных будет искать школу по названию без индекса.

-- Показываем план выполнения для поиска по названию

EXPLAIN ANALYZE

SELECT * FROM schools WHERE school_name = 'LinguaPlus';

Объяснение:

Ключевая фраза здесь — Seq Scan (Sequential Scan). Это и есть то самое "чтение от корки до корки". База данных честно просмотрела все строки, чтобы найти нужную. На наших 5 записях это происходит мгновенно, но на миллионах записей это заняло бы много времени.

Шаг 2. Создание индекса

Теперь создадим индекс на столбце school_name.

-- Создаем индекс

CREATE INDEX idx_schools_school_name ON schools(school_name);

Шаг 3. Демонстрация быстрого запроса (с индексом)

Выполним тот же самый запрос EXPLAIN ANALYZE еще раз. Теперь база данных "увидит" наш новый индекс и предпочтет использовать его.

-- Снова показываем план выполнения для того же запроса

EXPLAIN ANALYZE

SELECT * FROM schools WHERE school_name = 'LinguaPlus';

Объяснение:

План выполнения кардинально изменился! Теперь используется Index Scan.

Вместо полного сканирования таблицы (Seq Scan) база данных сначала посмотрела в наш компактный и отсортированный индекс idx_schools_school_name.

Она мгновенно нашла там 'LinguaPlus' и указатель на нужную строку в основной таблице.

Затем она обратилась напрямую к этой строке, проигнорировав все остальные.

Результат:

Мы создали индекс idx_schools_school_name на столбце school_name.

С помощью команды EXPLAIN ANALYZE мы продемонстрировали, что после создания индекса база данных меняет свою стратегию выполнения запроса с неэффективного полного сканирования (Seq Scan) на высокоэффективный поиск по индексу (Index Scan).

Хотя на нашем крошечном наборе данных разница во времени выполнения (Execution Time) незаметна, на большой базе данных разница в производительности была бы колоссальной — от нескольких секунд или даже минут до долей миллисекунды.