DBデータベーススペシャリスト試験午後Ⅰ

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

事例を読んで設問の答えを考えてから、解説のポイントとIPA公式の解答例・採点講評で確認しましょう。

問題

問3 変更履歴を記録するテーブルに関する次の記述を読んで，設問1～3に答えよ。 H銀行では，預金者へのサービス向上を図るために，顧客情報管理に使用している“顧客”テーブルの設計を見直すことになった。そのため，システム部のG部長の下にプロジェクトチームが組まれ，Fさんが設計を担当することになった。〔顧客情報管理の概要〕顧客情報管理の主な業務内容は，次のとおりである。 (1) 営業店担当者は，顧客からの依頼によって電話番号などの顧客属性情報を変更する。変更した顧客属性情報は，依頼当日から適用される。同一日に変更を取り消すことはない。 (2) 顧客ごとに設定した優遇レベルによって，ATM（現金自動預け払い機）の時間外利用手数料を割り引くなどのサービスを提供している。 (3) 優遇レベルは，新規登録時の預金額によって決定され，その後は預金残高などの利用状況に基づいて毎月末に決定される。優遇レベルが変更となる場合は，月末日の夜間バッチ処理によって新たな優遇レベルが設定され，翌月の1日から適用される。〔顧客情報管理に使用される主なテーブルの構造〕顧客情報管理に使用される主なテーブルの構造を図1に示す。各テーブルの主な列の意味及び制約は，表1に示すとおりである。図1 顧客情報管理に使用される主なテーブルの構造表1 顧客情報管理に使用されるテーブルの主な列の意味及び制約（一部省略）〔“顧客”テーブルの変更〕 1. “顧客”テーブルの構造の変更 Fさんは，顧客属性情報（顧客名，支店番号，郵便番号，住所，電話番号，優遇レベル）の変更を履歴として記録するために，“顧客”テーブルの構造を図2のように変更した。追加した列の意味及び制約は，表2に示すとおりである。図2 変更後の“顧客”テーブルの構造表2 変更後の“顧客”テーブルの列の意味及び制約（一部省略）変更後の“顧客”テーブルには，顧客属性情報の変更履歴が表3のように記録される。 (1) 顧客コードA111111 ① 2008年6月16日に新規の顧客コードA111111が追加された。 ② その日以降，顧客属性情報は変更されていない。 (2) 顧客コードB222222 ① 2007年3月1日に電話番号が変更された。変更連番が一つ前の行の適用終了日は，NULLから2007年2月28日に設定された。 ② 2007年11月15日に当該顧客との取引がなくなり，顧客コードが削除された。適用終了日にNULLの行がないことが，削除されたことを示している。 (3) 顧客コードC333333 ① 2009年1月15日に電話番号が変更された。 ② 2009年2月1日に優遇レベルが変更された。表3 変更後の“顧客”テーブルの行の例（一部省略） 2. 変更後の“顧客”テーブルへの照会 (1) ある顧客の現在日付の顧客属性情報を1行読み込むために，図3のようなSQL文を設計した。ここで，現在日付を表す予約語をCURRENT_DATEとする。図3 現在日付の顧客属性情報を1行読み込むSQL文 (2) ある顧客の属性情報について，優遇レベルが変更された日を調べる（例えば，表4のような結果行を求める）ために，図4のようなSQL文を設計した。表4 顧客の優遇レベルを調べた結果行の例図4 顧客属性情報の優遇レベルが変更された日を調べるSQL文 3. 顧客属性情報を先日付で変更する処理現在，月末日の夜間バッチ処理によって優遇レベルを設定しているが，新規顧客の増加に伴い，処理時間に余裕がなくなった。そこでH銀行では，毎月20日時点の預金残高などに基づいて優遇レベルを決め，優遇レベルが変更となる場合は，20日から月末日までのいずれかの日の夜間バッチ処理によって新たな優遇レベルを設定し，先日付となる翌月の1日から適用することにした。例えば，2009年5月20日に顧客コードC333333の優遇レベルを2から3に先日付で変更する場合，表5のように変更連番4の行を追加することにした。また，優遇レベル以外の顧客属性情報の変更についても，顧客からの変更依頼を受け付けた日（以下，変更受付日という）ではなく，変更の適用を開始すべき指定日を適用開始日列に設定することにした。しかし，顧客情報管理部門からは，“顧客属性情報の変更受付日を漏れなく記録したい”という要望が寄せられている。表5 “顧客”テーブルを2009年5月20日に先日付で変更する場合の行の例〔“取引履歴”テーブルの集計処理〕 Fさんは，“顧客”テーブルの構造を変更したことによって，これまで“取引履歴”テーブルと結合して顧客単位に集計処理を行っていたSQL文を見直した。例えば，2009年4月の支店・顧客別月間預入額を集計するSQL文を図5に示すように設計した。そして，テスト用の“取引履歴”，“口座”，“顧客”の各テーブルにテストデータをロードし，SQL文の実行結果を検証した。ここで，ISODATE()は，日付を表す文字列をDATE型に変換するユーザ定義関数とする。図5 Fさんが設計した支店・顧客別月間預入額を集計するSQL文〔変更後の“顧客”テーブルに関する指摘事項〕 G部長は，Fさんに対し，変更後の“顧客”テーブルに関して，次のように指摘した。 ① 同一顧客の適用期間は，連続していなければならない。すなわち，変更連番が1以外の場合の適用開始日は，変更連番が一つ前の行の適用終了日と連続していなければならない（日にちが抜けたり，重なったりしてはならない）。その制約条件を追加し，制約が守られているかどうかを検証するSQL文を設計すべきである。 ② 優遇レベルを先日付で変更する処理について，更に検討する必要がある。 ③ “顧客属性情報の変更受付日を漏れなく記録したい”という顧客情報管理部門からの要望にこたえていない。 ④ 図5の支店・顧客別月間預入額を集計するSQL文には，誤りがある。設問1 変更後の“顧客”テーブルを照会するSQL文について，(1)，(2)に答えよ。 (1) 図3のSQL文中の［ a ］，［ b ］に入れる適切な字句を答えよ。 (2) 図4のSQL文中の［ c ］，［ d ］に入れる適切な字句を答えよ。設問2 〔変更後の“顧客”テーブルに関する指摘事項〕①～③について，(1)～(4)に答えよ。 (1) 指摘事項①に対応するために，適用終了日がNULLでも削除日付でもない行のうち，適用期間が連続していない行を読み込むためのSQL文を設計したい。次のSQL文中の［ e ］，［ f ］に入れる適切な字句を答えよ。ここで，NEXT_DAY()は，引数とした日付の翌日付を求めるユーザ定義関数とする。なお，［ c ］には設問1の(2)と同じ答えが入る。 SELECT * FROM 顧客 X WHERE X.顧客コード=:顧客コード AND ( X.適用終了日 IS NOT NULL AND EXISTS (SELECT * FROM 顧客 Y WHERE ［ c ］ =Y.変更連番 AND ［ e ］ =Y.顧客コード AND NEXT_DAY(X.適用終了日) <> ［ f ］ )) (2) 指摘事項②を確認するために，顧客コードC333333の電話番号が2009年5月25日に変更された場合を想定して，“顧客”テーブルに次の表のように変更連番5の行を追加した。しかし，指摘事項①に対応していないので，図3のSQL文では，例えば，CURRENT_DATEが2009年5月25日であるとき，想定した結果を得られない。どのような結果になるのか，15字以内で述べよ。（表）変更連番5の行を追加した“顧客”テーブルの行 (3) 指摘事項②に対応するために，変更連番の列名を“適用順番”に，その意味を“適用開始日の順番”に変更し，指摘事項①の制約条件を追加した。このとき，顧客コードC333333の顧客属性情報の列値について，次の表中の［ g ］～［ j ］に入れる適切な字句を答えよ。（表）適用順番に変更した後の顧客コードC333333の行 (4) 指摘事項③に対応するために，(3)の変更を行った上で，変更受付日の列を追加し，その意味を次のように定義した。（表）変更受付日列の定義しかし，この変更受付日の列の追加だけでは，“顧客属性情報の変更受付日を漏れなく記録したい”という要望にこたえられない場合がある。どのような場合にこたえられないのか，35字以内で述べよ。設問3 〔変更後の“顧客”テーブルに関する指摘事項〕④について，答えよ。指摘事項④に対応するために，具体的なテストデータを用いて検討した。適用期間中，月に1回以上の預け入れが存在するA～Iの顧客について，図5のSQL文を利用して集計した月間預入額の結果を表に整理した。表中の顧客コード欄に該当する顧客コードをA～Iから選んですべて答えよ。該当する顧客コードがない場合は，空欄にすること。なお，SQL文の結果行が存在しなかった顧客の場合，月間預入額を0円とする。また，適用期間は連続していて，日にちが抜けたり，重なったりしていることはない。各顧客の支店番号，顧客コード，顧客名は変更されないものとする。（図）顧客A～Iの適用期間と4月1日・4月30日の関係（表）図5のSQL文を利用して集計した月間預入額の結果

