SQL入門
データベースの正規化とマスタの作成

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
最終更新日:2019-12-04

データベースの正規化とマスタの作成


エクセルVBAでデータベースを扱うためのSQL入門です。
前回までは1つのテーブルのみを扱ってきましたが、これからは複数のテーブルを扱っていきます。
複数テーブルの情報を必要に応じて結合して取得できるようになると、よりデータベースおよびSQLの利便性が感じられると思います。


エクセルでは、SUMIF,COUNTIF,VLOOKUP,INDEX,OFFSET等々、これらの関数で他シートの情報を取得できます。
SQLでは、これらの機能を強力かつ柔軟にしたことが行えるます。

前回まで使っていたテーブルは、全てのデータが1つのテーブルに入っていました。
同じcodeで同じnameのデータが多数存在していました。
同じデータが存在することはとても無駄なことです、ディスクの容量の無駄遣いでもあります。
このような無駄を排除してデータベースをあるべき形にすることをデータベースの正規化と言います。

データベース正規化とは

データの整合性を保ちつつ、データの冗長性(重複)を排除してデーベースを設計することです。
情報関係の資格を取るのであれば言葉の定義を正確に覚える必要がありますが、ここではごく簡単な説明にとどめます。
以下の説明は一般的な説明として問題ないはずですが、言葉使いや細部においては資格試験等の説明と相違する可能性がある点はご承知おきください。

第1正規形
繰り返し項目を持たない
導出項目を持たない

データベースのテーブルに入れることが出来れば概ね第1正規形であると言えます。
正規化されていないデータとは、エクセルで考えると分かりやすいと思います。
・セル結合されている
・横(列)に繰り返し現れるグループある
セル結合はいうまでもないでしょう。
繰り返し現れるグループとは、日別のデータが横に並んでいるようなデータです。
これらは、エクセルで関数を使ってデータ取得する時にも、かなり困った状態であることはお分かりいただけると思います

第1正規形とは、これらを排除した状態になります。
前回まで使用したテーブルは、金額が導出項目なので(であるとすれば)、完全には第1正規形とは言えないことになります。

第2正規形
第1正規形である
すべての非キー属性が候補キーに関して完全関数従属している

第1正規形からさらに、複数レコードに該当する値ごとに1つのテーブルを作成します。
レコードが、テーブルの主キー 以外に従属しないようにします。
主キーだけで決まる(関数従属)ものがあれば別テーブルに分離します。

前回まで使用したテーブルは、第2正規形になっていません。
codeで決まるnameが全てのレコードに存在しています。
このような項目が複数ありますので、これを分離して別テーブルにするのが第2正規形になります。

第3正規形
第1正規形である
いかなる非キー属性も候補キーに対して推移的関数従属をしない

推移的関数従属とは
Aが決まればBが決まり、Bが決まればCが決まる、しかし、AからCは決まらない場合です。
A→B、B→C、かつ、A→Cではない
レコードに、キーの一部ではない値が含まれる場合、その値を別のテーブルに分離します。
フィールドの内容が、テーブル内の複数のレコードに適用されるときは、それらのフィールドを別のテーブルに配置します。

「推移的関数従属」であるかどうかは、データの特質を良く見なければ簡単には判断できません。
第3正規形をあまり厳密に適用すると、使いづらいデータベースになってしまう場合もあります。
第3正規形は、頻繁に変更されるデータ以外では、あまり厳密に適用しないほうが良い場合もあります。

マスタデータとトランザクションデータ

マスタデータ:IT用語辞典より
企業内データベースなどで、業務を遂行する際の基礎情報となるデータのこと。
また、それらを集約したファイルやデータベースのテーブルなど。
単に「マスタ」と省略するのが一般的である。

トランザクションデータ:IT用語辞典より
企業の情報システムなどが扱うデータの種類の一つで、業務に伴って発生した出来事の詳細を記録したデータのこと。

各種伝票データはトランザクションデータであり、科目情報や取引先情報はマスタデータになります。

