カテゴリ:5.データベース > 5.1 データベースと正規化

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
注文番号→担当者番号→担当者 が成立します。
ですが、
担当者番号→注文番号 は成立しません。

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


H29春AP午前
問27 “受注明細”表は,どのレベルまでの正規形の条件を満足しているか。ここで,実線の下線は主キーを表す。

問27





【正解】イ

H28春AP午前
問27 関数従属を次のように表記するとき,属性a~eで構成される関係を第3正規形にしたものはどれか。
20170906_1
20170906_2





【正解】エ

H27秋AP午前
問28 関係R (A,B,C,D,E,F)において,次の関数従属が成立するとき,候補キーとなるのはどれか。

〔関数従属〕
A→B,A→F,B→C,C→D,{B,C}→E,{C,F}→A

ア B
イ {B,C}
ウ {B,F}
エ {B,D,E}





【正解】ウ

H26秋AP午前
問26 関係R(A,B,C,D,E,F)において,関数従属A→B,C→D,C→E,{A,C}→Fが成立するとき,関係Rの候補キーはどれか。

ア A
イ C
ウ {A,C}
エ {A,C,E}





【正解】ウ

問27 その月に受注した商品を,顧客ごとにまとめて月末に出荷する場合,受注クラスと出荷クラスとの間の関連のa,bに入る多重度の組合せはどれか。ここで,出荷のデータは実績に基づいて登録される。また,モデルの表記にはUMLを用いる。
20170906_3






【正解】ウ

H26春AP午前
問26 UMLを用いて表した部門と社員の関係を表すデータモデルの説明のうち,適切なものはどれか。
20170906_4
ア 社員が1人も所属していない部門は登録できない。
イ 社員は複数の部門に所属することができる。
ウ どの部門にも所属しない社員は登録できない。
エ 一つの部門に複数の社員は所属できない。





【正解】ウ

問27 次の表はどこまで正規化されたものか。
20170906_5
ア 第2正規形  イ 第3正規形  ウ 第4正規形  エ 非正規形





【正解】ア

H25秋AP午前
問29 関係を第2正規形から第3正規形に変換する手順はどれか。

ア 候補キー以外の属性から,候補キーの一部の属性に対して関数従属性がある場合,その関係を分解する。
イ 候補キー以外の属性間に関数従属性がある場合,その関係を分解する。
ウ 候補キーの一部の属性から,候補キー以外の属性への関数従属性がある場合,その関係を分解する。
エ 一つの属性に複数の値が入っている場合,単一の値になるように分解する。





【正解】イ

H25春AP午前
問28 “プログラマは全て社員であり,社員の約10%を占める。社員は社員番号と氏名をもち,職種がプログラマである場合は,使用できるプログラム言語を一つ以上もつ。”という状況を記録するデータベース設計案として,適切なものはどれか。ここで,実線の下線は主キーを,破線の下線は外部キーを表す。
20170906_6





【正解】エ

応用情報技術者試験シラバスでは、「データベースの論理設計」に関して、以下の記載があります。

③ データベースの論理設計
データの重複や矛盾が発生しないテーブル(表)設計の考え方,主キー,外部キーなどの概念,参照制約,一貫性制約などの制約を理解する。また,ユーザビューの機能と定義を理解する。

用語例 配置モード,親子集合順序,親子集合,索引,フィールド(項目),レコード,ファイル,NULL,一意性制約

過去問の例

H28春AP午前

問29 次の表において,“在庫” 表の製品番号に定義された参照制約によって拒否される可能性がある操作はどれか。ここで,実線の下線は主キーを,破線の下線は外部キーを表す。
20170906_2-1
ア “在庫” 表の行削除
イ “在庫” 表の表削除
ウ “在庫” 表への行追加
エ “製品” 表への行追加

正解は、ウです。

H27春AP午前

問28 “学生” 表が次のSQL文で定義されているとき,検査制約の違反となるSQL文はどれか。

CREATE TABLE 学生(学生番号CHAR(5)PRIMARY KEY,
          学生名CHAR(16),
          学部コードCHAR(4),
          住所CHAR(16),
          CHECK(学生番号LIKE 'K%'))
20170906_2-2
ア DELETE FROM 学生 WHERE 学生番号 = 'K1002'
イ INSERT INTO 学生 VALUES ('J2002','渡辺次郎','Mee6','東京都')
ウ SELECT * FROM 学生 WHERE 学生番号 = 'K1001'
エ UPDATE 学生 SET 学部コード = 'N001' WHERE 学生番号 LIKE 'K%'

正解は、イです。

H26春AP午前

問25 SQL文においてFOREIGN KEY と REFERENCES を用いて指定する制約はどれか。
ア キー制約
イ 検査制約
ウ 参照制約
エ 表明

正解は、ウです。

問26 データベースの3層スキーマ構造に関する記述のうち,適切なものはどれか。

ア 概念スキーマは,データの物理的関係を表現する。
イ 外部スキーマは,データの利用者からの見方を表現する。
ウ 内部スキーマは,データの論理的関係を表現する。
エ 物理スキーマは,データの物理的関係を表現する。
正解は、イです。

問28 “部品”表のメーカコード列に対し,B+木インデックスを作成した。これによって,“部品”表の検索の性能改善が最も期待できる操作はどれか。ここで,部品及びメーカのデータ件数は十分に多く,“部品”表に存在するメーカコード列の値の種類は十分な数があり,かつ,均一に分散しているものとする。また,“部品”表のごく少数の行には,メーカコード列にNULLが設定されている。実線の下線は主キーを,破線の下線は外部キーを表す。
問28
ア メーカコードの値が1001以外の部品を検索する。
イ メーカコードの値が1001でも4001でもない部品を検索する。
ウ メーカコードの値が4001以上, 4003以下の部品を検索する。
エ メーカコードの値がNULL以外の部品を検索する。
正解は、ウです。

↑このページのトップヘ