この記事では、PostgreSQLのデモデータベースについて説明します。なぜそれが私たちにとって重要で、どのように役立つか、スキーマがどのように構成されているか、スキーマに含まれるデータです。
詳細な説明へのリンクをすぐに提供します（デモデータベースの入手先とインストール方法も記載されています）。
なんで？
私たちの観点からは、デモンストレーションベースの必要性は長い間待ち望まれています。 ほとんどすべてのDBMS機能について説明するには、いくつかのデータ、1つまたは複数のテーブルが必要です。 毎回このバイクを再発明することは、リスナーの注意と彼自身の時間の両方を無駄にします。 理由もなく、すべてのDBMS製造元にはベースがあり、何かを実証する必要があるたびに使用されます。
なぜこのようなデータベースが必要なのでしょうか？
まず、SQLの自習用。 あなたが学生で、SQLをマスターし、たとえば再帰クエリについて読んだとします。 結局のところ、何かを練習する必要がありますか？
一方、学生が再帰クエリについて読むことができるようにするには、誰かがそれについて書く必要があります。 当社は複数の著者と協力しており、現在データベーステクノロジーの大学コースとSQLトレーニングマニュアルに取り組んでいます。どちらの本もデモデータベースを使用します。 本を読むだけでなく、本に記載されている例をすぐに再現し、それらを使って「遊び」、実用的なタスクを完了することができます。
別のオプションは、大学のデータベースコースで練習を行うことです（または商用SQLコースを読むこともできます。デモデータベースが発行されているPostgreSQLライセンスで許可されています）。 そのような使用例はすでにあります。
ブログのメモやPostgreSQLとその機能に関する記事を書くためにデモベースを使用すると便利です。 毎回「ラベルを作成し、generate_seriesを使用してデータを挿入する」という言葉で始める代わりに、すぐにビジネスに取り掛かることができます。
また、スキーマとデモデータベースのデータに可能な限り依存するように、PostgreSQLドキュメントを長期にわたって改訂することも考えています。
何が必要ですか？
デモデータベースのいくつかの要件を提示します。
- データスキーマは、あまり説明しなくても理解できるほど単純でなければなりません。
- 同時に、データスキームは、最もささいなクエリではない構築を可能にするのに十分複雑でなければなりません。
- データベースには、実際のデータに似たデータを入力する必要がありますが、これは興味深いものです。
もちろん、私たちが最初にしたことは、 どの塩基がすでに存在するかを調べることでしたが、私たちに適した塩基はありませんでした。 決して「悪い」とは言いたくありませんが、それらは他のタスクのために作成されました：あるものではスキームが単純すぎ、あるものは専門的すぎ、あるものでは原始的な内容です。
データスキーマ
したがって、結果としてデータベースは、独自に作成しました。 あなたが写真から推測したかもしれないように、航空輸送は主題分野として選ばれました：我々は子会社航空会社について話している（これまでのところ、悲しいかな、まだ存在していません）。 データスキームを図に示します。
ここでの主な本質は予約です。
複数の乗客を1つの予約に含めることができ、各乗客には個別のチケット （チケット）が発行されます。 そのため、乗客は個別のエンティティではありません。簡単にするために、すべての乗客は一意であると想定できます。
チケットには、1つ以上のフライト （ticket_flights）が含まれます。 いくつかのケースでは、いくつかのフライトがチケットに含まれる場合があります。
- 出発地と目的地を結ぶ直行便はありません（乗り継ぎ便）。
- 帰りのチケットが取られました。
データスキームに厳密な制限はありませんが、同じ予約のすべてのチケットが同じフライトセットを持っていると想定されます。
各フライトは、ある空港から別の空港へと続きます。 同じ番号のフライトの出発地と目的地は同じですが、出発日が異なります。
フライトの登録時に、乗客は搭乗券 （boarding_passes）を発行されます。これは飛行機の場所を示します。 乗客は、自分のチケットにあるフライトのみチェックインできます。 フライトと飛行機の座席の組み合わせは、座席ごとに2つの搭乗券の発行を防ぐために一意でなければなりません。
航空機の座席数とサービスクラス別の分布は、フライトを実行している航空機 （航空機）のモデルによって異なります。 各モデルには内部レイアウトが1つだけあると想定されています。 データスキームは、搭乗券の座席が飛行機の座席に対応することを制御しません。
回路のすべてのオブジェクトについては、この記事の冒頭ですでに言及したドキュメントで詳しく説明しています 。 また、単純なクエリの形式のテーブルへの「ガイド」もあります。
中身は？
クエリの作成方法を学習するには、データベースにすでにいくつかのデータが含まれており、数行ではなく、かなり大きな配列が含まれている必要があります。 デモデータベースには、データ量が異なる3つのバージョンがあります。
- 小さなデータベースには、1か月のフライトに関するデータが含まれています。 ディスク容量をあまり消費しませんが、クエリを作成できます。
- 平均ベースは 3か月にわたって広がっています。
- 今年の大規模なフライトでは、パフォーマンスに関連するニュアンスを直接体験できます。
一般に、テストデータの生成はそれ自体が魅力的なアクティビティであり、後で説明します。 しかし、この問題を解決するツール（ DataFillerなど）が長い間存在していたため、興味深いのは何ですか？ はい、それらは存在しますが、それはすべての情報品質があなたに合っているかどうかに依存します。
たとえば、チケットには乗客の名前と姓が含まれています。 このフィールドのデータを生成するにはどうすればよいですか？ いくつかのオプションを考え出すことができます。
最も簡単な方法は、ランダムな文字から特定の長さの文字列を形成することです。 レイブラッドベリーはAaa氏を買う余裕がありますが、QDEMFI TGBSWAJVZHに会う準備はできていますか（これは、作り上げの例ではありません）？
姓と名の事前定義リストから値を選択できます。 それは真実に近いものになりますが、データ配信などもあります。 同様に可能性のある名前の1つを選択すると、AlexandrovはHalf-Actsとほぼ同じデータベースに格納されます。 違いは何ですか？ ただし、大きな違いがあります。Aleksandrovのすべてを取得する必要がある場合、実際のデータベースでは、すべての行の約10％を選択する必要があり、Half-Actsはない場合があります。 そして、これは、ある場合と他の場合のクエリプランが異なる必要があることを意味します。このため、DBMSは列のデータの分布に関する統計を収集します。
より正直な方法は、各名前と各姓に周波数特性を使用することです。 それが私たちがしたことです。 （国の特性と名前の人気の経時的な変化を考慮に入れることもできますが、時間内に停止することが重要です。）
別の例を示します。 データベースには、約100の空港が含まれています。 直行便はすべての空港を結ぶわけではありませんが、どの空港からでも複数の乗り換えで他の空港に行くことができます。 つまり、接続のグラフは不完全である必要がありますが、接続されている必要があります。 生成方法 繰り返しますが、それはすべて、データ品質が私たちに合っているかどうかに依存します。
単純な場合、最初の任意の空港を2番目に劣らない任意の空港に関連付けてから、2番目の空港を次の空港に数回関連付けることができます。 接続されていない空港を優先するたびに、適切なグラフが表示されます。 彼は真実のように見えるでしょうか？ 少なくともではありません。 取得できるものは次のとおりです（線の色は乗客の流れに依存します：暗いほど、より負荷がかかります）。
よく見ると、すべての都市がかなり均一なウェブによって互いに接続されていることがわかります。 そして、ロシアのフライトの実際のグラフは次のようになります（ OpenFlights.orgによる）。
ここでの特徴は、リンクの大部分が少数のノードに集中していることです。 このようなグラフはスケールレスと呼ばれます。 リンクによって、それらの生成のためのアルゴリズムを見つけることができます。
私たちの場合、グラフを生成するだけでなく、それを実際の都市に重ねる必要があります（どのような状況でも、モスクワがロシア最大のハブになることは明らかです）。 実際、実際のデモベースを超えて少し広く見ると、タスクが簡素化されます。各空港を説明するために、座標だけでなく、いくつかの特性も使用します。 そのうちの1つは乗客の交通量であり、記事の冒頭でその助けを借りて生成されたグラフを見ました。
そして、なぜ既存の航空会社のルートをとってみませんか？ もちろん、そうすることはできますが、柔軟性は失われます。アルゴリズムを使用すると、任意の数の都市、架空の国、または銀河間飛行について妥当なグラフを生成できます。
-ところで、空港から他の空港への移動に必要な乗り継ぎの最大数はいくらですか？ （もちろん、この質問に対する答えはSQLクエリでなければなりません。）
さて、ここでルートグラフを生成しましたが、それでも通常のフライトスケジュールに変換する必要があります。 さらに、ポイントAとB間のフライトは、全員を輸送するのに十分なはずですが、多すぎることはありません。そうしないと、飛行機は空を飛んでしまいます。 また、航空機の種類を考慮する必要があります。 より小さな飛行機に乗り、より多くのフライトを行うことができます。
-デモデータベースには、割り当てられた航空機の最大範囲を超えるフライトがありますか？
またはその逆-フライトは少ないが飛行機は大きい。 しかし、すべての空港が大型の大型船を受け入れることができるわけではありません。 これは必要に応じて確認することもできますが、空港クラスに関する情報をデモデータベース自体に転送しませんでした。
まあなど。 ここでは、データ生成が一見したほど簡単ではないことを示唆するいくつかの質問を示します。
-実際の飛行時間は計画された時間とどのように異なりますか？
-通常、西から東へのフライトは長く（夜に飛び出して翌日の朝に到着します）、東から西へは短いです（ほぼ同じ日に到着します）。 そして、デモベースではどうなりますか？
-予約時間とチェックイン時間は、フライトの日付と時間に関してどのように分割されますか？
-通常、1つの予約に含まれる人数は？
-往復する乗客はいますか？ 「ある」ルートは常に「戻る」ルートと一致していますか？
-すべての乗客は、予約時に選択したサービスクラスに対応する搭乗券の座席を持っていますか？
-乗客がキャビン内にない座席のチケットを受け取ることはありますか？ 2人の乗客が1つの座席を要求できますか？
-同じフライトの同じサービスクラスの座席のチケットの費用は常に同じですか？
最後に
このデータを操作することに興味を持ったのと同じように、このデータを操作することもあなたにとって興味深いことではないことを願っています。 さらに（即時ではありませんが）計画には、全文検索、不十分な構造の情報、一時的なデータ、さまざまなインデックス戦略など、より「高度な」分野をカバーするスキームの開発が含まれます。
デモデータに矛盾が見つかった場合（これが起こる可能性があります-世界のすべてを予見するのは困難です）、遠慮なくedu@postgrespro.ru宛てにご連絡ください 。
また、データスキーマの実際の使用についても非常に興味があります。 あなたの経験を共有してください。そして、私たちはコミュニケーションを受け入れ、私たちを共有する準備ができています。