５．正規化とテーブル作成

＜正規化の必要性＞

これまでの実習を通して、サンプルテーブルについて以下のことを考えてみる。

  • 単価と個数があれば、金額はいつでも出せる
  • １商品に値上げ等の変更があると、何件ものデータを直さないといけない

いろいろと無駄や問題点があるので、テーブルの再構成を検討する。　→　「正規化」
（これは本来、最初にやっておくべきこと。「DB設計」という作業）

＜不要な［金額］列をなくす＞

［金額］列は、いざとなったら計算で求められる。
そういうデータは、DBに永久保存しておく必要がない。

（最低限、販売テーブルにあればいい列）

  • 販売番号
  • 販売日
  • 商品名
  • 個数
  • 単価
  • 販売先
  • 配送
  • 備考

＜商品と単価の一覧を切り分ける＞

商品名が決まれば、それに応じて単価も決まる。
商品名と単価の一覧があれば済む。

（商品マスタ）　※「○○」リストに相当する固定的なデータを「マスタ」と呼ぶことがある。

  • 商品名
  • 単価

（新・販売テーブル）

  • 販売番号
  • 販売日
  • 個数
  • 販売先
  • 配送
  • 備考

商品の販売があった場合、商品マスタには手を加える必要がなく、販売テーブルにのみデータを追加していけばいい。
同じ商品が500件販売された場合など、無駄なデータ増加を避けられる。

＜複数テーブル間を結合する＞

ところで、このようにテーブルを分割すると、いつ誰が何を買ったか分からなくなって困る。
そこで、それぞれのテーブルに共通な「商品コード」を割り当てることで解決させる。

（改・商品マスタ）

  • 商品コード
  • 商品名
  • 単価

（改・新・販売テーブル）

  • 販売番号
  • 販売日
  • 商品コード
  • 個数
  • 販売先
  • 配送
  • 備考

いつ誰が何を買ったか知りたければ、販売テーブルにある［商品コード］をもとに商品マスタをチェックし、同じ商品コードを持つデータを見ればいい。

ただし、販売テーブルの商品コードと同じものが商品マスタにも存在することが必須となる。（マスタに登録されていない商品を買うことはできない！）
逆に、商品マスタに存在する商品コードが、必ず販売テーブルにもあるとは限らない。「登録されているけど、なかなか売れない」商品もあり得る。

同じ商品は何度も売れる。販売テーブルには、同じ商品コードを持つデータが繰り返し出現することがある。
逆に、商品マスタは「このお店で扱っている商品の一覧」という扱い。同じ商品コードが何度も出現することは、性質上あり得ない。
（この「１対多」の関係が、テーブルの関係パターンとしては最も多い。）

単価の修正が入った場合など、商品マスタの単価をひとつ修正すれば済む。

＜プライマリー キー＞

商品マスタにおける［商品コード］のように、「あるテーブルの中で、ただ1件のデータを特定できる性質のもの」を「プライマリーキー（主キー）」と位置づける。
販売テーブルにおける「販売番号］も、同様の役割を持つ。

結合のキーとなる列は、たいていどちらかがプライマリーキーであることが多い。
プライマリーキーとなる列には、重複する値は挿入できない。nullも許可されない。

プライマリーキーを指定しなくても、テーブルを作成することは（論理上）可能。
ただ、実際にシステムで使うテーブルでは、プライマリーキーが指定されていることがほとんど。

５－１

商品マスタの作成 − テーブルの作成

以下のテーブル定義に応じて、新しいテーブルを作成する。

テーブル名：product

列名 データ型 その他
product_code 文字型、3桁 プライマリーキー
product_name 文字型、最大40桁 　
product_price 数値型 　

できたら、「\d テーブル名」で確認してみる。

キーワード：create table

【サンプルソースコード】

5-1.sql

/* 5-1．商品マスタの作成 */ create table product( product_code char(3) primary key, product_name varchar(40), product_price integer );

５－２

販売テーブルの作成 − テーブルの作成2

以下のテーブル定義に応じて、新しいテーブルを作成する。

テーブル名：sale2

列名 データ型 その他
sale_id 数値型 プライマリーキー
sale_date 日付型 　
product_code 文字型、3桁 　
product_count 数値型 　
customer 文字型、最大40桁 　
is_delivery 論理型 初期値：false
note 文字型、最大128桁 　

できたら、「\d テーブル名」で確認してみる。

キーワード：create table

【サンプルソースコード】

5-2.sql

/* 5-2．販売テーブルの作成 */ create table sale2( sale_id integer primary key, sale_date date, product_code char(3), product_count integer, customer varchar(40), is_delivery boolean default false, note varchar(128) );

５－３

商品マスタのデータ入力

商品マスタproductに、以下のデータを挿入する。

