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

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

Transcript

1. 関係データモデル ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス学部 yusuke_yamamoto@acm.org 第3回 データベース 2024年4月29日
2. 関係データモデルのデータ構造 1 Structure of Relational Data Model
3. 関係データベース 様々な事柄 あらゆる事象・状態を表で表現して格納・処理 項目A B C D xxx xxx xx xx xxx xx xx xx xxx xx xx xx 関係データベース システム（DBS） モデリング 格納 表（関係データ） データの正しさを管理しやすくするために関係データモデルで抽象化
4. 関係データモデル あるゆるデータを表（=関係）として表現するモデル - 1970年にEdgar Coddが提唱 - 背後に強⼒な数学的基盤をもつ
5. 関係データモデルの構造（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 商品 関係名 タプル
6. 関係データモデルの構造（2/2） 商品ID 名称 単価 登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P1000 きのこの里 200 2023/01/08 商品 属性 属性 ・表の各⾒出しに対応 ・タプルを構成する要素の種別名（注⽬するデータの特徴名） ・各属性に対応するタプルの値を属性値と呼ぶ 属性「登録⽇」の 属性値
7. 関係データモデル あるゆるデータを表（=関係）として表現するモデル - 1970年にEdgar Coddが提唱 - 背後に強⼒な数学的基盤をもつ 関係データの「関係」って何？
8. 直積 集合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
9. 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 タプル
10. 数学上の「関係」 集合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項関係． なぜなら ! ⊂ #×%. このとき
11. Q2: 関係 Q. S$%&' = Python, R, C(( , S)*)+$%,-./ = ⼈気, 不⼈気 , S0-12-3+$./ = 難, 普通, 易 S$%&' , !4546789:;< , S0-12-3+$./上の関係の例を挙げよ． { (Python, ⼈気,易),(R, 不⼈気,普通), とする． (C++, ⼈気, 難) } ⊂ S!"#$ ×*%&%'()*+,- ×S./01/23!45 関係は集合であるため 集合を⽰す記号が必要
12. 関係データモデルにおける「関係」 関係 関係スキーマ インスタンス = +
13. 関係スキーマ 関係 関係スキーマ インスタンス = + 関係の名前と関係がもつ属性，⼀貫性制約に関する情報 (" A! , … , A" , {'! , … , '# }) 記法 関係名 属性 ⼀貫性制約の集合 " A! , … , A" ⼀貫性制約が⾃明 or 考慮しないとき
14. 関係スキーマ 関係 関係スキーマ インスタンス = + 関係の名前と関係がもつ属性，⼀貫性制約に関する情報 ID 名称 単価 登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 商品 商品(ID, 名称, 単価, 登録⽇) 関係 スキーマ
15. Q3: 関係スキーマ Q. 関係スキーマ 学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) に従う表データの例を作成せよ 学籍番号 氏名 学部 年齢 出身都道府県 S1 川澄 桜 医学部 23 三重 S2 山畑 滝子 文学部 20 愛知 S3 田辺 通 薬学部 22 岐阜 S4 田辺 瑞穂 DS学部 19 北海道 学⽣
16. 属性のドメイン - 関係スキーマに記された属性の値が取り得る値の集合 - 属性ごとにドメインを定義する - 属性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, …}
17. 属性のドメイン - 関係スキーマに記された属性の値が取り得る値の集合 - 属性ごとにドメインを定義する - 属性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 名称 = ! | ! ∈ ⽂字列集合 ＝ {あ, い, う, え, お, …, ああ, あい, …, はーいお茶, …}
18. 属性のドメイン - 関係スキーマに記された属性の値が取り得る値の集合 - 属性ごとにドメインを定義する - 属性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 登録⽇ = ! | ! ∈ 年⽉⽇集合 ドメインが属性の取り得る値を規定
19. コラム: データの型 • データはその種類を表す「型」を持つ • 関数や演算の引数には「型」が設定されている “Python” “⼭本祐輔” “⽣⻨⽣⽶⽣卵” 421 3.141592 -4678501 2024/4/29 2024-4-29 令和6年4⽉29⽇ 数値型 ⽂字列型 ⽇付型 ⽂字列型のデータである ことを明⽰するために ダブルクォーツで囲む “3.141592” これは数字ではない
20. コラム: データの型 • データはその種類を表す「型」を持つ • 関数や演算の引数には「型」が設定されている A + B = add(A, B) 演算⼦の例 ⾜し算の引数は数値型を想定している 3.141592 + 1 = 4.1415926535 “3.141592” + 1 = 計算不能 型の効⽤の例 型があるから計算の正しさを保証できる
21. Q4: ドメイン Q. 関係スキーマ 学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) の各属性について，そのドメインを定義せよ． $%& 学籍番号 = ( | ( ∈ !から始まる⽂字列集合 $%& ⽒名 = ( | ( ∈ ⽂字列集合 $%& 学部 = ⼯学部, 医学部, 薬学部, ⽂学部, $!学部 $%& 年齢 = ( | ( ∈ ℕ, ( > 17 $%& 都道府県 = 北海道, ⻘森, … , 沖縄
22. インスタンス（1/2） 関係 関係スキーマ インスタンス = + • 関係スキーマ< A! , … , A" における 678 @! , … , 678(@# )の直積の部分集合 • 表におけるタプルの集合に対応 ! ⊂ #$% &! × ⋯×#$%(&" ) これが関係におけるインスタンス
23. インスタンスの例 $%& 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 登録⽇ = ! | ! ∈ 年⽉⽇集合
24. インスタンスの例 $%& 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)} ドメインの定義上，インスタンスとしては 変なものもあり得る
25. 重要：集合の要素は重複が許されない（1/2） %C = {1, 2, 3, 4, 4, 5} = {1, 2, 3, 4, 5} %D = {⼭本, ⾠⼰, 能勢, ⼭本} = {⼭本, ⾠⼰, 能勢} %E = { 三重, 津 , 愛知, 名古屋 , (三重, 津)} = { 三重, 津 , 愛知, 名古屋 } 集合内の重複する要素は無視される 集合内の要素の順序も無視される
26. 重要：集合の要素は重複が許されない（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 商品 関係データモデルに従うとするとタプルの重複はダメ
27. ⾮正規関係と第1正規形（1/2） 関係モデルではドメイン要素として原子値を想定 それ以上⼩さな構成要素に 分解すると意味が失われる値 ⽂字列 実数 ｚ 原⼦値 真偽値(true/false) ⽇付 … 整数 タプル 集合 原⼦値ではない値 関係 … リスト
28. ⾮正規形と第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 … 「購買品⽬」の値として 集合を許してしまっている 正規化 正規化された関係はデータ構造が簡潔になり扱いやすい
29. データの一貫性制約 2 Integrity Constraints
30. 関係データモデル上の⼀貫性制約 一貫性制約 ドメイン制約 キー制約 参照制約 データ従属性 DB上のデータが 実世界を正しく反映すべく データが満たすべき規則 … ⼀貫性制約を考慮して関係スキーマの定義や関係の分解を⾏う
31. 関係データモデル上の⼀貫性制約 一貫性制約 ドメイン制約 キー制約 参照制約 データ従属性 DB上のデータが 実世界を正しく反映すべく データが満たすべき規則 … ⼀貫性制約を考慮して関係スキーマの定義や関係の分解を⾏う
32. ドメイン制約 関係:(; 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 ∈ ⽂字列集合 ドメイン制約に違反 商品
33. Q5: ドメイン制約 $%& 学籍番号 = ( | ( ∈ !から始まる⽂字列集合 $%& ⽒名 = ( | ( ∈ ⽂字列集合 $%& 学部 = ⼯学部, 医学部, 薬学部, ⽂学部, $!学部 $%& 年齢 = ( | ( ∈ ℕ, ( > 17 $%& 都道府県 = 北海道, ⻘森, … , 沖縄 Q. 関係スキーマ 学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) の各属性のドメインを定義した上で， ドメイン制約に違反するタプルの例を2，3挙げよ
34. 関係データモデル上の⼀貫性制約 一貫性制約 ドメイン制約 キー制約 参照制約 データ従属性 DB上のデータが 実世界を正しく反映すべく データが満たすべき規則 …
35. キーの概念（1/5） 超キー 関係Rにおける属性集合のうち，それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるもの 学籍番号 氏名 学部 年齢 S1 川澄 桜 経済学部 23 S2 山畑 滝子 文学部 20 S3 田辺 通 薬学部 18 S4 田辺 瑞穂 DS学部 19 S5 山畑 川名 DS学部 27 学⽣ 学籍番号が決まれば どの学⽣かは決まる 学籍番号は超キー その値が決まると表の⾏が1つに決まると思えばよい
36. キーの概念（2/5） 超キー 関係Rにおける属性集合のうち，それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるもの 学籍番号 氏名 学部 年齢 S1 川澄 桜 経済学部 23 S2 山畑 滝子 文学部 20 S3 田辺 通 薬学部 18 S4 田辺 瑞穂 DS学部 19 S5 山畑 川名 DS学部 27 学⽣ {学籍番号} {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} … 超キーは複数ある
37. キーの概念（3/5） キー（候補キー） 関係Rにおける属性集合のうち，それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるものうち，極⼩なもの 学籍番号 氏名 学部 年齢 S1 川澄 桜 経済学部 23 S2 山畑 滝子 文学部 20 S3 田辺 通 薬学部 18 S4 田辺 瑞穂 DS学部 19 S5 山畑 川名 DS学部 27 学⽣ 最も⼩さい部分集合 {学籍番号} {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} … キーは「学籍番号」
38. キーの概念（4/5） キー（候補キー） 関係Rにおける属性集合のうち，それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるものうち，極⼩なもの 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx 川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 AB5230xxx 山畑 川名 DS学部 学⽣ キーは複数あり得る キー = 学籍番号 Passport番号 or
39. Q6: 候補キー（R4年度ITパスポート試験 問65改題） Q. 関係スキーマ 従業員（従業員ID, ⽒名, 部⾨コード, ⽣年⽉⽇, 住所） において，候補キーは何か． なお，関係「従業員」は以下の制約条件をもつ •各従業員は重複のない従業員番号を1つだけもつ •同姓同名の従業員はいない（⼊社させない） •各部⾨は重複のない部⾨コードを1つだけもつ •1つの部⾨には複数名の従業員が所属する •1⼈の従業員が所属する部⾨は1つだけである A. {従業員ID} {⽒名}
40. キーの概念（5/5） 主キー 候補キーのうち，値として未定義や空値（NULL値）を取る 可能性がなく，かつ運⽤上最も都合のよいキーの1つ 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx 川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 AB5230xxx 山畑 川名 DS学部 学⽣ パスポートを 持っていない人もいそう 主キーは 「学籍番号」 キーはインスタンスではなくスキーマに定義されることに注意
41. Q7: 主キー Q. 以下の関係「会員管理」において 想定される主キーは何か？ 店舗コード 店舗名 会員番号 会員名 会員種別 S01 星ヶ丘 1 山畑 滝子 ゴールド S01 星ヶ丘 2 田辺 通 ゴールド S02 八事 1 北 千種 学生 S02 八事 2 川澄 桜 プラチナ S03 御器所 1 川澄 桜 一般 A. {店舗コード, 会員番号}
42. 超キー・候補キー・主キーの関係性 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx 川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 AB5230xxx 山畑 川名 DS学部 学⽣ 主キー 学籍番号 {学籍番号} パスポート 番号 超キー {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} (候補)キー …
43. キー制約 関係!(" 1 , ..., " $ )のある属性集合が主キーと指定された時， どのタプルも主キーに関する条件に従う必要がある 学生(Passport番号, 学⽣番号, ⽒名, 学部) 関係スキーマ 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx 川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 北 千種 DS学部 学⽣ 主キーには下線を引く Passport番号が空値 のためキー制約違反
44. 関係データモデル上の⼀貫性制約 一貫性制約 ドメイン制約 キー制約 参照制約 データ従属性 DB上のデータが 実世界を正しく反映すべく データが満たすべき規則 …
45. 外部キー 関係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は外部キー 履修. 科⽬'( ⊂ 科⽬. 科⽬'( 履修. 学⽣'( ⊂ 学⽣. 学⽣'(
46. 参照制約（もしくは参照整合性制約） 関係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 田辺通 可 履修 … 履修. 科⽬'( ⊂ 科⽬. 科⽬'( 履修. 学⽣'( ⊂ 学⽣. 学⽣'( の参照制約違反 の参照制約違反
47. Q8: 参照整合性制約 Q. 以下の関係スキーマをもつ5つの関係かなる データベースにおいて，定義すべき参照制約は？ 顧客( 顧客ID, ⽒名, 性別 ) 店舗( 店舗ID, 店舗, 住所 ) 商品( 商品ID, 商品名, 商品カテゴリID, 単価 ) 商品カテゴリ( 商品カテゴリID, カテゴリ名) 購買( 購買ID, 店舗ID, 顧客ID, 商品ID, 個数, 購買⽇)
48. 関係データモデル上の⼀貫性制約 一貫性制約 ドメイン制約 キー制約 参照制約 データ従属性 DB上のデータが 実世界を正しく反映すべく データが満たすべき規則 …
49. データ従属性 データ従属性 関数従属性 多値従属性 結合従属性 データ間に成⽴する制約
50. データ従属性 データ従属性 関数従属性 多値従属性 結合従属性 データ間に成⽴する制約 属性Xの値が決まると 属性Yの値も⼀意に決まる制約 例1 住所 郵便番号 都道府県 郵便番号 例2 関数従属性は関係データベースの正規化理論のコア
51. 回 実施日 トピック 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 期末試験 今後の予定 55
52. 次回授業で使⽤するもの Google Colaboratory - データベース問合わせ練習⽤ - ブラウザで動く - 要Googleアカウント PC