平成29年度春期 データベーススペシャリスト 午後Ⅰ 問3

変更前後の差分を軸に設計を検討する

構造変更を伴う設計の問題では、個々の用語の知識より先に、変更前と変更後で何が変わり何が変わらないのかを問題の記述から整理することが大切です。その差分を起点に、問われている箇所への影響を一つずつ確かめましょう。

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

確認すべき観点は一つ、変更によって前提が崩れる箇所を特定することです。記述中の条件や制約が変更後も成り立つかを追い、その前提に依存している処理や設計判断がどこかを結び付けて考える姿勢が求められます。

視点

データベースの設計は、ある時点の構造やデータの状態を前提にして組み立てられています。構造を変更すると、その前提に依存していた部分が暗黙のうちに影響を受けることがあります。一般的な検討手順としては、まず変更の内容を正確に把握し、次にその変更に依存する要素を洗い出します。最後に、それぞれの要素について前提が維持されるかを問題の記述に照らして確かめます。自分の知識や一般論だけで判断せず、記述に示された条件を根拠にすることが重要です。

確認ポイント

• 変更前の構造・条件と変更後の構造・条件を、問題の記述から対応付けて整理できているか
• 問われている箇所が、変更によって失われる前提や新たに加わる前提に依存していないかを確かめたか
• 判断の根拠を一般論ではなく、問題中に示された条件や記述に求めているか

解説

繰返し項目を横持ちにするか縦持ちにするかという判断を題材にした問題です。月別売上テーブルを横持ちにすると、統計情報と索引のキー順から読込みページ数が変わることを見積もり、年月が列名になるため動的SQLが必要になることを押さえます。社員連絡先テーブルでは、横持ちのままだと検査制約で問題1～3を防ぐ必要があり、縦持ちに変えるならNOT NULL制約と一意性制約を定義したうえで、UNIONを使ってデータを移行します。この一連の流れを追う問題です。

問題の全体像

A社の販売分析支援システムで、Fさんが二つのテーブルを見直す事例です。要望1（照会の応答時間の改善）には、月別に1行ずつ持つ“月別売上”を、1年分の12か月を列として持つ横持ちの“月別売上B”に変えて対応します。要望2（電話番号を3個以上登録したい）には、電話番号1・電話番号2を列として持つ横持ちの“社員連絡先”を、表示順ごとに1行を持つ縦持ちの“社員連絡先B”に変えて対応します。出題趣旨では、横持ちと縦持ちそれぞれの長所・短所・制約を理解したうえでテーブルとSQLを設計する能力、テーブル構造の変更に伴うデータ移行を行う能力を問うとしています。採点講評では、全体として正答率は高かったものの、設問1(1)のケ・コと設問1(3)でつまずいた受験者が多かったと述べています。

設問1(1)(ア)

本文では、“月別売上”の行は主索引のキー順にロードされ、“月別売上B”にも主索引のキー順にロードしたと述べています。行の物理的な並びが主索引のキー順と一致しているので、主索引のキー順に複数行を読むと、隣り合うページを順番に読み込むことになります。公式解答例では「主」です。

設問1(1)(イ)

表1の副次索引は、売上年月（売上年）、商品コード、店舗コードの順にキーが定義されており、主索引の列の順番（年月、店舗、商品）とは異なります。このため副次索引のキー順は行の物理的な並びと一致せず、1行ごとに別のページをランダムに読み込むことになります。公式解答例では「副次」です。

設問1(1)(ウ)

表1で“月別売上”は360、000、000行あり、売上年月は60、店舗コードは200、商品コードは30、000の値を持ちます。60×200×30、000＝360、000、000なので、全ての組合せに1行ずつ存在すると読み取れます。1店舗の1年間分は、30、000商品×12か月＝360、000行です。公式解答例では360、000です。

設問1(1)(エ)

主索引の先頭列は売上年月、2番目の列は店舗コードです。そのため、1店舗の行が連続して並ぶのは同じ月の中だけで、1か月分30、000行が連続し、それが12か月分に分かれています。1ページ当たりの行数は100行なので、1か月分で300ページ、12か月で3、600ページを最小限読み込む必要があります。公式解答例では3、600です。

設問1(1)(オ)

“月別売上B”は売上年、店舗コード、商品コードの組合せごとに1行を持ち、12か月分の売上額と販売数を1行の列として保持します。年をまたがない1年間であれば、1店舗につき商品数と同じ30、000行です。公式解答例では30、000です。

設問1(1)(カ)

主索引は売上年、店舗コード、商品コードの順なので、同じ年の1店舗分30、000行は連続して格納されています。1ページ当たり30行なので、30、000÷30＝1、000ページを順次に読み込めば足ります。公式解答例では1、000です。行数だけでなくページ数も“月別売上”の3、600ページから減る点が、横持ちの長所です。

設問1(1)(キ)

例えば2016年4月から2017年3月のように指定した1年間が年をまたぐと、売上年が異なる二つの行にデータが分かれます。このため2年分の行を読み込む必要があります。公式解答例では2です。

設問1(1)(ク)

1年分は1、000ページなので、2年分では1、000×2＝2、000ページになります。公式解答例では2、000です。横持ちでは、分析する期間が行の区切り（年）と合わないと、読込み量が増えるという短所があります。

設問1(1)(ケ)

分析処理2は、1商品について任意の1か月分を分析します。“月別売上”では売上年月と商品コードを指定すると、店舗数と同じ200行になります。“月別売上B”でも売上年と商品コードを指定すると、店舗ごとに1行ずつの200行になります。公式解答例では200です。

設問1(1)(コ)

