テーブルと集合演算・テーブルの正規化

(2006.10 更新)

■　準備：

レポートフォルダから、第3回の資料をマイドキュメントへコピーしておく

■　用語確認：（前回へ　レコード,　タプル,　テーブルなどの意味は？）

属性（アトリビュート）　：　テーブルの列名のこと。テーブルの該当する列に何のデータが格納されているかを示す。Ａｃｃｅｓｓではフィールド　とよぶ。

例）　商品番号、得意先番号、得意先名をフィールドに持ち、レコードが５件記録されたテーブル

定義域（ドメイン）　：　フィールドに格納するデータの正しい範囲のこと。何が正しい範囲かをデータベースの設計時に定義する。

定義の例）
　　月＝１〜１２
　　性別フィールド＝｛男性,　女性,　不明｝
　　年齢＝０〜２００
　　県名＝｛愛知,　・・・　,沖縄｝
　　商品番号＝4桁整数
　　名前＝全角文字１０文字まで

主キー　：　フィールドのうち、レコードを一意に決定するもの。
つまり、主キーについて値を指定して検索すると　データが見つかる場合は必ず１件だけ見つかる　ことが保証できる。
同じデータが重複して記録されることがないフィールドは主キーの候補。

候補キー　：　テーブルのうち、主キーに指定可能なフィールド

例） 以下の表では、学籍番号　と　受験番号が　候補キー。
※　重要：　この表では年齢を指定するとレコードが1件決まるように見えるが、　年齢　は主キーにはできない。（同じ年齢がいないとの保証が無いため）

外部キー　：　テーブルで他のテーブルの主キーをデータとするフィールド

例）

連結キー　：　複数のフィールドを指定することで　主キー　の役割を果たすフィールドの組。

正規化　　：　テーブルを効果的に分割すること。　リレーショナルデータベースで重要な概念。
※正規化した場合の効果（データベース復習　参照）
　要点
　　・無駄なデータの記録を省く（不必要に同じデータを繰り返し記録しない）
　　・データの変更が容易（不必要に複数箇所のデータを修正しない）
　　・データの削除が確実（不必要なデータだけを削除できる）

■　正規化について

※ここが本講義で最も重要なポイントです。　理解しないまま先に進んではいけません。　理解できない場合、　質問すること。　理解するのがいやなら、単位をあきらめること。

テーブルを正規化するための理論的な手順：
　以下の様にテーブルを１ＮＦ（Normal Form: 正規形）から５ＮＦまでの性質を満たすように順次分割してゆく。

• 第1正規形（1NF）・・・・・繰り返しのフィールドが無い

• 第2正規形（2NF）・・・・・候補キーに完全従属

• 第3正規形（3NF）・・・・推移的に従属しない

• ボイス・コッド正規形（BCNF）・・・全ての候補キーは決定項である

• 第4正規形（4NF）・・・一つの表には、1つの多値従属制のみ存在する

• 第5正規形（5NF）・・・２つの表には、情報無損失分解できない

ただし、本講義では実用上十分な正規形である　３ＮＦ　までを扱う。

正規化は、１ＮＦから３ＮＦまで順番に進めてゆく。

◎１ＮＦ

テーブルの１つのフィールドに１つのデータを記録する表にテーブルを修正する

例）

◎２ＮＦ

主キーを決める。ただし、主キーが連結キーで複数のフィールドを持つ場合、
　　連結キーの各フィールドを部分的に用いただけで決まるフィールド
がある場合は、その部分を別のテーブルに分割する

例）以下では、学籍番号と、科目が連結キーで　主キー　となる。　しかし、名前などは学籍番号だけで決まるため、別のテーブルに分割する

◎３ＮＦ

テーブルで、主キー以外のフィールドの値から間接的に求まるフィールドがあれば取り除くか、別の表にする

質問：　なぜ、上記のように表を分割し、正規化する必要があるのでしょうか？

