応用情報技術者 2010年 秋期 午後 問06

解答の導き方

1. [c] の決定（顧客番号）

• 図3のCREATE文に「FOREIGN KEY ( 顧客番号 ) REFERENCES 顧客 ( [ c ] )」とあるため、請求テーブルの顧客番号が顧客テーブルのどの属性を参照するかを問うていることが分かります。
• 図2の顧客エンティティには「顧客番号（主キー、実線下線）」があるので、参照先の属性 [c] は「顧客番号」です。
• したがって [c] = 顧客番号。顧客側では主キー（実線下線）として表し、参照している側（例：入金や請求）では外部キー（破線下線）として扱います。

1. [d] の決定（請求書番号）

• 図4の処理に「pay.消込額 ← y, pay.入金番号 ← credit.入金番号, pay.[d] ← bill.請求書番号（1）」とあるため、入金消込テーブルの属性 [d] が請求の請求書番号を受け取ることが明示されています。
• 図2の請求エンティティには「請求書番号（主キー、実線下線）」があるため、入金消込の [d] は請求の主キーである「請求書番号」を参照する外部キーです。
• したがって [d] = 請求書番号。請求エンティティ側では主キー（実線下線）、入金消込エンティティ側では外部キー（破線下線）として表します。

1. [a] の決定（売上明細と商品間の関係向き）

• 図2に「売上明細 … 商品番号（外部キー、破線下線）」とあり、同じく「商品 … 商品番号（主キー、実線下線）」と示されています。
• 外部キーが売上明細側にあることは「1つの商品に対して複数の売上明細がある（商品：1、売上明細：多）」を意味します。
• E-R図で「売上明細 ――→ 商品」となっている箇所は、向きが逆であるため修正が必要です。正しくは商品から売上明細へ向かう矢印、つまり図中の空欄 [a] に「←」を入れて「売上明細 ← 商品」と表します。
• よって [a] = ←（商品が1、売上明細が多であることを表す向き）。

1. [b] の決定（請求と顧客の関係向き）

• 図2の請求エンティティに「顧客番号（外部キー、破線下線）」があることから、請求は顧客を参照しています。外部キーが請求側にあるということは「1人の顧客に対して複数の請求がある（顧客：1、請求：多）」です。
• E-R図の表記「請求 ――←― [ b ] ―→ 顧客」は、中央の記号に適切な向きを入れて「顧客 ――→ 請求」を表す必要があります。問題の表記に合わせると [b] に「←」を入れることで、左側（請求）は多、右側（顧客）は1を示します。
• よって [b] = ←（顧客が1、請求が多であることを表す向き）。

誤りやすいポイント

• 矢印の向きを逆にする（どちらが1でどちらが多かは「どちらに外部キーがあるか」で判定する）。
• 外部キー＝参照先の主キーであることを見落とし、[c]や[d]を誤答する。図3のFOREIGN KEY句と図4の代入文は必ず確認すること。
• [d]を入金消込の主キーだと誤解する（入金消込の[d]は外部キー。入金消込の一意性は別に定義する）。
• 「関係名」と「矢印の向き」を取り違える（今回の[a],[b]は向き＝1対多の向きを問う箇所）。

FAQ

解答の導き方

1. e（CREATE文の最初の欄名）について
   図3の該当行は "e CHAR(5)," の形で列名を定義する場所です。図2の請求エンティティには「請求書番号（主キー、実線下線）」と示されています。したがって列名として入るべきは請求エンティティの主キーである「請求書番号」です。
   結論：e = 請求書番号
2. f（CREATE文中の制約指定）について
   図3では次の行が "f ( e )," となっており、これはテーブルレベルで主キーなどの制約を定義する文法です。図2で「請求書番号」が主キーであることが明示されているので、この位置には主キーを宣言するキーワード「PRIMARY KEY」が入ります。これにより "PRIMARY KEY ( 請求書番号 )" という形になります。
   結論：f = PRIMARY KEY
3. g（FOREIGN KEY の参照列）について
   図3の最後の行は "FOREIGN KEY ( 顧客番号 ) REFERENCES 顧客 ( g )" となっており、参照先テーブル顧客のどの列を参照するかを指定する箇所です。図2の顧客エンティティには「顧客番号（主キー、実線下線）」とあるため、参照すべき列は「顧客番号」です。
   結論：g = 顧客番号

誤りやすいポイント

• 請求書番号と顧客番号を取り違える
  図2の請求エンティティには両方並んでいるため、「どちらが主キーか」を下線や（主キー）の表示で確実に確認してください。主キーは「請求書番号」です。
• PRIMARY KEY の書き方を混同する
  SQLでは列定義内に「請求書番号 CHAR(5) PRIMARY KEY」と書く方法とテーブル末尾で「PRIMARY KEY ( 請求書番号 )」と書く方法が両方あります。図3のテンプレートはテーブルレベルの制約を書かせる形なので、その形式で埋めるのが期待解答となります。
• REFERENCES の参照列名を省略してしまう
  「FOREIGN KEY ( 顧客番号 ) REFERENCES 顧客」と列名を省略する記述を許すDB実装はありますが、出題者の期待や移植性を考えると "REFERENCES 顧客 ( 顧客番号 )" と参照列を明示するのが無難です。
• カンマや括弧の位置を誤ると文法エラーになる
  CREATE文では行末のカンマや括弧の対応を間違いやすいので、1行ずつ構文が閉じているか確認してください。

FAQ

解答の導き方

