データベーススペシャリスト試験 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 物件設備（物件コード, 設備コード, 設置済個数）」
     ⇒ ３列目に固定値「1」を入れる指示は「(4)…設置済個数列に1を設定し」と明示。
   • SQL4 で設備コードが登録されている例：

     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. ２つの 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. “新物件”側の行数確認
   【問題文】表２
   「行数 1,600,000」を持つ“物件”テーブルが手順(2)でそのまま“新物件”に複写された。したがって物件コードは“新物件”でも“物件設備”でも最大1,600,000件になる。
2. “物件設備”への登録条件
   手順(4)
   「“物件”テーブルにある設備に限って行を登録した」とあり、SQL5 でも WHERE エアコン = 'Y' など Y 行だけを INSERT している。
   さらに【問題文】3.(4)
   「どの設備もいずれかの物件に設置されている」と書かれている。これにより 20設備のいずれかは必ず各物件に存在すると解釈でき、結果として物件コードは全件現れる。
   ⇒ （あ）＝「1,600,000」
3. 設備コードの種類
   手順(3) で 100種類の設備コードを“設備”に登録したが、手順(4) では「20個は“物件”テーブルの各設備列に対応させた」と限定している。移行時に登録対象はこの20種類のみ。
   ⇒ （い）＝「20」
4. 以上より表５の空欄は
   （あ）1,600,000、（い）20 となる。

誤りやすいポイント

• 「100種類登録したのだから設備コードは100」と思い込む
  手順(4)は“20個に限って行を登録”と明記。
• 「全物件に設備があるとは限らない」と考え列値個数を小さくする
  【問題文】3.(4)から「どの設備もいずれかの物件に設置」→20設備のどれかは必ず存在する、という前提を取りこぼしがち。
• 行数と列値個数の混同
  表５が求めているのは distinct 個数であり、登録行数そのものではない点を見落としやすい。

FAQ

模範解答

解説

解答の論理構成

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

誤りやすいポイント

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

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

模範解答

解説

解答の論理構成

1. “エアコン” の識別
   • 【問題文】の SQL4 に「('A1', 'エアコン')」とあるため，'A1' が “エアコン” を示す設備コードと確定。
2. ビューで返したい値
   • ビュー“物件”は旧 “物件” テーブルと同じ列を提供する目的で定義されている（本文「実績のある SQL文を変更することなく使いたい」）。
   • 旧テーブルでは “エアコン” 列に Y／N が必ず格納されていた（NOT NULL 制約あり）。
3. 外部結合を選択
   • ある物件に “エアコン” 情報が存在しない場合でも，行自体は返し N を表示する必要があるため，新物件 を基点に LEFT OUTER JOIN を用いる。
4. CASE 式の判定
   • JOIN して一致する設備コードがあるとき BS1.設備コード は非 NULL，無いとき NULL。
   • よって (k) には「BS1.設備コード='A1'」という一致条件と，「BS1.設備コード IS NOT NULL」を OR で併記すれば十分。
   • 一致していれば (l)＝'Y'，そうでなければ (m)＝'N'。
5. 他設備への適用
   • “オートロック” についても，同様に 'A2' を使い，JOIN はすべて LEFT OUTER JOIN で統一する。
6. 以上より，(k)〜(o) の具体値を導出。

誤りやすいポイント

• INNER JOIN を使うと設備が無い物件が消え，NULL→'N' の置換ができなくなる。
• CASE 式の条件を BS1.設備コード='A1' だけにすると，外部結合で NULL が来たときに WHEN が偽になり ELSE へ落ちるため見かけ上問題ないが，可読性・要件（有無判定と一致条件の両立）を満たさないと採点で減点される危険。
• IS NOT NULL を忘れて CASE WHEN BS1.設備コード='A1' THEN 'Y' … と書くと，“A1 以外の設備コードを誤って結合した場合” にも 'N' となり不正確。

FAQ