DB勉強会レポート_2019年01月08日(火)実施分

今回の解説範囲 平成30年度 春期 データベーススペシャリスト試験 午後Ⅰ 問3 設問1 (1) 設問1 (2) ⚓ 設問1 (1) 作業W2(追加制約設計)で”店舗”、”精算”の各テーブルにUNIQUE制約を設計する場合について、UNIQUE制約を定義する列の構成(列名または列名の組み合わせ)を、それぞれ一つ答えよ。 なお, UNIQUE制約がない場合、”なし”と答えよ。 設問文は上記の通りですが、参加者で「 UNIQUE制約がない場合, “なし”と答えよ。」を見落としていたメンバーが多かったです。 「設問をよく読め」、受験生の時に耳にタコが出来るくらい口うるさく言われたことですが、制限時間が厳しい中ではついつい忘れがち、しっかり意識したいところです。 “店舗”テーブル テーブル構造(抜粋) 店舗(施設ID, 店舗ID, 店舗名, 内線番号, …) * 実線の下線は主キーを表しています。 列の意味・制約(抜粋) 列名 意味・制約 店舗ID 施設内の各店舗(エステ, 理容, 食事処, 売店など)を識別する文字列 内線番号 各店舗に設置されている内線番号を施設内で識別する番号 解説 設問では「UNIQUE制約を定義する列の構成(列名または列名の組み合わせ)を、それぞれ一つ答えよ」とあります。 したがって、すでにUNIQUE制約の掛かっている複合主キーの施設IDと店舗IDは対象から外れます。 よって、考慮の対象となるのは店舗名と内線番号になります。 列の意味・制約に非常な重要な情報が書かれています。 内線番号列の意味・制約を見ると、「各店舗に設置されている内線番号を施設内で識別する番号」とあります。 「識別する」ためには、この列は一意である必要があります。 ただし、内線番号のみでUNIQUE制約をかけると良からぬことが起こります。 例えば、下記の行を挿入するとしましょう。 施設ID 店舗ID 店舗名 内線番号 001 111 レストランA 1111 001 111 カフェB 1111 002 111 マッサージ店C 1111 列の意味・制約を見ると、内線番号は「施設内」で識別する番号なので、同じ施設では重複せず、違う施設では重複可能であるべきです。 「レストランA」と同一の施設IDを持つ「カフェB」は、重複した内線番号を持っていて制約に反しているため、「レストランA」の挿入後には登録出来ません(ただし、「カフェB」が先に挿入されていた場合は「レストランA」が登録出来ません)。 「マッサージ店C」は、「レストランA」と同じ内線番号を持っていますが、店舗IDが異なるため、この問題では登録出来るべきです。しかし、内線番号のみでUNIQUE制約をかけると、例え施設IDが異なっても重複した内線番号は登録出来なくなってしまいます。 そこで、施設IDと内線番号の組み合わせでUNIQUE制約をかければ、施設IDと内線番号の両方が同一のものはテーブルに挿入出来なくなり、制約を満たします。 よって、UNIQUE制約を定義する列の構成は「施設IDと内線番号」の組み合わせとなります。 ところで、店舗名についてはいかがでしょう? 店舗名のみでUNIQUE制約をかけた場合、同一の店舗名を同じ施設内で持てないということになります。 現実的には同一施設内で同一店舗名の店舗が営業することは考えにくいですが、そういった制約は問題文で明示されていませんし、設問では各テーブルごとに「それぞれ一つ答えよ」となっているため、より明らかに問題文内で明示されていて議論の余地のない(施設ID、内線番号)の組が回答として適切な解答となります。 よって、店舗名はUNIQUE制約の対象外になります。 “精算”テーブル テーブル構造(抜粋) 精算(利用年月日, 施設ID, 券番号, 精算時刻, 利用額合計, 会員ID, ポイント消費数) * 下線のうち、実線は主キー、破線は外部キーを表しています。 列の意味・制約(抜粋) 列名 意味・制約 店舗ID 施設内の各店舗(エステ, 理容, 食事処, 売店など)を識別する文字列 券番号 入館券を発行する都度, 日ごと施設ごとに付与される1から始まる連番 会員ID 会員を識別する文字列(会員カードの裏面にバーコードで刻印されている) 解説 すでにUNIQUE制約の掛かっている複合主キーの利用年月日、施設ID、券番号は対象から外れます。 よって、考慮の対象となるのは精算時刻、利用額合計、会員ID、ポイント消費数となります。 それぞれの列について、本文や列の意味・制約から関連する情報を拾っていきましょう。 まずは精算時刻と利用額合計、それからポイント消費数。 これらの列に関しては、UNIQUE制約に関わる情報は見当たりません。 よって、値の重複は認められると考えられます。 続いて、会員ID。 この列に関して、本文ll.2 – 3 に重要な情報があります。 全施設で利用できる会員カードを利用する客に発行し、(以下省略) そう、全ての客が会員である必要はなく、あくまで「希望する」客に付与されるオプションが会員IDとなりますので、この列にはNULLが入り得ます。 さて、会員IDの値が存在する場合は一意でなければなりませんが、NULLは重複しても良いのでしょうか? 答えは「重複して良い」です。 厳密に言えば、NULLは重複とは見なされません。 Cockroach LABSでは以下のように説明されています。 You can insert NULL values into columns with the UNIQUE constraint because NULL is the absence of a value, so it is never equal to other NULL values and not considered a duplicate value. (UNIQUE制約の下でもNULL値を挿入することが可能である。何故なら、NULLは値が存在しないことを表し、決して他のNULLと同一であることはなく、重複値とも見なされないからである。) Unique Constraint_Cockroach LABSより抜粋 さて、ここまでの説明を具体的な例を挙げて見ていきましょう。 例えば、”精算”テーブルに下記のような行を追加すると仮定します。 利用年月日 施設ID 券番号 精算時刻 利用額合計 会員ID ポイント消費数 20190123 123 4567 17:00 3500 NULL 800 20190123 123 1234 17:00 3500 500 800 20190123 345 4567 … Continue reading DB勉強会レポート_2019年01月08日(火)実施分