令和3年度秋期 データベーススペシャリスト 午後Ⅰ 問3

移行前後で表す情報の対応を確かめる

表の構造を変える設計やSQLを検討するときは、変更前の表が表していた一つ一つの事実が、変更後の構造でどのように表されるかをひとつずつ対応付けて考えることが大切です。この観点に絞って、問われている箇所を確認しましょう。

この問題で問われていること

問題が扱っている処理や設計が、元の構造で持っていた情報を変更後の構造で同じ意味のまま扱えるかという点に注目します。各要素が、どの時点のどの構造を前提にしているかを整理して読み進めてください。

視点

同じ事実でも、列に並べて持つ構造と行に分けて持つ構造では、表し方が異なります。どちらが優れているかを先に決めつけず、ある事実が一方の構造では何か所に、どのような形で記録され、もう一方ではどう現れるかを書き出してみましょう。取り出す、書き換える、確かめるといった操作がどちらの構造を前提にしているかを意識すると、判断の根拠を自分で説明しやすくなります。

確認ポイント

• 変更前の構造で、一つの事実がどこにどのように記録されているかを自分の言葉で説明できるか
• その同じ事実が、変更後の構造ではどこにどのように現れるかを対応付けられるか
• 問われている処理や検証が、変更前と変更後のどちらの構造を前提にしているかを区別できているか

解説

設備の有無を20個の列で持つ“物件”テーブルを、“設備”・“新物件”・“物件設備”の3テーブルへ移行する問題です。問われるのは、列値分布を使った結果行数の見積り、GROUP BYと集約、UNION ALLを使った行への展開、移行後の列値個数、内結合による同じ結果の再現、DISTINCTが必要になる理由、そして左外結合とCASE式で旧テーブルと同じ形を返すビューの定義です。

問題の全体像

A社の検索システムでは、“物件”テーブルがエアコンやオートロックなど最大20個の設備の有無をY/Nの列で持っています。そのため、設備を100個まで増やしたい、設置済個数も知りたい、という課題に対応できません。Kさんは“設備”“新物件”“物件設備”の3テーブルに分ける案Bを採用し、移行用のSQL、移行後の検証SQL、旧テーブル名で参照できるビューを設計しています。出題趣旨では、テーブル構造の変更が妥当かを評価した上で、SQL文の変更方法、移行手順、基本的なSQL構文、移行時に考慮すべき統計情報を理解しているかを問うとしています。採点講評では、全体の正答率は平均的だったとしています。

設問1(1)(イ)

表2では、行数が1、600、000、沿線の列値個数が400、エアコンとオートロックの列値個数がそれぞれ2（Y/N）です。設問の条件は「各列が独立し、一様分布に従う」です。沿線が○△線の物件は1、600、000÷400＝4、000件です。そのうちエアコンが'Y'の割合は1/2、オートロックが'Y'の割合も1/2で、両方を満たすのは4、000×1/2×1/2＝1、000件です。公式解答例でも1、000です。

設問1(1)(ロ)

SQL2の条件は、エアコンとオートロックのどちらかが'Y'（OR条件）です。両方とも'N'になる割合は1/2×1/2＝1/4なので、少なくとも一方が'Y'になる割合は3/4です。沿線が○△線の4、000件に掛けて4、000×3/4＝3、000件になり、公式解答例も3、000です。

設問1(1)(ハ)

SQL3では、沿線ごとに「両方の設備がある物件数」を数え、それを全物件数で割って百分率にします。WHERE句で両方の設備がある行に絞った上でGROUP BYしているので、分子は各グループの行数を数える集約関数になります。公式解答例ではCOUNT(*)です。

設問1(1)(ニ)

分母の全物件数は、WITH句で定義したTEMPのTOTAL列（SELECT COUNT(*) FROM 物件 の結果）をCROSS JOINで各行に付けたものです。したがって分母はTOTALで、公式解答例もTOTALです。

設問1(1)(ホ)

