カテゴリ: 5.データベース

1.データベースとは
データベース(DataBase)とは、言葉の通り、データ(Data)の土台(Base)となるものです。
例えば、各企業では、顧客データベースがあります。ここでは、顧客ID,顧客名、所在地などの情報が、Excelなどの表計算ソフトの表のように整理されています。
多少乱暴な言い方ですが、データベースの見た目は、Excelの表と思っていいでしょう。
情報セキュリティマネジメント試験を目指す剣持成子_7 

じゃあ、わざわざデータベースを利用せずに、Excelでもいいのでは?




小さいシステムであれば、Excelで十分でしょう。でも、データベースならではの機能があります。
たとえば、データの重複をさけるように正規化をしますし、SQL文を使って高度な組み合わせができます。また、インデックスを使って高速検索ができるようになります。

※SQL(Structured Query Language)とを直訳すると、Structured(構造化された)Query(問い合わせ)の言語(Language)となります。Query(問い合わせ)先はデータベースで、データベースへの操作を行う言語です。

(1)テーブル(表)、フィールド(項目)、レコード、行、列
これらの言葉を、理解しておきましょう。
図のように、テーブルの横軸がレコード(行)で、縦軸がフィールド(列)になります。
レコードとフィールド_情報セキュリティマネジメント試験 

(2)インデックス
インデックス(index)は「索引」という意味です。本の最後にある索引をイメージしてください。索引があれば、探したい情報に早くたどり着けます。この機能が、データベースにもあります。
過去問(H24年秋FE午前問27)では、「関係データベースの表の列に利用者がインデックスを設定する目的」として、「データの格納位置への効率的なアクセスが可能となり,検索速度の向上が期待できる」と述べられています。

(3)関係データベース
 このような表で表されるデータベースを関係データベース(リレーショナルデータベース、RDB)と言います。関係(リレーション)でいくつもの表がつながっているから、このように言われます。
参考までに、関係データベース以外には、木構造の階層型データベースとネットワーク型データベースの3つがあります。

ある値Xが決まると、Yが一意に決めることができる場合、「YはXに関数従属している」と言います。
以下の顧客テーブルがあるとします。
顧客(顧客番号、氏名、住所、電話番号) ※主キーは「顧客番号」です。
このとき、「顧客番号」が決まると「氏名」が決まります。また、氏名だけでなく、「住所」や「電話番号」も決まります。
このような場合、「氏名は顧客番号に関数従属している」といい、「顧客番号→氏名」と表します。

参考ですが、過去問(H28春AP午前)の抜粋(一部改変)を見てみましょう。
関数従属は、次のように表されます。
(1)属性Xの値が与えられると,属性Yの値を一意に決めることができる。(左図)
(2)属性Xと属性Yの二つの値が与えられると,属性Zの値を一意に決めることができる。(右図)
aa


2.キー(key)
①主キー
行を一意に識別するものを候補キーと言います。この中から、管理上最適なものが主キーです。
上記の「受注明細テーブル」の主キーは、「受注番号と明細番号」です。受注番号だけでは行を1つに特定できず、2つが合わさって初めて一つに特定できます。
また、上記の「商品テーブル」の主キーは、「商品コード」です。
応用情報技術者試験を勉強する成子

なぜ候補キーと言うのですか?
主キーの候補だからです。
ただ、応用情報技術者試験においては、両者の違いを厳密に区別しなくてもいいと思います。主キー=候補キーと考えて、問題を解いてもらえばいいでしょう。

②外部キー
外部を参照するキーです。「受注明細テーブル」の外部キーは「商品コード」です。これにより、商品テーブルの情報を参照することができます。
862492bc
参照することは分かりました。
でも、わざわざ外部キーとして定義しなくても、勝手に参照していればいいのではないでしょうか。
過去問(H28春FE午前問29)では、「関係データベースにおいて,外部キーを定義する目的」として,「関係する相互のテーブルにおいて,レコード間の参照一貫性が維持される制約をもたせる。」とあります。もし、この外部キーによる参照制約がないと、参照先の項目が削除されて存在しないなど、データとしての不整合が発生する場合があります。

