【授業レポート】システム開発（2年）第35週：ER図からSQLへ、データベース設計と永続化を学ぶ

第35週は、前週に整理したUMLのクラス図を、データベースで保存できる形へ変換する実習です。
クラスの属性をテーブルの列に置き換えるだけでなく、データ同士の関係、重複を抑える方法、検索性能、障害時の復旧まで検討しました。

永続化とは、アプリケーションを終了しても必要なデータが残るように保存することです。
今回は図書館システムを例に、クラス図からER図、SQL、運用へ進む流れを確認しました。

クラス図をER図へ変換する目的

ER図は、保存する対象と、その対象同士の関係を整理する図です。
授業では、クラス図で表した構造を、次のようにデータベースの要素へ対応させました。

田中先生：「クラスで定義したデータは、実際にはテーブルとレコードになります。UMLの属性をそのままテーブルに落とすだけでなく、正規化、インデックス、トランザクションなど運用を意識して設計することが大事です。」

図書館システムの三つのテーブル

班ごとのワークでは、クラスが独立した役割を持つか、別の場所から繰り返し参照されるかを考え、テーブルを分けました。
中心となったのは次の三つです。

• members：会員ID、氏名、登録日時を保存する。
• books：書名、著者名、禁帯出かどうか、貸出可能かどうかを保存する。
• loans：誰がどの本を借りたか、貸出日、返却期限、返却日を保存する。

会員は複数の本を借りられ、一冊の本も時期を変えて複数の会員に貸し出されます。
そこで、会員と本を直接結ぶのではなく、貸出という出来事をloansに記録しました。
この中間テーブルがあることで、現在の貸出状況だけでなく、返却済みの履歴や返却期限も同じ構造で扱えます。

正規化で重複と更新漏れを抑える

正規化は、同じ情報を何度も保存して食い違いが起きる状況を減らすために、テーブルの役割を整理する考え方です。
授業では第1正規形から第3正規形までを、次の順序で点検しました。

1. 第1正規形（1NF）：一つの列に複数の値を詰め込まず、phone1、phone2のような繰り返し列も避ける。
2. 第2正規形（2NF）：複数の列で主キーを作る場合、主キーの一部だけで決まる属性を別のテーブルへ移す。
3. 第3正規形（3NF）：主キー以外の属性によって決まる属性を分け、変更箇所が複数に散らばらないようにする。

たとえば、複数の本に同じ分館名を直接保存すると、分館名を変更するときに該当する本をすべて直す必要があります。
実習では、library_branch_nameをbooksに持たせず、branchesという別テーブルに分ける案を検討しました。

ER図をSQLへ落とし込む

ER図でテーブルと関係を決めた後は、DDL（データ定義言語）で構造を作りました。
次は授業で使用した例です。

-- members テーブル
CREATE TABLE members (
  id SERIAL PRIMARY KEY,
  member_id VARCHAR(20) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- books テーブル
CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author VARCHAR(100),
  is_reference BOOLEAN DEFAULT FALSE,
  is_available BOOLEAN DEFAULT TRUE
);

-- loans テーブル
CREATE TABLE loans (
  id SERIAL PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id),
  book_id INTEGER NOT NULL REFERENCES books(id),
  borrowed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  due_date DATE,
  returned_at TIMESTAMP NULL
);

制約には、それぞれ異なる役割があります。

• 主キー（PRIMARY KEY）：各レコードを区別する識別子にする。
• 一意制約（UNIQUE）：同じ会員IDが重複して登録されることを防ぐ。
• NOT NULL制約：会員名や書名など、必要な値の未入力を防ぐ。
• 外部キー（REFERENCES）：存在する会員と本だけを貸出記録から参照できるようにする。

サンプルデータで関係を確認する

テーブルを作っただけでは、関係が意図どおりに機能するか判断できません。
サンプルデータを登録し、貸出処理とJOINによる検索を試しました。

INSERT INTO members (member_id, name) VALUES ('M001', '田中');
INSERT INTO books (title, author) VALUES ('ハリーポッター', 'J.K.ローリング');

-- 会員IDと書籍IDがともに1である場合の貸出処理
BEGIN;
INSERT INTO loans (member_id, book_id, due_date) VALUES (1, 1, '2025-12-01');
UPDATE books SET is_available = FALSE WHERE id = 1;
COMMIT;

-- 貸出中の本を取得
SELECT b.title, m.name, l.borrowed_at, l.due_date
FROM loans l
JOIN books b ON l.book_id = b.id
JOIN members m ON l.member_id = m.id
WHERE l.returned_at IS NULL;

トランザクションは、複数の更新を一つの処理単位として扱う仕組みです。
この例では、貸出履歴の追加と本の貸出状態の更新を同じ単位にまとめています。
途中で処理に失敗した場合に変更を取り消せるよう設計することで、片方だけが反映された状態を避けやすくなります。

インデックスは検索と更新の両方から考える

インデックスは、検索対象を見つけやすくするための索引です。
授業では、検索条件やJOINに頻繁に使う列を候補にしました。

CREATE INDEX idx_books_title ON books(title);
CREATE INDEX idx_loans_member_id ON loans(member_id);

• books.title：書名による検索が多い場合に検討する。
• members.member_id：会員IDで会員を探す処理に使う。
• loans.member_idとloans.book_id：貸出情報と会員、本を結合する処理で使う。

インデックスを増やすと検索には役立ちますが、データの追加や更新では索引も更新する必要があります。
そのため、すべての列に付けるのではなく、実際の検索頻度と更新頻度を基に選ぶという判断を学びました。

運用まで含めてデータを守る

テーブルが正しく作れても、変更手順や復旧方法が決まっていなければ、継続して安全に運用できません。
実習の最後に、次の四点を確認しました。

• バックアップ：定期ダンプやポイントインタイムリカバリの考え方を知り、復元までを手順として考える。
• マイグレーション：スキーマ変更を段階的に適用し、問題が起きた場合の戻し方も準備する。詳しい流れはDBマイグレーションの実務ガイドでも紹介しています。
• SQLインジェクション対策：値をSQL文字列へ直接埋め込まず、プレースホルダ付きクエリを使う。
• 権限管理：読み取り専用など、役割に必要な範囲へDBユーザーの権限を絞る。

田中先生：「設計が良くても、運用でデータを失ったり漏洩したりしたら意味がありません。永続化は『最後の責任』でもあります。」

第35週の到達点と宿題

今回の実習では、クラス図の構造をER図で整理し、DDLで実際のテーブルへ変換しました。
正規化、キー制約、トランザクション、インデックスは別々の知識ではなく、データの一貫性と扱いやすさを支える設計判断としてつながっています。

次回に向けた宿題は次の三点です。

1. 班の最終ER図をPNGまたはPDFで作成し、CREATE TABLE文をまとめたDDLスクリプトとともに提出する。
2. 各テーブルのインデックス候補を三つ以上挙げ、それぞれの理由を20字から60字で説明する。
3. データ損失が起きた場合を想定し、バックアップから復元までの手順を1ページ以内に整理する。

次週はER図を確定し、マイグレーション実装とDAO設計へ進みます。