応用情報技術者過去問題 平成22年秋期 午後問6

⇄問題文と設問を画面2分割で開く⇱問題PDF

問6 データベース

販売管理システムに関する次の記述を読んで,設問1〜4に答えよ。

 L社は,焼酎を製造販売する酒造会社である。L社では顧客である小売店との取引管理に販売管理システム(以下,本システムという)を利用している。

〔請求締め業務〕
 請求額は,前月度の請求額,今月度(前月21日から今月20日まで)の入金額及び今月度の買上額を基に算出する。請求額がマイナスの場合は,預り金が発生していることを示す。本システムによる請求書発行処理は毎月25日20時に実行され,顧客ごとに請求書が発行される。請求書の例を図1に示す。
pm06_1.gif/image-size:490×205
〔入金消込み業務〕
 担当者は顧客からの入金を確認する都度,本システムによって,支払がされていない請求にこの入金を割り当てて入金消込み処理を行う。
 本システムでは, 1回の請求に対して複数回に分けて入金することが可能であり,複数の請求に対する支払を1回の入金で行うことも可能である。入金で余りが発生した場合は,次回の請求締め業務で精算する。また,入金は本システムが付与する入金番号によって一意に特定できる。

〔本システムのE-R図〕
 本システムのE-R図を図2に示す。請求レコードは,請求締め業務の中で作成される。"請求"エンティティの"消込額"は,ある請求に対して,入金によって消し込まれた総額である。また,"入金"エンティティの“消込額"は,ある入金に対して請求への消込みに充てた総額である。
 本システムでは, E-R図のエンティティ名を表名,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理する。例として,請求テーブルを作成するCREATE文を図3に示す。
pm06_2.gif/image-size:487×567
〔入金消込み処理〕
 本システムの入金消込み処理では, 1回の入金に対して,図4の流れ図に従い,古い請求から順に消込みを行う。請求への消込みは,入金額が請求への消込みにすべて充てられるか,又は,支払が残っている請求がなくなるまで繰り返す。
pm06_3.gif/image-size:516×1157

設問1

図2及び図4中のadに入れる適切な属性名又はエンティティ間の関連を答え,E-R図を完成させよ。属性名が主キーや外部キーの場合は,凡例に倣って下線を引くこと。
(※正誤判定の都合上,主キー属性は{属性名},外部キー属性は(属性名)と入力してください)

解答入力欄

  • a:
  • b:
  • c:
  • d:

解答例・解答の要点

  • a:
  • b:
  • c:顧客番号
  • d:請求書番号

解説

一般にエンティティA,B間に関係があることがわかっている場合、エンティティAの主キーの属性がエンティティBでも存在すれば、エンティティBのその属性は外部キーであることが形式的に判断できます。
原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ(多重度)は「1対多」です。

aについて〕
売上明細エンティティと商品エンティティには共通の属性「商品番号」があります。この属性「商品番号」は、商品エンティティ側では主キーであり、商品を一意に特定します。また、売上明細エンティティ側では外部キーとなっています。1つの商品が複数回売れることは明らかにあるので、売上明細エンティティには同一の商品番号をもつ複数のレコードが存在し得ます。したがって、商品エンティティと売上明細エンティティのカーディナリティは1対多であり、空欄には「←」が当てはまります。

a=←

bについて〕
請求エンティティと顧客エンティティには共通の属性「顧客番号」があります。この属性「顧客番号」は、顧客エンティティ側では主キーであり、顧客を一意に特定します。また、請求エンティティ側では外部キーとなっています。1人の顧客に複数回の販売を行う可能性があるため、請求も1人の顧客に対して複数回行う可能性があります。よって、請求エンティティには同一の顧客番号をもつ複数のレコードが存在し得ます。したがって、顧客エンティティと請求エンティティのカーディナリティは1対多であり、空欄には「←」が当てはまります。

b=←

