データベース ― 設計・SQL・トランザクション

データベース方式と 3 層スキーマ

データベースは「データを一元管理し、複数の利用者・プログラムから矛盾なく共有する」ために存在します。その管理を担うソフトウェアが DBMS（Database Management System：データベース管理システム）で、機能は次の 3 つに大別されます。

• 定義機能: スキーマ（表・列・制約・ビュー）を定義する。SQL では DDL が担当
• 操作機能: データの検索・挿入・更新・削除を行う。SQL では DML が担当
• 制御機能: 同時実行制御（排他制御）、障害回復、機密保護（アクセス権）、保全（一貫性制約）を行う

DBMS が管理するメタデータ（データについてのデータ。表名・列名・データ型・制約・統計情報など）を集めたものをデータディクショナリ（データ辞書）といいます。設計段階の名称ゆれ（異音同義語・同音異義語）を抑えるためのデータ項目標準化も、ここに集約します。

3 層スキーマアーキテクチャ

3 層スキーマは、データの見え方を 3 段に分けてデータ独立性を確保する考え方です。上下の層を切り離しておくと、片方を変えてももう片方を直さずに済みます。

データモデルとデータベースの種類

論理データモデルは「データの論理構造をどう表すか」の型です。試験で問われるのはほぼ関係モデルですが、種類の名前と構造の対応は用語問題として出ます。

関係モデルの用語は科目 B の記述でも使うので、別名まで対応づけて覚えます。関係（リレーション）＝表、タプル＝行（組）、属性＝列（フィールド）、定義域（ドメイン）＝属性が取りうる値の集合、実現値＝実際に格納された値、関係スキーマ＝関係の構造の定義です。関係は「タプルの集合」なので、同じタプルが 2 つ存在せず、行にも列にも順序の意味はありません。

データ分析の進め方には、企業データモデルから降ろすトップダウンアプローチと、既存の帳票・画面から積み上げるボトムアップアプローチがあり、データを中心に据えて業務を設計する考え方を DOA（Data Oriented Approach：データ中心アプローチ）といいます。

概念設計 ― E-R 図と多重度

概念設計では、業務に登場する「もの・こと」をエンティティ、その性質を属性、エンティティ間の関わりをリレーションシップとして整理し、E-R 図（Entity-Relationship Diagram）に描きます。試験で問われるのはほぼ 1 点、関連線の向き（カーディナリティ＝多重度）です。

多重度は感覚で決めず、表定義から機械的に導きます。一方の主キーが、他方に外部キーとして入っていれば、主キー側が「1」・外部キー側が「多」です。どちらにも入っていなければ多対多を疑い、両方の主キーを持つ連関エンティティ（交差エンティティ）に分解します。

設計判断でよく使う型

• サロゲートキー（代用キー）: 業務上のコードが変更されうる・桁数が変わりうるとき、意味を持たない連番の主キーを別に立てる。業務コードは一意性制約で守る
• スーパータイプ／サブタイプ: 「講座」の共通属性を親に、「対面講座」「オンライン講座」の固有属性を子に分ける
• 自己参照関連: 「社員」の上司も社員、「組織」の親も組織。同じ表を 2 回使うので、SQL では自己結合になる
• 履歴保持: 単価や住所が変わっても過去伝票を再現したいときは、伝票側に発生時点の値を持たせるか、有効期間（開始日・終了日）を属性に追加する

正規化 ― 関数従属と第 1〜第 3 正規形

正規化の目的は更新時異状（更新の抜け・矛盾）の排除です。1 つの表に何でも詰め込むと、顧客名を直すのに何百行も更新することになり、直し漏れが矛盾になります。正規化はこれを「1 つの事実は 1 か所に」の形へ分解する手続きです。

土台は関数従属です。属性 X の値が決まれば属性 Y の値が 1 つに決まるとき、Y は X に関数従属するといい、X → Y と書きます。ここから 3 つの型が出ます。

• 完全関数従属: 複合キーのすべてが揃って初めて決まる（受注番号 + 商品コード → 数量）
• 部分関数従属: 複合キーの一部だけで決まってしまう（受注番号 → 受注日）
• 推移的関数従属: キー → A → B と 2 段で決まる（受注番号 → 顧客コード → 顧客名）

