【基本情報技術者】データベースについて

ちょっとどのくらいの量があるのかをチラ見してみたのですが、データベースの範囲はプロットだけでも結構な量行きそうです。

さて、がんばって覚えていきましょう。

基本情報技術者～データベース～

データベースの基本

データベースが扱う「情報」は、4大経営資源のひとつです（ヒト、モノ、カネ、情報）。このため、多くの企業にとってデータベースの活用は必須といえます。

データベースとは

データベース（Database）とは、情報の集まりです。データベースに情報を集めることで、データを一元管理することができます。

データモデルとは

データモデルとは、現実世界にある複雑なデータの相互関係をわかりやすく表した図のことです。データモデルはデータベースを作る際の設計図になります。

関係データモデル

データモデルと一口にいっても、世の中には「関係データモデル」「階層データモデル」「ネットワークデータモデル」といった、さまざまなデータモデルが存在しています。
これらのうち、最も普及しているものが、「関係データモデル」です。簡単なものですと、Excelの表として表現することができます。

関係データベース

関係データベースとは

関係データベースとは、データを「表」で管理するデータベースのことです。これは、関係データモデルをコンピュータ上に実装したものです。

• レコードと列
  関係データベースとは、データを「表」で管理するデータベースと説明しましたが、基本情報技術者試験では「表」ではなく、「テーブル」と表現します。
  そしてこの「テーブル」は、「レコード（行）」と「列」で構成されます。

主キー

主キーとは、膨大なデータの中から1つのレコードを識別するための列です。専門的にいうと「テーブルの中からレコードを一意に識別するための列」となります。
データが数件しかない場合は良いのですが、

• 主キーに向いている列
  例えば、「学生番号」「学生名」「電話番号」という3つの列で構成されているテーブルがあるとします。この中で、どの項目が主キーに向いているかを考えていきます。
  まず「電話番号」が個人を特定できそうで主キーとして使えそうな気がします。しかし、一時的な理由などで「電話番号」がない、「null」のデータになっている生徒もいるかも知れませんので、これは主キーとして使えません。
  次に、「学生名」は主キーとして使えそうでしょうか。一見使えそうなのですが、これも何十年分、何千何万というデータになることを考慮した場合に、同姓同名の生徒が出たら「一意」にならないので主キーとして使えませんよね。
  というわけで、「学生番号」ですが、これは「一意」に識別できる番号なので、「学生番号」が主キーとして向いているということができます。

• 主キーの条件
  （１）重複する値がないこと
  （２）データが空（NULL）でないこと

• 複合主キー
  テーブル内に主キーの条件を満たす列が1つもない場合、複数の列を組み合わせて主キーにすることもできます。これを「複合主キー」と呼びます。

外部キー

外部キーとは、別のテーブルの主キーを参照する列のことです。下図の「教員番号」が外部キーにあたります。

• [教員番号]はあるが、[教員名]はない
  [学生]テーブルには[教員番号]がありますが、[教員名]がありません。このため、山田太郎くんの担当の教員を調べるには、次のような手順が必要になります。
  1. [学生番号]が「1」のレコードを検索する
  2. 検索したレコードの[教員番号]の値を調べる（教員番号＝2）
  3. その値から[教員]テーブルを調べる
  4. [教員]テーブルから、担当が「伊藤一朗」であることがわかる

• 参照一貫性とは
  参照一貫性（参照整合性ともいう）とは、外部キーの参照先のレコードが必ず存在していることというような意味です。
  例えば、上のテーブルをみたときに、[学生]テーブルで外部キーとして指定された値は、必ず[教員]テーブルに存在しなくてはなりません。

データベース設計

E-R図

E-R図（Entity-Relationship Diagram）とは、データの関係性を「エンティティ（実体）」と「リレーションシップ（関連）」という2つの概念で表した図のことです。
関係データベースを作成する際の設計図として、E-R図が使われます。

• 実体と関連

要素	説明
エンティティ（実体）	人間が管理する対象。「名詞で表せるもの」と考えると理解しやすい。
リレーションシップ（関連）	エンティティとエンティティの関係。「動詞で表せるもの」と考えると理解しやすい。

• E-R図の例

多重度

多重度とは、エンティティとエンティティの間の量の関係を示す度合いです。E-R図では、多重度として次のような表記ルールを用います。