cについて〕
顧客エンティティと入金エンティティの間には"1対多"の関係があります。関係データベースでは、共通の主キーと外部キーを持たせることでエンティティ同士を関連付けますが、顧客エンティティと入金エンティティは関連があるにもかかわらず共通の属性がありません。したがって、顧客エンティティの主キー属性である「顧客番号」を入金エンティティに追加する必要があります。追加する属性「顧客番号」は、顧客エンティティの「顧客番号」を参照する外部キーとなります。したがって、空欄には外部キーを示す破線を付けた「顧客番号」が当てはまります。

c顧客番号

dについて〕
請求エンティティと入金消込エンティティの間には"1対多"の関係があります。請求エンティティと入金消込エンティティは関連があるにもかかわらず共通の属性がありません。したがって、請求エンティティの主キー属性である「請求書番号」を入金消込エンティティに追加する必要があります。追加する属性「請求書番号」は、請求エンティティの「請求書番号」を参照する外部キーとなります。

次に、入金消込エンティティにおいて「請求書番号」が主キーの一部となるかどうかを考えていきます。
〔入金消込み業務〕には「1回の請求に対して複数回に分けて入金することが可能であり,複数の請求に対する支払を1回の入金で行うことも可能である」と記載されているため、請求エンティティと入金エンティティは多対多の関係を持ちます。入金消込エンティティは、多対多の関係を2つの1対多の関係に分離するために設けられた連関エンティティという位置付けです。連関エンティティは関連する2つの表の主キーを組み合わせた複合主キーを持つので、形式的に請求書番号が主キーであると判断することができます。

別の視点からも考えてみます。入金消込エンティティは消込額という属性を持ちますが、同じ消込額という属性を入金エンティティも持っています。これはどういうことかというと、入金エンティティの消込額は、その入金のうち請求に充当された金額を表すの対し、入金消込エンティティの消込額は、どの入金がどの請求にどれだけ充当されたのかという、入金と請求の対応関係を表すものと考えることができます。このため、入金消込エンティティのレコードを一意に特定するには、入金番号と請求書番号の2つの情報が必要となります。したがって、請求書番号も主キーの一部を構成することになります。

請求書番号は主キーかつ外部キーの属性ですが、図2の凡例には「主キーの実線が付いている属性名には,外部キーの破線を付けない」とあるため、空欄には主キーを表す下線だけを付けた「請求書番号」が当てはまります。

d請求書番号

設問2

図3中のegに入れる適切な字句を答え,CREATE文を完成させよ。

解答入力欄

  • e:
  • f:
  • g:

解答例・解答の要点

  • e:請求書番号
  • f:PRIMARY KEY
  • g:顧客番号

解説

CREATE文の構文は以下の通りです。
CREATE TABLE 表名
(
-- 属性の定義部
列名 データ型,
列名 データ型,
列名 データ型,

-- フィールド属性定義部
PRIMARY KEY (主キー列名),
FOREIGN KEY (外部キー列名) REFERENCES 外部表名 (外部表の主キー列名)
)
E-R図上の請求エンティティと対比しながら見ていきます。

eについて〕
E-R図の請求エンティティの属性でありながら属性の定義に含まれていないこと、値のデータ型が5文字(図1の請求書参照)であることから「請求書番号」が入ることがわかります。

fについて〕
請求書番号を列名として宣言していること、このCREATE TABLE文には主キー制約が不足していることから「PRIMARY KEY」が入ることがわかります。

gについて〕
外部表の主キー列名が入ります。請求エンティティの顧客番号は、顧客エンティティの顧客番号を参照する外部キーですから「顧客番号」が当てはまります。

e=請求書番号
 f=PRIMARY KEY
 g=顧客番号

設問3

図4中のhiに入れる適切な式を図4の表記に倣って答えよ。

解答入力欄

  • h:
  • i:

解答例・解答の要点

  • h:bill.消込額 + x
  • i:credit.入金額

解説

