В этом посте я расскажу о нашей демо-базе данных для PostgreSQL: почему она важна для нас и чем может быть полезна вам, как устроена схема и какие данные она содержит. сразу дам ссылку Полное описание (там же написано, где взять демонстрационную базу данных и как ее установить).
За что?
С нашей точки зрения, необходимость в демонстрационной базе давно назрела.Чтобы обсудить практически любую функцию СУБД, нужны данные, нужна таблица или несколько таблиц.
Каждый раз изобретать этот велосипед — пустая трата как внимания слушателя, так и вашего собственного времени.
Не зря у каждого производителя СУБД есть база данных, которую он использует каждый раз, когда ему нужно что-то продемонстрировать.
Зачем может понадобиться такая база данных? Во-первых, для самостоятельного изучения SQL. Допустим, вы студент, изучаете SQL и читаете, скажем, о рекурсивных запросах.
Вам нужно что-то попрактиковать? С другой стороны, чтобы студент мог прочитать о рекурсивных запросах, кто-то должен об этом написать.
Наша компания сотрудничает с несколькими авторами и в настоящее время работает над университетским курсом по технологии баз данных и учебным пособием по SQL, оба из которых будут использовать демонстрационную базу данных.
Книгу можно будет не только читать, но и сразу воспроизводить приведенные в ней примеры, «играть» с ними, выполнять практические задания.
Другой вариант — попрактиковаться на курсе баз данных в университете (или даже прочитать коммерческий курс по SQL: лицензия PostgreSQL, под которой выпущена демонстрационная база данных, это позволяет).
Примеры такого использования уже есть.
Демо-базу данных также полезно использовать для написания постов в блогах или статей о PostgreSQL и его возможностях.
Вместо того, чтобы каждый раз начинать со слов «давайте создадим таблицу и вставим какие-нибудь данные с помощью ignore_series», можно сразу приступить к делу.
Мы также думаем о том, чтобы со временем переработать документацию PostgreSQL, чтобы она максимально опиралась на схему и данные демонстрационной базы данных.
Что вам нужно?
Мы выдвинули несколько требований к демонстрационной базе данных:- Схема данных должна быть достаточно простой, чтобы ее можно было понять без особых объяснений.
- При этом схема данных должна быть достаточно сложной, чтобы можно было строить не самые тривиальные запросы.
- База данных должна быть наполнена реальными данными, с которыми будет интересно работать.
Я ни в коем случае не хочу сказать, что они «плохие», но они созданы для других задач: в некоторых схема слишком проста, в других они слишком специализированы, в третьих слишком примитивно наполнение.
Схема данных
Поэтому в итоге мы создали собственную базу данных.Как вы уже догадались по картинке, в качестве тематики были выбраны авиаперевозки: речь идет о нашей дочерней авиакомпании (которой, увы, пока не существует).
Диаграмма данных представлена на рисунке: 
Основной сущностью здесь является бронирование (бронирование).
В одно бронирование могут быть включены несколько пассажиров, каждому из которых оформляется отдельный билет. билет (Билеты).
Таким образом, пассажир не является отдельной сущностью: для простоты можно предположить, что все пассажиры уникальны.
Билет включает в себя одно или несколько полеты (ticket_flights).
В билет можно включить несколько рейсов в нескольких случаях:
- Прямого рейса, соединяющего пункты отправления и назначения, нет (рейс с пересадками);
- Возьмите билет туда и обратно.
Каждый полет (полеты) следует из одного аэропорт (аэропорты) в другой.
Рейсы с одинаковым номером имеют одинаковые пункты отправления и назначения, но различаются датой вылета.
При регистрации на рейс пассажиру выдается посадочный талон (boarding_passes), который указывает место в самолете.
Пассажир может зарегистрироваться только на тот рейс, который указан в его билете.
Комбинация рейса и места должна быть уникальной, чтобы предотвратить выдачу двух посадочных талонов на одно место.
Количество места (мест) в самолете и их распределение по классам обслуживания зависит от модели самолет (воздушные суда), выполняющие рейс.
Предполагается, что каждая модель имеет только одну внутреннюю планировку.
Схема данных не гарантирует, что места в посадочных талонах соответствуют имеющимся на борту самолета.
Все объекты схемы подробно описаны в документ , о котором я уже говорил в начале статьи.
Также есть «путеводитель» по таблицам в виде простых запросов.
То, что находится внутри?
Чтобы научиться писать запросы, нужно, чтобы база данных уже содержала какие-то данные, и не пару строк, а достаточно большой массив.Наша демонстрационная база данных доступна в трёх версиях, отличающихся объёмом данных:
- Маленькая база содержит данные полета за один месяц; он не занимает много места на диске, но позволяет писать запросы.
- Средняя база применяется в течение трех месяцев.
- Большая база на основе полетов более года уже позволит вам непосредственно ощутить нюансы, связанные с производительностью.
Что в этом такого интересного, ведь инструменты существуют уже давно (например, DataFiller ) что решит эту проблему? Да, они существуют, но все зависит от того, какое качество информации вас устраивает. Например, билет содержит имя и фамилию пассажира.
Как я могу сгенерировать данные для этого поля? Вы можете придумать несколько вариантов.
Самый простой — сформировать строки заданной длины из случайных символов.
Рэй Брэдбери мог бы позволить себе мистера Ааа, но готовы ли вы встретиться с QDEMFI TGBSWAJVZH (кстати, это не выдуманный пример)? Выбрать значения можно из заранее подготовленного списка имен и фамилий.
Это будет больше похоже на правду, но есть еще такое понятие, как распространение данных.
Если с равной вероятностью выбрать одно из имен, то Александровых в базе будет примерно столько же, сколько Полуэктов.
Казалось бы, какая разница? Но есть разница, и большая: если вам нужно получить все Александры, в реальной базе данных вам придется выбрать около 10% всех строк, а Полуекты могут вообще не быть найдены.
Это значит, что планы запросов в одном и в другом случае должны быть разными — именно поэтому СУБД собирают статистику распределения данных по столбцам.
Более честный способ — использовать частотные характеристики для каждого имени и для каждой фамилии.
Именно это мы и сделали.
(Можно было бы также учитывать национальные особенности и изменение популярности имен с течением времени, но здесь важно вовремя остановиться.
) Вот еще один пример.
В нашей базе около ста аэропортов.
Прямые рейсы связывают не все аэропорты, но из любого можно добраться в любой другой с помощью нескольких пересадок.
Другими словами: граф связей должен быть неполным, но связным.
Как его сгенерировать? Опять же, все зависит от того, какое качество данных нас устраивает. В простом случае можно соединить первый произвольный аэропорт со вторым столь же произвольным аэропортом, затем соединить второй со следующим и так несколько раз.
Если каждый раз отдавать предпочтение аэропортам, которые еще не подключены, то формально мы получим подходящий граф.
Будет ли это выглядеть как настоящее? Ни в малейшей степени.
Вот что у нас может получиться (цвет линий зависит от пассажиропотока: чем темнее, тем перегружен маршрут): 
Если присмотреться, то можно увидеть, что все города связаны друг с другом довольно однородной паутиной.
А вот как выглядит реальный график полетов в России (по данным OpenFlights.org ): 
Характерной особенностью здесь является то, что основная масса соединений сосредоточена в небольшом количестве узлов.
Такие графы называются без накипи ; Алгоритмы их генерации вы также можете найти по ссылке.
В нашем случае нам нужно не просто построить график, но и применить его к реальным городам (ведь понятно, что в любом случае Москва будет крупнейшим хабом России).
На самом деле это упрощает задачу, если выйти за рамки самой демо-базы и посмотреть немного шире: для описания каждого аэропорта мы используем не только координаты, но и еще несколько характеристик.
Один из них — объём пассажиропотока, и график, построенный с его помощью, вы видели в самом начале статьи.
Почему бы просто не воспользоваться маршрутами какой-нибудь существующей авиакомпании? Можно, конечно, сделать это, но вы потеряете гибкость: имея алгоритм, вы сможете сгенерировать правдоподобный график для любого количества городов, или для вымышленной страны, или даже для межгалактических полетов.
— Кстати, какое максимальное количество пересадок нужно, чтобы добраться из любого аэропорта в любой другой? (Разумеется, ответом на этот вопрос должен быть SQL-запрос.
) Итак, мы создали граф маршрутов, но нам еще нужно превратить его в расписание регулярных рейсов.
Причем рейсов между пунктами А и Б должно быть достаточно, чтобы перевезти всех, но не слишком много, иначе самолеты будут летать пустыми.
Также нужно учитывать тип самолета.
Вы можете взять самолет меньшего размера и совершить больше рейсов.
— Есть ли в демо-базе полеты, превышающие максимальную дальность закрепленного за ними самолета? А может быть наоборот – меньше рейсов, но самолет побольше.
Но не все аэропорты могут принимать тяжелые широкофюзеляжные самолеты; Это тоже при желании можно проверить, хотя в саму демо-базу мы не включили информацию о классах аэропортов.
И так далее.
Вот еще несколько вопросов, которые намекают, что генерация данных не так тривиальна, как может показаться на первый взгляд: — Насколько фактическое время полета отличается от запланированного? — Обычно рейсы с запада на восток длинные (вылетаем ночью, прилетаем утром следующего дня), а с востока на запад короткие (прилетаем в один и тот же день практически в одно и то же время).
Что происходит в демонстрационной базе данных? — Как распределяются время бронирования и время регистрации в зависимости от даты и времени вылета? — Сколько человек обычно включается в одно бронирование? — Есть ли пассажиры, летающие туда и обратно? Всегда ли маршрут «туда» совпадает с маршрутом «обратно»? — У всех пассажиров в посадочных талонах указаны места, соответствующие классу обслуживания, выбранному при бронировании? — Могло ли случиться так, что пассажиру выдали билет на место, которого нет в салоне? Могут ли два пассажира претендовать на одно место? — Всегда ли билеты на места одного класса обслуживания на одном рейсе стоят одинаково (и почему)?
Окончательно
Надеемся, что вам будет так же интересно работать с этими данными, как было интересно работать с ними нам.В планы на будущее (хотя и не на ближайшее время) входит разработка схемы для охвата более «продвинутых» областей: полнотекстовый поиск, полуструктурированная информация, временные данные, различные стратегии индексации.
Если вы обнаружите какие-либо расхождения между демо-данными и здравым смыслом (а это вполне может случиться – ведь все на свете предусмотреть сложно), не стесняйтесь, напишите нам по адресу [email protected] .
Нам также очень интересно услышать о реальном использовании схемы данных.
Поделитесь своим опытом, а мы, в свою очередь, открыты к общению и готовы поделиться своим.
Теги: #postgresql #sql #базы данных #демонстрация #генерация данных #postgresql #sql
-
Преимущества Erp В Страховом Секторе
19 Oct, 24 -
Зеленый Веб-Хостинг
19 Oct, 24 -
Мы Оценим Вашу Деятельность. Недорогой
19 Oct, 24
