🔖 キーワード索引

この用語ページの主要トピックを一覧から飛べます。

🎨 直感で掴む

ER 図を 「組織図」と「家系図」の合体と例えると分かりやすい。 組織図は「箱」と「線」で組織構造を表す。 ER 図は「エンティティ（箱）」と「リレーションシップ（線）」でデータ構造を表す。

🎬 ストーリー：図書館システムを設計する

図書館の業務を IT 化する。 ヒアリングしたら：

• 会員が本を借りる
• 本には著者がいる
• 会員はカテゴリ別に好みがある
• 本にはジャンルがある

これを ER 図にすると：

[会員] --借りる(0..*)-- [貸出記録] --(*..1)-- [本] --書いた(*..*)-- [著者]
                                              |
                                              -属する(*..1)- [ジャンル]
    

これだけ描けば、 「会員テーブル」「本テーブル」「貸出記録テーブル」「著者テーブル」「ジャンルテーブル」と、 多対多を解消する「著者本テーブル（関連テーブル）」が必要、 と一目で分かる。

🎨 視覚的比喩：「立体パズル」

ER 図は 立体パズルの設計図。 各エンティティはパーツ、 リレーションシップは「どう組み合わさるか」。 設計図なしに作ると、 後で「ピースが合わない」事態に。 描いてから組むのが鉄則。

🌐 ER 図が活きる 4 つの場面

1. 新規 DB 設計：白紙からテーブル構造を考える。
2. 既存 DB のリバースエンジニアリング：レガシーシステムの構造を可視化。
3. ステークホルダー間のコミュニケーション：非エンジニアにも理解しやすい。
4. ドキュメント：DB 構造の継続的な記録、 新人教育。

🔍 SSDSE-B-2026 を ER 図で表現してみる

SSDSE-B-2026 は 1 つの大きな CSV ですが、 これを RDB に格納するなら：

[Prefecture] --(1..*)-- [YearlyStat] --属する(*..1)-- [Category]
   |                       |
   都道府県名               年度
   地域コード              人口・出生数・etc.
    

この設計だと、 1 県あたり 1 行ではなく「県 × 年度」で複数行になり、 経年変化を見やすい形に正規化される。 ER 図がなければこの判断は出てこない。

🎨 概念図で押さえる

ER 図はエンティティ（実体）と関係（リレーションシップ）を視覚化する。 ここでは「カーディナリティ・3 種類のリレーション・正規化までの流れ」を概念図で確認する。

🎨 R282 補強: ER 図の実装デモ

「ER 図 → 正規化 → CREATE TABLE → INSERT → 結合クエリ」までを SSDSE-B-2026 で一気通貫で示す。 ER 図は「絵」のままだと半分の理解しかない。

1. SQLite で DDL を発行

このコードでやること： 都道府県マスタ、 年度マスタ、 統計値テーブルを正規化して作成。 PK / FK 制約を全て明示する。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

import sqlite3

con = sqlite3.connect(':memory:')
con.executescript("""
CREATE TABLE pref (
  pref_code TEXT PRIMARY KEY,
  pref_name TEXT NOT NULL UNIQUE,
  region    TEXT
);
CREATE TABLE year (
  year INTEGER PRIMARY KEY
);
CREATE TABLE stat (
  pref_code TEXT NOT NULL,
  year      INTEGER NOT NULL,
  pop_total INTEGER,
  deaths    INTEGER,
  PRIMARY KEY (pref_code, year),
  FOREIGN KEY (pref_code) REFERENCES pref(pref_code),
  FOREIGN KEY (year)      REFERENCES year(year)
);
""")
print('テーブル作成完了')
for row in con.execute("SELECT name FROM sqlite_master WHERE type='table'"):
    print(' -', row[0])

💬 ER 図の 3 つの実体が pref（都道府県マスタ）・year（年度マスタ）・stat（事実テーブル）として作られた。stat の主キーは (pref_code, year) の組で、「1 県 × 1 年度に 1 行」という ER 図上の関係を DB の制約として表している。まだ行は 0 件で、ここで表示されたのはテーブル名だけ。

2. SSDSE-B-2026 から INSERT

このコードでやること： CSV を読み、 正規化された 3 テーブルに分割投入する。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', header=0)
# 2 行目の日本語名の行を落とし、都道府県コードの行だけ残す
df = df[df['Code'].astype(str).str.match(r'^R\d{5}$', na=False)].copy()
# 都道府県コードは 'Code' 列。'SSDSE-B-2026' 列は年度
df = df.rename(columns={'Code': 'pref_code', 'Prefecture': 'pref_name'})
df['SSDSE-B-2026'] = pd.to_numeric(df['SSDSE-B-2026'], errors='coerce')
for _c in ['A1101', 'A4200']:
    df[_c] = pd.to_numeric(df[_c], errors='coerce')

# pref マスタ
pref = df[['pref_code', 'pref_name']].drop_duplicates()
pref['region'] = pref['pref_name'].map({'北海道':'北海道', '青森県':'東北'}).fillna('その他')
pref.to_sql('pref', con, if_exists='append', index=False)

# year マスタ
year = pd.DataFrame({'year': sorted(df['SSDSE-B-2026'].unique())})
year.to_sql('year', con, if_exists='append', index=False)

# stat 事実テーブル
stat = df[['pref_code', 'SSDSE-B-2026', 'A1101', 'A4200']].rename(
    columns={'SSDSE-B-2026':'year','A1101':'pop_total','A4200':'deaths'})
stat.to_sql('stat', con, if_exists='append', index=False)

print(f'pref: {con.execute("SELECT COUNT(*) FROM pref").fetchone()[0]} rows')
print(f'year: {con.execute("SELECT COUNT(*) FROM year").fetchone()[0]} rows')
print(f'stat: {con.execute("SELECT COUNT(*) FROM stat").fetchone()[0]} rows')

💬 pref マスタは 47 行、year マスタは 2012〜2023 の 12 行、stat 事実テーブルは 564 行で、47 × 12 = 564 と一致するので結合キー（pref_code, year）に欠けも重複も無い。ER 図で「pref 1 対 多 stat」「year 1 対 多 stat」と描いた関係が、行数の掛け算で確かめられる。region は北海道と青森県だけ埋めた見本なので、残り 45 県は「その他」になっている点に注意。

3. 結合クエリで ER 図の威力を確認

このコードでやること： pref と stat を JOIN し、 2023 年の死亡率 TOP5 を取得する。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

q = """
SELECT p.pref_name,
       s.pop_total,
       s.deaths,
       ROUND(1000.0 * s.deaths / s.pop_total, 2) AS death_rate_permille
FROM stat s
JOIN pref p ON s.pref_code = p.pref_code
WHERE s.year = 2023
ORDER BY death_rate_permille DESC
LIMIT 5
"""
print(pd.read_sql(q, con))

💬 2023 年度の人口千人あたり死亡数は秋田県 19.17 が最も高く、青森県 17.60・高知県 17.17 と続く。47 県の単純平均は約 14.1、全国（合計どうしの比）は約 12.7 で、最低は東京都 9.74。上位はそのまま高齢化率の高い県で、死亡率の差の多くは年齢構成で説明できるので、県の健康状態を比べるなら年齢調整した死亡率を使う。stat と pref を pref_code で結合して県名を引けているのは、ER 図の 1 対多の関係がそのまま SQL の JOIN になった形。

