平成29年度午後Ⅰ試験問3：テーブルおよびSQLの設計に関する問題

連載 [第3回] :

2018年3月5日(月)

はじめに

今回は、平成29年度午後Ⅰ試験問3のテーブルおよびSQLの設計に関する問題の解き方について解説します。この問題で必要となる基礎知識は整合性制約と性能設計、およびSQLです。頻出問題ではないので、試験対策として学習する上での優先度は低いですが、このような問題も出ることがあることを理解しておくと良いでしょう。

平成29年度午後Ⅰ試験問3に挑戦！

それでは、早速平成29年度午後Ⅰ試験問3に挑戦しましょう。問題文はこちらからダウンロードしてください。また、解答用紙はこちらに用意しました。解答例はこちらをご覧ください。

設問1の解説

(1)分析処理に関する記述中の穴埋め問題

(ア)(イ)
〔システムの概要〕1.に「また，索引のキー順に，ページ単位で順次またはランダムに磁気ディスク装置からバッファに読み込まれる」と記載されています。また、〔“月別売上”テーブルの構造の変更〕1.に「“月別売上”テーブルには，行が主索引のキー順にロードされている。（中略）“月別売上B”テーブルに主索引のキー順にロードした」と記載されています。これらのことから、表1　“月別売上”テーブルと“月別売上B”テーブルにおいて、複数行を索引のキー順に読み込む場合、アクセス経路が主索引の場合はページを順次に読み込みます。その一方で、アクセス経路が副次索引の場合は副次索引の順にデータがロードされていないため、ランダムアクセスとなります。したがって、(ア)は「主」、(イ)は「副次」となります。
(ウ)
〔“月別売上”テーブルの構造の変更〕3.の分析処理1に「指定した1店舗について，任意の1年間の売上データを分析する」と記載されています。つまり、店舗別、年別の売上データを分析することになります。〔“月別売上”テーブルの構造の変更〕3.(2)より、求めるのは「分析に必要な“月別売上”テーブルの1店舗当たりの年間平均行数」です。表1より以下の式で算出します。

(“月別売上”テーブルの全行数)÷(店舗の列値個数)÷(売上年月の列値個数÷1年の月数)
＝360,000,000÷200÷(60÷12)=360,000

したがって、(ウ)は「360,000」となります。
(エ)
〔“月別売上”テーブルの構造の変更〕3.(2)より、求めるのは(ウ)の行数を読み込むときにアクセスする必要があるページ数です。表1より以下の式で算出します。

(“月別売上”テーブルの1店舗当たりの年間平均行数)÷(1ページ当たりの行数)
＝360,000÷100=3,600

したがって、(エ)は「3,600」となります。
(オ)
〔“月別売上”テーブルの構造の変更〕3.(2)より、求めるのは、「“月別売上Ｂ”テーブルの1店舗当たりの年間平均行数」です。表1より、以下の式で算出します。

(“月別売上Ｂ”テーブルの全行数)÷(店舗の列値個数)÷(売上年の列値個数)
＝(360,000,000÷12)÷200÷(60÷12)=30,000

したがって、(オ)は「30,000」となります。
なお、“月別売上Ｂ”テーブルの全行数を“月別売上”テーブルの全行数÷12としたのは、図1、図2より“月別売上”テーブルの12か月分が“月別売上Ｂ”テーブル1件となるためです。
(カ)
〔“月別売上”テーブルの構造の変更〕3.(2)より、求めるのは(オ)の行数を読み込むときにアクセスする必要があるページ数となります。また、〔“月別売上”テーブルの構造の変更〕3.(1)に「アクセス経路が主索引のとき，ページは順次に読み込まれる」と記載されています。このことから、主索引を用いてデータを読み込む場合、読み込む行数を1ページ当たりの行数で割れば読み込むページ数を算出できます。分析処理で使用する検索条件は売上年月と店舗コードであることから、分析処理1では主索引を使用できます。表1より、以下の式で算出します。

