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

関係データモデル⼭本祐輔名古屋市⽴⼤学データサイエンス学部 [email protected] 第3回データベース 2024年4月29日

講義ノート https://bit.ly/3xqTSds

関係データモデルのデータ構造 1 Structure of Relational Data Model

関係データベース様々な事柄あらゆる事象・状態を表で表現して格納・処理項目A B C D xxx xxx xx
xx xxx xx xx xx xxx xx xx xx 関係データベースシステム（DBS）モデリング格納表（関係データ）データの正しさを管理しやすくするために関係データモデルで抽象化

関係データモデルあるゆるデータを表（=関係）として表現するモデル - 1970年にEdgar Coddが提唱 - 背後に強⼒な数学的基盤をもつ

関係データモデルの構造（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 商品関係名タプル

関係データモデルの構造（2/2）商品ID 名称単価登録日 P1 はーいお茶 130 2020/07/15 P2
午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P1000 きのこの里 200 2023/01/08 商品属性属性・表の各⾒出しに対応・タプルを構成する要素の種別名（注⽬するデータの特徴名）・各属性に対応するタプルの値を属性値と呼ぶ属性「登録⽇」の属性値

関係データモデルあるゆるデータを表（=関係）として表現するモデル - 1970年にEdgar Coddが提唱 - 背後に強⼒な数学的基盤をもつ関係データの「関係」って何？

直積集合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

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

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.

数学上の「関係」集合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項関係．なぜなら ! ⊂ #×%. このとき

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

Q2: 関係 Q. S$%&' = Python, R, C(( , S)*)+$%,-./
= ⼈気, 不⼈気 , S0-12-3+$./ = 難, 普通, 易 S$%&' , !4546789:;< , S0-12-3+$./上の関係の例を挙げよ． { (Python, ⼈気,易),(R, 不⼈気,普通), とする． (C++, ⼈気, 難) } ⊂ S!"#$ ×*%&%'()*+,- ×S./01/23!45 関係は集合であるため集合を⽰す記号が必要 A.

関係データモデルにおける「関係」関係関係スキーマインスタンス = +

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

関係スキーマ関係関係スキーマインスタンス = + 関係の名前と関係がもつ属性，⼀貫性制約に関する情報 ID 名称単価
登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 商品商品(ID, 名称, 単価, 登録⽇) 関係スキーマ

Q3: 関係スキーマ Q. 関係スキーマ学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) に従う表データの例を作成せよ

Q3: 関係スキーマ Q. 関係スキーマ学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) に従う表データの例を作成せよ
学籍番号氏名学部年齢出身都道府県 S1 川澄桜医学部 23 三重 S2 山畑滝子文学部 20 愛知 S3 田辺通薬学部 22 岐阜 S4 田辺瑞穂 DS学部 19 北海道学⽣ A.

属性のドメイン - 関係スキーマに記された属性の値が取り得る値の集合 - 属性ごとにドメインを定義する - 属性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, …}

単価登録日 P1 はーいお茶 130 2020/07/15 P2 午前の紅茶 130 2020/09/25 P3 健康麦茶 150 2021/02/16 P10 きのこの里 200 2023/01/08 商品 678 名称 = ! | ! ∈ ⽂字列集合＝ {あ, い, う, え, お, …, ああ, あい, …, はーいお茶, …}

単価登録日 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 登録⽇ = ! | ! ∈ 年⽉⽇集合ドメインが属性の取り得る値を規定

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

コラム: データの型 • データはその種類を表す「型」を持つ • 関数や演算の引数には「型」が設定されている A + B =
add(A, B) 演算⼦の例⾜し算の引数は数値型を想定している 3.141592 + 1 = 4.1415926535 “3.141592” + 1 = 計算不能型の効⽤の例型があるから計算の正しさを保証できる

Q4: ドメイン Q. 関係スキーマ学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) の各属性について，そのドメインを定義せよ．