4. ER 図の妥当性検証

このコードでやること： 参照整合性違反を意図的に発生させ、 FK 制約が効くことを確認。

1
2
3
4
5
6

con.execute('PRAGMA foreign_keys = ON')
try:
    con.execute("INSERT INTO stat VALUES ('R99999', 2023, 1000, 50)")
    print('NG: 制約違反が許された')
except sqlite3.IntegrityError as e:
    print(f'OK: 参照整合性違反を検知 → {e}')

💬 存在しない県コード R99999 の行は「FOREIGN KEY constraint failed」で拒否され、pref に無い県の統計が紛れ込むのを DB 側で防げた。ただし SQLite は外部キー制約が既定で無効で、PRAGMA foreign_keys = ON を実行しないと同じ INSERT が黙って通る。ER 図に線を引いただけでは整合性は守られず、接続ごとにこの設定が要る。

🎮 触って学ぶ：カーディナリティと連関テーブル

教材の定番例「学生・科目・成績」で、 リレーション「履修」の 多重度（カーディナリティ）を 1:1 / 1:N / N:M に切り替えると、 クロウフット（鳥の足, IE 記法）の記号と「意味」がどう変わるかを体感する。 実体（エンティティ）＝長方形、 関連＝線＋多重度記号で描く。 N:M は直接テーブル化できないので、 ボタンで 連関（中間）テーブル「履修」へ分解できる。 主キー（PK）・外部キー（FK）のハイライトで対応を確認しよう。

📐 数式または定義

ER 図には「数式」よりも「記法の規則」があります。 主要記法を整理。

1. Chen 記法（オリジナル、 1976）

• エンティティ＝四角形
• 属性＝楕円（エンティティから線で接続）
• リレーションシップ＝菱形
• 多重度＝線の上に「1」「N」「M:N」を書く

2. IE 記法（クロウフット、 現代主流）

• エンティティ＝四角形（属性も中に列挙）
• リレーションシップ＝線
• 多重度＝クロウフット（鳥の足）記号
• 「1 と必須」＝│、 「0 または 1」＝○│、 「1 以上」＝│Ϟ、 「0 以上」＝○Ϟ

3. 多重度（カーディナリティ）の表記

4. 主キーと外部キー

• 主キー (PK)：エンティティ内で一意に識別する属性。 下線または「PK」マーク。
• 外部キー (FK)：他エンティティの PK を参照する属性。 「FK」マーク。
• 複合主キー：複数属性で 1 つの PK。 関連テーブルで頻出。

5. 弱エンティティ

親エンティティに依存し、 単独では存在できない。 二重線の四角で表記。 例：「貸出記録」は「会員」と「本」がないと存在しない（が、 これは関連テーブルで表現するのが現代的）。

6. 多重度の決定式

2 つのエンティティ A, B の関係多重度は、 4 つの問いで決まる：

1. 1 つの A に対し、 B は最小何個？（0 or 1）
2. 1 つの A に対し、 B は最大何個？（1 or 多）
3. 1 つの B に対し、 A は最小何個？（0 or 1）
4. 1 つの B に対し、 A は最大何個？（1 or 多）

🔬 数式を言葉で読み解く

🔬 数式・定義を「言葉」で読み解く

「ER 図」は単なる絵ではなく、 厳密な意味を持つ記号体系です。 1 つ 1 つを正確に理解しないと、 後の DB 設計で破綻します。

1. 🎯 「エンティティ」とは何か

エンティティ（Entity）は、 業務上識別できる「もの」「人」「事象」を指します。 「会員」「本」「貸出記録」など。 物理的なものに限らず、 「予約」「契約」「イベント」のような抽象概念も含む。 ポイントは「同じ種類の複数の実体（インスタンス）」を抱える「型」であること。

2. 📥 「属性」とは何か

• 単純属性：1 つの値しか持たない。 例：会員 ID、 名前。
• 複合属性：複数の値からなる。 例：住所＝市＋通り＋郵便番号。
• 多値属性：複数の値を持てる。 例：電話番号（複数登録可）。 → これは別エンティティに切り出す。
• 派生属性：他の属性から計算可能。 例：年齢＝生年月日から計算。 通常は格納せず、 SELECT 時に計算。
• キー属性：主キー候補。 一意性が必要。

3. 🧠 「リレーションシップ」と多重度

リレーションシップ（Relationship）は、 エンティティ間の 「関係」を表します。 動詞で命名するのが定石（「借りる」「所属する」「書く」）。 重要なのは 多重度（cardinality）：

• 1 対 1（1:1）：会員 ─ 会員カード（1 人 1 枚）
• 1 対 多（1:N）：部署 ─ 社員（1 部署に N 人）
• 多対多（M:N）：学生 ─ 講義（学生は複数履修、 講義は複数受講者）。 → 関連テーブルで解消するのが現代的。

4. 🔍 「関連テーブル」の重要性

多対多関係を物理 DB に落とすには、 関連テーブル（associative entity, junction table）が必須です。 例：「学生 ─ 履修 ─ 講義」と、 中間に履修テーブルを作る。 履修テーブルの PK は (学生 ID, 講義 ID) の複合主キー。 さらに「履修日」「成績」などの履修固有の属性をそこに置く。 これを ER 図上で「履修」エンティティとして明示的に描くのが論理設計のポイント。

5. 💬 「概念モデル → 論理モデル → 物理モデル」の三段階

• 概念モデル：業務世界のエンティティ・関係を素直に描く。 ステークホルダーとの会話用。 多対多 OK。
• 論理モデル：RDB に格納する形に変換。 多対多は関連テーブルで解消。 正規化（第 1〜3 正規形）を適用。 PK/FK を明示。 DB 製品中立。
• 物理モデル：特定 DB（PostgreSQL、 MySQL、 Oracle）に最適化。 データ型、 インデックス、 制約、 パーティション、 物理ストレージを定義。

6. 📚 正規化との連携

ER 図を描いた後、 各エンティティに対し 正規化を適用：

• 第 1 正規形 (1NF)：属性は単一値（多値属性は別テーブル）
• 第 2 正規形 (2NF)：1NF + 部分関数従属の除去（複合 PK の一部にしか依存しない属性を分離）
• 第 3 正規形 (3NF)：2NF + 推移的関数従属の除去（非 PK 属性間の従属を分離）
• BCNF、 4NF、 5NF：さらに高度な正規化。 実務では 3NF までが多い。

7. 🎨 非正規化（denormalization）の判断

読み取り性能のために、 あえて正規形を崩すことがあります（非正規化）。 「同じデータが複数箇所にコピーされる」リスクと、 「JOIN なしで読める」メリットのバランス。 DWH や OLAP では非正規化が定石（スタースキーマ、 スノーフレーク）。

🧮 実値で計算してみる — SSDSE-B-2026

SSDSE-B-2026 を RDB に格納するための ER 図設計を具体的に行います。

1. 元データの形

SSDSE-B-2026 は CSV 1 枚で、 行＝年度×都道府県（47 県 ×1 年 = 47 行）、 列＝112 個の指標（人口、 出生数、 病院数、 etc.）。 これを「1 テーブル」で持つのは正規化的に NG。