データべースのテーブル命名規則
命名規則は、それぞれの会社・システムにおいて検討されるものですが、良く使われるものとして、
マスタデータは、m_○○、m○○、M_○○、M○○
トランザクションデータは、t_○○、t○○、T_○○、T_○○
このような命名は良く使われます。
○○は英文を使って端的な単語にする場合や、数値で管理したりします。
テーブル数が多くなってくると、英単語ではかえって分かりづらくなってしまう為、数値(さらに記号を追加)を使ったりします。

SQL入門では、
マスタデータは、m_○○
トランザクションデータは、t_○○
このような名称を使っていきます。

正規化したテーブル定義

前回まで使用したテーブルを正規化します。

前回までの非正規化状態
テーブル:t_sales


列名 PRIMARY KEY AUTOINCREMENT NOT NULL
1 id INTEGER
2 code TEXT
3 name TEXT
4 address TEXT
5 sales_date TEXT
6 item_code TEXT
7 item_name TEXT
8 item_price INTEGER
9 item_count INTEGER
10 item_amount INTEGER
11 comment TEXT
※UNIQUやDEFAULTは使っていないので省略しています。

正規化後の状態
テーブル:m_customer
列名 NOT NULL PRIMARY KEY AUTOINCREMENT
1 code TEXT
2 name TEXT
2 address TEXT

CREATE TABLE m_customer (
 "code" TEXT NOT NULL
,"name" TEXT NOT NULL
,"address" TEXT
,PRIMARY KEY (code)
);

テーブル:m_item


列名 NOT NULL PRIMARY KEY AUTOINCREMENT
1 item_code TEXT
2 item_name TEXT

CREATE TABLE m_item (
 "item_code" TEXT NOT NULL
,"item_name" TEXT NOT NULL
,PRIMARY KEY (item_code)
);

このテーブルだけで見た時には、カラム名はcode,nameだけでも良いように見えます。
ここはどちらが良いという事もありませんし、意見がわかれるところだと思います。

テーブル:t_sales
列名 PRIMARY KEY AUTOINCREMENT NOT NULL
1 id INTEGER
2 code TEXT
3 sales_date TEXT
4 item_code TEXT
5 item_price INTEGER
6 item_count INTEGER
7 comment TEXT

CREATE TABLE t_sales (
 "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,"code" TEXT NOT NULL
,"sales_date" TEXT
,"item_code" TEXT
,"item_price" INTEGER
,"item_count" INTEGER
,"comment" TEXT
);

※item_priceは、item_codeが同じでも金額が違うので従属関係にありません。
例えば割引方法があり、それによって決まるのであれば、
そもそもその情報が欠落したデータベースだという事になります。
これはデータ蓄積時に良く注意しなければなりません。
後から、このような情報を追加することは極めて困難になります。

SQL入門では、SQLの解説が主になりますので、なるべく簡単なデータ例としています。

SQLの作成は、シートに上記の定義を入れて、
テーブル名変更と列追加(ALTER TABLE)とテーブル自動作成
エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。今回は作成したテーブルの名称変更とカラム(列)を追加するSQLを解説します。そして、エクセルのシートにテーブル情報を記入して、そこからテーブルを自動作成するVBAも紹介します。
こちらで作成したプロシージャーを使用しています。

非正規化から正規化したテーブルを作成

m_customerとm_itemは、データを全て新規にINSERTするので、そのままCREATEしてからINSERTします。
ただし、同名のテーブルがないかは確認してから行ってください。

t_salesは、カラム削除になりますが、SQLiteではカラム削除ができません。
データをシートに取得後に、DROP TABLE→CREATE TABLE→INSERTの順で行います。

テーブル:m_customerの作成
t_salesをcodeでユニーク化したデータになります。
ワークシートでも簡単にできますが、SQLで作成してみましょう。

SELECT code,name,address
 FROM t_sales
 GROUP BY code
 ORDER BY code

このSQLを、
VBAクラスの全コード:データの取得
SQL入門の「データの取得:条件指定(SELECT,WHERE)」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール:clsSQLite
このVBAで実行すると、シートにcodeのユニークなデータが作成されます。

