平成21年

変更履歴を記録するテーブルに関する次の記述を読んで、設問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円とする。また、適用期間は連続していて、日にちが抜けたり、重なったりしていることはない。各顧客の支店番号、顧客コード、顧客名は変更されないものとする。


詳細はこちら
(画像が見づらい場合は、クリックして拡大してご覧ください)

問2 データベースの設計に関する次の記事を読んで、設問1~3に答えよ

J社は、完成品を組み立てる製造業者に部品を供給する部品製造業者である。J社では、生産管理のための新システムを開発する予定である。そこで、システム部のK部長の下にプロジェクトチームを編成し、L君がデータベースの設計を担当することになった。

[業務概要]

1.部品管理
部品は、一つまたは複数の部品から構成され、図1に示すような階層構造で管理されている。

図1において、各階層の上位から見た会の部品を子部品、会から見た上位の部品を親部品と呼ぶ。子部品を組み立てて、親部品を作る。図1では、部品Aは部品B,C及びDの親部品であり、部品B,C及び、Dは部品Aの子部品である。部品Bは部品E,Dの親部品であり、逆に部品Dから見ると、部品A,Bはともに親部品である。

親部品と子部品は、ともに部品として管理する。部品は、部品番号によって、J社内で一意に識別される。

(1)部品の区分
部品は次の三つの観点で区別されている。

 (a)調達区分
  部品には、社外から調達するものとJ社で製造するものがあり、調達区分はこの観点から分類する場合の区分である。前者の調達品、後者を製造品と呼ぶ。調達品か製造品化は、部品ごとに決められている。図1で、部品A,Bは製造品、部品C,D及びEは調達品である。同じ調達品でも多くの場合、複数の調達先から仕入れている。

 (b)販売区分
  部品委は、販売するものと販売しないもの場あり、販売区分はこの観点から分類する番号の区分である。前者を製品と呼ぶ。製品は、部品番号とは別に、製造番号によってJ社内で一意に識別される。製造品だけが製品として販売され、調達品はそのまま製品として販売されることはない。

 (c)顧客仕様区分
  部品には、J社が顧客の設計仕様に従って、製造または調達うるものとそれ以外のものがあり、顧客仕様区分はこの観点から鵜bん類する場合の区分である。前者を顧客仕様部品と呼ぶ。顧客仕様部品の中で、顧客に販売するものを顧客仕様製品と呼ぶ。顧客仕様製品を構成する子部品委は、顧客仕様部品以外の部品が含まれることがある。

(2)リードタイム
部品には部品ごとにリードタイム(以下、LTという)が決められている。LTには1階層LTと全階層LTがある。

(a)1階層LT
製造品の場合は製造LTと呼び、子部品がすべてそろっている状態での親部品の組立期間である。調達品の場合は調達LTと呼び、社外から調達するのに必要な期間で、調達先ごとに設定される。

(b)全階層LT
全階層LTはすべての子部品をそろえるための期間に親部品の組み立て機関を加えたものである。部品の全階層LTを算出するときに、子部品に調達品がある場合は、同じ調達品の中で、最大の調達LTが用いられる。全階層LTは、構成部品が変更になるたびに一括して再計算される。

図1で示した部品の調達LT、製造LT及び全階層LTを、表1に示す。

(3)部品使用開始日・部品使用終了日と部品販売開始日・部品販売終了日
一つの部品は、子部品として使用されたり、製品として販売されたりする。部品が、子部品として使用される日、または製品として出荷される日を、使用日と呼ぶ。新規部品の最初の使用日を部品使用開始日、最後の使用日を部品使用終了日と呼ぶ。部品は、製造または調達された日の翌日から使用可能になる。

新規部品の注文受付を開始する日を部品販売開始日と呼び、部品の注文受付を終了する日を部品販売終了日と呼ぶ。

(4)製造番号の付与
製品には、通常、一つの製品番号を付与するが、複数の製品番号を付与する場合もある。後者は、量産効果によってコストを抑えるため、性能の高い部品を集中製造し、性能の低い製品として転用する場合である。このため、部品、製品に対して、それぞれ部品仕様書製品仕様を分けて定義する。

