魔術師見習いのノート

データベースの構造・スキーマを3階層に分けて、定義する方式がある。

内部スキーマ

DBの実態。データを効率的に管理するための物理設計。DBもデータである以上、ファイルとして管理される。DB開発者が考えるスキーマ。

概念スキーマ

テーブル。データを一意に効率的に管理するための設計。DB設計者が考えるスキーマ。
キーワード：正規化, ER図

外部スキーマ

テーブルやビュー、SELECTの結果。ユーザ視点のもので、実際にデータを使用する際に考えるスキーマ。

生徒のテストの点数を管理する場合、エクセルのような表計算ソフトを使用するヒトは、以下のような表を作成するだろう。

このような表を横持ちテーブルという。1つのレコードに対して各フィールドに必ず1つの値が入る場合、有効な設計である。しかしRDBでは横持ちテーブルは、列間の独立性が高く、常に良いとは限らない。横持ちテーブルに対し、以下のようなテーブルを縦持ちテーブルという。

一見すると、縦持ちテーブルは横持ちテーブルに比べて、可読性が低く、かつデータが重複しているため、効率が悪いように見えるかもしれない。しかし、RDBでは縦持ちテーブルの方が効率が良い場合がある。

横持ちテーブルで科目数を増減したい場合、列を追加する必要がある。RDBでは列の追加は、ALTER文を使用してテーブル設計を変更する必要がある。これに対し、縦持ちテーブルはINSERT文だけで追加可能である。すなわち、テーブルを再設計するコストがない。

横持ちテーブル

ALTER TABLE テーブル ADD 美 int;

ALTER TABLE テーブル DROP 美;

縦持ちテーブル

INSERT INTO テーブル VALUES ('衛宮四郎', '美', 50);

DELETE FROM テーブル WHERE 科目='美';

更に、横持ちテーブルでこのような変更を行った場合、各フィールドにデータが入らないケースが発生する(すなわちnull)。そうなると、列ごとの何らかの処理を行う場合、nullを除外するような処理を記述しなければならなくなる(例：COALESCEを使う)。

また、データ処理においても、縦持ちテーブルは優れた性質を持つ。生徒ごとの合計点を計算する場合、縦持ちテーブルと横持ちテーブルではそれぞれ次のように記述する。

横持ちテーブル

SELECT 名前,国+数+社+理+英 FROM テーブル GROUP BY 名前;

縦持ちテーブル

SELECT SUM(点数) FROM テーブル WHERE 名前="佐藤太郎"

このように、縦持ちテーブルは、ある関連するデータの値が0個〜複数個ある場合に、横持ちテーブルよりも拡張性に優れている。

しかしその反面、縦持ちテーブルは横持ちテーブルに比べて、レコード数が増加しやすい。RDBにおいて、レコード数が多いと、検索や処理速度の低下を招くため、前述のようなケースでも必ず縦持ちテーブルが良いとは限らない。例えば前述の横持ちテーブルは、1つのUPDATE文やINSERT文で5教科の更新や追加をできるのに対し、縦持ちテーブルではそれぞれ5回発行しなければならない。

横持ち→縦持ち

以下のようなテーブルがあったとする。

SELECT ニックネーム1 AS ニックネーム FROM テーブル WHERE 名前 ='佐藤太郎' UNION
SELECT ニックネーム2 AS ニックネーム FROM テーブル WHERE 名前 ='佐藤太郎' UNION
SELECT ニックネーム3 AS ニックネーム FROM テーブル WHERE 名前 ='佐藤太郎';

前述の三層スキーマでも述べたように、SQLでは実態のテーブル(概念スキーマ)と実際に扱うデータ構造(外部スキーマ)を分けることができる。

縦持ちから横持ちテーブルへの変換については後述する。

CREATE VIEW ニックネームテーブル (名前,ニックネーム) AS
SELECT 名前,ニックネーム1 FROM テーブル UNION
SELECT 名前,ニックネーム2 FROM テーブル UNION
SELECT 名前,ニックネーム3 FROM テーブル;

プログラミング経験者のSQL初心者は、はじめてSQLを使用するとSQLはただデータを格納するためだけに使い、データの整理はプログラミングでどうにかしようとしたくなる(少なくとも私はそうだった)。

例えば以下のような学校があったとする(Appendixに初期化用のSQL文を記載)。