図1 顧客情報管理に使用される主なテーブルの構造。支店，顧客，口座，取引種別，取引履歴の5テーブルの関係スキーマ。各テーブルの列を示し，主キーの列には下線が引かれている（例: 口座は支店番号，科目番号，口座番号が主キー）。

表1 顧客情報管理に使用されるテーブルの主な列の意味及び制約（一部省略）。支店，顧客，口座，取引履歴の各テーブルについて，支店番号，顧客コード，優遇レベル，科目番号，口座番号，預金残高，端末機番号，取引連番，取引日，取引種別番号，取引金額の意味と制約を示した表。

図2 変更後の“顧客”テーブルの構造。変更後の顧客テーブルの構造。主キーは顧客コードと変更連番で，顧客名，支店番号，郵便番号，住所，電話番号，優遇レベル，適用開始日，適用終了日の列を持つ。

表2 変更後の“顧客”テーブルの列の意味及び制約（一部省略）。追加した変更連番（1から始まり1ずつ増加し，抜けのない整数），適用開始日，適用終了日（未定の場合はNULL，顧客コード削除時は削除日付）の意味と制約を示した表。

表3 変更後の“顧客”テーブルの行の例（一部省略）。顧客A111111，B222222，C333333の行例。変更連番，電話番号，優遇レベル，適用開始日，適用終了日を示す。例としてC333333の変更連番3は333-2222，レベル2，2009-02-01開始，終了日NULL。

