データベーススペシャリスト 2019年 午後1 問01
データベース設計に関する次の記述を読んで、設問1〜3に答えよ。
A社は、スポーツイベント(以下、大会という)の運営サービスを主催者に提供している大会運営サービス会社である。 A社では、大会運営システムを新たに構築することになり、B君がデータベース設計を任された。
〔大会の登録から参加申込受付の準備まで〕
1．主催者
(1) 大会を主催する団体を主催者という。
(2) 主催者は、主催者番号で識別し、主催者名、代表者氏名、住所、電話番号、メールアドレスを登録する。
2．種目と種目分類
(1) フルマラソン、ハーフマラソン、自転車ロードレースなどを種目という。
(2) 種目は、種目コードで識別し、種目分類コードで、ランニング、自転車レースなどに分類する。
3．大会
大会は、大会番号で識別し、大会名、開催年月日、開催場所の都道府県コード、主催者番号を登録する。
4．運営サービス
(1) A社が主催者に提供するサービスを運営サービスという。 運営サービスには、大会に関する告知サービス、大会への参加申込みを受け付けるエントリサービス、記録計測サービスなどがある。
(2) 運営サービスは、運営サービスコードで識別し、運営サービス名、課金単位、単価を登録する。
(3) 主催者は、大会ごとに一つ以上の運営サービスを選択する。 A社は、主催者が選択した運営サービスを登録する。
5．エントリ枠
(1) 大会において、参加希望者からの参加申込みを受け付ける単位をエントリ枠という。主催者は、大会ごとに一つ以上のエントリ枠を登録する。 エントリ枠は、大会番号とエントリ枠番号で識別し、エントリ枠名、エントリ枠説明、種目コード、定員、参加費用、募集期間(募集開始年月日〜募集終了年月日)などを登録する。
(2) 一つの大会において、幾つかのエントリ枠に同じ種目を登録することがある。例えば、フルマラソンに対して、一般枠、地元優先枠、アスリート優先枠の三つのエントリ枠を登録することがある。
(3) エントリ枠に対する参加者を決める方式には、先着順と抽選があり、先着順抽選区分で分類する。 抽選の場合は、抽選年月日を登録する。 抽選年月日には、募集終了年月日よりも後の日付を登録する。
(4) エントリ枠には、エントリ枠状態を保持する。 エントリ枠状態の取り得る値には、参加者を決める方式ごとに、先着順の場合は、‘募集前'、'募集中'、‘参加者確定’があり、抽選の場合は、'募集前'、'募集中'、'抽選中'、'参加者確定'がある。
6．アイテム
(1) 大会で、参加者に配布する参加賞や、ナンバカード、ICタグなどをアイテムという。
(2) アイテムは、アイテムコードで識別し、アイテム名を登録する。
(3) 主催者は、大会ごとに利用するアイテムを複数登録することができる。
〔大会への参加申込みから参加費用の入金まで〕
1．会員
(1) 大会の参加希望者は、あらかじめ会員登録をする。
(2) 会員は、会員番号で識別し、会員氏名、性別、生年月日、住所、電話番号、メールアドレスを登録する。
2．参加申込み及びエントリ枠状態の設定
(1) 会員は、参加したい大会に対して、エントリ枠を指定して参加申込みを行う。
(2) 会員は、一つの大会について一つのエントリ枠だけ参加申込みできる。
(3) 参加申込みは、大会番号、会員番号で識別し、参加申込年月日を登録する。
(4) 参加申込みと同時に、エントリ枠の参加申込数も合わせて更新する。
(5) エントリ枠状態は、次のように設定する。
① エントリ枠の登録においては、初期値を‘募集前’にする。
② 募集期間中は‘募集中’にする。
③ エントリ枠が先着順の場合
・募集期間が終わったら ‘参加者確定’にする。
・参加申込数が定員に達したら、募集期間中であっても‘参加者確定’にする。
④ エントリ枠が抽選の場合
・募集期間が終わり、参加申込数が定員以下だったら ‘参加者確定’にする。
・募集期間が終わり、参加申込数が定員を超えていれば‘抽選中’にし、その後、抽選年月日に抽選を実施した上で ‘参加者確定’にする。
(6) エントリ枠状態が ‘募集中’の間だけ、参加申込みを受け付ける。
3．抽選結果の登録
抽選を実施したら、参加申込みに抽選結果を登録する。
4．参加費用の入金及びポイントの付与
(1) 参加が確定したら、会員は参加費用を支払う。
(2) A社は、会員の参加費用の支払を確認して入金年月日を登録し、参加費用に対して一定割合のポイントを会員に付与する。
(3) 会員は、保持しているポイントを、1ポイント=1円として、参加費用に充てることができる。
(4) 会員は、ポイントを使用する場合、使用ポイントを登録し、参加費用から使用ポイントを差し引いた額を支払う。
〔概念データモデルと関係スキーマの設計〕
B君が設計した概念データモデルを図1に、関係スキーマを図2に示す。
〔指摘事項〕
C部長は、概念データモデル及び関係スキーマに対して、次を指摘した。
・エントリ枠状態と抽選実施を決める決定表が必要である。
・この決定表は、日付が変わった時点及び参加申込受付時点で評価する。
この指摘を受けて作成した、日付が変わった時点及び参加申込受付時点で評価する決定表を表1に示す。 この決定表の各条件の取り得る値は次のとおりである。
なお、( )内に略字がある場合、表1は略字で表す。
先着順抽選区分 ：先着順、抽選
募集期間に対する本日 ：募集期間よりも前の日 (前)、募集期間中 (中)、募集期間よりも後の日 (後)
参加申込数 ：定員未満 (未満)、定員以下 (以下)、定員到達 (到達)、定員超過 (超過)
抽選年月日に対する本日 ：抽選年月日よりも前の日 (前)、当日、抽選年月日よりも後の日 (後)
〔新たな要件の追加〕
1．多段階抽選方式
例えば、地元優先枠、アスリート優先枠、一般枠の三つの枠があり、会員が地元優先枠又はアスリート優先枠に参加申込みをして落選したら、その後に抽選を行う一般枠の抽選対象に加えるというような多段階に抽選するサービスを新たに追加することになった。
このサービスを実現するために、多段階抽選方式の仕様を次のように決定した。
・多段階抽選の対象のエントリ枠には、後続のエントリ枠を一つ設定する。
・後続のエントリ枠が設定されたエントリ枠で落選した会員は、後続するエントリ枠の抽選対象に加える。
・エントリ枠の抽選ごとに抽選結果を登録する。
2．ポイント有効期限
ポイントに有効期限を設けることにした。 ポイントの有効期限は、付与された日から1年であり、有効期限を超過したポイントは消失する。 ポイントの使用は、有効期限の近いものから行う。
解答に当たっては、巻頭の表記ルールに従うこと。 ただし、エンティティタイプ間の対応関係にゼロを含むか否かの表記は必要ない。 また、関係スキーマの表記又は関係スキーマに入れる属性名を答える場合、主キーを表す実線の下線及び外部キーを表す破線の下線を明記すること。
なお、エンティティタイプ間のリレーションシップには “多対多” のリレーションシップを用いないこと。
（1）図2中の(a)〜(g)に入れる適切な属性名を答えよ。（d, e, f, gは順不同）
模範解答
a：種目分類コード
b：主催者番号
c：種目コード
d：大会番号
e：会員番号
f：入金年月日
g：使用ポイント
解説
解答の論理構成
- (a) の決定
- 【問題文】「種目は、種目コードで識別し、種目分類コード で、ランニング、自転車レースなどに分類する」
- よって “種目” 表には “種目分類コード” が必要。
- (b) の決定
- 【問題文】「大会は、大会番号で識別し、…、主催者番号 を登録する」
- “大会” 表に欠けている外部キーは “主催者番号”。
- (c) の決定
- 【問題文】「エントリ枠は、大会番号とエントリ枠番号で識別し、…、種目コード、定員…を登録する」
- よって “エントリ枠” 表の不足分は “種目コード”。
- (d)(e) の決定（順不同）
- 【問題文】「参加申込みは、大会番号、会員番号で識別 し、参加申込年月日を登録する」
- 主キーとなる2属性を (d)(e) に割り当てる。
- (f) の決定
- 【問題文】「A社は、会員の参加費用の支払を確認して 入金年月日 を登録」
- 支払確認で追加される属性は “入金年月日”。
- (g) の決定
- 【問題文】「会員は、ポイントを使用する場合、使用ポイント を登録し、参加費用から…」
- 参加費用内訳を保持する “使用ポイント” が必要。
誤りやすいポイント
- 「参加申込みは…識別」の一文を読み飛ばし、エントリ枠番号まで主キーに含めてしまう。
- “入金年月日” を “支払日” など別名にしてしまい、原文引用要件に抵触。
- (a) を “種目分類名” と誤答する。分類はマスタ参照なのでコードが正しい。
- “使用ポイント” を会員ポイント表に入れると、支払ごとの履歴が残せなくなる。
FAQ
Q: エントリ枠番号を主キーに含めないと一意にならないのでは？
A: 【問題文】で「会員は、一つの大会について一つのエントリ枠だけ参加申込みできる」と定義されているため、(大会番号、会員番号) で一意になります。
A: 【問題文】で「会員は、一つの大会について一つのエントリ枠だけ参加申込みできる」と定義されているため、(大会番号、会員番号) で一意になります。
Q: “ポイント残高” があるのに “使用ポイント” を別に持つ理由は？
A: “ポイント残高” は最新の残高を示す集約値、対して “使用ポイント” は各参加申込み取引における消費量を保持し、後続の残高更新や監査に使います。
A: “ポイント残高” は最新の残高を示す集約値、対して “使用ポイント” は各参加申込み取引における消費量を保持し、後続の残高更新や監査に使います。
Q: “入金年月日” がNULLのままでも良いのですか？
A: 支払未確認の状態ではNULLで保持し、確認後に更新します。これにより未入金レコードを抽出できます。
A: 支払未確認の状態ではNULLで保持し、確認後に更新します。これにより未入金レコードを抽出できます。
関連キーワード: リレーショナルモデル、外部キー、主キー、正規化、参照整合性
（2）図1のリレーションシップは未完成である。 必要なリレーションシップを全て記入し、図を完成させよ。
模範解答
解説
解答の論理構成
-
要件から必須のエンティティを洗い出す
・【問題文】「主催者は、大会ごとに一つ以上の運営サービスを選択する。」より「主催者」「大会」「運営サービス」「大会運営サービス」が必要です。
・【問題文】「主催者は、大会ごとに一つ以上のエントリ枠を登録する。」より「大会」「エントリ枠」が必要です。
・【問題文】「フルマラソン、 ハーフマラソン、 自転車ロードレースなどを種目という。」より「種目」が必要で、同節 (2) 「種目は、種目コードで識別し、 種目分類コードで、 ランニング、 自転車レースなどに分類する。」から「種目分類」との階層関係が導けます。
・【問題文】「主催者は、大会ごとに利用するアイテムを複数登録することができる。」より「アイテム」「大会アイテム」が必要です。
・【問題文】「会員は、一つの大会について一つのエントリ枠だけ参加申込みできる。」より「会員」「参加申込み」「エントリ枠」の関連が決まります。
・【問題文】「A社は、会員の参加費用の支払を確認して入金年月日を登録し、参加費用に対して一定割合のポイントを会員に付与する。」から「会員ポイント」をエンティティとして独立させ、「参加申込み」で使用／付与情報を管理する必要があります。 -
多重度（一対多）の決定
・親側キーが子側の外部キーになる明記を求める設問なので、“多対多”は禁止。要件文から一方が必ず１側になるかを判定します。
例) 【問題文】「種目は、種目分類コードで、 ランニング、 自転車レースなどに分類する。」→「種目分類」１に対し「種目」多。 -
リレーションシップの具体化
上記①②を総合し、図１を完成させると模範解答図のように１１本のリレーションシップが引けます。- 「種目分類」→「種目」
- 「主催者」→「大会」
- 「運営サービス」→「大会運営サービス」
- 「大会」→「大会運営サービス」
- 「アイテム」→「大会アイテム」
- 「大会」→「大会アイテム」
- 「大会」→「エントリ枠」
- 「種目」→「エントリ枠」
- 「エントリ枠」→「参加申込み」
- 「会員」→「参加申込み」
- 「会員ポイント」→「参加申込み」
-
特殊化（サブタイプ）
【問題文】「エントリ枠には、先着順と抽選があり…抽選の場合は、 抽選年月日を登録する。」から抽選専用属性を持つ「抽選エントリ枠」をサブタイプ化し、スーパタイプ「エントリ枠」と三角（）で接続します。 -
完成図の整合確認
・全エンティティが要件に登場する。
・“多対多”はなく、全て中間エンティティ（例：「大会運営サービス」）を用いて解消済み。
・サブタイプ／スーパタイプ間の重複属性はなし。
誤りやすいポイント
- 「大会運営サービス」「大会アイテム」のような中間エンティティを置かずに“多対多”で描いてしまう。
- 「会員ポイント」を「会員」に従属した弱エンティティと誤解し、リレーションシップを省略して「参加申込み」側のポイント使用・付与が表現できなくなる。
- 「種目」→「エントリ枠」を忘れ、「大会」だけで識別できると勘違いする。要件に「一つの大会において、幾つかのエントリ枠に同じ種目を登録することがある。」があるため、種目との関係は必須です。
- 「抽選エントリ枠」を独立エンティティにしてしまい、「エントリ枠」との継承関係（三角記号）を落とす。
FAQ
Q: 「エントリ枠」から「参加申込み」は必ず１対多ですか？
A: はい。【問題文】「会員は、一つの大会について一つのエントリ枠だけ参加申込みできる。」ので１人の会員につき１行ですが、エントリ枠側から見ると多数の会員が申し込むため多側になります。
A: はい。【問題文】「会員は、一つの大会について一つのエントリ枠だけ参加申込みできる。」ので１人の会員につき１行ですが、エントリ枠側から見ると多数の会員が申し込むため多側になります。
Q: 「大会運営サービス」と「大会アイテム」は同じように見えますが、なぜ二つに分けるのですか？
A: 属性内容も主キーも異なるためです。「大会運営サービス」には「運営サービスコード」が必要で課金単位・単価と関係しますが、「大会アイテム」は「アイテムコード」を持ち費用情報は不要という違いがあります。中間エンティティとして分けることで将来の拡張にも強くなります。
A: 属性内容も主キーも異なるためです。「大会運営サービス」には「運営サービスコード」が必要で課金単位・単価と関係しますが、「大会アイテム」は「アイテムコード」を持ち費用情報は不要という違いがあります。中間エンティティとして分けることで将来の拡張にも強くなります。
Q: 「会員ポイント」と「参加申込み」の関係を図に入れると冗長では？
A: ポイントの使用・付与は申込み単位で起こるため、【問題文】「ポイントの使用は、有効期限の近いものから行う。」など後続要件を考慮すると、申込みからポイント明細をトレースできる構造が不可欠です。
A: ポイントの使用・付与は申込み単位で起こるため、【問題文】「ポイントの使用は、有効期限の近いものから行う。」など後続要件を考慮すると、申込みからポイント明細をトレースできる構造が不可欠です。
関連キーワード: 正規化, サブタイプ化, 一対多リレーション, 中間エンティティ, 外部キー
設問2：
問題文を見る表1は、太枠で示した部分が未完成である。 太枠外の例に倣って表を完成させよ。
模範解答
解説
解答の論理構成
- 先に与えられていた先着順4列をベースに、同じ行見出しを流用して “抽選” 列を設計します。
- 【問題文】“募集期間が終わり、参加申込数が定員以下だったら ‘参加者確定’”
→ 募集期間＝「後」、参加申込数＝「以下」、抽選年月日は評価不要（－）で ‘参加者確定’ の列を1つ置く（列7）。 - 同じく “募集期間が終わり、参加申込数が定員を超えていれば‘抽選中’”
→ 募集期間＝「後」、参加申込数＝「超過」、抽選年月日＝「前」で ‘抽選中’ の列を1つ置く（列8）。 - 抽選当日には “抽選実施した上で 参加者確定” が必要
→ 募集期間は問わず既に「後」、参加申込数＝「超過」、抽選年月日＝「当日」で ‘抽選実施’＋‘参加者確定’ （列9）。 - 抽選翌日は “抽選結果登録済み” の扱い
→ 募集期間は「後」、参加申込数＝「超過」、抽選年月日＝「後」で ‘参加者確定’ のみ（列10）。 - 募集期間中にまだ定員を超えていないケース
→ 募集期間＝「中」、参加申込数＝「－」、抽選年月日は評価不要で ‘募集中’（列6）。 - 募集開始前で抽選の列（列5）は、募集期間＝「前」、行動は無し（全て “―”）。
- 列間重複が無いこと、行アクションがブランクにならないことを確認して完成です。
誤りやすいポイント
- 「定員以下」と「定員到達」を混同し、抽選列に “到達→抽選中” を置いてしまう。
- “抽選年月日よりも前” と “募集期間よりも後” を同時に成り立たせる日付が存在することを見落とす。
- 当日列で ‘抽選実施’ と ‘参加者確定’ の両方にXを入れ忘れる。
- 列5（募集前）を作らず、結果として “募集中” 列が重複する。
FAQ
Q: 募集期間中に定員超過した抽選枠は“抽選中”にする必要がありますか？
A: いいえ。【問題文】には “募集期間が終わり、参加申込数が定員を超えていれば‘抽選中’にし” とあります。募集期間中は状態は ‘募集中’ のままです。
A: いいえ。【問題文】には “募集期間が終わり、参加申込数が定員を超えていれば‘抽選中’にし” とあります。募集期間中は状態は ‘募集中’ のままです。
Q: “抽選実施” と “参加者確定” のアクションは同じ列に併記してよいのですか？
A: はい。決定表では同じ条件列に複数アクションを並記し、システムが同時に実行することを示せます。抽選当日は抽選を行い、その結果で確定させるため両方のXが必要です。
A: はい。決定表では同じ条件列に複数アクションを並記し、システムが同時に実行することを示せます。抽選当日は抽選を行い、その結果で確定させるため両方のXが必要です。
Q: “定員以下” と “定員未満” の差はなぜ重要なのですか？
A: “以下” は “＝定員” を含み、“未満” は含みません。抽選枠では “≤定員なら確定” というビジネスルールなので “定員以下” を使い、“定員到達” が確定トリガとなる先着順とは区別しています。
A: “以下” は “＝定員” を含み、“未満” は含みません。抽選枠では “≤定員なら確定” というビジネスルールなので “定員以下” を使い、“定員到達” が確定トリガとなる先着順とは区別しています。
関連キーワード: 決定表、状態遷移、ビジネスルール、トリガ、データモデリング
設問3：〔新たな要件の追加〕 について(1)、(2)に答えよ。
問題文を見る（1）多段階抽選方式に対応できるよう、図2の関係スキーマに次の変更を行う。
① ある関係に一つの属性を追加する。 属性を追加する関係名及び追加する属性名を答えよ。
② ある関係から一つの属性を削除する。 属性を削除する関係名及び削除する属性名を答えよ。
③ 新たに一つの関係を追加する。 追加する関係の関係スキーマを答えよ。
模範解答
①：関係名：抽選エントリ枠
属性名：後続エントリ枠番号
②：関係名：参加申込み
属性名：抽選結果
③：抽選結果：(大会番号、会員番号、エントリ枠番号、抽選結果)
解説
解答の論理構成
- 「多段階抽選の対象のエントリ枠には、後続のエントリ枠を一つ設定する。」
→ エントリ枠同士の自己参照が必要。既に多段階抽選対象は「抽選エントリ枠」に限定されているので、この関係に 後続エントリ枠番号 を追加する。 - 「後続するエントリ枠の抽選対象に加える。」
→ 後続枠を表す外部キーはNULL許容（後続がない場合）で良い。 - 「エントリ枠の抽選ごとに抽選結果を登録する。」
→ 会員は複数枠に跨って抽選される可能性があるため、「参加申込み」に単一の 抽選結果 を置くと1対多を抱えて第1正規形に違反。抽選結果を独立させ (大会番号、会員番号、エントリ枠番号) を主キーとする新関係 抽選結果 を導入する。 - 既存「参加申込み」から 抽選結果 を削除し、第3ステップで正規化完了。
誤りやすいポイント
- 参加申込み1行で複数回抽選を管理しようとし、第1正規形の重複列問題に気付かない。
- 後続エントリ枠番号を「エントリ枠」側に付けると、通常枠でも列が存在しNULL大量発生。要件は「抽選エントリ枠」に限定されている点を読み落とす。
- 新関係の主キーに エントリ枠番号 を含め忘れ、同会員が複数枠で重複登録できなくなる。
FAQ
Q: 後続エントリ枠番号は外部キー制約を張るべきですか?
A: はい。参照先は「抽選エントリ枠(大会番号、エントリ枠番号)」です。同一大会内で自己参照する形になります。
A: はい。参照先は「抽選エントリ枠(大会番号、エントリ枠番号)」です。同一大会内で自己参照する形になります。
Q: 「抽選結果」関係に入れる非キー属性は抽選結果だけで良いのですか?
A: 問題文要求は「抽選結果を登録する」のみなので現段階では1属性で十分です。抽選日時など追加要件が出れば拡張します。
A: 問題文要求は「抽選結果を登録する」のみなので現段階では1属性で十分です。抽選日時など追加要件が出れば拡張します。
Q: 後続エントリ枠が無い場合はどう表現しますか?
A: 後続エントリ枠番号 をNULLにします。これにより最終段階の枠を表現できます。
A: 後続エントリ枠番号 をNULLにします。これにより最終段階の枠を表現できます。
関連キーワード: 第1正規形、自己参照外部キー、多段階抽選、主キー設計、正規化
設問3：〔新たな要件の追加〕 について(1)、(2)に答えよ。
問題文を見る（2）ポイント有効期限に対応できるよう、関係 “会員ポイント” を変更する。 変更後の関係の属性名を全て答えよ。
模範解答
会員番号、ポイント付与年月日、付与ポイント、使用済ポイント
解説
解答の論理構成
- 現行関係は「会員ポイント（会員番号、ポイント残高）」のみで、“付与日” 情報がありません。
- 要件追加:
・【問題文】「ポイントの有効期限は、付与された日から1年」
・【問題文】「ポイントの使用は、有効期限の近いものから行う」
有効期限判定と先入先出の使用順序を実装するには “付与日” が必要です。 - 付与日ごとに行を持たせるため、“ポイント付与年月日” を主キーに追加し、複合主キー 会員番号、ポイント付与年月日 を設定します。
- 残高を逐次計算できるよう、付与時点の数量と使用済み数量を分ける。
・付与ポイント：その行で付与された総ポイント
・使用済ポイント：すでに充当済みのポイント - 以上より属性は 「会員番号、ポイント付与年月日、付与ポイント、使用済ポイント」 となります。
誤りやすいポイント
- まとめ残高を維持して “ポイント残高” を残すと、有効期限切れポイントの判定ができません。
- “使用済ポイント” を置かず “残ポイント” を直接持つと、履歴を追跡しづらくなり整合性検査も複雑化します。
- 主キーを “会員番号” のみとすると、同日に複数回付与した場合や履歴管理ができなくなります。
FAQ
Q: 既存の “ポイント残高” は完全に不要ですか？
A: はい。付与・使用履歴から残ポイントを算出できるため、冗長な集計値は保持しません。
A: はい。付与・使用履歴から残ポイントを算出できるため、冗長な集計値は保持しません。
Q: 付与日が同じ日に複数レコード発生することはありますか？
A: 可能性がある場合は “連番” などを追加主キーにしてもよいですが、多くのシステムでは一日のバッチでまとめ付与にしておき “会員番号”＋“ポイント付与年月日” で一意性を確保します。
A: 可能性がある場合は “連番” などを追加主キーにしてもよいですが、多くのシステムでは一日のバッチでまとめ付与にしておき “会員番号”＋“ポイント付与年月日” で一意性を確保します。
Q: 有効期限を直接属性に持たせる案はどうですか？
A: “付与日＋1年” で計算できるため冗長です。計算式で求められる値は正規化の観点から保持しません。
A: “付与日＋1年” で計算できるため冗長です。計算式で求められる値は正規化の観点から保持しません。
関連キーワード: ポイント管理、複合主キー、正規化、有効期限、履歴テーブル
