Overview Info21 v1.0

Анализ и статистика данных по «Школе 21». В данном проекте была создана база данных в Postgres со знаниями о «Школе 21», написаны процедуры и функции на plpgsql для получения различной информации, а также триггеры для её изменения.

Логический вид моели БД

1. Part 1. Создание базы данных
2. Part 2. Изменение данных
3. Part 3. Получение данных
4. Part 4. Метаданные

Part 1. Создание базы данных

Скрипт part1.sql служит для создания базы данных и всех таблиц, описанных выше.

Для удобства в скрипт внесены процедуры, позволяющие импортировать и экспортировать данные для каждой таблицы из файла/в файл с расширением .csv.
В качестве параметра каждой процедуры указывается разделитель csv файла. Данные можно загрузить из csv-файлов в папке datasets.

Part 2. Изменение данных

Скрипт part2.sql содержит слкдующие функции и процедуры.

1) Процедура добавления P2P-проверки

Параметры: ник проверяемого, ник проверяющего, название задания, статус P2P-проверки, время. \

2) Процедура добавления проверки Verter'ом

Параметры: ник проверяемого, название задания, статус проверки Verter'ом, время. \

3) Триггер: после добавления записи со статусом «начало» в таблицу P2P изменяется соответствующая запись в таблице TransferredPoints

4) Триггер: перед добавлением записи в таблицу XP проверяется корректность добавляемой записи

Запись считается корректной, если:

• Количество XP не превышает максимальное доступное для проверяемой задачи.
• Поле Check ссылается на успешную проверку.

Part 3. Получение данных

Скрипт part3.sql содержит слкдующие процедуры и функции:

1) Функция, возвращающая таблицу TransferredPoints в более человекочитаемом виде

Ник пира 1, ник пира 2, количество переданных пир-поинтов.
Количество отрицательное, если пир 2 получил от пира 1 больше поинтов.

Пир - это ученик «Школы 21».

2) Функция, возвращающая таблицу вида: ник пользователя, название проверенного задания, кол-во полученного XP

В таблицу включи только задания, успешно прошедшие проверку (определять по таблице Checks).
Одна задача может быть успешно выполнена несколько раз. В таком случае в таблицу включи все успешные проверки.

3) Функция, определяющая пиров, которые не выходили из кампуса в течение всего дня

Параметры функции: день, например, 12.05.2022.
Функция возвращает только список пиров.

4) Расчет изменения в количестве пир-поинтов каждого пира по таблице TransferredPoints

Формат вывода: ник пира, изменение в количество пир-поинтов.

5) Расчет изменения в количестве пир-поинтов каждого пира по таблице, возвращаемой первой функцией из Part 3

Формат вывода: ник пира, изменение в количество пир-поинтов.

6) Определение самого часто проверяемого задания за каждый день

Формат вывода: день, название задания.

7) Определение всех пиров, выполнивших весь заданный блок задач и дату завершения последнего задания

Параметры процедуры: название блока, например, «CPP».
Формат вывода: ник пира, дата завершения блока (т. е. последнего выполненного задания из этого блока).

8) Определение, к какому пиру стоит идти на проверку каждому обучающемуся

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

9) Определение процента пиров, которые:

• Приступили только к блоку 1;
• Приступили только к блоку 2;
• Приступили к обоим;
• Не приступили ни к одному.

Параметры процедуры: название блока 1, например, SQL, название блока 2, например, A.
Формат вывода: процент приступивших только к первому блоку, процент приступивших только ко второму блоку, процент приступивших к обоим, процент не приступивших ни к одному.

10) Определение процента пиров, которые когда-либо успешно проходили проверку в свой день рождения

Также определение процента пиров, которые хоть раз проваливали проверку в свой день рождения.
Формат вывода: процент пиров, успешно прошедших проверку в день рождения, процент пиров, проваливших проверку в день рождения.

11) Определение всех пиров, которые сдали заданные задания 1 и 2, но не сдали задание 3

Параметры процедуры: названия заданий 1, 2 и 3.
Формат вывода: список пиров.

12) Определение кол-ва предшествующих задач для каждой задачи

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

13) Поиск «удачныех» для проверок днией. День считается «удачным», если в нем есть хотя бы N идущих подряд успешных проверки

Параметры процедуры: количество идущих подряд успешных проверок N.
Под идущими подряд успешными проверками подразумеваются успешные проверки, между которыми нет неуспешных.
При этом кол-во опыта за каждую из этих проверок должно быть не меньше 80% от максимального.
Формат вывода: список дней.

14) Определение пира с наибольшим количеством XP

Формат вывода: ник пира, количество XP.

15) Определение пиров, приходивших раньше заданного времени не менее N раз за всё время

Параметры процедуры: время, количество раз N.
Формат вывода: список пиров.

16) Определение пиров, выходивших за последние N дней из кампуса больше M раз

Параметры процедуры: количество дней N, количество раз M.
Формат вывода: список пиров.

17) Определение процента ранних входов для каждого месяца

Расчет для каждого месяца, сколько раз люди, родившиеся в этом месяце, приходили в кампус за всё время (общее число входов).
Расчет для каждого месяца, сколько раз люди, родившиеся в этом месяце, приходили в кампус раньше 12:00 за всё время (число ранних входов).
Расчет процента ранних входов в кампус относительно общего числа входов для каждого месяца.
Формат вывода: месяц, процент ранних входов.

Part 4. Метаданные

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

1) Хранимая процедура, которая, не уничтожая базу данных, уничтожает все те таблицы текущей базы данных, имена которых начинаются с фразы 'TableName'.

2) Хранимая процедура с выходным параметром, которая выводит список имен и параметров всех скалярных SQL-функций пользователя в текущей базе данных. Имена функций без параметров выводить не нужно. Имена и список параметров должны выводиться в одну строку. Выходной параметр возвращает количество найденных функций.

3) Хранимая процедура с выходным параметром, которая уничтожает все SQL DML триггеры в текущей базе данных. Выходной параметр возвращает количество уничтоженных триггеров.

4) Хранимая процедура с входным параметром, которая выводит имена и описания типа объектов (только хранимых процедур и скалярных функций), в тексте которых на языке SQL встречается строка, задаваемая параметром процедуры.