更新不整合を体験する
不整合は3種類ある
第1章で更新不整合を1つ見ました。実は、冗長なテーブルで起きる事故は3種類あります。正規化はこの3つを潰すための作業なので、まず3つとも自分の手で起こしておきます。
題材はこの表です。店舗と商品と売上が1枚に入っています。
| id | store_id | store_name | store_pref | product_name | unit_price |
|---|---|---|---|---|---|
| 1 | 1 | 渋谷店 | 東京都 | ノートPC | 128000 |
| 2 | 1 | 渋谷店 | 東京都 | マウス | 3200 |
| 3 | 3 | 博多店 | 福岡県 | キーボード | 8900 |
更新不整合
渋谷店の店名を変えるには2行を書き換える必要があります。1行だけ書き換わると、同じ store_id に2つの店名ができます。第1章で起こしたのがこれです。
原因は、店名という1つの事実が2行に書かれていることです。
挿入不整合
新しく札幌店を開店しました。まだ1件も売れていません。この店をどうやって登録しますか。
この表は売上の表なので、売上の行を作らないと店舗を登録できません。売れていないのに架空の売上行を作るか、product_name を NULL にした変な行を作るか、どちらもおかしなことになります。
売上が無いと店舗を登録できない。これが挿入不整合です。本来は無関係なはずの2つの事実が、1枚の表に同居しているせいで巻き込まれています。
削除不整合
博多店の売上は id=3 の1件だけです。この注文がキャンセルされて行を消すと、博多店という店が存在するという事実も一緒に消えます。
消したかったのは売上1件だけなのに、店舗の情報まで失われました。これが削除不整合です。
3つは同じ1つの原因から出ている
並べてみると、原因はすべて同じです。
独立した複数の事実が、1枚の表に混ざっている。
店舗という事実と、売上という事実は、生まれるタイミングも消えるタイミングも違います。寿命が違うものを同じ行に入れると事故が起きる、と考えると分かりやすいでしょう。
正規化とは、混ざった事実を寿命ごとに分ける作業です。次のレッスンから、その分け方を段階的に見ていきます。
1行だけ削除する
図書館の貸出を取り消す例です。
SQL クエリ
DELETE FROM borrowings WHERE id = 8;DELETE FROM に表名を書き、WHERE で消す行を選びます。条件を外すと全行が消えるので、対象の id を確認してから実行します。残った行の件数は最初に学んだ GROUP BY と count(*) で確認できます。
手を動かす
一番痛い削除不整合を起こします。売上を1件消しただけで、店舗が1つ消えることを確かめてください。
sales_flat から、キャンセルされた売上 id = 3 の行を DELETE してください。
そのあと、残った売上に登場する店舗を store_id、store_name、売上件数 cnt の3列で、store_id の順に取り出してください。
消したのは売上1件だけのはずですが、結果に何店舗残っているかを見てください。
削除と残った店舗の集計を担当します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 4 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE sales_flat (
id integer PRIMARY KEY,
store_id integer NOT NULL,
store_name text NOT NULL,
store_pref text NOT NULL,
product_name text NOT NULL,
unit_price integer NOT NULL
);
INSERT INTO sales_flat VALUES
(1, 1, '渋谷店', '東京都', 'ノートPC', 128000),
(2, 1, '渋谷店', '東京都', 'マウス', 3200),
(3, 3, '博多店', '福岡県', 'キーボード', 8900),
(4, 2, '梅田店', '大阪府', 'モニタ', 24800);
期待される出力
| store_id | store_name | cnt |
|---|---|---|
| 1 | 渋谷店 | 2 |
| 2 | 梅田店 | 1 |
ヒント
まだ開いていません
付箋は 3 枚。下の段ほど答えに近づきます
ヒント 2
ヒント 3答えに近い