VBA マクロ SQL 正規化

このシートを使って、
データの挿入:バルクインサート
エクセルVBAでデータベースを扱うためのSQL入門です。前回でデータをINSERTすることができるようになりましたが、処理時間に不満がありました。せっかくデータベースを使うのでしたら、エクセルで扱いに困ってしまうような大量データをストレスなく扱いたいところです。
ここのテーブル自動作成でデータをINSERTします。

INSERT INTO m_customer
 ("code","name","address")
 VALUES
 ('001','販売先001','住所001')
,('002','販売先002','住所002')
,('003','販売先003','住所003')
,・・・

テーブル:m_itemの作成
m_customer同様に、以下のSQLでシートに出力してから、INSERTします。
SELECT item_code,item_name
 FROM t_sales
 GROUP BY item_code
 ORDER BY item_code

VBA マクロ SQL 正規化

INSERT INTO m_item
 ("item_code","item_name")
 VALUES
 ('10001','商品10001')
,('10002','商品10002')
,('10003','商品10003')
,・・・

テーブル:t_salesの作成
m_customer同様に、以下のSQLでシートに出力しします。

SELECT code,sales_date,item_code,item_price,item_count,comment
 FROM t_sales

100万行あると少し時間がかかりますが、それでも10秒程度でしょう。

VBA マクロ SQL 正規化

データはシートに確保したので、テーブルを削除します。

DROP TABLE t_sales

CREATE TABLE t_sales (
 "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,"code" TEXT NOT NULL
,"sales_date" TEXT
,"item_code" TEXT
,"item_price" INTEGER
,"item_count" INTEGER
,"comment" TEXT
);

シートからデータをINSERTします。

INSERT INTO t_sales
 ("code","sales_date","item_code","item_price","item_count","comment")
 VALUES
 ('057','2019-11-28','10051',1200,12,'備考001')
,('046','2019-11-29','10099',1500,20,'備考002')
,('027','2019-11-30','10027',1300,5,'備考003')
,・・・

データベース正規化の最後に

今回は、次回以降のテーブルを結合して取得する前準備を行いました。
データベース正規化については、特に専門的にDBを扱うという事でないなら、単純に重複データを排除するくらいの認識で問題ありません。

ここでは、シートを経由してデータを別のテーブルにINSERTしましたが、
実は、SQLで他のテーブルから直接INSERTすることができます。
これについては、後々に紹介します。

次回は、複数テーブルを結合してデータ取得するSQLの解説になります。



同じテーマ「SQL入門」の記事

データの挿入:バルクインサート
データの取得:条件指定(SELECT,WHERE)
データの取得:集約集計、並べ替え(DISTINKT,GROUP,HAVING,ORDER)
SQL関数と演算子
データベースにおけるNULLの扱い方
データベースの正規化とマスタの作成
テーブルを結合して取得(INNER JOIN,OUTER JOIN)
複数のSELECT結果を統合(UNION,UNION ALL)
データの更新(UPDATE)
データの削除(DELETE)
他のテーブルを基にデータを追加/更新/削除


新着記事NEW ・・・新着記事一覧を見る

他のテーブルを基にデータを追加/更新/削除|SQL入門(12月8日)
データの削除(DELETE)|SQL入門(12月7日)
データの更新(UPDATE)|SQL入門(12月6日)
複数のSELECT結果を統合(UNION,UNION ALL)|SQL入門(12月5日)
テーブルを結合して取得(INNER JOIN,OUTER JOIN)|SQL入門(12月4日)
データベースの正規化とマスタの作成|SQL入門(12月3日)
データベースにおけるNULLの扱い方|SQL入門(12月2日)
オブジェクト変数とは何か|VBA技術解説(12月2日)
SQL関数と演算子|SQL入門(12月1日)
データの取得:集約集計、並べ替え(DISTINKT,GROUP,HAVING,ORDER)|SQL入門(11月30日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • データベースの正規化とマスタの作成

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
    掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
    本文下部へ