テーブルの移行及びSQLの設計に関する次の記述を読んで，設問1，2に答えよ。
A社は，不動産賃貸仲介業を全国規模で行っている。RDBMSを用いて物件情報検索システム（以下，検索システムという）を運用している運用部門のKさんは，物件情報を検索するSQL文を設計している。
〔検索システムの概要〕
検索システムは，物件を管理するシステムを補完するシステムであり，社内利用者が接客するとき，当該システムの“物件”テーブルを利用している。
1.社内利用者の接客業務の概要
（1）物件を探している借主に対して，当該借主の希望に近い物件を探す支援を行い，借主と貸主との間の交渉賃貸契約の仲介を行う。
（2）物件の貸主に対して，物件の審査を行う。当該貸主に長期の空き物件がある場合，周辺の競合物件の付帯設備（以下，設備という）の設置状況を調査し，当該空き物件に人気の設備を増強することなど，物件の付加価値を高める対策の助言を行うこともある。
2.“物件”テーブル
（1）A社が仲介する全ての物件を，物件コードで一意に識別する。
（2）物件の沿線，最寄駅，賃料，間取りなどの基本属性を記録する列がある。
（3）エアコン，オートロックなどの設備が設置されているかどうかの有無を記録する列があり，一つの物件に最大20個の設備の有無を記録できる。
（4）記録されている20個の設備について，どの設備もいずれかの物件に設置されているが，20個全ての設備が設置されている物件は限られている。
（5）設備に流行があるので，テーブルの定義を変更し，記録する人気の設備を毎年入れ替える処理を行っている。この処理を物件設備の入替処理と呼んでいる。
（6）“物件”テーブルの全ての列にNOT NULL制約を指定している。
3.“物件”テーブルのテーブル構造，主な列の意味と制約及び主な統計情報
“物件”テーブルのテーブル構造を図1に，主な列の意味・制約を表1に，RDBMSの機能を用いて取得した主な統計情報を表2に示す。
図1 “物件”テーブルのテーブル構造（一部省略）
| 列名 | 意味・制約 |
| 物件コード | 物件を一意に識別するコード |
| 沿線 | 物件から利用可能な沿線のうち代表的な沿線の名前 |
| 最寄駅 | 物件から利用可能な駅のうち代表的な駅の名前 |
| エアコン，オートロック，… | 当該設備が設置されているかどうかの有無を示す値 Y：設置あり，N：設置なし |
| テーブル名 | 行数 | 列名 | 列値個数 |
| 物件 | 1,600,000 | 物件コード | 1,600,000 |
| 沿線 | 400 | ||
| エアコン | 2 | ||
| オートロック | 2 |
4.検索システムの課題
Kさんは，社内利用者に聞取り調査を行い，その結果を二つの課題にまとめた。
（1）“物件”テーブルの各設備の有無を示す列（以下，総称して設備列という）の数は不十分で，借主からの問合せに十分に対応できていない。追加したい設備は，テレワーク対応，宅配ボックス，追い焚き風呂などがあり，現在の20個を含め，全部で100個ある。将来，増える可能性がある。
（2）設備の設置済個数が分からない。例えば，借主から物件に設置されているエアコンについて問合せがあったとき，設置されている正確な個数が分からず，別の詳細な物件設備台帳を調べなければならない。
1.物件の設備に関する調査
Kさんは，現在検索できる設備の組合せを述語に指定したSQL文を調査した。そのSQL文の例を，表3に示す。そしてKさんは，SQL文の結果行を保存するファイルの所要量を見積もる目的で，表3の各SQL文の結果行数を見積もった。
表3 設備の組合せを述語に指定したSQL文の例（未完成）
2.物件の設備に関する課題への対応
Kさんは，物件の設備に関する課題に対応するため，次の2案について長所及び短所を比較した結果，案Bを採用することにした。
案A “物件”テーブルにエアコン台数列を追加する。
案B 追加・変更するテーブルのテーブル構造を，図2に示すとおりにする。
・“設備”テーブルを追加する。
・図1に示した“物件”テーブルを“新物件”テーブルに置き換える。
・“物件設備”テーブルを追加する。
図2 追加・変更するテーブルのテーブル構造
設備コードは，全設備を一意に識別するコードで，そのうち20個は，“物件”テーブルの各設備列に対応させた。また，“設備”テーブルの設備名の列値に“物件”テーブルの設備列名をそのまま設定し，今後追加される設備名を含めて重複させないことに決めた。
3.テーブルの移行
Kさんは，追加・変更するテーブルへの移行を，次のような手順で行った。
（1）“設備”，“新物件”及び“物件設備”テーブルを定義した。
（2）“物件”テーブルから設備列20個を除いた全行を，“新物件”テーブルに複写した。
（3）“設備”テーブルに100個の設備を登録した。エアコン又はオートロックを登録するSQL文の例を，表4のSQL4に示す。
（4）“物件設備”テーブルには“物件”テーブルにある設備に限って行を登録した。エアコン又はオートロックがある行を登録するSQL文の例を，表4のSQL5に示す。ここで，設置済個数列に1を設定し，正確な個数を移行後に設定することにした。
（5）テーブルの統計情報を取得した。主な統計情報を表5に示す。
表4 “設備”テーブル又は“物件設備”テーブルに登録するSQL文の例（未完成）
表5 追加・変更したテーブルの主な統計情報（未完成）
〔テーブルの移行の検証〕
Kさんは，テーブルの移行を次のように検証し，新たなビューを定義した。
1.SQL文の検証
テーブルの移行の前後でSQL文が同じ結果行を得るか検証するため，移行前のSQL文（表3のSQL1，SQL2）に対応する移行後のSQL文を，それぞれ表6のSQL6，SQL7のとおりに設計した。そして，"1.物件の設備に関する調査”で保存したファイルを用いて，SQL1とSQL6の結果行，SQL2とSQL7の結果行がそれぞれ一致することを確認した。
表6 移行後のSQL文の例（未完成）
2.ビューの定義
Kさんは，“物件”テーブルの定義を削除した後でも実績のあるSQL文を変更することなく使いたいと考えている。そのために“物件”テーブルにあった沿線列かつ，エアコン列とオートロック列の両方を表示するビュー“物件”を図3のとおりに定義した。
図3 ビュー“物件”の定義（未完成）
設問1 〔物件の設備に関する調査及び課題への対応〕について，（1）〜（4）に答えよ。
（1）表3中の イ ， ロ に入れる適切な数値を， ハ 〜 ホ に入れる適切な字句を答えよ。ここで，沿線，エアコン，オートロックの列値の分布は互いに独立し，各列の列値は一様分布に従うと仮定すること。
解答・解説
解答例
イ：1,000
ロ：3,000
ハ：COUNT(*)
ニ：TOTAL
ホ：沿線,TOTAL
解説
ー
（2）“2.物件の設備に関する課題への対応”について，Kさんが採用した案Bの長所を一つ，本文中の用語を用いて，25字以内で具体的に述べよ。
解答・解説
解答例
・物件設備の入替処理が不要である。
・全設備の有無と個数の問合せに答えられる。
・将来，増える設備に対して行追加で対応できる。
解説
ー
（3）表4中の a ， c 及び d に入れる適切な字句を， b ， e に入れる一つの適切な述語を答えよ。
解答・解説
解答例
a：物件コード, 'A1',1
b：エアコン='Y'
c：UNION ALL
d：物件コード, 'A2',1
e：オートロック='Y'
解説
ー
（4）表5中の あ ， い に入れる適切な数値を答えよ。
解答・解説
解答例
あ：1,600,000
い：20
解説
ー
設問2 〔テーブルの移行の検証〕について（1）〜（3）に答えよ。
（1）表6中の f 〜 j に入れる適切な字句を答えよ。
解答・解説
解答例
f：INNER JOIN
g：INNER JOIN
h：・S1.設備名＝'エアコン'
・S1.設備コード＝'A1'
i：・S2.設備名＝'オートロック'
・S2.設備コード＝'A2'
j：・（S.設備名＝'エアコン' OR S.設備名＝'オートロック'）
・（S.設備コード＝'A1' OR S.設備コード＝'A2'）
解説
ー
（2）表6中のSQL7の選択リストにあるDISTINCTの目的は，結果行の重複を排除するためである。このSQL7で行が重複するのはどのような場合か。本文中の用語を用いて，30字以内で具体的に述べよ。
解答・解説
解答例
エアコンとオートロックの両方が設置されている場合
解説
ー
（3）図3中の k 〜 o に入れる適切な字句を答えよ。
解答・解説
解答例
k：・BS1.設備コード='A1'
・BS1.設備コード IS NOT NULL
l：'Y'
m：'N'
n：LEFT OUTER JOIN
o：BS1.設備コード='A1'
解説
ー
IPA公開情報
出題趣旨
業務を改善するために，データベースのテーブル構造を変更することがある。その場合，現行のテーブル構造から新しいテーブル構造に適切な手順で効率よく移行することが求められる。
本問では，RDBMS を用いた不動産賃貸仲介業の検索システムを題材として，テーブル構造の変更の妥当性を適切に評価した上で，検索に利用する SQL 文の変更方法，テーブルの移行手順，移行に利用する基本的な SQL 構文，及び移行のときに考慮すべきテーブルの統計情報を理解しているかを問う。
採点講評
問 3 では，物件情報検索システムを題材に，テーブルの移行及び SQL の設計について出題した。全体として正答率は平均的であった。
設問 1(3)は，正答率は平均的であったが，c に UNION ALL と解答すべきところを，AND 又は OR とする誤 答が散見された。二つの SELECT 文の結果行の和集合を求めていることに留意し，正答を導き出してほしい。 設問 1(4)では，追加した“物件設備”テーブルの物件コード及び設備コードの列値個数を，状況記述から読み取ることを求めたが，前者に比べて後者の正答率が低かった。テーブルの行数だけでなく列値個数も，テーブルの物理設計及び性能見積り，及び性能改善に欠かせない基本的な統計情報の一つなので，よく理解してほしい。
設問 2(1)は，正答率が平均的であった。表 3 の SQL1 及び SQL2 の WHERE 句中の述語を手掛かりに，h～j に入れる適切な述語を導くことができる。ただし，表 6 の SQL 文の構文中に設備コードを参照する場合，どのテーブルの設備コードを参照するかのあいまいさを排除するため，相関名で列名を修飾しなければならないことに注意してほしい。
設問 2(3)では，左外結合を選択できるかを問うたが，正答率が低かった。“3.テーブルの移行”において，エアコン又はオートロックがある物件を“物件設備”テーブルに登録していること，及びビュー“物件”のエアコン列又はオートロック列の列値が‘N’の行を求めることに留意し，正答を導き出してほしい。