3.E-R 図
データの実体(entity)とその関連(relationship)を表現します。
過去問(H24FE春問28)では、「E-R図に関する記述」として、「業務で扱う情報をエンティティ及びエンティティ間のリレーションシップとして表現する。」と述べられています。
エンティティというのがよく分からないかもしれません。エンティティとはデータの実体のことですが、表(テーブル)と考えていいです。たとえば、上に表を掲載していますが、「受注明細」「商品」が、それぞれエンティティになります。
両者の関係をE-R図で表すと、以下になります。
情報セキュリティマネジメント試験のE-R図 

過去問(今回はH27年春DB午後の資料を引用)では、E-R図のルールに関して、以下の説明があります。
(1)エンティティタイプとリレーションシップの表記ルールを,図1に示す。
 ① エンティティタイプは,長方形で表し,長方形の中にエンティティタイプ名を記入する。
 ② リレーションシップは,エンティティタイプ間に引かれた線で表す。
   “1対1"のリレーションシップを表す線は,矢を付けない。
   “1対多”のリレーションシップを表す線は,“多”側の端に矢を付ける。
   “多対多”のリレーションシップを表す線は,両端に矢を付ける。
データベーススペシャリスト試験のe-r図

なぜデータの正規化が必要なのでしょうか。過去問では、「データの正規化を行う目的」として,「データか重複したり,データ更新の際に矛盾が生じたりしないようにする(H21秋IP問63)」と述べられています。
以下のテーブルを見てください。

◆受注明細テーブル
受注番号明細番号商品コード商品名数量
158671TV2000620型テレビ20
158672TV2400524型テレビ10
158673TV2800728型テレビ5
158681TV2400524型テレビ8
(表はH22年AP午前問32より)

この受注明細テーブルは正規化されているでしょうか。
情報セキュリティマネジメント試験を目指す剣持成子_11 

正規化されていないと思います。
なぜなら、商品コードと商品名の状態がよくないと思います。
その通り。この表では、商品コードが決れば商品名が決まるのですが、不整合が起きる可能性があります。
たとえば、以下のように、同じ商品コード(TV24005)であっても、「24型テレビ」と「23型テレビ」という不整合が起きます。
受注番号明細番号商品コード商品名数量
158671TV2000620型テレビ20
158672TV2400524型テレビ10
158673TV2800728型テレビ5
158681TV2400523型テレビ8
このような不整合を無くすために正規化をします。
上記を正規化し、以下の2つのテーブルに分けます。

◆受注明細テーブル
受注番号明細番号商品コード数量
158671TV2000620
158672TV2400510
158673TV280075
158681TV240058

◆商品テーブル
商品コード商品名
TV2400524型テレビ
TV2000620型テレビ
TV2800728型テレビ

正規化には、第1正規形、第2正規形、第3正規系があります。
過去問(H25秋AP午前問29)では、関係を第一正規系⇒第2正規形⇒第3正規形に変換する手順が記載されています。この情報も参考に、3つの正規形について整理します。
①第1正規形
「一つの属性に複数の値が入っている場合,単一の値になるように分解する(H25秋AP午前問29)」より。
それ以外には、重複の排除などがあります。
よって、第一正規形とは、(厳密にはもう少したくさんありますが、とりあえず)「繰り返し項目が無いもの」程度に考えておきましょう。

②第2正規形
「候補キーの一部の属性から,候補キー以外の属性への関数従属性がある場合,その関係を分解する(H25秋AP午前問29)」とあります。
候補キーの一部の属性からの関数従属を、「部分関数従属」と言います。
よって、第2正規形とは、「(非キーの)すべての属性が、候補キーに関数従属している」、または、「部分関数従属性が排除された」表です。

※参考ですが、主キーが1つだけの場合、部分関数従属はありません。よって、その場合は第一正規形と第二正規形は同一になります。

③第3正規形
「候補キー以外の属性間に関数従属性がある場合,その関係を分解する(H25秋AP午前問29)」
候補キー以外の属性間に関数従属性がある状態を、推移的関数従属性と言います。
よって、第3正規形とは、「推移的関数従属性が排除された」表です。
■補足解説:推移的関数従属性
属性A、B、Cにおいて、A→B→Cが成り立つが、B→Aが成立しないこと。
たとえば、以下において
http://sm.seeeko.com/archives/15876865.html
注文番号→担当者番号→担当者 が成立します。
ですが、
担当者番号→注文番号 は成立しません。

