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

模範解答のキーワード・論点整理

• 独立一様分布 による行数推定
  「沿線, エアコン, オートロックの列値の分布は互いに独立し, 各列の列値は一様分布に従うと仮定すること」（問題文より）
• フィルタ条件ごとの行数算出
  • 全行数：1,600,000（“物件”テーブルの行数）
  • 沿線の種類数：400（“沿線”列の列値個数）
  • 設備列の種類数：2（Y／N）
• AND／OR による確率計算
  • P(A and B) = P(A) × P(B)（独立の場合）
  • P(A or B) = P(A) + P(B) − P(A and B)
• SQL3 の WITH（CTE）構文 と GROUP BY
  • TEMP(TOTAL) AS (SELECT COUNT(*) FROM 物件)
  • GROUP BY で参照列を明示的に指定する必要

解答になる理由の論理的説明

1. SQL1 の結果行数イ の算出
   問題文：「行数 1,600,000」「沿線の列値個数 400」「エアコンの列値個数 2」（表2より）

   WHERE 沿線 = '○△線'  
   

   • 全行数1,600,000を一様分布400で割る → 1,600,000 / 400 = 4,000行

   AND ( エアコン = 'Y' AND オートロック = 'Y' )  
   

   • 各設備の「Y」の確率は1/2。独立なので 1/2 × 1/2 = 1/4
   • 4,000 × 1/4 = 1,000 → イ：1,000
2. SQL2 の結果行数ロ の算出

   WHERE 沿線 = '○△線'
   

   • 同じく 4,000行

   AND ( エアコン = 'Y' OR オートロック = 'Y' )
   

   • P(Y or Y) = 1/2 + 1/2 − 1/4 = 3/4
   • 4,000 × 3/4 = 3,000 → ロ：3,000

   SQL	フィルタ内容	フィルタ後行数	確率	結果行数
   SQL1	沿線='○△線' AND (Y 且つ Y)	4,000	1/4	1,000
   SQL2	沿線='○△線' AND (Y または Y)	4,000	3/4	3,000

3. SQL3 の空欄ハ～ホ の埋め方

   WITH TEMP( TOTAL ) AS ( SELECT COUNT(*) FROM 物件 )
   SELECT 沿線,
          FLOOR( <ハ> * 100 / <ニ> )
   FROM 物件 CROSS JOIN TEMP
   WHERE エアコン = 'Y' AND オートロック = 'Y'
   GROUP BY <ホ>;
   

   • <ハ> は 「COUNT(*)」（行数を集計）
   • <ニ> は CTE で定義した総件数の列名「TOTAL」
   • <ホ> には SELECT 句に現れる非集約列をすべて指定
     • 「沿線」 と 「TOTAL」
   • したがって
     • ハ：COUNT(*)
     • ニ：TOTAL
     • ホ：沿線,TOTAL

受験者が誤りやすいポイント

• AND と OR の確率計算を取り違える
  • OR の場合、P(A)+P(B)−P(A∧B) を使うことを忘れると過剰集計になる
• GROUP BY に必要な列を抜かす
  • CTE の列（TOTAL）を GROUP BY に含めないとエラーになる
• 一様分布・独立の仮定を適用し忘れる

試験対策として覚えておくべきポイント

1. 行数推定では「全行数 ÷ 値の種類数」
2. 独立一様分布なら AND→乗算、OR→加算−乗算
3. WITH（CTE）で定義した列を SELECT で参照する場合は GROUP BY に含める
4. FLOOR 関数で小数点以下を切り捨てる計算パターンを定番問題として押さえる

1. 模範解答の核心キーワードと論点整理

2. 解答が正しい理由の論理的説明

1. 従来の課題：「物件設備の入替処理」
   問題文では，設備の流行に合わせて「テーブルの定義を変更し，記録する人気の設備を毎年入れ替える処理」を行っており，これを「物件設備の入替処理」と呼んでいます（問題文 2-(5)）。
2. 案Bの構造：EAVモデルの採用
   • 「設備」テーブルに全設備を登録し（図2），
   • 「物件設備」テーブルで物件コード×設備コード×設置済個数を管理。
     問題文 2-(B) の記述参照。
3. 物件設備の入替処理が不要となる理由
   • 新たに流行する設備を追加するときは，「設備」テーブルに行を追加し，物件との紐付けを「物件設備」テーブルにINSERTすればよい。
   • テーブル定義（列の増減）は一切行わず，スキーマ変更コスト（入替処理）が不要となる。
4. 全設備の有無・個数問合せが可能
   • 「物件設備」テーブルの「設置済個数」列により，単純な有無（Y/N）だけでなく，実際の個数も把握できる（問題文 2-(B)／3-(4)）。