(“月別売上Ｂ”テーブルの1店舗当たりの年間平均行数)÷(1ページ当たりの行数)
＝30,000÷30=1,000

したがって、(カ)は「1,000」となります。
(キ)(ク)
図２より、“月別売上Ｂ”テーブルには同一売上年のデータが同一行に格納されています。そのため、分析対象の1年間のデータが年をまたぐ場合、2年間分のデータを読み込む必要があります。このとき、読み込むページ数は(カ)の2倍となるため、(キ)は「2」、(ク)は「2,000」となります。
(ケ)
〔“月別売上”テーブルの構造の変更〕3.の分析処理2に「指定した1商品について，任意の月の売上データを分析する」と記載されています。つまり、商品別、月別の売上データを分析することになります。〔“月別売上”テーブルの構造の変更〕3.(3)より、求めるのは「分析に必要な“月別売上”テーブルの1商品当たりの月間平均行数」です。表1より、以下の式で算出します。

(“月別売上”テーブルの全行数)÷(商品の列値個数)÷(売上年月の列値個数)
＝360,000,000÷30,000÷60=200

したがって、(ケ)は「200」となります。
ちなみに、「分析に必要な“月別売上Ｂ”テーブルの1商品当たりの月間平均行数」を求めると、以下の式になります。

(“月別売上Ｂ”テーブルの全行数)÷(商品の列値個数)÷(売上年の列値個数)
＝30,000,000÷30,000÷5=200

したがって、“月別売上”テーブルを用いても、“月別売上Ｂ”テーブルを用いても結果は同じです。
(コ)
〔“月別売上”テーブルの構造の変更〕3.(1)に「アクセス経路が副次索引の場合，1行当たり1ページがランダムに読み込まれる」と記載されています。分析処理2は検索条件が売上年月と商品コードなので、検索時に使える索引は副次索引となります。したがって、検索対象の行数がアクセスするページ数となり、(コ)は「200」となります。

(2)同じ年の二つの月の売上額の差を求めるSQLの構文

(a)
この例では、2017年1月と2017年2月の売上額の差を求めます。SQL2は“月別売上B”テーブルを使用しています。したがって、(a)は「売上額2月－売上額1月」となります。
(b)
検索条件は「売上年が2017年」となります。ここで注目すべき内容は、図2の注記「売上年列のデータ型はYYYY形式のCHAR(4)型」です。ここで、(b)を「売上年=2017」としたくなるところですが、これではデータ型が一致しません。したがって、(b)は「売上年=’2017’」となります。
また、売上年を動的に指定できるように、プレースホルダーを用いることも可能です。この場合、(b)の別解は「売上年=？」となります。

(3)動的SQLを使用する理由
表2のSQL1の場合、二つの月の売上額の差を求めるために、“月別売上”テーブルの検索条件として、売上年月列の値をホスト変数で指定すれば良かったのですが、SQL2においては、比較する売上年月によって設問1(2)(a)の選択列を動的に変更しなければなりません。これは動的SQLを使わないと実現できないため、解答は「比較する売上年月ごとに選択リストの列名を変更しなければならないから」となります。

設問2の解説

(1)“社員連絡先B”テーブルの電話番号列にNOT NULL制約を定義した理由
設問文に「本文中の字句を用いて25字以内で述べよ」と記載されているので、本文中から該当する語句を探します。〔“社員連絡先”テーブルの構造の変更〕1.の問題3に「電話番号が設定されている場合だけ行を登録すべきところ」と記載があるので、これを参考に解答は「電話番号が設定されている場合だけ行を登録したいから」とします。

(2)“社員連絡先B”テーブルの一意性制約を定義すべき列名または列名の組合せ
〔“社員連絡先”テーブルの構造の変更〕3.の要件として、「要件1　問題1～3を解決すること」「要件2　社員1人当たりの電話番号を3個以上登録できること，なお，同じ電話番号が複数の社員で使われることがある」があります。