(5)部品構成管理
 (a)構成管理
  一つの親部品を製造するときの、子部品の種類とその使用数量を表したものを部品構成表と呼ぶ。表2に部品構成表の例を示す。構成適用開始日、構成適用終了日は、部品構成表の各子部品に関する情報の有効期間を規定するものである。ここで、構成適用終了日の初期値には9999-12-31が設定されている。
部品構成管理票は、新規の親部品の製造時に新規作成され、子部品の変更時に変更される。表2では、部品Aについて ,次の新規作成・変更が行われたことを表している。

①2007年9月1日に、部品B、C及びDを使用して、部品Aを新規に製造開始。
②2008年9月30日に、部品Cの使用を終了。2008年10月1日から部品Fの使用を開始。

③2008年10月1日から、部品Dの使用数量を8個から4個に変更。


 (b)登録日付のチェック
 部品構成表の新規作成・変更に伴い、子部品の手配計画も変更される。構成適用開始日に当該子部品をそろえられる手配する日を、部品手配開始日と呼ぶ。また、当該子部品の手配を終了する日を、部品手配終了日と呼ぶ。部品手配開始日を設定する際には、構成適用開始日と部品の各LTとを比較し、実現性をチェックする。

2.顧客管理
(1)顧客と企業のと登録
顧客とは、J社が取引を行う事業所の単位である。複数の事業所を有するような企業では、一つの企業で複数の顧客が登録されることがある。顧客に対しては、J社内で一意な顧客番号を付与し、顧客番号とは別に企業に対しては、J社内で一意な企業コードを付与する。顧客単位に取引の開始・終了を管理し、一度取引が終了した顧客と取引を再開する場合は、新たな顧客番号を付与する。

(2)顧客と営業担当者との関係
1顧客に対して、1人の営業担当者が割り当てられる。1人の営業担当者は複数の顧客を担当することができる。組織変更によって、営業担当者が変わることがあるが、契約内容の問い合わせなどに備えて、過去の営業担当者を特定する仕組みが必要である。1人の営業担当者がある顧客の担当から外れた後、再度同じ顧客を担当することがある。

3.顧客仕様製品の新システムへの登録
 顧客仕様製品をh新規に受注する場合には、J社側で顧客仕様製品を新システムに登録する。その場合、顧客から指定された顧客仕様製品名と顧客ごとに一意な顧客仕様製品コードを登録し、対応する部品番号は新システムで一意な値が自動採番される。ただし、受注した時に顧客仕様製品名と顧客仕様製品コードが未定の場合、顧客仕様製品名と顧客仕様製品コードは空値(NULL)のままで新システムに登録される。その後、顧客仕様製品名と顧客仕様製品コードが決定した時点で、それぞれ登録する。

[テーブル設計]
 L君はデータベースの設計に当たり、まず図2に示すテーブルを設計した。これに対し、K部長からは次のような指摘があった。

[指摘事項]
①主キー、外部キーが設定されていないテーブルがある。
②”顧客担当”テーブルは第3正規形にしたほうがよい
③”部品構成”テーブルの日付は、実現性の面から矛盾しない日付となるように制約を整理する必要がある。
④”部品”テーブルは主キーの設定に誤りがあるので、再設計する必要がある。


解答に当たっては、、巻頭の表記ルールに従うこと。
なお、テーブル構造の表記は、”関係データベースのテーブル(表)構造の表記ルール”を用いること。さらに、主キー及び外部キーを明記せよ。

設問1 [テーブル設計]におけるK部長の指摘事項①、②について、(1)~(3)に答えよ。

(1)”顧客担当”テーブルは、第1正規形である。第2正規形でない理由を、列名を用いて具体的に60字以内で述べよ。

(2)”顧客担当”テーブルを第3正規形に分割し、分割後の”顧客担当”テーブル及び新たなテーブルの主キー及び外部キーも併せて答えよ。ここで、新たに作成するテーブルについては、内容を表す適切なテーブル名として本文中の用語を用いる。