2. 概念モデル

[Prefecture] --(1..*)-- [YearlyStat] --(*..1)-- [Year]
                            |
                       多くの数値属性
    

3. 論理モデル — テーブル分割案 A（属性をワイドのまま）

• prefectures (pref_code PK, pref_name, region)
• yearly_stats (id PK, pref_code FK, year, A1101_pop, A1303_elderly, B4101_temp, ...)

4. 論理モデル — テーブル分割案 B（縦持ち、 EAV）

• prefectures (pref_code PK, pref_name)
• indicators (indicator_code PK, name, unit, description)
• observations (id PK, pref_code FK, year, indicator_code FK, value)

案 B は「指標が増えてもテーブル構造を変えずに済む」柔軟性が高いが、 SELECT に JOIN が必要で複雑。 案 A は単純だが列数が爆発する。

5. 物理モデル（PostgreSQL）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE prefectures (
  pref_code  CHAR(6)      PRIMARY KEY,
  pref_name  VARCHAR(20)  NOT NULL,
  region     VARCHAR(20)
);

CREATE TABLE indicators (
  indicator_code VARCHAR(10) PRIMARY KEY,
  name           VARCHAR(100),
  unit           VARCHAR(20),
  description    TEXT
);

CREATE TABLE observations (
  id            BIGSERIAL  PRIMARY KEY,
  pref_code     CHAR(6)    REFERENCES prefectures(pref_code),
  year          SMALLINT,
  indicator_code VARCHAR(10) REFERENCES indicators(indicator_code),
  value         DOUBLE PRECISION,
  UNIQUE (pref_code, year, indicator_code)
);

CREATE INDEX idx_obs_pref_year ON observations(pref_code, year);
CREATE INDEX idx_obs_indicator ON observations(indicator_code);

6. 多重度の検証

• prefectures (1) ─ (N) observations: 1 県に複数観測値、 1 観測値は 1 県 → 多重度 OK
• indicators (1) ─ (N) observations: 1 指標に複数観測、 1 観測は 1 指標 → 多重度 OK
• observations の (pref_code, year, indicator_code) は一意 → UNIQUE 制約で保証

6-2. 多重度と主キーを実データで検査する

上の 3 行は「こうなっているはず」という設計上の主張です。ER 図に描いた主キーと多重度は、実データに当てて初めて確かめられます。SSDSE-B-2026 の 564 行について、主キーの候補ごとに重複する行を数え、県コードと県名の対応（関数従属）と、1 県・1 年度あたりの観測の件数を調べます。

🎯 このコードでやること：SSDSE-B-2026 の全 564 行について、4 つの主キー候補の重複行の数、県コードと県名が 1 対 1 か、1 県あたり・1 年度あたりの行数、欠けている (県, 年度) の組、欠損のある測定列の数を調べる。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df.rename(columns={'SSDSE-B-2026': 'year', 'Code': 'pref_code', 'Prefecture': 'pref_name'})
print(f'行数 {len(df)}、列数 {df.shape[1]}')

# 主キー候補が本当に 1 行を 1 つに決めるか
for key in [['pref_code'], ['year'], ['pref_code', 'year'], ['pref_name', 'year']]:
    print(f'{str(key):26s} 重複する行 {df.duplicated(key).sum():3d} → {"主キーになれる" if not df.duplicated(key).any() else "なれない"}')

# 関数従属 pref_code → pref_name（1 つのコードに名前が 1 つだけか）と、その逆
print('1 コードあたりの県名の種類（最大）:', df.groupby('pref_code')['pref_name'].nunique().max())
print('1 県名あたりのコードの種類（最大）:', df.groupby('pref_name')['pref_code'].nunique().max())

# 多重度: 都道府県 1 : N 観測、年度 1 : N 観測 の N が何件か
n_per_pref = df.groupby('pref_code').size()
n_per_year = df.groupby('year').size()
print(f'1 県あたりの観測 {n_per_pref.min()}〜{n_per_pref.max()} 行、1 年度あたり {n_per_year.min()}〜{n_per_year.max()} 行')
print('欠けている (県, 年度) の組:', len(df['pref_code'].unique()) * len(df['year'].unique()) - len(df))

# 測定属性の欠損（NULL を許す列が要るか）
na = df.drop(columns=['year', 'pref_code', 'pref_name']).isna().sum()
print('欠損のある測定列:', int((na > 0).sum()), '/', len(na))

💬 県コードだけでは 517 行が重複し（1 県に 12 年度分あるため）、年度だけでは 552 行が重複します。(県コード, 年度) の組は重複 0 で、観測テーブルの複合主キーになれます。(県名, 年度) も重複 0 で、これも候補キーです。1 つの県コードに対応する県名は 1 種類、1 つの県名に対応するコードも 1 種類なので、県コード → 県名、県名 → 県コードの関数従属が両方向に成り立ち、県マスタへ分けてよいことが分かります。1 県あたりちょうど 12 行、1 年度あたりちょうど 47 行、欠けている組は 0 なので、「都道府県 1 : N 観測（N = 12）」「年度 1 : N 観測（N = 47）」の多重度は実データでも成り立っています。109 の測定列に欠損は無く、このデータの範囲では NOT NULL を付けられます。

ここで (県名, 年度) も候補キーになることに注意してください。候補キーが 2 つあるとき、主キーには変わりにくい方を選びます。県名は表記（「東京」と「東京都」、旧字体など）が揺れうる一方、R13000 のような県コードは JIS で決まった値なので、主キーは県コードにし、県名は県マスタの属性（必要なら UNIQUE 制約）にとどめます。次の年度のデータが届いたら、同じ検査を回して「1 年度あたり 47 行」「欠けている組 0」が崩れていないかを確かめるのが、ER 図を運用で守る最小の手順です。

7. クエリ例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 47 都道府県の 2023 年高齢化率トップ 10
SELECT
  p.pref_name,
  obs.value AS aging_rate
FROM observations obs
JOIN prefectures p ON obs.pref_code = p.pref_code
WHERE obs.indicator_code = 'AGING_RATE'
  AND obs.year = 2023
ORDER BY obs.value DESC
LIMIT 10;

8. 元 CSV の縦持ち化スクリプト

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

import pandas as pd
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])

# wide → long
indicators = ['A1101', 'A1303', 'B4101', 'F3101', 'I510120']
long_df = df.melt(
    id_vars=['SSDSE-B-2026', 'Code', 'Prefecture'],
    value_vars=indicators,
    var_name='indicator_code',
    value_name='value'
)
long_df.rename(columns={'SSDSE-B-2026': 'year', 'Code': 'pref_code'}, inplace=True)
long_df.to_csv('observations.csv', index=False)
print(long_df.head())
print(f'rows: {len(long_df)}')

💬 5 指標を縦持ちにすると 564 × 5 = 2,820 行になる。先頭 5 行はどれも北海道の総人口（A1101）で、2023→2019 と年度が降順に並ぶのは元の CSV の並びがそのまま残るため。value 列は人口と気温のように単位の違う値が同居して float になるので、ER 図では indicator_code から単位を引く指標マスタを別エンティティとして持たせる。

8-2. 案 A（ワイド）と案 B（EAV）を実データで作って同じ問いを投げる

