はじめに

会社のDBリソースで扱っている１次情報を手動でスプレッドシートでまとめている現状があるので、それをBigQueryのテーブルを使ってバッチ処理によってテーブルにデータを格納していこうという話がありました。

なので手動で打ち込んでいるスプレッドシートの項目をDBテーブルに落とし込む（正規化）作業となり、DB設計の学習を筆者は急いでしているところです。

『達人に学ぶDB設計』が有名なので学習していますが、インプット→アウトプットのフェーズが重要なので、
ChatGPTを使った、題材選定依頼→正規化→添削のフローを共有できたらと思います。

第３正規形に持っていく考え方を実際のアウトプットをするトレーニングを始めました。
流れとしては、第１正規化→第２正規化→第３正規化
というフローに沿っていくイメージで、ChatGPTを使って、題材選定から正規化の過程の説明を添削してもらいます。
それぞれのフローの中にある、関数従属性であったり、推移的関数従属を正規化のステージごとに洗い出して、ChatGPTに添削してもらいましょう。

フローその１：正規化の考え方の共有

以下のように基本的な概念をChatGPTに共有します(参考：https://qiita.com/MAKOTO1995/items/411c9fe25e80d2a0c371)

会社でスプレッドシートにある非正規形のテーブルを正規化するタスクがあり、書籍で学習しています。
また、DB設計の書籍の概要があって、正規化の章については以下のようなまとめがありました。これを踏まえて今度はアウトプットをしたいのですが、ChatGPTより演習問題を提示して練習をアウトプットすることはできますか？以下は書籍内容の概要と実際に扱うデータ項目です。※以下は内容です。
正規化
正規化の目的
正規化を意識せず設計されたDBでは、下記の問題が起きる。
冗長性：一つの情報が複数のテーブルに存在し、無駄なデータ領域・面倒な更新処理を発生させる。
非一貫性：冗長なデータを持っていることにより、更新処理のタイムラグによってデータに不整合が生じる。
これらを解決するための方法論が正規化である。
第３正規形までの定義
第１正規形
１つのセルには１つの値（スカラ値）しか含まないこと。配列などを含んではいけない。
第１正規形でないことによる問題は、
主キーを決められないこと（主キーが各列の値を一意に特定できない）
テーブルの意味やレコードの単位をすぐに理解できないこと
第2正規形
部分関数従属を排除して完全関数従属とする。
部分関数従属とは、複合主キーが設定されたテーブルにおいて、一部の主キー列のみに対して従属する列があること。
第2正規形でないことによる問題は、
テーブルにデータを登録できないケースがあること
運用を誤ると、部分関数従属している列と主キー列の対応が、レコードごとにマチマチになってしまうこと。
第３正規形
推移的関数従属を排除する。
推移的関数従属とは、主キー以外の列に従属する列がテーブル内に存在すること。
正規化と非正規化
基本的に正規化はすべきだが、パフォーマンスの面でデメリットもある。
検索スピードの悪化
正規化によりテーブルを分割していくと、検索時にSQL分の中で結合（JOIN）が必要になる。
結合はSQLの処理の中でも高コストなため、多用するとSQLの速度低下を引き起こす。
一方、更新処理については正規化した方が早い。(正規化されたテーブルでは冗長性がなく、１レコードの更新で済むため)
正規化の次数が低いほど検索SQLのパフォーマンスは高いがデータ整合性は低い。逆に正規化していくほどパフォーマンスは低下する代わりにデータ整合性が高くなるというトレードオフがある。
ただし、原則的に非正規化はとるべきではない。他の手段でパフォーマンス向上を図り、それでもどうしようもない時の最後の手段として非正規化する。
非正規化のリスク
更新不整合の危険
更新パフォーマンスの低下
データのリアルタイム性の低下
例えば、サマリデータ列（商品数など）を作り非正規化すると、バッチ処理により一定間隔でサマリデータを更新する必要があるが、リアルタイムに整合しているデータではなくなる。
設計変更が発生したときの手戻りが大きい
データモデルの変更は、コードベースの修正に比べて改修コストが非常に大きいことを意識して論理設計しなくてはいけない。

フローその２：　題材選定の依頼

以下のように題材選定を依頼するといくらか提案してくれます。

題材に移りたいと思いますが、そのほかどのような題材があるのかいくつかメニューを提示していただけますでしょうか？

ChatGPTの解答は次のような題材

図書館管理システム: 図書の貸出、返却、予約システム。図書、著者、出版社、利用者などのデータを含む。
病院管理システム: 患者の診察、治療記録、薬の処方。患者、医師、診察記録、薬品などのデータを含む。
大学の学生管理システム: 学生の登録、成績管理、コース登録。学生、教員、コース、成績などのデータを含む。
ホテル予約システム: 顧客の予約管理、部屋の割り当て、料金計算。顧客、部屋、予約、請求書などのデータを含む。
レストランの注文管理システム: 注文の受付、料理の準備、支払い処理。顧客、注文、メニュー、支払いなどのデータを含む。
交通管理システム: バスや電車の運行スケジュール、乗客の乗車情報。車両、ルート、スケジュール、乗客などのデータを含む。

フローその３：　題材を選定する

実際にやりたい題材を選定するとChatGPTがデータセットを投げてくれます。
このデータセットはまだ第１正規形にはなっていないため、スカラ値を自分で構築する場面からのスタートとなります。

「大学の学生管理システム」をテーマにしたデータベース正規化の演習を行います。このシステムでは、学生の登録、成績管理、コース登録などの情報を管理します。以下のようなデータセットを用いて、第3正規形までの正規化を進めてみます。
題材: 大学の学生管理システム
データセット:
学生ID
学生名
学生の住所
学生のメールアドレス
教員ID
教員名
教員の専門分野
コースID
コース名
コース担当教員ID
成績ID
学生ID（成績を受けた学生）
コースID（成績を受けたコース）
成績（点数または評価）
学期
学年
学生のコース登録ID
コース登録日
タスク:
第1正規形への正規化**: 各列に単一の値が含まれ、各行が一意に識別可能になるようにデータを整理します。
第2正規形への正規化**: 全ての非キー属性が、主キー全体に関数従属するようにデータを整理します。
第3正規形への正規化**: 非キー属性間の推移的関数従属を排除します。
アプローチ:
データ項目間の関連性を特定し、それぞれのテーブルを作成します。
各テーブルでのキーを定義し、データの冗長性を排除します。
学生、教員、コース、成績など、異なるエンティティを識別し、それぞれのテーブルを作成することがポイントです。
このシナリオを用いて、実際にどのようにデータを分割し、関連付けるかを考えながら正規化のプロセスを進めてみてください。各ステップでの決定理由も重要ですので、それらを考慮しながら進めることをお勧めします。

フローその４：自分で正規化をしてみる

あとでChatGPTに投げることになるので、以下のテンプレを構成してMacのメモ帳なのでテンプレの中にテーブルを構成していきます。
第１正規形から第３正規形まで順を追って関数従属性を洗い出してはテーブルを設計していきます。

以下は回答で、正規化を①〜③で第３正規化まで段階を分けています。また、「・」は私の主張です。
以下、添削をお願いいたします。

①（第1正規形への正規化: 各列に単一の値が含まれ、各行が一意に識別可能になるようにデータを整理します）

・「教員の専門分野」はスカラ値を構成しない可能性が高いため「教員分野テーブル」を先に構成します。
「教員分野テーブル」：教員分野ID, 教員ID, 分野ID
・「学生のコース登録ID」はスカラ値を構成しない可能性が高いため「コース登録テーブル」を先に構成します。
「コース登録テーブル」：コース登録ID, 学生ID, コースID, コース登録日
・「成績（点数または評価）」はスカラ値を構成しない可能性が高いため「コース成績テーブル」を先に構成します。
「コース成績テーブル」：コース成績ID, 成績（点数または評価）,  学期, 学年

・その他のデータセットはスカラ値を満たしていると考えられます。


②（第2正規形への正規化: 全ての非キー属性が、主キー全体に関数従属するようにデータを整理します（完全関数従属）。）
・
「学生テーブル」:				 			学生ID, 学生名, 学生のメールアドレス, 学生の住所,
「教員テーブル」:				 			教員ID, 教員名
「教員分野テーブル」：						教員分野ID, 教員ID, 分野ID
「分野テーブル」：							分野ID, 分野名
「コーステーブル」：						コースID, コース名, 教員ID
「コース登録テーブル」：					コース登録ID, 学生ID, コースID, コース登録日
「コース成績テーブル」：					コース成績ID, 学生ID, コースID, 成績（点数または評価）, 学期, 学年


また、第１正規形では、学生の成績がスカラ値を構成していないので「コース成績テーブル」を構成した通り、第２正規形では「学生ID」と「コースID」の従属性を見出した。
また、{コース成績ID}→ {コース登録ID}→{学生ID, コースID} という従属性があるので、「学生ID」ないし「コースID」を第３正規形では「コース登録テーブル」に完全従属するように変える。これによって、各「学生ID」が「コースID」ごとの「成績」を、コース成績テーブルによってレコードを保持することができる。


③（第3正規形への正規化: 非キー属性間の推移的関数従属を排除します）
・
「学生テーブル」:				 			学生ID, 学生名, 学生のメールアドレス, 学生の住所,
「教員テーブル」:				 			教員ID, 教員名
「教員分野テーブル」：						教員分野ID, 教員ID, 分野ID
「分野テーブル」：							分野ID, 分野名
「コーステーブル」：						コースID, コース名, 教員ID
「コース登録テーブル」：					コース登録ID, 学生ID, コースID, コース登録日
「コース成績テーブル」：					コース成績ID, 成績（点数または評価）, 学期, 学年, コース登録ID

備考
・この第３正規化により、「コース成績テーブル」にある{コース成績ID}→ {コース登録ID}→{学生ID, コースID} という従属性を「コース登録ID」の外部キーに置き換えて推移的関数従属を削減していると考えられる。

フローその5：　ChatGPTから添削を受ける

実際に上のテンプレから正規化が見直しを含めて済んだら、ChatGPTに投げましょう。
全体的に誤植によって構成カラムに矛盾があったりするとすぐに指摘してくれますし、全体的に筋が通っていて問題がなければ同じことしか言ってきません。

あなたの提案した正規化のアプローチを検討し、いくつかの点を指摘します。
第1正規形（1NF）
あなたの主張：「教員の専門分野」、「学生のコース登録ID」、および「成績（点数または評価）」を分離する。
添削：適切なアプローチです。これにより、各列がスカラ値を持ち、各行が一意に識別可能になります。
第2正規形（2NF）
あなたの主張：各エンティティに関連するデータを適切なテーブルに分割する。
添削：基本的には適切ですが、「教員分野テーブル」の「教員分野ID」は必要ないかもしれません。通常、教員IDと分野IDの組み合わせで十分です。
第3正規形（3NF）
あなたの主張：「コース成績テーブル」に「コース登録ID」を含めて、推移的関数従属を削減する。
添削：これは適切なアプローチです。「コース成績テーブル」において「コース登録ID」を使用することで、成績がどの学生のどのコース登録に関連しているかを明確にします。
追加の考慮点
「コース登録テーブル」に「コース登録日」を含めることは適切ですが、このテーブルに「学生ID」と「コースID」の両方を含めることで、どの学生がどのコースに登録しているかを特定できます。
「コース成績テーブル」で「コース登録ID」を使用することで、どの学生がどのコースでどの成績を受けたかを正確に追跡できます。
全体的に、あなたのアプローチはデータの整合性と効率性を高めるために必要な正規化の基本を反映しています。各テーブルのキーと属性を適切に設計することで、データベースの有用性が向上します。

まとめ

DB設計は座学だとインプットに傾きがちですが、客観的な視点でアウトプットができるのはChatGPTの利点だと思うのでガンガン正規化して行きましょう。
また懸念事項等、問題点がありましたら、ぜひご指摘ください！