図3 現在日付の顧客属性情報を1行読み込むSQL文。SELECT * FROM 顧客 WHERE 顧客コード=:顧客コード AND 適用開始日<=CURRENT_DATE AND ( [a] OR [b] ) の形のSQL文。空欄a，bの枠を含む。

表4 顧客の優遇レベルを調べた結果行の例。優遇レベルを調べたSQLの結果行の例。顧客コードC333333，適用開始日2009-02-01，優遇レベル2の1行を示す。

図4 顧客属性情報の優遇レベルが変更された日を調べるSQL文。顧客X，顧客Yを自己結合し，X.顧客コード=Y.顧客コード，[c]=Y.変更連番，[d]<>Y.優遇レベルを条件にY.顧客コード，Y.適用開始日，Y.優遇レベルを求めるSQL文。空欄c，dを含む。

表5 “顧客”テーブルを2009年5月20日に先日付で変更する場合の行の例。C333333の変更連番1～4の行例。変更連番3の適用終了日は2009-05-31になり，変更連番4（333-2222，優遇レベル3，2009-06-01開始，終了日NULL）が追加されている。

図5 Fさんが設計した支店・顧客別月間預入額を集計するSQL文。取引履歴X，口座Y，顧客Zを結合し，取引種別番号=1，取引日が2009年4月中で，適用開始日が4月1日以降かつ適用終了日が4月30日以前またはNULLの行について，支店・顧客別にSUM(取引金額)を求めるSQL文。

