基本情報技術者 2017年 春期 午後 問03
住民からの問合せに回答するためのデータベースに関する次の記述を読んで、設問1〜3に答えよ。
W市役所の生活環境課では、職員は住民からのごみの出し方に関する問合せがあったとき、対象物（ごみ）を検索条件としてルール表を検索し、出し方のルールを回答する業務を行っている。
問合せごとに一意な受付Noを割り当て、受付日、回答日、回答したルールの区分ID及び対象物IDを入力したレコードを問合せ記録表に登録する。問合せがあった対象物が対象物表になかった場合、回答日、区分ID及び対象物IDはNULLにして、メモ欄に当該対象物をテキストで入力したレコードを登録する。後日、問合せ記録表から、回答日、区分ID及び対象物IDがNULLのレコードを選択し、メモ欄に入力されている当該対象物の分別区分を決定し、一意な対象物IDを割り当て、対象物表に新たな対象物のレコードを登録する。ルール表に新たなルールのレコードを登録して、問合せ記録表の選択したレコードに、追加したルールの区分IDと対象物IDを設定する。
データベースの表構成とデータ格納例は、図1のとおりである。下線付きの項目は主キーを表す。
業務を行っている過程で幾つかの課題が明らかになった。これらのうちから対策すべき課題を選び、それを実現するために、データベースの表構成の見直し案を作成した。その結果を、表1に示す。
設問1：
問題文を見る ルールIDの項目を追加する理由として適切な答えを、解答群の中から選べ。
解答群
ア：同じ区分IDの同じ対象物IDに対する出し方のルールを複数件登録できるようにする。
イ：同じ区分IDの異なる対象物IDに対する出し方のルールを登録できるようにする。
ウ：異なる区分IDの同じ対象物IDに対する出し方のルールを登録できるようにする。
模範解答
ア
解説
解答の導き方
表1の課題には、「同じ対象物でも、大きさなどによって出し方を分ける必要が出てきた。」とあります。つまり、同じ対象物について、サイズなどの条件に応じた複数のルールを登録したいということです。
図1のルール表では、区分IDと対象物IDの組合せが主キー（表の各行を一意に識別する項目）になっています。複数の項目を組み合わせて行を一意に識別する主キーを、複合主キーといいます。この場合、区分IDと対象物IDが両方とも同じレコードは、二つ登録できません。
そこで表1では、ルールIDを主キーとして追加し、区分IDと対象物IDは主キーではなくす案になっています。これにより、区分IDと対象物IDの組合せが同じでも、別のルールIDを付ければ複数のルールを登録できます。また、問合せ記録表にルールIDを記録すれば、どのルールで回答したかも特定できます。
したがって、ルールIDを追加する理由は、同じ区分IDと同じ対象物IDに対するルールを複数件登録できるようにすることです。
イとウは、区分IDと対象物IDのどちらかが異なります。その場合は主キーの組合せも異なるため、元の表構成でも登録できます。
誤りやすいポイント
- 「同じ対象物」を見て、対象物IDだけが同じならよいと考えないようにします。問われているのは、区分IDと対象物IDの両方が同じ場合です。
- ルールIDを追加するだけでなく、区分IDと対象物IDを主キーではなくす点も確認します。元の組合せを主キーのままにすると、同じ組合せのルールを複数登録できません。
- イとウのように区分IDまたは対象物IDが異なる場合は、主キーの組合せも異なります。そのため、ルールIDを追加する理由にはなりません。
FAQ
Q: サイズが異なっても、区分IDと対象物IDは同じことがあるのですか？
A: あります。大きさによって出し方のルールを分けても、分別区分と対象物が同じなら、区分IDと対象物IDの組合せは同じです。その複数のルールを区別するために、ルールIDを使います。
A: あります。大きさによって出し方のルールを分けても、分別区分と対象物が同じなら、区分IDと対象物IDの組合せは同じです。その複数のルールを区別するために、ルールIDを使います。
Q: 図2のDDLにある UNIQUE (区分ID, 対象物ID) は、どのような制約ですか？
A: 区分IDと対象物IDの組合せが重複しないようにする制約です。そのDDLを見直し後の表にそのまま適用する場合、同じ組合せのルールを複数登録する目的とは両立しません。設問の選択肢は、表1に示された見直し案の意図に基づいて判断します。
A: 区分IDと対象物IDの組合せが重複しないようにする制約です。そのDDLを見直し後の表にそのまま適用する場合、同じ組合せのルールを複数登録する目的とは両立しません。設問の選択肢は、表1に示された見直し案の意図に基づいて判断します。
Q: ルールIDを問合せ記録表にも追加するのはなぜですか？
A: 同じ区分IDと対象物IDに複数のルールが登録されると、その二つのIDだけでは、どのルールで回答したかを特定できないためです。
A: 同じ区分IDと対象物IDに複数のルールが登録されると、その二つのIDだけでは、どのルールで回答したかを特定できないためです。
関連キーワード: 主キー、複合主キー、一意性制約、外部キー
設問2：
問題文を見る 次の記述中のに入れる適切な答えを、解答群の中から選べ。
ルール表のレコードを特定するために、問合せ記録表にルールIDの項目を追加するとき、ルールIDを外部キーとしたルール表に対するa制約を設定する。追加するルールIDの項目には、b。
aに関する解答群
ア：非NULL
イ：NULL
ウ：UNIQUE
エ：参照
オ：検査
カ：主キー
bに関する解答群
ア：非NULL制約は適用できない
イ：UNIQUE制約を適用する
ウ：更新操作を行うことはできない
エ：主キー制約を適用する
模範解答
a：エ、b：ア
解説
解答の導き方
この設問では、問合せ記録表に追加する「ルールID」について、ルール表との関係をどう設定するか、また値を必ず入れる必要があるかを考えます。
-
aには、ルール表に対する「参照」制約を設定します。外部キー（別の表の行を指す項目）を使うと、問合せ記録表のルールIDから、ルール表の該当するルールを特定できます。ただし、問合せ記録表に入力されたルールIDが、実際にルール表に存在することも保証する必要があります。そのために設定するのが参照制約です。参照制約があれば、ルール表に存在しないルールIDを問合せ記録表に登録することを防げます。
-
bには、非NULL制約を適用できません。NULL（値が設定されていないことを表す値）が必要になる場面があるからです。本文には、対象物表になかったものは「回答日、区分ID及び対象物IDはNULLにして」問合せ記録表に登録し、後日、分別区分やルールを決めて記録を更新するとあります。この時点では、まだ対応するルールが登録されていません。したがって、新たに追加するルールIDも設定できず、NULLにしておく必要があります。後日ルールを登録した後で、そのルールIDを問合せ記録表に設定します。よって、ルールIDには参照制約を設定しますが、値を必須にする非NULL制約は適用できません。
誤りやすいポイント
-
外部キーを設定すれば、値も必ず入力されると思うこと
外部キーに参照制約を設定しても、非NULL制約を設定しなければNULLを入れられます。NULLでない値には、参照先のルールが存在する必要があります。 -
ルールIDにUNIQUE制約を設定すること
UNIQUE制約（同じ値の重複を禁止する制約）を問合せ記録表のルールIDに設定すると、同じルールを使った別の問合せを記録できなくなります。ルールIDは複数の問合せ記録で使われる可能性があります。 -
ルールIDを問合せ記録表の主キーだと考えること
主キー（表の中の行を一意に識別する項目）は、問合せ記録表では受付Noです。ルールIDは、問合せで使ったルールを示す項目です。
FAQ
Q: NULLを認めると、参照制約が働かなくなるのですか？
A: ルールIDがNULLの間は、対応するルールがまだ決まっていない状態として扱います。NULLでない値が設定された場合は、その値がルール表に存在する必要があります。
A: ルールIDがNULLの間は、対応するルールがまだ決まっていない状態として扱います。NULLでない値が設定された場合は、その値がルール表に存在する必要があります。
Q: 参照制約があれば、問合せ記録表のルールIDは必ずルール表にある値になりますか？
A: ルールIDが設定されている場合は、参照先のルール表に同じIDが必要です。ただし、非NULL制約も設定しない限り、NULLは許されます。
A: ルールIDが設定されている場合は、参照先のルール表に同じIDが必要です。ただし、非NULL制約も設定しない限り、NULLは許されます。
Q: ルールIDを問合せ記録表の主キーにできないのですか？
A: 1つのルールが複数の問合せで使われる可能性があるため、ルールIDだけでは問合せ記録の行を一意に識別できません。
A: 1つのルールが複数の問合せで使われる可能性があるため、ルールIDだけでは問合せ記録の行を一意に識別できません。
関連キーワード: 外部キー、参照制約、主キー、NULL、非NULL制約、UNIQUE制約
設問3：
問題文を見る ルール表作成用DDLの見直しについて、次の記述中のに入れる適切な答えを、解答群の中から選べ。
図2に示す、ルールIDの項目と登録状態の項目追加後のルール表作成用DDLのレビュー時に、cは削除すべきとの指摘を受けた。また、登録状態の項目に対する制約が不足しているとの指摘もあり、dを追加することになった。
cに関する解答群
ア：区分IDの項目及び対象物IDの項目のUNIQUE制約
イ：対象物IDの項目の非NULL制約
ウ：対象物IDの項目の参照制約
エ：ルールIDの項目の主キー制約
dに関する解答群
ア：CHECK(登録状態 IN('未公開','公開'))
イ：CHECK(登録状態 IS NULL)
ウ：UNIQUE('未公開','公開')
エ：WHERE 登録状態 IN('未公開','公開')
模範解答
c：ア、d：ア
解説
解答の導き方
この設問では、ルール表に追加された項目に合わせて、不要になる制約と、新たに必要な制約を考えます。制約とは、表に登録できるデータに設ける条件です。
まず、問題文には「同じ対象物でも、大きさなどによって出し方を分ける必要が出てきた」とあります。つまり、区分IDと対象物IDが同じでも、別々のルールを登録する場合があります。
図2のDDL（Data Definition Language：データベースの表などの構造を定義する言語）には、区分IDと対象物IDの組合せが重複しないようにする UNIQUE 制約があります。UNIQUE 制約は、指定した項目の値や組合せが同じ行を複数登録できないようにする制約です。この制約が残っていると、同じ区分IDと対象物IDに対して、異なるルールを登録できません。
そのため、削除すべきなのは「区分IDの項目及び対象物IDの項目のUNIQUE制約」です。ルールIDの項目を主キー（表の中で行を一つに特定する項目）にした後も、この UNIQUE 制約を残すと、同じ組合せのルールを複数登録できないためです。
一方、ルールIDの主キー制約は削除しません。ルールごとに異なるIDを割り当て、各ルールを区別するために必要です。また、対象物IDの非NULL制約（値をNULL、つまり未設定にできないようにする制約）や、対象物表にあるIDを参照する制約も、ルール表のデータを正しく保つために必要です。
次に、登録状態の項目に対する制約を考えます。問題文では、登録状態の値は「未公開」または「公開」のいずれかとされています。図2のDDLにある NOT NULL制約は、値が未設定になることを防ぎますが、「確認中」など、指定された二つ以外の文字が登録されることまでは防げません。
そこで、登録状態の値が二つのどちらかに限られるよう、CHECK 制約（値が指定された条件を満たすかを確認する制約）を追加します。解答群では、CHECK(登録状態 IN('未公開','公開')) がその条件を表しています。
誤りやすいポイント
- ルールIDを主キーにしたからといって、ルールIDの主キー制約を削除するわけではありません。ルールを一つずつ区別するため、主キーは必要です。
- UNIQUE 制約が対象にしているのは、区分IDと対象物IDの組合せです。この組合せが同じルールを複数登録できるようにするため、該当する制約を削除します。
- NOT NULL 制約と CHECK 制約は役割が異なります。NOT NULL は値が未設定になるのを防ぎ、CHECK は値を「未公開」と「公開」に限定します。
- WHERE は検索対象の行を絞り込むときに使う句であり、登録状態に入力できる値を制限する制約ではありません。
FAQ
Q: ルールIDが異なれば、同じ区分IDと対象物IDの組合せのルールを複数登録できますか？
A: はい。区分IDと対象物IDの組合せに対する UNIQUE 制約を削除すれば、ルールIDが異なる別のルールとして登録できます。
A: はい。区分IDと対象物IDの組合せに対する UNIQUE 制約を削除すれば、ルールIDが異なる別のルールとして登録できます。
Q: NOT NULL 制約があるのに、なぜ CHECK 制約も必要なのですか？
A: NOT NULL 制約だけでは、値が入力されていることしか確認できません。「未公開」「公開」以外の値も入力できてしまうため、CHECK 制約で値の種類を限定します。
A: NOT NULL 制約だけでは、値が入力されていることしか確認できません。「未公開」「公開」以外の値も入力できてしまうため、CHECK 制約で値の種類を限定します。
Q: 「未公開」と「公開」のどちらも登録できるようにする条件は、どの部分ですか？
A: IN の後にある「未公開」と「公開」です。登録状態がこのどちらかに当てはまる場合だけ、条件を満たします。
A: IN の後にある「未公開」と「公開」です。登録状態がこのどちらかに当てはまる場合だけ、条件を満たします。
関連キーワード: 主キー、UNIQUE制約、外部キー制約、NOT NULL制約、CHECK制約、DDL
