Database Specialist Examination cheat sheet

概念データモデルは業務上の実体（エンティティ）と関連を、DBMSや物理構造に依存せずに表現する。属性・識別子・多重度を定義する。

関連の多重度は両端のカーディナリティ（1対1、1対多、多対多）とオプショナリティ（必須/任意）で表す。

多対多の関連は関連実体に分解し、両端の識別子を組み合わせた主キーまたは代理キーを持たせる。

エンティティ識別子は業務的に一意・不変・非NULLであることを条件に選び、候補キーから主キーを決定する。

サブタイプは排他的/非排他的、必須/任意を明示する。非排他的なら複数のサブタイプに同時所属できる。

データ標準化では項目名、コード体系、ドメイン（型・桁数・値域・単位）を統一し、メタデータとして登録する。

リポジトリにはデータモデル、項目辞書、CRUD図、系譜（lineage）、変換規則を格納し、影響範囲分析に使う。

CRUD図でC（作成）がない実体は参照専用マスタの可能性が高く、業務プロセスの抜けを確認する。

概念→論理→物理の3層で設計を進め、各層の対応関係をリポジトリで追跡可能にする。

第1正規形：属性を原子値にし、繰り返し項目を排除する。

第2正規形：複合主キーの真部分集合への関数従属（部分関数従属）を排除する。

第3正規形：非キー属性間の関数従属（推移的関数従属）を排除する。

BCNF：すべての非自明な関数従属の決定項が候補キーであること。第3正規形より厳しい条件である。

第4正規形：自明でない多値従属を排除する。第5正規形：自明でない結合従属を排除する。

関数従属X→YのXを決定項、Yを被決定項と呼ぶ。アームストロングの公理（反射・増加・推移）で従属を導出できる。

論理設計では正規化後に、性能要件に応じて導出列や集約表を検討し、更新時の同期方法を決める。

物理設計では表領域、ブロックサイズ、PCTFREE、パーティション、索引、圧縮、格納順を決める。

代理キーは業務キーの変更に強いが、業務上の一意性は別途UNIQUE制約で保証する。

DDLはCREATE/ALTER/DROP/TRUNCATE、DMLはSELECT/INSERT/UPDATE/DELETE/MERGE、DCLはGRANT/REVOKEである。

TRUNCATEは行単位のログを取らない実装が多く、DDL扱いで暗黙コミットされる場合がある。

結合はINNER、LEFT/RIGHT/FULL OUTER、CROSS、自己結合。外部結合は一致しない行をNULLで補う。

NULLは不明値であり、= NULLや<> NULLはUNKNOWNになる。判定はIS NULL/IS NOT NULLを使う。

NOT INの部分問い合わせにNULLが含まれると、全体がUNKNOWNになり行が返らないことがある。

GROUP BYで集約し、HAVINGで集約後の条件を指定する。WHEREは集約前の行に適用する。

ウィンドウ関数はOVER（PARTITION BY ... ORDER BY ...）で行を残したまま集計・順位付けを行う。

相関副問い合わせは外側の行ごとに評価される。EXISTSは存在判定のみで列値を返さない。

トランザクション制御はCOMMIT/ROLLBACK/SAVEPOINT。分離レベルはSET TRANSACTIONで指定する。

ACID：原子性（全部成功か全部失敗）、一貫性（制約を満たす）、独立性（相互干渉しない）、永続性（コミット後は失われない）。

分離レベルの異常：READ UNCOMMITTEDはダーティリード、READ COMMITTEDは非再現リード、REPEATABLE READはファントムリードが残る。SERIALIZABLEはすべて防ぐ。

共有ロック（S）は読み取り、排他ロック（X）は更新に使う。S同士は共存できるが、Xは他のS/Xと共存できない。

二相ロック（2PL）は拡張相で取得のみ、縮小相で解放のみを行い、直列化可能性を保証する。

デッドロックは待ちグラフの閉路で検出し、一方をロールバックする。タイムアウト方式もある。

MVCCでは読み取りがスナップショットを参照するため読み取りと書き込みがブロックしにくいが、書き込み競合時は直列化失敗で再試行する。

WALではデータページの書き込み前にログを永続化する。障害回復はREDO（ロールフォワード）とUNDO（ロールバック）で行う。

チェックポイントは回復開始点を進め、REDOの対象を減らす。媒体障害はバックアップとアーカイブログで復旧する。

参照整合性は外部キーで保証し、削除・更新時の動作をCASCADE/SET NULL/RESTRICT/NO ACTIONで指定する。

実行計画は結合順序、結合方式（ネステッドループ、ハッシュ、マージ）、索引アクセス、ソートの有無を確認する。

B-tree索引は等価・範囲検索に有効。複合索引は先頭列を含む条件で使われやすい。

カバリング索引は問い合わせ列をすべて含み、表本体のアクセスを避けてI/Oを削減する。

統計情報が古いとオプティマイザのコスト見積りが誤り、適切な計画が選ばれない。

パーティショニングはレンジ/リスト/ハッシュ/コンポジット。条件にパーティションキーを含めるとプルーニングが効く。

セキュリティは最小権限、ロールによる権限管理、監査ログ、保存時・転送時の暗号化、マスキングで構成する。

バックアップはフル/差分/増分とアーカイブログを組み合わせ、RPO（目標復旧時点）とRTO（目標復旧時間）で方式を決める。

レプリケーションは同期（整合性重視）と非同期（性能・可用性重視）に分かれ、遅延とデータ欠損の許容度で選ぶ。

DWHは分析用にスタースキーマなどで再構成し、データレイクは生データをスキーマオンリードで蓄積する。ETL/ELTとCDCで連携する。