データベース管理システム(DBMS、SQL、トランザクション管理、排他制御)

この章で覚えておきたいこと

• DBMS は、データの定義、検索・更新、同時実行制御、障害回復、セキュリティ管理をまとめて担います。
• SQLは、FROM、JOIN、WHERE、GROUP BY、SELECT、ORDER BY の流れで読むと、実行結果を追いやすくなります。
• INNER JOIN は両方の表に対応がある行だけを残し、LEFT OUTER JOIN は左側の表の行を残します。
• COUNT(*) は行数を数え、COUNT(列名) は NULLを数えない 点が頻出です。
• 条件付き集計では、CASE で分類し、SUM や COUNT で必要な値を集計します。
• トランザクションは、複数の更新をひとまとまりに扱う仕組みです。ACID は原子性、一貫性、独立性、耐久性を表します。
• 排他制御では、ロック粒度が細かいほど同時実行性は高まりやすい一方、管理負荷は増えやすくなります。
• 障害回復では、ロールバックとロールフォワードの違い、分散環境では2相コミットの役割を押さえることが重要です。

基本知識

DBMSが果たす役割

DBMSは、データベースを安全かつ効率的に使うための土台です。単に表を保存するだけでなく、利用者からの要求を整理し、正しい形で読み書きさせる役割を持ちます。

• データ定義機能: 表、列、制約、索引などの構造を定義します。
• データ操作機能: 検索、追加、更新、削除をSQLで実行できるようにします。
• 同時実行制御機能: 複数利用者の処理が衝突しないように調整します。
• 障害回復機能: 障害発生時にログやバックアップを使って整合性を回復します。
• セキュリティ機能: 利用者ごとの権限設定やアクセス制御を行います。

試験では、インデックス、トリガ、ストアドプロシージャ、レプリケーションなどを、どの機能に属するかで判断させる問題が出ます。名称だけではなく、何を実現する仕組みなのかで覚えることが大切です。

SQLを読む基本順序

SQLは記述順のまま読むより、処理の流れに沿って読むほうが理解しやすいです。特に空欄補充や実行結果選択では、この読み順がそのまま解答手順になります。

1. FROM で対象となる表を確認します。 2. JOIN でどの表同士を、どの列で結びつけるかを確認します。 3. WHERE で抽出条件を当てはめ、残る行を絞ります。 4. GROUP BY で集計単位を決めます。 5. SELECT で表示する列や集計結果を確定します。 6. ORDER BY で並び順を確認します。

結果表の1行が何を意味するかを最初に見ると、GROUP BY と集計関数の候補を絞りやすくなります。店舗ごとの集計なのか、商品ごとの集計なのかを先に見抜くことが重要です。

集計問題では、WHERE、HAVING、DISTINCT の位置づけも頻出です。

| 句・指定 | 役割 | 読む位置 | 典型的なひっかけ | |---|---|---|---| | WHERE | 行を絞り込む | 集計前 | 集計後の条件を書く場所ではない | | GROUP BY | 集計単位を決める | 絞り込み後 | 結果表の1行の粒度と合っているかを見る | | HAVING | 集計結果を条件で絞る | 集計後 | COUNT や SUM の結果に対する条件はここ | | DISTINCT | 重複行を除く | 表示列の確定時 | 集計単位を作る命令ではない |

たとえば「店舗ごとの売上合計が10万円以上の店舗だけ」を出すなら、先に店舗で GROUP BY し、合計額に対して HAVING を使います。一方、「東京都の店舗だけ」を対象にするなら、集計前の行条件なので WHERE で絞ります。

JOINと外部結合の考え方

JOIN は複数の表を結びつける処理です。結合の種類は、対応するデータがない行を残すかどうかで判断します。

• INNER JOIN: 両方の表で結合条件に一致する行だけを残します。
• LEFT OUTER JOIN: 左側の表の行をすべて残し、右側に対応がない場合は右側の列がNULLになります。

たとえば、利用者一覧を基準にして、商品登録がない利用者も含めて集計したいなら、利用者表を左側に置いた LEFT OUTER JOIN が必要です。ここで INNER JOIN を使うと、商品登録がない利用者が結果から消えてしまいます。

RIGHT OUTER JOIN も考え方は同じで、右側の表を残す結合です。ただし、試験では「どちらの表を残したいか」を明確にするため、LEFT OUTER JOIN の形で考えるほうが整理しやすいです。

集計SQLとCOUNTの読み分け

集計SQLでは、まず結果表の粒度を読み、その粒度に合わせて GROUP BY を決めます。そのうえで、何を数えるか、何を合計するかを関数で表します。

• COUNT(*): 行数を数えます。NULL行も含めて、残っている行そのものを数えます。
• COUNT(列名): 指定列がNULLでない行だけを数えます。
• SUM(列名): 数値の合計を求めます。
• AVG(列名): 平均値を求めます。
• MAX(列名)、MIN(列名): 最大値、最小値を求めます。

外部結合と組み合わせるときは、COUNT(*) と COUNT(列名) の違いが特に重要です。対応データがない行を0件として扱いたいなら、NULLになる右表の列を COUNT(列名) で数える必要があります。COUNT(*) を使うと、左側の行自体は残っているため1件と数えられてしまいます。

CASE式とGROUP BYによる条件付き集計

条件付き集計では、CASE で条件ごとに値を振り分け、その結果を SUM や COUNT で集計します。試験では、分類条件に使う列と、合計対象の列を取り違えないことが重要です。