（表）変更連番5の行を追加した“顧客”テーブルの行。C333333の変更連番1～5の行。変更連番4は333-2222，レベル3，2009-06-01開始・NULL。追加した変更連番5は333-3333，レベル2，2009-05-25開始・2009-05-31終了。変更連番3の終了日は2009-05-31のまま。

（表）適用順番に変更した後の顧客コードC333333の行。適用順番1～5の行。適用順番3は2009-02-01～2009-05-24。適用順番4（2009-05-25～05-31）の電話番号・優遇レベルが空欄g，h，適用順番5（2009-06-01～NULL）が空欄i，jになっている。

（表）変更受付日列の定義。顧客テーブルに追加する変更受付日列の意味。当該顧客の属性情報（優遇レベル以外）の変更依頼を受け付けた日付，又は優遇レベルを設定した日付と定義している。

（図）顧客A～Iの適用期間と4月1日・4月30日の関係。顧客A～Iの各行の適用期間を線分で示し，4月1日と4月30日を縦の点線で表した図。○は適用開始日，●は適用終了日，▶は適用終了日がNULLであることを示す注記付き。

（表）図5のSQL文を利用して集計した月間預入額の結果。月間預入額の結果4区分（正しい，多くなる，少なくなるが0円ではない，預け入れがあるのに0円）ごとに，該当する顧客コードを記入する空欄の顧客コード欄を持つ表。

解き方のヒントを見る

適用期間の定義に照らして条件を読む

履歴管理では、ある時点で有効な行がどれかを決める規則が設計の中心になります。照会や検証の条件を評価する前に、その設計が適用期間の始まりと終わりをどう定めているかを確かめ、条件がその定めと一致しているかを一つずつ照合しましょう。

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

焦点は、適用期間の境界を設計がどう表しているかという一点です。期間の端に当たる日付や、終わりがまだ決まっていない状態の表し方を自分の言葉で整理し、照会や検証の記述がその整理と矛盾しないかを確かめてください。

視点

履歴管理テーブルでは、同じ対象について時間の区切りごとに複数の行が存在します。ある時点にどの行が該当するかは、期間の端点を含めるかどうかという約束事で決まります。この約束事は設計ごとに定められるものです。まずその設計の約束事を確認し、それを基準に各記述を読むことが大切です。

確認ポイント

設計の説明から、期間の始まりと終わりの端点がそれぞれ含まれるかどうかを読み取れたか
終わりが未確定の期間がどのように表されているかを確認したか
各記述の条件を、読み取った期間の定義と一つずつ照らし合わせたか

正解と解説を見る

解説

適用開始日と適用終了日で変更履歴を管理する“顧客”テーブルを題材に、ある日付に有効な行の検索、自己結合による変化点の抽出、期間が連続しているかの検証、先日付変更で起きる期間の重なりや受付日の記録漏れ、既存の集計SQLで期間条件を誤った場合の影響を問う問題です。出題趣旨では、履歴特有のデータ整合性を考えた実装能力と、設計変更が既存SQLに与える影響を見極める能力が狙いとされています。

問題の全体像

H銀行では、“顧客”テーブルに変更連番・適用開始日・適用終了日を追加して、顧客属性情報の変更履歴を残すことにしました。適用終了日がNULLなら現在も有効な行で、顧客コードが削除されたときは削除日付が入ります。その後、優遇レベルを20日〜月末のバッチで設定し、翌月1日から適用する先日付変更を採り入れます。また、顧客情報管理部門から変更受付日を漏れなく記録したいという要望も出ます。G部長は、①適用期間の連続性、②先日付変更、③受付日の記録、④図5の集計SQLの誤り、の4点を指摘しました。出題趣旨によると、この問題の狙いは次の3点です。(1)履歴テーブルに求められる機能と運用要件を理解しているか。(2)履歴特有の整合性を考慮して実装できるか。(3)既存SQLへの影響を見極められるか。

