Share
Embed
データベース11: 正規化(1/2) - 望ましくない関係スキーマ
1. 実体関連モデルから関係スキーマへの変換
2. 更新時異状
3. 情報無損失分解
講義ノートURL
https://dbnote.hontolab.org/content/db-design/01.html
Y. Yamamoto PRO
June 29, 2026
Transcript
71 slides
-
関係スキーマ 関係 関係スキーマ インスタンス = + 関係の名前と関係がもつ属性,⼀貫性制約に関する情報 (𝐑 A! ,
… , A" , {𝜎! , … , 𝜎# }) 記法 関係名 属性 ⼀貫性制約の集合 𝐑 A! , … , A" ⼀貫性制約が⾃明 or 考慮しないとき -
典型的な関係データモデル 学生ID 姓 名 入学年 所属 s00001 川澄 桜 2023
A学部 s00002 山畑 滝子 2024 B学部 s00003 田辺 通 2024 C学部 科目ID 科目名 開講年度 c0001 線形代数 2023 c0002 線形代数 2024 c0003 統計学入門 2024 科目ID 学生ID 成績 c0001 s00001 不可 c0002 s00001 良 c0002 s00002 優 c0003 s00003 可 学⽣ 科⽬ 履修 … … … データの正しさを担保するためにデータを複数の表に分割 -
⾮正規形と第1正規形 ドメイン要素として原⼦値以外の値を取りうる関係 非正規形の関係 ドメイン要素として原⼦値のみ取りうる関係 第1正規形の関係 購買ID 購買品目 売上 登録日 T1
はーいお茶, きのこの里, のど飴 530 2020/07/15 T2 午前の紅茶 130 2020/09/25 T3 麦茶, おにぎり 250 2021/02/16 T100 きのこの里 200 2023/01/08 … 購買履歴(⾮正規系の関係) 購買履歴(第1正規形) 購買ID 購買品目 売上 登録日 T1 はーいお茶 130 2020/07/15 T1 きのこの里 230 2020/07/15 T1 のど飴 170 2020/07/15 T2 午前の紅茶 130 2020/09/25 T3 麦茶 120 2021/02/16 T3 おにぎり 130 2021/02/16 … 「購買品⽬」の値として 集合を許してしまっている 正規化 -
データモデリングの流れ 概念モデリング 論理モデリング 対象世界を ざっくりとモデル化 DBMSに応じて 概念モデルを変換 実体関連モデル 関係データモデル 実体関連モデルを⽤いると関係スキーマを機械的に導出可能
𝐑 A! , … , A" 𝐑 A! , … , A" 𝐑 A! , … , A" -
以前作成した実体関連図 商品 商品ID 商品名 価格 発売⽇ ユーザ ユーザ名 ⽒名 email
住所 購⼊希望 登録⽇ 製造 メーカー 企業名 email TEL ショッピングサイトにおける「ユーザが購⼊希望の商品」 「商品の製造メーカー」の情報の管理 -
「実体型」の関係スキーマへの変換⼿順 1. 実体型名を関係スキーマの関係名にする 2. 属性を関係スキーマの属性にする 3. キーを関係スキーマの主キーにする 商品 商品ID 価格
商品名 商品 ( 商品ID , 商品名, 価格 ) 関係スキーマ変換 -
関係スキーマとインスタンスの対応関係(1/2) 商品ID 名称 単価 P1 はーいお茶 130 P2 午前の紅茶 130
P3 健康麦茶 150 P1000 きのこの里 200 商品 商品 ( 商品ID , 価格 , 商品名 ) … -
Q1: 関係スキーマへの変換(1/4) A. ユーザ ( ユーザ名, ⽒名, 住所, email )
商品 ( 商品ID, 商品名, 発売⽇, 価格 ) メーカー ( 企業名, TEL, email ) 注文 ( 注⽂ID, 注⽂⽇ ) -
「関連型」の関係スキーマへの変換⼿順 多対多関連パターン 1. 関連名を関係スキーマの関係名にする 2. 関連型とつながるすべての実体型のキーを 関連型の主キーとする 3. 関連型の属性を関係スキーマの属性に追加 購入希望(
ユーザ名 , 商品ID , ) 登録⽇ 4. 主キーに関する参照整合性制約を追加する 購⼊希望.ユーザ名 ⊆ ユーザ.ユーザ名 購⼊希望.商品ID ⊆ 商品.商品ID 商品 ユーザ ユーザ名 ⽒名 email 住所 購⼊希望 登録⽇ 商品名 価格 発売⽇ 商品ID 参照制約 -
関係スキーマとインスタンスの対応関係(2/2) ユーザ名 氏名 email 住所 U1 山畑滝子 … … U2
川澄桜 … … U3 北千種 … … ユーザ 購入希望 ( ユーザ名 , 商品ID , 登録⽇) … 商品ID 名称 単価 P1 はーいお茶 130 P2 午前の紅茶 130 P3 健康麦茶 150 商品 … ユーザ名 商品ID 登録日 U1 P1 2024/06/21 U1 P1 2024/06/29 U2 P3 2024/07/01 購⼊希望 … -
「関連型」の関係スキーマへの変換⼿順 多対1関連パターン 1. 関連名を関係スキーマの関係名にする 2. 関連型とつながる実体型のうち「多」の実体型のキーを 関連型の主キーとする 3.「1」の実体型のキー&関連型属性をスキーマ属性として追加 製造 (
商品ID , ) 企業名 4. 主キーに関する参照整合性制約を追加する 製造.商品ID ⊆ 商品.商品ID 製造.企業名 ⊆ メーカー.企業名 メーカー 商品 商品ID 商品名 価格 発売⽇ 製造 TEL email 企業名 -
「関連型」の関係スキーマへの変換⼿順 1対1関連パターン 1. 関連名を関係スキーマの関係名にする 2. 関連型とつながる実体型のうち、いずれかの「1」の 実体型のキーを関連型の主キーとする 3. 残りの実体型のキー&関連型属性をスキーマ属性として追加 ロゴ
( 企業名 , ) 画像ID 4. 主キーに関する参照整合性制約を追加する ロゴ.画像ID ⊆ 画像.画像ID 製造.企業名 ⊆ メーカー.企業名 メーカー 画像 画像ID サイズ ファイルパス ロゴ TEL email 企業名 -
Q2: 関係スキーマへの変換(2/4) A. 明細 ( 注⽂ID, 商品ID, 数量 ) 明細.注⽂ID
⊆ 注⽂.注⽂ID 明細.商品ID ⊆ 商品.商品ID -
Q3: 関係スキーマへの変換(3/4) A. 購買履歴 ( 注⽂ID, ユーザ名 ) 購買履歴.注⽂ID ⊆
注⽂.注⽂ID 購買履歴.ユーザ名 ⊆ ユーザ.ユーザ名 -
「汎化・継承」の関係スキーマへの変換⼿順 1. 継承先の実体型名を関係スキーマの関係名にする 2. 継承元の実体型のキーを関係スキーマの主キーにする 3. 継承先の実体型の属性を関係スキーマの属性に追加 4. 主キーに関する参照整合性制約を追加する プレミアムユーザ(
ユーザ名 , ) IsA プレミアム ユーザ ユーザ ユーザ名 ⽒名 email 住所 登録⽇ 登録⽇ プレミアムユーザ.ユーザ名 ⊆ ユーザ.ユーザ名 -
「弱実体型」の関係スキーマへの変換⼿順 1. 弱実体型名を関係スキーマの関係名にする 2. 部分キーおよび弱関連でつながる実体型のキーを 関係スキーマの主キーにする 3. 弱実体型の属性を関係スキーマの属性に追加 4. 主キーに関する参照整合性制約を追加する
家族 ( ユーザ名 , ⽒名 , ) ユーザ ユーザ名 ⽒名 email 住所 続柄 同居 家族 ⽒名 続柄 家族.ユーザ名 ⊆ ユーザ.ユーザ名 -
「弱関連型」の関係スキーマへの変換⼿順 1. 弱関連型名を関係スキーマの関係名にする 2. 弱実体型の部分キーおよび弱関連でつながる 実体型のキーを関係スキーマの主キーにする 3. 弱関連型の属性を関係スキーマの属性に追加 4. 主キーに関する参照整合性制約を追加する
5. 弱実体型に対応する関係でタプルを把握できる場合, 作成したスキーマを削除する 同居 ( ユーザ名 , ⽒名 ) ユーザ ユーザ名 ⽒名 email 住所 同居 家族 ⽒名 続柄 家族 ( ユーザ名 , ⽒名 , ) 続柄 削除 -
例: 架空ショッピングサイトの購買履歴を扱うデータベース 購買 ( 購買ID , ユーザ, 購⼊商品, 単価, 数量
) 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 ⼩売店店主の ⼭畑さん ⼭畑さんたちが 作成した関係スキーマ このテーブル1つで購買履歴や商品情報を管理!? -
挿⼊時異状 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130
3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 NULL NULL たきのこの里山 250 NULL 新商品「たきのこの⾥⼭」をDBに登録するために, (NULL, NULL, たきのこの⾥⼭, 250, NULL) を追加 -
挿⼊時異状 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130
3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 NULL NULL たきのこの里山 250 NULL 新商品「たきのこの⾥⼭」をDBに登録するために, (NULL, NULL, たきのこの⾥⼭, 2050, NULL) を追加 × キー制約違反でエラー -
削除時異状 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130
3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 ユーザU2が購買ID T3ののど飴について 払い戻しを求めたので,購買記録を削除 -
削除時異状 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130
3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 ユーザU2が購買ID T3ののど飴について 払い戻しを求めたので,購買記録を削除 DBから「のど飴の商品情報」が失われる (NULL, NULL, のど飴, 170, NULL)を挿⼊しても⼿遅れ -
Q5: 異状(1/2) Q. 架空の家具販売店「⼋⾼家具」では、以下のような 関係スキーマを⽤いて営業記録をつけはじめた. 営業記録 ( ユーザ, 商品 ,
連絡先, メーカー, 営業担当 ) 関係表「営業記録」のみを⽤いてデータ管理したとき に⽣じる,削除時異状の例を挙げよ. -
修正時異状 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130
3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 はーいお茶の単価が120円の間違いだった ので,データを修正したい 120円 1つの商品の修正なのに購買履歴をすべて修正するのは冗⻑ -
Q6: 異状(2/2) Q. 架空の家具販売店「⼋⾼家具」では、以下のような 関係スキーマを⽤いて営業記録をつけはじめた. 営業記録 ( ユーザ, 商品 ,
メーカー, 営業担当 ) 関係表「営業記録」のみを⽤いてデータ管理したとき に⽣じる,挿⼊時異状の例を挙げよ. -
Q7: 削除,挿⼊,修正(1/3) Q. 架空の家具販売店「⼋⾼家具」では、以下のような 2つの関係スキーマを⽤いて営業記録をつけはじめた. 営業記録 ( ユーザ, 商品 ,
メーカー, 営業担当 ) 以下の表は上の関係スキーマに基づいたDBである. 「ソファベットのメーカーをA社からE社に変更」し, その結果を⽰しなさい. 商品 ( 商品 , メーカー ) -
Q8: 削除,挿⼊,修正(2/3) Q. 営業記録 ( ユーザ, 商品 , メーカー, 営業担当
) 以下の表は上の関係スキーマに基づいたDBである. 「⻄尾担当によるtanabeeに対する本棚(⼤)の営 業記録を削除」し,その結果を⽰しなさい. 商品 ( 商品 , メーカー ) -
Q9: 削除,挿⼊,修正(3/3) Q. 営業記録 ( ユーザ, 商品 , メーカー, 営業担当
) 以下の表は上の関係スキーマに基づいたDBである. 「春⽇井担当によるユーザnorth1000に対するオ フィスチェア(メーカーD社の新商品)の営業記録 を追加」し,その結果を⽰しなさい. 商品 ( 商品 , メーカー ) -
3種類の更新時異状 挿入時異状 削除時異状 修正時異状 購買ID ユーザ 購入商品 単価 数量 T1
U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 NULL NULL たきのこの里山 250 NULL × × 120円 × 修正忘れ 主キーをNULLにはできない のど飴の 商品情報が 消失 -
3種類の更新時異状 挿入時異状 削除時異状 修正時異状 購買ID ユーザ 購入商品 単価 数量 T1
U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 NULL NULL たきのこの里山 250 NULL × × 120円 × 修正忘れ 主キーをNULLにはできない のど飴の 商品情報が 消失 なぜ更新時異状が起きるのか? -
更新時異状が発⽣する理由 独⽴して扱える情報を 1つのテーブル内に混在させているから A. 購買ID ユーザ 購入商品 単価 数量 T1
U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 商品の基礎情報は独⽴して扱えるはず… -
独⽴した情報を扱えるよう関係テーブルを射影する(1/2) 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130
3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 SELECT DISTINCT 購⼊商品, 単価 FROM 購買履歴; -
独⽴した情報を扱えるよう関係テーブルを射影する(2/2) 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130
3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 SELECT DISTINCT 購買ID, ユーザ, 購⼊商品, 数量 FROM 購買履歴; -
射影されたテーブルを使ったデータベースの更新 購買ID ユーザ 購入商品 数量 T1 U1 はーいお茶 3 T2
U1 きのこの里 2 T3 U2 のど飴 1 T4 U2 きのこの里 1 T5 U3 はーいお茶 1 T6 U4 タケノコの山 1 購入商品 単価 はーいお茶 130 きのこの里 230 のど飴 170 タケノコの山 200 タケノコの里山 250 関係「購買履歴」 関係「商品」 -
射影されたテーブルを使ったデータベースの更新 購買ID ユーザ 購入商品 数量 T1 U1 はーいお茶 3 T2
U1 きのこの里 2 T3 U2 のど飴 1 T4 U2 きのこの里 1 T5 U3 はーいお茶 1 T6 U4 タケノコの山 1 購入商品 単価 はーいお茶 130 きのこの里 230 のど飴 170 タケノコの山 200 タケノコの里山 250 関係「購買履歴」 関係「商品」 うまくテーブルを分解すれば更新時異状が発⽣しない 削除時異状ナシ 挿⼊時異状ナシ 120 修正時異状なし -
分解後のテーブルを内部結合してみる(1/2) 購買ID ユーザ 購入商品 数量 T1 U1 はーいお茶 3 T2
U1 きのこの里 2 T3 U2 のど飴 1 T4 U2 きのこの里 1 T5 U3 はーいお茶 1 T6 U4 タケノコの山 1 購入商品 単価 はーいお茶 130 きのこの里 230 のど飴 170 タケノコの山 200 関係「購買履歴」 関係「商品」 SELECT * FROM 購買履歴 INNER JOIN 商品 USING (購⼊商品); ⋈ 購⼊商品 (内部結合) -
分解後のテーブルを内部結合してみる(2/2) SELECT * FROM 購買履歴 INNER JOIN 商品 USING (購⼊商品);
購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 分解前のテーブル -
情報無損失分解 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130
3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 購買ID ユーザ 購入商品 数量 T1 U1 はーいお茶 3 T2 U1 きのこの里 2 T3 U2 のど飴 1 T4 U2 きのこの里 1 T5 U3 はーいお茶 1 T6 U4 タケノコの山 1 購入商品 単価 はーいお茶 130 きのこの里 230 のど飴 170 タケノコの山 200 ⋈ 購⼊商品 = ある関係を情報を失うことなく2つの関係に分解 内部結合によって元テーブルを 復元できる分解が可能 情報無損失分解によってDB更新の影響を極⼒⼩さくできる -
情報無損失分解 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130
3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 購買ID ユーザ 購入商品 数量 T1 U1 はーいお茶 3 T2 U1 きのこの里 2 T3 U2 のど飴 1 T4 U2 きのこの里 1 T5 U3 はーいお茶 1 T6 U4 タケノコの山 1 購入商品 単価 はーいお茶 130 きのこの里 230 のど飴 170 タケノコの山 200 ⋈ 購⼊商品 = ある関係を情報を失うことなく2つの関係に分解 内部結合によって元テーブルを 復元できる分解が可能 情報無損失分解によってDB更新の影響を極⼒⼩さくできる 分解の仕⽅がいい加減だと 情報“損失”分解になりえる… -
情報 損失 分解(1/2) 購買ID ユーザ 購入商品 単価 数量 T1 U1
はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 分解前のテーブル -
情報 損失 分解(1/2) 購買ID ユーザ 購入商品 単価 数量 T1 U1
はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 SELECT DISTINCT 購買ID, ユーザ FROM 購買履歴; -
情報 損失 分解(1/2) 購買ID ユーザ 購入商品 単価 数量 T1 U1
はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 SELECT DISTINCT ユーザ, 購⼊商品, 単価, 数量 FROM 購買履歴; -
情報 損失 分解(1/2) 購買ID ユーザ T1 U1 T2 U1 T3
U2 T4 U2 T5 U3 T6 U4 購買1 ユーザ 購入商品 単価 数量 U1 はーいお茶 130 3 U1 きのこの里 230 2 U2 のど飴 170 1 U2 きのこの里 230 1 U3 はーいお茶 130 1 U4 タケノコの山 200 1 購買2 ⋈ ユーザ SELECT * FROM 購買1 INNER JOIN 購買2 USING (ユーザ); -
情報 損失 分解(2/2) 購買ID ユーザ 購入商品 単価 数量 T1 U1
はーいお茶 130 3 T1 U1 きのこの里 230 2 T2 U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 130 1 T3 U2 きのこの里 230 1 T4 U2 のど飴 130 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 購買1 と 購買2 を 「ユーザ」で結合した表 関係「購買」には なかったレコード が増えている 分解の軸となった「ユーザ」は、その値決まっても それに対応する値(購入商品など)が一意に決まらない 失敗要因 -
情報 損失 分解(2/2) 購買ID ユーザ 購入商品 単価 数量 T1 U1
はーいお茶 130 3 T1 U1 きのこの里 230 2 T2 U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 130 1 T3 U2 きのこの里 230 1 T4 U2 のど飴 130 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 購買1 と 購買2 を 「ユーザ」で結合した表 関係「購買」には なかったレコード が増えている 分解の軸となった「ユーザ」は、その値決まっても それに対応する値(購入商品など)が一意に決まらない 失敗要因 どうすれば情報無損失分解できる? -
回 実施日 トピック 1 04/13 ガイダンス:データベースを使わない世界 2 04/20 データベースの概念 3
04/27 関係データモデル 4 05/11 SQL (1/3) 5 05/18 SQL (2/3) 6 05/25 SQL (3/3) 7 06/01 SQL演習 – レポート課題1 8 06/08 実体関連モデル (1/3) 9 06/15 実体関連モデル (2/3) 10 06/22 実体関連モデル (3/3) 11 06/29 正規化 (1/2) 12 07/06 正規化 (2/2) 13 07/13 データベース設計演習 – レポート課題2 14 07/20 索引付け 15 07/27 授業の振り返り or 発展的話題 16 期末試験 今後の予定 72