令和3年度 秋期 データベーススペシャリスト試験 午後Ⅰ 問3 物件設備のデータ構造変更に伴う移行SQLと互換ビューの設計

• 表2の統計情報より、「物件」テーブルの全行数は $1,600,000$ 行である。
• 各列の列値は一様分布に従うと仮定されており、列値は互いに独立している。
• 列「沿線」の列値個数は $400$ なので、特定の沿線（'◯△線'）である確率は $1/400$。
• 列「エアコン」の列値個数は $2$（'Y', 'N'）なので、'Y'である確率は $1/2$。
• 列「オートロック」の列値個数は $2$（'Y', 'N'）なので、'Y'である確率は $1/2$。
• SQL1のWHERE句は 沿線 = '◯△線' AND ( エアコン = 'Y' AND オートロック = 'Y' ) であり、すべての条件がANDで結ばれている。
• これらの条件を同時に満たす確率は、$(1/400) \times (1/2) \times (1/2) = 1/1600$ となる。
• したがって、見積もった結果行数は $1,600,000 \times (1/1600) = 1,000$ 行となる。

• 表2の統計情報より、「物件」テーブルの全行数は $1,600,000$ 行である。
• 各列の列値は一様分布に従い独立している。
• 沿線が'◯△線'である確率は $1/400$。
• SQL2のWHERE句の条件は 沿線 = '◯△線' AND ( エアコン = 'Y' OR オートロック = 'Y' ) である。
• エアコン = 'Y' OR オートロック = 'Y' を満たす確率を求める。エアコンが'Y'の確率は $1/2$、オートロックが'Y'の確率は $1/2$。両方が'Y'の確率は $(1/2) \times (1/2) = 1/4$。
• したがって、少なくとも一方が'Y'である確率は $1/2 + 1/2 - 1/4 = 3/4$ となる。
• これらは独立しているため、WHERE句全体を満たす確率は $(1/400) \times (3/4) = 3/1600$ となる。
• したがって、見積もった結果行数は $1,600,000 \times (3/1600) = 3,000$ 行となる。

• SQL3は、「設備にエアコンとオートロックの両方がある物件を沿線ごとに集計した物件数が，全物件数に占める割合を百分率（小数点以下切捨て）で求める」クエリである。
• メインのSELECT文では、WHERE エアコン = 'Y' AND オートロック = 'Y' で絞り込み、GROUP BY ホ (沿線) で沿線ごとにグループ化している。
• 百分率を求める式 FLOOR( ハ * 100 / ニ ) において、「ハ」は分子となる「沿線ごとの条件を満たす物件数」である必要がある。
• グループ内の行数を数える集計関数は COUNT(*) である。したがって、「ハ」には COUNT(*) が入る。

高得点のポイント

• 集計関数の目的を理解していること
• 行数を数えるための正しい記述となっていること

• SQL3の百分率を計算する数式 FLOOR( ハ * 100 / ニ ) において、「ニ」には分母となる「全物件数」が入る必要がある。
• 全物件数は、先行する WITH 句で定義された TEMP テーブルの TOTAL 列として SELECT COUNT(*) FROM 物件 により計算されている。
• メインのクエリでは CROSS JOIN TEMP によりこの TOTAL 列が全ての行に結合されているため、分母として TOTAL を参照できる。
• したがって、「ニ」には TOTAL が入る。

高得点のポイント

• 割合を求めるための分母として全物件数が必要であることを理解していること
• TEMPテーブルの列名である TOTAL を正しく指定していること

• SQL3では「沿線ごとに集計した物件数」を求めるため、集計のキーとなる 沿線 でグループ化する必要がある。
• さらに、集計関数（COUNT(*)）を使用するクエリにおいて、SELECT句に指定した列のうち、集計関数に含まれない列はすべて GROUP BY 句に指定するというSQLの仕様がある。
• SELECT句には 沿線 と TOTAL （数式内の分母）が指定されているため、GROUP BY にも 沿線 と TOTAL の両方を含める必要がある。
• したがって、「ホ」には 沿線, TOTAL （または 沿線,TOTAL）が入る。

高得点のポイント

• GROUP BY句にはSELECT句に指定された非集計列をすべて含める必要があるというSQLの仕様を理解していること
• 沿線 と TOTAL の両方が正しい構文で列挙されていること

