データベーススペシャリスト 2021年 午後1 問03

解答の論理構成

1. 前提確認
   • “行数” 1,600,000、沿線の種類 400 → 1沿線当たり行数＝$1,600,000/400=4,000$。
   • 各設備列は値 Y と N の2種類で一様 → $P(Y)=1/2$。
   • 列どうしは「互いに独立」。
2. （イ）SQL1の見積行数
   • 条件確率：沿線 = 指定線 $(1/400)$ かつYかつY $(1/2\times 1/2)$。
   • $P=\frac{1}{400}\times \frac{1}{4}=\frac{1}{1600}$。
   • 行数＝$1,600,000\times \frac{1}{1600}=1,000$。
3. （ロ）SQL2の見積行数
   • Y OR Y: $P=\frac{1}{2}+\frac{1}{2}-\frac{1}{4}=\frac{3}{4}$。
   • 全体確率：$\frac{1}{400}\times \frac{3}{4}=\frac{3}{1600}$。
   • 行数＝$1,600,000\times \frac{3}{1600}=3,000$。
4. （ハ）〜（ホ）SQL3の集計句
   • 分子は「条件を満たす行数」→ COUNT(*)。
   • 分母は WITH TEMP(TOTAL) で求めた全行数列→ TOTAL。
   • GROUP BY には 沿線 と TOTAL の両方を指定する必要がある（TOTAL を選択句で使用するため）。

誤りやすいポイント

• OR 条件を単純に $1/2+1/2=1$ と誤算しがち（重複部分の控除を忘れる）。
• GROUP BY 沿線 のみとすると TOTAL が非集約列扱いでエラーになる。
• 「一様分布＝半数がY」を忘れ、実務経験則で偏った分布を想定してしまう。

FAQ

解答の論理構成

1. 現行方式の課題
   • 【問題文】「(5) 設備に流行があるので、テーブルの定義を変更し、記録する人気の設備を毎年入れ替える処理」を実施しており、列の増減作業が発生。
2. 案Bの構造
   • 図2では“設備”と“物件設備”を追加。設備は行で増やすため列変更不要。
3. 結果
   • 行追加だけで設備の増減を吸収でき、列を削除・追加する「物件設備の入替処理」が不要となる。
   • よって長所は「物件設備の入替処理が不要である」と言える。

誤りやすいポイント

• 「全設備の有無と個数が分かる」を答えると列幅問題の改善という別の観点になるため、入替処理の不要化を明示しないと減点されやすいです。
• 「正規化できる」「可変長に対応」など抽象表現のみで本文中の用語「物件設備の入替処理」を使わないと失点します。
• 25字以内という制約を意識しすぎて主語やキーワードを削り過ぎると、具体性不足で評価が下がります。

FAQ

解答の論理構成

1. 列順・値の決定
   • 【問題文】「INSERT INTO 物件設備（物件コード、設備コード、設置済個数）」
     ⇒ 3列目に固定値「1」を入れる指示は「(4)…設置済個数列に1を設定し」と明示。
   • SQL4で設備コードが登録されている例：
     sql INSERT INTO 設備 VALUES ('A1', 'エアコン') INSERT INTO 設備 VALUES ('A2', 'オートロック')
     ⇒ 設備コードは 'A1'、'A2' しかあり得ない。
   • 以上から (a) と (d) は「物件コード、'A1',1」と「物件コード、'A2',1」。
2. 抽出条件の決定
   • 【問題文】「…エアコン又はオートロックがある行を登録するSQL文の例を、表4のSQL5に示す。」
     ⇒ WHERE 句は各列の値が 'Y' であることを判定するだけ。
   • 設備列名は エアコン と オートロック で固定なので
     (b) は「エアコン='Y'」、(e) は「オートロック='Y'」。
3. 2つの SELECT を連結する演算子
   • 行をまとめてインサートするため、重複検査を省きパフォーマンス重視の
     UNION ALL が最適（UNION だと重複チェックが走る）。
   • したがって (c) は「UNION ALL」。

誤りやすいポイント

• UNION と UNION ALL の混同
  行数が巨大（【問題文】の表2で「1,600,000」行）なので重複チェックがかかるUNOINは性能劣化の原因。
• 設備コードの記号ミス
  SQL4の 'A1'、'A2' を 'a1'、'A01' などと書き換えると参照整合性違反。
• 列順の取り違え
  INSERT 対象列を明示していても VALUES／SELECT 側が「設備コード、物件コード…」の順になると挿入失敗。

FAQ

解答の導き方

• （あ）は「物件設備 テーブルの物件コード列に現れる異なる物件コードの個数」、
• （い）は「物件設備 テーブルの設備コード列に現れる異なる設備コードの個数」です。

1. （い）＝20の根拠
   • 問題文に「一つの物件に最大20個の設備の有無を記録できる」とあります。これが移行前に物件テーブルで定義されていた設備の種類数が20種類であることを示します。
   • また移行手順(4)に「物件設備テーブルには“物件” テーブルにある設備に限って行を登録した」とあるので、物件設備に登録された設備は元の物件テーブルに列としてあった20種類に限定されます。
   • したがって物件設備の設備コードの異なる値の個数は「20」です。