1. 前提の確認（図4の定義・処理）
   • 図4の注に「xは入金の消込可能な残額を示す」、「yは請求の消し込まれていない残額を示す」とあるので、$x$ はその時点で入金からまだ割り当てられていない残額、$y$ は当該請求に対してまだ支払われていない残額であることが分かります。
   • 図4の処理5に「y ← bill.買上額 – bill.消込額」とあるので、$y$ は該当請求の「買上額」からこれまでに消込まれた合計を差し引いた額であることが明確です。
2. 条件分岐の意味（x > yの場合とそうでない場合）
   • 左分岐（x > yのとき）は入金の残額 $x$ がその請求の未消込額 $y$ を超えているので、その請求を全額消し込む処理を行います（bill.消込額 をbill.買上額 にする、credit.消込額 にyを加える、xをx - yにする、等）。
   • 右分岐（x > y が成り立たない、すなわち $x\le y$）は入金の残額 $x$ がその請求の未消込額 $y$ 以下であり、入金が尽きる（その入金分だけを当該請求に充てる）処理を行います。図4の右分岐の最後で「pay.消込額 ← x」として入金消込テーブルに割当額が記録される点に注目します。
3. 右分岐（部分消込）の各代入が表す意味と式の導出
   • 当該請求に今回割り当てる金額は明示的に「pay.消込額 ← x」としているため、請求側の消込合計も今回の割当分だけ増える必要があります。したがって請求テーブルの消込額は既存の値に今回の割当額 $x$ を加える操作であり、 h = bill.消込額 + xが正しいことになります。
   • 入金側（credit.消込額）は「入金に対して請求へ消し込んだ総額」を保持する属性です。ループのこれまでの繰返しで既に加算された金額の合計を $s$ とすると、入金の総額はcredit.入金額 であり、残額は $x=credit.入金額-s$ です。部分消込で残額 $x$ を全て使うと、入金に対して消し込んだ総額は $s+x=credit.入金額$ になります。したがって入金テーブル側は入金が尽きたことを明示する式として i = credit.入金額 を代入するのが自然で簡潔です。
   • 補足（等価性の説明）：同じ結果を得る式として credit.消込額 ← credit.消込額 + x も成り立ちます。なぜならループ前の credit.消込額 を $s$ とすれば $s+x=credit.入金額$ だからです。両者は等価であり、どちらを用いても最終値は一致します。
4. 結論（設問の答え）
   • h：bill.消込額+x
   • i：credit.入金額

誤りやすいポイント

• bill.消込額 ← x としてしまう誤り
  既存の消込合計を上書きしてしまい、これまでの消込分を失うため誤りです。部分消込では既存値に今回割当額を加える必要があります。
• bill.消込額 ← bill.買上額 としてしまう誤り
  これはその請求を全額消し込む操作であり、$x<y$（部分消込）の場合は不適切です。等号の場合（$x=y$）は結果的に同じになりますが、条件分岐の意図を踏まえると一般にbill.消込額 + xが正しい処理です。
• credit.消込額 の扱いに関する誤解
  「credit.消込額 ← credit.消込額 + x とすると二重計上になる」と誤認する受験者がいますが、既に加算済みの値を $s$ とすると $s+x$ は入金総額 credit.入金額 と一致します。よって credit.消込額 ← credit.消込額 + x も正しく、credit.消込額 ← credit.入金額 と等価です。どちらを使うかは実装上の書き方の違いにすぎません。
• 請求の残額を求める際に属性を取り違える誤り
  図4は明確にyをbill.買上額 – bill.消込額 と定義しています。請求テーブルに請求額と買上額の両方が存在する点に惑わされ、誤って別の属性を使うと計算が狂います。
• 条件の等号処理（x = y）への理解不足
  図4は判定を「x > y」としているため、等号は右分岐（部分消込側）に入ります。等号の扱いを誤るとループの分岐や更新順序に齟齬が生じるので注意してください（ただし更新の中身が等価なら結果は同じです）。

FAQ

解答の論理構成

1. 預り金とは
   • 【問題文】では「請求額がマイナスの場合は、預り金が発生していることを示す」と説明しています。マイナスになる原因は、入金額がまだ請求へ全額消し込まれていない“余り”があるためです。
   • “余り”は【問題文】の“入金”エンティティにある「入金額」と「消込額」の差で表現されます。
2. 不要消込額の算出式
   • 1レコード当たりの預り金額＝「入金額−消込額」。
   • これを顧客単位で集計するため、SELECT 句には「SUM(入金額-消込額)」を置きます。
   • これが図5の j に該当します。
3. 対象期間の限定
   • 今月度の末日時点での残高を求めるので、日付条件が必要です。
   • ホスト変数「:今月度末日」が示す日付までに登録された入金だけを集計するため、WHERE 句に「入金日 <= :今月度末日」を入れます。
   • これが図5の k または l のいずれかになります。
4. 余りが存在するレコードの限定
   • まだ預り金として残っている入金だけを対象にするには「入金額 > 消込額」でフィルタリングします。
   • これが残る1つの WHERE 条件にあたり、図5の k または l となります。
5. GROUP BY
   • 集計単位は顧客番号ごと。既に「GROUP BY 顧客番号」が記載されているため、SELECT 句と整合が取れます。
6. まとめ
   • 上記を反映すると、図5の空欄は次のように埋まります。

誤りやすいポイント

• 「SUM(入金額-消込額)」ではなく「SUM(入金額) - SUM(消込額)」と書く
  → 数式自体は正しくても、GROUP BY との組合せで NULL を生みにくくシンプルな前者が典型解です。
• 日付条件を「< :今月度末日」や「BETWEEN」で書き、末日を含めない
  → 問題文は「今月度の末日時点」と明記しているため“＝”を含める必要があります。
• 「入金額 >= 消込額」としてしまう
  → 入金額と消込額が完全に一致しているレコード（差額０円）は預り金ではありません。

FAQ