product_code product_name product_price
N01 いちご 400
N02 いちじく 600
N03 りんご 140
N04 メロン 3500
N05 マンゴー 260
N06 バナナ 100
S01 七夕セット 1780
S02 常夏セット 2300
S03 秋の先取りセット 2500

できたら、「select * from product;」で確認してみる。

キーワード：insert into 〜 values

【サンプルソースコード】

5-3.sql

/* 5-3．商品マスタのデータ入力 */ insert into product values ('N01','いちご',400); insert into product values ('N02','いちじく',600); insert into product values ('N03','りんご',140); insert into product values ('N04','メロン',3500); insert into product values ('N05','マンゴー',260); insert into product values ('N06','バナナ',100); insert into product values ('S01','七夕セット',1780); insert into product values ('S02','常夏セット',2300); insert into product values ('S03','秋の先取りセット',2500);

補足

CUIでSQLを実行する場合の補足事項。

コマンドラインで何回もinsertするのは、けっこう面倒。
insert文は長くなりがちだし、データによっては日本語切り替えを何度もやらないといけないので、ミスも起きやすい。

SQL文をテキストファイルに記述しておけば、mysqlコマンド（\マーク付きのコマンド）からファイル名を指定するだけで、ファイル内に書かれているSQLをまとめて実行できる。

【使い方・メリット】
同じSQL文を何度も実行する（ファイルとして保存しておけば、再利用可能）
似たようなSQL文を実行する（テキストファイル上の記述作業のほうが、コピーや修正がやりやすい）
一連のSQL文をまとめて実行する（流れを確認しながら作成できる）

MySQLに限らず、たいていのDBには似たような機能がある。

元のファイル例：test.sql （ファイル名は何でもいい）

insert into t_test1 values (101, 'データその１', 400);
insert into t_test1 values (102, 'データその２', 800);
insert into t_test1 values (103, 'データその３', 1200);

/* ・・・以下、insertに限らずどんなSQLを並べてもいい・・・ */

実行コマンド例

m_ogawa=> source test.sql　・・・「source」のあとにファイル名を指定（場合によっては、絶対パスで指定）
m_ogawa=> source test.sql　・・・ 「source」のあとにファイル名を指定（場合によっては、絶対パスで指定）

INSERT 26056 1　　　　 ・・・ ファイルに書かれているSQLが実行される
INSERT 26057 1
INSERT 26058 1

m_ogawa=> select * from t_test1;

  testid  |   testdata    | testnum
----------+---------------+---------
      101 | データその１  |     400
      102 | データその２  |     800
      103 | データその３  |    1200
(3 rows)

m_ogawa=>
INSERT 26056 1　　　　 ・・・ ファイルに書かれているSQLが実行される
INSERT 26057 1
INSERT 26058 1

m_ogawa=> select * from t_test1;

  testid  |   testdata    | testnum
----------+---------------+---------
  101 | データその１  |     400
  102 | データその２  |     800
  103 | データその３  |    1200
(3 rows)

m_ogawa=>

５－４

販売テーブルのデータ入力

販売テーブルsale2に、以下のデータを挿入する。

sale_id sale_date product_code product_count customer is_delivery note
101 2007/4/1 N03 20 A商店 false (NULL)
102 2007/4/2 N01 15 B商店 true (NULL)
103 2007/5/4 N01 2 A商店 false (NULL)
104 2007/5/12 N02 32 A商店パリ支店 true 空輸
105 2007/5/20 N05 18 C商店 false (NULL)
106 2007/6/7 N03 3 A商店 false (NULL)
107 2007/7/1 N04 12 W商店 true のしをつける
108 2007/7/7 S01 3 C商店 false (NULL)
109 2007/7/10 N05 10 B商店 false (NULL)
110 2007/8/1 S02 6 海の家 false (NULL)

できたら、「select * from sale2;」で確認してみる。

キーワード：insert into 〜 values

【サンプルソースコード】

5-4.sql

/* 5-4．販売テーブルのデータ入力 */ insert into sale2 values (101,'2007/4/1','N03',20,'A商店',false,null); insert into sale2 values (102,'2007/4/2','N01',15,'B商店',true,null); insert into sale2 values (103,'2007/5/4','N01',2,'A商店',false,null); insert into sale2 values (104,'2007/5/12','N02',32,'A商店パリ支店',true,'空輸'); insert into sale2 values (105,'2007/5/20','N05',18,'C商店',false,null); insert into sale2 values (106,'2007/6/7','N03',3,'A商店',false,null); insert into sale2 values (107,'2007/7/1','N04',12,'W商店',true,'のしをつける'); insert into sale2 values (108,'2007/7/7','S01',3,'C商店',false,null); insert into sale2 values (109,'2007/7/10','N05',10,'B商店',false,null); insert into sale2 values (110,'2007/8/1','S02',6,'海の家',false,null);