2. （あ）＝1,600,000の根拠
   • 表2により、移行元の「物件」テーブルの行数（物件数）が「1,600,000」であることが明示されています。
   • 移行手順(2)に「“物件” テーブルから設備列20個を除いた全行を、“新物件” テーブルに複写した」とあるので、新物件テーブルの物件コードの集合は元の物件テーブルと同じく1,600,000個の物件コードを持ちます。
   • 移行手順(4)の SQL の例（表4 の SQL5）を見ると、物件設備への登録は元の物件テーブルをソースにした INSERT … SELECT によって行われていることが分かります（SQL5 の構文に SELECT … FROM 物件 とある）。
   • 問題文全体の移行方針は「元の物件テーブルに記録されていた設備情報を物件設備テーブルへ移す」ことですから、移行結果として物件設備に現れる物件コードの種類は元の物件（＝新物件）の物件コードの範囲をカバーすることが前提になっています。よって物件設備の物件コードの異なる値の個数は元の物件数である「1,600,000」となります。

• （あ）＝1,600,000
• （い）＝20

誤りやすいポイント

• 「記録されている20個の設備について、どの設備もいずれかの物件に設置されている」を「各物件に必ず少なくとも1つの設備がある」と誤解しないこと。前者は「各設備に対して設置されている物件が少なくとも1件はある」という意味であり、各物件ごとに必ず設備があることを保証する文ではありません。
• 「列値個数」と「行数」を混同しないこと。列値個数はその列に出現するユニークな値の数、行数はテーブルの総行数です。問題では列値個数を問われています。
• 「設備」テーブルの行数（100）と「物件設備」に実際に現れる設備コードの種類（20）を混同しないこと。設備テーブルは100件分の設備を管理しますが、移行直後に物件設備へ登録したのは元の物件テーブルで列として扱われていた20種に限られる、という点に注意してください。

FAQ

解答の論理構成

1. 旧SQLの要件確認
   • SQL1は「沿線 = '○△線'」かつ「エアコン = 'Y' AND オートロック = 'Y'」を満たす行を取得。
2. 新スキーマへの写像
   • “新物件” で「沿線 = '○△線'」を判定。
   • “物件設備” は物件と設備の多対多を表すため、同じ物件に対して “エアコン” と “オートロック” の2行が必ず存在する必要がある。
3. 2行の存在を保証するための結合方法
   • 1物件に対し2レコードを必須としたい＝結合に漏れがあってはいけない ⇒ INNER JOIN。
   • よって (f)(g) ともに INNER JOIN。
4. 設備を特定する述語
   • 手順(3) で INSERT INTO 設備 VALUES ('A1'、'エアコン')、('A2'、'オートロック') と登録している。
   • 同じ行は「設備名」でも「設備コード」でも一意に指せるので、(h)(i)(j) いずれも名称／コードのいずれか、あるいは両方を組み合わせた句が正答。
5. OR／AND の違い
   • SQL6は2回結合して AND 条件、SQL7は1回結合後に OR 条件でフィルタ。

誤りやすいポイント

• LEFT JOIN を選ぶと「どちらかが欠けていても行が残る」ため SQL1 の意味を満たさない。
• (h)(i) で = と IN の使い分けを間違え、冗長なサブクエリを作ってしまう。
• SQL7で DISTINCT を忘れると「エアコンとオートロック両方ある物件」が2行重複して返る可能性を見落とす。

FAQ

解答の論理構成

1. 【問題文】表6の SQL7
   SELECT DISTINCT B.物件コード, B.物件名 ...
   とあり、DISTINCT で重複除去しています。
2. 同じ表6で 物件設備 BS を JOIN し、さらに 設備 S で (j) 条件を付与。
   (j) は「設備名が 'エアコン' 又は 'オートロック'」を意味します。
3. 物件に
   • エアコンだけ → 1行
   • オートロックだけ → 1行
   • エアコンとオートロック両方 → 2行
     のようにヒット数が異なります。
4. 両方がある場合、同じ 物件コード が 2回返るので重複し、DISTINCT が必要となります。
5. したがって設問の「行が重複する場合」は「エアコンとオートロックの両方が設置されている場合」となります。

誤りやすいポイント

• 「沿線が同じ物件が多いから重複」と勘違いする
  → 重複源は設備の二重ヒットです。
• LEFT JOIN／INNER JOIN の違いに気を取られ、本質を見落とす
  → JOIN 種別ではなく 同じ物件が複数行マッチするかどうかが鍵。
• 別名BSとSの結合条件を読み飛ばし、DISTINCT を不要と判断する。

FAQ

解答の導き方

誤りやすいポイント

• BS1.設備コード='A1' とBS1.設備コード IS NOT NULLを OR で併記する誤り
  • 誤り例：CASE WHEN BS1.設備コード='A1' OR BS1.設備コード IS NOT NULL THEN 'Y' …
  • 問題点：ある物件に 'A3' のような別の設備行が存在してBS1.設備コード が非NULLなら OR 部分が真になり、エアコンが無くても誤って 'Y' を返す可能性があります。等価比較（='A1'）だけで十分です。
• ON 条件を WHERE に書く（あるいは ON に設備コードを加えず WHERE で設備コードを絞る）ことで LEFT OUTER JOIN の意味が変わる誤り
  • 例：LEFT OUTER JOIN ... ON B.物件コード = BS1.物件コード で結合してから WHERE BS1.設備コード = 'A1' とすると、NULL を持つ行が WHERE 節で除外され、実質的に INNER JOIN と同じになり、設備なし物件が消えます。
• INNER JOIN を使う誤り
  • INNER JOIN にすると該当設備を持たない物件は結果から消えるため、元の 'N' を再現できません。
• ON で設備コードを間違える／設備コードの対応を間違える（'A1' と 'A2' の取り違え）
  • 表示する列ごとに対応する設備コードを正しく割り当てる必要があります。

FAQ