SQL(Structured Query Language)とは、データベースを操作する言語です。SQL文を使うことで、表からデータを選択したり、追加、削除、変更などが行えます。

■データベースの操作
基本情報のシラバスには「(1)データベースの操作関係データベースのデータの操作として,データの挿入,更新,集合演算(和,差,積,直積),関係演算(選択,射影,結合,商)などの代表的なデータの操作を理解する。」とあります。
ここで、選択、射影、結合について説明します。
①選択
SELECTを使って、行を取り出します。
SELECT * FROM 表 WHERE 行を抽出する条件
②射影
SELECTを使って列を取り出します。
SELECT フィールドA、フィールドB FROM 表
※フィールドA,Bが、取り出す列
③結合
SELECTを使って、複数の表を結合します。
SELECT * FROM 表1、表2 WHERE 表1.フィールドA=表2.
フィールドB
※表1と表2で一致する属性(フィールド)をもとに、
表を結合します。

※実際にやってみたい場合は、以下をhttp://sqlfiddle.com/などで試しましょう。
http://sm.seeeko.com/archives/15876965.html

過去問(H28秋AP午前問28)を見てみましょう。
問28 関係データベースにおける実表と導出表に関する記述のうち,適切なものはどれか。

ア 実表に対する射影,結合などによって導出表が得られる。
イ 導出表は,データを参照する場合だけに用いる。
ウ 導出表は,複数の実表から得られる表である。
エ 導出表は,元の実表の列だけから成る。



正解は、アです。

■データベース言語
SQLの言語はいくつかに分かれます。応用情報試験のシラバスには「データベース言語は,DDL(Data Definition Language:データ定義言語)とDML(Data Manipulation Language:データ操作言語)などに大別される」とあります。もう一つ、DCL(Data Control Language:データ制御言語)があります。

①データ操作言語
 データ操作言語の代表はSELECT(選択)です。それ以外に、INSERT(行の挿入),UPDATE(値の更新) ,DELETE(行の削除)などがあります。

②データ定義言語
データ定義言語には、CREATE文があります。
たとえば、表を作る場合は CREATE TABLE
VIEWを作る場合は CREATE VIEW

③データ制御言語
アクセス権を付与するGRANTや、削除するREVOKEがあります。

■文法
SQLの文法も理解しておきましょう。
基本となるSQL文はSELECT文です。なので、SELECT文でSQLの基本を理解しましょう。
SELECT 表の列名 FROM 表 WHERE 条件

■句
・WHERE 条件を指定する
・GROUP BY グループ化する
・ORDER BY 並び替える
・HAVING →GROUP BYでグループ化している場合の条件を指定します。たとえば、グループ化して平均値(AVG)を求め、そのAVGの値が70以上など(HAVING AVG(得点)>= 70)


過去問(H25春FE午前問28)をみてみましょう
問28“中間テスト表からクラスごと,教科ごとの平均点を求め,クラス名,教科名の昇順に表示するSQL文中のaに入れるべき字句はどれか。
中間テスト(クラス名,教科名,学生番号,名前,点数)
〔SQL文〕
 SELECT クラス名,教科名, AVG(点数) AS 平均点 FROM 中間テスト [ a ]
 
ア GROUP BYクラス名,教科名 ORDER BY クラス名, AVG(点数)
イ GROUP BY クラス名,教科名 ORDER BY クラス名,教科名
ウ GROUP BY クラス名,教科名,学生番号 ORDER BY クラス名,教科名,平均点
工 GROUP BYクラス名,平均点 ORDER BY クラス名,教科名




実際に実行してみた様子は以下です。
http://sm.seeeko.com/archives/15876965.html

正解はイ

さて、データベースの正規化を実際にやってみましょう。たとえば、レストランでカレーを食べたとき、以下のレシートがあったとします。これをデータベース化しましょう。
database


■非正規形
これを表にすると、以下の1行目のようになります。
注文番号担当者番号担当者名商品番号商品名数量単価
102117016鈴木010カレー1800
202サラダ1300
301コーヒー1200
102118008田中012パスタ2900
301コーヒー2200
※合計金額は自動計算できますので、データベースに記録する必要はありません。