設問1(1)(a)

図3は、現在日付に有効な1行を取り出すSQLです。条件『適用開始日<=CURRENT_DATE』はすでにあるので、残りは『適用終了日が今日以降』か『まだ終了していない（NULL）』かの判定です。公式解答例では、a を『適用終了日>=CURRENT_DATE』または『適用終了日=CURRENT_DATE』としています。先日付変更を導入する前は、変更は依頼当日から適用され、前の行の終了日は前日になります。削除の場合も終了日は削除日です。そのため、終了日が今日より後の日付になることはなく、『=』でも『>=』でも同じ結果になります。備考には『順不同』とあり、a と b は入れ替えて答えてもかまいません。

設問1(1)(b)

終了日が決まっていない最新の行は、適用終了日がNULLです。公式解答例では b を『適用終了日 IS NULL』としています。NULLは『=NULL』では比較できないので、必ず IS NULL と書きます。採点講評の画像でも、IS NULL と =NULL という語句が並んで取り上げられており、NULL比較の書き方が採点のポイントになっていることがうかがえます。これを a と OR でつなぐと、『今日時点で有効な行』を1行だけ取り出せます。

設問1(2)(c)

図4は“顧客”を X と Y の別名で自己結合し、同じ顧客の『ある行』と『その次の行』を比べるSQLです。変更連番は1から始まり、1ずつ増え、抜けがない（表2）ので、次の行は変更連番が1大きい行です。したがって、公式解答例の c は『X.変更連番 + 1』（=Y.変更連番）です。表3の C333333 では、変更連番2の行と3の行が対応します。

設問1(2)(d)

欲しいのは、優遇レベルが変わった行（Y側）の適用開始日です。前の行 X と次の行 Y の優遇レベルが異なる組を選べばよいので、公式解答例の d は『X.優遇レベル』（<>Y.優遇レベル）です。表3の C333333 では、変更連番2の優遇レベルは1、変更連番3は2です。そのため Y＝変更連番3の行が選ばれ、表4の『2009-02-01、2』という結果になります。電話番号だけが変わった変更連番1→2の組は、優遇レベルが同じなので除かれます。

設問2(1)(e)

期間の連続性を確かめるには、行 X と、同じ顧客の次の行 Y を対応させます。副問合せでは『[c]=Y.変更連番』で次の連番と対応させており、さらに同じ顧客であることを条件にする必要があります。そのため、公式解答例の e は『X.顧客コード』（=Y.顧客コード）です。X.適用終了日 IS NOT NULL と EXISTS を組み合わせているので、次の行がない削除行や最新行は対象外になります。これで『適用終了日がNULLでも削除日付でもない行』だけを検査できます。

設問2(1)(f)

期間が連続している状態とは、『X の適用終了日の翌日＝次の行 Y の適用開始日』が成り立つ状態です。この等式が成り立たない行を読み込めばよいので、NEXT_DAY(X.適用終了日) と比べる f は、公式解答例のとおり『Y.適用開始日』です。『<>』なので、日にちの抜けも重なりも同じように検出できます。

設問2(2)

追加後の C333333 の行で、CURRENT_DATE＝2009-05-25 のときに図3の条件を満たす行を数えます。

・変更連番3（2009-02-01〜2009-05-31）：開始日≦5/25、終了日≧5/25なので該当する。

・変更連番5（2009-05-25〜2009-05-31）：開始日＝5/25、終了日≧5/25なので該当する。

2行の期間が重なっているため、1行を読み込むはずの図3のSQLで複数行が返ります。公式解答例は『結果行が複数行になる。』または『SQL文の実行が失敗する。』です（1行を取得する前提の処理では、複数行が返ると実行エラーになるためです）。15字以内なら『結果行が複数行になる』程度にまとめます。

