平成23年度特別 データベーススペシャリスト 午後Ⅰ 問2

項目が何で決まるかを見極める

データ設計の問題では、ある項目の値がどの項目の値によって決まるのかという依存の関係を、業務の説明に即して一つずつ確かめることが検討の出発点になります。思い込みではなく記述を根拠に判断しましょう。

この問題で問われていること

問われているのは個々の用語の暗記よりも、業務上のルールをデータの構造に落とし込むときの考え方です。一つの項目の値を決めるのに何が必要かを、業務の状況を具体的に思い浮かべながら確かめる姿勢が求められます。

視点

関係データベースの設計では、ある項目の値を知れば別の項目の値が一通りに定まるかどうか、という依存の関係が判断の土台になります。この関係は、項目の名前から一般論で決めるものではありません。同じ名前の項目でも、業務の運用しだいで決まり方が変わります。そのため、時点や条件が変わったときに値が変わりうるか、同じ値が複数の行に繰り返し現れないかを、業務の記述と照らして確かめることが大切です。

確認ポイント

• 注目している項目の値が、どの項目の値の組合せで一通りに定まるのかを、業務の記述を根拠に確かめたか
• 業務の条件や時点が変わったときに、その項目の値が変わりうるかどうかを具体的な場面で想像したか
• 同じ内容が複数の箇所に重複して記録される状況が生じないかを、依存の関係から見直したか

解説

飲食店の割引クーポン、変動単価、セット商品扱いを題材にしたテーブル設計の問題です。多値の利用条件を含む表の候補キーと正規化、区分値の組合せ（NULLを含む）による参照先の限定、セット商品扱いで売上明細の主キーが重複する問題と区分列の追加による解決が問われています。採点講評では、候補キーをクーポン番号だけと答えた解答、決定表で存在しない組合せを『参照する』とした解答、設問3(1)で問題点を片方しか挙げていない解答が多かったと指摘されています。

問題の全体像

B社は飲食店チェーンで、これまで店舗の手作業で行っていた変動単価と割引クーポンの運用をシステム化します。商品は単品商品と複合商品（セット商品・一括商品）に分かれます。標準単価のほかに、単品商品には店舗別曜日別時間帯別、一括商品には店舗別曜日別の変動単価があります。セット商品と一括商品は、提供する曜日と時間帯が限られています。クーポンには、利用可能商品・店舗・時間が複数あり得る利用条件が付きます。売上は注文伺単位で明細を記録します。公式解答例の出題趣旨では、利用条件が限られるサービスのテーブル実装や、区分値による商品特性の定義を通して、業務要件の分析力とテーブル設計の能力を問うとされています。設問1はクーポン利用条件テーブルの候補キーと正規化、設問2は商品の区分値と各変動単価テーブルの設計、設問3はセット商品扱いを入れたときの売上明細テーブルの問題点と改善を扱います。

設問1(1)

〔業務の概要〕2.(1)の利用条件に着目します。割引額と有効期間はクーポンごとに『一つ』ですが、利用可能商品と利用可能店舗は『一つ又は複数』、利用可能時間は『曜日ごとの複数の時間帯』です。つまり、クーポン番号が一つ決まっても、商品番号・店舗番号・曜日・時間帯番号は複数の値を取ります。図3のクーポン利用条件テーブルは、これらを一つの表に並べているため、行を一意に識別するにはクーポン番号に加えて商品番号、曜日、時間帯番号、店舗番号のすべてが必要です。公式解答例でも候補キーは{クーポン番号、 商品番号、 曜日、 時間帯番号、 店舗番号}です。採点講評では、『クーポン番号』だけを答えた解答が多く、正答率が低かったとされています。クーポン番号は一意でも、この表の行を一意にするわけではない点に注意しましょう。

設問1(2)

