Базы данных/Лабораторная работа 2

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

Всё ещё в процессе написания, но задания можно начинать делать

Работа с реляционной алгеброй

В предыдущей лабораторной работе вы работали как правило с одной таблицей, выполняя операции селекции (добавляя условия в WHERE) и проекцией (перечисляя, что именно вы хотите выбрать между SELECT и FROM, в том числе, выполняя подзапросы к другим базам). Основные механизмы, которые дают большое преимущество реляционным СУБД относятся к взаимодействию нескольких отношений или результатов выборок. Эта лабораторная работа завершает введение и исчерпывает тему DML.

Объединения

Объединять множества в реляционных базах данных можно двумя способами: вертикально и горизонтально. Горизонтальные объединения используются намного чаще, так как реляционные базы нормализованы и нужные вам атрибуты могут быть раскиданы по разным сущностям. Посмотрите, как это выглядит в базе IMDb:

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

Исчерпывающее руководство для визуалов:

Но если вы любите копировать команды из методички и смотреть, что получается, читайте дальше:)

Введение в JOIN

Присоединение обычно происходит по некоторым атрибутам, которые называются первичными и внешними ключами. Первичный ключ обычно называется id, он содержится во всех таблицах, которые претендуют на участие в верно построенной реляционной базе. Также таблицы могут содержать внешние ключи - это атрибуты, как правило типа unsigned integer, которые содержат те же значения, что и первичные ключи соответствующих сущностей.

Например, теперь, если вы хотите получить отчет, в котором будут следующие поля: имя актера, имя персонажа, фильм, год выпуска фильма, то вам нужно присоединить к таблице cast_info таблицы, где содержатся названия фильмов, имена актеров и имена персонажей, а именно: char_name, title и name, которые нужно присоединить по их первичным ключам.

Внутреннее объединение INNER JOIN

[В процессе написания]

• Задание:* Сделайте следующий отчет: название компании, выпускающей кинофильмы; название фильма; факты о фильме - для какой-либо выбранной вами кинокомпании (выберите более менее активную) за период в 5 последних лет. Диапазон дат укажите не конкретными датами, а относительно сегодняшнего дня, поищите соответствующие функции работы с временем.

Объединения слева и справа LEFT JOIN, RIGHT JOIN

[В процессе написания]

Задания:

1. Найдите фильмы, в которых нет персонажей.
2. Найдите актеров, которые никогда не снимались в фильмах. (двумя способами: с помощью JOIN и с помощью WHERE NOT EXIST)

Полное объединение FULL JOIN

[В процессе написания]

Пока никаких идей для запроса, full join нужен обычно для того, чтобы обнаруживать нарушения целостности.

Декартово произведение

[В процессе написания]

Посчитайте, сколько всего различных комбинаций актеров и персонажей может быть исходя из базы. В этой выборке следует также участь, что персонаж может появляться в разных фильмах, но называться одинаково, при этом просто умножив cast_info на что-то не получится. Выбирайте сразу количество, не выводя результаты выборки.

Оконные функции

[В процессе написания]

• http://www.postgresql.org/docs/9.3/static/tutorial-window.html
• https://habrahabr.ru/post/268983/

Создание представлений CREATE VIEW

[В процессе написания]

• http://postgresql.ru.net/gruber/ch20.html
• http://www.postgresql.org/docs/9.3/static/sql-createview.html

Комплексные выборки

Для выполнения этих заданий используйте всё, что знаете.

1. Выберите топ 10 фильмов, над созданием которых потрудилось больше всего людей (таблица complete_cast), поля: название фильма, количество всех, кто участвовал в создании, количество актеров
2. Выберите топ 10 актеров, которые снялись в наибольшем количестве фильмов, поля: имя актера, дата рождения, количество фактов, количество фильмов, в которых он снялся, кинокомпания, с которой актер сотруднила над наибольшим количеством фильмов.
3. Выберите топ 10 кинорежиссеров, которые сняли фильмы с наибольшим количеством задействованных людей, поля: название фильма, среднее количество людей, которые принимают участие в фильме режиссера, дата рождения режиссера, количество фактов о режиссере.