データサイエンスとデータベース（04/X） - 「大人の教養・知識・気付き」を伸ばすブログ

　データベースの理論的な部分を

データサイエンスのためのデータベース (データサイエンス入門シリーズ)

作者:吉岡真治,村井哲也
講談社

Amazon

を基に学んでいく。

power-of-awareness.com

今日のまとめ
3.　SQLと正規化
次回

今日のまとめ

$\mathrm{SQL}$ の基本的な文法をまとめつつ、正規形の概念を導入する準備として、関数従属性を導入する。

関係スキーマ $R$ の属性列 $A_R$ に対して、 $X,Y$ を部分列とする。関係スキーマ $R$ において関数従属性「 $X\rightarrow Y$ 」が成り立つのは、 $R$ の任意のインスタンス $r$ について、その任意のタプル $t,t^{\prime}\in r$ を取ったときに
$\begin{aligned}t\left[X\right]=t^{\prime}\left[X\right]\Rightarrow t\left[Y\right]=t^{\prime}\left[Y\right]\end{aligned}$
が成り立つときをいう。

3.　SQLと正規化

3.1　SQL

　国際標準である $\mathrm{SQL}$ は以下の3言語から構成される。

データ定義言語 $\mathrm{DDL}$
データ操作言語 $\mathrm{DML}$
データ制御言語 $\mathrm{DCL}$

ここではデータ操作に焦点を当てて議論する。
　 $\mathrm{SQL}$ は列から構成されるテーブルである。

3.1.1　データ探索

　 $\mathrm{SQL}$ において基本的なデータの問い合わせは $\mathrm{SELECT}$ 文で済ませる。

- テーブルXからすべてのデータを検索する
SELECT *
  FROM X;

- 射影：テーブルXから列Aを検索する
SELECT A
  FROM X;

- 重複を除外した検索：テーブルXから列Aを重複なく検索する
SELECT DISTINCT A
                  FROM X;

- 条件を付けた検索：テーブルXから列Bがb以上であるようなデータを検索する
SELECT *
  FROM X
WHERE X.B >= b;

- 複数条件を付けた検索①：テーブルXから列Bがb以上かつ列Cがc以上であるようなデータを検索する
SELECT *
  FROM X
WHERE (X.B >= b AND X.C >= c);

- 複数条件を付けた検索②：テーブルXから列Bがb以上または列Cがc以上であるようなデータを検索する
SELECT *
  FROM X
WHERE (X.B >= b OR X.C >= c);

$\mathrm{SQL}$ では命令の最後にセミコロン(半角)を付ける。

3.1.2　BETWEEN演算子、IN演算子

　 $\mathrm{SQL}$ の条件式では $\mathrm{BETWEEN}$ , $\mathrm{IN}$ , $\mathrm{LIKE}$ , $\mathrm{NULL}$ といった演算子が使用できる。

- テーブルXから列Aの値がa1以上a2以下であるようなデータを検索する
SELECT *
  FROM X
 WHERE A BETWEEN a1 AND a2;

- テーブルXから列Bの値がb1またはb2であるようなデータを検索する
SELECT *
  FROM X
 WHERE A IN ('b1','b2');

- テーブルXから列Cの値の前方2文字が'ab'であるようなデータを検索する
SELECT *
  FROM X
 WHERE C LIKE 'ab%'; - %は長さ0文字以上の任意の文字列

- テーブルXから列Cの値の前方2文字が'ab'であるような3文字であるようなデータを検索する
SELECT *
  FROM X
 WHERE C LIKE 'ab_'; - _は長さ1文字の任意の文字列

- テーブルXから列Dの値が空欄であるようなデータを検索する
SELECT *
   FROM  X
 WHERE D IS NULL;

- テーブルXから列Dの値が空欄でないようなデータを検索する
SELECT *
   FROM  X
 WHERE D IS NOT NULL;

3.1.3　集計関数

　 $\mathrm{SQL}$ では集計関数もいくつか用意されている。

- テーブルXから列Aの平均を求める
SELECT AVG(A)
   FROM X;

- テーブルXから列Bの最大値を求める
SELECT MAX(B)
   FROM X;

3.1.4　グループ化とソート

- テーブルXから、それに所属する列Bの平均がb以上であるような列Aを検索する
SELECT A, AVG(B)
  FROM X
 WHERE AVG(B) >= b;

- テーブルXから列Cおよび列Dを、列Dの降順(昇順)で検索する
SELECT C,D
    FROM X
ORDER BY D DESC -- ASC;

3.1.5　結合