５－５

テーブルの削除

販売テーブルsale2を削除する。

※これを実行すると、テーブルがほんとになくなる。 　削除したままだと以降の作業に支障が出るので、再作成しておくこと。

キーワード：drop table

【サンプルソースコード】

5-5.sql

/* 5-5．テーブルの削除　実行したら、再作成すること！ */ drop table sale2;

課題

以下のスポーツクラブ会員テーブルを適切に正規化する。

[ member_q5 ]

本テーブルは member_id と lesson_id が決まるとレコードが特定できる状態になっている。
member_id と lesson_id による複合主キーである。

member_id regist_date member_name birthday member_gender class_id class member_fee area lesson_id lesson
M101 2005/2/10 秋田きりん 1978/5/4 男性 C01 マスター 10000 上野 L01 スカッシュ
M101 2005/2/10 秋田きりん 1978/5/4 男性 C01 マスター 10000 上野 L02 ジャズダンス
N511 2006/8/15 長野となかい 1980/10/9 男性 C02 ナイト 8000 上野 L03 スイミング
M340 2006/8/15 山口ぱんだ 1976/4/28 女性 C01 マスター 10000 上野 L02 ジャズダンス
H028 2006/10/10 長崎らっこ 1979/7/16 男性 C03 ホリデー 7200 品川 L03 スイミング
M502 2007/1/20 石川うさぎ 1976/5/21 女性 C01 マスター 10000 千葉 L01 スカッシュ
N113 2007/1/31 岡山ふくろう 1965/12/30 男性 C02 ナイト 8000 上野 L01 スカッシュ
D327 2007/3/6 富山ひよこ 1986/4/23 女性 C04 デイ 6500 千葉 L03 スイミング
N280 2007/4/1 香川ひらめ 1972/6/24 男性 C02 ナイト 8000 品川 L02 ジャズダンス
H335 2007/4/18 宮崎まんぼう 1968/3/12 男性 C03 ホリデー 7200 品川 L04 ホットヨガ
M211 2007/4/28 福岡かまきり 1980/10/8 男性 C01 マスター 10000 千葉 L02 ジャズダンス

　

課題5-1．会員テーブルの正規化を行う。

提出物：
正規化後のテーブル数に応じて、全て作成する。

  1. 正規化後のテーブルを作成するためのCREATE文
  2. 正規化後のテーブルに既存データを追加するためのINSERT文

全テーブルのCREATE文が記載されたテキストファイル、全レコードのINSERT文が記載されたテキストファイルの計2ファイルを提出する。
既存会員テーブルのデータ型などは会員テーブルDDLを確認する。
テーブルの作成順、レコードの追加順に注意すること。
例えば、以下のような商品テーブルと購入履歴テーブルがあった場合、

[ 商品テーブル ]

商品ID(PK) 商品名
S01 カレンダー
S02 目薬

[ 購入履歴テーブル ]

購入履歴ID(PK) 名前 商品ID(FK)
1 石田 S01
2 坂上 S02

テーブルの作成・レコードの追加順は以下のようになる。

  1. 商品テーブルのCREATE
  2. 購入履歴テーブルのCREATE
  3. 商品テーブルのINSERT
  4. 購入履歴テーブルのINSERT

購入履歴テーブルは商品テーブルとリレーションシップが設定されているため、関連先のテーブルである商品テーブルが存在しないといけません。
そのため、まず関連先である商品テーブルを先に作成する必要がある。
SQLが正しくても、順番が間違っているとエラーが発生するため、作成順・追加順も意識すること。

正規化時の条件：

  1. 今後は、1人の会員が複数種類のレッスンを受けることもできるようにする。
    現在のテーブル構成でも以下の方法で実現はできるが、どちらもデータの重複や手間がかかるなど現実的ではない。
    1. 会員テーブルにレッスン分レコードを追加する。
      【問題点】レッスン以外の個人情報がどんどん重複していく
    2. lessonカラムを,区切りなどで連結していく
      【実施例】ジャズダンス　⇒　ジャズダンス,スカッシュ　⇒　ジャズダンス,スカッシュ,スイミング
      【問題点】データの更新をする際にSQLを実行する以外のプログラムを挟む必要があり、手間がかかる
  2. 主キー制約・外部キー制約は必ず設定すること。
    外部キー制約が存在しないテーブルは主キー制約だけでよい。
    テーブル間の関係性をしっかり考えて両方必要か、主キー制約だけでよいのか考えること。

正規化後のテーブル名のつけ方について：
テーブル名の先頭に「名字名前」をつけること。
例）石井 晃がmemberテーブルを作成する場合は、ishii_akira_member、となる。

Copyright © Xincor miXell Co., Ltd. All rights reserved

results matching ""

    No results matching ""