上記の表の場合、1つの注文番号に、商品が複数並んでいますので、データベースとしては適正な状態ではありません。そこで、正規化を行います。

■第一正規形
第一正規形では、上記のように、1行に複数の属性があるような状態を解消します。
注文番号担当者番号担当者名商品番号商品名数量単価
102117016鈴木010カレー1800
102117016鈴木202サラダ1300
102117016鈴木301コーヒー1200
102118008田中012パスタ2900
102118008田中301コーヒー2200

これで、以下の注文テーブルが出来上がりました。
注文(注文番号,担当者番号、担当者名、商品番号,商品名,数量,単価)

■第二正規形
第二正規形では、候補キー(およびその一部)からの関数従属性を分割します。
さて、このテーブルの候補キーは何でしょうか。候補キーは、注文番号と、商品番号です。つまり、この2つが決まると、すべての属性が決まります。
候補キーおよびその一部は「注文番号」「商品番号」「注文番号、商品番号」の3つです。この3つからの関数従属性は以下です。
・注文番号→担当者番号、担当者名
・商品番号→商品名、単価
・注文番号、商品番号→数量

この関係をテーブルにします。
【注文】
注文番号担当者番号担当者名
102117016鈴木
102118008田中

【商品】
商品番号商品名単価
010カレー800
012パスタ900
202サラダ300
301コーヒー200

【注文明細】
注文番号商品番号数量
1021170101
1021172021
1021173011
1021180122
1021183012

これで、以下の3つのテーブルが出来上がりました。
注文(注文番号、担当者番号、担当者名)
商品(商品番号、商品名、単価)
注文明細(注文番号、商品番号、数量)


■第三正規形
第三正規形では、候補キー以外の属性にて関数従属性がある場合,その関係を分解します。
今回の場合、注文テーブルにおいて、担当者番号→担当者名という関数従属性があります。
これ分解すると、以下の2つのテーブルになります。
注文(注文番号、担当者番号)
担当者(担当者番号、担当者名)

最終的には以下の4つのテーブルが出来上がります。
注文(注文番号、担当者番号)
担当者(担当者番号、担当者名)
商品(商品番号、商品名、単価)
注文明細(注文番号、商品番号、数量)

【注文】
注文番号担当者番号
102117016
102118008

【担当者】
担当者番号担当者名
016鈴木
008田中

【商品】
商品番号商品名単価
010カレー800
012パスタ900
202サラダ300
301コーヒー200

【注文明細】
注文番号商品番号数量
1021170101
1021172021
1021173011
1021180122
1021183012


H28秋AP午前
問29 “サッカーチーム”表と“審判”表から,条件を満たす対戦を導出するSQL文のaに入れる字句はどれか。

 〔条件〕
  ・出場チーム1のチーム名は出場チーム2のチーム名よりもアルファベット順で先にくる。
  ・審判は,所属チームの対戦を担当することはできない。


サッカーチーム
チーム名
X
Y
Z
審判
氏名
所属チーム名
佐藤健太
X
鈴木翔太
Y
高橋拓也
Z


対戦
出場チーム1
出場チーム2
審判氏名
X
Y
高橋拓也
X
Z
鈴木翔太
Y
Z
佐藤健太


〔SQL文〕
 SELECT A.チーム名 AS 出場チーム1,B.チーム名 AS 出場チーム2,C.氏名 AS 審判氏名
    FROM サッカーチーム AS A,サッカーチーム AS B,審判 AS C
    WHERE A.チーム名 < B.チーム名 AND [  a  ]


ア (A.チーム名 <> C.所属チーム名 OR B.チーム名 <> C.所属チーム名)
イ C.所属チーム名 NOT IN (A.チーム名,B.チーム名)
ウ EXISTS
    (SELECT * FROM 審判 AS D WHERE A.チーム名 <> D.所属チーム名 AND B.チーム名 <> D.所属チーム名)
エ NOT EXISTS
    (SELECT * FROM 審判 AS D WHERE A.チーム名=D.所属チーム名 OR B.チーム名 = D.所属チーム名)





【正解】エ