　 $\mathrm{WHERE}$ の後で結合条件式を書くことでテーブルを結合できる。

- テーブルXとYをそれぞれにある列A,Bで結合する
SELECT X.*,Y.*
  FROM X,Y
 WHERE X.A=Y.B

- テーブルXとYをそれぞれにある列A,Bで結合する
SELECT X.*,Y.*
  FROM X JOIN Y
 WHERE X.A=Y.B

3.1.6　副問い合わせ

- 列A,Bを持つテーブルXについて、列Aに対応する列Cを持つテーブルYの列Dがdであるようなデータを検索する
SELECT A,B
    FROM X
 WHERE A IN
     (SELECT C
        FROM Y
       WHERE D='d')

3.1.7　データベースの定義・更新・管理

　 $\mathrm{SQL}$ で関係データベースを作成する手順には、

データベースを作る
　 $\mathrm{CREATE}$ $\mathrm{DATABASE}$ 文
テーブルを作る
　 $\mathrm{CREATE}$ $\mathrm{TABLE}$ 文
データを作る
　 $\mathrm{INSERT}$ 文、 $\mathrm{DELETE}$ 文、 $\mathrm{UPDATE}$ 文

がある。

3.2　正規化

　関係データベースでは一般に複数のテーブルを通じてデータを管理する。全てのデータを1つのテーブルで管理した場合、複数の内容のデータが混在し、データを更新するときに問題が発生する可能性があるからだ。そこで関数従属性に着目してデータベースを構成する属性を分類し、分類された属性でそれぞれ関係スキーマを構成する。この構成で基礎となるのが正規化である。

3.2.1　更新時異常

　すべてのデータを1つのテーブルで管理した場合、

挿入時異常
　たとえば複数の主キーがあるテーブルにおいて、一方の主キーが空値であるようなデータを挿入しようとすると、主キー制約に違反することが該当する。
削除時異常
　たとえば一方の主キーについて1つしかない値を削除したとき、もしそれに対応する主キーの値が1つしなかった場合、もう一方の主キーに関連した(=主キーでない他列の)値が一緒に消えてしまうことが該当する。
修正時異常
　たとえばある主キーの関係列を更新した場合、その関係列値と同じ値を持つ他列の同列値が一緒に更新されてしまうことが該当する。

という3つの異常が発生し得ることが知られており、これらを総称して更新時異常という。

3.2.2　関数従属性

　前項で述べたように、テーブルにデータの内容に関するまとまりが複数存在し、それが更新時異常の原因であると考えられる。ここで問題になるのは、このデータのまとまりをどのように特定するか、である。これを解決するために関数従属性を導入する。
　

例
　列 $\mathrm{a,b,c,d,e,f}$ を持つ(主キーは $\mathrm{a,d}$ で、そのことを表すのに以降大文字とする)テーブル $\mathrm{X}$ について、他の主キー属性との関係を考える。
　まず列 $\mathrm{D}$ の値を1つ定めると列 $\mathrm{e}$ が一意に定まる場合、関数従属性
$\begin{aligned} \mathrm{列D}\rightarrow\mathrm{列e} \end{aligned}$
が成り立つという。非キー属性 $\mathrm{f}$ についても同様のことが成り立つとすれば、
$\begin{aligned} \mathrm{列D}\rightarrow\mathrm{列f} \end{aligned}$
である。これらを併せて
$\begin{aligned} \mathrm{列D}\rightarrow\mathrm{列e},\mathrm{列f} \end{aligned}$
と書ける。
　一方で非キー属性 $\mathrm{b}$ はキー属性 $\mathrm{A}$ に対して一意に定まるとすれば、
$\begin{aligned} \mathrm{列A}\rightarrow\mathrm{列b} \end{aligned}$
である。他方で非キー属性 $\mathrm{c}$ が主キー $\mathrm{A,D}$ により一意に定まるならば、
$\begin{aligned} \mathrm{列A,列D}\rightarrow\mathrm{列c} \end{aligned}$
と書ける。

　以上を踏まえ、関数従属性を定義する。

定義3.1　関数従属性　関係スキーマ $R$ の属性列 $A_R$ に対して、 $X,Y$ を部分列とする。関係スキーマ $R$ において関数従属性「 $X\rightarrow Y$ 」が成り立つのは、 $R$ の任意のインスタンス $r$ について、その任意のタプル $t,t^{\prime}\in r$ を取ったときに

$\begin{aligned} t\left[X\right]=t^{\prime}\left[X\right]\Rightarrow t\left[Y\right]=t^{\prime}\left[Y\right] \end{aligned}$