平成26年度春期 データベーススペシャリスト 午後Ⅰ 問3

表の分け方と整合性の関係を追う

サブタイプをどのように表へ割り当てるかによって、制約の置き場所や問合せの書き方、更新の手順が変わり得ます。設問が問う場面で、どの表のどの行が関わるかを一つずつ具体的に想定しながら確認していきましょう。

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

確認すべき観点は一つ、「対象となるデータが実際にどの表のどの列に格納されるか」です。これを設問の場面ごとに自分で書き出すと、制約・SQL・更新順序について根拠を持って判断する足掛かりになります。

視点

データモデル上の一つの概念は、物理的には一つの表にも複数の表にもなり得ます。表の構成が決まると、値の必須性、参照の向き、行を追加・削除する際の前後関係、問合せで必要となる表の組合せが連動して決まります。方式名や用語の印象だけで判断せず、具体的な行を仮に一件置き、それがどの表に入るかを書き出して追跡してください。

確認ポイント

• 仮のデータを一件想定し、それが格納される表と列を書き出したか
• 書き出した表の構成から、その値が必須かどうか、他の表を参照するかを自分で確かめたか
• 追加・削除や問合せの場面で、関わる表とその順序を自分の書き出しに照らして確かめたか

解説

単品商品とセット商品というサブタイプを、1テーブルに統合する案1と、テーブルを分割する案2で実装した場合の違いを問う問題です。問われているのは、制約定義（UNIQUE、外部キー、検査制約）、同じ結果を得るSQLの書き分け、性能のための述語追加、そして在庫更新の順序によるデッドロックの可能性とその回避策です。

問題の全体像

E社の受注管理システムでは、商品を単品商品とセット商品に分けて管理します。セット商品の在庫が足りない場合は、構成する単品商品を引き当てます。Fさんは、サブタイプを“商品”テーブルに統合する案1と、“単品商品”“セット商品”テーブルに分割する案2を比較しています。出題趣旨では、統合案と分割案について、CREATE TABLE文の制約定義とSQL文の違い、さらに排他制御で考慮すべき点を理解しているかを問うとしています。採点講評によると、設問1の正答率は高く、設問2と設問3の正答率は低かったとのことです。

設問1(1)(a)

公式解答例では【a】は「表示順」です。要求仕様2(2)に「表示される順番は、商品全体で重複がないように、商品企画担当者が決めた表示順に基づく」とあるので、一意であるべき非キー列は表示順です。商品番号はすでにPRIMARY KEYに指定されており、PRIMARY KEY制約には一意性制約が含まれます。採点講評では、商品番号をUNIQUE制約にも定義した解答が散見されたと指摘されています。

設問1(1)目的

公式解答例の趣旨は「商品全体で重複がないように商品の表示順を決めるため」または「商品の表示順を商品全体で一意にするため」です。要求仕様にある「商品全体で重複がない」「表示順」という字句を使い、表示順の一意性を保証することが目的だと30字以内で書きます。

設問1(2)

本文に「ユニーク索引は、一つのテーブル内でキー列の一意性を保証するもの」であり「複数のテーブルにまたがって作成することはできない」とあります。案2では表示順が“単品商品”と“セット商品”の2テーブルに分かれています。そのため、テーブルごとにUNIQUE制約を付けても、単品商品とセット商品の間で表示順が重複することは防げません。公式解答例では、この本文の字句を使い、ユニーク索引は1テーブル内でしか一意性を保証しないから、またはテーブルをまたいで定義できないから、とまとめています。

設問1(3)(ア)

案1では単品商品とセット商品が同じ“商品”テーブルに入っています。“セット商品構成”の単品商品番号に“商品”を参照する外部キーを定義しても、参照先には単品区分'N'のセット商品の商品番号も存在します。そのため、外部キー制約だけでは単品商品番号列にセット商品番号が入ることを防げません。項番1（セット商品番号列に単品商品番号を設定できてしまう）と対になる内容で、公式解答例は「単品商品番号列にセット商品番号を設定できてしまう」です。

設問1(3)(イ)

案2では商品が2テーブルに分かれていますが、“在庫”テーブルの商品番号には単品商品とセット商品の両方が入ります。1つの外部キーが参照できるのは1つのテーブルだけです。例えば“単品商品”を参照するように定義すると、セット商品の在庫行を挿入できなくなります。公式解答例では「“在庫”テーブルに行を挿入できない」「異なるテーブルの主キーを同じ外部キーに入力できない」「単品商品番号とセット商品番号を同じ外部キーに入力できない」が示されています。採点講評では、案1の不都合と同じ発想の「商品番号列にセット商品番号を設定できてしまう」という誤答が多かったと指摘されています。

設問1(4)(b)

図5の1行目は、単品区分='Y'（単品商品）の行が満たすべき条件です。ここには①「社内原価 IS NOT NULL」がすでに書かれているので、空欄【b】【c】に①を再び入れることはありません。単品商品には化粧箱番号も詰合せ日数もないので、残る2つの空欄には④「化粧箱番号 IS NULL」と⑥「詰合せ日数 IS NULL」が入ります。公式解答例では【b】を④、【c】を⑥とし、備考でbとcは順不同としています。つまり【b】【c】の組として④と⑥をそろえることが求められ、どちらかが欠けたり①を重ねたりすると公式解答例とは一致しません。

設問1(4)(c)