H27秋AP午前
問29    "倉庫別商品在庫集計”表から在庫数の合計を求めたい。倉庫番号'C003'の倉庫で在庫数が100 以上の商品に対して,全ての倉庫における在庫数の合計を求めるSQL文のaに入る適切な字句はどれか。ここで,該当する商品は複数存在するとともに在庫数が100未満の商品も存在するものとする。また,実線の下線は主キーを表す。

倉庫別商品在庫集計(倉庫番号,商品コード,在庫数)

〔SQL文〕
SELECT 商品コード, SUM(在庫数) AS 在庫合計 FROM 倉庫別商品在庫集計
      WHERE [  a  ]
      GROUP BY 商品コード

ア 商品コード = (SELECT 商品コード FROM 倉庫別商品在庫集計
               WHERE倉庫番号 = 'C003' AND 在庫数 >= 100)
イ 商品コード = ALL(SELECT 商品コード FROM 倉庫別商品在庫集計
               WHERE倉庫番号 = 'C003' AND 在庫数 >= 100)
ウ 商品コード IN(SELECT 商品コード FROM 倉庫別商品在庫集計
               WHERE 倉庫番号 = 'C003' AND 在庫数 >= 100)
エ EXISTS (SELECT * FROM 倉庫別商品在庫集計
               WHERE 倉庫番号 = 'C003' AND 在庫数 >= 100)





【正解】ウ

H27春AP午前
間26 "電話番号"列にNULLを含む"取引先"表に対して,SQL文を実行した結果の行数は幾つか。
20170906_3-1
〔SQL文〕
SELECT * FROM 取引先 WHERE 電話番号 NOT LIKE '010%'

ア 1   イ 2     ウ 3   エ 4





NOT LIKE '010%'はあいまい検索で「010」で始まるもの以外ということを表します。
NULL(ヌルと呼ぶ人が多い)は「値が無い」という意味です。LIKEを使った場合、NULLを抽出することはできません。よって、2行目、4行目、5行目の3つが正解です。
【正解】ウです。

(H25春AP午前問29と同じ)
問27 埋込みSQLにおいて,問合せによって得られた導出表を1行ずつ親プログラムに引き渡す操作がある。この操作と関係の深い字句はどれか。

ア CURSOR
イ ORDER BY   
ウ UNION
エ UNIQUE





【正解】ア

H26秋AP午前
問25 表に対するSQLのGRANT文の説明として,適切なものはどれか。

ア パスワードを設定してデータベースへの接続を制限する。
イ ビューを作成して,データベースへのアクセス処理を隠蔽し,表を直接アクセスできないようにする。
ウ 表のデータを暗号化して,第三者がアクセスしてもデータの内容が分からないようにする。
エ 表の利用者に対し,表への問合せ,更新,追加,削除などの操作を許可する。





【正解】エ

H26春AP午前
問28 "東京在庫" 表と "大阪在庫" 表に対して,SQL文を実行して得られる結果はどれか。ここで,実線の下線は主キーを表す。
20170906_3-2





【正解】エ

H25秋AP午前
(H21春AP午前問31と同じ)
問27 クライアントサーバシステムにおけるストアドプロシージャに関する記述のうち,誤っているものはどれか。
ア 機密性の高いデータに対する処理を特定のプロシージャ呼出しに限定することによって,セキュリティを向上させることができる。
イ システム全体に共通な処理をプロシージャとして格納しておくことによって,処理の標準化を行うことができる。
ウ データベースへのアクセスを細かい単位でプロシージャ化することによって,処理性能(スループット)を向上させることができる。
エ 複数のSQL文から成る手続を1回の呼出しで実行できるので,クライアントとサーバの間の通信回数を減らすことができる。





【正解】ウ

問31 地域別に分かれている同じ構造の三つの商品表,"東京商品","名古屋商品","大阪商品"がある。次のSQL文と同等の結果が得られる関係代数式はどれか。ここで,三つの商品表の主キーは"商品番号"である。

SELECT * FROM 大阪商品
   WHERE 商品番号 NOT IN (SELECT 商品番号 FROM 東京商品)
UNION
SELECT * FROM 名古屋商品
   WHERE 商品番号 NOT IN (SELECT 商品番号 FROM 東京商品)

