初級システムアドミニストレータ/データベース
![[半保護]](/wiki/%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB:Crystal_Clear_action_lock3.png){kind=small}
- お知らせ
初級システムアドミニストレータ試験（初級シスアド）は2009年（平成21年）度春期の試験を最後に廃止されました。ただし、初級シスアドの後継であるITパスポート試験や基本情報技術者試験にも、データベースに関する問題が出題されています。
ITパスポート試験や基本情報技術者試験の合格を目指される方はしっかり学習してください。
データベース
多くのデータを効率的に扱うには、表計算ソフトだけでなく、データベース(特に関係データベース)が役に立ちます。
- まずは頭のトレーニング
係長の赤井さんには、二人の部下(宇部さんと小田さん)がいます。
この場合「宇部さんの上司は、赤井さん」です。ですが「赤井さんの部下は、宇部さん」だとは限りません。
この例のように「BならばAだ。だが、AだからといってBとは限らない」、そんな例が、これからお話する「関係データベース」には、よく出てきます。
- 「データベース」とは
「データベース」という言葉も、もう日本語の一部になりましたが、まだ漠然と「データを貯めておく所」というニュアンスだけで語られることが多いです。
RDBMS
データベースの用語で、データを格納したり取り出したりするためのソフトウェアのことを、DBMS(DataBase Management System：データベース管理システム)と呼びます。その中で特に、ここで採り上げる関係データベース(Relational Database)の考えを前提に作られたものを、RDBMS(Relational DataBase Management System)と呼びます。RDBMSで最も普及数の多いソフトウェアは、マイクロソフト社のAccessです。他にも基幹系システムでよく用いられるOracleや、Webサーバでよく用いられるMySQLなども、このRDBMSに属します。
「関係(リレーショナル)データベース」では、データをテーブル(表計算でいう、一枚の表のようなもの)の形で持たせておき、1個以上のテーブルを組み合わせて、扱いたいデータの場所を指定していきます。なお、テーブル同士を組み合わせてデータを取り出す場合は、“○○番号”や“△△コード”といった値を使って、各テーブルをつなげる事を考えます。
ところでRDBMSは、データをテーブルの形で表現するからといって、表計算ソフトとは似て非なるものです。特に大量のデータ(数100～数千万件)を扱う時の効率や、勝手に他のユーザにいじらせたくないデータを保護する機能などが充実しているのが、RDBMSです。
社員表
| 社員番号 | 名前 | 部署コード |
|---|---|---|
| 1 | 赤井 | E |
| 2 | 伊藤 | K |
| 3 | 宇部 | E |
| 4 | 小田 | E |
部署表
| 部署コード | 部署名 |
|---|---|
| K | 開発部 |
| E | 営業部 |
これらのテーブルを使って「社員番号が1番の人の、部署名を調べたい」時は、2つのテーブルを“部署コード”つながりでたどって調べることになります。
この教材で学んで頂くこと
データをテーブルにしまうよりも前に、それぞれのテーブルを設計する作業が必要です。その際、「データベースの正規化」と呼ばれる考え方を知っていると、実務にも試験問題を解く時にも役に立ちます。
そして、データを取り出す際には、SQL(Structured Query Language：構造化照会言語)と呼ばれる、プログラミング言語に似たものを使って取り出します。SQLは、データを取り出すだけでなく、テーブルにデータをしまったり、テーブルそのものを設計する際にも使えるのですが、まずは初級シスアド試験の出題メインテーマである「データを取り出す際のSQL」を中心に身につけていきましょう。
- 【コラム】
- 超入門「データベースの正規化」
Q:さっきの2つのテーブルを、こんな一覧表にまとめても良いのでは?その方が「社員番号が1番の人の、部署名を調べたい」時に楽ですが。
社員と部署の一覧表
| 社員番号 | 名前 | 部署コード | 部署名 |
|---|---|---|---|
| 1 | 赤井 | E | 営業部 |
| 2 | 伊藤 | K | 開発部 |
| 3 | 宇部 | E | 営業部 |
| 4 | 小田 | E | 営業部 |
A:検索の速さや、人間がパッと見た場合の素早さにこだわるなら、これでも構わないです。ですがテーブルを分けておくと、データの書き換えがイッパツで済みます。たとえば、部署名“営業部”を“販売部”に書き換えてみましょう。
社員と部署の一覧表
| 社員番号 | 名前 | 部署コード | 部署名 |
|---|---|---|---|
| 1 | 赤井 | E | 販売部(1箇所) |
| 2 | 伊藤 | K | 開発部 |
| 3 | 宇部 | E | 販売部(2箇所) |
| 4 | 小田 | E | 販売部(3箇所) |
該当者は3人ですので、3箇所を書き換えました。
これに対し、うまくテーブルを分けておけば、該当者が3人だろうが100万人だろうが、1箇所だけの書き換えで済みます。
部署表
| 部署コード | 部署名 |
|---|---|
| K | 開発部 |
| E | 販売部(←この1箇所) |
楽ですので、データを書換える処理のトラブルを減らせます。このように、「データベースの正規化」とは、イッパツ変更できるように、うまくテーブルを分けておく作業とも言えます。
また、「(膨大な量のデータが)コンパクトにまとまる」効果もあります。この例では実感が薄いですが、「販売部」という全角3文字を格納する場所が(もし100万人いても)1箇所だけで済みます。
- テーブルをよく見てみると
情報処理の世界では、テーブル(table)という言葉を、一覧表や早見表といった意味で使います(時刻表をタイムテーブルと呼ぶのと同じです)。ここでは、関係データベースで扱うテーブルを詳しく見てみましょう。
社員表
| 社員番号 | 名前 | 部署コード |
|---|---|---|
| 1 | 赤井 | E |
| 2 | 伊藤 | K |
| 3 | 宇部 | E |
| 4 | 小田 | E |
- 属性
テーブル名のすぐ下、マス目の一番上に太字で書かれたもの(社員番号、名前、部署コード)は、それぞれの列がどんな意味を持つのかを表す部分であって、実際にしまっておくデータとは切り分けて考えます。この部分、平たく言えば「列の名前」ですが、これを正式には「属性」と呼びます。
属性は普通、テーブルの一番上に書いておきます。この属性部分は、テーブルの行数にはカウントしません。あくまでもその下に続く「実際のデータ部分のみ」を、そのテーブルの行数としてカウントすることにします。
なお試験問題の冊子には、「どんなデータが入っているかは置いといて、とりあえずここにテーブルがありますよ」という意味で、属性名のみでテーブルを表現することもあります。
- 表現例1
社員表
| 社員番号 | 名前 | 部署コード |
|---|
- 表現例2
社員表(社員番号，名前，部署コード)
- 行と列
表計算ソフトの時と同じです。横の並び(1件分のデータ)を行、たての並びを列と呼びます。
- 主キーと、その一意性について
「主(しゅ)キー」とは、あるテーブルにおいて、特定の1行を指し示すことができる属性のことで、この社員表では、属性“社員番号”が該当します(属性“名前”は、同姓の人が入社したら2行以上が指し示されてしまうため、主キーには向きません)。
主キー属性のデータは、そのテーブルにおいて一意(いちい)な値でないといけません。一意とは、あるものを誤解なく識別できることを指す言葉で、たとえば「携帯電話番号は、ケータイ端末ごとに一意に割り当てられている」のように使う言葉です。
下のテーブルでは、属性“学籍番号”の値が“001”である行は1行しかありません。ですが属性“名前”の値が“加藤”である行が2行あるので、行を一意には識別できません。この場合は、学籍番号を主キーとして扱います。
| 学籍番号 | 名前 |
|---|---|
| 001 | 加藤 |
| 002 | 田中 |
| 003 | 加藤 |
そして、主キーである属性の下にはアンダーラインを引くことがあります。
| 学籍番号 | 名前 |
|---|---|
| 001 | 加藤 |
| 002 | 田中 |
| 003 | 加藤 |
- 【コラム】
- ここで出てきた用語は、人によって呼び名が異なることがあります。
テーブル = 表、エンティティ
属性 = 列名、アトリビュート、データ項目
行 = タプル、レコード(row)
列 = カラム(column)
データが入るマス目 = ドメイン、セル
主キー = プライマリキー(PRIMARY KEY)
一意 = ユニーク(UNIQUE)
関数従属(かんすうじゅうぞく)
「AならばBだ。だが、BだからといってAとは限らない」場合、「Bは、Aに関数従属する」と呼びます。
学生表
| 学籍番号 | 名前 |
|---|---|
| 001 | 加藤 |
| 002 | 田中 |
| 003 | 加藤 |
「学籍番号001番は、加藤さんだ。だが加藤さんだからといって、学籍番号が001番だとは限らない(003番の加藤さんかも？)」
この場合「名前は、学籍番号に関数従属している」と言えます。ごく特殊なケースを除いてほとんどの場合、「主キー以外の属性は、主キーに関数従属する」と言えます。
- 【コラム】
- “関数”って?中学で習った とかの事?
この式の場合「x=2ならy=12だ。だが、y=12だからといって、x=2とは限らない(-2かもしれない)」です。これと同じイメージでとらえておくと良いかと思います。
推移的関数従属(すいいてきかんすうじゅうぞく)
「AならばBだ。そしてBならばCだ」といった、関数従属が何段もつながっている状態を、推移的関数従属と呼びます。
商品メーカ一覧表
| 商品コード | 商品名 | メーカコード | メーカ名 |
|---|---|---|---|
| 3001 | テレビ | SPDK | さっぽろ電機 |
| 3002 | ラジオ | SPDK | さっぽろ電機 |
| 3003 | テレビ | KMMS | くまもと無線 |
この表は、じつは下記のように2つの表に分けることができます。
商品表
| 商品コード | 商品名 | メーカコード |
|---|---|---|
| 3001 | テレビ | SPDK |
| 3002 | ラジオ | SPDK |
| 3003 | テレビ | KMMS |
メーカ表
| メーカコード | メーカ名 |
|---|---|
| SPDK | さっぽろ電機 |
| KMMS | くまもと無線 |
- 商品表より、「商品コードが定まれば、(その商品の)メーカコードが決まる」
- メーカ表より、「メーカコードが定まれば、(そのメーカコードの)メーカ名が決まる」
これら2つの表に分けても問題ない場合、元の表(商品メーカ一覧表)には「推移的関数従属があった」と言えます。
データベースの正規化
「データベースの正規化(以下“正規化”と略します)」という考え方があります。この考えを知っているといないとでは、初級シスアド試験のデータベース分野の問題(特に、午後問題)を解くための時間が、劇的に変わってきます。これが身につくと、午後のデータベース分野の出題を、1問あたり5分で完答するのも夢ではありません。
また、実務はもちろん、更に上位の情報処理技術者試験を受験する際にも、この知識は役に立ちます。具体的には、ソフトウェア開発技術者試験を勉強し始める知識までなら、この教材で身につくはずです。
正規化とは
「ムダにデータがある列を、別の表に“くくり出す”こと」です。正規化の作業を進めると、
- データをコンパクトに格納することができる
- データを1ヶ所書き換えれば、全体を書き換えたのと同じ効果が得られる
というメリットがあります。例題をもとに、見ていきましょう。
- 【正規化の課題】
- 以下の表は、ある架空の学校での、在学生と取得済み資格の一覧表です。“主キー”が設定されていないことを除けば第1正規形を満たしています。
この表に主キーを設定し、第2正規形および第3正規形へと正規化して下さい。
資格取得一覧テーブル
| 名前 | 学籍番号 | 学部コード | 学部名 | 資格コード | 資格名 |
|---|---|---|---|---|---|
| 西村 | Ａ００８ | Ａ | 法学部 | ＦＥ | 基本情報 |
| 西村 | Ａ００８ | Ａ | 法学部 | ＡＤ | シスアド |
| 前川 | Ａ０２２ | Ａ | 法学部 | ＡＤ | シスアド |
| 田口 | Ｂ０２２ | Ａ | 法学部 | ＡＤ | シスアド |
| 辻村 | Ｂ０９０ | Ｂ | 文学部 | ＦＥ | 基本情報 |
| 辻村 | Ｂ０９０ | Ｂ | 文学部 | ＡＤ | シスアド |
| 戸田 | Ｂ０９２ | Ｂ | 文学部 | ＦＥ | 基本情報 |
なお、この例題を解く際の条件として、
- 入学時に与えられた学籍番号は、卒業(退学)まで不変とします。
- ある学生と同じ学籍番号を、卒業(退学)生や他の学生が持つ事は無いものとします。
- この学校には、同じ苗字(属性［名前］)の学生が複数名在学することもあります。
- 今回の課題には該当者がいませんが、在学中の苗字(属性［名前］)変更もありえます。ただし今回の課題では、名前変更の手続き(変更日管理など)は考慮しません。
- 同じ資格に複数回合格しても、1行しか登録しないものとします(例：辻村さん(Ｂ０９０)は、平成15秋と平成16春にシスアドに合格し、今後も引き続き同じ試験の受験を考えているようです)。
- 在学中の学部変更もありえます(例：田口さん(Ｂ０２２・入学時は文学部))。ただし、今回の課題では、学部変更の手続き(変更日管理など)は考慮しません。
- 属性［学籍番号］を、［Ａ］と［００８］のように分割はしないものとします。
- この学校では、ＡＤといえば『シスアド』、ＦＥといえば『基本情報』と呼ばれており、また、毎月受験できる『簿記３級』など、他の資格取得への対応も考えています。
上記の条件は、いわゆる『ひっかけ問題』を狙ったものではありません。ですが(ソフトウェア開発技術者試験を受験される方は、特に)上記の条件が規定されていなかったら、どんな問題が起こりうるか？という背景も併せてお考えください。
- 考え方
この例のように7行6列の“資格取得一覧テーブル”程度のデータだと、表計算ソフトで作るほうがはるかに手軽です。ですが、ここでは練習のため、RDBMSで扱うことを考えましょう。
このテーブルを見ると、資格名の列には、似たデータが何箇所も出てきます。仮にこれが何千人・何万人となったら、そのムダも無視できません。
正規化を行うメリット
- データをコンパクトに格納する
そこで登場するのが正規化です。正規化の目的の一つは、そのムダを省いてくれる所にあります。
資格名の列だけを、列ごと削除してみます。その上で別途、こんなテーブルを作ってみました。
資格テーブル
| 資格コード | 資格名 |
|---|---|
| FE | 基本情報 |
| AD | シスアド |
| B3 | 簿記３級 |
資格コードが“FE”といえば…資格テーブルを見に行くとそこには“基本情報”と書いてある。という具合に、必要な時にだけ資格テーブルを参照することにします。こうしておけば、ちょっとスマートにデータをしまうことができます。この程度の人数だとそんなに効果はないですが、これが10万人分のデータを扱う場合、どうでしょうか？
仮に10万人分であっても、この3行2列のテーブルだけで事足ります(この2つ以外の資格に合格した場合は、話は別です。ですがその場合も、資格テーブルに新たな資格コードと資格名の列を追加してやれば対応できです)。
- データを1ヶ所書き換えたら、全体を書き換えたのと同じことになる
これから正規化を行う、本当の醍醐味をお話します。
この学校では、長年親しんできた「シスアド」という呼び名を、明日からは「初級シスアド」へと呼び替えたいと考えたとします。そして、それに対応するため、データベースも“シスアド”取得者全員の該当データを“初級シスアド”へと書き換えることにしましょう。正規化が進んでいない場合は、シスアド合格者の行数分だけデータを書き換えないといけません。つまり1万人合格していたら、1万行ぜんぶ書き換えるわけです。
ところが正規化されていると、資格テーブルの、たった1箇所を“シスアド”から“初級シスアド”へと書き換えてやると、どうでしょうか。
資格テーブル
| 資格コード | 資格名 |
|---|---|
| FE | 基本情報 |
| AD | 初級シスアド(←ここ) |
| B3 | 簿記３級 |
これだけで、資格コード“AD”を持つ全学生が、“初級シスアド”に書き換わったのと同じことになります。こうやって、データを“確実に、ラクに”書き換えられるよう、テーブルを分けるのが、正規化をする最大のメリットです(これを「データの更新時異状を防ぐ」と呼びます)。
こうやって、テーブル間を“○○コード”“△△番号”を基にしてつなげておき、その番号つながり(リレーションシップ)で別のテーブルへと必要なデータを取りに行くこと。これが、関係(リレーショナル)データベースの基本的な考え方です。
正規化を進める
第1正規形への正規化
第1正規形への正規化の、最も大事な点は、「主キー」を見つけ出すことです。
主キーとは、英語の“primary key”の日本語訳です。このテーブルにも何個か属性(その列の、いわばタイトルにあたる部分)がありますが、この中で「ある属性(の集まり)の値が定まれば、特定の1行を一意に指し示せる。そんな性質を持った属性(の集まり)」のことを、主キーと呼びます。
例えば、3つの属性(会員番号、名前、誕生日)で作られたテーブルがあったとします。
資格テーブル
| 会員番号 | 名前 | 誕生日 |
|---|---|---|
| 001 | 安藤 | 1/1 |
| 002 | 伊藤 | 5/30 |
| 003 | 伊藤 | 1/1 |
属性“会員番号”の値が“002”である行は、(002、伊藤、5/30)というデータが入った(ほかならぬ1行だけの)行のことですね。このように、ある属性の値を1個バチッと定めると、特定の1行をバッチリと指定できる性質を持つ属性が、主キーです。
では、他の属性(“名前”や“誕生日”)だと、どうでしょうか？
属性“名前”の値が“伊藤”である行は、2行あります。これでは、とある1行をバチッと定めることができません。同様に、属性“誕生日”も、主キーとしてふさわしくありません。
ところで、先ほど主キーは「属性(の集まり)」であると書きました。“集まり”と書いたのは、ある表における主キー(を構成する)属性の数は、1個だけとは限らないという意味です。先の例題ではどうでしょう。条件の部分から、以下のことが読み取れます。
- 入学時に与えられた学籍番号は、たとえ学部が変わろうが、卒業時まで変わらない。
- 仮に同姓同名がいたとしても、別々の学籍番号によって区別することができる
- 逆に言えば、1行目の西村さんと2行目の西村さんは、同一人物だとも言い切れる
- 同じ試験に何回合格しても、合格した資格は1回しか登録できない。
- 【問題】
- これら条件から判断して、この「資格取得一覧テーブル」における主キーを捜して下さい。
【正解】
| 名前 | 学籍番号 | 学部コード | 学部名 | 資格コード | 資格名 |
|---|
この例題では、属性“学籍番号”と属性“資格コード”の2つの属性の値がバチッと定まれば、1行を特定できることが言い切れます。これ以外の組み合わせ(例えば“名前”と“資格コード”)だと、同姓の人が入学すると不都合が起こりますので、主キーとしては不適切です。
- 【コラム】
- Q:他にも1行が特定できる組み合わせがあります。(“学籍番号”と“資格名”)を、主キーとしてはダメでしょうか？
- A:資格コードと資格名は1対1で対応しているため、確かに1行を特定できます。そのため“資格コード”のかわりに“資格名”を主キーの一部に使っても、間違いとは言い切れません(実際にDBMSに登録しても、動作はする)。ですが、同じならば“資格コード”を主キーにしてあげて下さい。それは“○○コード”や“△△番号”といったコード化された属性の方が、コンピュータでの処理上、都合が良いためです。
第2正規形への正規化
「AとBが決まれば、Cが決まる。だが『Bだけ決まれば、Cが決まるじゃないか』という関係を見つけたら、Bだけを主キーとしたテーブルに、くくり出す」のが、第2正規形への正規化です。
第1正規形の時に出てきた主キー(の集まり)を基に、主キーの組み合わせのうち一部分だけに関数従属するものを見つけたら、それを別の表にくくり出していきます。今回の例題の場合、第一正規形の時の主キー(の集まり)は、“学籍番号”と“資格コード”でした。その一部分だけの組み合わせは、以下の3種類です。
- “学籍番号”のみ
- “資格コード”のみ
- “学籍番号”と“資格コード”の組み合わせ
まず、“学籍番号”のみに関数従属する属性を探してみましょう。たとえば、学籍番号の値が“A008”だと決まれば…
- 名前は“西村”だと言い切れる。
- 学部コードは“A”だと言い切れる。
- 学部名は“法学部”だと言い切れる。
- (だが、資格名は“シスアド”だけとは限らない)
これらにより、属性“学籍番号”だけに関数従属する属性は(名前，学部コード，学部名)だとわかります。
次に、“資格コード”のみに関数従属する属性を探してみましょう。たとえば、資格コードの値が“AD”だと決まれば…
- 資格名は“シスアド”だと言い切れる。
- (だが、名前は“西村”だけとは限らない)
- (だが、学部コードは“A”だけとは限らない)
- (だが、学部名は“法学部”だけとは限らない)
これらにより、属性“資格コード”だけでも関数従属できちゃう属性は(資格名)だけだとわかります。
そして、ここで洗い出された属性を使って、新たにテーブルを作ります。なお、新たに作るテーブル名は、ふさわしい名前でさえあればさほど問題はありません。
学生テーブル
| 学籍番号 | 名前 | 学部コード | 学部名 |
|---|---|---|---|
| Ａ００８ | 西村 | Ａ | 法学部 |
| Ａ０２２ | 前川 | Ａ | 法学部 |
| Ｂ０２２ | 田口 | Ａ | 法学部 |
| Ｂ０９０ | 辻村 | Ｂ | 文学部 |
| Ｂ０９２ | 戸田 | Ｂ | 文学部 |
資格テーブル
| 資格コード | 資格名 |
|---|---|
| FE | 基本情報 |
| AD | シスアド |
ところで、“学籍番号”と“資格コード”という、2つの主キー属性の組み合わせは、どうしておきましょうか？
これはこれで、一つのテーブルとして、残しておくべきです。なぜなら、大事な情報である「誰が、どの資格に合格しているか」という情報は、このテーブルによってのみ管理されているからです。そのため、「誰が」を意味する学籍番号と「どの資格か」を意味する資格コードとの対応表として、このテーブルも残しておきましょう。
資格取得テーブル
| 学籍番号 | 資格コード |
|---|---|
| Ａ００８ | ＦＥ |
| Ａ００８ | ＡＤ |
| Ａ０２２ | ＡＤ |
| Ｂ０２２ | ＡＤ |
| Ｂ０９０ | ＦＥ |
| Ｂ０９０ | ＡＤ |
| Ｂ０９２ | ＦＥ |
見様によっては、もとのテーブルから、不要な列を削り取ったものとも見えますね。
- 【コラム】
- 第2正規形の定義
第2正規形は「第1正規形であり、かつ、部分関数従属性が排除された形」と定義されます。
第1正規形で出てきた主キーの組み合わせの、一部分だけで関数従属する(部分関数従属)かどうかを調べて、関数従属するならばそれを別の表にくくり出してやる(排除する)のが、第2正規形への正規化でした。
第3正規形への正規化
第2正規形への正規化作業では、第1正規形のときに出てきた主キーの、部分(いわば「カケラ」)に関数従属する属性を、別のテーブルにくくり出してきました。
第3正規形への正規化作業では、第2正規形のときに作られたテーブル内で、主キー以外の属性同士での関数従属性を探し、見つかればそれを別の表にくくり出します。
具体例で見ていきましょう。第2正規形へと正規化されたテーブルのうち、学生テーブルを見てみます。
学生テーブル
| 学籍番号 | 名前 | 学部コード | 学部名 |
|---|---|---|---|
| Ａ００８ | 西村 | Ａ | 法学部 |
| Ａ０２２ | 前川 | Ａ | 法学部 |
| Ｂ０２２ | 田口 | Ａ | 法学部 |
| Ｂ０９０ | 辻村 | Ｂ | 文学部 |
| Ｂ０９２ | 戸田 | Ｂ | 文学部 |
このテーブルの、主キー以外の属性間(名前、学部コード、学部名)での関数従属性を探してみましょう。
すると「学部コードが決まれば、学部名が決まる」という関係が見つかります。これを別のテーブルにくくり出し、元の学生テーブルから該当列を削除します。
学生テーブル
| 学籍番号 | 名前 | 学部コード |
|---|---|---|
| Ａ００８ | 西村 | Ａ |
| Ａ０２２ | 前川 | Ａ |
| Ｂ０２２ | 田口 | Ａ |
| Ｂ０９０ | 辻村 | Ｂ |
| Ｂ０９２ | 戸田 | Ｂ |
学部テーブル
| 学部コード | 学部名 |
|---|---|
| Ａ | 法学部 |
| Ｂ | 文学部 |
「もう、これ以上、関数従属性は見つかりませ～ん」というところまで、別テーブルへとくくり出し尽くす作業が、第3正規形への正規化作業です。
- これまでのテーブルを振り返ってみる
ここまでで合計4個のテーブルができあがりました。それぞれのテーブルは、こうです。
第1正規形に正規化した際の主キーを持つテーブル
資格取得テーブル
| 学籍番号 | 資格コード |
|---|---|
| Ａ００８ | ＦＥ |
| Ａ００８ | ＡＤ |
| Ａ０２２ | ＡＤ |
| Ｂ０２２ | ＡＤ |
| Ｂ０９０ | ＦＥ |
| Ｂ０９０ | ＡＤ |
| Ｂ０９２ | ＦＥ |
第2正規形に正規化した際、発生したテーブル
学生テーブル
| 学籍番号 | 名前 | 学部コード |
|---|---|---|
| Ａ００８ | 西村 | Ａ |
| Ａ０２２ | 前川 | Ａ |
| Ｂ０２２ | 田口 | Ａ |
| Ｂ０９０ | 辻村 | Ｂ |
| Ｂ０９２ | 戸田 | Ｂ |
資格テーブル
| 資格コード | 資格名 |
|---|---|
| FE | 基本情報 |
| AD | シスアド |
第3正規形に正規化した際、発生したテーブル
学部テーブル
| 学部コード | 学部名 |
|---|---|
| Ａ | 法学部 |
| Ｂ | 文学部 |
ところで、これら4つのテーブルを使って、元の“資格取得一覧テーブル”の復元ができることに、お気づきでしょうか？
【参考】資格取得一覧テーブル
| 名前 | 学籍番号 | 学部コード | 学部名 | 資格コード | 資格名 |
|---|---|---|---|---|---|
| 西村 | Ａ００８ | Ａ | 法学部 | ＦＥ | 基本情報 |
| 西村 | Ａ００８ | Ａ | 法学部 | ＡＤ | シスアド |
| 前川 | Ａ０２２ | Ａ | 法学部 | ＡＤ | シスアド |
| 田口 | Ｂ０２２ | Ａ | 法学部 | ＡＤ | シスアド |
| 辻村 | Ｂ０９０ | Ｂ | 文学部 | ＦＥ | 基本情報 |
| 辻村 | Ｂ０９０ | Ｂ | 文学部 | ＡＤ | シスアド |
| 戸田 | Ｂ０９２ | Ｂ | 文学部 | ＦＥ | 基本情報 |
学部コードつながりで“学部テーブル”と“学生テーブル”をつないだものを、学籍番号つながりで“資格取得テーブル”とつなぎ、そこに資格コードつながりで“資格テーブル”をつなぐと、(理屈の上では)モトの一覧表と同じものが得られます。
つまり、まったく情報を失わず、正規化のメリット(データの書き換えがラク、など)を得ることができるわけです。
- 【コラム】第3正規形の定義
- 第3正規形の定義は「第2正規形であり、かつ推移的関数従属性が排除された形」です。少しややこしいですが、「学籍番号が(B090だと)決まれば、その人の学部コードが(Bだと)一意に決まる。その人の学部コードが(Bだと)決まれば、その人の学部名が(文学部だと)一意に決まる」という推移的関数従属の関係を、別の表にくくり出したのを思い出して下さい。なお「Aが決まればBが決まる。Bが決まればCが決まる。Cが決まればDが決まる。Dが決まれば…」のように、推移的関数従属が何段も続く場合があります。この場合、できる所までくくり出し尽くします。
正規化を進めるメリットとデメリット
正規化を進めることで、データの「書き換え」がラクになることをご説明しました。
ところで、データを「検索」する時は、どうなのでしょうか？
複数の表を「○○コード」つながりでつなげた上で検索を行う場合、この「つなげる処理」にコンピュータの負担がかかるため、正規化を進めてテーブル数を増やすと、その分だけ検索スピードが遅くなると言われています。
よく例えられる例は「図書館の蔵書検索システム」です。いったん出版された本の、書籍名や著者名が変わってしまう事はまず無いため、この場合はデータを正規化するメリットが薄いです。むしろ検索スピードを追求したシステムとした方がユーザに喜ばれるので、わざと正規化度を下げて(第2正規形あたりに留める、など)テーブルを作っておく事があります。
- 【コラム】
- これ以降の正規化について
データベース正規化理論を打ち立て、OLAPの提唱者でもある故E.F.Codd博士は、この先「ボイスコッド正規形」「第4正規形」「第5正規形」も規定しています。ですがこれらは初級シスアド試験では出題されていません(ソフトウェア開発技術者試験でも)。
とは言っても興味の湧いた方のために、ボイスコッド正規形のみ、軽く触れておきましょう。
- 【背景】
- シスアド合格が３度目の辻村さん(Ｂ０９０)から「複数行、登録して欲しい」という要望が来ました。そこで同じ資格に複数回合格した場合には、その回数分だけ登録できる事にします。また、履歴管理のために合格発表年月(属性名“取得年月”)の列を追加し、さらに、資格取得勉強時の事務連絡用に届け出たメールアドレス(属性名“取得時メール”)の列も追加します。
- 【問題】
- この表に主キーを設定し、第3正規形まで正規化して下さい。なお、追加される条件は、以下の２点です。
- 属性名“取得時メール”は、特定の１名にのみ届くメールアドレスとします。
- メールアドレスを、主キーの一部として使っても良いものとします。
- “シスアド”と”基本情報”の両方を、同じ年月に取得することは無いものとします。
ボイスコッド正規形が作れる資格取得一覧テーブル
| 名前 | 学籍番号 | 学部コード | 学部名 | 資格コード | 資格名 | 取得年月 | 取得時メール |
|---|---|---|---|---|---|---|---|
| 西村 | Ａ００８ | Ａ | 法学部 | ＦＥ | 基本情報 | 200611 | a008@aaa.ac.jp |
| 西村 | Ａ００８ | Ａ | 法学部 | ＡＤ | シスアド | 200411 | a008@aaa.ac.jp |
| 前川 | Ａ０２２ | Ａ | 法学部 | ＡＤ | シスアド | 200405 | a022@aaa.ac.jp |
| 前川 | Ａ０２２ | Ａ | 法学部 | Ｂ３ | 簿記３級 | 200611 | m@ekawa.jp |
| 田口 | Ｂ０２２ | Ａ | 法学部 | ＡＤ | シスアド | 200511 | b022@aaa.ac.jp |
| 辻村 | Ｂ０９０ | Ｂ | 文学部 | ＦＥ | 基本情報 | 200405 | b090@aaa.ac.jp |
| 辻村 | Ｂ０９０ | Ｂ | 文学部 | ＡＤ | シスアド | 200311 | tsuji@rim.net |
| 辻村 | Ｂ０９０ | Ｂ | 文学部 | ＡＤ | シスアド | 200505 | b090@aaa.ac.jp |
| 辻村 | Ｂ０９０ | Ｂ | 文学部 | ＡＤ | シスアド | 200611 | b090@aaa.ac.jp |
| 戸田 | Ｂ０９２ | Ｂ | 文学部 | ＦＥ | 基本情報 | 200605 | toda@mifty.com |
【解答】
この表の主キーは、属性3個で構成されます。実は主キーの組み合わせが2種類あるのですが、そのうち1種類でも解ければ初級シスアドとしては充分なレベルです。属性“取得時メール”の扱いが曲者です。
教科書通りの正規化の場合、主キーは(学籍番号、資格コード、取得年月)となります。属性名“取得時メール”は主キーにはなりません。
ですがこの属性名“取得時メール”も正規化したい場合、取得時メールの値さえ決まればその人の学籍番号が一意に定まることから、いっそ主キーを(取得時メール、資格コード、取得年月)とすることもできます。
このように「Dが決まれば、Aが決まる」「AとBとCが決まれば、Dが決まる」「だがDは、AとBとCのあらゆる組み合わせに部分関数従属しない」という関係がある場合、普通に正規化するとDは主キーにはなれませんが、Dを第1正規形の主キーに組み入れた(つまり、AとBとCで主キーだったものを、DとBとCで主キーとする)上で第3正規形まで正規化すると、第3正規形の時点でボイスコッド正規形も満たしてくれます。
E-R図
初級シスアド試験の、特に午後のデータベース問題では、E-R図を読み解かせる問題が出題されます。E-R図は、関係データベースを設計や分析に欠かせない、便利な図法です。
- E(Entity：実体)
長方形で表し、多くの場合“もの”の名前(名詞)が名付けられます。
- R(Relationship：関連)
- もの同士がどのような具合で“関連”があるのかを表す線です。そしてこの線には(必要に応じて)矢印をつけ、「矢の刺さっている側が“多(た)”」という、数の関係(カーディナリティ)も同時に表現できます。
E-R図は、いろんな学者がいろんな記法(描き方)を提唱しています。情報処理技術者試験で主に用いられる図法は、覚えやすい“バックマン線図”です。ここでも、それに基づいて表記します。
- E-R図の読み方
次に、E-R図の基本的な読み方から、見ていきましょう。
“1対1”の関連
+------+ +---------+ I 学 部 I ------ I 学 部 名 I +------+ +---------+
今回の例題では、1つの学部コードに、1つの学部名が対応していました。この場合、それぞれの“もの”同士は「1対1で対応している」と呼びます。
関係データベースでは、1対1の関係が見つかれば、この2つの“もの”を、1つの表へとまとめます。
“1対多”の関連
+------+ +------+ I 学 部 I -----> I 学 生 I +------+ +------+
今回の例題では、1つの学部に、複数の学生が在籍していました。ですが1人の学生が、同時に複数の学部には在籍できません。この場合、それぞれの“もの”同士は「1対多で対応している」と呼びます。
この矢印、よく見ると関数従属していく方向と、ちょうど逆です。
“多対多”の関連
+------+ +------+ I 学 生 I <----> I 資 格 I +------+ +------+
今回の例題では、1人の学生が、複数の資格を取得できました。そして1つの資格は、複数の学生に取得されています。このように、1対多の関係がお互いに成り立つ場合、それぞれの“もの”同士は「多対多で対応している」と呼びます。
関係データベースでは、このような“多対多”の関係が見つかれば、それは極力“1対多”の関係へと作り直していきます。
- 連関(れんかん)エンティティ
“多対多”の関係が見つかった時、それを“1対多”の関係に作り直すため、なかば強引に埋め込むエンティティのことを、連関(れんかん)エンティティと呼びます。
“多対多”とは「1対多の関係が、お互いに成り立っている」形です。こういう形を想像してみて下さい。
+------+ +------+ I 学 生 I <----- I 資 格 I +------+ -----> +------+
そこで“多対多”の仲を強引に引き裂き、間に新しく“もの”を埋め込んだ上で、その“もの”に(多少強引な名前で構いませんので)ネーミングしてみます。
+------+ +-----------+ +------+ I 学 生 I I 資 格 取 得 I <----- I 資 格 I +------+ -----> +-----------+ +------+
1人の学生が、同時に複数の資格取得ができます。そして1つの資格は、複数の資格取得がなされています。この「資格取得」という“もの”(日本語文法としては“体言”と呼ぶ方がふさわしい？)が、連関エンティティにあたります。
E-R図の、関係データベースへの応用
次に、E-R図の、関係データベースへの応用を見ていきましょう。
E(Entity：実体)は、ものの名前(名詞などの体言)であり、R(Relationship：関連)は、もの同士のつながりの線の事でした。これらは具体的には、
- 実体(エンティティ)とは、正規化された後にできた表(テーブル)のこと
- 関連(リレーションシップ)とは、それぞれの表が「ナニつながり」か
だと考えて下さい。
先の例題を第3正規形まで正規化すると、テーブルが4個できました。そして各テーブルには、ふさわしい名前もつけました。
- 資格取得表(学籍番号 ， 資格コード)
- 資格表(資格コード ， 資格名)
- 学生表(学籍番号 ， 名前 ， 学部コード)
- 学部表(学部コード ， 学部名)
これらのテーブル名を“実体(エンティティ)”とみなします。
次に、それぞれのテーブルが、「どのような番号(コード)をたどってつなぐと、元の表を復元できるか」の観点から、テーブル同士のつながりを考えます。
- 「学部」表と「学生」表は、学部コードつながり
- 「学生」表と「資格取得」表は、学籍番号つながり
- 「資格」表と「資格取得」表は、資格コードつながり
でした。これらのつながりを“関連(リレーションシップ)”とみなします。
では、テーブル名と、そのつながりを、線でつなぎましょう。
+------+ +------+ +-----------+ +------+ I 学 部 I ------ I 学 生 I ------ I 資 格 取 得 I ------ I 資 格 I +------+ +------+ +-----------+ +------+
次に矢をどう書き込むかですが、各テーブルにおいて複数行にわたって同じ値がある方を“多”と考えます。これは結果的には、テーブルを作っていった方向と“ちょうど正反対の矢”を書き込むにもなります。
+------+ +------+ +-----------+ +------+ I 学 部 I -----> I 学 生 I -----> I 資 格 取 得 I <----- I 資 格 I +------+ +------+ +-----------+ +------+
矢を書き込んで、改めて見てみましょう。
- 1つの「学部」には、複数の「学生」がいる
- 1人の「学生」が、複数の「資格取得」を行っている
- 1種類の「資格」に対し、複数の「資格取得」がなされている
確かに、数の関係が成り立っています。
試験の出題では、問題用紙にE-R図が書いてあり、エンティティ間に線が引いてあったならば「該当するテーブル間は、何かの属性でつながっているのだ」とわかります。
初級シスアドの午後試験では、線の引かれたE-R図と、2つのテーブルのうち片方にだけ空欄がある、という出題パターンが多いです。
【例題】空欄［ ａ ］に入れるべき属性名を答えよ。
+------+ +------+ I 学 部 I -----> I 学 生 I +------+ +------+
学生表
| 学籍番号 | 名前 | ［ ａ ］ | 住所 | 連絡先 |
|---|
学部表
| 学部コード | 学部名 |
|---|
この例題のように、もう片方のテーブルには、ちゃんと属性名が記されている場合、各テーブルが、どの属性でつながっているのか確認した上で、［学部コード］と穴埋めしてあげて下さい。
E-R図と各テーブルとの関係がしっかり理解できると、関係データベースの設計がスムーズにできます。実務で関係データベースを設計する場合、今回練習したように正規化した後にE-R図を書く“ボトムアップ”と、まず先にE-R図を書いてから各テーブルに属性を書き足していく“トップダウン”を使い分けています。
一般には、既にある情報システムの改良にはボトムアップ型のアプローチが、まったく新規の開発にはトップダウン型のアプローチが向いている、と言われています。
E-R図についての説明は、これで終わります。いまいちど読み返して、正規化とE-R図は、密接だということを理解しておいて下さい。必ず、実務に役立ちます。
【コラム】連関エンティティに書き足す属性
連関エンティティには、その両側のエンティティから、主キーを代表選手として送り込みます。この場合、両側のテーブルの主キーは、それぞれ“学籍番号”と“資格コード”でした。
+------+ +-----------+ +------+ I 学 生 I -----> I 資 格 取 得 I <----- I 資 格 I +------+ +-----------+ +------+
資格取得テーブル
| 学籍番号 | 資格コード |
|---|
じつは今回の課題は、先にE-R図を書いてからトップダウンアプローチで作成しました。
データベースの応用
- データウェアハウス(Data Warehouse)
データウェアハウスとは、業務の根幹に関わるデータベース(これを「基幹系データベース」と呼びます)とは別に構築された、データ分析用のデータが入ったデータベースのことです。データウェアハウスは、企業の現状や将来とる戦略を分析する目的で活用されます。
- OLAP(On-Line Analytical Processing)
データベースの正規化理論を打ち立てた、故E.F.Codd博士が晩年に提唱した考え方で、集めたデータを多角的に分析する手法の一つです。
OLAPは「多次元データベース」と呼ばれる技術が中核になっています。多次元データベースとは、同じフォーマットの表計算シートが重なったものを想像して下さい。
この3次元のデータは「キューブ」と呼ばれ、キューブからいずれかの次元軸方向にデータを切り出すことを「スライシング」と呼びます。また、分析対象を絞り込む(週ごとの分析から日ごとの分析へ、など)事を「ドリルダウン」と呼び、反対に分析対象を広くする(週ごとの分析から月ごとの分析へ、など)事を「ドリルアップ」と呼びます。
なお、OLAPという言葉は、OLTP(On-Line Transaction Processing)が基幹系データベースに対して使われる用語なのに対し、データウェアハウスのように基幹系データベースとは別に作られた(データ分析用の)データベースに対して名付けられた言葉です。
- データマイニング(data mining)
“mining”を辞書で引くと、採鉱や鉱業という意味だそうです。データウェアハウスのデータを人間が分析するのは大変なので、専用のツールを使って、統計学的な計算を駆使して相関関係などを洗い出す(宝の山を掘り当てるイメージです)ことを、データマイニングと呼びます。
- 【コラム】
- マイクロソフト社のSQL Serverには、データウェアハウス作成のためのツールDTS(Data Transform Service)と、OLAPとデータマイニングのためのツール“Analysis Services”が入っています。
SQL(Structured Query Language)
SQLによるデータ検索
多くのRDBMSでは、SQL(Structured Query Language:構造化照会言語)というプログラミング言語に似た言語をサポートしており、これを使って、データの検索やテーブルの作成、テーブルへのデータの追加や削除などができます。
- SELECT文を使ってデータを取り出す
SQLを使って、テーブルからデータを検索したい場合、“SELECT文”と呼ばれる書き方を使います。
SELECT 列の名前 FROM テーブルの名前 WHERE 該当する行の条件
- SELECT文の使用例
学部コードが“Ａ”である人の、名前を調べようと思います。 学生テーブル
| 学籍番号 | 名前 | 学部コード |
|---|---|---|
| Ａ００８ | 西村 | Ａ |
| Ａ０２２ | 前川 | Ａ |
| Ｂ０２２ | 田口 | Ａ |
| Ｂ０９０ | 辻村 | Ｂ |
| Ｂ０９２ | 戸田 | Ｂ |
入力するSQL文
SELECT 名前 FROM 学生テーブル WHERE 学部コード = 'Ａ'
実行結果
| 名前 |
|---|
| 西村 |
| 前川 |
| 田口 |
あらかじめテーブルにデータを格納した上で、DBMSにSELECT文で指示を与えると、指定した列と、指定した条件の行が交わった部分のデータを取り出せます。なお、特にプログラミング言語をかじったことのある方は要注意ですが、WHERE句で書かれる条件の“=(イコール記号)”は、代入を意味するイコールではありません。あくまでも「左右の値が等しい」という意味です。
- SELECT，FROM，WHERE の意味
SELECT 列の名前
テーブルから取り出したい列(属性)を指定する部分です。もし、別のテーブルにも同じ列名がある場合は、一体どちらのテーブルの列なのか？によって検索結果が変わってくる場合があります。そのため、「テーブルの名前.列の名前」と書くのが確実です。
FROM テーブルの名前
検索したいテーブルを指定する部分です。複数のテーブルを組み合わせて検索処理を行う場合は、各テーブル名をカンマで区切って列挙します。
WHERE 該当する行の条件
FROM句で指定したテーブルから、取り出したい行を指定します。この時に使う主な演算子は、以下のものがあります。
- =
- 左右の値が等しい行を選択する
- NOT =
- 左右の値が等しくない行を選択する
- <>
- 左右の値が等しくない行を選択する
- IN
- 左右の値が等しい行が、複数行ありそうな場合に使う
- NOT IN
- 左右の値が等しくない行が、複数行ありそうな場合に使う
【練習問題】
学生テーブル
| 学籍番号 | 名前 | 学部コード |
|---|---|---|
| Ａ００８ | 西村 | Ａ |
| Ａ０２２ | 前川 | Ａ |
| Ｂ０２２ | 田口 | Ａ |
| Ｂ０９０ | 辻村 | Ｂ |
| Ｂ０９２ | 戸田 | Ｂ |
このテーブルにおいて、
SELECT 名前 FROM 学生テーブル WHERE 学籍番号 = 'B022'
と指定した場合に、取り出されるデータは？
- 【解き方】
- SELECT句に書かれた、該当する列を(ペンか何かで)縦方向に丸で囲みます。そして、WHERE句で書かれた条件に合う行も横方向に丸で囲むと、その交わる部分が取り出されるデータです。
【実行結果】
| 名前 |
|---|
| 田口 |
あくまでも、指定した列と行の、交点のデータしか取り出されません。検索条件によっては、全くデータが取り出されないことも、ありえます。
今回は1個だけのテーブルから検索しましたが、複数のテーブルを組み合わせて検索することもできます(むしろその方が、試験での出題比率も高いです)。
- 2つ以上のテーブルを組み合わせての検索
複数のテーブルを組み合わせて検索させたい場合、各テーブルの、つなげたい列の名前を、イコールで結んでやる手法が、よく出題されますので押さえておいて下さい。
つなげ方の基本は、E-R図を描いたら“なにつながり”になるかを考え、その属性をイコールで結びます。
- 【例題】
- 初級シスアド平成14年秋期午前問32
Ａ表とＢ表に対して次のＳＱＬ文が行う関係演算はどれか。
SELECT 得意先名, A.製品番号, 製品名, 受注数 FROM A, B WHERE A.製品番号 = B.製品番号 ORDER BY 得意先名
Ａ
| 得意先名 | 製品番号 | 受注数 |
|---|---|---|
| Ｘ商店 | Ｂ００１ | 3,000 |
| Ｙ代理店 | Ａ００２ | 2,000 |
| Ｚ販売店 | Ａ００１ | 2,500 |
Ｂ
| 製品番号 | 製品名 |
|---|---|
| Ａ００１ | テレビ |
| Ａ００２ | ビデオデッキ |
| Ｂ００１ | ラジオ |
ア 結合 イ 射影 ウ 選択 エ 併合
【正解】ア
この例題で学んで頂きたいのは、2つ以上のテーブルを用いたSELECT文の書き方です。
SELECT文の基本的な書き方は、“SELECT 列の名前 FROM テーブル名 WHERE 該当する行”でした。なお、SQL文の最後にある“ORDER BY”は、処理結果を並べ替え(ソート)するための命令です。
FROM句に複数のテーブルを指定すると、各テーブルをつないで処理します。これを結合(join)と呼びますが、RDBMSは自動的に下記のような大きなテーブルを作った上で処理をする、と考えてみて下さい。
RDBMSが、作業用に作成するテーブル
| (Ａ表) | (Ｂ表) | |||
|---|---|---|---|---|
| 得意先名 | 製品番号 | 受注数 | 製品番号 | 製品名 |
| Ｘ商店 | Ｂ００１ | 3,000 | Ａ００１ | テレビ |
| Ｘ商店 | Ｂ００１ | 3,000 | Ａ００２ | ビデオデッキ |
| Ｘ商店 | Ｂ００１ | 3,000 | Ｂ００１ | ラジオ |
| Ｙ代理店 | Ａ００２ | 2,000 | Ａ００１ | テレビ |
| Ｙ代理店 | Ａ００２ | 2,000 | Ａ００２ | ビデオデッキ |
| Ｙ代理店 | Ａ００２ | 2,000 | Ｂ００１ | ラジオ |
| Ｚ販売店 | Ａ００１ | 2,500 | Ａ００１ | テレビ |
| Ｚ販売店 | Ａ００１ | 2,500 | Ａ００２ | ビデオデッキ |
| Ｚ販売店 | Ａ００１ | 2,500 | Ｂ００１ | ラジオ |
Ａ表の行数×Ｂ表の行数＝9行の、とても大きなテーブルです。このように、片方のテーブルの1行に対し、もう片方の全ての行を組み合わせたものを“直積(ちょくせき)”と呼びます。
この直積のうち、“WHERE A.製品番号 = B.製品番号”、つまり左右で「製品番号が同じ」である行を、太字で表してみます。
RDBMSが、作業用に作成するテーブル
| (Ａ表) | (Ｂ表) | |||
|---|---|---|---|---|
| 得意先名 | 製品番号 | 受注数 | 製品番号 | 製品名 |
| Ｘ商店 | Ｂ００１ | 3,000 | Ａ００１ | テレビ |
| Ｘ商店 | Ｂ００１ | 3,000 | Ａ００２ | ビデオデッキ |
| Ｘ商店 | Ｂ００１ | 3,000 | Ｂ００１ | ラジオ |
| Ｙ代理店 | Ａ００２ | 2,000 | Ａ００１ | テレビ |
| Ｙ代理店 | Ａ００２ | 2,000 | Ａ００２ | ビデオデッキ |
| Ｙ代理店 | Ａ００２ | 2,000 | Ｂ００１ | ラジオ |
| Ｚ販売店 | Ａ００１ | 2,500 | Ａ００１ | テレビ |
| Ｚ販売店 | Ａ００１ | 2,500 | Ａ００２ | ビデオデッキ |
| Ｚ販売店 | Ａ００１ | 2,500 | Ｂ００１ | ラジオ |
これら太字の3行について、“SELECT 得意先名, A.製品番号, 製品名, 受注数”を実行すると、このようなデータが取り出されます。
| 得意先名 | 製品番号 | 製品名 | 受注数 |
|---|---|---|---|
| Ｘ商店 | Ｂ００１ | ラジオ | 3,000 |
| Ｙ代理店 | Ａ００２ | ビデオデッキ | 2,000 |
| Ｚ販売店 | Ａ００１ | テレビ | 2,500 |
なお、実行前と実行後で、列の並び順が変わっていますが、SELECT句で指定した順番(得意先名, A.製品番号, 製品名, 受注数)の通りに実行されます。
- 【コラム】その他の用語について
- 選択 テーブルから行を指定してデータを取り出すこと(selection)
- 射影 テーブルから列を指定してデータを取り出すこと(projection)
- 併合と結合
- 併合と結合は、日本語としては似た意味ですが、情報処理用語で併合(マージ)というと、2つのテーブルから1つのテーブルを作る際、共通する列名(属性名)のものをひとつのテーブルにまとめることを呼びます。
社員マスタファイル
| 社員番号 | 名前 |
|---|---|
| A205 | 佐藤 |
| A288 | 鈴木 |
| A320 | 田中 |
追加用の差分ファイル
| 社員番号 | 名前 |
|---|---|
| A234 | 加藤 |
| A238 | 木村 |
| A311 | 楠 |
これらを併合(マージ)処理によって1つのテーブルにまとめると、社員マスタファイルは新たにこのようになります。
社員マスタファイル
| 社員番号 | 名前 |
|---|---|
| A205 | 佐藤 |
| A234 | 加藤 |
| A238 | 木村 |
| A288 | 鈴木 |
| A311 | 楠 |
| A320 | 田中 |
- 相関名(そうかんめい)
ちょっと長いSELECT文を書くと、長いテーブル名を何度も書くのが面倒になる時があります。そんな時に便利なのが、テーブル名に“あだ名”をつける方法です。この、あだ名の事をSQLでは「相関名」と呼びます。
例えば、以下の2つのテーブルから、「学部名が“法学部”である人の学籍番号を調べたい」場合のSQLを考えます。
学生表
| 学籍番号 | 学部コード |
|---|---|
| A008 | A |
| B022 | A |
| B092 | B |
学部表
| 学部コード | 学部名 |
|---|---|
| A | 法学部 |
| B | 文学部 |
SELECT 学生表.学籍番号 FROM 学生表 , 学部表 WHERE 学生表.学部コード = 学部表.学部コード AND 学部表.学部名 = '法学部'
実はこのSQL文は、このように書き換えることができます。
SELECT X.学籍番号 FROM 学生表 X , 学部表 Y WHERE X.学部コード = Y.学部コード AND Y.学部名 = '法学部’
実行結果
| 学籍番号 |
|---|
| A008 |
| B022 |
FROM句にある、テーブル名のすぐ後ろのアルファベット(ここでは“X”“Y”)が、相関名(いわば“あだ名”)です。このようにSQLには、FROM句で名前の関連付けをしておけば、他の箇所も読み替えてくれる機能があります。
なお、相関名はFROM句で規定されますが、ちゃんと行をさかのぼって、SELECT句でも読み替えてくれます。
- 副問合(ふくといあわ)せ
「副問合せ」の仕組みを使うと、SELECT文で検索して得られた結果をもとに、別のSELECT文の検索を行えます。
ここでは「まずは学生表の学籍番号が“A008”である人の学部コードを調べ、その学部コードに対応する学部名を調べる」処理を、副問合せを使って書いてみます。
学生表
| 学籍番号 | 学部コード |
|---|---|
| A008 | A |
| B022 | A |
| B092 | B |
学部表
| 学部コード | 学部名 |
|---|---|
| A | 法学部 |
| B | 文学部 |
SELECT 学部名 FROM 学部表 WHERE 学部コード = (SELECT 学部コード FROM 学生表 WHERE 学籍番号 = 'A008')
実行結果
| 学部名 |
|---|
| 法学部 |
先に、カッコの内側のSELECT文が処理されます。そこで得られた結果(学部コードは“A”)が、カッコの外側のSELECT文に受け渡されます。
- 副問合せに使う、=(イコール)とINの違いについて
同じテーブルを使って、「学籍番号の頭文字が“B”で始まる人の、学部名を調べたい」というつもりで、副問合せを書いてみます。なお、ここで出てくる%記号はワイルドカードとも呼ばれ、LIKEと合わせて使われます。この場合「半角文字Bで始まるような文字列を、すべて拾い出す」という意味になります。
(こんな実行結果が欲しい)
| 学部名 |
|---|
| 法学部 |
| 文学部 |
SELECT 学部名 FROM学部表 WHERE 学部コード = (SELECT 学部コード FROM 学生表 WHERE 学籍番号 LIKE 'B%')
実行結果はどうなるでしょうか？
実は、エラー表示が出て検索処理が止まります。
次に、副問合せ結果を橋渡ししているイコール記号を、“IN”と書き換えてみます。
SELECT 学部名 FROM学部表 WHERE 学部コード IN (SELECT 学部コード FROM 学生表 WHERE 学籍番号 LIKE 'B%')
実行結果
| 学部名 |
|---|
| 法学部 |
| 文学部 |
今度は正しく検索されました。
副問合せ部分の検索結果が2件以上出てくる、と予想された場合には、=(イコール記号)のかわりにINを使ってあげて下さい。
- =
- 副問合せの実行結果が、多くても1件までの場合に使える書き方
- IN
- 副問合せの実行結果が、何件であっても使える書き方
ならば最初から、副問合せの場合は、INでつないでおけば無難です。「副問合せの処理でエラーが出たのはなぜか」という出題パターンで、わざとイコール記号でつないだ副問合せ文を提示してきた場合、この、イコール部分を疑ってみて下さい。
SQLによるテーブル操作
SQLの機能は、データを検索するだけではありません。テーブルを作ったり、作ったテーブルにデータを追加(削除)する機能もあります。これらを「テーブル操作」と呼びます。
- CREATE TABLE文で、テーブルを作る
テーブルを作りたい場合、MS-Accessだと専用の画面がありますが、SQLでも同等のことができます。ここでは、学部表と学生表という2つのテーブルを作るSQL(CREATE TABLE文)を書いてみます。
CREATE TABLE 学部表( 学部コード CHAR(1), 学部名 CHAR(30), PRIMARY KEY(学部コード) ) CREATE TABLE 学生表( 学籍番号 CHAR(4), 名前 CHAR(30), 学部コード CHAR(1), PRIMARY KEY(学籍番号), FOREIGN KEY(学部コード) REFERENCES 学部表(学部コード) )
実行結果
学部表
| 学部コード | 学部名 |
|---|
学生表
| 学籍番号 | 名前 | 学部コード |
|---|
CREATE TABLE文は、その名の通り、テーブルの枠組みを作り出しますが、まだデータは入っていません。
「CHAR(けた数)」は、その属性が何文字分の幅を持つかを指定する書き方です。これは、必要とするメモリをどれだけ確保するかを明確にするために用います。
「PRIMARY KEY(属性名)」は、このテーブルの主キー属性はこれだよ、と教えてやる書き方です。
「FOREIGN KEY(属性名) REFERENCES テーブル名(そのテーブルでの属性名)」は、「この属性は、よその表のあの属性とつながっているよ」と教えてやる書き方です。REFERENCESと、三単現のSがついていることに注意して下さい。
- INSERT INTO文で、テーブルにデータを追加する
テーブルにデータを追加する場合のSQLは、INSERT INTO文です。学部表に1行追加してみましょう。
INSERT INTO 学部表( 学部コード, 学部名 ) VALUES( 'A' , '法学部' )
実行結果 学部表
| 学部コード | 学部名 |
|---|---|
| A | 法学部 |
属性名と、1行分のデータを、いちいち指定しないといけません。VALUESと、複数形のSが付いていることに注意して下さい。
- DELETE文で、テーブルからデータを削除する
テーブルからデータを削除する場合のSQLは、DELETE文です。先ほどの学部表から、条件に合致する行を削除してみます。
DELETE FROM 学部表 WHERE 学部名 = '法学部'
実行結果 学部表
| 学部コード | 学部名 |
|---|
- 一貫性制約記述
先ほど、CREATE TABLE文のお話に、“FOREIGN KEY ～ REFERENCES ～”とありました。特にこの指定をせずともテーブル作成や、データの検索・追加・削除もできてしまうのですが、あえてこの指定をするのは、なぜでしょうか？
例題で見てみましょう。
- 【例題】
- 初級シスアド平成13年秋期午前問28
“BUSHO”表と“SHAIN”表があり，“SHAIN”表は次のSQL文で定義されている。
CREATE TABLE SHAIN (S_CODE CHAR(3) PRIMARY KEY, S_NAME NCHAR(3), BU_CODE CHAR(3), S_AGE DECIMAL(2), FOREIGN KEY(BU_CODE) REFERENCES BUSHO, CHECK (S_AGE BETWEEN 18 AND 60) )
また，“BUSHO”表と“SHAIN”表には現在次のようなデータが格納されている。
BUSHO
| BU_CODE | BU_NAME |
|---|---|
| B01 | 人事部 |
| B02 | 総務部 |
| B03 | 経理部 |
SHAIN
| S_CODE | S_NAME | BU_CODE | S_AGE |
|---|---|---|---|
| 111 | 山田 | B02 | 60 |
| 122 | 川上 | B03 | 55 |
| 233 | 田中 | B01 | 35 |
| 259 | 岡本 | B02 | 34 |
このとき，“SHAIN”表に追加可能なタプルとして，適切なものはどれか。
| S_CODE | S_NAME | BU_CODE | S_AGE | |
|---|---|---|---|---|
| ア | 012 | 山田 | B03 | 60 |
| イ | 111 | 山田 | B02 | 55 |
| ウ | 320 | 山本 | B04 | 34 |
| エ | 920 | 山下 | B03 | 17 |
【正解】ア
【解説】タプルとは、「行」と同じ意味だととらえて下さい。
正解はアなのですが、ここで大事なのは「なぜ、他の選択肢が間違いなのか?」です。
- イ
- S_CODEに“PRIMARY KEY”指定を行うと、すでに存在する主キーの値と全く同じ値のデータを追加しようとした際に「これじゃ一意にならないよ」と、自動的にエラーにしてくれます(これを「一意性制約」と呼びます)。
- エ
- S_AGEに“CHECK”指定を行うと、17という低すぎる値を追加しようとした際に「範囲に収まってないよ」と、自動的にエラーにしてくれます(これを「検査制約」と呼びます)。
では、ウはなぜエラーになるのでしょうか？
実はテーブルを定義する際に、“FOREIGN KEY ～ REFERENCES ～”指定を行うと、「本当に(REFERENCESしに行く表に)そんなデータ、あったかな？」と見に行って、すでに存在していることを確認した上でデータ追加を許してくれます(これを「参照制約」と呼びます)。
これによって、ありもしない部署(“BUSHO”表にありもしないBU_CODEの値)のデータを追加しようとしたら、自動的にエラーにしてくれるのです。
さらに、“FOREIGN KEY ～ REFERENCES ～”指定には、見に行かれる側のテーブル(“BUSHO”表)のデータを好き勝手に行削除しようとした時に、エラーではじいてくれる機能もあります。たとえば“BUSHO”表のBU_CODEがB01(人事部)という行を勝手に削除すると、あとでS_CODEが233番の田中さんの部署名が検索できずに困ります。それを防ぐために、削除の前に自動的にエラーではじいてくれるのです。
このように“PRIMARY KEY”“CHECK”“FOREIGN KEY ～ REFERENCES”指定により、ポカミスによってデータがグチャグチャになるの未然に防いでくれる、フールプルーフが働きます。これらを総称して「一貫性制約(integrity constraint)」と呼びます。