候補キーが 1 つしかなく、それが単一の属性なら、第 3 正規形とボイスコッド正規形は一致します。両者が食い違うのは、候補キーが複数あって属性を共有している場合だけです。

逆に、分解しすぎると結合の回数が増えて検索が遅くなります。非正規化は、更新が少なく参照が圧倒的に多い表であえて冗長を残し、結合を減らす性能設計です。集計値をあらかじめ持つ（派生属性）のも同じ考え方で、更新時に矛盾させない仕掛け（トリガや夜間バッチ）とセットにします。

キーと制約 ― 参照整合性とビュー

キーは「行を特定するための属性の組」です。行を一意に決められる属性の組すべてが候補キー、その中から設計者が 1 つ選んだものが主キーで、主キーには一意性制約と非 NULL 制約が同時に効きます。選ばれなかった候補キーは代替キーです。外部キーは、他の表（または自表）の主キー・候補キーを参照する属性で、これによって表と表がつながります。

参照整合性（参照制約）は「外部キーの値は、参照先に必ず実在する」という約束です。この約束を壊す操作だけが拒否される、という向きを取り違えないことが得点の分かれ目になります。

CREATE TABLE 社員 (
  社員番号   CHAR(6)      NOT NULL,
  氏名       VARCHAR(40)  NOT NULL,
  メール     VARCHAR(120) UNIQUE,
  部門コード CHAR(4),
  基本給     INTEGER      CHECK (基本給 >= 0),
  PRIMARY KEY (社員番号),
  FOREIGN KEY (部門コード) REFERENCES 部門(部門コード)
    ON DELETE SET NULL
);
ALTER TABLE 社員 ADD CONSTRAINT uq_氏名_部門 UNIQUE (氏名, 部門コード);

後から制約を足すときは ALTER TABLE を使います。既存データに重複があると UNIQUE の追加は失敗するので、先に重複を 1 件だけ残して削除する、という順序が科目 B でも問われます。

ビューとアクセス権

ビュー（導出表）は、SELECT 文に名前を付けたものです。実データは持たず、参照されるたびに元の実表から作られます。利用者に見せたい列・行だけを切り出せるので、外部スキーマとアクセス制御の両方を担います。

CREATE VIEW 営業部社員 (社員番号, 氏名) AS
  SELECT 社員番号, 氏名 FROM 社員 WHERE 部門コード = 'S001';

GRANT SELECT, INSERT ON 営業部社員 TO 営業管理者;
REVOKE INSERT ON 営業部社員 FROM 営業管理者;

ビューへの更新は、その 1 行が元表のどの行に対応するか一意に決まる場合だけ可能です。射影と選択だけで作ったビューは更新可能、集約関数・GROUP BY・DISTINCT・UNION を含むビューは更新不可と覚えます。列名はビュー定義で別名を付けられます。

権限は GRANT 権限 ON 対象 TO 利用者 で与え、REVOKE で取り消します。権限の種類は SELECT（参照）・INSERT（挿入）・UPDATE（更新）・DELETE（削除）などで、WITH GRANT OPTION を付けると受け取った側が他人へ再付与できます。

関係代数と SQL ― 結合・集計・副問合せ

関係データベースの操作は、集合の演算として定義されています。SQL はこの関係代数に文法を与えたものなので、演算の名前と結果の形を先に押さえると SQL の読み違いが減ります。

和・差・共通は、列数と各列の定義域が一致していること（和両立）が前提です。自然結合は、同名の列で等結合したうえで重複する列を 1 つにまとめた結合を指します。

SELECT 文は書いた順ではなく決まった順で評価される

SQL の各句は、記述順ではなく評価順で動きます。WHERE に集約関数を書けない理由も、SELECT で付けた別名を WHERE で使えない理由も、この順序で説明できます。

結合と NULL

結合は「どちらの表の行を残すか」で型が分かれます。実績が無い側も残したいという要件が出たら外部結合、というのが科目 B の定番パターンです。

SELECT S.部門コード, B.部門名,
       COALESCE(SUM(T.金額), 0) AS 売上合計
FROM   部門 B
       LEFT OUTER JOIN 売上 T ON B.部門コード = T.部門コード
       INNER JOIN 部門 S ON S.部門コード = B.部門コード
GROUP BY S.部門コード, B.部門名
HAVING COALESCE(SUM(T.金額), 0) < 1000000
ORDER BY 売上合計 DESC;