3・4 で挙げた 2 つの分割案を、SQLite のメモリ上に実際に作ります。問いは「2023 年度の高齢化率（65 歳以上人口 ÷ 総人口）の上位 3 県」です。案 A なら 1 行の中の 2 列の割り算で済みますが、案 B では 2 つの指標が別々の行にあるので、観測テーブルどうしを結合して 1 行にそろえる必要があります。

🎯 このコードでやること：全 564 行から県マスタ・案 A のワイド表・案 B の縦持ち表・指標マスタの 4 表を作って行数を数え、小数を含む指標を洗い出し、同じ問いを 2 つの案の SQL で解いて結果が一致するかを確かめる。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

import sqlite3
import pandas as pd

raw = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', header=None, nrows=2)
names = dict(zip(raw.iloc[0], raw.iloc[1]))                  # 列コード → 日本語名
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df.rename(columns={'SSDSE-B-2026': 'year', 'Code': 'pref_code', 'Prefecture': 'pref_name'})
codes = [c for c in df.columns if c not in ('year', 'pref_code', 'pref_name')]

con = sqlite3.connect(':memory:')
df[['pref_code', 'pref_name']].drop_duplicates().to_sql('pref', con, index=False)
df.drop(columns='pref_name').to_sql('obs_wide', con, index=False)                 # 案 A: ワイド
long = df.melt(id_vars=['year', 'pref_code'], value_vars=codes, var_name='indicator_code')
long.to_sql('obs_long', con, index=False)                                          # 案 B: EAV
ind = pd.DataFrame({'indicator_code': codes, 'name': [names[c] for c in codes],
                    'is_integer': [bool((df[c] % 1 == 0).all()) for c in codes]})
ind.to_sql('indicator', con, index=False)
for t in ['pref', 'obs_wide', 'obs_long', 'indicator']:
    print(f'{t:10s} {con.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]:6d} 行')
print('小数を含む指標:', ind.loc[~ind['is_integer'], 'name'].tolist())

qa = """SELECT p.pref_name, ROUND(100.0 * w.A1303 / w.A1101, 2) AS aging
        FROM obs_wide w JOIN pref p USING (pref_code)
        WHERE w.year = 2023 ORDER BY aging DESC LIMIT 3"""
qb = """SELECT p.pref_name, ROUND(100.0 * o65.value / tot.value, 2) AS aging
        FROM obs_long o65
        JOIN obs_long tot ON tot.pref_code = o65.pref_code AND tot.year = o65.year
                         AND tot.indicator_code = 'A1101'
        JOIN pref p ON p.pref_code = o65.pref_code
        WHERE o65.indicator_code = 'A1303' AND o65.year = 2023
        ORDER BY aging DESC LIMIT 3"""
a, b = pd.read_sql(qa, con), pd.read_sql(qb, con)
print(a.to_string(index=False))
print('案 A と案 B の結果は一致:', a.equals(b))

💬 案 B の縦持ち表は 564 × 109 = 61,476 行になり、指標マスタは 109 行です。同じ問いに対して、案 A は 1 回の結合（県名を引くため）、案 B は観測テーブル自身との結合がもう 1 回必要ですが、答えはどちらも秋田県 39.06%・高知県 36.34%・徳島県 35.40% で一致します。小数を含む指標は合計特殊出生率・年平均気温・最高気温・最低気温・降水量・ごみのリサイクル率の 6 つで、残る 103 指標は整数です。案 B では 1 つの value 列にすべてを入れるので、人口のような整数も REAL 型で持つことになります。

案 B は「指標が増えても表の定義を変えなくてよい」代わりに、比率を 1 つ計算するたびに自己結合が増え、型や単位は指標マスタを見ないと分かりません。SSDSE のように指標が年に 1 回決まった形で届くデータなら案 A、指標が頻繁に増減する・指標ごとに出典や単位を管理したいなら案 B（と指標マスタ）、というのが 2 つの案を選ぶ目安です。分析の段階では、案 B で保管し、使う指標だけを pivot して案 A の形に戻す、という組み合わせもよく使われます。

9. インデックス設計

「県別＋年度」検索が多いなら (pref_code, year) の複合インデックス。 「指標別」検索なら (indicator_code) 単独インデックス。 全部にインデックスを張ると INSERT が遅くなる。 トレードオフ。

10. 拡張性

将来 SSDSE-B-2027, 2028 が出ても、 observations テーブルに INSERT するだけで済む。 これが 正規化された ER 設計の強み。

🧮 数式に値を入れて手で計算する: 多対多解消で増える表数

合成データで n×m 関係を中間表で正規化する場合のテーブル数を計算する。

Step 1: 元のエンティティ

Step 2: 合計

🐍 Python で再現

1
2
3
4
5
6
7

n_relations = 4
entities = 2 * n_relations
junction = n_relations
total = entities + junction
print(f"エンティティ: {entities}")
print(f"中間表: {junction}")
print(f"合計表数: {total}")

📤 実行結果

💬 手計算 (Step 2) 12 と Python 出力が完全一致。

🐍 Python 実装

① SQLAlchemy で ER モデルを Python クラスで定義

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

from sqlalchemy import create_engine, Column, String, Integer, Float, ForeignKey
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class Prefecture(Base):
    __tablename__ = 'prefectures'
    pref_code = Column(String(6), primary_key=True)
    pref_name = Column(String(20), nullable=False)
    region = Column(String(20))
    observations = relationship('Observation', back_populates='prefecture')

class Indicator(Base):
    __tablename__ = 'indicators'
    indicator_code = Column(String(10), primary_key=True)
    name = Column(String(100))
    unit = Column(String(20))
    observations = relationship('Observation', back_populates='indicator')

class Observation(Base):
    __tablename__ = 'observations'
    id = Column(Integer, primary_key=True, autoincrement=True)
    pref_code = Column(String(6), ForeignKey('prefectures.pref_code'))
    year = Column(Integer)
    indicator_code = Column(String(10), ForeignKey('indicators.indicator_code'))
    value = Column(Float)
    prefecture = relationship('Prefecture', back_populates='observations')
    indicator = relationship('Indicator', back_populates='observations')

engine = create_engine('postgresql://user:pass@localhost/ssdse')
Base.metadata.create_all(engine)

② pgAdmin / DBeaver でリバースエンジニアリング

1
2
3
4
5
6
7
8

# pgAdmin の手順（GUI 操作）:
# 1. 対象 DB に接続
# 2. Schema → Right click → Generate ERD
# 3. PDF / PNG で保存

# CLI 版（schemaspy）:
# java -jar schemaspy.jar -dp postgresql.jar -t pgsql \
#   -host localhost -db ssdse -u user -p pass -o ./erd_out

③ Mermaid でテキストから ER 図を描く

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

