CONTENTS コンテンツ

SQLを学ぼう！(8)

学習編-SQL

2024.06.24

　生成AIで架空のポケモンを作るためのデータベースを作る過程で必要になったSQLについて学んでいます。今回は正規化のボイスコッド正規化から。

入力：ボイスコッド正規化の練習問題を作ってください

出力：

もちろんです。以下にボイスコッド正規形（BCNF）に関する練習問題を作成しました。BCNFは、第三正規形をさらに厳密にしたものであり、すべての決定者が候補キーであることを求めます。

練習問題

問題

以下の第三正規形のテーブルをボイスコッド正規形に変換してください。

3NF Table: Enrollments

student_idcourse_idinstructor_idinstructor_name
11011001Smith
21021002Johnson
31011001Smith
41031003Williams
51041004Brown
61021002Johnson

質問: 上記のテーブルをボイスコッド正規形に変換するために、必要なテーブルスキーマを示してください。

回答例

解説: まず、現在のテーブルの決定者（決定要因）を確認します。このテーブルでは、course_id が instructor_id および instructor_name を決定します。したがって、course_id は候補キーの一部であり、他のキーに完全に依存しません。

BCNFに変換するために、次のようにテーブルを分割します。

  1. Course_Instructors テーブル: 各コースのインストラクター情報を保持します。
  2. Enrollments テーブル: 学生の登録情報を保持します。
-- Course_Instructors テーブル
CREATE TABLE Course_Instructors (
  course_id INTEGER PRIMARY KEY,
  instructor_id INTEGER,
  instructor_name VARCHAR(255)
);

-- Enrollments テーブル
CREATE TABLE Enrollments (
  student_id INTEGER,
  course_id INTEGER,
  PRIMARY KEY(student_id, course_id),
  FOREIGN KEY(course_id) REFERENCES Course_Instructors(course_id)
);

-- データ挿入例
INSERT INTO Course_Instructors (course_id, instructor_id, instructor_name) VALUES
(101, 1001, 'Smith'),
(102, 1002, 'Johnson'),
(103, 1003, 'Williams'),
(104, 1004, 'Brown');

INSERT INTO Enrollments (student_id, course_id) VALUES
(1, 101),
(2, 102),
(3, 101),
(4, 103),
(5, 104),
(6, 102);

このようにして、テーブルをBCNFに正規化することで、すべての決定者が候補キーとなるようにデータを整えます。

　なにがキーか書いてないので仮定を置く必要があって問題としては微妙。だけどChatGPTなので「キーはstudent_idとcourse_idですか？」と聞いていけば手軽に補完できる。インタラクティブであることの利点だ。

　続いてインデックス。教材ではインデックスの設計概念のみの話だったので、実装の問題を解いて地に足を付けよう。

入力：SQLのインデックスの練習問題を作ってください

出力：

もちろんです。以下に、SQLのインデックスに関する練習問題を作成しました。これらの問題を通じて、インデックスの作成方法やその利点を理解することができます。

練習問題

問題1: インデックスの基本

以下の employees テーブルに対して、 last_name カラムにインデックスを作成してください。

Employees Table:

employee_idfirst_namelast_namedepartmentsalary
1JohnDoeHR50000
2JaneSmithIT60000
3MichaelBrownFinance55000
4EmilyDavisMarketing52000
5LisaJohnsonIT62000

質問1.1: last_name カラムにインデックスを作成するSQL文を書いてください。

回答例

回答1.1:

CREATE INDEX idx_last_name ON employees(last_name);

　そっかぁ。インデックスを作る問題を作ってくるから、作られたインデックスの内容がどうかは説明してくれないのか。追加で聞こう。

入力：問題及び回答1で作られたインデックスの中身はどのようになっていますか？

出力：

インデックスの中身

インデックスは通常、Bツリーやハッシュテーブルなどのデータ構造を使用して構築されます。ここでは、Bツリーを使用したインデックスの中身を例に説明します。

Bツリー構造のインデックス例

インデックスがlast_nameカラムに対して作成された場合、インデックスには以下のような情報が格納されます：

Bツリーインデックス:
----------------------------------
|           Root Node            |
|--------------------------------|
|             Smith              |
|--------------------------------|
|         /          \           |
|      Doe            Johnson    |
|      /  \           /  \       |
|   Brown  Davis     ...  ...    |
----------------------------------

インデックスのエントリ

インデックスの各エントリには、インデックス対象のカラムの値と、その値が存在する行のポインタ（参照）が含まれます。例えば：

  • Doe -> Employee ID: 1
  • Smith -> Employee ID: 2
  • Brown -> Employee ID: 3
  • Davis -> Employee ID: 4
  • Johnson -> Employee ID: 5

インデックスの効果

インデックスを使用することで、last_nameカラムに基づく検索が高速化されます。例えば、次のクエリはインデックスを使用して効率的に実行されます：

SELECT * FROM employees WHERE last_name = 'Smith';

インデックスがない場合、このクエリはテーブル全体をスキャンして last_name が ‘Smith’ の行を探す必要がありますが、インデックスがある場合はインデックスを検索するだけで該当する行を見つけることができます。

まとめ

インデックスは、データベース内の特定のカラムに対して効率的な検索を可能にするデータ構造です。問題1の last_name カラムに作成されたインデックスは、各 last_name の値とその行へのポインタを保持しており、検索速度を大幅に向上させます。

入力：インデックスはlast_nameとemployee_idから構成されるテーブルIを保持していて、テーブル全体の25要素を検索する代わりにテーブルIのlast_nameの5行だけ検索するため、検索量が1/5になる、ということでしょうか？

出力(抜粋)：

インデックスの検索

インデックスを使用すると、特定の last_name に対応する employee_id を効率的に検索できます。例えば、last_name が ‘Smith’ のレコードを検索する場合：

  1. テーブル全体をスキャンする場合： employees テーブルのすべての行（5行）を順に調べて、last_name が ‘Smith’ の行を探します。
  2. インデックスを使用する場合： インデックステーブル（I）のみを検索し、対応する employee_id を見つけます。この場合、インデックスはBツリーの構造を持つため、検索は効率的に行われます。

検索効率の向上

インデックスを使用することで、検索対象の行数が大幅に減少します。具体的には、インデックステーブルは元のテーブルよりも小さく、検索範囲を絞ることができます。例えば：

  • 元の employees テーブルには 5 行あります。
  • インデックステーブル（I）も 5 行ですが、インデックスの構造により検索効率が向上します。

実際の効果：

  • インデックスを使うことで、Bツリーなどのデータ構造により、インデックス内での検索が対数時間 (O(log n)) で行われます。
  • 例えば、テーブルが 25 行あった場合、通常の全表スキャンでは 25 行を調べる必要がありますが、インデックスを使うことでその検索は B ツリーの深さに依存し、通常は数回の比較で済みます。

まとめ

インデックスは、特定のカラムの値に基づいて行を効率的に検索するためのデータ構造です。インデックスを使用することで、全表スキャンの代わりにインデックスを検索し、対応する行を迅速に見つけることができます。これは、特に大規模なデータセットでの検索効率を大幅に向上させます。

　あーなるほどね。完全に理解した。検索用にカラムの値と番地のみに情報を削ぎ落したテーブルをもっておいて、検索対象として特定のカラムが指定された場合、専用テーブルを検索して番地を返すことで検索範囲を限定してスピードアップしているってことか。

次回に続く

この記事をシェアする

新着記事