5. 将来の設備増加への対応
   • 新しい設備は「設備」テーブルへの追加のみで対応可能。
   • 従来のように20列に制限されず，100個，さらに将来増えてもスキーマ変更不要。

3. 受験者が誤りやすいポイント

• 案A（列追加）との混同
  案Aでは「エアコン台数列を追加」するだけのため，結局40列，100列とスキーマ変更が続き，入替処理は解決しない。
• EAVモデルの意義
  データモデル初心者は「EAV＝複雑で性能低下」と誤解しがちですが，属性数が可変・増加するケースでは有効です。
• 「入替処理」対象の定義
  問題文中の「物件設備の入替処理」はテーブル定義変更を指すため，「物件設備」テーブルへのデータ移行処理と混同しないよう注意。

4. 試験対策として覚えておくべきポイント

1. EAVモデルのメリット・デメリット
   • メリット：属性数が可変／将来増加にもスキーマ変更不要
   • デメリット：JOINが増える，クエリが複雑化する
2. 正規化と可変属性管理
   • 第三正規形を超える可変属性はEAVで管理
   • スキーマ変更コストを抑えたい場合に適用
3. SQL設計とビュー活用
   • 既存SQLを無変更で使いたい場合は，ビューで旧スキーマを再現
   • CASE式やJOINを組み合わせ，物理構造と論理構造を隔離
4. 統計情報の読み取り
   • 行数／列値個数から選択性や結合順序を推定
   • 性能要件とデータ量のバランスを考慮した設計

1. キーワード・論点の整理

• 「設置済個数列に1を設定」する点
• 各SELECTで固定文字列として設備コード（例：'A1'／'A2'）を返す点
• エアコンとオートロックの各設備ごとに行を取得し、結果をまとめる点

2. 解答の論理的説明

“物件設備” テーブルには “物件” テーブルにある設備に限って行を登録した。
エアコン又はオートロックがある行を登録する SQL文の例を, 表4の SQL5 に示す。
ここで, 設置済個数列に1を設定し, 正確な個数を移行後に設定することにした。

1. 対象行
   • 物件テーブルの「エアコン = 'Y'」または「オートロック = 'Y'」を 持つ行
2. INSERT先の列
   • (物件コード, 設備コード, 設置済個数)
3. 設置済個数
   • 「1」を設定
4. 設備コード
   • エアコンは 'A1'、オートロックは 'A2'（表4のSQL4より）
5. 複数設備分の行をまとめる
   • UNION ALL で２つのSELECT結果を連結

INSERT INTO 物件設備（物件コード, 設備コード, 設置済個数）
SELECT 物件コード, 'A1', 1
  FROM 物件
 WHERE エアコン = 'Y'
UNION ALL
SELECT 物件コード, 'A2', 1
  FROM 物件
 WHERE オートロック = 'Y';

3. 誤りやすいポイント

1. UNION vs UNION ALL
   • UNION は重複行を排除しますが、移行時の性能や意図からも重複を気にせず結合する UNION ALL が適切です。
2. リテラルの扱い
   • 設備コードは文字列リテラルなので必ずシングルクォートで囲む（例：'A1'）。
   • 設置済個数は数値リテラル（1）として扱う点に注意。
3. WHERE句の条件
   • "エアコン='Y' OR オートロック='Y'" のように1つのSELECTで OR 条件にしてしまうと、同じ物件で両方の設備がある場合に1行しか作れず、設備ごとの行取得要件を満たせません。

4. 試験対策として覚えておくポイント

• INSERT … SELECT構文
  複数の行を他テーブルから挿入するときは INSERT INTO … SELECT … を使い、複数SELECTをまとめるには UNION ALL を利用する。
• リテラルの指定方法
  • 文字列はシングルクォートで囲む
  • 数値はそのまま記述
• 行設計 vs 列設計
  設備のように可変・追加が想定される属性は、列として増やすのではなく行として管理するテーブル設計（EAVモデル）を検討する。
• 性能・重複の考慮
  UNION は重複排除でコストがかかるため、「重複が存在し得ない」「重複を気にしない」場合は UNION ALL を選ぶ。

核心となるキーワードや論点整理

1. 「列値個数」
   − 表５では各テーブル・列ごとに 異なる値の個数 を示している。
   − 物件設備テーブルの
   • 「物件コード」列の列値個数 → 何件の異なる物件コードが登録されたか
   • 「設備コード」列の列値個数 → 何件の異なる設備コードが登録されたか
2. テーブルの移行手順
   − 【問題文】(3)、(4)より、
   • “設備” テーブルには100個の設備コードを登録
   • “物件設備” テーブルには 元の“物件”テーブルにおいて 設置あり（‘Y’）の設備だけを登録
   • 設置済個数列はすべて「1」で仮登録