外部結合で対応が無かった側は NULL になり、SUM は NULL を無視するのでグループ全体が NULL になります。0 で見せたいときは COALESCE（最初の非 NULL を返す）で置き換えます。NULL は「値が無い」ことを表す印なので、= NULL では判定できず IS NULL を使い、比較・算術に混ざると結果も NULL になります。

集計では、SELECT に書ける非集約の列は GROUP BY に並べた列だけ（SQL:1999 以降は GROUP BY 列に関数従属する列を例外的に許す規格・実装もありますが、試験対策としては原則どおりに考えます）、という規則が効きます。COUNT(*) は行数、COUNT(列) は NULL を除いた件数で、両者の差がひっかけになります。

副問合せと相関副問合せ

副問合せ（サブクエリ）は SELECT 文の中の SELECT 文です。内側が外側の別名を参照していれば相関副問合せで、外側の 1 行ごとに内側が評価し直されます。参照していなければ 1 回だけ評価される単純な副問合せです。

SELECT COUNT(*) FROM 社員 A
WHERE NOT EXISTS (
  SELECT 1 FROM 出張申請 B
  WHERE B.社員番号 = A.社員番号 );

SELECT 部門コード, 氏名, 基本給,
       RANK() OVER (PARTITION BY 部門コード ORDER BY 基本給 DESC) AS 部門内順位,
       CASE WHEN 基本給 >= 400000 THEN '上位' ELSE '一般' END AS 区分
FROM   社員
WHERE  部門コード IN (SELECT 部門コード FROM 部門 WHERE 拠点 = '東京');

ウィンドウ関数は、GROUP BY のように行をまとめずに、行を保ったまま集計・順位付けを行う仕組みです。OVER (PARTITION BY ... ORDER BY ...) で「どの範囲を、どの順に見るか」を指定します。シラバス Ver.7.2 の用語例に入っており、直近では令和 6 年秋に出題されました。

集合演算は UNION（和・重複除去）、UNION ALL（和・重複そのまま）、EXCEPT（差）、INTERSECT（共通）です。UNION は重複除去のための処理（多くはソート、実装によってはハッシュ集約）が入るので、重複がないと分かっているなら UNION ALL のほうが速くなります。

DDL・DML・DCL と、プログラムからの利用

SQL は役割で 3 つに分かれます。DDL（データ定義言語）は CREATE／ALTER／DROP、DML（データ操作言語）は SELECT／INSERT／UPDATE／DELETE、DCL（データ制御言語）は GRANT／REVOKE／COMMIT／ROLLBACK です（IPA のシラバスは DCL という区分を使わず、DDL／DML の 2 分類で GRANT 文などを「その他のデータ操作言語」として扱います）。

SQL を単独で使うのが独立言語方式（会話型 SQL）、他の言語の中に埋め込むのが親言語方式（埋込み SQL）です。埋込み SQL では、複数行が返る問合せをプログラムの変数へ 1 行ずつ受け取るためにカーソルを使い、DECLARE（宣言）→ OPEN（開始）→ FETCH（読込み）→ CLOSE（終了）の順に操作します。値を渡すときは :変数名 の形の埋込み変数を使います。

ストアドプロシージャは、一連の SQL を DBMS 側に登録しておき、名前で呼び出す仕組みです。1 回の呼出しで複数の SQL がサーバ内で実行されるので、クライアントとサーバ間の通信量が減るのが最大の利点で、この 1 点がそのまま繰り返し出題されています。あらかじめ構文解析・最適化を済ませられる点も利点です。これに対しトリガは、表への INSERT／UPDATE／DELETE を契機に自動起動する処理で、呼び出して使うものではありません。

なお、利用者の入力を文字列連結で SQL に埋め込むと SQL インジェクションを招きます。対策はプレースホルダ（バインド機構）による値の受け渡しで、詳細は第 12 章で扱います。

物理設計 ― インデックスと性能

物理設計は、アクセス効率と記憶効率の観点で「どう格納するか」を決める工程です。試験で問われるのはほぼインデックスの選び方で、B+ 木とハッシュの得意分野の対比が固定の出題パターンになっています。

