本文へ移動
  1. 高校情報
  2. 基本情報ハンズオンラボ
  3. データベース

中分類9 大分類：技術要素 テクノロジ系

データベース

データを表に分けてしまい、SQL で取り出し、同時に使う人がいても矛盾が生じないように守るのがデータベースです。SQL の結果・正規化・ロックの3つを、手を動かして確かめます。

生徒の表のクラス番号が外部キーとしてクラスの表の主キーを指し、その下に SELECT … FROM … WHERE の SQL と、ロックを表す錠前を並べた図
図：主キーと外部キーでつながる2つの表、SQL、ロック

要点

データベース方式

データベースの種類と特徴
表で表す関係データベース（RDB）が中心。木構造で親が1つの階層型、親を複数持てる網型もある。
表にこだわらない型としてキーバリュー型・ドキュメント指向・列指向・グラフデータベースがある。
全データをメモリに置くインメモリデータベースは速い。オブジェクトをそのまま格納するのがOODB。
3層スキーマ
利用者から見た形が外部スキーマ、表の論理的な定義が概念スキーマ、ディスク上の格納形式が内部スキーマ。
3層に分けると、格納形式を変えても利用者のプログラムを直さずに済む（データの独立性）。
データモデル
論理データモデルには関係モデル・階層モデル・ネットワークモデルがある。格納の仕方まで決めたものが物理データモデル。
点と線でつながりを表すグラフ型には、属性を持てるプロパティグラフと、主語・述語・目的語の3つ組のトリプルストアがある。
関係モデル
表が関係（リレーション）、行がタプル、列が属性。列がとりうる値の集まりを定義域（ドメイン）という。
表名と属性の並び「生徒（生徒番号, 氏名, クラス番号）」が関係スキーマ。表どうしは共通の列の値でつなぐ。
データベース管理システム
DBMSは表の定義・データの操作・同時に使うときの制御をまとめて受け持つ。
同時実行制御（排他制御）、障害回復、利用者ごとに使える範囲を決めるデータ機密保護も DBMS の機能。

データベース設計

データ分析
同じものに別の名前が付く異音同義語と、別のものに同じ名前が付く同音異義語を、項目名の標準化で防ぐ。
項目の意味・型・桁数などのメタデータはデータディクショナリにまとめる。
概念設計
E-R図でエンティティ（もの）・属性・リレーションシップ（関連）を描く。DBMS の種類には左右されない。
関連の数の対応がカーディナリティ。1対1・1対多・多対多があり、多対多は間に表を足して1対多2つに分ける。
論理設計
行を1つに決める列が主キー。重複とNULL（値なし）を許さない。
ほかの表の主キーを指す列が外部キー。指す先に無い値を入れられない（参照制約）。
同じ値を2行に入れさせないのが一意性制約。制約で、データの矛盾を DBMS に防がせる。
データの正規化
繰り返し項目をなくして1行1件にしたのが第1正規形。
主キーの一部だけで決まる列（部分関数従属）を別の表に出したのが第2正規形。残りは主キー全体に完全関数従属する。
主キー以外の列で決まる列（推移関数従属）も別の表に出したのが第3正規形。同じ値を何か所も直す手間と矛盾が減る。
物理設計
1行のバイト数×行数に余裕を足してディスク容量を見積もり、表をどのファイル・領域に置くかを決める。
想定の件数と処理で性能評価を行い、インデックスや格納の仕方を調整する。

データ操作

