avvessalom / itmo-information-systems-and-databases

Лабораторные работы по Информационным системам и базам данных (ИТМО, ПИиКТ-СиППО, 3 курс).

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

• На основе предложенной предметной области (текста) составить ее описание. Из полученного описания выделить сущности, их атрибуты и связи.
• Составить инфологическую модель.
• Составить даталогическую модель. При описании типов данных для атрибутов должны использоваться типы из СУБД PostgreSQL.
• Реализовать даталогическую модель в PostgreSQL. При описании и реализации даталогической модели должны учитываться ограничения целостности, которые характерны для полученной предметной области.
• Заполнить созданные таблицы тестовыми данными.

Описание предметной области, по которой должна быть построена доменная модель:

Олвин уже почти трансформировал свою комнату, когда до его сознания дошел настойчивый сигнал, напоминающий позвякивание
колокольчика. Сформировав мысленный импульс, Олвин позволил гостю появиться, и стена, на которой он только что занимался 
живописью, снова связала его с внешним миром. Как он и ожидал, в обрисовавшемся проеме стояли его родители, а чуть позади
них -- Джизирак. Присутствие наставника означало, что визит носит не просто семейный характер. Впрочем, даже и не будь 
здесь Джизирака, он бы все равно догадался об этом. 

Лабораторная Работа №2

Составить запросы на языке SQL (пункты 1-7).

1. Сделать запрос для получения атрибутов из указанных таблиц, применив фильтры по указанным условиям: Н_ТИПЫ_ВЕДОМОСТЕЙ, Н_ВЕДОМОСТИ. Вывести атрибуты: Н_ТИПЫ_ВЕДОМОСТЕЙ.ИД, Н_ВЕДОМОСТИ.ИД. Фильтры (AND): a) Н_ТИПЫ_ВЕДОМОСТЕЙ.НАИМЕНОВАНИЕ > Ведомость. b) Н_ВЕДОМОСТИ.ДАТА < 2010-06-18. c) Н_ВЕДОМОСТИ.ДАТА > 2022-06-08. Вид соединения: INNER JOIN.

2. Сделать запрос для получения атрибутов из указанных таблиц, применив фильтры по указанным условиям: Таблицы: Н_ЛЮДИ, Н_ВЕДОМОСТИ, Н_СЕССИЯ. Вывести атрибуты: Н_ЛЮДИ.ИМЯ, Н_ВЕДОМОСТИ.ДАТА, Н_СЕССИЯ.УЧГОД. Фильтры (AND): a) Н_ЛЮДИ.ФАМИЛИЯ > Иванов. b) Н_ВЕДОМОСТИ.ИД < 1250972. Вид соединения: INNER JOIN.

3. Вывести число фамилий и отчеств без учета повторений. При составлении запроса нельзя использовать DISTINCT.

4. Выдать различные отчества людей и число людей с каждой из этих отчеств, ограничив список отчествами, встречающимися менее 50 раз на кафедре вычислительной техники.

   Для реализации использовать соединение таблиц.

5. Выведите таблицу со средними оценками студентов группы 4100 (Номер, ФИО, Ср_оценка), у которых средняя оценка не больше средней оценк(е|и) в группе 1101.

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

   номер группы;

   номер, фамилию, имя и отчество студента;

   номер пункта приказа;

   Для реализации использовать соединение таблиц.

7. Сформировать запрос для получения числа на ФКТИУ отличников.

Лабораторная Работа №3

Составить запросы на языке SQL (пункты 1-2).

Для каждого запроса предложить индексы, добавление которых уменьшит время выполнения запроса (указать таблицы/атрибуты, для которых нужно добавить индексы, написать тип индекса; объяснить, почему добавление индекса будет полезным для данного запроса).

Для запросов 1-2 необходимо составить возможные планы выполнения запросов. Планы составляются на основании предположения, что в таблицах отсутствуют индексы. Из составленных планов необходимо выбрать оптимальный и объяснить свой выбор. Изменятся ли планы при добавлении индекса и как?

Для запросов 1-2 необходимо добавить в отчет вывод команды EXPLAIN ANALYZE запрос

Подробные ответы на все вышеперечисленные вопросы должны присутствовать в отчете (планы выполнения запросов должны быть нарисованы, ответы на вопросы - представлены в текстовом виде).

1. Сделать запрос для получения атрибутов из указанных таблиц, применив фильтры по указанным условиям: Н_ТИПЫ_ВЕДОМОСТЕЙ, Н_ВЕДОМОСТИ.

   Вывести атрибуты: Н_ТИПЫ_ВЕДОМОСТЕЙ.ИД, Н_ВЕДОМОСТИ.ДАТА.

   Фильтры (AND): a) Н_ТИПЫ_ВЕДОМОСТЕЙ.ИД < 3. b) Н_ВЕДОМОСТИ.ДАТА > 1998-01-05.

   Вид соединения: RIGHT JOIN.

2. Сделать запрос для получения атрибутов из указанных таблиц, применив фильтры по указанным условиям: Таблицы: Н_ЛЮДИ, Н_ОБУЧЕНИЯ, Н_УЧЕНИКИ.

   Вывести атрибуты: Н_ЛЮДИ.ФАМИЛИЯ, Н_ОБУЧЕНИЯ.НЗК, Н_УЧЕНИКИ.НАЧАЛО.

   Фильтры: (AND) a) Н_ЛЮДИ.ОТЧЕСТВО < Георгиевич. b) Н_ОБУЧЕНИЯ.ЧЛВК_ИД > 163276. c) Н_УЧЕНИКИ.ИД = 1.

   Вид соединения: LEFT JOIN.

Лабораторная работа №4

Для отношений, полученных при построении предметной области из лабораторной работы №1, выполните следующие действия:

• опишите функциональные зависимости для отношений полученной схемы (минимальное множество);
• приведите отношения в 3NF (как минимум). Постройте схему на основеNF (как минимум). Постройте схему на основе полученных отношений;
• опишите изменения в функциональных зависимостях, произошедшие после преобразования в 3NF (как минимум). Постройте схему на основеNF;
• преобразуйте отношения в BCNF. Докажите, что полученные отношения представлены в BCNF;
• какие денормализации будут полезны для вашей схемы? Приведите подробное описание.