設問2(3)(g)

適用順番を『適用開始日の順番』とし、期間の連続性制約を加えます。すると、5/25の電話番号変更は3番目の期間（2/1〜5/31）を5/24で打ち切り、5/25〜5/31の新しい行を差し込む形になります。この行は、5/25からの新しい電話番号を反映します。したがって、公式解答例の g は『333-3333』です。

設問2(3)(h)

5/25〜5/31の期間の優遇レベルは、先日付変更（6/1適用）の前なので、まだ変わっていません。直前の期間と同じ値のままです。公式解答例の h は『2』です。

設問2(3)(i)

6/1からの行は、もともと表5の変更連番4（電話番号333-2222、優遇レベル3）でした。しかし、5/25の電話番号変更は6/1以降にも引き継ぐ必要があります。先日付の行をそのままにすると、6/1から古い電話番号に戻ってしまいます。そのため公式解答例の i は『333-3333』です。先日付の行がすでにある状態で属性を変更するときは、未来の行も更新しなければならないことが、この小問の要点です。

設問2(3)(j)

6/1からは、5/20に先日付で設定した新しい優遇レベルが適用されます。公式解答例の j は『3』です。この結果、6/1の行には『電話番号の変更（5/25受付）』と『優遇レベルの設定（5/20）』という2つの異なる変更が同居することになり、これが設問2(4)につながります。

設問2(4)

変更受付日の列は1行に1つしか持てません。公式解答例は次の2つを挙げています。

①同じ適用開始日に、変更受付日の異なる顧客属性情報が存在する場合。設問2(3)の6/1の行のように、優遇レベルの設定日と電話番号の受付日が1行にまとまり、どちらかの受付日が残らなくなります。

②先日付で設定した適用開始日より前に顧客コードを削除する場合。未来の行がなくなるか、その行の適用自体が成立しなくなり、先日付変更の受付日が記録されずに消えてしまいます。

答案では、35字以内で『1行に受付日が複数ある状況』または『先日付の行が無効になる状況』のどちらかを具体的に書きます。

設問3

図5の期間条件は『適用開始日≧4/1』かつ『適用終了日≦4/30 または NULL』です。4月に有効な行ではなく、4月中に開始した行を選ぶ条件になっているため、4月以前から有効な行が漏れます。さらに、取引日とその行の適用期間を対応させていません。そのため、条件を満たす行が1顧客に複数あると、その顧客の4月の預入が行の数だけ重複して合計されます。図の各顧客に当てはめると、次のようになります。

・A：条件を満たすのは4月中に開始した最新行の1行だけなので、正しい額になる。

・D：4月中に開始した1行だけなので、正しい額になる。

・F：開始が4/30より後で条件は満たすが、4月の預入はそもそもないため、0円で正しい。

・G：4月中に開始・終了した1行だけなので、正しい額になる。

・B：4月中に開始した行が2行あるため、預入額が2重に集計されて多くなる。

・E：4月中に開始した行が3行あるため、3重に集計されて多くなる。

・C、H：開始日が4/1より前なので、結果行がなく0円になる。

・I：終了日が4/30より後でNULLでもないので、結果行がなく0円になる。

公式解答例も、正しい＝A、D、F、G、多くなる＝B、E、少なくなるが0円ではない＝該当なし（空欄）、0円＝C、H、I としています。

学習のポイント

期間の検索条件は『開始日≦基準日の終わりかつ終了日≧基準日の始まり（またはNULL）』で書くこと、自己結合で次の行と比べる方法、先日付の行を後から追加・分割するときの整合性の保ち方、結合によって行が重複し集計値が膨らむ仕組みを重点的に学習しましょう。

出典：独立行政法人情報処理推進機構（IPA）平成21年度春期データベーススペシャリスト試験午後Ⅰ 問3（公式資料：問題冊子（PDF）／解答例／採点講評）

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

問題