データベース
INPUT · スライド
01 / 12
SQL のコースで、注文 表と 顧客 表を JOIN でつなぐ練習をした。あのとき、こう思わなかっただろうか。そもそも最初から1枚の表にしておけば、つなぐ手間は要らないのに。
じつは1枚にまとめると困ることが起きる。同じ顧客が10回注文したら、顧客名が10か所に書かれる。名字が変わったとき、その10か所を全部直さないといけない。1つでも直し忘れたら、同じ顧客番号なのに名前が2種類あるデータができあがる。
だから表は意図的に分けてある。そして分けたものをつなぎ直すために JOIN がある。このレッスンでは、その「分け方」に名前と手順があることを見ていくよ。
02 / 12
関係データベースとは、データを2次元の表で表すデータベースのことだよ。行と列があって、表と表は互いの列の値で関連づけられる。
この表の裏には 関係モデル という数学の理屈があって、そちらでは別の呼び名を使う。試験ではこの対応がよく問われるよ。
気をつけたいのは、言葉が対応していても性質までは同じではないこと。関係モデルの属性やタプルには順序がないけれど、実装された表の列には左から右の順序があるし、行にも並び順がある。定義域も文字だけでなく数値や日付を指定できるよ。
関係 → 表タプル → 行属性 → 列定義域 → 値の範囲03 / 12
データを入れる前に、どんな列があってどんな型なのかを決めて宣言しておく必要がある。このデータ定義を集めたものが スキーマ だよ。CREATE TABLE で書いていたのは、まさにスキーマの宣言だった。
スキーマは操作の話でも制約の話でもない、形の話だという点をおさえておこう。INSERT や SELECT のような操作は別のもの、「値が空でないこと」のような制約も別のものだよ。
さらに DBMS は、スキーマを3つの層に分けて持っている。目的は下の層を変えても上に影響が出ないようにすること。ディスクの並べ方を変えてもアプリを直さなくていいのは、この分け方のおかげだよ。
外部スキーマ 利用者から見た形概念スキーマ 論理的な全体内部スキーマ 物理的な格納04 / 12
表の中のある1行を確実に指せる列が要る。それが 主キー だよ。
主キーになるには2つの条件がある。
NULL を入れてはいけない一意なだけでは足りない、というのがよく問われるところ。空欄が入ると「どの行か」を指せなくなるので、NULL も禁止されているよ。
主キーになれる候補が複数あることもある。社員表なら社員番号でもメールアドレスでも一意に決まる。このなれる候補を 候補キー と呼び、その中から選んで主キーにする。
主キーは1列でなくてもいい。「注文番号と商品番号の組」のように複数の列をまとめて主キーにできるよ。
社員(社員番号, 氏名, 部署) ~~~~~~~~ 主キー・重複しない・空(NULL)にしない05 / 12
注文 表の 顧客番号 は、顧客 表の主キーを指している。この他の表の主キーを指す列が 外部キー だよ。JOIN ... ON 注文.顧客番号 = 顧客.顧客番号 と書けたのは、この対応があったからなんだ。
外部キーを宣言しておくと、DBMS が指し先のない値を拒否してくれる。顧客 表にいない顧客番号で注文を追加しようとしても通らないし、注文が残っている顧客の行を削除しようとしても通らない。この指し先が必ず存在している状態を 参照整合性（参照一貫性）と呼ぶよ。
外部キーの目的は、あくまでこの制約をかけること。検索を速くするためのものでも、壊れたデータを復旧するためのものでもないよ。
注文(注文番号, 顧客番号) ↑ 外部キー顧客(顧客番号, 顧客名) ↑ 主キー06 / 12
表を分ける手順を知るには、まず「何が何で決まるか」を言葉にできないといけない。Xの値が決まればYの値が1つに決まるとき、YはXに 関数従属 しているといい、X → Y と書くよ。
社員番号が決まれば氏名は1つに決まるから 社員番号 → 氏名。逆は成り立たない（同姓同名がありうる）ので、向きがあることに注意しよう。
名前が付いている形が2つあるので、これを覚えておこう。
X → Y と Y → Z がつながって、X から Z が決まるこの2つが、次に出てくる第2正規形と第3正規形の的になるよ。
社員番号 → 氏名{注文番号, 商品番号} → 数量 部分従属: 商品番号 → 商品名注文番号 → 顧客番号 → 顧客名 これが推移的関数従属07 / 12
正規化 とは、関数従属を手がかりに表を分けていく手順のことだよ。段階に番号がついていて、第1・第2・第3と進めていく。
まず何もしていない状態を 非正規形 と呼ぶ。紙の伝票をそのまま写したような表で、1行の中に繰り返しが入っている。「1枚の注文伝票に商品が3行ぶら下がっている」という形だね。
第1正規形 は、この繰り返しを別の行に分けて、1つのマスに1つの値だけを入れた状態だよ。伝票1枚が1行だったところを、商品1件が1行になるように開く。
ここまでやると、ようやく SELECT で扱える形になる。1つのマスに複数の値が詰まっていたら WHERE で絞ることができないからね。
非正規形注文(番号, {商品, 数量}) 繰り返しがある第1正規形注文明細(番号, 商品, 数量)08 / 12
第2正規形 は、部分関数従属を追い出した状態だよ。主キーが「注文番号と商品番号の組」なのに、商品番号 → 商品名 のようにキーの一部だけで決まる列があったら、それを別の表に出す。商品名は 商品 表へ移す、ということだね。
第3正規形 は、推移的関数従属を追い出した状態。主キーで決まる列のうち、注文番号 → 顧客番号 → 顧客名 のようにキー以外の列を経由して決まる列があったら、それも別の表に出す。顧客名は 顧客 表へ移すよ。
覚え方は、追い出す相手が違うだけ。第2は「キーの一部で決まる列」、第3は「キー以外の列で決まる列」。どちらも出した先で主キーになり、元の表には外部キーだけが残るよ。
第1正規形 繰り返しを外に出した第2正規形 主キーの一部で決まる列を 別の表へ第3正規形 キー以外で決まる列を 別の表へ09 / 12
ここが試験でいちばん問われるところだよ。正規化の目的は、同じ事実を何か所にも書かないようにすること ― つまり 冗長性の排除 だ。
そして冗長性をなくすと何が防げるのか。更新時異状（更新時異常）と呼ばれる、更新のときに起きるおかしな事態だよ。
ここで引っかかりやすいのは、格納効率を上げるためではないという点。重複が減るぶん容量は得をするけれど、それは目的ではなく副産物。目的はデータの食い違いを起こさないことだよ。
1枚の表に顧客名を持つと顧客名が変わったとき その顧客の全行を直す → 直し漏れで食い違う分けておけば1か所で済む10 / 12
表をどう分けるかを、CREATE TABLE を書く前に絵で整理しておきたい。そのための図が E-R 図（実体関連図）だよ。対象の世界を、実体と関連という2つの概念だけで表す図なんだ。
関連には 多重度 がついていて、1対1 / 1対多 / 多対多 のどれなのかを示す。1人の顧客が多くの注文を持つなら 1対多 だよ。
多対多はそのまま表にできない。「学生と科目」のように互いに複数あるときは、間に表を1枚はさむ。受講 表を作って 1対多 を2つにするんだ。JOIN で3つの表をつなぐ場面は、これが理由だよ。
1対1 社員 — 社員証1対多 部署 — 社員多対多 学生 — 科目 ↓学生 — 受講 — 科目11 / 12
関係モデルでは、表から新しい表を作る操作を 関係演算 と呼ぶ。名前を3つ覚えれば、書いてきた SELECT 文がそのまま読み解けるよ。
SELECT に並べる列WHEREJOIN や FROM に2表射影が列、選択が行。名前と向きが逆に感じるので、ここは意識して覚えよう。
もう1つ 直積 がある。2つの表の行を総当たりで組み合わせる演算で、SQL では FROM R, S と表を並べるだけの形になる。ここに WHERE で条件を足したものが結合だよ。だから FROM A, B WHERE A.x = B.x が JOIN と同じ結果になるんだ。
射影 → SELECT の列選択 → WHERE結合 → FROM 2表 + ON直積 → FROM R, S12 / 12
正規化して表が増えると、毎回同じ JOIN を書くのが面倒になる。そこで、演算の結果に名前を付けて、表のように見せるのが ビュー だよ。
言葉を分けておこう。実際にデータが入っている表を 基底表、演算の結果として出てくる表を 導出表 と呼ぶ。ビューは名前を付けた導出表で、実データは持たない。使うたびに定義した演算が実行されるよ。
うれしいのは2つ。よく使う JOIN を1語で呼べること、そして見せたい列だけを切り出して渡せることだ。
順番と個数のきまりも押さえておこう。ビューは基底表がないと定義できないので、基底表が先。1つの基底表にいくつでもビューを作れるし、ビューの上にさらにビューを定義することもできるよ。
基底表 実際にデータがある導出表 演算の結果の表ビュー 名前を付けた導出表