応用情報技術者 2010年 秋期 午後 問06
販売管理システムに関する次の記述を読んで、設問1～4に答えよ。
L社は、焼酎を製造販売する酒造会社である。L社では顧客である小売店との取引管理に販売管理システム（以下、本システムという）を利用している。
〔請求締め業務〕
請求額は、前月度の請求額、今月度（前月21日から今月20日まで）の入金額及び今月度の買上額を基に算出する。請求額がマイナスの場合は、預り金が発生していることを示す。本システムによる請求書発行処理は毎月25日20時に実行され、顧客ごとに請求書が発行される。請求書の例を図1に示す。
〔入金消込み業務〕
担当者は顧客からの入金を確認する都度、本システムによって、支払がされていない請求にこの入金を割り当てて入金消込み処理を行う。
本システムでは、1回の請求に対して複数回に分けて入金することが可能であり、複数の請求に対する支払を1回の入金で行うことも可能である。入金で余りが発生した場合は、次回の請求締め業務で精算する。また、入金は本システムが付与する入金番号によって一意に特定できる。
〔本システムのE-R図〕
本システムのE-R図を図2に示す。請求レコードは、請求締め業務の中で作成される。“請求”エンティティの“消込額”は、ある請求に対して、入金によって消し込まれた総額である。また、“入金”エンティティの“消込額”は、ある入金に対して請求への消込みに充てた総額である。
本システムでは、E-R図のエンティティ名を表名、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。例として、請求テーブルを作成するCREATE文を図3に示す。
〔入金消込み処理〕
本システムの入金消込み処理では、1回の入金に対して、図4の流れ図に従い、古い請求から順に消込みを行う。請求への消込みは、入金額が請求への消込みにすべて充てられるか、又は、支払が残っている請求がなくなるまで繰り返す。
設問1：
図2及び図4中のa〜dに入れる適切な属性名又はエンティティ間の関連を答え、E-R図を完成させよ。属性名が主キーや外部キーの場合は、凡例に倣って下線を引くこと。
模範解答
a：←
b：←
c：顧客番号
d：請求書番号
解説
解答の論理構成
-
空欄 c について
- 図2のエンティティ“入金”には c が欠落しています。
- 同図のリレーションシップに「顧客 ――→ 入金」が示されており、凡例より矢印先（“入金”側）が多側で外部キーを持つことが分かります。
- “顧客”の主キーは「顧客番号」。従って “入金” が参照する外部キーは「顧客番号」。
- よって c には「顧客番号」を入れます。
-
空欄 d について
- “入金消込”は “入金” と “請求” を結ぶ多対多解消用エンティティです。
- 図2の属性リストでは d が「」で下線指定されているため、主キーの一部であることが分かります。
- “請求”の主キーは「請求書番号」なので、“入金消込”側の対応列も同じく「請求書番号」。
- よって d には「請求書番号」を入れます。
-
空欄 a について
- 図2の関係「売上明細 ――→ 商品 関係名：a」は、関係名（リレーションシップ名）の不足を示しています。
- “売上明細”は必ず1件の“商品”を参照し、1つの“商品”は多数の“売上明細”に現れるため、多側は“売上明細”。
- 矢印は多側に向ける規約ですから、本来「売上明細 ――← 商品」とすべきで、左向き矢印「←」が正しい関係向きとなります。
- 従って a には「←」。
-
空欄 b について
- 図2の関係「請求 ――←― b ―→ 顧客」は、“売上”を介して“請求”と“顧客”を結ぶことを表しています。
- しかし矢印が左右で不整合になっているため、b には左右を合わせるための左向き矢印「←」を補う必要があります。
- よって b には「←」。
以上より模範解答は
a：←
b：←
c：顧客番号
d：請求書番号
a：←
b：←
c：顧客番号
d：請求書番号
誤りやすいポイント
- 外部キーと主キーの見分けを凡例で確認せず、下線の種類を誤ってしまう。
- 矢印の向きの規約（多側に矢印先を向ける）を忘れ、1対多の向きを逆に書く。
- “入金消込”が多対多解消エンティティであることに気付かず、d を外部キーではなく単独列と勘違いする。
FAQ
Q: 矢印の向きはどのように判定すればよいですか？
A: 凡例に「―――→：1対多」とある通り、「→」の先が多側です。多側のエンティティに外部キーが入る点と合わせて確認してください。
A: 凡例に「―――→：1対多」とある通り、「→」の先が多側です。多側のエンティティに外部キーが入る点と合わせて確認してください。
Q: “顧客番号”はなぜ破線下線（外部キー）なのですか？
A: “入金”エンティティ自身の主キーは「入金番号」であり、“顧客番号”は“顧客”を参照するだけなので外部キー（破線下線）になります。
A: “入金”エンティティ自身の主キーは「入金番号」であり、“顧客番号”は“顧客”を参照するだけなので外部キー（破線下線）になります。
Q: “請求書番号”が“入金消込”で主キーになる理由は？
A: “入金消込”は「1回の入金を複数請求へ」「1つの請求に複数入金を」対応させる多対多解消エンティティです。そのため「入金番号」と「請求書番号」の複合主キーで一意性を保ちます。
A: “入金消込”は「1回の入金を複数請求へ」「1つの請求に複数入金を」対応させる多対多解消エンティティです。そのため「入金番号」と「請求書番号」の複合主キーで一意性を保ちます。
関連キーワード: E-R図、外部キー、複合主キー、1対多 관계、矢印記法
設問2：
図3中のe〜gに入れる適切な字句を答え、CREATE文を完成させよ。
模範解答
e：請求書番号
f：PRIMARY KEY
g：顧客番号
解説
解答の論理構成
- “請求”エンティティの主キー
図2のエンティティ一覧には
「4. 請求 ―――――――――
請求書番号 …」
とあり、下線が実線で示されている属性は主キーを意味します。したがって CREATE 文の最初の列 e には 「請求書番号」 を置きます。 - 主キー制約の宣言
CREATE TABLE 内で主キーを宣言するキーワードは SQL 標準の 「PRIMARY KEY」 です。従って列リストの後に続く制約 f には「PRIMARY KEY」を記述します。 - 外部キー参照先の列
FOREIGN KEY 句では「REFERENCES 顧客 ( g )」となっており、顧客テーブル側の対応列は図2の“顧客”エンティティで主キーになっている
「5. 顧客 ―――――――――
顧客番号 …」
です。よって g には 「顧客番号」 を記入します。
以上より
e：請求書番号
f：PRIMARY KEY
g：顧客番号
e：請求書番号
f：PRIMARY KEY
g：顧客番号
誤りやすいポイント
- E-R 図で破線の下線（外部キー）と実線の下線（主キー）を取り違える。
- 「請求番号」「請求ID」など、存在しない列名を想像で書いてしまう。必ず図中の正式名称 “請求書番号” を引用する必要があります。
- PRIMARY KEY を列定義の後ろに書くか列内に書くかで迷い、句を省略してしまう。
- FOREIGN KEY 句で参照列名 “顧客番号” を書き忘れたり、表側（請求テーブル）の列と参照側（顧客テーブル）の列を混同する。
FAQ
Q: 「PRIMARY KEY」を列定義中に書かず、後ろの制約だけに書いても良いですか？
A: 図3の書式は制約部にまとめる形を採用しています。列定義中に書く方法もありますが、問題の空欄 f は制約部なので「PRIMARY KEY」が正解です。
A: 図3の書式は制約部にまとめる形を採用しています。列定義中に書く方法もありますが、問題の空欄 f は制約部なので「PRIMARY KEY」が正解です。
Q: 外部キーの参照先列名を省略するとどうなりますか？
A: 省略は許されません。SQL では参照先列を明示しないと文法エラーになりますし、試験でも “顧客番号” を書かないと誤答になります。
A: 省略は許されません。SQL では参照先列を明示しないと文法エラーになりますし、試験でも “顧客番号” を書かないと誤答になります。
Q: E-R 図に“請求額”と“買上額”がありますが主キーには含めなくて良いのでしょうか？
A: 主キーは一意性を担保する最小項目です。図2で実線下線が引かれているのは “請求書番号” だけなので、それだけで一意性が保証され、他の列を含める必要はありません。
A: 主キーは一意性を担保する最小項目です。図2で実線下線が引かれているのは “請求書番号” だけなので、それだけで一意性が保証され、他の列を含める必要はありません。
関連キーワード: 主キー、外部キー、CREATE TABLE, 制約、E-R図
設問3：
図4中のh、iに入れる適切な式を図4の表記に倣って答えよ。
模範解答
h：bill.消込額+x
i：credit.入金額
解説
解答の論理構成
- 前提の再確認
- 問題文には「注（⁴） x は入金の消込可能な残額を示す。」とあります。
- また「注（⁵） y は請求の消し込まれていない残額を示す。」とあります。
- 判定部では x > y かどうかで分岐し、No（すなわち x ≤ y）の場合に h と i を設定します。
- 請求側（bill.消込額）の更新
- x ≤ y のときは “現在の請求に対して入金をすべて充当しても請求が完済しない、またはぴったり完済する” 状態です。
- したがって新しい請求の消込額は「これまでの消込額 ＋ 今回充当する額 x」。
- フローチャートの表記例に倣うと bill.消込額 ← bill.消込額 + x が正しい。
→ h ＝ bill.消込額 + x
- 入金側（credit.消込額）の更新
- x は「まだ使っていない残額」です。今回 x をすべて使い切るので、入金レコードに記録すべき累計消込額は“入金全額”になります。
- 入金レコードには元々「credit.消込額 ← 0」で開始し、Yes 分岐では「credit.消込額 ← credit.消込額 + y」と段階的に加算していました。No 分岐が最後の充当になるため、ここで一気に「入金額そのもの」を設定すれば累計が完成します。
- 本システムの属性名は「入金」エンティティの表記どおり 入金額 です。
→ i ＝ credit.入金額
- 以上より
- h：bill.消込額 + x
- i：credit.入金額
誤りやすいポイント
- credit.消込額 ← credit.消込額 + x としてしまう
- 既に credit.消込額 に部分消込分が足し込まれている場合、再度 + x すると二重計上になります。No 分岐は“最後の一括加算”である点に注意が必要です。
- bill.消込額 ← bill.買上額 としてしまう
- x ≤ y のケースでは請求を完済するとは限りません。請求残高 y の一部だけ消込む可能性もあるため、買上額をそのまま代入するのは誤りです。
- 変数 x・y の定義を取り違える
- x は入金残、y は請求残。逆に覚えると計算式が逆転し、分岐判定も誤読します。
FAQ
Q: credit.入金額 を直接代入しても過去の加算値が失われませんか？
A: いいえ。No 分岐が実行される時点で充当処理は完了し、累計金額は“入金全額”と一致します。途中まで加算していた値を上書きしても正しい最終値になるため問題ありません。
A: いいえ。No 分岐が実行される時点で充当処理は完了し、累計金額は“入金全額”と一致します。途中まで加算していた値を上書きしても正しい最終値になるため問題ありません。
Q: bill.消込額 + x によって上限を超える心配はありませんか？
A: x ≤ y なので bill.消込額 + x の結果は bill.買上額 を超えません。買上額 が上限となるため不整合は起きません。
A: x ≤ y なので bill.消込額 + x の結果は bill.買上額 を超えません。買上額 が上限となるため不整合は起きません。
Q: もし x がちょうど y と等しい場合、処理はどうなりますか？
A: 等しい場合も No 分岐になります。bill.消込額 が bill.買上額 と一致し請求完済、credit.消込額 が入金全額となり、次のループでは n < list.length でも x が 0 なので判定部には入らずループ終了です。
A: 等しい場合も No 分岐になります。bill.消込額 が bill.買上額 と一致し請求完済、credit.消込額 が入金全額となり、次のループでは n < list.length でも x が 0 なので判定部には入らずループ終了です。
関連キーワード: 入金消込、フローチャート、データ整合性、累積計算
設問4：
今月度の請求締め業務が終了すると、顧客の中には預り金が発生している場合がある。今月度の末日時点で預り金の発生している顧客の顧客番号と預り金額の一覧を求めるためのSQL文を図5に示す。図5中のj〜lに入れる適切な字句を答え、SELECT文を完成させよ（kとlは順不同）。
なお、ホスト変数として“:今月度末日”が定義されているものとする。
模範解答
j：SUM(入金額-消込額)
k：入金日 <= :今月度末日
l：入金額 > 消込額
解説
解答の論理構成
-
預り金とは
- 【問題文】では「請求額がマイナスの場合は、預り金が発生していることを示す」と説明しています。マイナスになる原因は、入金額がまだ請求へ全額消し込まれていない“余り”があるためです。
- “余り”は【問題文】の“入金”エンティティにある「入金額」と「消込額」の差で表現されます。
-
不要消込額の算出式
- 1レコード当たりの預り金額＝「入金額−消込額」。
- これを顧客単位で集計するため、SELECT 句には「SUM(入金額-消込額)」を置きます。
- これが図5の j に該当します。
-
対象期間の限定
- 今月度の末日時点での残高を求めるので、日付条件が必要です。
- ホスト変数「:今月度末日」が示す日付までに登録された入金だけを集計するため、WHERE 句に「入金日 <= :今月度末日」を入れます。
- これが図5の k または l のいずれかになります。
-
余りが存在するレコードの限定
- まだ預り金として残っている入金だけを対象にするには「入金額 > 消込額」でフィルタリングします。
- これが残る1つの WHERE 条件にあたり、図5の k または l となります。
-
GROUP BY
- 集計単位は顧客番号ごと。既に「GROUP BY 顧客番号」が記載されているため、SELECT 句と整合が取れます。
-
まとめ
- 上記を反映すると、図5の空欄は次のように埋まります。
sql
SELECT 顧客番号、
SUM(入金額-消込額)
FROM 入金
WHERE 入金日 <= :今月度末日
AND 入金額 > 消込額
GROUP BY 顧客番号;
誤りやすいポイント
- 「SUM(入金額-消込額)」ではなく「SUM(入金額) - SUM(消込額)」と書く
→ 数式自体は正しくても、GROUP BY との組合せで NULL を生みにくくシンプルな前者が典型解です。 - 日付条件を「< :今月度末日」や「BETWEEN」で書き、末日を含めない
→ 問題文は「今月度の末日時点」と明記しているため“＝”を含める必要があります。 - 「入金額 >= 消込額」としてしまう
→ 入金額と消込額が完全に一致しているレコード（差額０円）は預り金ではありません。
FAQ
Q: 「消込額」は請求テーブルにもありますが、なぜ入金テーブルだけを参照するのですか？
A: 預り金の源泉は“入金の余り”なので、「入金額−消込額」が正しく残高を示せます。請求側の「消込額」は支払済み総額を示すもので、本問の趣旨と異なります。
A: 預り金の源泉は“入金の余り”なので、「入金額−消込額」が正しく残高を示せます。請求側の「消込額」は支払済み総額を示すもので、本問の趣旨と異なります。
Q: SUM の中で差を取るとパフォーマンスが悪化しませんか？
A: 差分計算は行単位で実行されます。適切にインデックスを張れば集約処理のオーバーヘッドはごく小さく、式展開より可読性重視で問題ありません。
A: 差分計算は行単位で実行されます。適切にインデックスを張れば集約処理のオーバーヘッドはごく小さく、式展開より可読性重視で問題ありません。
Q: 日付の比較に文字列型 CHAR(8) を使うのは安全ですか？
A: 西暦表記の YYYYMMDD 形式なら、文字列比較でも日付順と辞書順が一致します。内部を DATE 型に変更すればさらに安全ですが、本試験では定義どおり CHAR(8) を前提にしています。
A: 西暦表記の YYYYMMDD 形式なら、文字列比較でも日付順と辞書順が一致します。内部を DATE 型に変更すればさらに安全ですが、本試験では定義どおり CHAR(8) を前提にしています。
関連キーワード: 集約関数、外部キー、消込処理、残高計算、条件抽出