db = func()
# func()は「select * from sample_table」の結果を受け取る。
# [
# {"クラス"=> "1-A", "男"=>15, "女"=>12}, 
# {"クラス"=> "1-B", "男"=>14, "女"=>10}, 
# {"クラス"=> "2-A", "男"=>11, "女"=>20}, 
# {"クラス"=> "2-B", "男"=>21, "女"=>10}, 
# {"クラス"=> "2-A", "男"=>15, "女"=>16}, 
# {"クラス"=> "3-B", "男"=>14, "女"=>15}
# ]

red = 0
white = 0
db.each do |x|
  case x["クラス"]
  when "1-A", "2-A", "3-A"
    red += x["男"] + x["女"]
  when "1-B", "2-B", "3-B"
    white += x["男"] + x["女"]
  end
end
puts "赤:#{red} ホワイト:#{white}"

SELECT
  CASE class WHEN '1-A' THEN '赤'
             WHEN '2-A' THEN '赤'
             WHEN '3-A' THEN '赤'
             WHEN '1-B' THEN '白'
             WHEN '2-B' THEN '白'
             WHEN '3-B' THEN '白'
             END,
  SUM(population)
from sample_table
  GROUP BY CASE class WHEN '1-A' THEN '赤'
             WHEN '2-A' THEN '赤'
             WHEN '3-A' THEN '赤'
             WHEN '1-B' THEN '白'
             WHEN '2-B' THEN '白'
             WHEN '3-B' THEN '白'
             END;

CASE式を覚えることで、性能改善や利便性の向上を図ることができる。ただし、CASE式や結合処理はDBの負荷を上げるため、アプリ側で対処するケースもあるため、そこは状況に応じた使い分けが必要である。

縦持ち→横持ち

SELECT class,
       sum(CASE WHEN sex='男' THEN population ELSE 0 END) as '男',
       sum(CASE WHEN sex='女' THEN population ELSE 0 END) as '女'
FROM sample_table GROUP BY class;

PRIMARY KEY制約

UNIQUE制約

NOT NULL制約

CHECK制約

データの重複をなくし、整合的にデータを取り扱えるデータベースを設計することを正規化という。 正規化は段階に合わせて6つに分類される。

正規化(データの整合性)とパフォーマンスは基本的にトレードオフの関係にある。そのため、必ずしも段階が高ければ良いとはいう訳ではなく、状況に応じて使い分けるのが望ましい。

なお、一般的には第3正規化まで行っている設計が多い。

第1正規化

以下の条件を満たしたテーブルにすること。

• 1つのセルに1つのデータのみ。
• レコードを一意に特定するキー(主キー:PK)がある。

非正規形のままではRDBで扱うことができない。 言ってみれば第1正規化とはRDBに格納可能な形にすることである。

1つのセルに1つのデータのみ。

名前	子供
田中一郎	太郎
	花子
伊藤五郎	null

→

名前	子供
田中一郎	太郎
田中一郎	花子
伊藤五郎	null

レコードを一意に特定するキー(主キー:PK)がある

例1)主キーが名前の場合

横持ち

名前	国	数	社	理	英
佐藤太郎	50	60	56	70	60
鈴木二郎	60	55	70	55	70
田中三郎	40	70	80	60	80

例2)主キーが名前と科目(複数)の場合

縦持ち

名前	科目	点数
佐藤太郎	国	50
佐藤太郎	数	60
佐藤太郎	社	56
佐藤太郎	理	70
佐藤太郎	英	60
鈴木二郎	国	60
鈴木二郎	数	55
鈴木二郎	社	70
鈴木二郎	理	55
鈴木二郎	英	70
田中三郎	国	40
田中三郎	数	70
田中三郎	社	80
田中三郎	理	60
田中三郎	英	80

キー

キーには、ナチュラルキーとサロゲートキーの2種類がある。前述したようなあるデータモデルから一意に特定できる要素を見出して、それをキーとしたものを、ナチュラルキー、データモデルにはないが、一意に特定するために新しく追加したキーをサロゲートキーという。例えば前述の縦持ちテーブルのPKをサロゲートキーにすると、次のようになる。

ID	名前	科目	点数
01	佐藤太郎	国	50
02	佐藤太郎	数	60
03	佐藤太郎	社	56
04	佐藤太郎	理	70
05	佐藤太郎	英	60
06	鈴木二郎	国	60
07	鈴木二郎	数	55
08	鈴木二郎	社	70
09	鈴木二郎	理	55
10	鈴木二郎	英	70
11	田中三郎	国	40
12	田中三郎	数	70
13	田中三郎	社	80
14	田中三郎	理	60
15	田中三郎	英	80

