勉強日記

チラ裏

ch3～ch5 演習

RDB 勉強メモ

ch3演習問題

支社支店商品

支社コード(PK) 支社名 支店コード(PK) 支店名 商品コード(PK) 商品名 商品分類コード 分類名
001 東京 01 渋谷 001 石鹸 C1 水洗用品
001 東京 01 渋谷 002 タオル C1 水洗用品
001 東京 01 渋谷 003 ハブラシ C1 水洗用品
001 東京 02 八重洲 002 タオル C1 水洗用品
001 東京 02 八重洲 003 ハブラシ C1 水洗用品
001 東京 02 八重洲 004 コップ C2 食器
001 東京 02 八重洲 005 箸 C2 食器
001 東京 02 八重洲 006 スプーン C2 食器
002 大阪 01 堺 001 石鹸 C1 水洗用品
002 大阪 01 堺 002 タオル C1 水洗用品
002 大阪 02 豊中 007 雑誌 C3 書籍
002 大阪 02 豊中 008 爪切り C4 日用雑貨

3-1 正規形の次数

  • 1カラム1スカラ値なので第一正規形ではある
  • 部分関数従属があるため第二正規形でない

3-2 関数従属性

{支社コード} -> {支社名}
{支社コード, 支店コード} -> {支店名}
{商品コード} -> {商品名|商品分類コード|分類名}
  • 推移的関数従属もある
{支社コード,支店コード,商品コード} -> {商品分類コード} -> {分類名}

3-3 正規化

支店商品

支社コード(PK) 支店コード(PK) 商品コード(PK)
001 01 001
001 01 002
001 01 003
001 02 002
001 02 003
001 02 004
001 02 005
001 02 006
002 01 001
002 01 002
002 02 007
002 02 008

支社

支社コード(PK) 支社名
001 東京
002 大阪

支店

支社コード(PK) 支店コード(PK) 支店名
001 01 渋谷
001 02 八重洲
002 01 堺
002 02 豊中

商品(未完成)

商品コード(PK) 商品名 商品分類コード 分類名
001 石鹸 C1 水洗用品
002 タオル C1 水洗用品
003 ハブラシ C1 水洗用品
004 コップ C2 食器
005 箸 C2 食器
006 スプーン C2 食器
007 雑誌 C3 書籍
008 爪切り C4 日用雑貨
  • 商品の推移的関数従属をさらにほぐす

商品

商品コード(PK) 商品名 商品分類コード
001 石鹸 C1
002 タオル C1
003 ハブラシ C1
004 コップ C2
005 箸 C2
006 スプーン C2
007 雑誌 C3
008 爪切り C4

商品分類

商品分類コード(PK) 分類名
C1 水洗用品
C2 食器
C3 書籍
C4 日用雑貨
  • 以上で第三正規形
  • 非キーからキーへの関数従属はないためBCNFでもある
  • 支店商品テーブルは支店と商品との多対多関連のみ表しているため、「複数の多値従属性がある」にはあたらない
    • 第五正規形

ch4演習問題

4-1 ER図

  • 暇な時

4-2 関連エンティティ

  • 支店商品エンティティが関連エンティティ
    • FK2つ
      • {支社コード, 支店コード}
      • {商品コード}

4-3 多対多の関連

  • 多対多の例
    • 動画サイトの動画とタグ
    • 学生と部活(兼部可能)
    • 書籍と著者
  • 関連実体

タグ付け

動画コード(PK) タグコード(PK)
...

所属

学生コード(PK) 部活コード(PK)
...

著述

ISBN(PK) 著者コード(PK)
...

ch5演習問題

5-1 正規化されたテーブルに対するSQL

商品分類ごとの商品数(結果には分類名含む)

SELECT 商品分類.分類名, COUNT(商品.商品コード)
  FROM 商品分類
 INNER JOIN 商品
         ON 商品.商品分類コード = 商品分類.商品分類コード
 GROUP BY 商品分類.分類名;

支社/支店別取扱商品一覧(結果には支社名、支店名、商品名含む)

