🎯 STEP 12: 正規化総合演習
実践的な演習問題で正規化の技術を完全にマスターしよう！
📋 このステップで学ぶこと
- 実践的な正規化プロジェクトに挑戦
- 非正規形から第3正規形まで段階的に正規化
- 複雑なビジネスルールをデータベース設計に落とし込む
- 実務で使えるテーブル設計スキルを習得
学習時間の目安： 5時間 ｜ 前提知識： STEP 7-11（正規化の基礎から応用）を理解していること
🎯 1. このステップの進め方
STEP 11からの続き：正規化編の総仕上げ
STEP 7〜11では、正規化の理論と各正規形（1NF、2NF、3NF、BCNF）を学びました。このSTEP 12では、学んだ知識を総合的に活用して、実務に近いシナリオで正規化に取り組みます。
4つの総合演習を通じて、正規化のスキルを実践レベルに引き上げましょう。
📋 演習の構成
| 演習 | テーマ | 目安時間 | 難易度 |
|---|---|---|---|
| 演習1 | 学生成績管理システム | 90分 | 中級 |
| 演習2 | Excelの販売実績表をDB化 | 120分 | 上級 |
| 演習3 | 集計テーブルの非正規化判断 | 90分 | 中級 |
| 演習4 | 病院予約システムの正規化 | 60分 | 上級 |
💡 演習の取り組み方
- まず自分で考えて設計してみる（解答を見る前に）
- ER図を紙に書いてみる（視覚化が重要）
- 各テーブルの主キー、外部キーを明確にする
- 解答と比較して、なぜその設計になったかを理解する
- 時間をかけてじっくり取り組む（急がない）
📝 演習1：学生成績管理システム（90分）
演習 1
中級
学生成績管理システムのデータベース設計
📋 要件定義
ある大学の成績管理システムを設計します。以下の情報を管理する必要があります：
- 学生情報（学生ID、氏名、学科、入学年度）
- 科目情報（科目コード、科目名、単位数、担当教員）
- 教員情報（教員ID、教員名、所属学部）
- 履修情報（学生がどの科目を履修し、どんな成績を取ったか）
- 学科情報（学科コード、学科名、所属学部）
- 1人の学生は1つの学科に所属
- 1つの科目は1人の教員が担当（複数教員での担当はない）
- 1人の学生は複数の科目を履修可能
- 1つの科目を複数の学生が履修可能
- 成績は「優」「良」「可」「不可」の4段階
❌ 現在のExcelでの管理方法（非正規形）
あなたのタスク：
学生成績一覧
—————————————————————————————–
学生ID | 学生名 | 学科 | 学部 | 履修科目 | 科目コード | 担当教員 | 教員所属 | 成績
S001 | 山田太郎 | 情報工学 | 工学部 | データベース,数学 | C001,C002 | 佐藤先生,鈴木先生 | 工学部,理学部 | 優,良
S002 | 佐藤花子 | 電気工学 | 工学部 | 数学 | C002 | 鈴木先生 | 理学部 | 優
S003 | 鈴木一郎 | 情報工学 | 工学部 | データベース | C001 | 佐藤先生 | 工学部 | 良
- 上記のExcel管理の問題点を3つ以上指摘してください
- 第1正規形、第2正規形、第3正規形の順に段階的に正規化してください
- 最終的なテーブル設計（テーブル名、カラム名、主キー、外部キー）を示してください
- 主要なテーブルについてCREATE TABLE文を書いてください
- 「学生ID: S001 の全履修科目と成績を取得するSQL」を書いてください
【解答】
1. Excelでの管理の問題点：
- 第1正規形違反：履修科目、科目コード、担当教員、教員所属、成績に繰り返し項目がある（カンマ区切り）
- データの重複：山田太郎と鈴木一郎の学科情報「情報工学、工学部」が重複している
- 更新異常：「情報工学科」の名前を変更する場合、全ての学生レコードを更新する必要がある
- 挿入異常：新しい学科を登録したいが、所属学生がいないと登録できない
- 削除異常：佐藤花子が退学すると、「電気工学科」の情報も失われる可能性がある
- 推移的関数従属性：学生ID → 学科 → 学部という推移的な依存関係がある
2. 段階的な正規化：
📌 第1正規形への変換
繰り返し項目を排除します。
student_records（第1正規形）
—————————————————————————————–
学生ID | 学生名 | 学科 | 学部 | 科目コード | 科目名 | 担当教員 | 教員所属 | 成績
S001 | 山田太郎 | 情報工学 | 工学部 | C001 | データベース | 佐藤先生 | 工学部 | 優
S001 | 山田太郎 | 情報工学 | 工学部 | C002 | 数学 | 鈴木先生 | 理学部 | 良
S002 | 佐藤花子 | 電気工学 | 工学部 | C002 | 数学 | 鈴木先生 | 理学部 | 優
S003 | 鈴木一郎 | 情報工学 | 工学部 | C001 | データベース | 佐藤先生 | 工学部 | 良
📌 第2正規形への変換
部分関数従属性を排除します。主キーは（学生ID, 科目コード）です。
students（学生テーブル）
—————————————
学生ID | 学生名 | 学科 | 学部
S001 | 山田太郎 | 情報工学 | 工学部
S002 | 佐藤花子 | 電気工学 | 工学部
S003 | 鈴木一郎 | 情報工学 | 工学部
courses（科目テーブル）
—————————————
科目コード | 科目名 | 担当教員 | 教員所属
C001 | データベース | 佐藤先生 | 工学部
C002 | 数学 | 鈴木先生 | 理学部
enrollments（履修テーブル）
—————————————
学生ID | 科目コード | 成績
S001 | C001 | 優
S001 | C002 | 良
S002 | C002 | 優
S003 | C001 | 良
📌 第3正規形への変換
推移的関数従属性を排除します。
departments（学科テーブル）
—————————————
学科コード | 学科名 | 学部コード
D001 | 情報工学 | F001
D002 | 電気工学 | F001
faculties（学部テーブル）
—————————————
学部コード | 学部名
F001 | 工学部
F002 | 理学部
students（学生テーブル）
—————————————
学生ID | 学生名 | 学科コード | 入学年度
S001 | 山田太郎 | D001 | 2023
S002 | 佐藤花子 | D002 | 2023
S003 | 鈴木一郎 | D001 | 2024
teachers（教員テーブル）
—————————————
教員ID | 教員名 | 学部コード
T001 | 佐藤先生 | F001
T002 | 鈴木先生 | F002
courses（科目テーブル）
—————————————
科目コード | 科目名 | 単位数 | 教員ID
C001 | データベース | 2 | T001
C002 | 数学 | 2 | T002
enrollments（履修テーブル）
—————————————
履修ID | 学生ID | 科目コード | 成績 | 履修年度
1 | S001 | C001 | 優 | 2024
2 | S001 | C002 | 良 | 2024
3 | S002 | C002 | 優 | 2024
4 | S003 | C001 | 良 | 2024
3. 最終的なテーブル設計：
- faculties（学部）：学部コード(PK), 学部名
- departments（学科）：学科コード(PK), 学科名, 学部コード(FK)
- students（学生）：学生ID(PK), 学生名, 学科コード(FK), 入学年度
- teachers（教員）：教員ID(PK), 教員名, 学部コード(FK)
- courses（科目）：科目コード(PK), 科目名, 単位数, 教員ID(FK)
- enrollments（履修）：履修ID(PK), 学生ID(FK), 科目コード(FK), 成績, 履修年度
4. CREATE TABLE文：
— 学部テーブル
CREATE TABLE faculties (
faculty_code VARCHAR(10) PRIMARY KEY,
faculty_name VARCHAR(100) NOT NULL
);
— 学科テーブル
CREATE TABLE departments (
department_code VARCHAR(10) PRIMARY KEY,
department_name VARCHAR(100) NOT NULL,
faculty_code VARCHAR(10) NOT NULL,
FOREIGN KEY (faculty_code) REFERENCES faculties(faculty_code)
);
— 学生テーブル
CREATE TABLE students (
student_id VARCHAR(10) PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
department_code VARCHAR(10) NOT NULL,
enrollment_year INT NOT NULL,
FOREIGN KEY (department_code) REFERENCES departments(department_code)
);
— 教員テーブル
CREATE TABLE teachers (
teacher_id VARCHAR(10) PRIMARY KEY,
teacher_name VARCHAR(100) NOT NULL,
faculty_code VARCHAR(10) NOT NULL,
FOREIGN KEY (faculty_code) REFERENCES faculties(faculty_code)
);
— 科目テーブル
CREATE TABLE courses (
course_code VARCHAR(10) PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
credits INT NOT NULL,
teacher_id VARCHAR(10) NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);
— 履修テーブル
CREATE TABLE enrollments (
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id VARCHAR(10) NOT NULL,
course_code VARCHAR(10) NOT NULL,
grade VARCHAR(10) NOT NULL,
enrollment_year INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_code) REFERENCES courses(course_code),
UNIQUE KEY (student_id, course_code, enrollment_year)
);
5. 学生ID: S001の全履修科目と成績を取得するSQL：
SELECT
s.student_id,
s.student_name,
c.course_code,
c.course_name,
c.credits,
t.teacher_name,
e.grade,
e.enrollment_year
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_code = c.course_code
JOIN teachers t ON c.teacher_id = t.teacher_id
WHERE s.student_id = ‘S001’
ORDER BY e.enrollment_year, c.course_code;
実行結果：
student_id | student_name | course_code | course_name | credits | teacher_name | grade | enrollment_year
S001 | 山田太郎 | C001 | データベース | 2 | 佐藤先生 | 優 | 2024
S001 | 山田太郎 | C002 | 数学 | 2 | 鈴木先生 | 良 | 2024
✅ 設計のポイント：
- 学部 → 学科 → 学生 という階層構造を明確に分離
- 教員は学部に所属（科目ではなく）
- 履修テーブルに履修IDを追加し、同じ科目の再履修に対応
- UNIQUE制約で「同一年度の同じ科目の重複履修」を防止
- すべてのテーブルが第3正規形を満たしている
📝 演習2：Excelの販売実績表をDB化（120分）
演習 2
上級
営業部のExcel販売実績表をデータベース化
📋 シナリオ
ある会社の営業部では、Excelで販売実績を管理しています。しかし、データの重複や入力ミスが多発しており、データベース化することになりました。
❌ 現在のExcel管理（非正規形）
販売実績表
———————————————————————————————
売上ID | 売上日 | 営業担当 | 所属支店 | 支店住所 | 顧客名 | 顧客住所 | 商品名,数量,単価 | 合計金額
1 | 2025-01-15 | 山田太郎 | 東京支店 | 東京都… | A商事 | 東京都… | ノートPC:2:80000,マウス:5:2000 | 170000
2 | 2025-01-16 | 山田太郎 | 東京支店 | 東京都… | B商店 | 大阪府… | キーボード:3:5000 | 15000
3 | 2025-01-17 | 佐藤花子 | 大阪支店 | 大阪府… | A商事 | 東京都… | ノートPC:1:80000 | 80000
4 | 2025-01-18 | 山田太郎 | 東京支店 | 東京都… | A商事 | 東京都… | マウス:10:2000,USBメモリ:5:1000 | 25000
現在の問題点：
- 商品情報がカンマ区切りで複数入っている
- 営業担当の所属支店情報が重複している
- 顧客情報（顧客名、住所）が重複している
- 支店情報（支店名、住所）が重複している
- 商品の単価が売上ごとに記録されており、価格改定時の整合性が取れない
📋 追加要件
あなたのタスク：
- 売上データは後から変更されない（確定したデータ）
- 商品マスタは別途管理し、現在の価格を保持
- 売上時の単価は、その時点の価格を記録（価格改定の影響を受けない）
- 営業担当者の異動（支店間の移動）にも対応できること
- 月次・年次の売上集計レポートを高速に作成したい
- 上記のExcel管理の全ての問題点を指摘してください
- 第3正規形までテーブルを設計してください
- 月次売上集計レポート用に非正規化テーブルを設計し、その理由を説明してください
- 主要なテーブルのCREATE TABLE文を書いてください
- 「2025年1月の営業担当者別売上合計を取得するSQL」を2パターン書いてください：
(a) 正規化されたテーブルから集計
(b) 非正規化された集計テーブルから取得
【解答】
1. Excelでの管理の問題点：
- 第1正規形違反：商品名、数量、単価が繰り返し項目（カンマ区切り）
- 推移的関数従属性：売上ID → 営業担当 → 所属支店、支店住所
- 推移的関数従属性：売上ID → 顧客名 → 顧客住所
- データの重複：「山田太郎、東京支店、東京都…」が複数行に重複
- データの重複：「A商事、東京都…」が複数行に重複
- 更新異常：営業担当が異動した場合、過去の全売上データを更新してしまうと履歴が失われる
- 一貫性の問題：商品単価が売上ごとに記録され、商品マスタとの整合性が保証されない
2. 第3正規形のテーブル設計：
— 支店テーブル
branches
—————————————
支店コード | 支店名 | 支店住所
B001 | 東京支店 | 東京都千代田区…
B002 | 大阪支店 | 大阪府大阪市…
— 営業担当者テーブル
sales_staff
—————————————
営業担当ID | 営業担当名 | 現在の支店コード
E001 | 山田太郎 | B001
E002 | 佐藤花子 | B002
— 顧客テーブル
customers
—————————————
顧客コード | 顧客名 | 顧客住所
C001 | A商事 | 東京都港区…
C002 | B商店 | 大阪府大阪市…
— 商品マスタテーブル
products
—————————————
商品コード | 商品名 | 現在価格
P001 | ノートPC | 80000
P002 | マウス | 2000
P003 | キーボード | 5000
P004 | USBメモリ | 1000
— 売上ヘッダーテーブル
sales_headers
—————————————
売上ID | 売上日 | 営業担当ID | 支店コード | 顧客コード
1 | 2025-01-15 | E001 | B001 | C001
2 | 2025-01-16 | E001 | B001 | C002
3 | 2025-01-17 | E002 | B002 | C001
4 | 2025-01-18 | E001 | B001 | C001
— 売上明細テーブル
sales_details
—————————————
売上明細ID | 売上ID | 商品コード | 数量 | 売上時単価
1 | 1 | P001 | 2 | 80000
2 | 1 | P002 | 5 | 2000
3 | 2 | P003 | 3 | 5000
4 | 3 | P001 | 1 | 80000
5 | 4 | P002 | 10 | 2000
6 | 4 | P004 | 5 | 1000
3. 非正規化テーブルの設計と理由：
📊 月次売上集計テーブル（非正規化）
monthly_sales_summary
—————————————
年月 | 営業担当ID | 営業担当名 | 支店コード | 支店名 | 売上件数 | 売上金額合計
202501 | E001 | 山田太郎 | B001 | 東京支店 | 3 | 210000
202501 | E002 | 佐藤花子 | B002 | 大阪支店 | 1 | 80000
非正規化の理由：
- ✅ 読み取り頻度が高い：月次レポートは経営会議などで頻繁に参照される
- ✅ 更新頻度は低い：月次集計は月1回のバッチ処理で更新
- ✅ JOIN不要：複雑なJOINと集計が不要になり、レポート表示が超高速化
- ✅ 過去データの保持：営業担当の異動があっても、その月の実績として記録される
- ⚠️ 元の正規化されたテーブルは維持し、真実の情報源として保持
- ⚠️ このテーブルは月次バッチで sales_headers と sales_details から集計して生成
4. CREATE TABLE文：
— 支店テーブル
CREATE TABLE branches (
branch_code VARCHAR(10) PRIMARY KEY,
branch_name VARCHAR(100) NOT NULL,
branch_address VARCHAR(200) NOT NULL
);
— 営業担当者テーブル
CREATE TABLE sales_staff (
staff_id VARCHAR(10) PRIMARY KEY,
staff_name VARCHAR(100) NOT NULL,
current_branch_code VARCHAR(10) NOT NULL,
FOREIGN KEY (current_branch_code) REFERENCES branches(branch_code)
);
— 顧客テーブル
CREATE TABLE customers (
customer_code VARCHAR(10) PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
customer_address VARCHAR(200) NOT NULL
);
— 商品マスタテーブル
CREATE TABLE products (
product_code VARCHAR(10) PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
current_price DECIMAL(10, 2) NOT NULL
);
— 売上ヘッダーテーブル
CREATE TABLE sales_headers (
sale_id INT AUTO_INCREMENT PRIMARY KEY,
sale_date DATE NOT NULL,
staff_id VARCHAR(10) NOT NULL,
branch_code VARCHAR(10) NOT NULL,
customer_code VARCHAR(10) NOT NULL,
FOREIGN KEY (staff_id) REFERENCES sales_staff(staff_id),
FOREIGN KEY (branch_code) REFERENCES branches(branch_code),
FOREIGN KEY (customer_code) REFERENCES customers(customer_code),
INDEX idx_sale_date (sale_date),
INDEX idx_staff_id (staff_id)
);
— 売上明細テーブル
CREATE TABLE sales_details (
detail_id INT AUTO_INCREMENT PRIMARY KEY,
sale_id INT NOT NULL,
product_code VARCHAR(10) NOT NULL,
quantity INT NOT NULL,
unit_price_at_sale DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (sale_id) REFERENCES sales_headers(sale_id),
FOREIGN KEY (product_code) REFERENCES products(product_code)
);
— 月次売上集計テーブル（非正規化）
CREATE TABLE monthly_sales_summary (
year_month VARCHAR(6) NOT NULL,
staff_id VARCHAR(10) NOT NULL,
staff_name VARCHAR(100) NOT NULL,
branch_code VARCHAR(10) NOT NULL,
branch_name VARCHAR(100) NOT NULL,
sales_count INT NOT NULL,
total_amount DECIMAL(15, 2) NOT NULL,
PRIMARY KEY (year_month, staff_id),
INDEX idx_year_month (year_month)
);
5. 2025年1月の営業担当者別売上合計を取得するSQL：
(a) 正規化されたテーブルから集計：
SELECT
ss.staff_id,
ss.staff_name,
b.branch_name,
COUNT(DISTINCT sh.sale_id) AS sales_count,
SUM(sd.quantity * sd.unit_price_at_sale) AS total_amount
FROM sales_headers sh
JOIN sales_details sd ON sh.sale_id = sd.sale_id
JOIN sales_staff ss ON sh.staff_id = ss.staff_id
JOIN branches b ON sh.branch_code = b.branch_code
WHERE sh.sale_date >= ‘2025-01-01’
AND sh.sale_date < '2025-02-01'
GROUP BY ss.staff_id, ss.staff_name, b.branch_name
ORDER BY total_amount DESC;
(b) 非正規化された集計テーブルから取得：
SELECT
staff_id,
staff_name,
branch_name,
sales_count,
total_amount
FROM monthly_sales_summary
WHERE year_month = ‘202501’
ORDER BY total_amount DESC;
実行結果（どちらも同じ）：
staff_id | staff_name | branch_name | sales_count | total_amount
E001 | 山田太郎 | 東京支店 | 3 | 210000.00
E002 | 佐藤花子 | 大阪支店 | 1 | 80000.00
✅ 設計のポイント：
- 売上時単価を記録：商品の価格改定があっても、過去の売上データは変わらない
- 売上時の支店を記録：営業担当が異動しても、その売上がどの支店の実績か明確
- ヘッダー・明細分離：1回の売上で複数商品に対応（1対多の関係）
- 戦略的非正規化：頻繁に参照されるレポート用に集計テーブルを用意
- インデックス設計：日付検索、営業担当検索を高速化
- データの整合性：元の正規化テーブルを真実の情報源として維持
📝 演習3：集計テーブルの非正規化判断（90分）
演習 3
中級
パフォーマンス最適化のための非正規化判断
📋 シナリオ
あるECサイトの管理画面では、ダッシュボードに以下の情報を表示しています：
- 本日の売上金額
- 今月の売上金額
- 商品カテゴリ別の売上トップ5
- 都道府県別の売上分布
現在、これらの情報は毎回リアルタイムで集計していますが、アクセスが集中すると画面表示に3秒以上かかる問題が発生しています。
📊 現在のテーブル構成（第3正規形）
あなたのタスク：
orders（注文テーブル）
—————————————
注文ID | 顧客ID | 注文日時 | 合計金額
order_details（注文明細テーブル）
—————————————
明細ID | 注文ID | 商品ID | 数量 | 単価
products（商品テーブル）
—————————————
商品ID | 商品名 | カテゴリID | 価格
categories（カテゴリテーブル）
—————————————
カテゴリID | カテゴリ名
customers（顧客テーブル）
—————————————
顧客ID | 顧客名 | 都道府県
データ規模：
- 注文：1日あたり約5,000件、総計200万件
- 注文明細：1日あたり約10,000行、総計400万行
- 商品：10,000件
- 顧客：50万人
- 現在のリアルタイム集計の問題点を分析してください
- 非正規化テーブル（集計テーブル）を設計してください：
– 日次売上集計テーブル
– カテゴリ別日次集計テーブル
– 都道府県別日次集計テーブル - 各集計テーブルの更新タイミングと方法を提案してください
- 集計テーブルを使った場合のメリットとデメリットを説明してください
- 「今月の売上金額を取得するSQL」を2パターン書いてください：
(a) 元テーブルから集計
(b) 集計テーブルから取得
【解答】
1. リアルタイム集計の問題点：
- パフォーマンス問題：400万行の order_details をJOINして集計するため、非常に時間がかかる
- データベース負荷：ダッシュボードのアクセスが集中すると、データベースサーバーの負荷が高まる
- ユーザー体験の悪化：画面表示に3秒以上かかり、ユーザーがストレスを感じる
- スケーラビリティの問題：データ量が増えるほど、さらに遅くなる
- 同じ集計の繰り返し：多くのユーザーが同じ期間の集計を見るのに、毎回計算している
2. 非正規化テーブルの設計：
— 日次売上集計テーブル
daily_sales_summary
—————————————
集計日 | 注文件数 | 売上金額 | 平均注文金額
2025-01-15 | 5123 | 15680000 | 3060
2025-01-16 | 4987 | 14920000 | 2992
2025-01-17 | 5345 | 16780000 | 3139
— カテゴリ別日次集計テーブル
daily_category_sales
—————————————
集計日 | カテゴリID | カテゴリ名 | 販売数量 | 売上金額
2025-01-15 | CAT001 | 電子機器 | 1234 | 8500000
2025-01-15 | CAT002 | 書籍 | 3456 | 2180000
2025-01-15 | CAT003 | 衣料品 | 2345 | 5000000
— 都道府県別日次集計テーブル
daily_prefecture_sales
—————————————
集計日 | 都道府県 | 注文件数 | 売上金額
2025-01-15 | 東京都 | 1567 | 4820000
2025-01-15 | 大阪府 | 987 | 3120000
2025-01-15 | 神奈川県 | 765 | 2340000
3. 更新タイミングと方法：
📅 更新戦略
戦略A：日次バッチ処理（推奨）
- タイミング：毎日深夜2時（アクセスが少ない時間帯）
- 処理内容：前日分のデータを集計して各テーブルに INSERT
- メリット：データベース負荷を分散、確実にデータを集計できる
- デメリット：当日分はリアルタイム集計が必要
戦略B：リアルタイム更新（注文確定時）
- タイミング：注文が確定するたびにトリガーで更新
- 処理内容：該当する集計テーブルの値を UPDATE（加算）
- メリット：常に最新のデータが集計テーブルに反映される
- デメリット：トランザクション処理が重くなる、複雑なロジックが必要
推奨アプローチ：ハイブリッド方式
- 過去データ：日次バッチで集計テーブルに格納
- 当日データ：リアルタイム集計（ただしキャッシュを活用）
- 表示時：集計テーブル（過去）+ リアルタイム集計（当日）を合算
4. メリットとデメリット：
| メリット | デメリット |
|---|---|
|
|
5. 今月の売上金額を取得するSQL：
(a) 元テーブルから集計（遅い）：
— 元テーブルから毎回集計（400万行をスキャン）
SELECT
SUM(od.quantity * od.unit_price) AS total_sales
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
WHERE o.order_date >= ‘2025-01-01’
AND o.order_date < '2025-02-01';
-- 実行時間: 約2.5秒
(b) 集計テーブルから取得（超高速）：
— 日次集計テーブルから取得（31行だけスキャン）
SELECT
SUM(sales_amount) AS total_sales
FROM daily_sales_summary
WHERE aggregation_date >= ‘2025-01-01’
AND aggregation_date < '2025-02-01';
-- 実行時間: 約0.01秒（250倍高速！）
✅ 設計のポイント：
- 目的に応じた粒度：日次、月次など、用途に応じた集計粒度を選択
- ハイブリッド方式：過去データは集計テーブル、当日はリアルタイム集計
- インデックス設計：集計日でのフィルタリングを高速化
- 監視とアラート：バッチ処理の失敗を検知する仕組み
- 元データの保持：正規化されたテーブルは真実の情報源として維持
- 段階的な導入：まず最も負荷の高い集計から非正規化
📝 演習4：病院予約システムの正規化（60分）
演習 4
上級
病院予約システムの総合設計
📋 要件定義
あなたのタスク：
総合病院の予約管理システムを設計します。以下の機能が必要です：
- 患者が医師を指定して診察予約
- 医師は複数の診療科を担当可能
- 予約の変更・キャンセル履歴の管理
- 診察結果と処方薬の記録
- 1つの予約枠は30分
- 1人の患者は同じ時間に複数予約できない
- 1人の医師は同じ時間に複数の患者を診察できない
- 医師は曜日・時間帯ごとに診察可能な診療科が異なる
- 予約は変更・キャンセル可能だが、履歴を残す
- 必要な全てのテーブルを第3正規形で設計してください
- 各テーブルの主キー、外部キーを明確にしてください
- ER図を描いてください（または文章で関係性を説明）
- 予約の変更・キャンセル履歴をどう管理するか設計してください
- 「2025年1月15日 14:00-14:30 の予約可能な医師一覧を取得するSQL」を書いてください
【解答】
1. テーブル設計（第3正規形）：
— 患者テーブル
patients
—————————————
患者ID (PK) | 患者名 | 生年月日 | 電話番号 | 住所
— 診療科テーブル
departments
—————————————
診療科コード (PK) | 診療科名
— 医師テーブル
doctors
—————————————
医師ID (PK) | 医師名 | 主な診療科コード (FK)
— 医師診療科テーブル（多対多）
doctor_departments
—————————————
医師ID (PK, FK) | 診療科コード (PK, FK) | 曜日 (PK) | 開始時刻 (PK) | 終了時刻
— 予約テーブル
appointments
—————————————
予約ID (PK) | 患者ID (FK) | 医師ID (FK) | 診療科コード (FK) | 予約日時 | 予約状態
— 予約履歴テーブル
appointment_history
—————————————
履歴ID (PK) | 予約ID (FK) | 変更日時 | 変更内容 | 変更理由 | 旧予約日時 | 新予約日時
— 診察記録テーブル
medical_records
—————————————
診察記録ID (PK) | 予約ID (FK) | 診察日時 | 診断内容 | 診察メモ
— 処方薬テーブル
prescriptions
—————————————
処方ID (PK) | 診察記録ID (FK) | 薬品コード (FK) | 数量 | 用法
— 薬品マスタ
medicines
—————————————
薬品コード (PK) | 薬品名 | 単位
2. 各テーブルの詳細設計（CREATE TABLE文）：
CREATE TABLE patients (
patient_id VARCHAR(10) PRIMARY KEY,
patient_name VARCHAR(100) NOT NULL,
birth_date DATE NOT NULL,
phone VARCHAR(20),
address VARCHAR(200)
);
CREATE TABLE departments (
department_code VARCHAR(10) PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);
CREATE TABLE doctors (
doctor_id VARCHAR(10) PRIMARY KEY,
doctor_name VARCHAR(100) NOT NULL,
main_department_code VARCHAR(10) NOT NULL,
FOREIGN KEY (main_department_code) REFERENCES departments(department_code)
);
CREATE TABLE doctor_departments (
doctor_id VARCHAR(10),
department_code VARCHAR(10),
day_of_week INT NOT NULL, — 0:日曜 ~ 6:土曜
start_time TIME NOT NULL,
end_time TIME NOT NULL,
PRIMARY KEY (doctor_id, department_code, day_of_week, start_time),
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
FOREIGN KEY (department_code) REFERENCES departments(department_code)
);
CREATE TABLE appointments (
appointment_id INT AUTO_INCREMENT PRIMARY KEY,
patient_id VARCHAR(10) NOT NULL,
doctor_id VARCHAR(10) NOT NULL,
department_code VARCHAR(10) NOT NULL,
appointment_datetime DATETIME NOT NULL,
status VARCHAR(20) NOT NULL, — ‘reserved’, ‘completed’, ‘cancelled’
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
FOREIGN KEY (department_code) REFERENCES departments(department_code),
INDEX idx_appointment_datetime (appointment_datetime),
INDEX idx_patient_id (patient_id),
INDEX idx_doctor_id (doctor_id),
UNIQUE KEY unique_patient_datetime (patient_id, appointment_datetime),
UNIQUE KEY unique_doctor_datetime (doctor_id, appointment_datetime)
);
CREATE TABLE appointment_history (
history_id INT AUTO_INCREMENT PRIMARY KEY,
appointment_id INT NOT NULL,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
change_type VARCHAR(20) NOT NULL, — ‘created’, ‘modified’, ‘cancelled’
change_reason TEXT,
old_datetime DATETIME,
new_datetime DATETIME,
FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);
CREATE TABLE medicines (
medicine_code VARCHAR(10) PRIMARY KEY,
medicine_name VARCHAR(100) NOT NULL,
unit VARCHAR(20) NOT NULL
);
CREATE TABLE medical_records (
record_id INT AUTO_INCREMENT PRIMARY KEY,
appointment_id INT NOT NULL,
examination_datetime DATETIME NOT NULL,
diagnosis TEXT NOT NULL,
notes TEXT,
FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);
CREATE TABLE prescriptions (
prescription_id INT AUTO_INCREMENT PRIMARY KEY,
record_id INT NOT NULL,
medicine_code VARCHAR(10) NOT NULL,
quantity DECIMAL(10, 2) NOT NULL,
dosage_instructions TEXT NOT NULL,
FOREIGN KEY (record_id) REFERENCES medical_records(record_id),
FOREIGN KEY (medicine_code) REFERENCES medicines(medicine_code)
);
3. ER図（関係性の説明）：
📊 エンティティ間の関係
- 患者 ↔ 予約：1対多（1人の患者は複数の予約を持つ）
- 医師 ↔ 予約：1対多（1人の医師は複数の予約を持つ）
- 診療科 ↔ 予約：1対多（1つの診療科で複数の予約）
- 医師 ↔ 診療科：多対多（1人の医師が複数の診療科を担当、1つの診療科に複数の医師）
- 予約 ↔ 予約履歴：1対多（1つの予約に複数の履歴）
- 予約 ↔ 診察記録：1対1（1つの予約に1つの診察記録）
- 診察記録 ↔ 処方薬：1対多（1つの診察で複数の薬を処方）
- 薬品マスタ ↔ 処方薬：1対多（1つの薬品が複数の処方に使われる）
4. 予約の変更・キャンセル履歴の管理：
📝 履歴管理の設計
設計方針：
- appointments テーブル：現在の予約状態のみを保持
- appointment_history テーブル：全ての変更履歴を記録
- トリガーまたはアプリケーション側：予約の作成・変更・キャンセル時に自動的に履歴を記録
履歴記録のタイミング：
- 予約作成時：change_type = ‘created’
- 予約変更時：change_type = ‘modified’, 旧日時と新日時を記録
- 予約キャンセル時：change_type = ‘cancelled’, appointments.status = ‘cancelled’
5. 2025年1月15日 14:00-14:30 の予約可能な医師一覧：
SELECT
d.doctor_id,
d.doctor_name,
dep.department_code,
dep.department_name
FROM doctors d
JOIN doctor_departments dd
ON d.doctor_id = dd.doctor_id
JOIN departments dep
ON dd.department_code = dep.department_code
WHERE
— 2025年1月15日は水曜日（曜日コード: 3）
dd.day_of_week = 3
— 14:00が診察時間内
AND dd.start_time <= '14:00:00'
AND dd.end_time > ’14:00:00′
— この時間帯に既に予約がない医師
AND NOT EXISTS (
SELECT 1
FROM appointments a
WHERE a.doctor_id = d.doctor_id
AND a.appointment_datetime = ‘2025-01-15 14:00:00’
AND a.status != ‘cancelled’
)
ORDER BY dep.department_name, d.doctor_name;
実行結果例：
doctor_id | doctor_name | department_code | department_name
D001 | 佐藤医師 | DEP01 | 内科
D003 | 鈴木医師 | DEP01 | 内科
D005 | 田中医師 | DEP02 | 外科
✅ 設計のポイント：
- ダブルブッキング防止：UNIQUE制約で同じ患者・医師の同時間予約を防止
- 医師の診療時間管理：doctor_departments で曜日・時間帯ごとの診療科を管理
- 履歴の完全性：全ての変更を appointment_history に記録
- 柔軟な検索：インデックスで高速な空き枠検索
- 診察記録との連携：予約から診察記録、処方へのトレーサビリティ
- データの整合性：外部キー制約で参照整合性を保証
🎓 総合演習のまとめ
✅ この演習で学んだこと
- 実践的な正規化技術：非正規形から第3正規形まで段階的に設計
- ビジネスルールの反映：現実の業務要件をテーブル設計に落とし込む
- 戦略的な非正規化：パフォーマンス要件に応じた適切な判断
- 履歴管理の設計：変更履歴を適切に保持する方法
- 制約の活用：UNIQUE制約、外部キー制約でデータの整合性を保証
- インデックス設計：クエリパフォーマンスを考慮した設計
💡 実務で活かすポイント
- 段階的な設計：いきなり完璧を目指さず、第1正規形→第2正規形→第3正規形と段階的に進める
- ビジネスルールの理解：業務要件を深く理解してから設計する
- パフォーマンスとのバランス：正規化と非正規化を適切に使い分ける
- ドキュメント化：設計判断の理由を必ず記録する
- レビューの実施：チームメンバーと設計をレビューし、問題点を早期発見
🎯 次のステップ
Part 2（正規化）を完了しました！おめでとうございます🎉
次のPart 3では、インデックス設計戦略、データウェアハウス設計、NoSQLなど、より高度で実践的なトピックを学びます。
これまで学んだ正規化の知識は、Part 3でも活かされます。しっかり復習してから次のステップに進みましょう！
❓ よくある質問
Q1: 演習が難しく感じます。どこから手を付ければいいですか？
まず「非正規形の問題点を見つける」ことから始めましょう。繰り返し項目（カンマ区切りのデータ）や、同じデータの重複を探します。問題点が分かれば、どう分割すべきかも見えてきます。紙に書きながら考えると理解が深まります。
Q2: 正規化とSQL作成、どちらを先に覚えるべきですか？
正規化を先に覚えることをお勧めします。正しいテーブル設計ができていれば、SQLは自然と書けるようになります。逆に、正規化されていないテーブルにSQLを書くと、複雑で非効率なクエリになってしまいます。
Q3: 実務では、どの程度の時間をかけてテーブル設計しますか？
プロジェクトの規模によりますが、設計には十分な時間をかけるべきです。小規模なシステムでも1〜2日、中規模なら1〜2週間はテーブル設計に使います。設計を急ぐと、後から大きな修正が必要になることが多いです。
Q4: 非正規化するかどうか、どう判断すればいいですか？
「まず正規化、問題があれば非正規化」が基本です。具体的には、(1)パフォーマンス問題が実際に発生している、(2)インデックスやキャッシュで解決できない、(3)読み取り頻度が高く更新頻度が低い、これらの条件を満たす場合に非正規化を検討します。
Q5: 履歴管理は全てのテーブルに必要ですか？
いいえ、ビジネス要件によります。監査が必要なデータ（金銭に関わるもの、法的に履歴が必要なもの）や、変更の追跡が業務上重要なデータには履歴管理が必要です。全てのテーブルに履歴管理を入れると、複雑さとストレージコストが増加します。
Q6: 演習の解答と異なる設計でも正解ですか？
はい、正解は1つではありません。正規化のルールを満たし、ビジネス要件に対応できていれば、異なる設計でも正解です。大切なのは「なぜその設計にしたか」を説明できることです。解答例は参考の1つとして捉えてください。
学習メモ
データベース設計・データモデリング - Step 12
📋 過去のメモ一覧
▼