Upgrade to Pro — share decks privately, control downloads, hide ads and more …

データベース03: 関係データモデル

データベース03: 関係データモデル

1. 関係データモデルのデータ構造
2. 一貫性制約

講義ノートURL
https://dbnote.hontolab.org/content/relational-data-model/01.html

Y. Yamamoto

April 29, 2024
Tweet

More Decks by Y. Yamamoto

Other Decks in Technology

Transcript

  1. 関係データベース 様々な事柄 あらゆる事象・状態を表で表現して格納・処理 項目A B C D xxx xxx xx

    xx xxx xx xx xx xxx xx xx xx 関係データベース システム(DBS) モデリング 格納 表(関係データ) データの正しさを管理しやすくするために関係データモデルで抽象化
  2. 関係データモデルの構造(1/2) 関係名(テーブル名) 表(関係)の名前 タプル(レコード) ・モデリングした結果である1つ1つの事柄・事象 ・表の⾏に対応 商品ID 名称 単価 登録日

    P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P1000 きのこの里 200 2023/01/08 商品 関係名 タプル
  3. 関係データモデルの構造(2/2) 商品ID 名称 単価 登録日 P1 はーいお茶 130 2020/07/15 P2

    午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P1000 きのこの里 200 2023/01/08 商品 属性 属性 ・表の各⾒出しに対応 ・タプルを構成する要素の種別名(注⽬するデータの特徴名) ・各属性に対応するタプルの値を属性値と呼ぶ 属性「登録⽇」の 属性値
  4. 直積 集合S1 , S2 , …, Sn が与えられたとき, !! ×!"

    …×!# = %! , … , %# %! ∈ !! , … , %# ∈ !# } なる !! × ⋯×!"を S1 , …, Sn 上の直積集合と呼ぶ 例 A = 2, 3 , , = {−2, −3} が与えられたとき A×, = 2, −2 , 2, −3 , 3, −2 , (3, −3) 2 A 3 -2 B -3
  5. Q1: 直積 Q. S$%&' = Python, R, C(( , S)*)+$%,-./

    = ⼈気, 不⼈気 , S0-12-3+$./ = 難, 普通, 易 このとき,S$%&' ×!4546789:;< ×S0-12-3+$./ は? とする.
  6. Q1: 直積 Q. S$%&' = Python, R, C(( , S)*)+$%,-./

    = ⼈気, 不⼈気 , S0-12-3+$./ = 難, 普通, 易 このとき,S$%&' ×!4546789:;< ×S0-12-3+$./ は? S$%&' ×!4546789:;< ×S0-12-3+$./ = { (Python, ⼈気, 難), (Python, ⼈気,普), (Python, ⼈気,易), (Python, 不⼈気, 難), (Python, 不⼈気,普), (Python, 不⼈気,易), (R, ⼈気, 難), (R, ⼈気,普), (R, ⼈気,易), (R, 不⼈気, 難), (R, 不⼈気,普), (R, 不⼈気,易), とする. (C++, ⼈気, 難), (C++, ⼈気,普), (C++, ⼈気,易), (C++, 不⼈気, 難), (C++, 不⼈気,普), (C++, 不⼈気,易) } 18 タプル A.
  7. 数学上の「関係」 集合S1 , S2 , …, Sn が与えられたとき, !! ×

    ⋯×!"の部分集合を S1 , …, Sn 上のn項関係と呼ぶ 例 A = 2, 3 , , = {−2, −3} が与えられたとき A×, = 2, −2 , 2, −3 , 3, −2 , (3, −3) . ! = 2, −3 , 3, −2 はA, B上のn項関係. なぜなら ! ⊂ #×%. このとき
  8. Q2: 関係 Q. S$%&' = Python, R, C(( , S)*)+$%,-./

    = ⼈気, 不⼈気 , S0-12-3+$./ = 難, 普通, 易 S$%&' , !4546789:;< , S0-12-3+$./上の関係の例を挙げよ. とする.
  9. Q2: 関係 Q. S$%&' = Python, R, C(( , S)*)+$%,-./

    = ⼈気, 不⼈気 , S0-12-3+$./ = 難, 普通, 易 S$%&' , !4546789:;< , S0-12-3+$./上の関係の例を挙げよ. { (Python, ⼈気,易),(R, 不⼈気,普通), とする. (C++, ⼈気, 難) } ⊂ S!"#$ ×*%&%'()*+,- ×S./01/23!45 関係は集合であるため 集合を⽰す記号が必要 A.
  10. 関係スキーマ 関係 関係スキーマ インスタンス = + 関係の名前と関係がもつ属性,⼀貫性制約に関する情報 (" A! ,

    … , A" , {'! , … , '# }) 記法 関係名 属性 ⼀貫性制約の集合 " A! , … , A" ⼀貫性制約が⾃明 or 考慮しないとき
  11. 関係スキーマ 関係 関係スキーマ インスタンス = + 関係の名前と関係がもつ属性,⼀貫性制約に関する情報 ID 名称 単価

    登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 商品 商品(ID, 名称, 単価, 登録⽇) 関係 スキーマ
  12. Q3: 関係スキーマ Q. 関係スキーマ 学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) に従う表データの例を作成せよ

    学籍番号 氏名 学部 年齢 出身都道府県 S1 川澄 桜 医学部 23 三重 S2 山畑 滝子 文学部 20 愛知 S3 田辺 通 薬学部 22 岐阜 S4 田辺 瑞穂 DS学部 19 北海道 学⽣ A.
  13. 属性のドメイン - 関係スキーマに記された属性の値が取り得る値の集合 - 属性ごとにドメインを定義する - 属性AのドメインはDom(A)と表記 ドメイン(定義域) ID 名称

    単価 登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 商品 ()* 単価 = ! | ! ∈ ℕ = {1, 2, 3,…,1000, …}
  14. 属性のドメイン - 関係スキーマに記された属性の値が取り得る値の集合 - 属性ごとにドメインを定義する - 属性AのドメインはDom(A)と表記 ドメイン(定義域) ID 名称

    単価 登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 商品 678 名称 = ! | ! ∈ ⽂字列集合 = {あ, い, う, え, お, …, ああ, あい, …, はーいお茶, …}
  15. 属性のドメイン - 関係スキーマに記された属性の値が取り得る値の集合 - 属性ごとにドメインを定義する - 属性AのドメインはDom(A)と表記 ドメイン(定義域) ID 名称

    単価 登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 商品 678 単価 = + | + ∈ ℕ 678 ID = ! | ! ∈ $から始まる⽂字列集合 678 名称 = ! | ! ∈ ⽂字列集合 678 登録⽇ = ! | ! ∈ 年⽉⽇集合 ドメインが属性の取り得る値を規定
  16. コラム: データの型 • データはその種類を表す「型」を持つ • 関数や演算の引数には「型」が設定されている “Python” “⼭本祐輔” “⽣⻨⽣⽶⽣卵” 421

    3.141592 -4678501 2024/4/29 2024-4-29 令和6年4⽉29⽇ 数値型 ⽂字列型 ⽇付型 ⽂字列型のデータである ことを明⽰するために ダブルクォーツで囲む “3.141592” これは数字ではない
  17. コラム: データの型 • データはその種類を表す「型」を持つ • 関数や演算の引数には「型」が設定されている A + B =

    add(A, B) 演算⼦の例 ⾜し算の引数は数値型を想定している 3.141592 + 1 = 4.1415926535 “3.141592” + 1 = 計算不能 型の効⽤の例 型があるから計算の正しさを保証できる
  18. Q4: ドメイン Q. 関係スキーマ 学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) の各属性について,そのドメインを定義せよ.

    $%& 学籍番号 = ( | ( ∈ !から始まる⽂字列集合 $%& ⽒名 = ( | ( ∈ ⽂字列集合 $%& 学部 = ⼯学部, 医学部, 薬学部, ⽂学部, $!学部 $%& 年齢 = ( | ( ∈ ℕ, ( > 17 $%& 都道府県 = 北海道, ⻘森, … , 沖縄 A.
  19. インスタンス(1/2) 関係 関係スキーマ インスタンス = + • 関係スキーマ< A! ,

    … , A" における 678 @! , … , 678(@# )の直積の部分集合 • 表におけるタプルの集合に対応 ! ⊂ #$% &! × ⋯×#$%(&" ) これが関係におけるインスタンス
  20. インスタンスの例 $%& 1$ ×$%&(名称) ×$%&(単価) ×$%&(登録⽇) ココは以下の直積の部分集合(インスタンス) ID 名称 単価

    登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 商品(ID, 名称, 単価, 登録⽇) 678 単価 = + | + ∈ ℕ 678 ID = ! | ! ∈ $から始まる⽂字列集合 678 名称 = ! | ! ∈ ⽂字列集合 678 登録⽇ = ! | ! ∈ 年⽉⽇集合
  21. インスタンスの例 $%& 1$ ×$%&(名称) ×$%&(単価) ×$%&(登録⽇) ココは以下の直積の部分集合(インスタンス) ID 名称 単価

    登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 商品(ID, 名称, 単価, 登録⽇) 678 単価 = + | + ∈ ℕ 678 ID = ! | ! ∈ $から始まる⽂字列集合 678 名称 = ! | ! ∈ ⽂字列集合 678 登録⽇ = ! | ! ∈ 年⽉⽇集合 {(“P001”, “こんな商品はありません”, 500億円, 794/01/01)} ドメインの定義上,インスタンスとしては 変なものもあり得る
  22. 重要:集合の要素は重複が許されない(1/2) *C = {1, 2, 3, 4, 4, 5} =

    {1, 2, 3, 4, 5} *D = {⼭本, ⾠⼰, 能勢, ⼭本} = {⼭本, ⾠⼰, 能勢} *E = { 三重, 津 , 愛知, 名古屋 , (三重, 津)} = { 三重, 津 , 愛知, 名古屋 } 集合内の重複する要素は無視される 集合内の要素の順序も無視される
  23. 重要:集合の要素は重複が許されない(2/2) ID 名称 単価 登録日 P1 はーいお茶 130 2020/07/15 P2

    午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 P3 健康麦茶 150 2021/02/16 商品 ID 名称 単価 登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 商品 関係データモデルに従うとするとタプルの重複はダメ
  24. ⾮正規形と第1正規形(2/2) ドメイン要素として原⼦値以外の値を取りうる関係 非正規形の関係 ドメイン要素として原⼦値のみ取りうる関係 第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 … 「購買品⽬」の値として 集合を許してしまっている 正規化 正規化された関係はデータ構造が簡潔になり扱いやすい
  25. ドメイン制約 関係:(; 1 , ..., ; % )に含まれるタプルの各成分は 対応する属性のドメイン要素でなければならない 商品(ID,

    名称, 単価, 登録⽇) 678 単価 = F F ∈ ℕ} 678 ID = ! | ! ∈ $から始まる⽂字列集合 678 名称 678 登録⽇ 関係スキーマ ID 名称 単価 登録日 P1 はーいお茶 130 2020/07 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 令和3年2月16日 P100 きのこの里 2百 2023/01/08 = " | " ∈ 年⽉⽇集合(⻄暦表記) = F | F ∈ ⽂字列集合 ドメイン制約に違反 商品
  26. Q5: ドメイン制約 !"# 学籍番号 = % | % ∈ (から始まる⽂字列集合

    !"# ⽒名 = % | % ∈ ⽂字列集合 !"# 学部 = ⼯学部, 医学部, 薬学部, ⽂学部, !(学部 !"# 年齢 = % | % ∈ ℕ, % > 17 !"# 都道府県 = 北海道, ⻘森, … , 沖縄 Q. 関係スキーマ 学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) の各属性のドメインを定義した上で, ドメイン制約に違反するタプルの例を2,3挙げよ A.
  27. Q5: ドメイン制約 Q. 関係スキーマ 学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) の各属性のドメインを定義した上で,

    ドメイン制約に違反するタプルの例を2,3挙げよ 学籍番号 氏名 学部 年齢 出身都道府県 S1 川澄 桜 経済学部 23 三重 S2 山畑 滝子 文学部 20 アメリカ S3 田辺 通 薬学部 17 岐阜 K4 田辺 瑞穂 DS学部 19 北海道 S5 山畑 川名 DS学部, 薬学部 27 愛知 学⽣ A.
  28. キーの概念(1/5) 超キー 関係Rにおける属性集合のうち,それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるもの 学籍番号 氏名 学部 年齢 S1 川澄

    桜 経済学部 23 S2 山畑 滝子 文学部 20 S3 田辺 通 薬学部 18 S4 田辺 瑞穂 DS学部 19 S5 山畑 川名 DS学部 27 学⽣ 学籍番号が決まれば どの学⽣かは決まる 学籍番号は超キー その値が決まると表の⾏が1つに決まると思えばよい
  29. キーの概念(2/5) 超キー 関係Rにおける属性集合のうち,それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるもの 学籍番号 氏名 学部 年齢 S1 川澄

    桜 経済学部 23 S2 山畑 滝子 文学部 20 S3 田辺 通 薬学部 18 S4 田辺 瑞穂 DS学部 19 S5 山畑 川名 DS学部 27 学⽣ {学籍番号} {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} … 超キーは複数ある
  30. キーの概念(3/5) キー(候補キー) 関係Rにおける属性集合のうち,それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるものうち,極⼩なもの 学籍番号 氏名 学部 年齢 S1 川澄

    桜 経済学部 23 S2 山畑 滝子 文学部 20 S3 田辺 通 薬学部 18 S4 田辺 瑞穂 DS学部 19 S5 山畑 川名 DS学部 27 学⽣ 最も⼩さい部分集合 {学籍番号} {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} … キーは「学籍番号」
  31. キーの概念(4/5) キー(候補キー) 関係Rにおける属性集合のうち,それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるものうち,極⼩なもの 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx

    川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 AB5230xxx 山畑 川名 DS学部 学⽣ キーは複数あり得る キー = 学籍番号 Passport番号 or
  32. Q6: 候補キー(R4年度ITパスポート試験 問65改題) Q. 関係スキーマ 従業員(従業員ID, ⽒名, 部⾨コード, ⽣年⽉⽇, 住所)

    において,候補キーは何か. なお,関係「従業員」は以下の制約条件をもつ •各従業員は重複のない従業員番号を1つだけもつ •同姓同名の従業員はいない(⼊社させない) •各部⾨は重複のない部⾨コードを1つだけもつ •1つの部⾨には複数名の従業員が所属する •1⼈の従業員が所属する部⾨は1つだけである
  33. Q6: 候補キー(R4年度ITパスポート試験 問65改題) Q. 関係スキーマ 従業員(従業員ID, ⽒名, 部⾨コード, ⽣年⽉⽇, 住所)

    において,候補キーは何か. なお,関係「従業員」は以下の制約条件をもつ •各従業員は重複のない従業員番号を1つだけもつ •同姓同名の従業員はいない(⼊社させない) •各部⾨は重複のない部⾨コードを1つだけもつ •1つの部⾨には複数名の従業員が所属する •1⼈の従業員が所属する部⾨は1つだけである A. {従業員ID} {⽒名}
  34. キーの概念(5/5) 主キー 候補キーのうち,値として未定義や空値(NULL値)を取る 可能性がなく,かつ運⽤上最も都合のよいキーの1つ 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx

    川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 AB5230xxx 山畑 川名 DS学部 学⽣ パスポートを 持っていない人もいそう 主キーは 「学籍番号」 キーはインスタンスではなくスキーマに定義されることに注意
  35. Q7: 主キー Q. 以下の関係「会員管理」において 想定される主キーは何か? 店舗コード 店舗名 会員番号 会員名 会員種別

    S01 星ヶ丘 1 山畑 滝子 ゴールド S01 星ヶ丘 2 田辺 通 ゴールド S02 八事 1 北 千種 学生 S02 八事 2 川澄 桜 プラチナ S03 御器所 1 川澄 桜 一般
  36. Q7: 主キー Q. 以下の関係「会員管理」において 想定される主キーは何か? 店舗コード 店舗名 会員番号 会員名 会員種別

    S01 星ヶ丘 1 山畑 滝子 ゴールド S01 星ヶ丘 2 田辺 通 ゴールド S02 八事 1 北 千種 学生 S02 八事 2 川澄 桜 プラチナ S03 御器所 1 川澄 桜 一般 A. {店舗コード, 会員番号}
  37. 超キー・候補キー・主キーの関係性 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx 川澄 桜 経済学部

    S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 AB5230xxx 山畑 川名 DS学部 学⽣ 主キー 学籍番号 {学籍番号} パスポート 番号 超キー {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} (候補)キー …
  38. キー制約 関係!(" 1 , ..., " $ )のある属性集合が主キーと指定された時, どのタプルも主キーに関する条件に従う必要がある 学生(Passport番号,

    学⽣番号, ⽒名, 学部) 関係スキーマ 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx 川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 北 千種 DS学部 学⽣ 主キーには下線を引く Passport番号が空値 のためキー制約違反
  39. 外部キー 関係Rx の属性FKが取り得る値が関係Ry の主キーが 取り得る値のいずれかであるとき,FKは外部キーとなる 学生ID 姓 名 所属 s00001

    川澄 桜 A学部 s00002 山畑 滝子 B学部 s00003 田辺 通 C学部 科目ID 科目名 開講年度 c0001 線形代数 2023 c0002 線形代数 2024 c0003 統計学入門 2024 学⽣ 科⽬ … … 科目ID 学生ID 成績 c0001 s00001 不可 c0002 s00001 良 c0002 s00002 優 c0003 s00003 可 履修 … 科⽬IDは外部キー 学⽣IDは外部キー 履修. 科⽬'( ⊂ 科⽬. 科⽬'( 履修. 学⽣'( ⊂ 学⽣. 学⽣'(
  40. 参照制約(もしくは参照整合性制約) 関係Rx の属性FKが関係Ry のPKの外部キーであるとき, FKが取り得る値は外部キー条件を満たす必要がある 学生ID 姓 名 所属 s00001

    川澄 桜 A学部 s00002 山畑 滝子 B学部 s00003 田辺 通 C学部 科目ID 科目名 開講年度 c0001 線形代数 2023 c0002 線形代数 2024 c0003 統計学入門 2024 学⽣ 科⽬ … … 科目ID 学生ID 成績 c0001 s00001 不可 線形代数 s00001 良 c0002 s00002 優 c0003 田辺通 可 履修 … 履修. 科⽬'( ⊂ 科⽬. 科⽬'( 履修. 学⽣'( ⊂ 学⽣. 学⽣'( の参照制約違反 の参照制約違反
  41. Q8: 参照整合性制約 Q. 以下の関係スキーマをもつ5つの関係かなる データベースにおいて,定義すべき参照制約は? 顧客( 顧客ID, ⽒名, 性別 )

    店舗( 店舗ID, 店舗, 住所 ) 商品( 商品ID, 商品名, 商品カテゴリID, 単価 ) 商品カテゴリ( 商品カテゴリID, カテゴリ名) 購買( 購買ID, 店舗ID, 顧客ID, 商品ID, 個数, 購買⽇)
  42. Q8: 参照整合性制約 Q. 以下の関係スキーマをもつ5つの関係かなる データベースにおいて,定義すべき参照制約は? 顧客( 顧客ID, ⽒名, 性別 )

    店舗( 店舗ID, 店舗, 住所 ) 商品( 商品ID, 商品名, 商品カテゴリID, 単価 ) 商品カテゴリ( 商品カテゴリID, カテゴリ名) 購買( 購買ID, 店舗ID, 顧客ID, 商品ID, 個数, 購買⽇)
  43. Q8: 参照整合性制約 A. 購買. 顧客ID ⊂ 顧客.顧客ID 購買. 店舗ID ⊂

    店舗.店舗ID 購買. 商品ID ⊂ 購買.商品ID 商品. 商品カテゴリID ⊂ 商品カテゴリ.商品カテゴリID
  44. 回 実施日 トピック 1 04/15 ガイダンス:データベースを使わない世界 2 04/22 データベースの概念 3

    04/29(祝) 関係データモデル 4 05/13 SQL (1/3) 5 05/20 SQL (2/3) 6 05/27 SQL (3/3) 7 06/03 SQL演習 – レポート課題1 8 06/10 実体関連モデル (1/3) 9 06/17 実体関連モデル (2/3) 10 06/24 実体関連モデル (3/3) 11 07/01 正規化 (1/2) 12 07/08 正規化 (2/2) 13 07/15(祝) データベース設計演習 – レポート課題2 14 07/22 索引付け 15 07/29 NoSQL 16 08/05 期末試験 今後の予定 63