3. 元の“物件”テーブルの統計情報
   − 【問題文】表2より、
   • “物件” テーブルの行数＝1,600,000行
   • 元の20個の設備列はすべて NOT NULL（Y/N）
   • 「どの設備もいずれかの物件に設置されているが、20個全ての設備が設置されている物件は限られている」
     − → 各物件には少なくとも 1 件の‘Y’があると想定
4. 表5の未記入部分

   テーブル名	行数	列名	列値個数
   物件設備	（省略）	物件コード	（あ）
   		設備コード	（い）

   − （あ）：物件設備テーブルに何件の異なる物件コードがあるか
   − （い）：物件設備テーブルに何件の異なる設備コードがあるか

解答の理由（論理的説明）

1. （あ）「物件コード」の列値個数
   − 移行前の“物件”テーブルには「1,600,000」行、かつ各行（各物件）に対し 少なくとも1つはY がある（元の20設備いずれかが必ず設置されている）。
   【問題文】

   「記録されている20個の設備について,どの設備もいずれかの物件に設置されているが,20個全ての設備が設置されている物件は限られている。」
   − 移行手順で、各物件コードは “物件” テーブルでYの設備がある分だけ “物件設備” テーブルに 1行以上 登録される。
   − 物件コードの重複を除いた異なる値の個数は、元の“物件”テーブルの全物件（1,600,000件）に対応する。
   − したがって、（あ）＝1,600,000

2. （い）「設備コード」の列値個数
   − “設備”テーブルには100個の設備コードが登録されているが、
   − “物件設備” には「物件”テーブルにある設備に限って行を登録する としている。
   【問題文】

   「物件設備テーブルには“物件”テーブルにある設備に限って行を登録した。」
   − 元の“物件”テーブルで列として持っていた設備は 20個。
   − よって、“物件設備”テーブルに登場する設備コードの種類はこの 20個 だけ。
   − したがって、（い）＝20

よくある誤りや注意点

1. 「すべての設備コード100個」と答えてしまう
   − 設備テーブルに100個登録されていても、物件設備テーブルには「物件テーブルにある設備に限って」登録している点を見落としやすい。
2. 「物件コードの列値個数」＝行数と混同する
   − 物件設備テーブルの行数は設備の数だけ増えるので 1,600,000×平均設置数 ほどになるが、
   − 列値個数は DISTINCT の考え方で、物件コードの重複を除いた数（物件数）になる点に注意。
3. 「物件によってはYが一つもないかも？」と迷う
   − 問題文で「どの設備もいずれかの物件に設置されている」とあり、各設備は少なくともどこかにYがあるが、
   − 各物件にYが必ず1つ以上という明示はない。しかし試験では「20個全ての設備が設置されている物件は限られている」とあるので、
   − “設備が1件もない物件”は想定外 と解釈し、全物件が少なくとも1行登録される前提で進める。

試験対策ポイント

• 表や図に示される「行数」「列値個数」は統計情報として DISTINCT を意識する。
• テーブル移行・正規化では、従来カラム→行への変換（EAV モデル）の際、実際に登録される行数と列値個数がどう変化するか を押さえる。
• 問題文の「○○に限って登録する」「～に対応させる」といった条件文は設問で必ず使う重要な根拠となる。
• 「設備コードの種類」「物件コードの種類」は元データとの対応関係を元に考える癖をつけると誤答を防げる。

コアとなるキーワード・論点整理

1. JOINの種類
   • 移行前後で同じ結果を得るには、物件と物件設備・設備を結合する際に不整合が起きないよう、余分な行を排除する内部結合（INNER JOIN）を用いる。
2. エイリアスを用いた複数JOIN
   • SQL6では 2 回の物件設備（BS1, BS2）および設備（S1, S2）へのJOIN、SQL7では 1 回の物件設備（BS）／設備（S）へのJOIN を行う。
3. 設備テーブルからの絞り込み条件
   • “エアコン” と “オートロック” は、表4（SQL4）で

     INSERT INTO 設備 VALUES ('A1', 'エアコン');
     INSERT INTO 設備 VALUES ('A2', 'オートロック');
     

     と登録されている。よって、設備名 or 設備コード を条件に指定できる。
4. OR条件による複数値指定
   • SQL7 では、エアコンまたはオートロックの両方を対象にするため、OR 条件でつなぐ必要がある。

解答とその論理的根拠

なぜINNER JOINか

• 表3のSQL1,2ではWHERE句で「エアコン = 'Y' AND／OR オートロック = 'Y'」としていた。
• 移行後は、NULL行や不要な物件を含めないよう、内部結合（INNER JOIN） を使い、条件に合致する行のみを抽出します。

h, i, j の条件指定

• 「設備名の列値に“物件”テーブルの設備列名をそのまま設定し」（問題文3-2）とあるため、設備名で指定するのがわかりやすい。
• 同時に、設備コード（'A1', 'A2'）もユニークで確実なため、こちらを用いる選択肢もOK。
• SQL6ではそれぞれ別々のJOINなので 単一の条件、SQL7ではまとめて抽出するため OR で連結した条件 をON句に含める。