• 「2. 物件の設備に関する課題への対応」における案Bは、設備の管理を列から行（「設備」テーブルと「物件設備」テーブル）へと正規化する設計である。
• これにより、本文中の課題に対応して以下のいずれかの長所が考えられる。
  1. 課題「(1) … 全部で 100 個ある。将来，増える可能性がある」に対して、新しい設備は「設備」テーブルと「物件設備」テーブルへの 行の追加 で対応でき、テーブル定義の変更が不要になる。
  2. 「検索システムの概要」2.(5) の「テーブルの定義を変更し，記録する人気の設備を毎年入れ替える処理を行っている。この処理を物件設備の入替処理と呼んでいる」について、全ての設備を保持できるようになるため、この 物件設備の入替処理が不要 になる。
  3. 課題「(2) 設備の設置済個数が分からない。」に対して、「物件設備」テーブルに「設置済個数」列を持たせることで、全ての設備の正確な設置個数を管理でき、問合せに答えられるようになる。
• いずれか一つを、指定された字数と本文中の用語を用いて解答する。

高得点のポイント

• 案Bの採用によって解決される課題（入替処理の不要化、設置済個数の管理、設備の追加への対応容易性）のいずれかの長所を正しく抽出できていること
• 本文中の用語を用いて、文意が通るように論理的に記述されていること

• SQL5は「物件設備」テーブルにデータを登録する処理であり、挿入先の列構成は (物件コード, 設備コード, 設置済個数) である。
• 最初のSELECT文はエアコンに関するデータを旧「物件」テーブルから抽出して登録する。
• 登録すべきデータは、「物件コード」、エアコンの設備コード（表4のSQL4より 'A1'）、「設置済個数」である。
• 「3. テーブルの移行」(4) に「設置済個数列に1を設定し」とあるため、個数として固定値の 1 を指定する。
• したがって、「a」には挿入先の列に対応する 物件コード, 'A1', 1 が入る。
• なお、a・b の組と d・e の組は入れ替えてもよい（公式解答例の備考「順不同」）。

高得点のポイント

• 挿入先に対応した値を正しく指定できていること
• SQLのSELECT句として正しい構文で記述されていること

• SQL5において、エアコンとオートロックに関する2つのSELECT文の結果を合わせて「物件設備」テーブルに挿入する。
• そのため、二つのSELECT文の結果行の和集合を求める演算子が必要である。
• 設備コードが 'A1' と 'A2' で異なるため結果行に重複は発生せず、重複排除を伴わない UNION ALL を使用するのが適切である。（UNION でも動作するが、より効率的な UNION ALL が期待される）
• したがって、「c」には UNION ALL が入る。

高得点のポイント

• 複数のSELECT文の結果を結合して和集合を求める演算子が必要であることを理解していること
• 重複排除が不要なため効率的な UNION ALL を用いて記述できていること

• 2つ目のSELECT文はオートロックに関するデータを旧「物件」テーブルから抽出して登録する処理である。
• 登録すべきデータは、「物件コード」、オートロックの設備コード（表4のSQL4より 'A2'）、「設置済個数」である。
• aと同様に、「設置済個数列に1を設定し」とあるため、個数として固定値の 1 を指定する。
• したがって、「d」には挿入先の列に対応する 物件コード, 'A2', 1 が入る。
• なお、a・b の組と d・e の組は入れ替えてもよい（公式解答例の備考「順不同」）。

高得点のポイント

• 挿入先に対応した値を正しく指定できていること
• SQLのSELECT句として正しい構文で記述されていること

• 最初のSELECT文はエアコンについてのデータを登録するため、エアコンがある行だけを旧「物件」テーブルから抽出する必要がある。
• 表1の制約より、旧「物件」テーブルにおいてエアコンが設置されていることを示す条件は エアコン = 'Y' である。
• したがって、絞り込み条件であるWHERE句の「b」には エアコン = 'Y' が入る。
• なお、a・b の組と d・e の組は入れ替えてもよい（公式解答例の備考「順不同」）。

高得点のポイント

• エアコンが設置されている条件を旧テーブルの列から正しく導出できていること
• SQLのWHERE句として正しい比較述語で記述されていること

• 2つ目のSELECT文はオートロックについてのデータを登録するため、オートロックがある行だけを旧「物件」テーブルから抽出する必要がある。
• 表1の制約より、旧「物件」テーブルにおいてオートロックが設置されていることを示す条件は オートロック = 'Y' である。
• したがって、絞り込み条件であるWHERE句の「e」には オートロック = 'Y' が入る。
• なお、a・b の組と d・e の組は入れ替えてもよい（公式解答例の備考「順不同」）。