(3)”顧客仕様製品”テーブルには二つの候補キーがある。これらの候補キーに関して、(a),(b)に答えよ。

(a)二つの候補キーのうち、適切な主キーを答えよ。

(b)もう一方の候補キーが主キーとして不適切な理由を候補キーを付帯的に示し、60字以内で述べよ。

設問2 [テーブル設計]におけるK部長の指摘事項③について、(1),(2)に答えよ。なお、回答に当たっては、本文中の用語を用いること。

(2)”部品”テーブルの列の値が設定されるかどうかは、部品によって異なる。これを表すため、図2の”部品”テーブルに、部品区分を表す列を追加する。そこで、部品区分とその組み合わせを、表3のように整理した。表3中のY,Nの意味を、本文中の用語を用いて答えよ。ここで、組み合わせ3(部品区分1~3がそれぞれY,N,Yである部品)は顧客仕様製品を表す。

詳細はこちら
(画像が見づらい場合は、クリックして拡大してご覧ください)

Z市では、長期にわたる糖尿病患者に対して、専門医(病院)と掛かり付け医(病院)が連携してケアを行うことになった。そこで、病院側の地域連携に必要な診療情報の共有・交換のために、データモデルについて検討を行った。

[診療情報の関係及び関数従属性]
糖尿病医療に関する診療情報を共有・交換するためのデータ形式を検討した。関係の表記は、関係の属性の代わりに、別の関係もとりうるように拡張した形式(XMLに対応付けられる形式)を用いることにした。この形式による診療情報の関係スキーマは、図1のとおりである。図3~6は、図2の関数従属性の表記表に従って、それぞれの関係について、属性間の関数従属性を表したものである。図1、3~6の主な属性と関係の意味および制約を、表に示す。



表 属性と関係の意味および制約(一部省略)








設問1 関係”患者”及び”地域連携”について、(1)~(3)に答えよ。
(1)表の属性と関係の意味及び制約を基に、図3を完成させよ。    には、属性名を記述し、関数従属性は図2の表記法に従うこと。また、導出される関数従属性は、省略するものとする。

(2)図4中に表記された関数従属性①~⑦のうち、図1の構造では成立しないものがある。その番号と、成立しない理由を60字以内で述べよ。

(3)図4中で、推移的関数従属性があれば、その例を1つ上げよ。なければ、”なし”と答えよ。

設問2 関係”診療”について、(1)~(5)に答えよ。
(1)関係”診療”は、第1正規形の条件を満たしていない。その根拠を30字いないで述べよ。

(2)関係”診療”を次のような三つの関係”診療・診断”、”合併症”及び”治療・指導”に分割した。各関係のそれぞれの候補キーをすべて挙げよ。

診療・診察(患者ID、診断日、主診断名、発症日、糖尿病の病型)
合併症(患者ID、診断日、網膜症、神経障害、じん症、その他)
治療・指導(患者ID、診断日、指導日、食事療法、運動療法、薬品名、調理担当、指示カロリー、自己血糖測定有無、測定器)

(3)関係”診療・診断”は、第1正規形、第2正規形、第3正規形のうち、どこまで正規化されているか。また、その根拠を60字以内で述べよ。

(4)関係”治療・指導”は、タプルの挿入に関してどのような問題があるか。30字以内で具体的に述べよ。

(5)関係”治療・指導”を、第3正規形に分割せよ。

設問3 関係”経過・評価”について、(1),(2)に答えよ。
(1)図1は、秒9印鑑で診療情報を共有・交換するためのデータ形式の検討結果である。図6の関数従属性を基に、図1中の  a    d  に入れる適切な字句を、図1の表記に倣って答えよ。図6中の網掛け部分の属性は省略し、それ以外の該当する属性名を記述するものとする。

(2)実際の業務では、血液検査と尿検査を同一日に行えない場合があることが、判明した。そのような場合に対応するためには、関係”経過・評価”をどのように変更すればよいか。変更後の  a    c  に入れる適切な字句を答えよ。
詳細はこちら

login

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