(1)の候補キーのうち、クーポン番号だけで開始年月日、終了年月日、割引額が決まります。これは候補キーの一部への関数従属（部分関数従属）なので、この表は第2正規形も満たしていません。そこで、これらをクーポン番号を主キーとする表に分け、本文の語句を使って『クーポン割引額有効期間（クーポン番号、 開始年月日、 終了年月日、 割引額）』のように命名します。残りは全属性がキーの表になります。公式解答例では、これを『クーポン利用可能商品店舗時間（クーポン番号、 商品番号、 店舗番号、 曜日、 時間帯番号）』の1表のまま残す解答と、商品・時間・店舗の独立した多値性に着目して『クーポン利用可能商品（クーポン番号、 商品番号）』『クーポン利用可能時間（クーポン番号、 曜日、 時間帯番号）』『クーポン利用可能店舗（クーポン番号、 店舗番号）』の3表に分ける解答の、どちらも示されています。曜日と時間帯番号は『曜日ごとの時間帯』として組で意味を持つので、同じ表に置きます。採点講評では、正答率は高く、候補キーがつかみにくい場合は第3正規形に分解して考えるのも有効だとされています。

設問2(1)(a)a

表1の区分と表2の値を照らし合わせます。表1では、単複区分は全商品が対象なのでNULLになりません。複合区分は単品商品のとき、単価区分はセット商品のときNULLです。表2の列aは、商品ウ（b=1、c=2）でNULLです。列cがNULLを持たないので単複区分、列bの1はセット商品と読み取れます。つまり、ウはセット商品であり、セット商品でNULLになるのは単価区分です。ほかの行でもaは1か2のいずれかで、変動単価の適用有無として矛盾しません。したがって公式解答例のとおり、aは③単価区分です。

設問2(1)(a)b

列bは、商品イとエでNULL、アで2、ウで1です。イとエは列cが1、つまり単品商品です。単品商品のときNULLになるのは、全複合商品だけが対象の複合区分です。また、値1と2がそれぞれセット商品と一括商品に当たり、アは一括商品、ウはセット商品と解釈できます。したがって公式解答例のとおり、bは②複合区分です。

設問2(1)(a)c

列cはどの行でもNULLにならず、値は1か2です。全商品が対象で、1:単品商品、2:複合商品を表す単複区分に当たります。列bがNULL（単品）の行ではcが1、列bに値がある（複合）行ではcが2となっており、整合します。したがって公式解答例のとおり、cは①単複区分です。

設問2(1)(b)

まず、実際に存在する区分の組合せを整理します（a=単価区分、b=複合区分、c=単複区分）。単品商品は（a=1または2、 b=NULL、 c=1）、セット商品は（a=NULL、 b=1、 c=2）、一括商品は（a=1または2、 b=2、 c=2）です。これ以外の組合せは存在しません。次に参照先を当てはめます。商品提供時間は、提供する曜日・時間帯が限られるセット商品と一括商品が参照します。表3(1/2)では一括商品で変動単価なし（1、2、2）の列にXが付いています。単品商品変動単価は、変動単価を適用する単品商品（2、NULL、1）だけが参照します。一括商品変動単価は、変動単価を適用する一括商品（2、2、2）だけが参照します。そのため、表3(2/2)の各列は公式解答例のとおりになります。(2、2、2)は一括商品で変動単価を適用するので、商品提供時間X、単品商品変動単価－、一括商品変動単価Xです。(2、NULL、1)は変動単価を適用する単品商品なので、単品商品変動単価だけXです。(NULL、1、2)はセット商品なので、商品提供時間だけXです。(2、NULL、2)、(NULL、1、1)、(NULL、2、1)、(NULL、2、2)、(NULL、NULL、1)、(NULL、NULL、2)は存在しない組合せなので、すべて－です。採点講評では、存在しない列にXを付けた解答が多かったと指摘されています。商品区分に基づいて分類すれば、存在しない列ははっきりするはずです。

設問2(2)d

〔業務の概要〕1.(2)①に『セット商品と一括商品は、商品別に、提供する曜日、時間帯を限定している』とあります。商品ごとに提供できる曜日と時間帯の組を複数持つので、商品提供時間テーブルには曜日と時間帯番号を追加し、商品番号とともに主キーにします。時間帯は図3の時間帯テーブルの時間帯番号で表します。公式解答例では、dは『曜日、 時間帯番号』（いずれも主キーの一部）です。

