データベーススペシャリスト 2011年 午後1 問02
データベースの設計に関する次の記述を読んで、設問1〜3に答えよ。
B社は、飲食店をチェーン展開している。 これまでB社の各店舗では、来店者を増やすために、来店者数の少ない日には商品単価を下げたり、割引クーポンを発行したりしていたが、店舗での作業負担が大きいことから今回これらの点を含めてシステム化することにした。
〔業務の概要〕
1．商品
(1) 商品区分
商品は、単品商品と複合商品に区分する。
単品商品は、例えば、生ビール中ジョッキ、冷ややっこなどのように一つの商品が一つの飲食物に対応する。 複合商品は、セット商品と一括商品に区分する。セット商品は、複数の単品商品を組み合わせた一つの商品である。 一括商品は、宴会のコース料理のように1人ずつに提供する一連の単品商品を一つにまとめた商品である。
なお、セット商品の商品単価は、構成する単品商品の商品単価の合計よりも低く設定する。一つの単品商品が、複数のセット商品に組み込まれることはない。
(2) 商品提供時間と商品単価
① 商品提供時間は、次のとおりである。
・全店の営業時間は同じである。
・“時間帯”は、営業時間を19時台、20時台のように、1時間刻みに分けたものである。
・単品商品は、すべての時間帯で提供している。
・セット商品と一括商品は、商品別に、提供する曜日、時間帯を限定している。
② 商品単価は、次のとおりである。
・すべての商品には、それぞれ一つの標準単価を設定する。
・単品商品の一部に、店舗別曜日別時間帯別に変動単価を設定する。
・一括商品の一部に、店舗別曜日別に変動単価を設定する。
・セット商品には、変動単価を設定しない。
・変動単価が設定された場合は、変動単価を標準単価に優先して適用し、変動単価が設定されない場合は、標準単価を適用する。
2．販売
(1) 割引クーポンの発行
販売促進のために割引クーポンを発行している。 割引クーポンは来店時のグループ全員に有効で、割引クーポンの種類によらず,1回の来店でグループ当たり1枚使用できる。ただし、複合商品を構成する単品商品には割引クーポンは適用できない。
割引クーポンには、次の項目からなる利用条件を定め、一意なクーポン番号を付与する。
・利用可能商品: 一つ又は複数の単品商品
・割引額 : 一つの割引額
・有効期間(開始年月日〜終了年月日):一つの有効期間
・利用可能店舗: 一つ又は複数の店舗
・利用可能時間: 利用可能な曜日ごとの複数の時間帯
割引クーポンは、必要な枚数が印刷される。 割引クーポンの例を、図1に示す。
(2) 売上計上方法
・来店したグループ (以下、利用客という) 単位に、一つの売上伝票番号を付与する。
・係員が利用客の注文を聞き始めてから、注文の内容確認後テーブルを離れるまでを、1回の“注文伺”という。注文伺開始時の時刻 (時分秒)を当該注文伺の時刻とする。
・一つの注文伺を構成する商品ごとの注文を、“注文”という。 ただし、一つの注文伺の中で同じ商品が複数個注文された場合は、数量で管理する。
・注文伺の後、既に売切れとなっていることが判明した商品については、利用客に説明して注文の取消しを行う。 注文の取消しは、新たな注文伺の中で当該商品の数量をマイナスにする。 一つの注文伺の中で、一つの商品が注文された後その場で取り消された場合は、注文がなかったものとする。
・注文伺ごとに、注文伺内容、注文伺金額小計、及び利用金額合計を表示した注文伺明細を、利用客に提示する。 1回の注文伺における注文伺明細の例を、図2に示す。図2は、前回の注文伺で受けた注文の取消しを含む追加注文伺明細の例である。
〔データベースの設計概要〕
〔業務の概要〕に対するデータベースの設計概要は、次のとおりである。
1．商品ごとの区分の設定
商品ごとに商品区分として、単複区分と複合区分を設定する。また、セット商品を除く商品について、商品単価の区分のために単価区分を設定する。各区分の取り得る値と意味を、表1に示す。
〔セット商品扱いの対応〕
現在は、複数の単品商品の注文によって一つの売上伝票番号内でセット商品を構成する単品商品がそろった場合でも、単品商品として取り扱っている。 システム化後は、このような場合には、自動的にセット商品が注文されたものとみなし(これを“セット商品扱い”という)、単品商品の注文を取消し扱いにする。 この場合、利用客からの注文とセット商品扱いを区別して、注文伺明細に表示する。 セット商品扱いを含む注文伺明細の例を、図4に示す。
解答に当たっては、巻頭の表記ルールに従うこと。
なお、テーブル構造の表記は、“関係データベースのテーブル (表) 構造の表記ルール”を用いること。 さらに、主キー及び外部キーを明記せよ。
（1）
“クーポン利用条件” テーブルの候補キーを一つ示せ。
模範解答
クーポン番号、商品番号、曜日、時間帯番号、店舗番号
解説
解答の論理構成
- 一意性の基準を確認
- 【問題文】「一意なクーポン番号を付与する」とあるので、まずクーポン番号でグルーピングする。
- 商品単位の制約
- 「利用可能商品: 一つ又は複数の単品商品」とあるため、クーポン番号だけでは同一クーポン内で複数行が生じる。よって商品番号が必要。
- 店舗単位の制約
- 「利用可能店舗: 一つ又は複数の店舗」とあるので、店舗番号も必要。
- 時間単位の制約
- 「利用可能時間: 利用可能な曜日ごとの複数の時間帯」とあるため、曜日と時間帯番号の両方を含める。
- 有効期間の扱い
- 有効期間は「一つの有効期間」と明示されており、クーポン番号が決まれば開始・終了日は一意に決まる。従ってキーに含める必要はない。
- 以上より候補キー
クーポン番号＋商品番号＋曜日＋時間帯番号＋店舗番号
誤りやすいポイント
- 開始年月日・終了年月日をキーに含めてしまう
→ クーポン番号で一意に定まる属性なので冗長。 - 時間帯番号だけで曜日を省略する
→ 時間帯番号は1時間刻みの番号で曜日情報を持たないため、曜日が変われば別行になる。 - 商品番号を除外する
→ 同一クーポンが複数の単品商品を対象にするケースを想定漏れ。
FAQ
Q: 有効期間が複数に分かれるクーポンは存在しませんか？
A: 【問題文】に「有効期間(開始年月日〜終了年月日):一つの有効期間」と明記されているため、分割されることは想定されていません。
A: 【問題文】に「有効期間(開始年月日〜終了年月日):一つの有効期間」と明記されているため、分割されることは想定されていません。
Q: 「曜日」が同じでも時間帯が連続していれば1行にまとめなくてよいですか？
A: 時間帯は1時間刻みで管理されるため、連続していても別レコードにし、曜日＋時間帯番号で区別する設計になります。
A: 時間帯は1時間刻みで管理されるため、連続していても別レコードにし、曜日＋時間帯番号で区別する設計になります。
関連キーワード: 候補キー、一意性制約、複合キー、正規化
（2）
“クーポン利用条件” テーブルを第3正規形の条件を満たすテーブルに分解せよ。
なお、テーブル名は本文中で与えられた語句を用いて、適切な名称にすること。
模範解答
クーポン割引額有効期間（クーポン番号、開始年月日、終了年月日、割引額）
クーポン利用可能商品（クーポン番号、商品番号）
クーポン利用可能時間（クーポン番号、曜日、時間帯番号）
クーポン利用可能店舗（クーポン番号、店舗番号）
又は
クーポン割引額有効期間（クーポン番号、開始年月日、終了年月日、割引額）
クーポン利用可能商品店舗時間（クーポン番号、商品番号、店舗番号、曜日、時間帯番号）
解説
解答の論理構成
- 非正規形の確認
図3の“クーポン利用条件（クーポン番号、商品番号、開始年月日、終了年月日、曜日、時間帯番号、店舗番号、割引額）”は、 「クーポン番号とその他全ての列の組合せ」が主キー候補。 - 関数従属性の抽出
問題文より
・「一つの割引額」
・「一つの有効期間(開始年月日〜終了年月日)」
は“クーポン番号”だけで決まる。
よって
クーポン番号 → 開始年月日、終了年月日、割引額
が存在。 - 第2正規形違反の除去
複合主キーの一部（クーポン番号）が、非キー項目（割引額など）を決定するので第2正規形違反。
⇒ クーポン割引額有効期間（クーポン番号、開始年月日、終了年月日、割引額）を新設。 - 残る可変条件の整理
・「利用可能商品」「利用可能店舗」「利用可能時間」は“複数可”なので、 ①別々の交差テーブルで管理するか
②まとめて一つの交差テーブルにするか
の設計選択がある。
試験の模範解答は両方のパターンを許容。 - 最終形（模範解答）
①分割パターン
・クーポン利用可能商品（クーポン番号、商品番号）
・クーポン利用可能店舗（クーポン番号、店舗番号）
・クーポン利用可能時間（クーポン番号、曜日、時間帯番号）
②統合パターン
・クーポン利用可能商品店舗時間（クーポン番号、商品番号、店舗番号、曜日、時間帯番号）
誤りやすいポイント
- 「曜日」と「時間帯番号」を単一列にまとめてしまい、将来の拡張性を損なう。
- 「割引額」を商品ごとに設定できると勘違いし、クーポン番号＋商品番号で保持してしまう。
- 「有効期間」を開始日・終了日に分けず文字列で1列にしてしまう。
FAQ
Q: 「曜日」と「時間帯番号」を持つテーブルの主キーはどう考えるべきですか？
A: 第3正規形では「クーポン番号、曜日、時間帯番号」の3項目が主キーです。曜日ごとに時間帯が複数取れると示されているため、曜日だけでは一意になりません。
A: 第3正規形では「クーポン番号、曜日、時間帯番号」の3項目が主キーです。曜日ごとに時間帯が複数取れると示されているため、曜日だけでは一意になりません。
Q: 「利用可能店舗」が1店舗だけの場合でも交差テーブルが必要ですか？
A: はい。多対多が起こり得る項目は、実際に“1件だけ”であっても正規化の原則に従い交差テーブルで管理します。
A: はい。多対多が起こり得る項目は、実際に“1件だけ”であっても正規化の原則に従い交差テーブルで管理します。
Q: 交差テーブルを1つにまとめる案と3つに分ける案はどちらが望ましいですか？
A: 更新頻度・検索パターン・参照整合性など運用要件次第です。検索条件が「店舗だけ」や「商品だけ」で発生するなら分割、常に全条件を合わせて検索するなら統合の方が効率的です。試験ではどちらも正とされています。
A: 更新頻度・検索パターン・参照整合性など運用要件次第です。検索条件が「店舗だけ」や「商品だけ」で発生するなら分割、常に全条件を合わせて検索するなら統合の方が効率的です。試験ではどちらも正とされています。
関連キーワード: 第3正規形、関数従属性、多対多関係、交差テーブル、正規化
設問2：商品ごとの区分について、(1)、(2)に答えよ。
問題文を見る（1）
図3中の“商品” テーブルにおける列 “商品番号”、“商品名”、(a)、(b)及び(c)の値(一例)を表2に示す。
(a)図3中の(a)〜(c)に入れる適切な区分を次の①〜③の中から選び、番号で答えよ。
①:単複区分、②: 複合区分、③ : 単価区分
(b)テーブル“商品提供時間”、“単品商品変動単価”、“一括商品変動単価”の外部キー“商品番号”は、“商品” テーブルの限定されたインスタンスを参照する。これらのテーブルの“商品番号” が “商品” テーブルを参照するとき、次に示す表3の決定表で各区分の条件を判定する。 表3(1/2)の商品提供時間を例にして、表3(2/2)中の空欄をすべて埋めよ。
なお、存在しない区分の組合せも、決定表の列に含まれている。
模範解答
(a)：
a：③
b：②
c：①
(b)：
解説
解答の導き方
- (a)〜(c)の区分を特定する（(a)の解答）
表1では、単複区分は「全商品」に設定し、複合区分は「全複合商品」、単価区分は「全単品商品、全一括商品」に設定して、対象外の商品はNULLにします。表2と照らし合わせます。- (c) は全ての行に値があるので、全商品に設定する**単複区分（①）**です。
- (b) は (c)＝2（複合商品）の行だけに値があるので、**複合区分（②）**です。
- (a) は、(b)＝1（セット商品）の商品番号3456の行だけがNULLです。セット商品を除く商品に設定する**単価区分（③）**です。
- 存在する区分の組合せを整理する
【問題文】に「セット商品には、変動単価を設定しない。」とあり、単価区分の対象外です。したがって、存在する組合せは次の3種類だけです。- 単品商品：(c)＝1、(b)＝NULL、(a)＝1又は2
- セット商品：(c)＝2、(b)＝1、(a)＝NULL
- 一括商品：(c)＝2、(b)＝2、(a)＝1又は2
これ以外の組合せは存在しないので、どのテーブルも参照しません（「－」）。
- 各テーブルが参照する商品を決める
- 商品提供時間：「セット商品と一括商品は、商品別に、提供する曜日、時間帯を限定している。」ので、セット商品と一括商品です。一括商品は単価区分を問いません（表3(1/2)の例でも、(a)＝1、(b)＝2、(c)＝2の一括商品が「X」です）。
- 単品商品変動単価：「単品商品の一部に、店舗別曜日別時間帯別に変動単価を設定する。」ので、単価区分が2の単品商品です。
- 一括商品変動単価：「一括商品の一部に、店舗別曜日別に変動単価を設定する。」ので、単価区分が2の一括商品です。
- 表3(2/2)の9列に当てはめる（(b)の解答）
したがって、商品提供時間は列1と列5、単品商品変動単価は列2、一括商品変動単価は列1が「X」で、それ以外は全て「－」です。
誤りやすいポイント
- 「商品提供時間は単複区分が2なら参照する」とだけ考え、列3・7・9のように存在しない組合せにも「X」を付けてしまう。設問に「存在しない区分の組合せも、決定表の列に含まれている。」とあり、注記1のとおり存在しない組合せは「－」です。
- セット商品にも単価区分があると考え、列5を存在しない組合せとして「－」にしてしまう。セット商品は単価区分の対象外なので、(a)＝NULLが正しい組合せです。
- 一括商品変動単価を、商品提供時間と同じく一括商品すべてで参照すると考える。変動単価を適用する（単価区分が2の）一括商品だけです。
FAQ
Q: 表2だけで (a) と (b) を区別できるのはなぜですか？
A: (a) がNULLの行（3456）は (b)＝1のセット商品で、(b) がNULLの行（2345、4567）は (c)＝1の単品商品です。表1でNULLになる商品を見ると、セット商品が対象外なのは単価区分、単品商品が対象外なのは複合区分なので、(a) が単価区分、(b) が複合区分と決まります。
A: (a) がNULLの行（3456）は (b)＝1のセット商品で、(b) がNULLの行（2345、4567）は (c)＝1の単品商品です。表1でNULLになる商品を見ると、セット商品が対象外なのは単価区分、単品商品が対象外なのは複合区分なので、(a) が単価区分、(b) が複合区分と決まります。
Q: 存在しない組合せまで決定表に並べるのはなぜですか？
A: 各区分の取り得る値（NULLを含む）を全て組み合わせて列を作ると、条件の漏れがないことを確かめられるからです。そのうえで存在しない組合せは参照しない（「－」）と明示することで、外部キーが誤ったインスタンスを参照しないことを確認できます。
A: 各区分の取り得る値（NULLを含む）を全て組み合わせて列を作ると、条件の漏れがないことを確かめられるからです。そのうえで存在しない組合せは参照しない（「－」）と明示することで、外部キーが誤ったインスタンスを参照しないことを確認できます。
関連キーワード: 決定表、外部キー、参照整合性、サブタイプ、区分コード
設問2：商品ごとの区分について、(1)、(2)に答えよ。
問題文を見る（2）図3中の(d)〜(f)に入れる適切な列名を、本文中の用語を用いてすべて答えよ。
模範解答
d：曜日、時間帯番号
e：店舗番号、曜日、時間帯番号、変動単価
f：店舗番号、曜日、変動単価
解説
解答の論理構成
- 商品提供時間テーブル (d)
- 引用: “セット商品と一括商品は、商品別に、提供する曜日、時間帯を限定している。”
- → 商品番号だけでは一意にならず、曜日と時間帯番号で複合キーにする必要がある。
- 列 (d) = 「曜日、時間帯番号」。
- 単品商品変動単価テーブル (e)
- 引用: “単品商品の一部に、店舗別曜日別時間帯別に変動単価を設定する。”
- → 条件項目は店舗番号＋曜日＋時間帯番号、結果項目が変動単価。
- 列 (e) = 「店舗番号、曜日、時間帯番号、変動単価」。
- 一括商品変動単価テーブル (f)
- 引用: “一括商品の一部に、店舗別曜日別に変動単価を設定する。”
- → 条件項目は店舗番号＋曜日、結果項目が変動単価。
- 列 (f) = 「店舗番号、曜日、変動単価」。
誤りやすいポイント
- 時間帯番号を (f) にも入れてしまう
- 引用条件を読み落とすと “曜日別時間帯別” と勘違いしやすい。
- 「曜日」をコード化して別テーブルに置くと考えてしまう
- 本設問は列名を答えるだけなので、正規化の深掘りは不要。
- 変動単価列を (d) に入れる
- 商品提供時間は提供可否だけのテーブル、単価情報とは別。
FAQ
Q: “曜日” は数値型にすべきですか？
A: 設計としては1〜7の数値や0〜6の列挙型でも可ですが、本設問は列名を問うだけなので「曜日」と記述すれば十分です。
A: 設計としては1〜7の数値や0〜6の列挙型でも可ですが、本設問は列名を問うだけなので「曜日」と記述すれば十分です。
Q: “時間帯番号” を時刻で持ってはいけませんか？
A: 時刻値だとキーが煩雑になるため、図3にもある「時間帯」テーブルの主キーで参照する形が適切です。
A: 時刻値だとキーが煩雑になるため、図3にもある「時間帯」テーブルの主キーで参照する形が適切です。
Q: 変動単価と標準単価が同時に存在した場合の優先順は？
A: 【問題文】で “変動単価が設定された場合は、変動単価を標準単価に優先して適用” と明示されています。
A: 【問題文】で “変動単価が設定された場合は、変動単価を標準単価に優先して適用” と明示されています。
関連キーワード: 正規化、複合キー、連鎖参照、価格マスタ、時間帯管理
（1）図3中の“売上明細” テーブルでは、セット商品扱いを想定した場合、問題点が二つある。それぞれの問題点について、40字以内で述べよ。
模範解答
①：・1回の注文伺時分秒で同じ商品番号が発生する場合があり一意性制約違反となる。
②：・利用客からの注文、売切れによる取消とセット商品扱いを区別できない。
解説
解答の論理構成
- 図3の“売上明細”テーブルは、店舗番号、売上伝票番号、注文伺時分秒、商品番号 の組で行を一意に識別する前提です。
- しかし〔セット商品扱いの対応〕では、セット商品として扱う単品商品の注文を取り消し、数量をマイナスにした行で表します。
- よって同じ「注文伺時分秒」に同じ「商品番号」が正の数量と負の数量で2行発生し、「1回の注文伺時分秒で同じ商品番号が発生する場合があり一意性制約違反となる。」
- さらに「利用客からの注文とセット商品扱いを区別して、注文伺明細に表示する。」という要件に対し、テーブルには区別用の列が存在しません。取消行が「売切れ取消」なのか「セット商品構成取消」なのかシステム上区分できず、「利用客からの注文、売切れによる取消とセット商品扱いを区別できない。」
誤りやすいポイント
- 数量がマイナスになること自体が問題だと早合点し、主キー重複という根本を見落とす。
- 「取消理由」をメモで判断できると考え、列追加の必要性まで踏み込めない。
- 「店舗番号」や「売上伝票番号」を含むから一意だと思い込み、同時刻重複の可能性に気付かない。
FAQ
Q: マイナス数量を許容するなら主キー列に「数量」を入れれば重複を避けられますか？
A: 物理的には可能ですが、同じ商品を複数回取消した場合の重複や正負混在の集計が困難になるため非推奨です。別途シーケンス列を設ける方が一般的です。
A: 物理的には可能ですが、同じ商品を複数回取消した場合の重複や正負混在の集計が困難になるため非推奨です。別途シーケンス列を設ける方が一般的です。
Q: 区別列を追加せず、行コメントで対応してはダメですか？
A: 運用依存のコメントは検索・集計・整合性検証が難しく、要件である「区別して表示」やBI分析に対応できません。属性として定義することが望ましいです。
A: 運用依存のコメントは検索・集計・整合性検証が難しく、要件である「区別して表示」やBI分析に対応できません。属性として定義することが望ましいです。
関連キーワード: 主キー、一意性制約、ネガティブインベントリ、区分属性、正規化
（2）“売上明細” テーブルに列を追加することで、(1)の問題点を解決することができる。このときの列の追加に関して、次の(a)、(b)に答えよ。
(a)列を追加した後のテーブル構造を答えよ。
(b)追加列名とその列に設定する値の説明を答えよ。
なお、解答欄はすべて埋まるとは限らない。
模範解答
(a)：売上明細（店舗番号、売上伝票番号、注文伺時分秒、商品番号、数量、
販売単価、クーポン番号、セット商品扱い区分）
(b)：
追加列名：セット商品扱い区分
設定する値の説明：利用客からの注文、売切れによる取消とセット商品扱いを区別する区分
解説
解答の論理構成
- 現状把握
“売上明細”の主キーは（店舗番号、売上伝票番号、注文伺時分秒、商品番号）で、状態を示す列がない。 - 業務要件の抜粋
- 「利用客からの注文とセット商品扱いを区別して、注文伺明細に表示する」
- 「注文の取消しは、新たな注文伺の中で当該商品の数量をマイナスにする」
- 問題点
「数量＝-1」だけでは “売切れ取消” なのか “セット商品への置換” なのか判別不能。 - 解決策
区分列を1つ追加し、3状態を表現。
例：1＝利用客注文、2＝売切れ取消、3＝セット商品扱い。 - テーブル再定義
売上明細（店舗番号、売上伝票番号、注文伺時分秒、商品番号、数量、販売単価、クーポン番号、セット商品扱い区分） - 列説明
追加列名：セット商品扱い区分
値：
・1：利用客からの通常注文
・2：売切れが判明した後の取消
・3：自動判定によるセット商品扱い
誤りやすいポイント
- 「数量が負なら取消し」と短絡的に考え、セット商品扱いを区別せず失点。
- 列を増やさずに別テーブルを設ける案を出し、設計変更コストを無視。
- 既存の主キーに追加列を含めてしまい、レコード一意性を崩すミス。
FAQ
Q: 数量を0にすれば扱いを区別できるのでは？
A: 「売切れ取消」も「セット商品扱い」もともに数量をマイナスで記録する仕様なので、0では履歴が残らず要件を満たしません。
A: 「売切れ取消」も「セット商品扱い」もともに数量をマイナスで記録する仕様なので、0では履歴が残らず要件を満たしません。
Q: “セット商品扱い区分” をNULL可にしても良い？
A: 互換性重視で既存レコードにNULLを入れる選択肢はありますが、後続処理で CASE 分岐が煩雑になるため非NULL（デフォルト1など）とするのが実戦的です。
A: 互換性重視で既存レコードにNULLを入れる選択肢はありますが、後続処理で CASE 分岐が煩雑になるため非NULL（デフォルト1など）とするのが実戦的です。
Q: 区分値を文字列で管理しても問題ない？
A: 可読性は上がりますが、数値の方が索引サイズと比較・集計コストを抑えられます。業務区分は固定長の数値型で管理するのが一般的です。
A: 可読性は上がりますが、数値の方が索引サイズと比較・集計コストを抑えられます。業務区分は固定長の数値型で管理するのが一般的です。
関連キーワード: 正規化、主キー、区分管理、売上履歴、セット商品