たとえば、販売店ごとの販売数量を商品単位で横持ち表示したい場合は、次のように考えます。

• GROUP BY の対象: 商品ごとに1行にしたいので商品IDです。
• CASE の判定対象: 販売店ごとに分けたいので販売店IDです。
• SUM の対象: 足し合わせたいので販売数量です。

件数を数えたいなら THEN 1 の形が使えますが、数量や金額を合計したいなら THEN 販売数量 や THEN 金額 のように、実際に合計したい列を返す必要があります。この違いは空欄補充でよく狙われます。

トランザクションとACID

トランザクションは、複数の処理を一体として扱い、途中だけ成功する状態を防ぐ仕組みです。代表例は振込処理で、出金だけ成功して入金が失敗する状態は許されません。

ACIDは、トランザクションが満たすべき代表的な性質です。

• 原子性: 全部成功するか、全部取り消すかのどちらかにします。
• 一貫性: 制約や整合性を壊さず、処理前後で矛盾のない状態を保ちます。
• 独立性: 同時実行しても、順番に実行した場合と同じ結果になるようにします。
• 耐久性: コミット後の結果は、障害が起きても失われないようにします。

試験では、原子性と耐久性、独立性と一貫性を入れ替えさせる選択肢がよく出ます。用語だけで覚えず、具体例と結びつけて理解することが必要です。

排他制御とロック

排他制御は、複数の利用者が同じデータを同時に扱っても不整合が起きないようにする仕組みです。代表的な方法がロックです。

• 行単位ロック: 必要な行だけをロックするため、同時実行性は高まりやすいです。
• 表単位ロック: 表全体をロックするため管理は単純ですが、他の処理を待たせやすくなります。

ここで重要なのは、ロック粒度の大小と性能の関係です。

• 粒度が細かい: 同時実行性は高まりやすいが、ロック管理の負荷は増えやすいです。
• 粒度が大きい: 管理負荷は下がりやすいが、待ち時間が増えやすいです。

試験では、排他制御の目的そのものに加えて、このトレードオフを問う選択肢が出ます。単純に「細かいほうが優れている」とは言えない点を押さえてください。

障害回復と分散トランザクション

障害回復では、バックアップと更新ログを組み合わせて整合性を戻します。似た用語が多いため、処理の向きを意識して整理することが大切です。

• ロールバック: 未完了の処理や取り消すべき処理を元に戻します。
• ロールフォワード: バックアップから復元した後、更新ログを適用して障害直前の状態へ進めます。

ロールバックは「戻す」、ロールフォワードは「進める」と覚えると区別しやすくなります。

分散環境では、複数のデータベースや資源にまたがる更新を同時に確定させる必要があります。このとき使われる代表的な仕組みが2相コミットです。2相コミットは、全参加者が確定可能かを確認してから、全体を一斉にコミットまたは中止させます。これにより、一部だけ確定してしまう不整合を防ぎます。

DBMSの周辺機能

DBMSには、日常的な運用を支える周辺機能も多くあります。用語問題では、次の役割を区別できるようにしておく必要があります。

• インデックス: 検索を高速化するための索引です。
• ストアドプロシージャ: 一連のSQL処理をDBMS側に保存し、呼び出せるようにしたものです。
• トリガ: 追加、更新、削除などをきっかけに、自動的に処理を実行する仕組みです。
• レプリケーション: データを複製し、可用性や参照性能の向上に役立てる仕組みです。

これらは名称が似ていなくても、出題では「何を実現する機能か」で選ばせることが多いです。索引は高速化、自動実行はトリガ、処理の部品化はストアドプロシージャというように、役割で結びつけて覚えてください。

この章のまとめ

• DBMSは、データ操作だけでなく、同時実行制御、障害回復、権限管理まで含めて扱います。
• SQLは、結果表の1行の意味を確認し、FROM から ORDER BY まで処理順で読むと整理しやすいです。
• 外部結合では、どちらの表を残したいかを先に決め、COUNT(*) と COUNT(列名) の違いまで意識します。
• 条件付き集計では、CASE の判定列、SUM や COUNT の対象列、GROUP BY の粒度を切り分けます。
• トランザクションでは ACID を具体例で説明できることが重要です。
• 排他制御は、データ保護と同時実行性の両立を図る仕組みであり、ロック粒度にはトレードオフがあります。
• 障害回復では、ロールバックは戻す処理、ロールフォワードは進める処理として区別します。
• DBMSの周辺機能は、インデックス、トリガ、ストアドプロシージャ、レプリケーションの役割差を押さえます。

一次試験過去問での出方

2025年第12問では、利用者をすべて残す集計SQLが出題され、LEFT OUTER JOIN と COUNT(列名) の組み合わせを正しく読めるかが問われました。

2024年第8問では、CASE と SUM と GROUP BY を組み合わせた条件付き集計が出題され、分類に使う列と合計対象の列を切り分ける力が求められました。

2023年度第2回第4問では、表の結合後に店舗単位で集計し、並び順まで含めてSQLの実行結果を読む問題が出ました。

2020年第7問では、ACIDの各性質が問われ、原子性、一貫性、独立性、耐久性を具体例で説明できるかが重要でした。

2017年第9問では、ロック粒度、ロールバック、ロールフォワード、2相コミットがまとめて問われ、排他制御と障害回復の基本理解が試されました。

2023年度第1回第5問では、インデックス、ストアドプロシージャ、トリガ、レプリケーションなどのDBMS機能が出題され、名称より役割で判断することが求められました。