【授業レポート】システム開発（2年） 第35週目 〜データベース設計（ER図）と永続化の落とし込み〜
第35週目は、先週作成したクラス図を元にデータベース（永続化）設計へ落とし込む実習を行いました。クラスの属性をどのようなテーブルにし、どのように関係（リレーション）を張るかを考えることで、実際に動くシステムの骨格を作る重要な回です。
■ 先生の導入：「メモリの中の“箱”をディスクに保存する設計」
田中先生：「クラスで定義したデータは、実際にはテーブルとレコードになります。UMLの属性をそのままテーブルに落とすだけでなく、正規化・インデックス・トランザクションなど運用を意識して設計することが大事です。」
■ 今日のポイント（要点）
- ER図でエンティティ（テーブル）と関係を定義する。
- 正規化（1NF〜3NF）を意識して冗長性を減らす。
- 主キー／外部キーの設定と参照整合性（FK制約）。
- インデックス設計（検索頻度に応じた適所のインデックス）。
- 簡単なマイグレーション（CREATE TABLE / ALTER）とサンプルデータで挙動確認。
- トランザクション・バックアップ・セキュリティ（SQLインジェクション対策）まで運用視点で検討。
■ 実習①：UML → ER図 に落とすワーク
班ごとに、クラス図の属性をどのテーブルに配置するかを議論。ポイントは次の通り。
- エンティティ化の判断：クラスが独立したライフサイクルや複数参照されるなら別テーブル（例：Book, Member, Loan）。
- 多対多の扱い：多対多（Member と Book の貸出）は中間テーブル（Loan）で表現する。
- 属性の正規化：住所や複数電話番号は別テーブルに分割するか検討する（繰り返し属性の排除）。
例：図書館システムの簡易ER（要旨）
members（id PK, name, member_id, created_at）books（id PK, title, author, is_reference, is_available）loans（id PK, member_id FK→members.id, book_id FK→books.id, borrowed_at, due_date, returned_at）
生徒の気づき：「Loan を作ると、貸出履歴や延滞チェックが自然に扱える！」
■ 実習②：正規化の確認（1NF→3NFのチェック）
班ごとにテーブル設計が正規化されているかを確認。
- 1NF：列は原子値で、繰り返しカラムを持たない（例：phone1, phone2は避ける）。
- 2NF：部分関数従属を排除（複合PKを使う場合の注意）。
- 3NF：非キー属性はキーに対してのみ依存すること（派生データは別テーブル／ビューへ）。
例：もし books に library_branch_name を入れているなら、branches テーブルに分ける方が適切、という指摘が出ました。
■ 実習③：SQLで作って動かしてみる（マイグレーションとサンプル）
授業では簡単な SQL を書き、学習用DBに適用して動作確認を行いました（SQLite / PostgreSQL の学習環境想定）。授業で使った例：
-- 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
);
その後、サンプルデータを挿入して JOIN クエリで動作確認。
INSERT INTO members (member_id, name) VALUES ('M001', '田中');
INSERT INTO books (title, author) VALUES ('ハリーポッター', 'J.K.ローリング');
-- 貸出処理（トランザクションで）
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;
生徒の反応：「トランザクションで貸出と在庫更新を一緒にやると、途中で失敗しても整合性が保たれる！」
■ 実習④：インデックスとパフォーマンスの基礎
テーブルを作ってデータ量が増えた場合の検索を想定し、どのカラムにインデックスを張るかを議論。
- 検索頻度の高い
books.titleやmembers.member_idにインデックスを検討。 - JOIN に使う
loans.book_id/loans.member_idはインデックス効果が高い。 - 一方、更新頻度が高いカラムや低選択性カラム（真偽値）には不要なインデックスは避ける。
簡単なSQL例：
CREATE INDEX idx_books_title ON books(title);
CREATE INDEX idx_loans_member_id ON loans(member_id);
生徒の気づき：「インデックスは速くするけど、書き込みが遅くなるトレードオフがあるんだ」
■ 実習⑤：運用面の考慮（バックアップ・マイグレーション・セキュリティ）
- バックアップ：定期ダンプ（pg_dump 等）やポイントインタイムリカバリの概念。
- マイグレーション：スキーマ変更は段階的に（ALTER の注意、ロールバック案の準備）。
- セキュリティ：プレースホルダ付きクエリで SQL インジェクションを防ぐ（例：
cursor.execute("SELECT ... WHERE id=%s", (id,))）。 - 権限管理：DBユーザーは最小権限の原則（読み取り専用ユーザーなど）。
田中先生：「設計が良くても、運用でデータを失ったり漏洩したりしたら意味がありません。永続化は“最後の責任”でもあります。」
■ 先生のひとこと
「データ設計は、アプリの信頼性と性能を大きく左右します。クラス図からER図へ、ER図からDDLへ、そして運用へ――この流れを意識して設計すると、実装もテストもスムーズになります。次週はER図を確定し、実際のマイグレーションスクリプトを班で作成します。」
■ 宿題（次回に向けて）
- 班の**最終ER図（PNG/PDF）**と、今回作成した CREATE TABLE（DDL）スクリプト を提出。
- 各テーブルについて 想定インデックス案 を3箇所以上書き、その理由を各20〜60字で説明。
- 「データ損失が起きたときのロールバック手順」を1ページ以内で作成（バックアップ→復元の流れ）。
第35週目は、設計を永続化のレベルまで落とし込み、実践的なSQLと運用の知見を得る週でした。生徒たちは「設計＝約束」「DBは慎重に扱うべき資産」という認識を深め、次の実装フェーズに向けて準備を整えました。