　ヒント：　正規化する前の表と、　正規化した後の表について、
　　　　　　　レコードを追加・変更・削除した時の手間の差や問題点を比較してみよう。

■　クエリーによる集合演算

Ａｃｃｅｓｓには、複数のテーブルを組み合わせて新たにテーブルを作成する機能があります。
テーブルは、上記で述べたように　正規化されて分割　している　ため実際に使用する一覧表などは　テーブルの結合（集合演算）　などで　Ａｃｃｅｓｓがその都度、作成します。

表の集合演算：　簡単に言うと、　
　・表から「レコードを抽出（条件似合ったレコードだけを取り出す）」
　・表の一部のフィールドを取り出す
　・表と表を結合する（結合の条件を指定、または無条件に全組み合わせを求める）
などの操作のことです。

◎テーブルを集合演算などで操作するには　クエリー　機能を使います。

・マイドキュメントにコピーした、ファイル　第3回　をダブルクリックで開く。

・データベースのメニュー：　クエリ→新規作成→デザインビュー

・表Aと表Bを追加

※以下の文では、A、Bは、全て「半角文字」です

　※　表Aと表Bが線で結合されています。　この線をリレーションと呼び、各表の得意先番号で同じ番号のものは、同じレコードであることを示します。

▼「積」　　複数のテーブルで共通するレコードを求める

フィールドをクリックし、以下のフィールドを設定する

※フィールドを指定し間違えた場合は、フィールドの上部の細い棒の部分をクリックしてフィールド全体を選択し、「右クリックのメニューから削除」で消去できます。

クエリーのデザイン画面を閉じる。
名前は、「積」とする。

クエリー「積」を開き、結果を確認する

▼「直積」　　複数のテーブルのレコード全ての組み合わせを求める

クエリー「積」をコピーして貼り付ける。（マウス右クリック→コピー→貼り付け）

名前は、「直積」とする
「直積」を選択（クリックする。ダブルクリックで開かないこと）し、デザインボタンを押す

「表A」と「表B」を結ぶ線の上で、マウスを右クリックする。

「削除」を選ぶ。

クエリーのデザイン画面を閉じ、変更結果を保存する。

クエリー「直積」を開き、結果を確認する

▼「左結合」　　片方のテーブルの全レコードにもう片方のテーブルのレコードを付け加える

クエリー「直積」をコピーして貼り付ける。

名前は、「左結合」とする
「左結合」を選択（クリックする。ダブルクリックで開かないこと）し、デザインボタンを押す

「表A」の得意先番号を「表B」にドラッグ＆ドロップし、AとBを結合する。

「表A」と「表B」を結ぶ線の上で、マウスを右クリックする。

「結合プロパティ」→結合の種類　→　２：「表Aの全レコードと表Bの同じ結合フィールドだけを含める」　を選ぶ

クエリーのデザイン画面を閉じ、変更結果を保存する。

クエリー「左結合」を開き、結果を確認する

▼「選択」　　条件にあったレコードを抽出する

※重要です

クエリーを新規作成する（新規作成→デザインビュー）
表Ａを追加。

以下のクエリーを作成してみよ。名前は「選択」とする。実行して結果を確認すること。

抽出条件の部分は、半角文字で以下の式を指定してみる
▼　>500　　　　(500より大きい)
また、他にも（クエリを修正して）、
▽　Like "*6"　　　(最後の一文字が6)　　（時間が無い場合とばしてもよい）
なども試してみること

これは　Ｌｉｋｅ　”＊６”の例

▼「射影」　　　テーブルのフィールドを部分的に抜き出して表示

以下のクエリーを作成してみよ。

テーブルは、「A2」を利用する。得意先番号を表示しないようにするには、フィールドで得意先番号を指定しなければ良い。

名前は「射影」とする。実行して結果を確認すること。

※　商品番号　と　得意先名　だけをフィールドで指定する。　得意先番号は指定しない。

フィールドの内容をよく確認すること

▼「結合」

以下のクエリーを作成してみよ。

テーブルは、「A2」と「B2」を利用する。