まず、要件1を満たすことを検討します。〔“社員連絡先”テーブルの構造の変更〕1.の問題1～3のうち、一意性制約に関する記述を見ると、問題1に「電話番号1列と電話番号2列は，異なる電話番号であるべきところ，同じ電話番号が設定されている行があった」と記載されています。これは、同一社員のデータにおいて同一電話番号が複数設定されていることが問題であることを意味しています。そこで、同一社員において同一電話番号が複数設定されないように一意性制約を定義します。

次に、要件2を満たすことを検討します。この要件のうち、一意性制約に関する内容は「同じ電話番号が複数の社員で使われることがある」という記載です。このことから、電話番号列のみに一意性制約を定義してはならないことがわかります。

以上のことから、解答は、「社員ID,電話番号」または「電話番号,社員ID」となります。

(3)“社員連絡先“テーブルに定義すべきだった検査制約
検査制約とは、テーブルに登録可能なデータに条件を設けるための制約条件のことです。図3の検査制約1行目の後半部分「電話番号1 電話番号2」は、〔“社員連絡先”テーブルの構造の変更〕1.の問題1の「電話番号1列と電話番号2列は，異なる電話番号であるべきところ，同じ電話番号が設定されている行があった」を解決するための記述ですが、この「電話番号1 電話番号2」を満たすための大前提として、電話番号1と電話番号2がともに非NULL値でなければなりません。したがって、(c)と(d)は「電話番号1 IS NOT NULL」と「電話番号2 IS NOT NULL」が該当します。

次に、問題2に記載されている「電話番号1列だけに電話番号を設定すべきところ，電話番号1列にNULLが，電話番号2列に電話番号が設定されている行があった」との記載があります。この問題を解決するために、前述の条件とは別に、以下の条件を指定する必要があります。

電話番号1が非NULL値であること
電話番号2がNULLであること

設問には「(c)～(e)に入れる述語を，①～④の中からそれぞれ重複なく一つずつ選んで答えよ」と記載されています。したがって、(c)は「①」の「電話番号1 IS NOT NULL」、(d)は「③」の「電話番号2 IS NOT NULL」、(e)は「④」の「電話番号2 IS NULL」とします。この解答により、問題3の「電話番号が設定されている場合だけ行を登録すべきところ，電話番号1列と電話番号2列の両方にNULL列が設定されている行があった」にも対応できます。

(4)“社員連絡先B“テーブルに行を移行するINSERT文
図4で使用する3つのSELECT文で、〔“社員連絡先”テーブルの構造の変更〕1.の問題1～3を解決した内容にするためには、以下のようにする必要があります。

1文目：電話番号1がNULLでない場合、電話番号1の値を表示順1に指定する

2文目：電話番号1と異なる電話番号2の値を表示順2に指定する

3文目：電話番号1がNULLで電話番号2が非NULL値の場合、電話番号2の値を表示順1に指定する

1文目の条件を満たすためには、WHERE句に「電話番号1 IS NOT NULL」を指定します。また、3文目の条件を満たすためには、WHERE句に「電話番号1 IS NULL」と「電話番号2 IS NOT NULL」を指定します。したがって、解答は(f)が「①」、(g)と(h)は「②」と「③」となります（gとhは順不同）。

(5)表4の完成
社員IDがE1～E５の電話番号1と電話番号2を図4の3つのSELECT文に当てはめ、移行対象行かどうかを評価します。

社員番号E1の電話番号1：1文目に合致するため移行対象となる

社員番号E1の電話番号2：2文目に合致するため移行対象となる

社員番号E2の電話番号1：1文目に合致するため移行対象となる

社員番号E2の電話番号2：電話番号1と一致するため移行対象外となる

社員番号E3の電話番号1：1文目に合致するため移行対象となる

社員番号E3の電話番号2：NULLのため移行対象外となる

社員番号E4の電話番号1：NULLのため移行対象外となる

社員番号E4の電話番号2：3文目に合致するため移行対象となる

社員番号E5の電話番号1：NULLのため移行対象外となる

社員番号E5の電話番号2：NULLのため移行対象外となる

この内容を図で表すと、以下の通りになります。