設問2(2)e

本文に『単品商品の一部に、店舗別曜日別時間帯別に変動単価を設定する』とあります。変動単価を一意に決めるには、商品番号に加えて店舗番号、曜日、時間帯番号が必要です。これらを主キーにして、従属する属性として変動単価を持たせます。公式解答例では、eは『店舗番号、 曜日、 時間帯番号、 変動単価』で、変動単価以外が主キーの一部です。

設問2(2)f

一括商品の変動単価は『店舗別曜日別』に設定され、時間帯別ではありません。そのため、主キーは商品番号・店舗番号・曜日で、非キー属性が変動単価になります。単品商品変動単価と違って時間帯番号を含めないのがポイントです。公式解答例では、fは『店舗番号、 曜日、 変動単価』です。

設問3(1)①

売上明細の主キーは{店舗番号、 売上伝票番号、 注文伺時分秒、 商品番号}です。図4の20時05分20秒の注文伺では、利用客が注文した『玉子焼き 1』と、セット商品扱いによる『＊玉子焼き −1』が同じ注文伺の中に並んでいます。どちらも注文伺時分秒と商品番号が同じなので、主キーの値が重複します。また、その場の取消しは注文がなかったものとするルールがありますが、セット商品扱いの行を差し引きして消すこともできません。公式解答例は、『1回の注文伺時分秒で同じ商品番号が発生する場合があり一意性制約違反となる』という趣旨です。40字以内に収めるには、『同一注文伺時分秒で同じ商品番号の行が生じる』ことと『主キーの一意性制約違反』の2点を入れます。

設問3(1)②

〔セット商品扱いの対応〕に、『利用客からの注文とセット商品扱いを区別して、注文伺明細に表示する』とあります。しかし図3の売上明細には、数量のマイナスが売切れによる取消し（図2のサバ塩焼き−1）なのか、セット商品扱いによる取消し扱い（図4の＊印の行）なのかを見分ける列がありません。セット商品のプラス行も、利用客の注文なのかセット商品扱いなのかを区別できません。公式解答例は、『利用客からの注文、売切れによる取消とセット商品扱いを区別できない』という趣旨です。採点講評では、主キーの一意性制約違反と区別不能の二点を求めたのに、片方しか書いていない解答が多かったとされています。一つ目がキーの観点なら、二つ目は業務表示の観点というように、観点を変えて検討することが大切です。

設問3(2)(a)

(1)の二つの問題は、行の種類を表す区分列を一つ追加し、それを主キーに含めれば同時に解決できます。区分が異なれば、同じ注文伺時分秒・同じ商品番号でも別の行として識別できるので一意性が保たれます。区分の値から明細の種類も判別できます。公式解答例では、『売上明細（店舗番号、 売上伝票番号、 注文伺時分秒、 商品番号、 数量、 販売単価、 クーポン番号、 セット商品扱い区分）』とし、店舗番号・売上伝票番号・注文伺時分秒・商品番号・セット商品扱い区分を主キー（実線下線）、クーポン番号を外部キー（破線下線）として表記しています。問題文にあるとおり、主キーと外部キーの明記を忘れないようにしましょう。

設問3(2)(b)

公式解答例では、追加列名は『セット商品扱い区分』です。設定する値の説明は、『利用客からの注文、売切れによる取消とセット商品扱いを区別する区分』とされています。説明には、利用客からの注文、売切れによる取消し、セット商品扱いの三種類を識別することを明示します。解答欄は複数ありますが、公式解答例で追加する列は一つだけで、すべての欄が埋まるとは限りません。

学習のポイント

『一つ又は複数』という記述から多値属性を見つけて候補キーを組み立てる力、区分値とNULLの組合せから実在する商品種別を列挙する力、例示された帳票から主キー重複を検出する力を重点的に鍛えましょう。