データベースの操作
同じ形の表どうしの和・差・積（共通）が集合演算。
行を選ぶ選択、列を選ぶ射影、共通の列でつなぐ結合、全組み合わせを作る直積、その逆にあたる商が関係演算（関係代数）。
データベース言語の種類
表を定義するDDLと、データを操作するDMLに分かれる。
SQL だけを打ち込む独立言語方式（会話型 SQL）と、ほかの言語の中から使う親言語方式（埋込み SQL）がある。
データ定義言語
CREATE TABLE で列のデータ型と列制約・表制約（PRIMARY KEY、FOREIGN KEY、CHECK、NOT NULL、UNIQUE）を決める。
CREATE VIEW で作るビューは実表から作る仮想の表。集約や結合を含むビューは、ふつう更新できない。
SELECT 文
WHERE で行を絞り、BETWEEN（範囲）・IN（候補のどれか）・LIKE（% は任意の文字列、_ は1文字）を使う。
集約関数（COUNT・SUM・AVG・MAX・MIN）は GROUP BY のグループごとに計算し、グループの条件は HAVING に書く。
副問合せは SQL の中の SQL。外側の行ごとに内側を計算し直すのが相関副問合せ。表の別名が相関名。
その他のデータ操作言語
INSERT で行を足し、UPDATE … SET で書き換え、DELETE で消す。WHERE を付け忘れると全行が対象になる。
GRANT で権限を与え、REVOKE で取り上げる。
埋込み SQL
結果が何行もあるときはカーソルを使う。DECLARE で宣言し、OPEN、FETCH で1行ずつ読み、CLOSE で閉じる。
結果が1行だけなら、カーソルを使わず親言語の変数へ直接受け取れる（非カーソル操作）。

トランザクション処理

同時実行制御（排他制御）
更新する間は専有ロック（ほかは読みも書きも待つ）、読むだけなら共有ロック（読む者どうしは同時にかけられる）。
ロック粒度（表ごと・行ごと）が大きいほど管理は軽いが、待たされる処理が増える。
2つの処理が互いに相手のロックを待って止まるのがデッドロック。全員がロックする順番をそろえると防げる。
処理を確定するのがコミット、取り消すのがロールバック（コミットメント制御）。資源の空きを数で管理するセマフォ方式もある。
障害回復
更新の記録をジャーナルファイル（ログファイル）に残す。更新前の値が更新前ログ、更新後の値が更新後ログ。
ディスクが故障したら、バックアップを戻して更新後ログで進める（ロールフォワード）。
途中で止まった処理は、更新前ログで元に戻す（ロールバック）。
メモリの内容をディスクへ書き出した時点がチェックポイント。そこから再開するのがウォームスタート、初期状態から始めるのがコールドスタート。
追加と削除をくり返して散らばった格納領域は、再編成で詰め直してアクセスを速くする。
トランザクション管理
トランザクションにはACID 特性が要る。全部やるか全くやらないかの原子性、矛盾を残さない一貫性。
同時に動いても互いに影響しない独立性と、確定した結果は障害でも消えない耐久性も要る。
データベースの性能向上
インデックスは本の索引にあたる。範囲検索はB-tree、一致検索はハッシュ、値の種類が少ない列はビットマップが向く。
値の重複を許さないユニークインデックス、行の並び自体をキー順にするクラスタ化インデックスがある。
索引だけで答えられるカバリングインデックス、語から文書を引く転置インデックスもある。多すぎると更新が遅くなる。
データへのアクセス制御
利用者ごとに、接続・参照権限（SELECT）・挿入権限・更新権限・削除権限を分けて与える。

データベース応用

データベースの応用
日々の取引を処理するのがOLTP。過去のデータを分析用にためるのがデータウェアハウス、部門向けに切り出したのがデータマート。
多次元に集計して切り口を変えるのがOLAP、規則性を探すのがデータマイニング。
取り出し・変換・格納の順がETL、格納してから変換するのがELT。誤りや表記ゆれを直す作業がデータクレンジング。
分散データベース
複数の場所にあるデータを1つのように使える。どこにあるかを意識させない性質が透過性。
2相コミットメントでは、まず全サイトに確定できるか尋ね、全部が可なら確定、1つでも不可なら全部取り消す。
データの複製をほかのサイトに置いて同期するのがレプリケーション。
データ資源管理
データの意味や格納場所をまとめたデータディクショナリ、開発の情報を一元管理するリポジトリ（IRDS）がある。
表になる構造化データ、JSON などの半構造化データ、画像や文章の非構造化データ。形を問わずためる場所がデータレイク。