インデックスは万能ではありません。更新のたびに索引も書き換わるので、索引を増やすほど INSERT／UPDATE／DELETE は遅くなります。また、値の種類が少ない列（選択率が悪い列）や、列に関数を適用した条件、前方一致でない LIKE '%語%' では効かないことが多く、表全体を読む処理では索引を使わないほうが速い場合もあります。どの経路で実行するかを統計情報から判断するのがコストベースオプティマイザで、統計情報が古いと誤った実行計画を選びます。

そのほかの物理設計の道具として、行を条件で分ける水平分割、列を使用頻度で分ける垂直分割、ディスク容量見積り、最適ブロック（ページ）長の設計、圧縮（コンプレッション）と伸張（デコンプレッション）があります。運用開始後は、断片化した領域を詰め直す再編成と、実行計画を改善するチューニングが保守作業になります。

トランザクションと同時実行制御

トランザクションは「これ以上分けられない一連の処理」です。振替なら「A 口座から引く」と「B 口座へ足す」で 1 つ。途中で止まると口座の合計が合わなくなるので、DBMS は次の 4 性質（ACID 特性）を保証します。

ロックと両立性

同時実行制御の基本はロックです。読むだけなら他人と共有してよいので共有ロック（S ロック）、書くなら独占しなければならないので専有ロック（X ロック）を掛けます。両立するのは共有ロック同士だけです。

ロックを掛ける単位がロック粒度です。行単位に細かくすると同時実行性は上がりますが、管理するロックの数と処理の負荷が増えます。表単位に粗くすると管理は軽くなりますが、待たされる相手が増えます。

2 相ロッキングプロトコルは、トランザクションを「ロックを獲得していく成長相」と「解放していく縮退相」に分け、いったん解放を始めたら新たなロックを取らない、という規則です。これを守ると直列化可能性が保証されますが、デッドロックは防げません。

デッドロックへの対処は、検出して片方をロールバックする（犠牲者の選択）か、そもそも起こさないように資源へのアクセス順序を全トランザクションで統一するかです。タイムスタンプを使う予防方式には、後発を諦めさせる Wait-Die 方式と、先発が後発を強制終了させる Wound-Wait 方式があります。

ロックを使わない方式が多版同時実行制御（MVCC）です。更新時に古い版（バージョン）を残し、参照する側は自分が開始した時点の版を読みます。読み手と書き手が待ち合わせないので同時実行性が高く、直近では令和 7 年秋に出題されました。更新の衝突を事前にロックで防ぐのが悲観ロック、更新直前に「他人に変えられていないか」を確認する（更新前後で版番号を比較する）のが楽観ロックです。

隔離性水準と 3 つの異常

独立性を厳しくするほど安全ですが同時実行性は落ちます。そのトレードオフを段階にしたのが隔離性水準で、水準ごとに許してしまう異常が決まっています。

• ダーティリード: 他人がまだコミットしていない値を読んでしまう
• ノンリピータブルリード: 同じ行を 2 回読むと、間に更新されて値が変わっている
• ファントムリード: 同じ条件で 2 回検索すると、間に挿入された行が増えている

長時間の処理を 1 つのトランザクションにまとめられない場面では、後から打ち消す処理を用意する補償トランザクションを組み合わせます。複数サービスにまたがる更新を、ローカルトランザクションと補償で連鎖させる設計が Saga パターン、仮押さえ・確定・取消の 3 段階に分けるのが TCC パターンです。同じ要求が二重に届いても結果が変わらない性質（べき等性）を持たせておくと、再送時の二重登録を防げます。

障害回復 ― ログ・チェックポイント・2 相コミット

DBMS はデータを更新する前に、更新前の値（更新前ログ）と更新後の値（更新後ログ）をジャーナルファイル（ログファイル）へ先に書きます。この順序を守るからこそ、後から「戻す」も「やり直す」もできます。

チェックポイントは、その時点でメモリ上のバッファをディスクへ書き出し、印をログに記録する処理です。回復のたびにログの先頭まで遡らずに済み、再開時間が短くなります。

バックアップは、全体を取るフルバックアップ、前回のフルバックアップからの変更分を取る差分バックアップ、前回のバックアップ（種類を問わず）からの変更分を取る増分バックアップがあります。差分は復元がフル 1 本＋差分 1 本で済み、増分は取得が速い代わりに復元でフル 1 本＋増分すべてが要ります。