図3、図4のフローチャートで行われている内容を簡単に説明すると、
  1. 顧客から入金があったら、顧客番号、入金日、入金額を入力して、
  2. 入金テーブルに登録する(まだ未消込なので一旦消込額を0円で)。
  3. 入力された顧客番号で消込が終わっていない請求をリスト化して、
  4. 請求日が古い順に入金額で買上額に対して消込を行う。
  5. 消込を行ったら入金テーブルと請求テーブルの消込額を更新して、
  6. 入金消込テーブルを登録してリストに次の請求があれば繰り返す。
という処理を行っています。

hiは「x>y」の分岐がNo、すなわち「x≦y」の場合に実行される処理です。
変数 x は、入金額(既に消込に使われた金額を除いた消込に使える金額、以下同じ)を保持しており、変数 y は「bill.買上額 - bill.消込額」、つまり、現在処理対象となっている請求で未消込である金額を保持しています。「x≦y」ということは、入金額が未消込額以下、すなわち入金額 x をすべてこの請求の消込に使うケースとなります。

hについて〕
「bill.消込額」は、その請求のうち消込済の額を保持しています。入金額 x が当該請求に充当されると、消込額が x だけ増えることになるので、請求の消込額を現在の消込額に入金額 x を加えた値で更新する処理が適切です。

iについて〕
「credit.消込額」は、その入金のうち消込に使われた金額を保持しています。入金額すべてが消込に使われた場合、入金された額と消込額は同じになるので、入金の消込額をその入金の入金額で更新する処理が適切です。
なお、「credit.消込額」は「x>y」がYesの処理で適宜更新されているので、現在の消込額に今回消込する x を加えた「credit.消込額 + x」でもOKだと思います。

h=bill.消込額 + x
 i=credit.入金額

設問4

今月度の請求締め業務が終了すると,顧客の中には預り金が発生している場合がある。今月度の末日時点で預り金の発生している顧客の顧客番号と預り金額の一覧を求めるためのSQL文を図5に示す。図5中のjlに入れる適切な字句を答え,SELECT文を完成させよ。
なお,ホスト変数として":今月度末日"が定義されているものとする。
pm06_4.gif/image-size:490×139

解答入力欄

  • j:
  • k:
  • l:

解答例・解答の要点

  • j:SUM(入金額−消込額)
  • k:入金日 <= :今月度末日
  • l:入金額 > 消込額

解説

klについて〕
WHERE句では結合条件を解答させる問題が定番ですが、今回はFROM句で入金表のみが指定されているため、結合条件は入りません。入金表からレコードを絞り込むための2つの条件が入ります。

このSQL文で抽出したいのは、①今月度の末日時点で、②預り金の発生している顧客です。
まず、今月度の末日経過後に行われた入金を除外する必要がありますから、①の条件は「入金日が今月度の末日以前であること」となります。条件式で表すと「入金日 <= :今月度末日」です。
そして、預り金の発生している状況とは、入金額の全部または一部が消込に使われておらず未消込額があるということです。これは、入金額が消込額よりも大きくなっていることにより判定できるので、②の条件は「入金額が消込額よりも大きい」となります。条件式で表すと「入金額 > 消込額」です。

この2つがWHERE句に入る条件式となります(順不同)。

k=入金日 <= :今月度末日
 l=入金額 > 消込額

jについて〕
このSQL文で表示したいのは、顧客番号と預り金額です。GROUP BY句でグループ化を行っている場合、SELECT句の列名には、GROUP BY句で指定した列または集約関数しか記述することができません。GROUP BY句で指定している顧客番号は既に記載されているので、空欄には預り金額を求める集約関数が入ると判断できます。
預り金額=未消込額ですから「入金額−消込額」で求めることができます。このSQL文は、1人の顧客が行った複数の入金で預り金が発生していることも考慮して、顧客ごとにグループ化していますから、顧客ごとの預り金額の合計を集計する「SUM(入金額−消込額)」が適切です。

j=SUM(入金額−消込額)
問6成績

平成22年秋期 午後問題一覧

問1 問2 問3 問4 問5 問6 問7 問8 問9 問10 問11 問12 採点講評
© 2010-2024 応用情報技術者試験ドットコム All Rights Reserved.

Pagetop