SELECT 支社.支社名, 支店.支店名, 商品.商品名
  FROM 支店商品
 INNER JOIN 支社
         ON 支社.支社コード = 支店商品.支社コード
 INNER JOIN 支店
         ON 支店.支社コード = 支店商品.支社コード 
        AND 支店.支店コード = 支店商品.支店コード
 INNER JOIN 商品
         ON 商品.商品コード = 支社商品.商品コード;

最も取扱商品数が多い支店の支店コードと商品数

link

  • どちゃくそ複雑
SELECT 支店商品.支社コード
     , 支店商品.支店コード
     , COUNT(*) AS 商品数
  FROM 支店商品
 GROUP BY 支店商品.支社コード
        , 支店商品.支店コード
HAVING COUNT(*) >= (SELECT MAX(TMP.商品数) AS 最大商品数
                      FROM (SELECT 支社コード
                                 , 支店コード
                                 , COUNT(*) AS 商品数
                             FROM 支店商品
                            GROUP BY 支社コード
                                   , 支店コード) TMP);
  • まず支店コードについて集計し、商品数を得る
    • 支店コードは支社コードと組み合わせないと意味をなさないため、支社コードも加える
SELECT 支社コード
     , 支店コード
     , COUNT(*) AS 商品数
  FROM 支店商品
 GROUP BY 支社コード
        , 支店コード;
支社コード 支店コード 商品数
001 01 3
001 02 5
002 01 2
002 02 2
  • 集計結果の商品数の最大値を求める
SELECT MAX(TMP.商品数) AS 最大商品数
  FROM (SELECT 支社コード
             , 支店コード
             , COUNT(*) AS 商品数
          FROM 支店商品
         GROUP BY 支社コード
                , 支店コード) TMP;
最大商品数
5
  • 集計後の表についてHAVINGで絞り込み
    • 同じクエリが重複。クソクエリ感がすごい
SELECT 支店商品.支社コード
     , 支店商品.支店コード
     , COUNT(*) AS 商品数
  FROM 支店商品
 GROUP BY 支店商品.支社コード
        , 支店商品.支店コード
HAVING COUNT(*) >= (SELECT MAX(TMP.商品数) AS 最大商品数
                      FROM (SELECT 支社コード
                                 , 支店コード
                                 , COUNT(*) AS 商品数
                             FROM 支店商品
                            GROUP BY 支社コード
                                   , 支店コード) TMP);
支社コード 支店コード 商品数
001 02 5
  • FK制約がない場合は支店商品テーブルのコードのが指す「支店」が
    支店テーブルに存在しない可能性があるため、INNER JOINしないと駄目かも
SELECT 支店商品.支社コード
     , 支社.支社名
     , 支店商品.支店コード
     , 支店.支店名
     , COUNT(支店商品.商品コード) AS 商品数
  FROM 支店商品
 INNER JOIN 支社
         ON 支社.支社コード = 支店商品.支社コード
 INNER JOIN 支店
         ON 支店.支社コード = 支店商品.支社コード
        AND 支店.支店コード = 支店商品.支店コード
 GROUP BY 支店商品.支社コード
        , 支店商品.支店コード;
支社コード 支社名 支店コード 支店名 商品数
001 東京 01 渋谷 3
001 東京 02 八重洲 5
002 大阪 01 堺 2
002 大阪 02 豊中 2

5-2 非正規化によるSQLチューニング

商品分類ごとの商品数(結果には分類名含む)

  • 商品分類テーブルと商品テーブルとのJOINをやめたい
  • 商品分類テーブルに商品数カラム追加
SELECT 分類名, 商品数
  FROM 商品分類;

支社/支店別取扱商品一覧(結果には支社名、支店名、商品名含む)

  • JOINしまくりやめたい
  • 商品分類以外分割する前のテーブル使う
    • 第二正規形ですらない

最も取扱商品数が多い支店の支店コードと商品数

  • 支店テーブルに商品数カラム追加
SELECT 支社コード
     , 支店コード
     , 商品数
  FROM 支店
 WHERE 商品数 >= (SELECT MAX(商品数)
                    FROM 支店);