今回の解説範囲
平成30年度 春期 データベーススペシャリスト試験 午後Ⅰ 問3
⚓ 設問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 | 17:00 | 3500 | NULL | 800 |
| 20190124 | 123 | 4567 | 17:00 | 3500 | NULL | 800 |
一行目を「Aさん」、二行目を「Bさん」、三行目を「Cさん」、四行目を「Dさん」の精算だとします。
複合主キーの利用年月日、施設ID、券番号は一意でなければならないので、この三つの組み合わせで値は重複しません。
ここで券番号を見てみましょう。
「Aさん」と「Cさん」、「Dさん」で同じ券番号が割り振られていますね。
これは問題ないのでしょうか？
列の意味・制約（抜粋）の券番号の説明を見ると、「日ごと施設ごとに付与される1から始まる連番」とあります。
つまり、利用年月日または施設IDが異なっていれば重複しても良いのです。
したがって、これは問題ありません。
「Aさん」、「Bさん」、「Cさん」、「Dさん」で精算時刻と利用額合計、ポイント消費数が重複していますが、これらについては本文中でUNIQUE制約について言及がないので問題ありません。
最後に、会員IDです。
「Aさん」と「Cさん」、「Dさん」でNULLが複数ありますが、先述の通り、NULL値は重複とは見なされないため、問題ありません。
考慮の対象となる精算時刻、利用額合計、会員ID、ポイント消費数はUNIQUE制約をかけません。
よって、"精算"テーブルではUNIQUE制約なしという結論に至ります。
⚓ 設問1 (2)
作業W2（追加制約設計）について、図2中の a ~ d に入る適切な述語を一つずつ答えよ。
SQL
CHECK((年齢区分 = '1' AND [a]) OR ([b] AND [c]) OR (年齢区分 = '3' AND [d]))
解説
最初に設問分を確認しましょう。
「述語」という言葉がありますが、ここではどういう意味なのでしょうか？
英訳すると「predicate」ですが、この用語の定義を英語版WikipediaのWhere (SQL)ページにあるPredicatesの説明が手に入る限り適切な情報なので以下に引用します。
Simple predicates use one of the operators
=,<>,>,>=,<,<=,IN,BETWEEN,LIKE,IS NULLorIS NOT NULL. Predicates can be enclosed in parentheses if desired. The keywords AND and OR can be used to combine two predicates into a new one. If multiple combinations are applied, parentheses can be used to group combinations to indicate the order of evaluation. Without parentheses, the AND operator has a stronger binding than OR.
(単純な述語は演算子の=,<>,>,>=,<,<=,IN, BETWEEN,LIKE,IS NULLorIS NOT NULLのうちの一つを扱う。必要に応じて括弧で括っても良い。ANDやORを使って、二つの述語を一つに纏めて扱うことが出来る。複数の述語の組み合わせを使う場合、述語は評価の順を明示するために（括弧で括って）グループ化することが出来る。括弧を用いない場合、ANDはORよりも高い優先度を持つ。)
Wikipedia Where (SQL) 3.Predicatesより引用
この述語ですが、式（条件式）と混同しやすいところだと思いますが、morimorihogeより記事レビューを受けた際に補足説明を賜ったのでご紹介します（本人了承済み）。
式は「評価して値を返すもの」なので、
A = 10-> 1つの述語で構成される条件式
A = 10 AND B = 20-> 2つの述語をANDで結びつけた条件式という点で使い分けがある・・・はず。
※言語によって用語違うかもですが、一般にはそうじゃないかな「式」というと複数の述語が組み合わさっていても式だけど「述語」というと一つのopで構成されたものになるんじゃないかなと思います。
なお、SQLにおいて「statement（文）」はSELECT文とかUPDATE文のような1つのクエリになるようなものを指すため「条件文」という言葉は厳密には間違いです。
u-ichi が貼ってくれた索引を見るとわかりますが 「clause（句）」になるので、厳密には「条件句」が正しい（と理解している
続いて、CHECKについて確認しておきましょう。
CHECKはTRUE/FALSEを返すデータ定義言語（DDL：Data Definition Language）です。
w3schools.comの説明によると以下の定義がなされています。
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
(CHECK制約は列に入ることが出来る値の範囲を制限するために使われる。単一の列に対してCHECK制約をかける場合は、その列には特定の値のみを許容する。テーブルに対してCHECK制約をかける場合は、行の他の列にある値に基づいて、特定の列の値を制限する。)
SQL CHECK Constraint_w3schools.comより引用
次に、年齢区分列の意味・制約を確認します。
| 列名 | 意味・制約 |
|---|---|
年齢区分 |
子供（12歳未満）：'1' 大人（12歳以上60歳未満）：'2' シニア（60歳以上）：'3' |
SQLと照らし合わせると、
-年齢区分が「1」かつ年齢が「12歳未満」の場合は「子供」に分類
-年齢区分が「2」かつ年齢が「12歳以上60歳未満」の場合は「大人」に分類
-年齢区分が「3」かつ年齢が「60歳以上」の場合は「シニア」に分類
という制約をかけたいのだと予想出来ます。
SQLを見てみると、ANDの前には年齢区分の制約が入り、ANDの後には年齢の制約が入ることが分かります。
上記の意味・制約に則って穴埋めをすると、下記の通りです。
a. 年齢 < 12
b. 年齢区分 = '2' #文字列の'2'であることに注意
d. 年齢 >= 60（「≧」はシングルバイトでは入力不可でSQLでは構文エラーになるので×）
さて、cに入る条件ですが、単純に年齢 >= 12 AND 年齢 < 60と入れてしまうとTRUE/FALSEを返す述語が二つになってしまい、設問文の指示に反してしまいます。
一つの述語で「12歳以上60歳未満」を表すにはどうすれば良いでしょうか。
この場合は年齢 BETWEEN 値1 AND 値2の構文が使えます。
ただ、注意しなければいけないのは、この構文は値1と値2も範囲の中に含まれるという点です。
単純にBETWEEN 12 AND 60としてしまうと年齢 >= 12 AND 年齢 <= 60と同じになってしまいます。
「12歳以上59歳以下」と読み替え、c. BETWEEN 12 AND 59とすれば正解です。
ちなみに、年齢区分 = '1' OR 年齢区分 = '2'の場合は年齢 IN('1','2')と書き換えることが出来ます。
今回の設問の解答
- 設問1
- (1)
- 店舗テーブル：
施設IDと内線番号 - 精算テーブル；
なし
- 店舗テーブル：
- (2)
- a.
年齢 < 12 - b.
年齢区分 = '2' - c.
BETWEEN 12 AND 59 - d.
年齢 <= 60
- a.
- (1)
参考ページ
- 平成30年度 春期 データベーススペシャリスト試験 - IPA情報処理推進機構 - 最終アクセス日: 2019年01月09日
- SQL CHECK Constraint_w3schools.com - 最終アクセス日: 2019年01月09日
- Predicates_Where (SQL)_Wikipedia - 最終アクセス日: 2019年01月17日
- Unique Constraint_Cockroach LABS - 最終アクセス日: 2019年01月23日
次回解説予定範囲
平成30年度 春期 データベーススペシャリスト試験 午後Ⅰ 問3 設問1 (3)