サロゲートキーを使用するメリットには、次のようなものがある(ミックさん談)。

1. そもそも主キーにできる項目がない場合(重複してる)
2. 主キーの値が使いまわされる場合
3. 主キーの体系が変化する場合

2と3の例には、商品コードと商品を扱う場合に、商品コードが再利用されるような場合がある。 商品コードをナチュラルキー(PK)とした場合、レコードを上書きするしかないが、別にキーを設定することで、新たに追加するだけで良い。

第2正規化

主キーの一部から非キーが一意に特定できる場合、テーブルが分けること。第2正規化とは、第1正規形から部分関数従属性を取り除くことである。

関数従属性

ある属性(列)の値が決まる時、別の属性が一意に決まること。

部分関数従属性

キーの一部に非キー属性が関数従属している状態のこと。

言ってみれば、第2正規化とは情報からエンティティ毎に整理することである。 大雑把に説明すると、例えばサッカーのグループリーグの情報を表すDBがあり、選手を起点に考える。

• 選手の名前(キー)
• 選手の身長
• 選手の体重
• 選手の所属チーム
• 所属チームのホームグラウンド
• 所属チームの勝利数
• 所属チームの敗北数

第2正規形でない第1正規形は、選手と所属チームという2つのエンティティをひとまとめに管理しているが、これらを別のものとして分けるのが第2正規形である。前述の一覧でいえば、「〜の」でテーブルを分割ことである。

RDBではキーがなければレコードを作成できない。そのため、第2正規形でない第1正規形のように1つのテーブルにエンティティが混在する場合、全体の主キーが決まらなければレコードが作成できない。また、同一のエンティティを複数のレコードで管理するため、データの整合性を保持するのが難しい。これを解消したものが第2正規形である。

第3正規化

第2正規形から推移関数従属性を取り除くこと。非キーから別の非キーを一意に特定できる場合、それをテーブルに分けること。

言ってみれば、第3正規化とはエンティティの情報を更に整理することである。第2正規化で全体の情報をエンティティに分けるが、このエンティティの持つ情報から更に一意に特定できる情報を独立化させたものが第3正規形である。

ボイス・コッド正規化

第3正規形のうち非キーから主キーが一意に定まらないDBのこと。ボイス・コッド正規形とは、非キーから主キーへの関数従属をなくした状態。

第4正規形

ボイス・コッド正規化までが情報の整理を行うのに対し、第4・5正規化はエンティティ間の関係を整理する。

エンティティはキーによって一意に特定できる。そのため、キーだけのテーブル(エンティティ)を作成することで、エンティティの関係を表すエンティティを作成できる。

例えば「A→→B|C」の関係のエンティティがある場合、「|Aのキー|Bのキー|」と「|Aのキー|Cのキー|」のようにして関係を表す。

第5正規形

1つの関係エンティティにつき1つの関係を表すDBが第5正規形である。

A→→B、A→→Cに加えてB→→Cの関係があるでは、3つのエンティティの関係を表すテーブルができる。

この時に以下のようにして分割することで、全てのエンティティの関係を表すことができる。

• |A|B|
• |A|C|
• |B|C|

Appendix

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8

START TRANSACTION;
CREATE DATABASE sampledb;
USE sampledb;
CREATE TABLE sample_table
(class  CHAR(3),
 sex CHAR(1),
 population  INTEGER);
INSERT INTO sample_table VALUES ('1-A', '男', 15);
INSERT INTO sample_table VALUES ('1-A', '女', 12);
INSERT INTO sample_table VALUES ('1-B', '男', 14);
INSERT INTO sample_table VALUES ('1-B', '女', 10);
INSERT INTO sample_table VALUES ('2-A', '男', 11);
INSERT INTO sample_table VALUES ('2-A', '女', 20);
INSERT INTO sample_table VALUES ('2-B', '男', 21);
INSERT INTO sample_table VALUES ('2-B', '女', 10);
INSERT INTO sample_table VALUES ('3-A', '男', 15);
INSERT INTO sample_table VALUES ('3-A', '女', 16);
INSERT INTO sample_table VALUES ('3-B', '男', 14);
INSERT INTO sample_table VALUES ('3-B', '女', 15);
COMMIT;