この章で学ぶこと
- 関係データベースデータベース・RDBMS・関係モデルと、関係(リレーション)・行・列を説明できる。
- データベースの設計エンティティとリレーションシップ、主キーと外部キー、正規化を説明できる。
- データの操作選択・射影・結合を見分け、条件に合う行を数えたり合計したりできる。
- トランザクション処理トランザクション、コミットとロールバック、排他制御、デッドロック、ログとチェックポイントを説明できる。
1関係データベース
たくさんのデータを決まった形で整理してためるデータベースと、データを表の形で管理するしくみ(関係モデル・RDBMS)、表の呼び方(関係・行・列)を学びます。
データベースとDBMS
会社には、客、商品、注文などのデータがたくさんあります。これらを、決まった形に整理してまとめ、何人もの人やシステムから、正しく使えるようにしたデータの集まりをデータベースといいます。データベースを管理し、データを書き込んだり、探したり、守ったりするソフトウェアをDBMS(データベース管理システム)といいます。
関係モデルとRDBMS
データを、行と列でできた表の形で表し、表と表を共通の項目でつないで扱う考え方を関係モデルといいます。関係モデルの考え方で作られたデータベースを関係データベースといい、それを管理するDBMSをRDBMS(関係データベース管理システム)といいます。今の会社のシステムでは、RDBMSが広く使われています。
関係データベースでは、表のことを関係(リレーション)といいます。表の横の1件分のデータを行、縦の項目を列といいます。
客の表です。1行が1人の客を表し、列が「客番号」「氏名」「住所」の項目を表します。
| 客番号 | 氏名 | 住所 |
|---|---|---|
| 1 | 田中 | 東京都 |
| 2 | 佐藤 | 大阪府 |
| 3 | 田中 | 福岡県 |
表そのものが関係(リレーション)です。第8章「業務とシステムの計画」のE-Rモデルで学んだ、実体どうしのつながりを表す関連(リレーションシップ)とは別のものなので、名前が似ていることに気をつけます。
データの量が多いと、目当ての行を探すのに時間がかかります。そこで、本の索引のように、列の値と、その行がある場所の対応を別に作っておき、検索を速くします。これをインデックス(索引)といいます。
2データベースの設計
データベースの表を作るときの考え方として、管理したいものとそのつながり(エンティティ・リレーションシップ)、行を見分ける列(主キー)、ほかの表とつなぐ列(外部キー)、同じ情報を重ねて持たないように表を分けること(正規化)を学びます。
エンティティとリレーションシップ
データベースを設計するときは、まず、管理したいもの・こと(客、商品、注文など)と、それらのつながりを考えます。管理したいもの・ことをエンティティ(実体)、エンティティどうしのつながりをリレーションシップ(関連)といい、第8章で学んだE-Rモデルで図に表します。エンティティが、データベースの表のもとになります。
客と注文を例に、エンティティとリレーションシップを図にすると、次のようになります。エンティティの箱の中には、記録する項目を書きます。
線の両端の「1」と「多」は、1人の客が多くの注文をし、1つの注文は1人の客のものであることを表します。図の項目は、このあと学ぶ表の列になります。
主キーと外部キー
表の中の1行を、ほかの行と見分けるための列(または列の組合せ)を主キーといいます。主キーの列には、同じ値が2つ出てはいけません。また、値が空ではいけません。先の客の表で、「氏名」には同じ「田中」が2人いるので、主キーにできません。「客番号」なら1人に1つなので、主キーにできます。1つの列では行が決まらないときは、たとえば「店」と「商品」のように、いくつかの列を組み合わせて主キーにします。
ある表の列で、別の表の主キーの値を指して、表どうしを結び付ける列を外部キーといいます。外部キーには、指す先の表の主キーにある値だけが書けます。同じ値が何度出てもかまいません。
注文の表です。「客番号」の列が外部キーで、客の表の主キー「客番号」を指しています。
| 注文番号 | 客番号 | 商品 |
|---|---|---|
| 101 | 1 | ノート |
| 102 | 3 | ペン |
| 103 | 1 | のり |
注文の表の主キーは「注文番号」です。「客番号」には、同じ1が2回出てきますが、外部キーなのでかまいません。ただし、客の表にない客番号(たとえば4)は書けません。
外部キーには、指す先の表にある値だけが書ける、という決まりを参照整合性といいます。参照整合性を守るため、データを入れるときは、指される側の表(客の表)に先に行を入れ、指す側の表(注文の表)に後から入れます。消すときは逆に、注文の表の行を先に消します。客の表から客番号1の行を先に消すと、注文の表に、指す先のない客番号1が残ってしまうからです。
正規化
1つの表に、同じ情報を何度も書いていると、直すときに困ります。たとえば、次の表は、注文のたびに客の住所を書いています。
| 注文番号 | 客番号 | 氏名 | 住所 | 商品 |
|---|---|---|---|---|
| 101 | 1 | 田中 | 東京都 | ノート |
| 103 | 1 | 田中 | 東京都 | のり |
田中さんが引っ越したとき、片方の行だけ住所を直し忘れると、同じ人の住所が食い違ってしまいます。そこで、同じ情報を重ねて持たないように、表を分けて、それぞれの情報を1か所にだけ書くようにすることを正規化といいます。この例では、客の表と注文の表に分けます。
客の表(主キーは客番号)
| 客番号 | 氏名 | 住所 |
|---|---|---|
| 1 | 田中 | 東京都 |
注文の表(主キーは注文番号、客番号は外部キー)
| 注文番号 | 客番号 | 商品 |
|---|---|---|
| 101 | 1 | ノート |
| 103 | 1 | のり |
正規化する前は、田中さんの住所が注文の行の数だけ書かれていました。正規化した後は、住所は客の表の1か所にだけ書かれ、注文の表は客番号で客の表とつながります。引っ越したときも、客の表の1か所を直すだけで済むので、直し忘れの食い違いが起きません。
また、分ける前の表では、田中さんの注文の行を全部消すと、田中さんの住所まで消えてしまいます。分けておけば、注文を消しても、客の表に住所が残ります。
3データの操作
表から必要なデータを取り出す3つの操作(関係演算:選択・射影・結合)と、条件に合う行の数え方を学びます。
選択・射影・結合
関係データベースから必要なデータを取り出す操作を、関係演算といいます。代表的なものは次の3つです。
表から、条件に合う行だけを取り出す操作を選択といいます。たとえば、客の表から、住所が東京都の客の行だけを取り出すことです。表から、必要な列だけを取り出す操作を射影といいます。たとえば、客の表から、「氏名」の列だけを取り出すことです。2つの表を、共通の列(外部キーと主キー)でつないで、1つの表にする操作を結合といいます。たとえば、注文の表と客の表を「客番号」でつなぎ、注文ごとに客の氏名を並べて見ることです。
行をしぼるのが選択、列をしぼるのが射影、表をつなぐのが結合です。
色の付いた所が、取り出したり、つなぐのに使ったりする部分です。選択では行が減り、射影では列が減ります。結合では、注文の表と客の表が、同じ客番号の行どうしでつながり、列が増えた1つの表になります。
条件に合う行の数え方
選択の条件には、「かつ」と「または」を使うことがあります。「かつ」(AND)は、2つの条件の両方に合う行だけを残します。「または」(OR)は、2つの条件のどちらか一方にでも合う行を残します。
売上の表です。
| 店 | 商品 | 個数 |
|---|---|---|
| 北店 | パン | 8 |
| 北店 | 牛乳 | 15 |
| 南店 | パン | 20 |
| 南店 | 卵 | 5 |
- 「店が北店」かつ「個数が10以上」:北店・牛乳・15 の1行
- 「店が北店」または「個数が10以上」:北店の2行と、南店・パン・20 の 3行
「または」では、北店・牛乳・15のように両方に合う行も、1回だけ数えます。1行ずつ、条件に合うかを確かめると、数え違えません。条件に合う行の個数を合計するときも、条件に合う行を全部集めてから足します。たとえば「商品がパン」の個数の合計は、北店の8と南店の20を足して28です。
条件で選んでから、別の表で名前を引く
選択の条件が「かつ」と「または」を組み合わせたものでも、1行ずつ、条件に合うかを確かめます。選んだ行の値を使って、結合した別の表から名前などを引くこともあります。
入館の記録の表と、社員の表です。「結果が失敗」または「時刻が9:00より前で、結果が成功」の行を選び、その社員の部署を求めます。
| 社員番号 | 結果 | 時刻 |
|---|---|---|
| 11 | 成功 | 8:40 |
| 12 | 失敗 | 9:10 |
| 13 | 成功 | 9:30 |
| 11 | 失敗 | 10:05 |
| 社員番号 | 部署 |
|---|---|
| 11 | 営業部 |
| 12 | 経理部 |
| 13 | 総務部 |
- 1行目:成功で、8:40は9:00より前。後ろの条件に合う。社員番号11
- 2行目:失敗。前の条件に合う。社員番号12
- 3行目:成功だが、9:30は9:00より前ではない。どちらにも合わない
- 4行目:失敗。前の条件に合う。社員番号11
- 社員の表で引く:11は営業部、12は経理部。答えは営業部と経理部
4トランザクション処理
ひとまとまりの書き換え(トランザクション)を確定させる・取り消す操作(コミット・ロールバック)と、同時の書き換えの食い違いを防ぐしくみ(排他制御)、互いに待ち合って進めなくなる状態(デッドロック)、止まったときに立て直すための記録(ログ・チェックポイント)を学びます。
トランザクションとコミット・ロールバック
席の予約では、「席を予約済みにする」ことと「代金の記録を足す」ことを、必ずそろえて行う必要があります。片方だけが済むと、席と代金の記録が食い違います。このように、全部がそろって済まないと意味がない、ひとまとまりの処理をトランザクションといいます。
トランザクションの処理が全部済んだときに、書き換えを確定させることをコミットといいます。途中で失敗したときに、すでに済んでいた書き換えも取消して、トランザクションを始める前の状態に戻すことをロールバックといいます。一部だけを確定させることはしません。
点線の枠の中が1つのトランザクションです。①と②の両方が済んだときだけコミットして確定させます。②で失敗したら、すでに済んでいた①も取り消し、始める前の状態に戻します。
排他制御とデッドロック
何人もの人が、同じデータを同時に書き換えると、食い違いが起きます。たとえば、在庫が1個の商品を、2人がほぼ同時に買うと、どちらも「在庫あり」と見て注文し、1個しかない商品が2個売れたことになってしまいます。そこで、ある人がデータを書き換えている間は、そのデータに鍵(ロック)をかけて、ほかの人を待たせます。これを排他制御といいます。このように、何人もの処理を同時に行っても、データが食い違わないように制御することを同時実行制御といい、排他制御はその代表的な方法です。
排他制御で、2つの処理が、互いに相手のかけた鍵が外れるのを待ち合い、どちらも先へ進めなくなることがあります。たとえば、処理Aがデータ1に鍵をかけてデータ2を待ち、処理Bがデータ2に鍵をかけてデータ1を待つ状態です。これをデッドロックといいます。
左の排他制御では、処理Aが鍵をかけている間、処理Bは待つので、同時に書き換えて食い違うことがありません。右のデッドロックでは、処理Aと処理Bが、相手の持つ鍵を互いに待っているので、どちらも先へ進めません。
ログとチェックポイント
データベースは、速く処理するために、書き換えをまずメモリ(第17章「コンピュータとシステムの構成」の3節「CPUとメモリ」)の上で行い、決まった時ごとに、まとめてディスクへ書き出します。この書き出す時点をチェックポイントといいます。また、いつ、どのデータを、どう書き換えたかを、記録として残しておきます。この記録をログといいます。
機械が急に止まると、メモリの上の書き換えは消えます。そこで、チェックポイントの時点のディスクのデータに、それ以降のログを使って、コミットまで済んでいた書き換えをやり直して反映し、コミットしていなかった書き換えは取り消して、データを正しい状態に立て直します。
黒い丸がコミットした時点です。T1はチェックポイントより前にコミットしたので、ディスクに書き出し済みです。T2とT3は、チェックポイントの後にコミットしたので、ログを使ってやり直します。T4は、止まった時点でまだコミットしていないので、取り消します。
データベースの内容を、別のコンピュータに写し、書き換えがあるたびに同じ内容にそろえ続けるしくみをレプリケーションといいます。1台が止まっても、写した先で続けて使えます。決まった時点の写しを取っておくバックアップとは、いつも同じ内容に保つ点が違います。
IPA、ITパスポート試験 シラバス(案)Ver.0.1(大分類4 中分類17 データベース)。