Q4: ドメイン Q. 関係スキーマ学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) の各属性について，そのドメインを定義せよ．
$%& 学籍番号 = ( | ( ∈ !から始まる⽂字列集合 $%& ⽒名 = ( | ( ∈ ⽂字列集合 $%& 学部 = ⼯学部, 医学部, 薬学部, ⽂学部, $!学部 $%& 年齢 = ( | ( ∈ ℕ, ( > 17 $%& 都道府県 = 北海道, ⻘森, … , 沖縄 A.

インスタンス（1/2）関係関係スキーマインスタンス = + • 関係スキーマ< A! ,
… , A" における 678 @! , … , 678(@# )の直積の部分集合 • 表におけるタプルの集合に対応 ! ⊂ #$% &! × ⋯×#$%(&" ) これが関係におけるインスタンス

インスタンスの例 $%& 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 登録⽇ = ! | ! ∈ 年⽉⽇集合

インスタンスの例 $%& 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)} ドメインの定義上，インスタンスとしては変なものもあり得る

重要：集合の要素は重複が許されない（1/2） *C = {1, 2, 3, 4, 4, 5} =
{1, 2, 3, 4, 5} *D = {⼭本, ⾠⼰, 能勢, ⼭本} = {⼭本, ⾠⼰, 能勢} *E = { 三重, 津 , 愛知, 名古屋 , (三重, 津)} = { 三重, 津 , 愛知, 名古屋 } 集合内の重複する要素は無視される集合内の要素の順序も無視される

重要：集合の要素は重複が許されない（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 商品関係データモデルに従うとするとタプルの重複はダメ

⾮正規関係と第1正規形（1/2）関係モデルではドメイン要素として原子値を想定それ以上⼩さな構成要素に分解すると意味が失われる値⽂字列実数ｚ原⼦値真偽値(true/false) ⽇付
… 整数タプル集合原⼦値ではない値関係 … リスト

⾮正規形と第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 … 「購買品⽬」の値として集合を許してしまっている正規化正規化された関係はデータ構造が簡潔になり扱いやすい

データの一貫性制約 2 Integrity Constraints

関係データモデル上の⼀貫性制約一貫性制約ドメイン制約キー制約参照制約データ従属性 DB上のデータが実世界を正しく反映すべくデータが満たすべき規則 …
⼀貫性制約を考慮して関係スキーマの定義や関係の分解を⾏う

ドメイン制約関係:(; 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 ∈ ⽂字列集合ドメイン制約に違反商品

Q5: ドメイン制約 Q. 関係スキーマ学⽣(学籍番号, ⽒名, 学部, 年齢, 出⾝都道府県) の各属性のドメインを定義した上で，
ドメイン制約に違反するタプルの例を2，3挙げよ

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

ドメイン制約に違反するタプルの例を2，3挙げよ

ドメイン制約に違反するタプルの例を2，3挙げよ学籍番号氏名学部年齢出身都道府県 S1 川澄桜経済学部 23 三重 S2 山畑滝子文学部 20 アメリカ S3 田辺通薬学部 17 岐阜 K4 田辺瑞穂 DS学部 19 北海道 S5 山畑川名 DS学部，薬学部 27 愛知学⽣ A.

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

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

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

キーの概念（4/5）キー（候補キー）関係Rにおける属性集合のうち，それらの属性値が決まれば関係Rのタプルを⼀意に特定できるものうち，極⼩なもの学籍番号 Passport番号氏名学部 S1 TR1234xxx
川澄桜経済学部 S2 XS4321xxx 山畑滝子文学部 S3 KK7743xxx 田辺通薬学部 S4 VU9802xxx 田辺瑞穂 DS学部 S5 AB5230xxx 山畑川名 DS学部学⽣キーは複数あり得るキー = 学籍番号 Passport番号 or

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

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

キーの概念（5/5）主キー候補キーのうち，値として未定義や空値（NULL値）を取る可能性がなく，かつ運⽤上最も都合のよいキーの1つ学籍番号 Passport番号氏名学部 S1 TR1234xxx
川澄桜経済学部 S2 XS4321xxx 山畑滝子文学部 S3 KK7743xxx 田辺通薬学部 S4 VU9802xxx 田辺瑞穂 DS学部 S5 AB5230xxx 山畑川名 DS学部学⽣パスポートを持っていない人もいそう主キーは「学籍番号」キーはインスタンスではなくスキーマに定義されることに注意

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

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

超キー・候補キー・主キーの関係性学籍番号 Passport番号氏名学部 S1 TR1234xxx 川澄桜経済学部
S2 XS4321xxx 山畑滝子文学部 S3 KK7743xxx 田辺通薬学部 S4 VU9802xxx 田辺瑞穂 DS学部 S5 AB5230xxx 山畑川名 DS学部学⽣主キー学籍番号 {学籍番号} パスポート番号超キー {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} (候補)キー …

キー制約関係!(" 1 , ..., " $ )のある属性集合が主キーと指定された時，どのタプルも主キーに関する条件に従う必要がある学生(Passport番号,
学⽣番号, ⽒名, 学部) 関係スキーマ学籍番号 Passport番号氏名学部 S1 TR1234xxx 川澄桜経済学部 S2 XS4321xxx 山畑滝子文学部 S3 KK7743xxx 田辺通薬学部 S4 VU9802xxx 田辺瑞穂 DS学部 S5 北千種 DS学部学⽣主キーには下線を引く Passport番号が空値のためキー制約違反

外部キー関係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は外部キー履修. 科⽬'( ⊂ 科⽬. 科⽬'( 履修. 学⽣'( ⊂ 学⽣. 学⽣'(

参照制約（もしくは参照整合性制約）関係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 田辺通可履修 … 履修. 科⽬'( ⊂ 科⽬. 科⽬'( 履修. 学⽣'( ⊂ 学⽣. 学⽣'( の参照制約違反の参照制約違反

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

Q8: 参照整合性制約 A. 購買. 顧客ID ⊂ 顧客.顧客ID 購買. 店舗ID ⊂
店舗.店舗ID 購買. 商品ID ⊂ 購買.商品ID 商品. 商品カテゴリID ⊂ 商品カテゴリ.商品カテゴリID

データ従属性データ従属性関数従属性多値従属性結合従属性データ間に成⽴する制約

データ従属性データ従属性関数従属性多値従属性結合従属性データ間に成⽴する制約属性Xの値が決まると属性Yの値も⼀意に決まる制約例1 住所
郵便番号都道府県郵便番号例2 関数従属性は関係データベースの正規化理論のコア

回実施日トピック 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

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

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

More Decks by Y. Yamamoto

Other Decks in Technology

Featured

Transcript