この処理では、年月（年）と商品コードが先頭にある副次索引を使います。副次索引のアクセスでは1行当たり1ページがランダムに読み込まれるので、200行に対して最大200ページを読み込みます。公式解答例では200です。採点講評では、アとイは正しく解答していながらケとコを誤った受験者が多く、ランダムにページを読み込む索引の理解が不十分だったと指摘しています。

設問1(2)(a)

SQL1は、自己結合を使って2017年2月の売上額（Y）から2017年1月の売上額（X）を引いています。“月別売上B”では同じ年の月別の売上額が1行の中に列として並んでいるので、結合せずに列どうしの差を求めれば同じ結果になります。公式解答例では「売上額2月 - 売上額1月」です。引く順番をSQL1（Y−X）と同じにすることに注意してください。

設問1(2)(b)

行を絞り込む条件は対象の年だけです。売上年はCHAR(4)型なので、公式解答例では「売上年 = '2017'」、またはパラメタを使った「売上年 = ?」としています。

設問1(3)

縦持ちの“月別売上”では、年月は売上年月列の値だったので、ホスト変数で指定できました。横持ちの“月別売上B”では、月は売上額1月、売上額2月のような列名に変わります。列名はホスト変数では指定できないため、静的SQLでは比較する年月の全ての組合せをあらかじめ用意しなければなりません。公式解答例では「比較する売上年月ごとに選択リストの列名を変更しなければならないから」としています。答案では、比較する年月によって選択リストの列名が変わる点を40字以内でまとめます。採点講評でも、年月が列値から列名に変わること、動的SQLなら実行の都度列名を変えて対応できることを理解してほしいと述べており、正答率が低かった設問です。

設問2(1)

問題3には「電話番号が設定されている場合だけ行を登録すべき」とあります。縦持ちの“社員連絡先B”では、1行が電話番号1件に対応するので、電話番号列がNULLの行は登録してはいけません。公式解答例では「電話番号が設定されている行だけを登録したいから」としています。本文中の字句を使い、25字以内で書きます。採点講評でも、問題3から、電話番号が設定されていない社員は“社員連絡先B”に登録されないことを読み取ってほしいと述べています。

設問2(2)

主キーは{社員ID、 表示順}なので、同じ社員が同じ電話番号を別の表示順で登録することは主キーでは防げず、問題1に当たる状態が起こり得ます。一方、本文には同じ電話番号が複数の社員で使われることがあるとあるので、電話番号だけに一意性制約を定義することはできません。公式解答例では「社員ID，電話番号」（順序は逆でもよい）です。

設問2(3)(c)

問題2と問題3から、電話番号1がNULLの行は、電話番号2の値にかかわらず許されません。したがって、ORでつないだ二つの条件の両方に「電話番号1 IS NOT NULL」が必要です。公式解答例ではc＝①です。

設問2(3)(d)

一つ目の条件は、電話番号が2件ある場合を表します。電話番号2も値を持ち、かつ電話番号1と異なる必要があるので（問題1の防止）、「電話番号2 IS NOT NULL」を指定します。公式解答例ではd＝③です。

設問2(3)(e)

二つ目の条件は、電話番号が電話番号1の1件だけの場合を表すので、「電話番号2 IS NULL」を指定します。公式解答例ではe＝④です。これで、許される状態は、電話番号が2件で互いに異なる場合と、電話番号1だけに値がある場合の二つに限られます。

設問2(4)(f)

図4の一つ目のSELECTは、電話番号1を表示順1として移行します。NOT NULL制約に違反しないよう、電話番号1が値を持つ行だけを対象にします。公式解答例ではf＝①です。

設問2(4)(g)

三つ目のSELECTは、電話番号2を表示順1として移行します。これは問題2の行、つまり電話番号1がNULLで電話番号2だけに値がある行を正しい形に直す処理です。そのため、条件の一つは「電話番号1 IS NULL」になります。公式解答例ではgとhは順不同で、②と③です。

設問2(4)(h)

もう一つの条件は「電話番号2 IS NOT NULL」です。これがないと、問題3の行（両方ともNULL）の電話番号2がNULLのまま移行されてしまいます。公式解答例ではgとhは順不同で、②と③です。

設問2(5)

表3の各行を、図4の三つのSELECTの条件に当てはめます。E1（1111、3333）は、一つ目のSELECTで（E1、 1、 1111）、二つ目のSELECTで（E1、 2、 3333）になります。E2（2222、2222）は、一つ目のSELECTで（E2、 1、 2222）になります。二つ目のSELECTの条件「電話番号1 <> 電話番号2」は2222と2222が等しいので偽になり、表示順2の行は作られません。つまり、E2が1行だけになるのは移行SQLの条件によるもので、一意性制約が行をまとめたわけではありません。E3（3333、NULL）は、一つ目のSELECTで（E3、 1、 3333）になります。二つ目のSELECTは、NULLとの比較が不明になるので選択されません。E4（NULL、4444）は、一つ目と二つ目のSELECTでは選択されず、三つ目のSELECTで（E4、 1、 4444）になります。E5（NULL、NULL）は、どのSELECTにも選択されません。公式解答例では、E1-1-1111、E1-2-3333、E2-1-2222、E3-1-3333、E4-1-4444の5行で、表4の最後の1行は空欄です。E1とE3が同じ3333を持っていても、社員IDが異なるので{社員ID、 電話番号}の一意性制約には違反しません。

学習のポイント

索引のキー順と物理的な行の並びの関係から読込みページ数を見積もる手順、横持ち・縦持ちそれぞれの長所と短所、NULLを含む述語の評価、移行SQLを行ごとに追って確かめる方法を重点的に学習してください。