沿線ごとに集計するので、GROUP BYには沿線が必要です。さらに、選択リストのFLOOR式ではTOTALを集約関数の外で使っています。集約問合せでは、集約関数の外で参照する列をグループ化列に含める必要があるため、TOTALもGROUP BYに加えます。公式解答例は「沿線、TOTAL」です。TOTALは1行しかないCTEの値なので、グループ数は沿線の数と同じ400のままで、表3の見積り結果行数400とも合います。

設問1(2)

案Aのエアコン台数列の追加は、エアコンの個数しか解決せず、設備を増やすたびに列を追加するテーブル定義の変更も続きます。案Bでは、設備を“設備”テーブルの行として持ち、物件ごとの設置状況と設置済個数を“物件設備”テーブルの行で持ちます。公式解答例は、①物件設備の入替処理が不要になる、②全設備の有無と個数の問合せに答えられる、③将来増える設備に行の追加で対応できる、の三つです。本文の用語（物件設備の入替処理、設置済個数、100個・将来増える可能性）と結び付け、このうち一つを25字以内で書きます。

設問1(3)(a)

SQL5は、INSERT INTO 物件設備(物件コード、 設備コード、 設置済個数) に対して、SELECTの結果を登録するINSERT文です。SQL4からエアコンの設備コードは'A1'と分かり、本文には設置済個数列に仮に1を設定するとあります。よって選択リストは「物件コード、 'A1'、 1」で、公式解答例と同じです。

設問1(3)(b)

“物件設備”テーブルには、“物件”テーブルで設置されている設備に限って行を登録します。1行目のSELECTはエアコン用なので、条件は「エアコン='Y'」です。公式解答例では、aとbの組とdとeの組は順不同とされています。

設問1(3)(c)

エアコンの行を作るSELECTとオートロックの行を作るSELECTの結果を合わせて一度に登録するため、和集合の演算子を使います。二つの結果は設備コードが'A1'と'A2'で異なり、重複しないので、重複排除をしないUNION ALLで足ります。公式解答例はUNION ALLです。採点講評では、ANDやORとする誤答が散見されたとし、二つのSELECT文の結果行の和集合を求めていることに留意するよう述べています。

設問1(3)(d)

2行目のSELECTはオートロック用です。SQL4からオートロックの設備コードは'A2'なので、選択リストは「物件コード、 'A2'、 1」になり、公式解答例と同じです。

設問1(3)(e)

オートロックが設置されている物件に限って登録するので、条件は「オートロック='Y'」です。公式解答例も同じです。

設問1(4)(あ)

列値個数は、その列に現れる異なる値の数です。“物件設備”テーブルの物件コードの列値個数は、物件設備に1行以上登録された物件の数になります。物件設備の物件コードは“新物件”（表5で1、600、000件、物件コードの列値個数も1、600、000）の物件を指すので、上限は1、600、000です。公式解答例では、この上限と同じ1、600、000としています。採点講評では、物件設備の物件コードと設備コードの列値個数を「状況記述から読み取ることを求めた」と述べています。ただし、問題冊子の本文には「全ての物件に1個以上の設備がある」とは書かれていません。2.(4)が示しているのは、20個の各設備がいずれかの物件に設置されていることです。この解説では、公式解答例の値と「列値個数は異なる値の数であり、新物件の物件コード数を超えない」という考え方を押さえることを重視します。

設問1(4)(い)

移行手順(4)では、“物件設備”テーブルに“物件”テーブルにある設備に限って行を登録するとしています。そのため、登場し得る設備コードは“物件”テーブルの設備列に対応する20個に限られます。本文2.(4)には「記録されている20個の設備について、どの設備もいずれかの物件に設置されている」とあるので、20個の設備コードはどれも少なくとも1行は登録されます。したがって列値個数は20で、公式解答例と一致します。“設備”テーブルには100個が登録されていますが、残り80個の設備は物件設備にまだ行がありません。この点に注意が必要です。採点講評では、(4)は物件コードより設備コードの正答率が低かったとし、列値個数は物理設計や性能見積りに欠かせない基本的な統計情報であると述べています。

