Демонстраційна база даних для PostgreSQL

У цій замітці я розповім про нашу демонстраційній базі даних для PostgreSQL: чому вона важлива для нас і може стати в нагоді вам, як влаштована схема і які дані в ній містяться.
Відразу наведу посилання на повний опис (там же написано, де взяти демо-базу і як її встановити): https://edu.postgrespro.ru/bookings.pdf.
image

Навіщо?

З нашої точки зору, необхідність в демонстраційній базі назріла давно. Щоб обговорити практично будь-яку можливість СУБД, потрібні якісь дані, потрібна таблиця або таблиці. Винаходити велосипед кожен раз заново — даремно витрачати і увагу слухача, і свій власний час. Недарма кожен виробник СУБД має базу, яку і використовує щоразу, коли потрібно щось продемонструвати.
Для чого може знадобитися таке база даних?
По-перше, для самостійного вивчення SQL. Припустимо, ви студент, освоюєте SQL і прочитали, скажімо, про рекурсивні запити. Треба ж на чомусь потренуватися?
З іншого боку, щоб студент зміг прочитати про рекурсивні запити, потрібно, щоб хто-небудь про це написав. Наша компанія співпрацює з кількома авторами і зараз йде робота над університетським курсом по технологій баз даних та навчальним посібником з SQL — обидві книги будуть використовувати демонстраційну базу. Можна буде не просто прочитати книгу, а тут же відтворити наведені в ній приклади, «погратися» з ними, виконати практичні завдання.
Інший варіант — проведення практики за курсом баз даних у вузі (або навіть читання комерційного курсу SQL: ліцензія PostgreSQL, під якою випущена демо-база, це дозволяє). Приклади такого використання вже є.
Демонстраційну базу корисно задіяти і для написання заміток в блог або статей про PostgreSQL і його можливості. Замість того, щоб кожен раз починати зі слів «створимо табличку і вставимо які-небудь дані з допомогою generate_series», можна відразу приступати до справи.
Ми думаємо над переробкою згодом документації PostgreSQL, щоб і вона максимально спиралася на схему і дані демо-бази.

Що потрібно?

До демонстраційній базі даних ми висунули кілька вимог:
  • Схема даних повинна бути достатньо простою, щоб бути зрозумілою без особливих пояснень.
  • У той же час схема даних має бути досить складною, щоб дозволяти будувати не самі тривіальні запити.
  • База даних повинна бути наповнена даними, що нагадують реальні, з якими буде цікаво працювати.
Звичайно, першим ділом ми подивилися, які бази вже існують, але жодна з них нас не влаштувала. Ні в якому разі не хочу сказати, що вони «погані», але створювалися для інших завдань: у якихось дуже проста схема, якісь занадто спеціалізовані, в яких-то занадто примітивне наповнення.

Схема даних

Тому базу даних у підсумку ми зробили свою власну. Як ви, можливо, вже здогадалися по картинці, в якості предметної області були обрані авіаперевезення: мова йде про нашої дочірньої авіакомпанії (поки, на жаль, ще неіснуючої). Схема даних наведена на малюнку:
image
Основною сутністю тут є бронювання (bookings).
В одне бронювання можна включити кілька пасажирів, кожному з яких виписується окремий квиток (tickets). Як такий пасажир не є окремою сутністю: для простоти можна вважати, що всі пасажири унікальні.
Квиток включає один або кілька перельотів (ticket_flights). Кілька перельотів можуть включатися до квиток в декількох випадках:
  1. Немає прямого рейсу, що з'єднує пункти відправлення і призначення (політ з пересадками);
  2. Узятий квиток «туди і назад».
У схемі даних немає жорсткого обмеження, але передбачається, що всі квитки в одному бронюванні мають однаковий набір перельотів.
Кожен рейс (flights) випливає з одного аеропорту (airports) в інший. Рейси з одним номером мають однакові пункти вильоту і призначення, але будуть відрізнятися датою відправлення.
При реєстрації на рейс пасажиру видається посадковий талон (boarding_passes), в якому зазначено місце в літаку. Пасажир може зареєструватися тільки на той рейс, який є у нього в квитку. Комбінація рейсу і місця в літаку повинна бути унікальною, щоб не допустити видачу двох посадкових талонів на одне місце.
місць (seats) в літаку і їх розподіл за класами обслуговування залежить від моделі літака (aircrafts), що виконує рейс. Передбачається, що кожна модель має тільки одну компонування салону. Схема даних не контролює, що місця в посадочних талонах відповідають наявним у літаку.
Всі об'єкти схеми докладно описані в документ, на яке я вже посилався на початку статті. Там же наведено «путівник» по таблиць у вигляді простих запитів.

Що всередині?

Щоб вчитися писати запити, потрібно, щоб база даних містила якісь дані, і не пару рядків, а досить великий масив. Наша демонстраційна база доступна в трьох варіантах, що відрізняються обсягом даних:
  • Невелика база містить дані про польоти за один місяць; вона не займає багато місця на диску, але цілком дозволяє писати запити.
  • Середня база поширюється на три місяці.
  • Велика база польотів за рік уже дасть можливість безпосередньо відчути нюанси, пов'язані з продуктивністю.
