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

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)より、求めるのは、「“月別売上B”テーブルの1店舗当たりの年間平均行数」です。表1より、以下の式で算出します。

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

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

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

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

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

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

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

    したがって、“月別売上”テーブルを用いても、“月別売上B”テーブルを用いても結果は同じです。
  • (コ)
    〔“月別売上”テーブルの構造の変更〕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~E5の電話番号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のため移行対象外となる

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

“社員連絡先”テーブルから“社員連絡先B”テーブルへのデータ移行イメージ

おわりに

今回は、平成29年度午後Ⅰ試験問3の解き方を紹介しました。この問題は、整合性制約をきちんと理解していれば、設問1(3)を除く全ての問題を正解できると思います。整合性制約や性能設計が得意な方はチャレンジする価値はあるでしょう。次回は平成29年度午後Ⅱ試験問1の解き方を紹介します。

NECマネジメントパートナー株式会社 人材開発サービス事業部
1993年日本電気株式会社入社。教育部門に所属し、データベース領域の教育(特にOracleデータベース)を担当。現在は情報システムの開発技術教育を担当し、要件定義や設計担当者を育成している。受講者が主体的に考え、楽しく学べる研修の提供により、高い評価をいただいている。

連載バックナンバー

Think ITメルマガ会員登録受付中

Think ITでは、技術情報が詰まったメールマガジン「Think IT Weekly」の配信サービスを提供しています。メルマガ会員登録を済ませれば、メルマガだけでなく、さまざまな限定特典を入手できるようになります。

Think ITメルマガ会員のサービス内容を見る

他にもこの記事が読まれています