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 商品.商品コード = 支社商品.商品コード;
最も取扱商品数が多い支店の支店コードと商品数
- どちゃくそ複雑
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 支店);