受験者が誤りやすいポイント

1. JOINの種類
   • OUTER JOIN を誤って使うと、NULLを含む余分な物件が結果に残り、移行前の結果と一致しなくなる。
2. ON句とWHERE句の混同
   • JOIN での条件はON句に書く。ON と WHERE を混ぜると、結合順序や結果行に影響を及ぼす。
3. OR条件の落とし穴
   • SQL7 で OR を忘れると、エアコンだけある物件かオートロックだけある物件かのいずれかしか抽出できない。
4. エイリアスの不一致
   • SQL6 の S1,S2 と SQL7 の S を混同しない。ON句のエイリアスを合わせること。

試験対策として覚えておくべきポイント

• 内部結合（INNER JOIN）の役割：両テーブルに共通する行だけを結合する。
• ON句に書く条件とWHERE句の違い：JOIN条件はON、後段の絞り込みはWHERE。
• エイリアス活用：同じテーブルを複数回JOINするときは必ず別名を付ける。
• OR条件の書き方：複数値をまとめて抽出する際のON句内での書き方を確実に。
• テーブル移行後のSQL検証：移行前後で同一結果を得るには、JOINの種類・条件指定が必須。

模範解答のキーワード・論点整理

• DISTINCT：結果行の重複を排除するキーワード
• OR条件：エアコン 又は オートロック のいずれかを満たす
• テーブル結合後の行生成：同じ物件が複数行にマッチする

なぜ「エアコンとオートロックの両方が設置されている場合」なのか

SELECT DISTINCT B.物件コード, B.物件名
FROM 新物件 B
  JOIN 物件設備 BS ON B.物件コード = BS.物件コード
                  AND B.沿線 = '〇△線'
  JOIN 設備 S   ON BS.設備コード = S.設備コード
                  AND (S.設備名 = 'エアコン'
                       OR S.設備名 = 'オートロック')

• 「物件設備」テーブルには，物件と設備の組合せごとに１行が登録されています。
• ある物件にエアコンとオートロックの両方があれば，OR条件（… OR …）で結合すると，
  • 物件＋エアコン の行
  • 物件＋オートロック の行
    の合計２行が出力されます。
• これをそのままSELECTすると同じ物件コードが２行出力されるため，
  重複を排除するために DISTINCT を指定しています。

受験者が誤りやすいポイント

試験対策として覚えておくべきポイント

1. テーブルを結合（JOIN）した際、複数条件にマッチすると同じキーが何行も生成されることがある。
2. 重複行を排除したい場合は SELECT DISTINCT を使う。
3. OR条件を結合句で使うと，該当するすべての行が出力対象になるので注意する。

1. キーワード・論点の整理

2. 解答が導かれる理由

2.1 CASE式でのY/N判定

“物件設備” テーブルには 物件にある設備に限って行を登録した。…設置済個数列に1を設定し…

• 「物件設備」に行が存在すれば 設置あり
• 存在しなければ 設置なし

CASE WHEN 【k】 THEN 【l】 ELSE 【m】 END AS エアコン

• WHEN には，BS1 というエイリアスの「物件設備」がエアコンを示す設備コード 'A1' を持つかを判定する条件
• THEN は設置ありの値＝'Y'
• ELSE は設置なしの値＝'N'

2.2 NULL と存在判定

物件に設備がない場合にもビューで行を残し，CASE で 'N' を返す

2.3 JOIN の ON 句でのコード指定

• ON B.物件コード = BS1.物件コード AND BS1.設備コード = 'A1'

3. 誤りやすいポイント

• JOIN の種類を INNER JOIN としてしまうと，設備なしの物件がビューに出てこず，CASE 式で ELSE を適用できず誤った結果になります。
• WHEN 条件に BS1.設置済個数 = 1 などとすると，移行後に正確個数を入れるタイミング次第で結果が変わる恐れがあります。
• ON 句と WHERE 句を混同し，WHERE で設備コードを制限すると，LEFT OUTER JOINが実質 INNER JOIN になってしまいます。

4. 小問の解答

     または  
     BS1.設備コード IS NOT NULL                    |

5. 試験対策ポイント

• ノーマル化後でも既存の列操作を変更せずに再現するには，CASE WHEN … THEN 'Y' ELSE 'N' END と LEFT OUTER JOIN の組み合わせが基本
• ON 句で結合条件を指定し，WHERE 句は後段の絞り込みに使い分ける（JOIN の外部結合を潰さないよう注意）
• NULL／NOT NULL 判定を使って存在チェックを行うテクニック
• ビューを使って既存SQLを透過的に置き換える方法を理解しておくこと