高得点のポイント

• オートロックが設置されている条件を旧テーブルの列から正しく導出できていること
• SQLのWHERE句として正しい比較述語で記述されていること

• 表5の空欄「あ」は、「物件設備」テーブルに存在する「物件コード」の列値個数（異なり数）を求めている。
• 「3. テーブルの移行」(4)より、「物件設備」テーブルには旧「物件」テーブルにある設備に限って行を登録している。
• 「物件設備」テーブルは物件と設備の組ごとに行をもつので、物件コードの列値個数は、いずれかの設備がある物件の数になる。
• 本文に設備が一つも無い物件の記述は無く、公式解答はすべての物件（1,600,000 件）がいずれかの設備をもつものとしている。
• したがって、物件コードの列値個数は全物件数と同じ 1,600,000 となる。

• 表5の空欄「い」は、「物件設備」テーブルに存在する「設備コード」の列値個数（異なり数）を求めている。
• 「3. テーブルの移行」の手順(4)で「“物件設備”テーブルには“物件”テーブルにある設備に限って行を登録した」と指定されている。
• 旧「物件」テーブルで管理されていた設備は、〔検索システムの概要〕2.(3)および入替処理の記述より、20個である。
• 〔検索システムの概要〕2.(4)に「どの設備もいずれかの物件に設置されている」とあり、物件テーブルの 20 個の設備コードがすべて“物件設備”に現れるので 20 となる。
• 「設備」テーブル自体には100個の設備が新規登録されたが、初期移行として「物件設備」テーブルに登録されたのは旧テーブルに存在した20個の設備のみである。
• したがって、「設備コード」の列値個数は 20 となる。

• SQL6は表3のSQL1（エアコンとオートロックの両方がある物件）の移行後SQLである。
• 移行後は「新物件」テーブルに対して「物件設備」テーブルを結合し、条件に合致する物件だけを抽出する必要がある。
• 指定した設備（エアコン）を持つ物件のみを残すため、結合対象のテーブルに存在する行だけを絞り込んで抽出する内部結合を使用する。
• したがって、「f」には INNER JOIN（または JOIN）が入る。

高得点のポイント

• 条件を満たす物件のみを抽出するためには内部結合が必要であることを理解していること
• 正しい結合演算子 INNER JOIN を指定できていること

• f と同様に、「物件設備」テーブルに紐づく「設備」テーブルを結合し、特定の設備であることを条件に絞り込む必要がある。
• 条件に合致する設備のみを残すため、内部結合を使用する。
• したがって、「g」にも INNER JOIN（または JOIN）が入る。

高得点のポイント

• 条件を満たす設備のみを抽出するためには内部結合が必要であることを理解していること
• 正しい結合演算子 INNER JOIN を指定できていること

• SQL6はエアコンとオートロックの両方がある物件を求める。
• 1つ目の結合先である設備テーブルのエイリアス S1 に対して、その設備が「エアコン」であるという条件を指定する必要がある。
• SQL6 では“設備”テーブルを S1・S2 の2回結合しており、どちらの表の列かを区別するため相関名で修飾する必要があるので、テーブルエイリアスを用いて S1.設備名 = 'エアコン'（または S1.設備コード = 'A1'）のように記述する。
• したがって、「h」には S1.設備名 = 'エアコン' が入る。
• なお、h・i は入れ替えてもよい（公式解答例の備考「順不同」）。

高得点のポイント

• 抽出したい設備（エアコン）を絞り込む条件を正しく導出できていること
• 列名を正しい相関名（エイリアス S1）で修飾して記述できていること

• SQL6において、2つ目の結合先である設備テーブルのエイリアス S2 に対しては、その設備が「オートロック」であるという条件を指定する必要がある。
• したがって、「h」と同様にテーブルエイリアスを用いて S2.設備名 = 'オートロック'（または S2.設備コード = 'A2'）のように記述する。
• これにより、エアコンとオートロックの両方を持つ物件のみが結果として残る。
• なお、h・i は入れ替えてもよい（公式解答例の備考「順不同」）。

高得点のポイント

• 抽出したい設備（オートロック）を絞り込む条件を正しく導出できていること
• 列名を正しい相関名（エイリアス S2）で修飾して記述できていること

