Как работать с диаграммой и описанием базы данных

В предыдущих уроках вы решали задания, в которых требовалось выгрузить данные из конкретных полей и таблиц. Но в реальной жизни специалист по SQL с такими задачами сталкивается редко: чаще всего заказчик не разбирается в особенностях базы и не знает названий полей. Если заказчику понадобятся данные о выручке за 2012 год, специалистам придётся самим разбираться, какие поля для этого понадобятся.

Разобраться в структуре базы поможет ER-диаграмма (от англ. entity-relationship diagram, диаграмма «сущность — связь»). ER-диаграмма графически отображает отношения таблиц в базе данных. Не забудьте, что в базе все данные связаны — этим база отличается от обычного хранилища.

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

Каждый блок схемы представляет собой одну таблицу. Соединительные линии — связи между таблицами. О том, какие бывают связи в базе данных, вы ещё узнаете, а теперь взгляните на то, как отображены таблицы на диаграмме.

Меткой PK отмечено поле, которое является первичным ключом в таблице. С внешним ключом вы ещё не знакомы — его помечают меткой FK. Такие поля помогают связывать таблицы, и подробнее о них вы узнаете в теме про связи в базе данных.

Специалист по SQL часто держит диаграмму базы данных под рукой, ведь так удобнее ориентироваться в таблицах. Но дополнительно сохранять схему вам не придётся — вы найдёте её в каждом уроке этого курса.

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

actor

Содержит данные об актёрах и актрисах.

Таблица включает такие поля:

первичный ключ actor_id — идентификатор актёра или актрисы;
first_name — имя актёра или актрисы;
last_name — фамилия актёра или актрисы;
last_update — дата последнего обновления данных об актёре или актрисе.

album

Содержит информацию об альбомах.

Таблица включает такие поля:

первичный ключ album_id — идентификатор альбома;
title — название альбома;
внешний ключ artist_id (ссылается на таблицу artist) — идентификатор исполнителя.

artist

Содержит данные об исполнителях.

Таблица состоит из полей:

первичный ключ artist_id — идентификатор исполнителя;
name — имя исполнителя.

category

Содержит информацию о категориях фильмов.

Таблица состоит из полей:

первичный ключ category_id — идентификатор категории;
name — название категории, например:
- Comedy — комедия;
- Sci-Fi — научная фантастика.
last_update — дата последнего обновления данных о категории.

client

Содержит данные о покупателях.

Таблица включает поля:

первичный ключ customer_id — идентификатор покупателя;
first_name — имя покупателя;
last_name — фамилия покупателя;
company — место работы;
address — адрес покупателя;
city — город проживания;
state — штат или регион;
country — страна;
postal_code — почтовый индекс;
phone — телефон;
fax — факс;
email — электронная почта;
внешний ключ support_rep_id (отсылает к таблице staff) — идентификатор сотрудника поддержки, который обрабатывал запрос покупателя.

film_actor

Содержит информацию о том, в каких фильмах играли актёры и актрисы из таблицы actor.

Таблица включает поля:

составной первичный ключ:
- actor_id (отсылает к таблице actor) — идентификатор актёра или актрисы;
- film_id (отсылает к таблице movie) — идентификатор фильма;
last_update — дата последнего обновления данных о фильмах.

film_category

Содержит информацию о категориях фильмов из таблицы movie.

В таблицу входят поля:

первичный ключ film_id — идентификатор фильма;
внешний ключ category_id (отсылает к таблице category) — идентификатор категории;
last_update — дата последнего обновления данных о фильмах и категориях.

genre

Содержит информацию о жанрах музыки и кино.

В таблицу входят поля:

первичный ключ genre_id — идентификатор жанра;
name — название жанра, например:
- Blues — блюз;
- Pop — поп-музыка.

invoice

Содержит данные о заказах и выставленных счетах.

Таблица включает поля:

первичный ключ invoice_id — идентификатор счёта;
внешний ключ customer_id (отсылает к таблице client) — идентификатор покупателя;
invoice_date — дата выставления счёта;
billing_address — адрес выставления счёта;
billing_city — город выставления счёта;
billing_state — штат или регион выставления счёта;
billing_country — страна выставления счёта;
billing_postal_code — почтовый индекс адреса выставления счёта;
total — стоимость заказа в долларах.

invoice_line

Содержит информацию о том, какие музыкальные треки входят в заказ.

Таблица состоит из полей:

первичный ключ invoice_line_id — идентификатор строки;
внешний ключ invoice_id (отсылает к таблице invoice) — идентификатор заказа;
внешний ключ track_id (отсылает к таблице track) — идентификатор трека;
unit_price — цена трека в долларах;
quantity — количество таких треков в заказе.

media_type

Содержит информацию о форматах видео и аудио.

Таблица включает поля:

первичный ключ media_type_id — идентификатор формата;
name — название формата, например:
- AAC audio file — аудиофайл в формате AAC;
- Protected MPEG-4 video file — защищённый формат MPEG-4 для видео.

movie

Содержит информацию о фильмах.

Таблица состоит из полей:

первичный ключ film_id — идентификатор фильма;
title — название фильма;
description — описание фильма;
release_year — год выхода;
language_id — идентификатор языка фильма;
rental_duration — срок аренды в днях;
rental_rate — цена аренды в долларах;
length — продолжительность фильма в минутах;
replacement_cost — стоимость замены;
rating — возрастной рейтинг, например:
- PG — детям рекомендуется смотреть такой фильм с родителями;
- PG-13 — детям до 13 лет смотреть такой фильм нежелательно.
last_update — дата последнего обновления данных о фильме в таблице;
special_features — особые характеристики;
fulltext — техническое поле для индексации.

playlist

Содержит информацию о плейлистах.

Таблица включает поля:

первичный ключ playlist_id — идентификатор плейлиста;
name — название плейлиста.

playlist_track

Содержит информацию о том, какие треки входят в плейлисты.

В таблицу входят поля:

первичный ключ playlist_id — идентификатор плейлиста;
внешний ключ track_id (отсылает к таблице track) — идентификатор трека.

staff

Содержит данные о сотрудниках.

В таблицу входят поля:

первичный ключ employee_id — идентификатор сотрудника;
first_name — имя сотрудника;
last_name — фамилия сотрудника;
title — должность сотрудника;
внешний ключ reports_to (отсылает к другой записи таблицы staff) — идентификатор менеджера сотрудника;
birth_date — дата рождения сотрудника;
hire_date — дата, когда сотрудника приняли на работу;
address — адрес сотрудника;
city — город проживания сотрудника;
state — штат или регион проживания сотрудника;
country — страна проживания сотрудника;
postal_code — почтовый индекс;
phone — телефон;
fax — факс;
email — электронная почта.

track

Содержит данные о музыкальных треках.

Таблица включает поля:

первичный ключ track_id — идентификатор трека;
name — название трека;
внешний ключ album_id (отсылает к таблице album) — идентификатор альбома;
внешний ключ media_type_id (отсылает к таблице media_type) — идентификатор аудиоформата;
внешний ключ genre_id (отсылает к таблице genre) — идентификатор жанра трека;
composer — автор трека;
milliseconds — длительность трека в миллисекундах;
bytes — размер трека в байтах;
unit_price — стоимость трека в долларах.

В следующих уроках вам встретятся задания без названий конкретных полей и таблиц. Чтобы с ними справиться, сверяйтесь с ER-диаграммой и описанием базы данных. В самостоятельный проект входят похожие задания, в которых вам пригодится информация о структуре базы.