表記	意味
1	1
*	任意の整数
0..1	0から1の範囲
1..*	1以上（1から任意の整数の範囲という意味）

• 1対1
  多重度が1対1の場合、リレーションシップの両端に「1」と記述します。上のE-R図で、校長と学校は1対1の関係にあります。

• 1対多
  多重度が1対多の場合、「1」と「1..*」と記述します。上のE-R図で、学校と学生は1対多の関係にあります。

• 多対多
  多重度が多対多の場合、リレーションシップの両端に「1..*」と記述します。上のE-R図で、学生と受講科目は多対多の関係にあります。

正規化

正規化とは、データが重複したり、データ更新の際に矛盾が生じないようにテーブルを分けることです。

• 正規化の具体例
  下図の正規化前の図を見ると、[学生]テーブルの中に[教員名」列が存在しています。ここで、2つの問題が生じています。
  1. もし「田中花子」先生の苗字が変わったとしたとき、このテーブルでは2個所を修正しなくてはなりません。（重複）
  2. もし「山田太郎」くんが引っ越しなどをしたとき、学生番号「1」を削除することになりますが、このとき、「伊藤一朗」先生のデータも無くなってしまいます。（矛盾）

これを下図のように、「すべてのデータを重複なく、データを更新しても矛盾しない形に整理すること」を「正規化」といいます。

関数従属

正規化について学ぶためには、「関数従属」という言葉も覚えておかなくてはなりません。
関数従属とは、ある属性（列）の値が決まると、他の属性（列）の値も一意に決まることを指します。
例えば、上のテーブルで表した[学生番号]が決まると[学生名]も一意に決まります。このとき、「学生名は学生番号に関数従属する」といいます。

• 関数従属の表記方法
  関数従属を表す際は、属性名（列名）同士を「→」（矢印）で結びます。

3つの関数従属

関数従属には3つの種類があります。

　（１）完全関数従属
　（２）部分関数従属
　（３）推移的関数従属

• （１）完全関数従属
  完全関数従属とは、属性が主キーに関数従属することです。矢印の書き方など、詳しくは下図をご覧ください。

• （２）部分関数従属
  部分関数従属とは、属性が主キーの一部に関数従属することです。詳しくは下図をご覧ください。

• （３）推移的関数従属
  推移的関数従属とは、属性が主キー以外の属性に関数従属することです。[教員番号]は主キーではありませんが、[教員番号]が決まると、[教員名]が一意に決まります。詳しくは下図をご覧ください。

正規化の手順

正規化を行なう目的は以下の通りです。

これをもう少し具体的に説明すると次のようになります。

ここでいう「完全関数従属だけを残すこと」とは、つまり「すべての矢印が主キーから出るようになるまでテーブルを分割していくこと」となります。
さて、ここからの正規化のやり方は重要ですよ！

• 非正規形　～正規化を行う前の状態～
  非正規形とは、属性に繰り返しがある状態です。正規化を行っていない、まっさらの状態といえます。下図をみると、[試験ID][試験名][得点]がそれぞれ2回ずつ繰り返されており、非正規形であることがわかります。

• 第1正規形
  第1正規化とは、属性の繰り返しがない状態にすることです。いわゆる一般的なテーブルにするという感じでしょうか。非正規形のテーブルにレコードを追加することで、第1正規形にすることができます。第1正規化した状態を第1正規形といいます。
  関数従属の関係を見ると、完全関数従属、部分関数従属、推移的関数従属と、すべての関数従属が入り乱れていることがわかります。
  これを第2正規形に移行することで、余計な関数従属を減らしていくことができます。

• 第2正規形
  第2正規化とは、部分関数従属がない状態にすることです。第2正規化した状態を第2正規形といいます。第2正規化では、テーブルを分割することで第1正規形から部分関数従属をなくします。

• 第3正規形
  第3正規化とは、推移的関数従属がない状態にすることです。第3正規化した状態を、第3正規形とよびます。第3正規化では、第2正規化と同様にテーブルを分割することで、第2正規形から推移的関数従属をなくします。

小休止！お試しトライ！過去問①

問1 関数従属

問題
6行だけから成る"配送"表において成立している関数従属はどれか。ここで、X→Yは、XはYを関数的に決定することを表す。

平成28年度

ア．配送先→部品ID　　　　　イ．配送日→部品ID
ウ．部署ID→部品ID　　　　　エ．部署名→配送先

解答
関数従属とは、ある属性の値が決まったとき、他の属性の値も一意に決まることです。ア．～エ．をすべて見ていくと、一意な関係にあるのは、配送先→部品IDのひとつです。

従って、解はア．となります。

データベース管理システム

ここでは、「データベース管理システム」と「SQL」について取り扱います。

データベース管理システムとは

データベース管理システム（DataBase Management System：DBMS）とは、アプリケーションソフトウェアの要求に応じて、データベースを操作するシステムです。

• データベース管理システムの3つの機能
  データベース管理システム（DBMS）の主な機能は、以下の3つです。
  1. データ操作
  2. トランザクション処理
  3. 排他制御

データ操作

データベースに格納されているデータを操作することを「データ操作」といいます。データ操作には、6つのやり方があります。

操作	説明
選択	テーブルから特定のレコードのみを取り出す操作
射影	テーブルからある特定の列のみを取り出す操作
結合	複数のテーブルを1つする操作
挿入	テーブルにレコードを追加する操作
更新	レコード内のデータを変更する操作
削除	テーブルからレコードを削除する操作

これら6つの操作のうち、特に重要なのは「選択」「射影」「結合」です。この3つについては、基本情報技術者試験でよく出題されるので、しっかりと覚えておきましょう。

• 選択
  選択とは、テーブルからレコードを取り出す操作です。

• 射影
  射影とは、テーブルから特定の列のみを取り出す操作です。

• 結合
  結合とは、複数のテーブルを1つにする操作です。

SQL

SQL（Structured Query Language）とは、関係データベースを操作するための言語です。
アプリケーションソフトウェア→（SQLで命令を出す）→RDBMS←（データを操作する）→関係DB

SELECT文

例えば、学生テーブルから学生名を取り出したいというときは、次のように記述します。

この場合、テーブルから列を取り出しているので、「射影」の操作になります。

• 複数の列を取り出す
  複数の列を取り出す場合の式は次のようになります。

• すべての列を取り出す
  すべての列を取り出す場合の式は次のようになります。

• 特定のレコードを取り出す
  上記のようなケースでは、列が1000件などと多い場合でも、すべての列を取り出してしまいます。特定のレコードのみを取り出すには、WHERE句を使用します。

例えば、学生テーブルから「学生名が鈴木愛子」のレコードのみを取り出したいというときは、次のように記述します。

この場合、テーブルからレコードを取り出しているので、「選択」の操作になります。

条件式（WHERE句）の書き方

SQLの条件式は、次のように指定します。

例えば、学生名の値が「鈴木愛子」のレコードを取り出すには、次のように指定します。

• 比較演算子
  WHERE句で使用する比較演算子は、以下の6つです。

比較演算子	意味
=	等しい
<>	等しくない
>	より大きい
<	より小さい
>=	以上
<=	以下

例えば、学生番号が「2」以上のレコードを取り出すには、以下のように条件式を指定します。

• 論理演算子
  WHERE句で使用する論理演算子は、以下の3つです。

論理演算子	意味
AND	かつ
OR	または
NOT	否定

例えば、学生番号が「2」以上、または、学生名が「鈴木愛子」のレコードを取り出すには、以下のように条件式を指定します。

テーブルを結合する

選択と射影の説明では、操作対象のテーブルは1つでした。しかし、結合の場合は複数のテーブルをくっつけてデータを取り出すという方法を用います。

• テーブルを結合する方法
  テーブルを結合するには、まず、FROMの後に、対象のテーブル名を「,（カンマ）」で指定します。次に、WHERE句で、2つのテーブルを紐づけるための「列名」を指定します。
  テーブルを結合するには、すべてのテーブルに共通して存在する列を指定する必要があります。

学生テーブルと教員テーブルを結合する場合は、以下のように記述します。

データを集計する

データを集計するには関数を使います。基本情報技術者試験に登場する主な関数は次の5つです。

関数	機能
SUM(列名)	指定した列の、合計を求める
AVG(列名)	指定した列の、平均を求める
MIN(列名)	指定した列の、最小値を求める
MAX(列名)	指定した列の、最大値を求める
COUNT(*)	行数を求める

例えば、学生テーブルに登録されているレコードの行数を求めるには、以下のように指定します。

データをグループ化する

データをグループ化するには、SELECT文にGROUP BY句を指定してあげます。GROUP BY句を使うことで、「列の値が同じレコード」をまとめることができます。

例えば、受注テーブルに登録されているデータから、商品ごとの数量の合計を求めたい場合には、次のように指定します。

WHERE句とGROUP BY句の記載順序

WHERE句とGROUP BY句を同時に使うときには、WHERE句を先に記述し、後にGROUP BY句を記述します。

例えば、受注テーブルから、商品名が「ペン」の数量だけを求めるには、以下のように指定します。

HAVING句

WHERE句は、グループ化する前のテーブルに対して条件式を指定しますが、HAVING句は、グループ化した後のテーブルに対して条件式を指定します。GROUP BY句の前にくるからWHERE句はグループ化する前のテーブルに、GROUP BY句の後にくるからHAVING句はグループ化した後のテーブルに対して効果があると覚えましょう。

データを並べ替える

データを並べ替える場合は、SELECT文にORDER BY句を指定します。ORDER BY句を使用すると、指定した列の値に応じてレコードを並べ替えることができます。

並べ方は2種類あります。並べ方の指定を省略した場合はASC（昇順）となります。

並べ方	意味
ASC	昇順（小さい値から大きい順に並べ替えられる）
DESC	降順（大きい値から小さい順に並べ替えられる）

例えば、受注テーブルのレコードを、数量で昇順に並べ替えるやり方は、以下のように指定します。

列名に別名をつける

列名の後ろにAS句を指定してあげると、列名に別名を付けることができます。

例えば、以下のよう指定すると、「SUM(数量)」という表示名に「合計数量」という別名を設定してあげることができます。

ビュー

データベースに保存されているデータ、つまりテーブルのことを「実表」または「基底表」と呼びます。一方で、「CREATE VIEW文」を用いて、新たに作成された仮想的な表のことを「ビュー」または「導出表」と呼びます。

• ビューの作り方
  ビューを作成するには、CREATE VIEW文を使用します。

これは今まで出てきたSQL文とはちょっと異なります。注意して覚えましょう。

CREATE VIEWの後にビュー名を指定します。そして、ASの後に「データを取り出すためのSELECT文」を指定します。

例えば、学生テーブルから学生番号と学生名のみを取り出して「公開学生名簿ビュー」を作成する場合は、次のように指定します。

• ビューの使い方
  ビューは仮想的な表ではありますが、実表と同じように扱うことができます。

このように、公開学生名簿ビューの元となっている学生テーブルに含まれている電話番号は取り出すことができません。これが、ビューの最大の特徴です。

• ビューを使うとデータを隠蔽できる
  上記のように、ビューを利用することで、実表に含まれているけど公開したくない情報を「隠蔽」することができます。これにより、ユーザーは実表に他にどんなデータがあるのかを知ることができません。

• ビューと実表との違い
  ビューは仮想的なテーブルですが、実表と同じようにSQLの中で利用することができます。また、ビューは実表と関連づいているため、次のような特徴があります。
  • 実表のデータを変更すると、ビューのデータも変更される
  • ビューのデータを変更すると、実表のデータも変更される

ただし、複数の表を結合したようなビューは、データを変更することはできません。

インデックス

インデックスとは、データベースの検索を高速化する機能です。インデックスは「索引」という意味です。データベースにインデックスを作成しておくことで、必要なデータをすぐに見つけることができます。

• インデックスの構造
  本の索引では、多くが「50音順に並んだキーワード」と「キーワードの記載ページ」から構成されます。一方で、インデックスは「1つの列」と「レコードの位置を指し示すポインタ」から構成されます。

上のインデックスでは、学生名が50音順に並んでいるため、目的の「学生名」をすぐに見つけることができます。さて、そうなるとすべての列に対してインデックスを作成すれば良いのかという話になりますが、すべての列にインデックスを作成してしまうと、データベース全体の処理効率が悪化してしまうので、ほどほどにする必要があるようです。

ストアドプロシージャ

ストアドプロシージャとは、データベースに対する複数の命令を1つの命令にまとめて、データベース管理システムに保存したものです。

通常は、命令の数だけアクセスする必要があるものが、データベース管理システムに保存されているストアドプロシージャを呼び出すだけで、複数の命令を処理させることができます。

ストアドプロシージャを使うことで、クライアントとサーバ間のネットワークの負荷を減らすことができます。

小休止！お試しトライ！過去問②

問1 選択・射影・結合

問題
列A1～A5から成るR表に対する次のSQL文は、関係代数のどの演算に対応するか。

平成25年度

ア．結合と射影　　　　　イ．差と選択
ウ．選択と射影　　　　　エ．和と射影

解答
まず、和と差については習っていません。そこで、残りはア．かウ．となります。

結合の場合、FROM句で2つのテーブルを扱うため、消去法で答えが出ます。

従って、解答はウ．となります。

トランザクション管理

DBMSの主な機能として、「データ操作」「トランザクション処理」「排他制御」を挙げました。このうち「トランザクション管理」についてみていきたいと思います。

トランザクション管理

トランザクション管理とは、ユーザーがDBMSに対して行う一連のトランザクション処理を管理することです。トランザクションとは「関連する複数の処理のまとまり」です。例えば、次のような処理です。

• 太郎くんの口座から1万円を出金する（出金処理）
• 花子さんの口座に1万円を入金する（入金処理）

この2つの処理をまとめて、トランザクションと呼びます。

• トランザクション管理はとても重要
  トランザクション管理がしっかりしていないと、例えば、太郎くんの口座から1万円を出金したあとに、花子さんの口座への入金が失敗したとすると、花子さんに入金されるはずだった1万円が浮いてしまいます。このようなことがないように、これらを一連の流れとして、作業全体をトランザクション管理という1つの塊で管理をするのです。

ACID特性

ACID特性アシッドとくせいとは、トランザクション管理に必要な4つの性質の頭文字を取ったものです。中でも、基本情報技術者試験では「原子性」がよく出題されます。

性質	説明
原子性（Atomicity）	トランザクション内の処理が「ぜんぶ実行される」または「ぜんぶ取り消される」のどちらかの結果となること。
一貫性（Consistency）	トランザクションの前後で、データベースに矛盾がないこと。
独立性（Isolation）	トランザクション結果が、他のトランザクション結果の影響を受けないこと。
耐久性（Durability）	トランザクション完了後にハードウェア障害が起きたとしても、更新されたデータベースの内容が保証されること。

障害回復

障害回復とは、壊れたデータやハードウェアを直すことです。「リカバリ」とも呼ばれます。障害回復を行うには、次の2つのファイルが必要となります。

種類	説明
バックアップファイル	データベース全体を保存しているファイル。毎週末などに定期的にバックアップを行っている。
ログファイル	トランザクションの前後の状態を記録したファイル。トランザクション開始直前のものを「更新前ログファイル」といい、完了後のものを「更新後ログファイル」という。

障害回復の方法

障害回復の方法には、次の2つがあります。

• ロールバック
  ロールバックとは、トランザクションを実行中に障害が発生した際に、データベースをトランザクション開始前の状態に戻す手法です。
  ロールバックでは、障害が発生したら「更新前ログファイル」を使ってデータベースを復元します。

• ロールフォワード
  ロールフォワードとは、ある時点で障害が発生した際に、データベースを直前のトランザクション完了後の状態に戻す手法です。
  ロールフォワードでは、障害が発生したら「バックアップファイル」「更新後ログファイル」を使ってデータベースを復元します。

排他制御

DBMSの主な機能として、「データ操作」「トランザクション処理」「排他制御」を挙げました。このうち「排他制御」についてみていきたいと思います。「排他制御」はこの3つのうちの中では、もっとも重要な機能であるといっても過言ではありませんので、皆さんしっかり覚えていきましょう。

排他制御

排他制御とは、複数の人が同じデータを同時に更新しようとしたときなどに、データに矛盾が生じないようにするための機能です。

具体例を出します。
太郎くんと花子さんの2人が経営する八百屋さんの「りんごの在庫処理」を追っていったときのものです。

1. 太郎くんがりんごを1個売る。
2. 太郎くんがりんごの在庫数をデータベースに反映させる。太郎くんはデータベースからりんごの在庫数3個を読み出す。
3. 太郎くんが処理をしている間に、花子さんもりんごを1個売る。
4. 花子さんは太郎さんがデータベースを処理しているとは知らずに、りんごの在庫数3個を読み出す。
5. 花子さんはりんごの在庫数を「2個」として書き込む。
6. 太郎くんもりんごの在庫数を「2個」として書き込む。

結果、りんごは2個売れたので、在庫は1となるはずなのですが、排他制御を行っていなかったため、在庫は2という矛盾が生じました。

ロック

排他制御では「ロック」という機能を使います。ロックとは、データベースに対するデータの読み書きを一時的にストップさせる機能です。太郎くんと花子さんの例で、今度はロック機能を使って説明してみます。

1. 太郎くんがりんごを1個売る。
2. 太郎くんは、自分がデータを読む前に、他の人がデータを読み書きできないようにロックをかける。
3. 太郎くんはデータベースからりんごの在庫数3個を読み出す。
4. 太郎くんが処理をしている間に、花子さんもりんごを1個売る。
5. しかし、花子さんはデータベースがロックされていたため、りんごの在庫数にアクセスできない。
6. そのため花子さんは、りんごの在庫数を読み出すことも書き込むこともできない。
7. 太郎くんはりんごの在庫数を「2個」として書き込む。
8. 太郎くんはロックを解除（アンロック）して、他の人がデータを読み書きできるようにする。
9. ようやく花子さんはデータベースを読み書きできるようになったため、ロックをかけて、在庫数を読み込み、新たな在庫数「1個」を書き込む。
10. 処理が正常に終了する。

• 専有ロック
  専有ロックとは、専有ロックをかけると、そのトランザクションのみ、データを読み書きできるようになるロックです。
  専有ロックは、データベースを更新する際に使われます。

• 共有ロック
  共有ロックとは、共有ロックをかけたトランザクションと他のトランザクション、すべてにおいて読み出しのみが行え、書き込みが禁止されるロックです。
  「データの一貫性を保つ」だけであれば、専有ロックだけで良いのですが、「データベース運用の効率性」も考慮すると共有ロックが必要になってきます。

• ロックの両立性
  ロックの両立性とは、複数のトランザクションが共有ロックを同時にかけることができる特性です。
  共有ロックは読み取りだけのロックなので、共有ロックが複数あっても矛盾は生じません。
  一方で、専有ロックがかかっていた場合は、他のトランザクションは専有ロックをかけることも共有ロックをかけることもできません。
  1. 共有ロックがすでにかかっているデータに対して、
     → 後から共有ロックをかけることは可能
  2. 共有ロックがすでにかかっているデータに対して、
     → 後から専有ロックをかけることは不可能
  3. 専有ロックがすでにかかっているデータに対して、
     → 後から共有ロックをかけることは不可能
  4. 専有ロックがすでにかかっているデータに対して、
     → 後から専有ロックをかけることは不可能

• ロックの粒度
  ロックの粒度とは、ロックの対象となるデータの単位です。ロックの単位としては、データベース単位、テーブル単位、レコード単位などさまざまです。
  ロックの粒度を小さくすると、他のトランザクションが利用できるデータが多くなるので、競合が起きにくくなります。一方で、ロックの管理が複雑になりCPUやメモリの負荷が増えます。
  ロックの粒度を大きくすると、他のトランザクションが利用できるデータが少なくなるので、競合が起きやすくなります。一方で、ロックの管理が単純になりCPUやメモリの負荷が減ります。

小休止！お試しトライ！過去問③

問1 障害回復

問題
データベースの更新前や更新後の値を書き出して、データベースの更新記録として保存するファイルはどれか。

平成30年度

ア．ダンプファイル　　　　　　イ．チェックポイントファイル
ウ．バックアップファイル　　　エ．ログファイル

解答
データベースの記録するものは、バックアップファイルとログファイルの2つです。そして、更新前、更新後に保存するファイルは、ログファイルです。

従って、解はエ．となります。

最後に

「データベース」の項目はかなり分量がありましたね。新しい耳慣れない言葉も多く出てきました。しっかりと復習したいところです。
ちなみに、テキストを読まないと完全な学習は行えませんので、ぜひ下で紹介している本をご購入いただけたらと思います。

こちらが参考にさせていただいているテキストです。