erDiagram
    PREFECTURE ||--o{ OBSERVATION : has
    INDICATOR ||--o{ OBSERVATION : measured
    PREFECTURE {
        string pref_code PK
        string pref_name
        string region
    }
    INDICATOR {
        string indicator_code PK
        string name
        string unit
    }
    OBSERVATION {
        int id PK
        string pref_code FK
        int year
        string indicator_code FK
        float value
    }

④ DrawIO で対話的に ER 図を描く

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

<!-- drawio はテキストベースの XML を内部で持つ -->
<mxfile>
  <diagram>
    <mxGraphModel>
      <root>
        <mxCell vertex="1" value="Prefecture\npref_code (PK)\npref_name\nregion"/>
        <mxCell vertex="1" value="Observation\nid (PK)\npref_code (FK)\nyear\nvalue"/>
      </root>
    </mxGraphModel>
  </diagram>
</mxfile>

⑤ Alembic で ER モデル変更のマイグレーション

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# alembic init alembic
# 設定ファイル env.py に Base.metadata を登録

# モデル変更後:
# alembic revision --autogenerate -m "add region column"
# alembic upgrade head

# 出力例（versions/abc123_add_region.py）:
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('prefectures',
        sa.Column('region', sa.String(length=20)))

def downgrade():
    op.drop_column('prefectures', 'region')

⑥ Mermaid + 自動 PR チェック（CI 統合）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

name: ERD Render
on:
  pull_request:
    paths: ['docs/erd.md']
jobs:
  render:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - uses: neenjaw/compass-mermaid-action@v1
        with:
          mermaid-files: docs/erd.md
      - run: |
          gh pr comment ${{ github.event.pull_request.number }} \
            --body "Updated ER diagram → $ARTIFACT_URL"

⚠️ 落とし穴 — よくある失敗 5 件

📊 実データで確かめる：正規化しないと何が起きるか（更新異常）

「属性の冗長化」の害を、SSDSE-B-2026 のワイド表で数えます。ワイド表では県名が行ごとに書かれていて、東京都という文字列は 12 回出てきます。そのうち 1 か所だけを直し損ねたらどうなるかを試します。

🎯 このコードでやること：ワイド表の県名の書かれている回数と、県マスタ（47 行）＋観測テーブルに分けたときの回数・CSV の大きさを比べる。次に 2023 年度の東京都の行だけ県名を「東京」に変え、県名で集計したときの群の数を数える。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df.rename(columns={'SSDSE-B-2026': 'year', 'Code': 'pref_code', 'Prefecture': 'pref_name'})

# 3NF に分けた形: 県マスタ（47 行）と観測テーブル（564 行、県名を持たない）
pref = df[['pref_code', 'pref_name']].drop_duplicates()
obs = df.drop(columns=['pref_name'])
print(f'ワイド表で県名が書かれている回数 {len(df)}、県マスタでは {len(pref)}（重複して持つ分 {len(df) - len(pref)}）')

size = lambda t: len(t.to_csv(index=False).encode('utf-8'))
print(f'CSV にしたときの大きさ: ワイド 1 表 {size(df):,} バイト / 県マスタ {size(pref):,} + 観測 {size(obs):,} = {size(pref) + size(obs):,} バイト')

# 更新異常: 東京都の表記を 2023 年度の行だけ「東京」に直してしまう
bad = df.copy()
bad.loc[(bad['pref_code'] == 'R13000') & (bad['year'] == 2023), 'pref_name'] = '東京'
print('1 コードに県名が 2 種類ある県:', bad.groupby('pref_code')['pref_name'].nunique().gt(1).sum())
print('県名で集計すると 東京都 の行数:', (bad['pref_name'] == '東京都').sum(), ' 東京 の行数:', (bad['pref_name'] == '東京').sum())
print('県名での groupby の群の数:', bad.groupby('pref_name').ngroups, '（本来 47）')

💬 ワイド表では県名が 564 回書かれ、県マスタに分ければ 47 回で済みます（重複して持っていた分は 517）。ただし CSV の大きさは 358,872 バイトから 353,942 バイトへ 1.4% 減るだけで、正規化の主な利点は容量ではありません。2023 年度の東京都の 1 行だけ県名を「東京」にすると、1 つのコードに県名が 2 種類ある県が 1 つ生まれ、県名で集計すると「東京都」11 行と「東京」1 行に割れて、群の数は 47 ではなく 48 になります。県名を県マスタだけに持たせていれば、直す場所は 1 か所なので、この食い違いは原理的に起きません。

これが更新異常です。表記を直す・県名が変わるといった更新のたびに、ワイド表では 12 か所を漏れなく直す必要があり、1 か所でも漏れると集計の群が割れます。分析者がよくやる「県名で groupby」は、このずれをそのまま結果に持ち込みます。集計や結合のキーには県名ではなく県コードを使う、という習慣も、同じ理由から来ています。

🌐 関連手法・派生

• 正規化：第 1〜5 正規形。 ER 図の論理化に必須。
• 主キー / 外部キー：主キー / 外部キー
• テーブル設計：テーブル
• SQL：SQL
• データベース：データベース
• RDB / RDBMS：RDB
• NoSQL：NoSQL
• UML クラス図：ER 図と類似だが OOP 寄り。
• データウェアハウス：スタースキーマ、 スノーフレーク。
• ER ツール：DrawIO、 dbdiagram.io、 Lucidchart、 Visual Paradigm。

🔗 関連用語（前提・並列・発展）

📚 関連グループ教材

• 📚 データエンジニアリング — ER 図を含む DB 設計全般
• 📚 データリテラシー — データ理解の基礎

📖 用語辞典 10 語 — この分野の必須語彙

エンティティ

業務上識別できる「もの」「人」「事象」。 テーブルの素。

属性

エンティティが持つ性質。 カラムの素。

リレーションシップ

エンティティ間の関係。 外部キーで実装。

主キー (PK)

エンティティを一意識別する属性。

外部キー (FK)

他エンティティの PK を参照する属性。

多重度

関係の最小・最大個数。 1:1, 1:N, M:N。

関連テーブル

多対多を解消する中間テーブル。

弱エンティティ

親に依存し単独で存在しないエンティティ。

正規化

データ冗長性を排除する設計手法。

非正規化

読み取り性能のために正規形を崩す。

📚 参考文献・規格・ドキュメント

1. Chen, P.P. (1976) The Entity-Relationship Model — Toward a Unified View of Data, ACM TODS, 1(1): 9-36.
2. Codd, E.F. (1970) A Relational Model of Data for Large Shared Data Banks, CACM, 13(6): 377-387.
3. ミック (2012) 『達人に学ぶDB設計徹底指南書』翔泳社.
4. Karwin, B. (2010) SQL Antipatterns, Pragmatic Bookshelf.
5. Kent, W. (1983) A Simple Guide to Five Normal Forms, CACM, 26(2): 120-125.
6. Kimball, R. (2013) The Data Warehouse Toolkit, 3rd ed., Wiley.
7. 増永良文 (2017)『リレーショナルデータベース入門』第 3 版, サイエンス社.

🗺 概念マップ

ER 図を中心とした概念ツリー：

データモデリング
├─ 概念モデリング
│  ├─ ER 図 (ERD)  ← この用語
│  │  ├─ Chen 記法
│  │  ├─ IE 記法 (Crow's Foot)
│  │  ├─ Barker 記法
│  │  └─ IDEF1X
│  ├─ UML クラス図
│  └─ ファクト指向モデリング (ORM)
├─ 論理モデリング (正規化)
└─ 物理モデリング (DBMS 固有)

派生・関連：
├─ ディメンショナルモデリング (DWH)
│  ├─ スタースキーマ
│  └─ スノーフレークスキーマ
├─ Data Vault モデリング
├─ NoSQL 設計パターン
│  ├─ ドキュメント (MongoDB)
│  ├─ KVS (Redis, DynamoDB)
│  ├─ カラムストア (Cassandra)
│  └─ グラフ (Neo4j)
└─ Event Sourcing

📜 歴史と系譜

ER 図の歴史を整理。

1. 1970: コッドのリレーショナルモデル

エドガー・F・コッドが「A Relational Model of Data for Large Shared Data Banks」を発表。 リレーショナルデータベースの理論的基盤を確立。

2. 1976: チェンの ER モデル

Peter Chen が論文「The Entity-Relationship Model — Toward a Unified View of Data」を発表。 「エンティティ」「リレーションシップ」「属性」の 3 要素で DB 構造を表現する手法を提唱。 ER 図の誕生。

3. 1981: IE 記法（クロウフット）

James Martin の Information Engineering で「クロウフット（鳥の足）」記法が普及。 Chen 記法より省スペースで読みやすく、 現代の主流に。

4. 1985: SQL 標準化

ANSI SQL 標準化。 ER 図から SQL DDL への変換が体系化される。 ER 図 → CREATE TABLE 文 の機械的変換が可能に。

5. 1990s: CASE ツールの普及

ERwin、 ER/Studio、 PowerDesigner などの専用ツールが企業で広く採用。 ER 図のラウンドトリップ（順方向・逆方向同期）が実現。

6. 2000s: UML の隆盛

UML クラス図が ER 図の代替候補として浮上。 ただし DB 専用の表現力では ER 図が依然優位。

7. 2010s: NoSQL とスキーマレス

MongoDB、 Cassandra、 DynamoDB の登場で「スキーマレス」が流行。 ER 図不要論も。 しかし大規模システムでは結局スキーマ設計が必要、 という揺り戻し。

8. 2020s: ドキュメント・アズ・コード

Mermaid、 PlantUML、 dbdiagram.io でテキストベース ER 図が普及。 git で変更履歴管理、 CI で自動レンダリング。 DevOps 文化との融合。

🔧 実装詳細

ER 図実装の細部。

1. ツール選定

• 無料・OSS：DrawIO、 Mermaid、 PlantUML、 dbdiagram.io
• 商用：ERwin、 ER/Studio、 PowerDesigner、 Lucidchart
• クラウド系：Miro、 Figma（協同編集）
• DB 統合：pgAdmin、 MySQL Workbench、 DBeaver

2. 命名規則

• テーブル：複数形、 snake_case（users、 yearly_stats）
• カラム：snake_case（first_name、 created_at）
• PK：id または table_id（user_id）
• FK：参照先テーブル名_id（pref_code）
• タイムスタンプ：created_at、 updated_at、 deleted_at
• 真偽値：is_active、 has_paid（is_/has_ プレフィクス）

3. データ型の選定

• 整数：SMALLINT / INTEGER / BIGINT を用途別に選ぶ（容量節約）
• 文字列：VARCHAR(N) で最大長を制限、 TEXT は無制限
• 金額：DECIMAL(p, s) で精度確保。 FLOAT は丸め誤差 NG
• 日付：DATE / TIMESTAMP / TIMESTAMPTZ（タイムゾーン考慮）
• 真偽値：BOOLEAN
• JSON：JSONB（PostgreSQL）、 JSON（MySQL）
• 地理：PostGIS の GEOMETRY 型

4. 制約の活用

• NOT NULL：必須項目
• UNIQUE：一意制約
• CHECK：ドメイン制約（age >= 0 など）
• FOREIGN KEY：参照整合性
• DEFAULT：デフォルト値

5. インデックス設計

• PK は自動で BTREE インデックス
• 外部キーには自動でインデックス（多くの DBMS）
• WHERE 句で頻出のカラムに追加
• JOIN キーに追加
• ORDER BY、 GROUP BY に活用
• 多すぎると INSERT が遅くなる

⚙️ 運用とトラブルシュート

ER 図運用での難題。

1. 仕様変更への追随

業務要件は変わり続ける。 ER 図も常にアップデート。 ツール選定時に「ラウンドトリップ可能性」を確認。

2. マイグレーション

Alembic、 Flyway、 Liquibase でスキーマ変更を git 管理。 本番反映前にステージングで検証。

3. パフォーマンスチューニング

EXPLAIN ANALYZE で実行計画確認。 SELECT 遅延は (a) インデックス追加、 (b) クエリ書き換え、 (c) パーティション、 (d) 非正規化 の順で対処。

4. ドキュメント生成

schemaspy、 sqldef、 tbls で DB スキーマから ER 図とドキュメントを自動生成。 README で公開。

5. データ品質

参照整合性、 制約、 トリガーで DB レベルで品質確保。 アプリ層だけに頼らない。

💴 コストと見積もり

ER 図関連のコスト。

1. ツール費用

2. 設計フェーズの人的コスト

中規模システム（50 テーブル程度）の初期 ER 設計：シニアエンジニア 1 名 × 1 ヶ月 ≈ 150 万円。 修正フェーズはさらに同程度。

3. 教育コスト

新人エンジニアに ER 図の読み書きを教えるのに 20〜40 時間。 OJT で覚えさせる方が定着率高い。

🛡 ガバナンス・セキュリティ

ER 図関連のガバナンス。

1. データオーナーシップ

各エンティティの所有部門を明確化。 個人情報（人）は人事部、 売上（売上）は経理部、 など。 RACI マトリクス。

2. データ品質管理

DAMA 6 次元（正確性・完全性・一貫性・適時性・一意性・妥当性）で各エンティティを評価。

3. 個人情報の取り扱い

個人情報を含むカラムに「PII」フラグ。 暗号化、 アクセスログ、 匿名化を必須化。

4. データ系譜（Data Lineage）

エンティティ間のデータの流れを記録。 Apache Atlas、 OpenLineage で自動化。

5. 監査

DB 構造変更履歴を git で管理。 SOX 法、 J-SOX 対応。

🏭 産業事例 6 件 — 現場ではどう使われているか

📊 ER 図記法の比較

📋 正規化レベル早見表

ER 図設計で頻出する「第 N 正規形」の到達条件と典型的なユースケースを表に整理する。 SSDSE-B-2026 のような分析用データセットでは 3NF が標準だが、 DWH では意図的に 2NF に留めるケースもある。

🗂 SSDSE-B-2026 を ER 図化する際の典型カラム対応

SSDSE-B-2026 (47 都道府県 × 多変量年次データ) を RDB スキーマに落とす際の対応関係を示す。 「年×都道府県」が複合 PK、 指標は属性 (人口・出生数など) となる。

✅ 実データの数値で理解度チェック

問 1. SSDSE-B-2026 は 47 都道府県 × 12 年度で 564 行あります。109 個の測定列をすべて縦持ち（EAV、1 行 = 県 × 年度 × 指標）の観測テーブルにすると何行になり、主キーは何になりますか。

答え. 564 × 109 = 61,476 行。主キーは (県コード, 年度, 指標コード) の 3 列の複合キーです。上の縦持ち化スクリプトは 5 指標だけなので 564 × 5 = 2,820 行でした。

問 2. 県コードだけで 2 つの観測テーブル（それぞれ 564 行）を結合すると何行になりますか。そのとき東京都の死亡数の 12 年分の合計は何倍になりますか。

答え. 1 県あたり 12 × 12 = 144 行、47 県で 6,768 行。死亡数の各行が相手側の 12 行と組になるので、合計は 12 倍（1,437,762 → 17,253,144）になります。

問 3. (県名, 年度) も重複が 0 で候補キーになりました。それでも主キーに県コードを選ぶ理由を 1 つ挙げてください。

答え. 県名は表記が揺れうる（「東京」と「東京都」など）からです。1 行だけ「東京」にしただけで、県名での集計は 47 群ではなく 48 群に割れました。コードは JIS X 0401 で決まっていて、表記の揺れが起きません。

問 4. ワイド表を県マスタと観測テーブルに分けても、CSV の大きさは 358,872 バイトから 353,942 バイトへ 1.4% しか減りませんでした。「容量がほとんど減らないなら正規化は不要」と言えますか。

答え. 言えません。正規化の目的は容量ではなく、同じ事実を 1 か所にだけ持つことで更新異常を防ぐことです。ワイド表では東京都の県名を 12 か所に持つので、1 か所の直し損ねで集計の群が 47 から 48 に割れました。容量が効いてくるのは、県名や指標名のような長い文字列が何百万行にも繰り返される大きな表の場合です。

問 5. 案 B（EAV）の value 列はなぜ REAL 型にする必要があり、それで何が失われますか。

答え. 109 指標のうち合計特殊出生率・気温・降水量・リサイクル率の 6 指標が小数を含み、1 つの列に全指標を入れるには小数を持てる型が要るからです。その代わり、人口のように整数であるべき値も REAL で持つことになり、「整数である」という制約を型で守れなくなります。指標マスタに型や単位の列（上の例では is_integer）を持たせて補います。

📝 演習 5 問 — 理解度チェック

💥 現場の失敗例 — こうして詰んだ

❓ よくある質問 (FAQ) 10 問

Q. ER 図と UML クラス図、 どちらを使うべき？

A. DB 設計が主なら ER 図、 ソフトウェア設計と統合したいなら UML。 ER 図は「データ」、 UML は「データ＋振る舞い」。 用途に応じて。

Q. NoSQL でも ER 図は必要？

A. MongoDB、 Cassandra でも「コレクション間の関係」を可視化する価値あり。 ただし正規化原則は緩い。 アクセスパターン中心の設計が重要。

Q. 無料ツールでおすすめは？

A. DrawIO（GUI）、 Mermaid（テキスト）、 dbdiagram.io（DSL）。 用途で使い分ける。

Q. チームで共同編集するには？

A. Mermaid / PlantUML をテキストで git 管理 + CI で画像化が最強。 Miro / Lucidchart などのリアルタイム共同編集も選択肢。

Q. 既存 DB から ER 図を生成できる？

A. Yes。 pgAdmin、 MySQL Workbench、 DBeaver、 schemaspy で自動生成。 ただし命名規則が悪い DB だと読みづらい。

Q. 第 3 正規形まで満たせば十分？

A. 業務系なら 3NF が標準。 OLAP / DWH では意図的に非正規化（スタースキーマ）。 用途次第。

Q. ER 図の更新はどのタイミング？

A. スキーマ変更 PR と同じタイミング。 Mermaid 化していれば自動。 「文書更新を後回しにしない」が鉄則。

Q. ER 図と論理モデルは何が違う？

A. 概念モデル（業務寄り）→ 論理モデル（RDB 設計、 正規化済み）→ 物理モデル（特定 DBMS 向け）。 ER 図は概念〜論理を表現する。

Q. エンティティ名は単数？複数？

A. テーブル名は複数形（users、 orders）、 ER 図上のエンティティ名は単数（User、 Order）が多数派。 流派次第。

Q. ER 図の学習に最適な書籍は？

A. 『達人に学ぶ DB 設計徹底指南書』(ミック著)、 『SQL アンチパターン』(Karwin 著)、 『リレーショナルデータベース入門』(増永良文著)。

🔗 隣接手法への橋渡し

ER 図は単独の作図ではなく、 業務分析 ・正規化 ・物理 DB 設計 ・SQL DDL 生成を結ぶ設計ドキュメントである。 エンティティ ・関係 ・属性 ・カーディナリティを業務要件と紐付けて記述する。

• 上流（前処理・データ整備）: 要件定義 (リサーチクエスチョン) / データモデリング (DB) / ドメイン知識
• 並列（代替・補完手法）: UML (データベース設計) / DFD (データベース設計) / クラス図 (設計)
• 下流（評価・解釈・応用）: RDBMS (データベース) / スキーマ設計 / DB 正規化

ER 図は「実体 (Entity) ・関係 (Relationship) ・属性で DB スキーマを表現する」設計手法で、 上流の業務要件分析でエンティティを抽出し、 並列の UML クラス図・正規化と組み合わせ、 下流の物理 DB 設計・SQL DDL 生成へと運ぶ。

🌳 意思決定ツリー — 状況別の手順

ER 図の選定・運用での意思決定をツリーで整理。

🌳 ツリー 1: 採用すべきか

現状システムに課題があるか？
├─ Yes → 課題はコスト／性能／拡張性／信頼性？
│  ├─ コスト → ROI 試算で ER 図 採用検討
│  ├─ 性能 → ベンチマークで比較
│  ├─ 拡張性 → スケーラビリティ要件を整理
│  └─ 信頼性 → SLA, MTBF を比較
└─ No → 「動いているものは触らない」原則
    

🌳 ツリー 2: アーキテクチャ選定

ワークロードの特性は？
├─ 予測可能・常時稼働 → リザーブド／専有
├─ 変動大・短期 → サーバーレス／スポット
├─ レイテンシ厳しい → エッジ／フォグ
└─ コンプライアンス厳しい → プライベート／オンプレ
    

🌳 ツリー 3: トラブル対応

症状は？
├─ 完全停止 → ロールバック先行、 原因究明は後
├─ 性能劣化 → メトリクス／ログ／トレースで根因分析
├─ コスト急増 → 利用量分析、 不正アクセス疑い
└─ セキュリティイベント → CSIRT 起動、 隔離
    

📝 補足：直感を「三要素」で描き直す

ER 図の本質は、 世界を 3 つの品詞に分けて絵にすることだと捉えると迷わない。 これは前述の「組織図＋家系図」の比喩を、 文法の言葉で言い換えたものである。

1. SSDSE-B-2026 の実測で三要素を確認する

実データ（SSDSE-B-2026.csv、 cp932、 skiprows=[1]）は 564 行 × 112 列。 内訳を三要素に写像すると次のとおり。 これは捏造ではなく実ファイルの形状に一致する。

たとえば 2023 年の総人口列 A1101 は東京都＝14,086,000 が最大値（実データ）。 この 1 マスは「実体＝都道府県(東京都)、 年度(2023)」×「属性＝総人口」の交点にすぎない。 1 マスを主キーで一意に特定できることが、 ER 図がめざす「行の意味の明確化」である。

2. 多重度を「4 つの問い」でなく「矢印の向き」で掴む

数式セクションの 4 問法を、 直感側から補う。 「片側から相手を見たとき何本の線が出るか」だけを見る。

3. PK / FK は図の上でどう描かれるか

• 主キー (PK)：属性名に下線、 または行末に「PK」。 複合 PK は複数行に下線（例：観測値の (Code, 年)）。
• 外部キー (FK)：行末に「FK」、 IE 記法では相手の実体へ線で接続。 FK は「1 つの値しか持てない」ため、 これが後述の「多対多を直接描けない」理由に直結する。

関連ページ：主キー ／ 外部キー ／ テーブル ／ データベース ／ RDB 詳細。

📝 補足：落とし穴の深掘り（重要）

冒頭「落とし穴 5 件」を、 原因まで遡って整理する。 表面の症状だけ覚えても再発するので、 「なぜ起きるか」を押さえる。

1. なぜ多対多は「中間テーブルなし」で表現できないのか

2. 多重度の誤り — 1:N と N:M の取り違え

「1 県に複数の観測値」だけ見て 1:N と決めると、 反対向き（1 観測値は何県に属すか）を検証し忘れる。 両方向を必ず問う。 SSDSE では「1 観測値 ＝ ちょうど 1 県」なので安全に 1:N。 だが「県 ─ 指標」を素朴に結ぶと、 1 県は多指標・1 指標は多県で N:M になり、 連関テーブル（＝観測値）が必要になる。 実は SSDSE の 109 指標列は、 この N:M を暗黙に横持ちで潰した状態と読める。

実データで確かめる：結合キーを 1 つ忘れると、1:1 が N:M になる

多重度の取り違えは、設計図の上だけでなく、分析コードの結合（JOIN・merge）でも起きます。総人口と死亡数を別々の観測テーブルに持っているとして、2 つを結合するときに年度をキーに入れ忘れると何が起きるかを試します。

🎯 このコードでやること：(県コード, 年度) で 1 行ずつの 2 つの観測テーブルを、正しく (県コード, 年度) で結合した場合と、県コードだけで結合した場合の行数と東京都の死亡数の合計を比べ、pandas の validate で誤りを検出する。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df.rename(columns={'SSDSE-B-2026': 'year', 'Code': 'pref_code'})
pop = df[['pref_code', 'year', 'A1101']]        # 観測テーブル A: 総人口
death = df[['pref_code', 'year', 'A4200']]      # 観測テーブル B: 死亡数（同じく県 × 年度で 564 行）

good = pop.merge(death, on=['pref_code', 'year'], validate='one_to_one')
bad = pop.merge(death, on='pref_code')          # 年度を結合キーに入れ忘れる
print(f'正しい結合 {len(good)} 行 / 年度を忘れた結合 {len(bad)} 行（{len(bad) // len(good)} 倍）')

t = good[good['pref_code'] == 'R13000']
tb = bad[bad['pref_code'] == 'R13000']
print(f'東京都の 12 年分の死亡数の合計: 正しい結合 {t["A4200"].sum():,} / 年度を忘れた結合 {tb["A4200"].sum():,}')

try:
    pop.merge(death, on='pref_code', validate='one_to_one')
except pd.errors.MergeError as e:
    print('validate で検出:', e)

💬 正しく結合すると 564 行のままですが、年度を入れ忘れると 6,768 行（12 倍）になります。1 県の 12 行と相手の 12 行がすべて組み合わさり、12 × 12 = 144 行が 47 県分できるためです。このとき東京都の 12 年分の死亡数の合計は、正しい 1,437,762 人に対して 17,253,144 人と 12 倍に水増しされ、エラーも警告も出ません。validate='one_to_one' を付けておけば、キーが一意でないことを MergeError として止めてくれます。

ER 図で「pop と death は (県, 年度) で 1:1」と決めてあれば、結合キーは (県, 年度) の 2 列だと図から読み取れます。結合の前に「この結合は 1:1 か 1:N か」を ER 図で確かめ、pandas なら validate（one_to_one・many_to_one など）、SQL なら結合後の行数の確認をセットにするのが、合計や平均の水増しを防ぐ習慣です。

3. 正規化「不足」と「過剰」は両方が罠

原則は 「まず 3NF、 必要なら意図して崩す」。 崩す判断（非正規化）は性能計測の後に行う。 早すぎる非正規化は技術的負債になる。

4. 弱実体（weak entity）の扱い

単独では識別できず、 親に依存する実体を 弱実体と呼ぶ。 自分だけでは主キーを構成できず、 親の PK＋自分の部分キーで複合 PK を作る（識別関係）。 SSDSE の「観測値」は、 都道府県と年度がないと意味を持たない典型的な弱実体で、 PK は (Code, 年)。 弱実体を強実体と誤認して代理キー（連番 id）だけ振ると、 (Code, 年) の一意性制約を張り忘れ、 重複行が忍び込む。

5. 命名の一貫性

同じ概念が pref_code / prefCode / 都道府県コード / Code と揺れると、 JOIN 条件で人的ミスが多発する。 プロジェクト開始時に snake_case 統一・FK は参照先_id 形式などを文書化し、 SSDSE の日本語列（年度・Code・Prefecture）も論理名へ正規化してから設計に入る。

6. 概念 / 論理 / 物理設計の混同

📝 補足：発展 — 変換・正規化・記法・NoSQL

1. ER 図 → リレーショナルスキーマの機械的変換規則

ER 図は、 ほぼ機械的に表定義へ落とせる。 この 7 つの規則を覚えると設計が速い。

2. 正規化（第 1〜3 正規形）との関係

ER 図の「論理化」は、 実体の各表に正規化を適用する工程そのもの。 テーブルの粒度を決める理論的裏付けが正規形である（本用語集に正規化の独立ページは未整備のため、 ここでは要点をテキストで示す）。

• 1NF：各セルが単一値。 SSDSE の 109 指標列はすべてスカラー → 満たす。
• 2NF：複合 PK (Code, 年) の 一部にしか依存しない属性を分離。 県名・地方区分は Code だけで決まる → prefectures 表へ。
• 3NF：非キー属性間の推移従属を排除。 「地方区分」が「県名」経由で決まるなら、 さらに整理。

3. 記法の違い — IE / IDEF1X / UML

比較表（📊 比較表）に加え、 実務で迷いやすい点を補う。

• IE 記法（クロウフット）：多重度を線の端の記号で表す現代主流。 「鳥の足＝多、 縦棒＝1、 ○＝任意(0)」。
• IDEF1X：米政府調達由来。 識別関係（実線）と非識別関係（破線）を厳密に区別し、 弱実体・FK 伝播を明示する。 弱実体の議論に強い。
• UML クラス図：多重度を 0..1 / 1..* のように数値範囲で書く。 データだけでなく「振る舞い（メソッド）」も持てるのが ER 図との差。

4. NoSQL での設計思想の違い

ER 図＋正規化は「書き込みの整合性」を最優先する RDB の思想。 NoSQL は逆に アクセスパターン駆動で設計する。

ただし NoSQL でも「どの実体がどう関係するか」を把握する意味で ER 的思考は有効で、 スキーマレス＝無設計ではない。 大規模化すると結局スキーマ設計へ回帰する、 というのが歴史セクションで触れた揺り戻しである。