• SQL7は表3のSQL2（エアコン又はオートロックのいずれかがある物件）の移行後SQLである。
• 内部結合において、設備の条件として「エアコン又はオートロック」のどちらかに合致するものを抽出する。
• したがって、OR論理演算子を用いて （S.設備名 = 'エアコン' OR S.設備名 = 'オートロック'）（設備コードでの指定も可）のように記述する。
• AND条件がOR条件より優先されるため、OR条件全体を括弧でくくる必要がある。
• ここでも相関名 S による列名の修飾が必要である。

高得点のポイント

• 「いずれか」という条件を満たすためにOR論理演算子が必要であることを理解していること
• 列名を正しい相関名（エイリアス S）で修飾し、構文として正しいOR条件を記述できていること

• SQL7は「新物件」テーブルに「物件設備」と「設備」を結合し、設備が「エアコン又はオートロック」である物件を検索している。
• 1つの物件に対して条件に合致する「物件設備」の行が複数存在する場合、テーブル結合（1対多の結合）の性質上、「新物件」側の同じ物件行が複製されて出力されてしまう。
• 具体的には、結合条件 j (S.設備名 = 'エアコン' OR S.設備名 = 'オートロック') を満たす設備が2つとも存在する場合、つまり「エアコンとオートロックの両方が設置されている場合」に、1つの物件に対して結果行が2行生成され重複する。
• そのため DISTINCT を用いて重複を排除している。

高得点のポイント

• 1対多のテーブル結合において、条件に合致する設備が複数ある場合に結果行が重複するメカニズムを理解していること
• 本文中の用語を用いて、文意が通るように論理的に記述されていること

• 図3のビュー定義において、SELECT句の CASE 文は、エアコンが設置されているかどうかで返す値を分岐させている。
• 外部結合（LEFT OUTER JOIN）を用いているため、物件にエアコンが設置されていない場合は BS1 側の列値はすべて NULL となる。
• 設置されている場合は、結合条件（BS1.設備コード = 'A1'）により値が存在する。
• したがって、エアコンが設置されているかどうかの判定条件として、「k」には BS1.設備コード = 'A1' や BS1.設備コード IS NOT NULL などが入る。

高得点のポイント

• 外部結合の結果から、設備が存在するかどうかを判定するための条件を正しく導出できていること
• 相関名を用いた正しいSQL述語で記述できていること

• CASE 文で条件 k を満たした（エアコンが設置されている）場合に返す値である。
• 表1「“物件” テーブルの主な列の意味・制約」より、当該設備が設置されていることを示す値は 'Y' である。
• したがって、「l」には 'Y' が入る。

高得点のポイント

• 設備が設置されていることを示すデータ値を旧テーブルの仕様から正しく特定できていること
• 文字列リテラルとして正しく（シングルクォーテーションで囲んで）記述できていること

• CASE 文で条件 k を満たさない（エアコンが設置されていない）場合に返す値である。
• 表1「“物件” テーブルの主な列の意味・制約」より、当該設備が設置されていないことを示す値は 'N' である。
• したがって、「m」には 'N' が入る。

高得点のポイント

• 設備が設置されていないことを示すデータ値を旧テーブルの仕様から正しく特定できていること
• 文字列リテラルとして正しく（シングルクォーテーションで囲んで）記述できていること

• このビューは旧「物件」テーブルの構造を再現するものであり、エアコンやオートロックが設置されていない物件（値が 'N' になる物件）も結果に含める必要がある。
• INNER JOIN（内部結合）を使用すると、その設備を持たない物件の行が結果から消滅してしまう。
• ベースとなる「新物件 B」の全行を保持しつつ、該当する「物件設備」の情報を結合するには、左外部結合を使用する必要がある。
• したがって、「n」には LEFT OUTER JOIN（または LEFT JOIN）が入る。

高得点のポイント

• 設備が設置されていない物件も結果行として残すために外部結合が必要であることを理解していること
• 正しい結合演算子 LEFT OUTER JOIN を指定できていること

• BS1 はエアコンに関する情報を結合するためのテーブルエイリアスである。
• エアコンの情報を正しく結びつけるには、結合の ON 条件として「物件設備」テーブル側の設備コードがエアコンのもの（表4より 'A1'）であることを指定する必要がある。
• したがって、「o」には BS1.設備コード = 'A1' が入る。

高得点のポイント

• エアコンの設備情報を結合するための条件（設備コードが'A1'）を正しく導出できていること
• 列名を正しい相関名（エイリアス BS1）で修飾して記述できていること