ア (大阪商品 ∩ 名古屋商品)-東京商品
イ (大阪商品 ∪ 名古屋商品)-東京商品
ウ 東京商品-(大阪商品 ∩ 名古屋商品)
エ 東京商品-(大阪商品 ∪ 名古屋商品)





【正解】イ

H25春AP午前
問27 クライアントサーバシステムにおいて,クライアント側からストアドプロシージャを利用したときの利点として,適切なものはどれか。

ア クライアントとサーバの間の通信量を削減できる。
イ サーバ内でのデータベースファイルへのアクセス量を削減できる。
ウ サーバのメモリ使用量を削減できる。
エ データの格納領域を削減できる。





【正解】ア

問30 A表は外部キーによってB表から参照されている。ここで,A表の行を削除するとき,それを参照しているB表の行を同時に全て削除することを指定するSQLの参照動作はどれか。

ア CASCADE
イ CONSTRAINT
ウ NO ACTION
エ REFERENCES





【正解】ア

H24春AP午前
問26 販売価格が決められていない“商品"表に,次のSQL文を実行して販売価格を設定する。このとき,販売ランクがbの商品の販売価格の平均値は幾らか。

 UPDATE 商品 SET 販売価格 =
        CASE
            WHEN 販売ランク = 'a' THEN 単価 * 0.9
            WHEN 販売ランク = 'b' THEN 単価 - 500
            WHEN 販売ランク = 'c' THEN 単価 * 0.7
            ELSE  単価
 END

H24春AP午前問26

 ア 1,675     イ 2,100     ウ 2,250     エ 2,500






【正解】エ

H23秋AP
問30 更新可能なビューを作成するSQL文はどれか。ここで, SQL文中に現れる表は全て更新可能とする。
ア CREATE VIEW 高額商品(商品番号,商品名,商品単価)
   AS SELECT 商品番号,商品名,商品単価 FROM 商品 WHERE 商品単価 > 1000
イ CREATE VIEW 受注商品(商品番号)
   AS SELECT DISTINCT 商品番号 FROM 受注
ウ CREATE VIEW 商品受注(商品番号,受注数量)
   AS SELECT 商品番号,SUM(受注数量) FROM 受注 GROUP BY 商品番号
エ CREATE VIEW 商品平均受注数量(平均受注数量)
   AS SELECT AVG(受注数量) FROM 受注





【正解】ア

H23特別AP午前
問30   "社員"表ど人事異動"表から社員ごとの勤務成績の平均を求める適切なSQL文はどれか。ここで,求める項目は,社員コード,社員名,勤務成績(平均)の3項目とする。
H23特別AP午前問30表

ア SELECT 社員.社員コード,社員名,AVG(勤務成績) AS "勤務成績(平均)"
     FROM 社員,人事異動
     WHERE 社員.社員コード = 人事異動.社員コード
     GROUP BY 勤務成績
イ SELECT 社員.社員コード,社員名,AVG(勤務成績) AS  "勤務成績(平均)" 
     FROM 社員,人事異動
     WHERE 社員.社員コード = 人事異動.社員コード
     GROUP BY 社員.社員コード,社員.社員名
ウ SELECT 社員.社員コード,社員名,AVG(勤務成績)/COUNT(勤務成績) 
                            AS  "勤務成績(平均)"
     FROM 社員,人事異動
     WHERE 社員.社員コード = 人事異動.社員コード
     GROUP BY 社員.社員コード,社員.社員名
エ SELECT 社員.社員コード,社員名,MAX(勤務成績)/COUNT(*)
                            AS "勤務成績(平均)"
     FROM 社員,人事異動
     WHERE 社員.社員コード = 人事異動.社員コード
     GROUP BY 社員.社員コード,社員.社員名





【正解】イ




■H22春AP午前
問33 表Aから実行結果Bを得るためのSQL文はどれか。
H22春AP問33
ア SELECT 部署コード,社員コード,名前 FROM A
       GROUP BY 社員コード
イ SELECT 部署コード,社員コード,名前 FROM A
       GROUP BY 部署コード
ウ SELECT 部署コード,社員コード,名前 FROM A
       ORDER BY 社員コード,部署コード
エ SELECT 部署コード,社員コード,名前 FROM A
       ORDER BY 部署コード,社員コード





【正解】エ

↑このページのトップヘ