【b】と同じく、単品区分='Y'の行ではセット商品固有の列がNULLでなければなりません。①は図5に記載済みなので、【b】【c】の組には④「化粧箱番号 IS NULL」と⑥「詰合せ日数 IS NULL」が入ります。公式解答例では【c】を⑥としています（bとcは順不同）。単品商品の詰合せ日数には、未定かどうかにかかわらず値が入らないので、⑥で「必ずNULL」と制約します。

設問1(4)(d)

単品区分='N'（セット商品）の行が満たすべき条件です。表1によると、社内原価は単品商品の属性なので、セット商品では②「社内原価 IS NULL」とします。また、セット商品には必ず一つの化粧箱が使われるため、③「化粧箱番号 IS NOT NULL」も必要です。公式解答例は【d】が②、【e】が③で、備考ではdとeが順不同とされています。

設問1(4)(e)

公式解答例では【e】は③「化粧箱番号 IS NOT NULL」です（dとは順不同）。セット商品の詰合せ日数は、表1に「未定の場合、NULLが設定される」とあり、値が入る場合もNULLの場合もあります。そのため⑤と⑥のどちらも制約に使えません。採点講評でも、dまたはeで⑥を選ぶ誤答が散見されたと指摘されており、未定の場合だけNULLになるのであってNULLとは限らない点に注意が必要です。

設問2(1)(f)

SQL1では、注文明細の各行が“商品”と結合し、単品商品なら化粧箱番号が、セット商品なら社内原価がNULLの行として返されます。案2のSQL2では、注文明細の1行は“単品商品”と“セット商品”のどちらか一方にしか一致しません。そのため、2回の結合をどちらも外結合にし、一致しない側の列をNULLにして注文明細の全行を残す必要があります。公式解答例では【f】は「LEFT OUTER」です。

設問2(1)(g)

SQL3では、単品商品と結合した結果と、セット商品と結合した結果をUNION ALLでつなぎます。それぞれのSELECTでは、一致した行だけを取り出し、相手側の列はCAST(NULL AS INT)で補っています。ここで外結合を使うと、一致しない明細行が余分に出てしまいます。そのため各SELECTは内結合でよく、公式解答例では【g】は「INNER」です。

設問2(2)

求めたいのは、注文されたセット商品を構成する単品商品の合計数です。セット商品1個当たりの単品商品の数量が構成数なので、注文数と構成数を掛けたものを合計します。公式解答例では【h】は「M.注文数*K.構成数」です。採点講評では「構成数」だけとする誤答が多かったと指摘されています。注文明細の注文数の意味を考えれば、掛け算が必要だとわかります。

設問2(3)

案2のSQL5では“セット商品”テーブルと結合するので、単品商品の明細行は②の段階で除外されます。一方、案1のSQL4では、統合された“商品”テーブルとの結合に条件がありません。そのため単品商品の明細行でも③の“セット商品構成”主索引へのアクセスが発生し（該当行はありません）、アクセス頻度が増えます。公式解答例では、セット商品だけに絞り込む述語「P.単品区分='N'」をWHEREにANDで追加します。採点講評では、単品区分の役割を性能面に応用する力が不十分だったと述べられています。

設問3(1)(ウ)

設計(3)では在庫行を商品一覧画面の表示順に更新し、設計(4)ではセット商品が不足した場合に、構成する単品商品を“セット商品構成”の主キー順、つまり単品商品番号順に更新します。TR1は単品商品2個を表示順に更新します。TR2は単品商品1個と、在庫が不足したセット商品を構成する単品商品を更新します。両者が同じ単品商品の行を逆の順序でロックする可能性があるので、公式解答例は「×」です。

設問3(1)(エ)

TR3は、セット商品が在庫不足であれば、構成する単品商品を単品商品番号順に更新します。TR1は単品商品を表示順に更新します。表示順と単品商品番号順は一致するとは限らないので、2つの単品商品の行を互いに逆順でロックして待ち合うおそれがあります。公式解答例は「×」です。

設問3(1)(オ)

TR3同士では、どちらもまずセット商品の在庫行を1行だけ更新し、在庫が不足した場合は構成する単品商品を常に単品商品番号順に更新します。単品商品のロック順序が逆になることはないので、待ちが発生してもデッドロックにはなりません。公式解答例は「○」です。採点講評では、オの正答率が低かったと述べられています。セット商品1個だけを注文するトランザクションは常に単品商品番号順に単品商品を調べ、逆順にはならないことに気付くことがポイントです。

設問3(1)(カ)

TR4は2つのセット商品を表示順に処理し、1つ目のセット商品の構成単品をロックしたまま2つ目のセット商品に進みます。例えば、TR4が1つ目のセットの構成単品をロックした後、TR3がロックしている2つ目のセット商品の行を待つとします。そのTR3が、TR4のロックしている単品商品を必要とすれば、デッドロックになります。公式解答例は「×」です。

設問3(2)

デッドロックの原因は、表示順（セット商品の処理順）と単品商品番号順という異なる順序で在庫行を更新していることです。一つのトランザクション内で“在庫”テーブルの行を、主キーである同じ列の順番で更新するように統一すれば、ロックを取る順序が全トランザクションでそろい、循環待ちが起きなくなります。公式解答例では列名は「商品番号」です。

学習のポイント

統合案では区分列と検査制約で整合性を保ち、分割案ではテーブルをまたぐ一意性・参照整合性をどう保証するかを整理しましょう。あわせて、行の更新順序を統一してデッドロックを防ぐ考え方を身に付けることが重要です。