データベースの正規化 normalization データベースゼミ 第三回 担当 DC1 辻順平 復習 • • • • • • テーブル クエリ 主キー 連結キー 外部キー 参照 正規化とは • データを効率的に管理するためのテーブル設計 – 「1事実1カ所（1 fact in 1 place）」 – テーブルの整合性 – 冗長性を排除 • 正則化の基本的な方法 – 第一正規化 – 第二正規化 – 第三正規化 第一正規化 • 問題：下記のテーブル（テーブル名：売上表） を作成するクエリを考えよ 売上番号 日付 顧客番号 顧客名 商品番号 商品名 単価 数量 G1001 2004/4/1 C01 新井商店 F101 オレンジ 100 50 F102 りんご 100 60 F101 オレンジ 100 100 F103 マンゴー 250 50 F102 りんご 100 20 F104 メロン 600 40 G1002 G1003 2004/4/2 2004/4/3 C02 C03 小野商会 関谷商事 第一正規化 • 第一正規形：配列がなくなりスカラ値のみと なったテーブル 第一正規形でない例： 売上番号 日付 顧客番号 顧客名 商品番号 商品名 単価 数量 G1001 2004/4/1 C01 新井商店 F101 オレンジ 100 50 F102 りんご 100 60 F101 オレンジ 100 100 F103 マンゴー 250 50 F102 りんご 100 20 F104 メロン 600 40 G1002 G1003 2004/4/2 2004/4/3 C02 C03 小野商会 関谷商事 売上番号 日付 顧客番号 顧客名 商品番号 商品名 単価 数量 G1001 2004/4/1 C01 新井商店 F101 オレンジ 100 50 F102 りんご 100 60 F101 オレンジ 100 100 F103 マンゴー 250 50 F102 りんご 100 20 F104 メロン 600 40 G1002 2004/4/2 G1003 2004/4/3 C02 C03 小野商会 関谷商事 第一正規化 連結キー 売上番号 日付 顧客番号 顧客名 売上番号 商品番号 商品名 単価 数量 G1001 2004/4/1 C01 新井商店 G1001 F101 オレンジ 100 50 G1002 2004/4/2 C02 小野商会 G1001 F102 りんご 100 60 G1003 2004/4/3 C03 関谷商事 G1002 F101 オレンジ 100 100 G1002 F103 マンゴー 250 50 G1003 F102 りんご 100 20 G1003 F104 メロン 600 40 参照 外部キー 第一正規形(first normal form; 1NF) • スカラ値のみのテーブル ⇒リレーショナルデータベースとして記述できる 連結キー 売上番号 日付 顧客番号 顧客名 売上番号 商品番号 商品名 単価 数量 G1001 2004/4/1 C01 新井商店 G1001 F101 オレンジ 100 50 G1002 2004/4/2 C02 小野商会 G1001 F102 りんご 100 60 G1003 2004/4/3 C03 関谷商事 G1002 F101 オレンジ 100 100 G1002 F103 マンゴー 250 50 G1003 F102 りんご 100 20 G1003 F104 メロン 600 40 参照 外部キー 第一正規形 • 問題：第一正規形まで正規化されたテーブル を使うときに生じうる問題点を３つ挙げよ 第一正規形の問題点 • 挿入時異常(insert) – 連結キーの一部が存在しないレコードは作成で きない • 削除時異常(delete) – 削除によってマスターデータが消えてしまう場合 がある • 更新時異常(update) – 要素名を変えた場合にすべて変えなければ不整 合が生じる 第二正規化 • 第二正規形：部分関数従属がなくなった状態 （完全関数従属） 第二正規形でない例： Cf. 関数従属 連結キー 売上番号 商品番号 商品名 単価 数量 G1001 F101 オレンジ 100 50 G1001 F102 りんご 100 60 G1002 F101 オレンジ 100 100 G1002 F103 マンゴー 250 50 G1003 F102 りんご 100 20 G1003 F104 メロン 600 40 キーの一部に関数従属している：部分関数従属 Ａが決まればＢが一意に決まるとき 「ＢはＡに関数従属している」 連結キー 売上番号 商品番号 商品名 単価 数量 G1001 F101 オレンジ 100 50 G1001 F102 りんご 100 60 G1002 F101 オレンジ 100 100 G1002 F103 マンゴー 250 50 G1003 F102 りんご 100 20 G1003 F104 メロン 600 40 キーの一部に関数従属している 第二正規化 外部キー 売上番号 商品番号 数量 商品番号 商品名 単価 G1001 F101 50 F101 オレンジ 100 G1001 F102 60 F102 りんご 100 G1002 F101 100 F103 マンゴー 250 G1002 F103 50 F104 メロン 600 G1003 F102 20 G1003 F104 40 参照 売上番号 日付 顧客番号 顧客名 G1001 2004/4/1 C01 新井商店 G1002 2004/4/2 C02 小野商会 G1003 2004/4/3 C03 関谷商事 第二正規化 売上番号 日付 顧客番号 顧客名 G1001 2004/4/1 C01 新井商店 G1002 2004/4/2 C02 小野商会 G1003 2004/4/3 C03 関谷商事 第二正規形(second normal form; 2NF) • 部分関数従属を解消した状態 売上番号 商品番号 数量 商品番号 商品名 単価 G1001 F101 50 F101 オレンジ 100 G1001 F102 60 F102 りんご 100 G1002 F101 100 F103 マンゴー 250 G1002 F103 50 F104 メロン 600 G1003 F102 20 G1003 F104 40 外部キー 参照 第二正規形 • 問題：第二正規化で下記のどの問題が解決 したか考えよう – 挿入時異常(insert) • 連結キーの一部が存在しないレコードは作成できない – 削除時異常(delete) • 削除によってマスターデータが消えてしまう場合があ る – 更新時異常(update) • 要素名を変えた場合にすべて変えなければ不整合が 生じる 第二正規形(2NF)の問題 • 2NFでは更新時異常が残る 売上番号 日付 顧客番号 顧客名 売上番号 日付 顧客番号 顧客名 G1001 2004/4/1 C01 新井商店 G1001 2004/4/1 C01 新井商店 G1002 2004/4/2 C02 小野商会 G1002 2004/4/2 C02 小野商会 G1003 2004/4/3 C03 関谷商事 G1003 2004/4/3 C04 関谷商事 G1004 2004/4/5 C03 関谷商事 G1004 2004/4/5 C03 関谷商事 update 主キー以外にも 関数従属の関係 があることが問題 第三正規化 • 第三正規形：推移的関数従属を解消した状 態 Cf. 推移的関数従属 第三正規形でない例： 非キー項目 主キー 売上番号 日付 顧客番号 顧客名 G1001 2004/4/1 C01 新井商店 G1002 2004/4/2 C02 小野商会 G1003 2004/4/3 C03 関谷商事 （主キーだけでなく）非キー項目に対しても 一意に定められる非キー項目が存在する：推移的関数従属 非キー項目 主キー 売上番号 日付 顧客番号 顧客名 G1001 2004/4/1 C01 新井商店 G1002 2004/4/2 C02 小野商会 G1003 2004/4/3 C03 関谷商事 推移的関数従属 第三正規形 主キー 外部キー 主キー 売上番号 日付 顧客番号 顧客番号 顧客名 G1001 2004/4/1 C01 C01 新井商店 G1002 2004/4/2 C02 C02 小野商会 G1003 2004/4/3 C03 C03 関谷商事 参照 第三正規形(third normal form; 3NF) • 推移的関数従属を解消した状態 主キー 外部キー 主キー 売上番号 日付 顧客番号 顧客番号 顧客名 G1001 2004/4/1 C01 C01 新井商店 G1002 2004/4/2 C02 C02 小野商会 G1003 2004/4/3 C03 C03 関谷商事 参照 各正規形の関係 すべてのテーブル 第一正規形 第二正規形 第三正規形 正規化の流れ 売 上 番 号 日 付 G 1 0 0 1 2 0 0 4 / 4 / 1 G 1 0 0 2 G 1 0 0 3 顧 客 番 号 顧 客 名 C 0 1 新 井 商 店 2 0 0 4 / 4 / 2 C 0 2 2 0 0 4 / 4 / 3 C 0 3 小 野 商 会 関 谷 商 事 商 品 番 号 商 品 名 単 価 数 量 F 1 0 1 オ レ ン ジ 1 0 0 5 0 F 1 0 2 り ん ご 1 0 0 6 0 F 1 1 オ スカラ値でない 1 0 0 レ 0 1 ン ジ 0 0 F 1 0 3 マ ン ゴ ー 2 5 0 5 0 F 1 0 2 り ん ご 1 0 0 2 0 F 1 0 4 メ ロ ン 6 0 0 4 0 売上番 号 商品番 号 商品名 単価 数 量 G1001 F101 オレン ジ 100 50 G1001 F102 りんご 100 60 G1002 F101 オレン ジ 100 10 0 G1002 F103 マン ゴー 250 50 G1003 F102 りんご 100 20 G1003 F104 メロン 600 40 第一 正規化 第二 正規化 部分関数従属 商品番 号 商品名 単価 商品番 号 商品名 単価 F101 オレンジ 100 F101 オレンジ 100 F102 りんご 100 F102 りんご 100 F103 マン ゴー 250 F103 マン ゴー 250 F104 メロン 600 F104 メロン 600 売上番 号 商品番 号 数量 売上 番号 商品番号 数量 G1001 F101 50 G1001 F101 50 G1001 F102 60 G1001 F102 60 G1002 F101 100 G1002 F101 100 G1002 F103 50 G1002 F103 50 G1003 F102 20 G1003 F102 20 G1003 F104 40 G1003 F104 40 売上 番号 日付 顧客 番号 顧客 名 売上 番号 日付 顧客 番号 顧客 名 G1001 2004/ 4/1 C01 新井 商店 G1001 2004/ 4/1 C01 新井 商店 G1002 2004/ 4/2 C02 小野 商会 G1002 2004/ 4/2 C02 小野 商会 G1003 2004/ 4/3 C03 関谷 商事 G1003 2004/ 4/3 C03 関谷 商事 推移的関数従属 非正規形 第三 正規化 第一正規形 第二正規形 売上番号 日付 顧客番号 G1001 2004/4/1 C01 G1002 2004/4/2 C02 G1003 2004/4/3 C03 顧客番号 顧客名 C01 新井商店 C02 小野商会 C03 関谷商事 第三正規形 正規化の結果 • 第一正規化 – 繰り返し項目を排除 • 第二正規化 – データの重複を排除 • 第三正規化 – 主キー以外の従属関係を排除 1 Fact in 1 Place （１つの場所に１つの事実を） データの一貫性 • 第一正規化 – 繰り返し項目を排除 ⇒ １つのレコードに１つのデータ • 第二正規化 – データの重複を排除 ⇒ データは１つのテーブルに • 第三正規化 – 主キー以外の従属関係を排除 ⇒ １つの項目に従属関係は１つ 正規化のメリット／デメリット • メリット – データの一貫性 • デメリット – テーブルの結合回数が増える（処理速度の低下） そのほかの正規化 • 第四正規化、第五正規化 • ボイス・コッド正規化（Boyce/Codd normal form; BCNF） 練習問題 • 問１：下記テーブル（db-4th-normalizationtask1.xls）を第三正規形まで正規化せよ 注文番号 注文年月日 商品ID 100072360 3/12/2006 100072361 4/4/2006 100072370 4/15/2006 64288 65588 67160 24771 65588 64288 65588 33456 商品名 姫トランス NOW JAZZ BEST ミレニアム フィール4 LOVE for NANA Only NOW JAZZ BEST ミレニアム 姫トランス フィール4 満月をさがして サウンドトラック フォーマッ ト CD CD CD CD CD CD CD CD 単価 数量 金額 1,980 2,800 2,400 2,600 2,800 1,980 2,400 2,500 1 1 1 1 2 1 1 1 1,980 2,800 2,400 2,600 5,600 1,980 2,400 2,500 合計金額 消費税 顧客ID 名前 住所 電話番号 支払方法 9,780 465 1004569 斉藤孝雄 東京都台東区緑品町3-15 03-2345-0000 クレジット 5,600 285 1003478 上島博 神奈川県山戸市下鶴関6-2 046-222-0000 クレジット 6,880 327 1001276 藤原良樹 東京都杉並区水野谷2-1 03-3300-0000 代金引換 • 問２：MySQL上に正規化した上記テーブルを作 成し、データを挿入せよ • 問３：上記すべてのデータをselectによって取得 するクエリを作成せよ 引用： http://www.atmarkit.co.jp/fdb/rensai/db_enginer03/db_enginer03_1.html
© Copyright 2024 ExpyDoc
