平成21年 春期 DB 午後Ⅰ 問3

変更履歴を記録するテーブルに関する次の記述を読んで、設問1~3に答えよ。

H銀行では、預金者へのサービス向上を図るために、顧客情報管理に使用している”顧客”テーブルの設計を見直すことになった。そのため、システム部のG部長の下にプロジェクトチームが組まれ、Fさんが設計を担当することになった。

[顧客情報管理の概要]
顧客情報管理の主な業務内容は、次のとおりである。
(1)営業店担当者は、顧客からの依頼によって電話番号などの顧客属性情報を変更する。変更した顧客属性情報は、依頼当日から適用される。同一日に変更を取り消すことはない。

(2)顧客ごとに設定した優遇レベルによって、ATM(現金自動預け払い機)の時間外手数料を割り策などのサービスを提供している。

(3)優遇レベルは、新規登録時の預金額にいよって決定され、その後は預金残高などの利用状況に基づいて毎月末に決定される。優遇レベルが変更となる場合は、月末日の夜間バッチ処理によって新たな優遇レベルが設定され、翌月の1日から適用される。

[顧客情報管理に使用される主なテーブルの構造]
顧客情報管理に使用される主なテーブルの構造を図1に示す。各テーブルの主な列の意味および制約は、表1に示すとおりである。




[”顧客”テーブルの変更]
1.”顧客”テーブルの構造の変更
Fさんは、顧客属性情報(顧客名、支店番号、郵便番号、住所、電話番号、優遇レベル)の変更を履歴として記録するために、”顧客”テーブルの構造を図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日に優遇レベルが変更された。


2.変更後の”顧客テーブルへの照会”
(1)ある顧客の現在日付の顧客属性情報を1行読み込むために、図3のようなSQL文を設計した。ここで、現在日付を表す予約語をCURRENT_DATEとする。

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

(2)ある顧客の属性情報について、優遇レベルが変更された日を調べる(たとえば、表4のような結果行を求める)ために、図4のようなSQL文を設計した。



3.顧客属性情報を先日付で変更する処理
現在、月末日の夜間バッチ処理によって優遇レベルを設定しているが、新規顧客の増加に伴い、処理時間に余裕がなくなった。そこでH銀行では、毎月20日時点の預金残高などに基づいて優遇レベルを決め、優遇レベルが変更となる場合は、20日から月末日までのいずれかの日の夜間バッチ処理によって新たな優遇レベルを設定絵師、先日付となる翌日の1日から適用することにした。たとえば、2009年5月20日に顧客コードC333333の優遇レベルを2から3に先日付で変更する場合、表5のように変更連番4の行を追加することにした。 また、優遇レベル以外の顧客属性情報の変更についても、顧客からの変更依頼を受け付けた日(以下、変更受付日という)ではなく、変更の適用を開始すべき指定日を適用開始日列に設定することにした。しかし、顧客情報管理部門からは、”顧客属性情報の変更受付日を漏れなく記録したい”という要望が寄せられている。



[”取引履歴”テーブルの集計処理]
Fさんは、”顧客”テーブルの構造を変更したことによって、これまで”取引履歴”テーブルと結合sh知恵顧客単位に集計処理を行っていたSQL文を見直した。
たとえば、2009年4月の視点・顧客別月間預入額を集計するSQL文を図5に示すように設計した。そして、テスト用の”取引履歴”、”講座”、”顧客”の各テーブルにテストデータをロードし、SQL文の実行結果を検証した。ここで、ISODATE()は、日付を表す文字列をDATE型に変換するユーザ定義関数とする。

SELECT Z.支店番号, Z.顧客コード, Z.顧客名, SUM(X.取引金額) AS 月間預入額
FROM 取引履歴 X, 口座 Y, 顧客 Z
WHERE X.支店番号=Y.支店番号
AND X.科目番号 = Y.科目番号
AND X.口座番号 = Y.口座番号
AND X.取引種別番号 = 1
AND Y.顧客コード = Z.顧客コード
AND X.取引日 BETWEEN ISODATE(‘2009-04-01′) AND ISODATE(‘2009-04-30′)
AND ISODATE(‘2009-04-01′) <= Z.適用開始日
AND (Z.適用終了日 <= ISODATE('2009-04-30') OR Z.適用終了日 IS NULL)
GROUP BY Z.支店番号, Z.顧客コード, Z.顧客名


図5 Fさんが設計した視点・顧客別月間預入額を集計するSQL文

[変更後の”顧客”テーブルに関する指摘事項]
G部長は、Fさんに対し、変更後の”顧客”テーブルに関して、次のように指摘した。
①同一顧客の適用期間は、連続していなければならない。すなわち、変更連番が1以外の場合の敵意用開始日は、変更連番が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\DAT()は、引数とした日付の翌日付を求めるユーザ定義関数とする。なお、 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字以内で述べよ。


