データベース(正規化・SQL・トランザクション)

基本情報技術者試験(FE)は2023年4月からCBT方式の通年実施となり、科目A(四肢択一60問・90分)と科目B(20問・100分)で構成され、1,000点満点中それぞれ600点以上で合格となる。データベースは、IPAが公表する試験要綱・シラバスにおいてテクノロジ系「大分類3:技術要素」の「中分類9:データベース」に位置づけられ、科目Aで毎回2〜4問程度の出題が続く定番分野である。出題の核は三つ、すなわち①関係データベースの設計(E-R図・キー・正規化)、②SQLによるデータ操作、③トランザクション管理(排他制御・障害回復)である。データベース管理システム(DBMS)は、データの一元管理、複数利用者による同時実行制御、機密保護、障害回復といった機能を提供するソフトウェアであり、アプリケーションプログラムとデータを分離することでデータ独立性を実現する。本章ではこの三本柱を、本試験で実際に問われてきた形に沿って体系的に整理する。

データベース設計ではまずANSI/SPARCの3層スキーマを押さえる。個々の利用者やアプリケーションから見たデータの見方を定義するのが外部スキーマ(ビューに相当)、データベース全体の論理構造を定義するのが概念スキーマ(表定義に相当)、記憶装置上の物理的な格納方法を定義するのが内部スキーマである。3層に分離することで、物理構造の変更が論理構造や利用者側に波及しないデータ独立性が確保される。概念設計ではE-R図(実体関連図)を用い、実体(エンティティ)、関連(リレーションシップ)、および1対1・1対多・多対多といった多重度(カーディナリティ)を表現する。関係モデルにおけるキーの用語も頻出であり、行を一意に識別できる属性または属性の組を候補キー、その中から設計者が選んだものを主キーという。主キーには重複値もナル(NULL)も許されない(一意性制約と非ナル制約)。また、他の表の主キーを参照する属性を外部キーといい、参照先の表に存在しない値の登録を禁止する参照制約(参照整合性)が課される。

正規化は、挿入・更新・削除時の不整合(更新時異状)を防ぐために表を分割し、データの重複を排除する設計技法であり、この章で最も出題実績が厚い論点である。判定の道具は関数従属である。属性Aの値が決まれば属性Bの値が一意に決まるとき「BはAに関数従属する」といい、主キーの一部の属性だけで非キー属性が決まる従属を部分関数従属、主キー→A→Bのように間接的に決まる従属を推移的関数従属という。段階は次のとおりである。繰返し項目を排除し、全ての属性が単一値をもつ状態が第1正規形。第1正規形であって、全ての非キー属性が主キーに完全関数従属する(部分関数従属を排除した)状態が第2正規形。第2正規形であって、非キー属性から非キー属性への推移的関数従属を排除した状態が第3正規形である。本試験では「この表は第何正規形か」「第3正規形にするにはどの表に分割すべきか」という形式で問われるため、受注伝票のような具体的な表を実際に分割する練習が必須である。なお、正規化を進めるほど結合演算が増えて検索性能が低下し得るため、性能を優先して意図的に非正規化を残す設計判断が問われることもある。

SQLはJIS X 3005(ISO/IEC 9075)として規格化されたデータベース言語であり、表の定義や権限管理を行うデータ定義言語(DDL:CREATE TABLE、GRANT・REVOKE等)と、データ操作言語(DML:SELECT・INSERT・UPDATE・DELETE)に大別される。科目Aの中心はSELECT文である。基本形はSELECT列名 FROM表名 WHERE条件であり、重複行を除くDISTINCT、並べ替えのORDER BY(既定は昇順ASC、降順はDESC)を組み合わせる。集計ではCOUNT・SUM・AVG・MAX・MINの集合関数とGROUP BY句を用い、グループ化した後の絞り込みはWHEREではなくHAVING句で行う。「WHEREは個々の行への条件、HAVINGはグループへの条件(集合関数を書ける)」という区別は繰り返し出題されている。複数表の結合では、両表で一致する行だけを返す内部結合(INNER JOIN)と、一致しない行も片側の表から残して返す外部結合(LEFT/RIGHT OUTER JOIN)の違い、およびINやEXISTSを用いた副問合せが問われる。さらにCREATE VIEWで定義するビューは実表から導出される仮想表であり、利用者に必要な列・行だけを見せることで機密保護と外部スキーマの実現に寄与する。

トランザクションとは、それ以上分割できない一連の処理単位であり、DBMSはその実行にACID特性を保証する。原子性(Atomicity)は処理が「全て実行される」か「全く実行されない」かのいずれかで終了すること、一貫性(Consistency)はデータベースの整合性制約が常に保たれること、独立性(Isolation:隔離性)は同時実行される複数のトランザクションが互いに干渉せず、直列に実行した場合と同じ結果になること、耐久性(Durability)はコミットした更新結果が障害後も失われないことを指す。同時実行制御(排他制御)ではロック方式が基本であり、読取り時に掛ける共有ロックは他のトランザクションの共有ロックと両立するが、更新時に掛ける専有(排他)ロックは他のいかなるロックとも両立しない。2つのトランザクションが互いに相手の保持する資源のロック解除を待ち合い、どちらも処理を進められなくなる状態がデッドロックであり、DBMSは一方を強制的にロールバックさせて解消する。また、ロックの粒度(表単位か行単位か)を細かくすると同時実行性は向上するが、ロック管理のオーバーヘッドが増えるというトレードオフも定番の出題である。

障害回復の仕組みはログ(ジャーナル)ファイルが土台である。DBMSは更新前情報(更新前ログ)と更新後情報(更新後ログ)を記録し、定期的にチェックポイントを設けてメモリ上の更新内容をディスクへ書き出す。トランザクション障害や未コミットの処理は、更新前情報を用いて開始前の状態に戻すロールバック(後退復帰)で対処する。システム障害後の再始動では、チェックポイント以降にコミットが完了していたトランザクションの結果を更新後情報を用いて反映し直すロールフォワード(前進復帰)を行い、未コミットのものはロールバックする。媒体障害(ディスク故障)では、バックアップファイルを復元した上で更新後情報によるロールフォワードで障害直前の状態まで回復する。この「どの障害に、どちらのログで、どちらの復帰か」という対応関係が科目A頻出である。加えて、検索を高速化する索引(インデックス:B+木構造が代表)、分析用に統合したデータウェアハウス、キーバリューストアやドキュメント指向データベースなどのNoSQL、分散環境で厳密な一貫性より可用性を優先するBASE特性といったシラバス掲載の周辺用語も近年出題されており、用語と目的を対で押さえておく。