そのほか、更新をページ単位の写しに書いて最後に切り替えるシャドウページ法、ログを使った回復処理を伴う再起動をウォームスタート、ログを使わず初期状態から立ち上げる再起動をコールドスタートと呼びます。

分散環境のコミット ― 2 相コミット

更新が複数のデータベースにまたがると、片方だけコミットされる事態を避ける必要があります。そこで、調停者（コーディネータ）が全参加者に「コミットできるか」を尋ねてから確定させるのが 2 相コミットです。

危ないのは、調停者が第 2 相の決定を送る直前に落ちる場合です。参加者はコミットともロールバックとも決められないまま資源をロックし続けます（ブロッキング）。この弱点を突く出題が令和 6 年秋にありました。

NoSQL・分散データベースとデータ活用

関係データベースは表とスキーマを固定して整合性を守る代わりに、水平方向への拡張が苦手です。そこで、用途を絞って拡張性・柔軟性を優先したのが NoSQL（Not only SQL）です。直近 3 年で最も出題が伸びた領域で、「この要件ならどの型か」を選ばせる形が増えています。

分散システムでは CAP 定理が効きます。一貫性（Consistency）・可用性（Availability）・分断耐性（Partition tolerance）の 3 つを同時に完全には満たせず、ネットワーク分断が起きたときに一貫性と可用性のどちらを取るかを選ぶことになる、という主張です。ここで可用性を優先する立場の性質が BASE（Basically Available, Soft state, Eventually consistent）で、更新直後は不揃いでも、やがて全複製が同じ値に落ち着く結果整合性を許します。

分散データベースでは、利用者に「分散していること」を意識させない透過性が目標です。位置の透過性（どのサイトにあるか意識しない）、複製に対する透過性（複製の存在と数を意識しない）、分割に対する透過性（表が分けられていることを意識しない）などがあります。表を行で分けて配置するのが水平分散、列で分けるのが垂直分散、同じ内容の複製を複数サイトに持つのがレプリケーション、キーの範囲でデータを別サーバへ振り分けて負荷を分けるのがシャーディングです。ネットワーク分断で両系が「自分が主」と思い込む状態をスプリットブレインといいます。

分析のためのデータベース

日々の業務処理（OLTP）と分析（OLAP）では、求められる形が違います。OLTP は小さな更新を大量にさばくため正規化しますが、OLAP は大量の行をまとめて集計するので、あえて非正規化した構造を使います。

これらを支える基盤として、大規模データを複数サーバへ分散して処理する Apache Hadoop（分散ファイルシステム HDFS）や Apache Spark、その上で SQL に近い問合せを実行する Apache Hive・Presto があります。データの意味・所在を管理するのがデータディクショナリや IRDS（情報資源辞書システム）で、開発・保守の情報まで一元管理する仕組みをリポジトリと呼びます。

名前だけ押さえておく用語

シラバス Ver.7.2 の用語例のうち、直近 10 年で単独出題の実績が無いものです。深追いは不要ですが、他の肢を消すのに使えるので「どの分野の語か」だけ結び付けておきます。

ひっかけポイント

科目 A の誤答肢は、正解の記述を「1 段ずらす」「向きを逆にする」「別の用語の説明とすり替える」の 3 通りで作られます。本章で実際にその形が繰り返されている 5 か所を、作られ方ごと潰しておきます。

理解度チェック（科目 A 形式）

まとめ

この章は「判定の手順を体に入れる」ことがそのまま得点になります。下の表は、科目 A で問われる形に絞った総まとめです。

• 科目 A は直近 10 年（19 回）で 93 問、1 回平均 4.9 問。上位 5 テーマ（正規化 8 回、ロールフォワード／ロールバック 6 回、参照制約 6 回、概念データモデル 6 回、インデックス 6 回）だけで 32 問を占めます
• 上位 15 テーマまで広げると 93 問中 66 問（約 71 %）。本章の表と図をひととおり言葉で説明できれば、目標の 4 問は届く範囲です
• 同一問題・同趣旨の再出題が多い分野です。図 9-3（正規化の分解）・図 9-4（参照制約）・図 9-7（B+ 木とハッシュ）・図 9-9（回復の判定）の 4 枚は、直前の見直しでそのまま使えます

次章では、この DBMS を利用者へ届ける通信基盤を扱います。第 10 章「ネットワーク」で、プロトコルの階層とアドレス設計を押さえてください。