設問2(1)(f)

SQL6とSQL7は、SQL1とSQL2と同じ結果行を得るためのSQLです。つまり、指定した設備の行が物件設備にある物件だけを残せばよいので、結合相手がない物件を除く内結合を使います。公式解答例ではINNER JOINです。

設問2(1)(g)

物件設備と設備を結合して設備名などで絞り込む部分も、条件に合う設備がある行だけを残せばよいので内結合です。公式解答例ではgもINNER JOINです。

設問2(1)(h)

SQL6では、物件設備と設備をBS1/S1とBS2/S2として2回結合し、エアコンとオートロックの両方がある物件を求めます。S1側の条件はエアコンで、公式解答例は「S1.設備名='エアコン'」または「S1.設備コード='A1'」です。採点講評では、設備コードを参照する場合、どのテーブルの列かのあいまいさを排除するため、相関名で修飾しなければならないと注意しています。

設問2(1)(i)

S2側の条件はオートロックで、公式解答例は「S2.設備名='オートロック'」または「S2.設備コード='A2'」です。hとiは順不同とされています。二つの内結合をそれぞれ別の設備で絞り込むことで、SQL1のAND条件（両方がある）を表しています。

設問2(1)(j)

SQL7はSQL2（エアコン又はオートロック）に対応し、物件設備と設備を1回だけ結合します。そのため、条件はどちらかの設備に当たるOR条件になり、前のANDとの優先順位を保つために括弧で囲みます。公式解答例は「(S.設備名='エアコン' OR S.設備名='オートロック')」または「(S.設備コード='A1' OR S.設備コード='A2')」です。

設問2(2)

SQL7では、1物件について条件に合う物件設備の行ごとに結合結果が1行できます。エアコンとオートロックの両方が設置されている物件は、'A1'の行と'A2'の行の2行と結合するため、同じ物件コード・物件名が2行出力されます。これを1行にまとめるのがDISTINCTの目的です。公式解答例は「エアコンとオートロックの両方が設置されている場合」で、30字以内にこの要素を入れます。

設問2(3)(k)

ビューでは、BS1をエアコン（'A1'）の行に限って外結合します。そのため、BS1の行が結合できたかどうか（BS1側がNULLでないかどうか）で、エアコンの有無を判定できます。公式解答例は「BS1.設備コード='A1'」または「BS1.設備コード IS NOT NULL」です。

設問2(3)(l)

旧“物件”テーブルの設備列は、表1のとおり設置ありを'Y'で表していました。既存のSQL文をそのまま使えるように、設置ありのときは'Y'を返します。公式解答例は'Y'です。

設問2(3)(m)

設置なしの場合は、旧テーブルと同じく'N'を返します。公式解答例は'N'です。旧テーブルでは全列にNOT NULL制約がありました。ELSEでNULLではなく'N'にすることで、既存SQLの「= 'N'」などの条件がそのまま働きます。

設問2(3)(n)

“物件設備”テーブルには、設置されている設備の行しか登録されていません。内結合にすると、エアコンやオートロックがない物件がビューから消えてしまいます。全物件を残し、相手がない場合にNULLを補って'N'と判定するには、左外結合が必要です。公式解答例はLEFT OUTER JOINです。採点講評では、この左外結合を選択できるかを問うたが正答率は低かったとし、エアコン又はオートロックがある物件だけを登録していることと、列値が'N'の行も求めることに留意するよう述べています。

設問2(3)(o)

BS1をエアコンの行に限定する条件は、WHERE句ではなく外結合のON句に書きます。WHERE句に書くと、結合できずNULLになった行が除外されて内結合と同じ結果になります。公式解答例は「BS1.設備コード='A1'」です。網掛けになっているBS2側も、同じ考え方でオートロックの行に限定する条件になります。

学習のポイント

繰り返し列を行に分ける正規化型の移行で、結合の種類（内結合・左外結合）、UNION ALL、列値個数の読み取り方を確実に押さえることが重点です。