(3)指摘事項②に対応するために、変更連番の列名を”適用順番”に、その意味を”適用開始日の順番”に変更し、指摘事項①の制約条件を追加した。この時、顧客コードC333333の顧客属性情報の列値について、次の表中の g  j に入れる適切な字句を答えよ。


(4)指摘事項③に対応するために、(3)の変更を行ったうえで、変更受付日の列を追加し、その意味を次のように定義した。


しかし、この変更受付日の列の追加だけでは、”顧客属性情報の変更受付日を漏れなく記録したい”という要望に応えられない場合がある。どのような場合に答えられないのか、35字以内で述べよ。

設問3 [変更後の”顧客”テーブルに関する指摘事項]④について、答えよ。
指摘事項④に対応するために、具体的なテストデータを用いて検討した。適用期間中、月に1回以上の預け入れが存在するA~Iの顧客について、図5のSQL文を利用して集計した月間預入額の結果を表に整理した。表中の顧客コード欄に該当する顧客コードをA~Iから選んですべて答えよ。該当する顧客コードがない場合は、空欄にすること。

 なお、SQL文の結果行が存在しなかった顧客の場合、月間預入額を0円とする。また、適用期間は連続していて、日にちが抜けたり、重なったりしていることはない。各顧客の支店番号、顧客コード、顧客名は変更されないものとする。





login

ログイン UVカット スプレー
ストロー 水筒
ミネトンカ モカシン
パソコンデスク L字型
ノンシリコンシャンプー レヴール
ぬいぐるみ 犬
水着 タンキニ
ネクタイ 6.5cm
ガウチョパンツ デニム
タンクトップ ロング
書道セット 女の子 激安
ワールド フィギュアスケート
スキニーパンツ ピンク
習字セット 激安
サーモス 水筒
ボトルホルダー ランニング
レインブーツ ショート
サングラス スポーツ用
ビーチサンダル 厚底
裁縫セット 激安
骨盤矯正 レギンス
着圧ソックス ベージュ
弁当箱 スープ用
ロクシタン ハンドクリーム 激安
マガジンラック 木製
ソーイングセット 家庭科
裁縫セット 家庭科
子供 ジャージ
メッシュ ワークキャップ
本棚 回転
ゴミ箱 おしゃれ
THERMOS シャトルシェフ
骨盤矯正 座椅子
トランクス 激安
骨盤矯正 スパッツ
サーモス ポット
つけまつげ ナチュラル
ゴルフ シューズケース アディダス
麦わら帽子 農作業
弁当箱 魔法瓶
ベージュ パンプス
サッカー ピステ 激安
THERMOS タンブラー
ローズ ド マラケシュ クレイシャンプー
クリスタルガイザー 激安
リボンブラ ワコール
遮光カーテン UV
フライパン ダイヤモンドコート
サンアルシデ 2012
メンズ ジャージ 激安
低反発 クッション
スマホ 車載ホルダー
麦わら帽子 大きいサイズ
ウェッジソール パンプス
サルエル スウェットパンツ
フォトアルバム アンパンマン
骨盤矯正 クッション
サルエルパンツ スウェット
ニューエラ ゴルフキャップ
着圧 ニーハイ
ジュニア ジャージ
水筒 サーモス
オーガニック シャンプー 激安
ハンター レインブーツ
スマホ カバー so-03d
骨盤矯正 ガードル
斜めがけバッグ キャンバス
UVカット サングラス
着圧ソックス メンズ
カーゴパンツ 迷彩
裁縫セット 小学生
スウェットパンツ 七分丈
スウェット アディダス
着圧ソックス 白
スマホ カバー ARROWS
ブーツサンダル ロング
魔法瓶 ステンレス
トリーバーチ トートバッグ
おーいお茶 激安
レスポートサック ラージウィークエンダー
ニューエラ メッシュキャップ
チノパン 大きいサイズ
ダンロップ スニーカー
サーモス ストロー
サルエルパンツ 激安
骨盤矯正 ベルト
山ガール レギンス
ビーチサンダル リボン
ダウニー クリーンブリーズ
ハーフパンツ ランニング
トリンプ ソルフェージュ
レインポンチョ 自転車用
テーブル 折りたたみ
つけまつげ ブラウン
レスポートサック トレスチック
サーモス 魔法瓶
ローファー ヒール
習字 バッグ
ステンレス 水筒
書道セット 男の子 激安
サーモス ジャグ
着圧 トレンカ
マガジンラック おしゃれ
THERMOS 水筒