※結合の種類に注意すること

名前は「結合」とする。実行して結果を確認すること。

※実行結果に、テーブルＡ２の全レコードが表示されていますか？

■　演習

以下の表を、データベース中に作成せよ。

・テーブル　「受注」　：
　フィールド　注文番号　　　　＊主キー
　フィールド　得意先番号

・テーブル　「得意先」　：
　フィールド　得意先番号　　＊主キー
　フィールド　得意先名
　フィールド　得意先住所

・テーブル　「受注内容」　：
　フィールド　注文番号　　＊主キー
　フィールド　商品番号　　＊主キー
　フィールド　個数

・テーブル　「商品」　：
　フィールド　商品番号　　＊主キー
　フィールド　商品名
　フィールド　単価

※　テーブルは　全部で　４個　作成します。

テーブルの作成 テーブル→新規作成→デザインビュー

フィールド名　と　データ型　を決める。 データ型は、 　　テキスト型：　文字で記録するデータに利用 　　数値型　　：　数値で記録するデータ（合計･平均などの計算が必要なデータ）に利用 　　オートナンバー型：　データベースが自動的に重複のない番号を割り当てる 　　Yes/No型：　真偽値型。　はい・いいえ、や　ある・なし　など　2値のデータに利用 フィールドに記録するデータの性質を考慮して各自でどれを使用するか、判断する。

主キーを設定すること。 主キーの設定されたフィールドには「鍵」のマークが表示される。
「フィールドを選択」→右クリック→主キー

設定が終わったら、テーブルのデザインビューは閉じてよい。
上記と同様にして、他のテーブルも作成する。

※　（注文番号、商品番号、個数）をフィールドに持つテーブルでは、注文番号と商品番号を組み合わせて主キーにする必要がある。
表（注文番号、商品番号、個数）　受注内容の表に主キーを設定するには
　両方のフィールドを選択し、右クリック→主キー　とすること

テーブルを作成し終わったら、
メニュー：
ツール→リレーションシップ

で、「右クリック」からテーブルを追加を選び、作成した４つのテーブルを追加する。
追加したテーブルに、適切にリレーションを設定する。

※リレーションの設定は、フィールド名をドラッグ＆ドロップでできます。

線（リレーション）の向きの設定には、線上で右クリックから、リレーションシップの編集を選ぶ。
「結合のプロパティ」で矢印の向き（左結合か右結合か）を設定する。

※矢印の意味
上記のテーブル4個を結合して　一覧表示　する場合を考える。
（受注の得意先番号）→（得意先の得意先番号）　は、受注1件に対して必ず得意先が１件対応することを示す。
受注の無い得意先も在り得るので、矢印の向きが←にはできない。

（受注の受注番号）→（受注内容の受注番号）　は、受注があれば必ず受注内容があることを示す。
（受注内容の商品番号）→（商品の商品番号）　は、受注内容には必ず商品があることを示す。
受注が1件もない商品も在り得るので、矢印の向きは←にできない。

■　確認

以下の２つで、今回の演習は終了です。

• テーブルに適当にデータを入力してみよ。
  ヒント：
  ４つのテーブルに、それぞれ数件ずつ、適当にレコードを入力する。
  ただし、　連結キーで用いる受注番号・商品番号などの　番号は、　同じレコードについては同じ番号を用いるようにすること。
  
• ４つの表全てを結合して表示するクエリー「受注明細表」を作成し、表示内容を確認する。
  ヒント：
  クエリを新規作成し、　４つのテーブル全てを追加する。
  表示するフィールドを、各テーブルから、ドラッグ＆ドロップして選択する。
  表示するフィールドは過不足が無い様に選び、分かりやすい順番にまとめてフィールドを配置すること。（例：　商品に関する項目は近くにまとめておくなど）

■　提出物について

最後に、作成したデータベースを提出せよ。

ファイル名「第3回#学籍番号」とする

□　応用：　時間が余った人は、以下にも挑戦してください。

・応用課題