さわって確かめる

SQL・正規化・ロックの3つを、次のカードで動かします。結果の欄には、途中の段階も出ます。

1. SQL を実行して結果の表を見る

「クラスごとの人数と平均点」を選んで実行し、GROUP BY で何行にまとまったかを確かめよう。

表「生徒」（主キーは生徒番号）

表「クラス」（主キーはクラス番号）

使える構文（この模擬で動く範囲）
  • SELECT [DISTINCT] 列や式 [AS 別名], … FROM 表 [相関名]
  • 結合：[INNER] JOIN 表 ON 列 = 列、または FROM 表1, 表2 WHERE …（直積を作ってから WHERE で絞る）
  • WHERE：= <> < > <= >=、AND OR NOT、BETWEEN a AND b、IN (…)、LIKE '…'（% と _）、IS [NOT] NULL
  • GROUP BY 列, …、HAVING 条件、ORDER BY 列や別名や列の番号 [ASC|DESC], …
  • 集約関数：COUNT(*)、COUNT(列)、SUM、AVG、MAX、MIN。四則演算 + - * /
  • 動かないもの：副問合せ、外部結合、INSERT・UPDATE・DELETE などの更新、表を3つ以上つなぐこと。文字列は '…' で囲む

2. 正規化を1段ずつ進める

「次の段階へ」を3回押し、注文書の表を第3正規形まで分けて、顧客名が何回書かれているかを比べよう。

3. ロックの順番とデッドロック

T1 を「A→B」、T2 を「B→A」にして1手ずつ進め、デッドロックが起きる手番を確かめよう。

T1 がロックする順
T2 がロックする順
ロックの種類

3問チェック

最初に選んだ答えで採点します。全問正解で、この単元に「済み」の印が付きます。

  1. 表「貸出」（貸出番号, 会員, 冊数）に (1, A, 2)、(2, B, 1)、(3, A, 3)、(4, C, 4)、(5, B, 2) の5行がある。SELECT 会員, SUM(冊数) FROM 貸出 GROUP BY 会員 HAVING SUM(冊数) >= 4 の結果は何行か。

    1. 1行
    2. 2行
    3. 3行
    4. 5行
    答えと解説

    正解はイ。会員ごとに合計すると A＝2＋3＝5、B＝1＋2＝3、C＝4。4以上は A と C の2行。3行は HAVING を無視したグループの数、1行は「4より大きい」と読んだ場合（A だけ）。5行は GROUP BY 前の行数。

  2. 表「社員」（社員番号, 氏名, 部署番号, 部署名）の主キーは社員番号で、部署名は部署番号で決まる。この表はどの段階か。

    1. 非正規形
    2. 第1正規形だが第2正規形ではない
    3. 第2正規形だが第3正規形ではない
    4. 第3正規形
    答えと解説

    正解はウ。繰り返し項目は無く、主キーは1列なので部分関数従属は起きない（第2正規形）。ところが部署名は主キーでない部署番号で決まる（社員番号→部署番号→部署名の推移関数従属）ので、第3正規形ではない。部署（部署番号, 部署名）を別の表に分けると第3正規形になる。

  3. データベースを置いたディスクが故障した。新しいディスクに前日夜のバックアップを戻した。障害の直前まで回復させるために、次に行うことはどれか。

    1. 更新前ログを使ってロールバックする
    2. 更新後ログを使ってロールフォワードする
    3. 更新前ログを使ってロールフォワードする
    4. チェックポイントの時点からウォームスタートする
    答えと解説

    正解はイ。バックアップ以降に確定した更新を、更新後ログ（新しい値）で順にやり直す。更新前ログ（古い値）は途中で止まった処理を元に戻すロールバックに使う。ウォームスタートはシステムが止まったときの再開で、ディスクの中身が失われた場合には使えない。