Взагалі, генерація тестових даних — саме по собі захоплююче заняття і далі мова піде саме про неї. А що ж тут цікавого, адже давно існують інструменти (наприклад, DataFiller), які вирішують цю задачу? Так, існують, але все залежить від того, яка якість інформації вас влаштовує.
Наприклад, у квитку є ім'я і прізвище пасажира. Як можна згенерувати дані для цього поля? Можна придумати декілька варіантів.
найпростіший — формувати рядка заданої довжини з випадкових символів. Рей Бредбері міг дозволити собі містера Ааа, але чи готові ви зустрітися з QDEMFI TGBSWAJVZH (це, до речі, не вигаданий приклад)?
Можна вибирати значення з заздалегідь заготовленого списку імен і прізвищ. Це буде більше схоже на правду, але є ще й така штука, як розподіл даних. Якщо вибирати одне з імен рівноймовірно, то Александров в базі виявиться приблизно стільки ж, скільки і Полуектов. Здавалося б, яка різниця? А різниця є, і велика: якщо потрібно отримати усіх Александров, в реальній базі даних вам доведеться відібрати близько 10% всіх рядків, а Полуектов може і зовсім не знайтися. А це означає, що плани запитів в одному і в іншому випадках повинні відрізнятися — саме для цього СУБД збирають статистику розподілу даних у стовпцях.
Більш чесний спосіб полягає у використанні частотних характеристик для кожного імені і для кожного прізвища. Саме так ми і вчинили. (Можна було б ще врахувати національні особливості та зміна популярності імен з часом, але тут важливо вчасно зупинитись.)
Ось інший приклад. В нашій базі даних міститься близько ста аеропортів. Прямі рейси з'єднують далеко не всі аеропорти, але з якого можна дістатися в будь-який інший з кількома пересадками. Інакше кажучи: граф зв'язків повинен бути неповним, але зв'язковим. Як його створити? І знову все залежить від того, яка якість даних нас влаштовує.
У простому випадку можна зв'язати перший довільний аеропорт з другим не менш довільним аеропортом, потім зв'язати другий з наступним й так далі кілька разів. Якщо щоразу віддавати перевагу ще не пов'язаним аеропортів, то формально ми отримаємо відповідний граф. Буде він схожий на правду? Ні в найменшій мірі. Ось що у нас може вийти (колір ліній залежить від пасажиропотоку: чим темніше, тим більш навантажений маршрут):
image
Якщо придивитися, то видно, що всі міста пов'язані один з одним досить рівномірної павутиною. А ось як виглядає реальний граф авіарейсів з Росії (за даними OpenFlights.org):
image
Характерна особливість тут полягає в тому, що основна маса зв'язків зосереджена в невеликій кількості сайтів. Такі графи називаються безмасштабными; за посиланням можна знайти і алгоритми їх генерації.
У нашому ж випадку потрібно не просто генерувати граф, але і накласти його на реальні міста (адже зрозуміло, що при будь-якому розкладі найбільшим хабом в Росії буде Москва). Насправді це спрощує завдання, якщо вийти за рамки власне демо-бази і подивитися трохи ширше: для опису кожного аеропорту ми використовуємо не тільки координати, але і ще кілька характеристик. Одна з них — об'єм пасажиропотоку, а згенерований з її допомогою граф ви бачили в самому початку статті.
А чому б просто не взяти маршрути якої-небудь існуючої авіакомпанії? Можна, звичайно, і так, але загубиться гнучкість: маючи алгоритм, можна згенерувати правдоподібний граф для будь-якого числа міст, або вигаданої країни, або взагалі для міжгалактичних перельотів.
— до Речі, яке максимальне число пересадок необхідно, щоб дістатися з будь-якого аеропорту в будь-який інший? (Звичайно, відповіддю на це питання має бути запит SQL.)
Ну добре, ось ми згенерували граф маршрутів, але його ще треба перетворити в розклад регулярних рейсів. Причому рейсів між пунктами А і Б повинно бути достатньо, щоб перевезти всіх охочих, але і не занадто багато, інакше літаки будуть літати порожніми. А ще треба взяти до уваги тип повітряного судна. Можна взяти літак поменше, а рейсів зробити побільше.
— чи Є в демо-базі рейси, що перевищують максимальну дальність призначеного на них літака?
А можна навпаки — рейсів поменше, зате літак побільше. Ось тільки не всі аеропорти можуть приймати важкі широкофюзеляжні суду; це теж при бажанні можна перевірити, хоча в саму демо-базу ми не виносили інформацію про класах аеропортів.
Ну і так далі. Ось ще кілька питань, які натякають на те, що генерація даних не настільки тривіальна, як це могло б здатися на перший погляд:
— Як реальний час польоту відрізняється від запланованого?
— Зазвичай польоти з заходу на схід довгі (вилітаємо вночі, прилітаємо вранці наступного дня), а зі сходу на захід — короткі (прилітаємо в той же день майже в той же час). А що відбувається в демо-базі?
— Як розподілено час бронювання і час реєстрації по відношенню до дати та часу рейсу?
— Скільки людина зазвичай входить в одне бронювання?
— Бувають пасажири, які летять туди-назад? Завжди маршрут «туди» збігається з маршрутом «назад»?
— У всіх пасажирів місця в посадочних талонах відповідають класу обслуговування, обраного при бронюванні?
— Може вийти, що виданий пасажиру квиток на місце, якого немає в салоні? Два пасажира претендувати на одне місце?
— Завжди квитки на місця одного класу обслуговування на одному рейсі коштують однаково (і чому)?

Наостанок

Сподіваємося, що вам буде не менш цікаво працювати з цими даними, ніж нам було цікаво працювати над ними. В подальші (хоч і не найближчі) плани входить розвиток схеми, щоб охопити більш «просунуті» області: повнотекстовий пошук, слабоструктурированную інформацію, темпоральні дані, різні стратегії індексування.
Якщо ви виявите які-небудь невідповідності демонстраційних даних зі здоровим глуздом (а таке цілком може статися — адже складно передбачити все на світі), не посоромтеся написати нам на edu@postgrespro.ru.
Нам також дуже цікаво почути про реальний використанні схеми даних. Поділіться вашим досвідом, а ми, в свою чергу, відкриті для спілкування і готові ділитися своїм.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.