令和6年度 秋期 応用情報技術者試験 午後問題 問6 E-R図・再帰SQL・インデックス設計
テクノロジデータベース
この問題は2024(R6)秋 応用情報技術者 午後に出題されたものです。出題時点の法令・制度に基づく内容のため、現行の内容と一致しない場合があります。
本ページの問題文・選択肢は、原本の体裁を Web 表示用に正規化しています（改行・記号・数式・図表参照の調整）。設問の趣旨および正解に影響する変更は加えていません。
学習ガイド
トレーディングカードの個人間売買サイトを題材に、E-R図の完成、再帰的なSQL問合せ、インデックス設計という3つのデータベース技術を横断する問題です。特にWITH句による再帰問合せは書き慣れていないと空欄を埋めにくく、本試験でも正答率が低かった論点です。この記事ではカテゴリ階層がSQLでどう展開されるかを具体例で追い、インデックスの設問では「絞り込みに使われる列はどれか」という判断基準を整理します。
この記事で押さえる論点
- 概念データモデルの欠落した関連を業務要件から補完する
- 再帰的問合せ(WITH RECURSIVE)の構文と動きを説明できる
- B-treeインデックスが効く列・効かない列を根拠つきで選別する
出題情報
- 出題
- 2024(R6)秋 応用情報技術者 午後 問6
- 配点
- 20点満点
- 模範解答
- 公表(設問ごとに掲載)
出題趣旨・採点講評(IPA 公表)
近年，個人間での商取引のニーズが増加しており，オンライン上でのフリーマーケットの場を提供するプラットフォームが多数生み出されている。
本問では，トレーディングカードの個人間売買サイトの構築を題材として，E-R図，SQL文による再帰的な問合せの記述，及びインデックスの原理に関する理解を問う。
問6では，トレーディングカードの個人間売買サイトの構築を題材に，概念データモデルの設計，SQL文による再帰的な問合せ，性能改善に向けたインデックス設計について出題した。全体として正答率は低かった。
問題本文
問6 トレーディングカードの個人間売買サイトの構築に関する次の記述を読んで，設問に答えよ。
S社は，トレーディングカード販売業のチェーンを営む中堅企業である。トレーディングカードを個人から買い取り，販売する事業を営んでいる。トレーディングカードの個人間の売買が盛んな市場環境を受け，個人間の売買を安心かつ手軽に行える取引プラットフォームをサービスとして提供して，安定的な手数料収入を得る新規事業を立ち上げることにした。S社の情報システム部は新規事業の要となる取引プラットフォームのシステム（以下，本システムという）を新規で構築することになり，Tさんがデータベースの設計及び開発を担当することになった。
〔新規事業の業務要件の確認〕
Tさんは，まず，新規事業において実現する業務要件を確認した。新規事業の業務要件（抜粋）を表1に示す。
図の説明テキスト
表1 新規事業の業務要件（抜粋）
項番1から7までの業務要件が記載されている。
- 1: 本システムの利用者は個人である。利用者は販売したいトレーディングカードを商品として出品できる。出品した利用者を出品者と呼ぶ。出品する際にはカテゴリ，商品名，商品説明，出品価格，商品状態を登録する。カテゴリはトレーディングカードのブランドやシリーズによって階層化されている。
- 2: 利用者は全ての出品に対してカテゴリ，価格帯（下限価格と上限価格），商品状態，出品状況，キーワードを指定して検索できる。カテゴリを指定する場合，指定されたカテゴリ及びその下位にある全てのカテゴリの出品が検索の対象となる。キーワードは，商品名及び商品説明を部分一致で検索したい場合に指定する。検索した結果を表示する際に並び順を変更できる。
- 3: 利用者は出品された商品に対して取引を希望する価格（以下，オファー価格という）を出品者に打診できる。この行為をオファーと呼ぶ。オファー価格での取引に出品者が合意した場合，その価格はオファーした利用者と出品者との間での当該商品の出品に対してだけ有効となる。
- 4: 利用者が商品を購入した場合，購入した利用者を購入者と呼ぶ。出品者は配送方法に基づいて送料を算出し，購入者に送料を通知する。購入者は出品価格（オファーが合意済みの状態の場合はオファー価格）と送料の合計金額を支払期日までにS社に対して支払う。一つの取引に関する合計金額を分割して支払うことや，複数の取引に関する合計金額をまとめて支払うことはできない。
- 5: 出品者は購入者の支払が完了したことをS社からの通知で確認した上で，購入者の住所に商品を発送する。一つの取引に関する商品を分割して発送することや，複数の取引に関する商品をまとめて発送することはできない。S社は発送した商品の追跡番号を管理し，配送業者のWebサイトと連携することで，出品者や購入者が商品の配送状況を確認できるようにする。
- 6: 購入者は商品を受領後，商品に問題がないことを確認した上で，受取連絡と出品者の評価を行う。購入者からの評価を受けて，出品者も購入者の評価を行う。
- 7: 購入者と出品者の双方の評価が完了した後に，S社は購入者から入金された代金から手数料を差し引いた金額を出品者に支払う。
図の説明テキスト
表1 新規事業の業務要件（抜粋）（続き）
- 8: 利用者はお気に入りの利用者をフォローできる。フォロー先の利用者が新たな商品を出品した場合，フォロー元の利用者は通知を受け取ることができる。
〔概念データモデルの設計〕
Tさんは，表1の業務要件に基づいて，E-R図を用いて本システムの概念データモデルを設計した。本システムの概念データモデル（抜粋）を図1に示す。なお，カテゴリの階層構造は，自己参照の関連を用いて表現する。
図の説明テキスト
図1 本システムの概念データモデル（抜粋）のE-R図。
カテゴリ、支払、配送業者、配送方法、出品、取引、発送、オファー、利用者、フォローの各エンティティとその属性、関連が描かれている。
図中には以下の空欄がある：
- カテゴリエンティティからカテゴリ自身への関連線に a
- 取引エンティティから発送エンティティへの関連線に b
- 発送エンティティの属性に c
- 利用者エンティティからフォローエンティティへの2本目の関連線に d
凡例により、実線の矢印なしは1対1、片側矢印は1対多、両側矢印は多対多を表すことが示されている。主キーは実線下線、外部キーは破線下線で示されている。
本システムのデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
〔SQLの作成〕
Tさんは，表1の項番2の業務要件を実現するための検索のSQL文を作成した。作成したSQL文を図2に示す。なお，“:カテゴリID”，“:下限価格”，“:上限価格”，“:商品状態”，“:出品状況”，“:キーワード” は，該当の値を格納する埋込み変数である。また，最上位であるカテゴリの上位カテゴリIDにはNULLが設定されている。
図の説明テキスト
図2 作成したSQL文。再帰的クエリと思われるSQL文が記述されており、以下の空欄が含まれる：
- e 指定カテゴリ(カテゴリID, カテゴリ名, 上位カテゴリID) AS (
- SELECT ... WHERE A.カテゴリID = :カテゴリID
f
SELECT ... - INNER JOIN 指定カテゴリ ON g
- AND (出品.商品名 h OR 出品.商品説明 h )
〔性能の検証と改善〕
Tさんがテストデータを用いて図2のSQL文の実行性能を検証したところ，実行を開始してから検索結果が得られるまでの処理時間が長く，実用的ではないことが判明した。
本システムでは出品される商品の数が膨大であり，利用者が図2のSQL文を頻繁に実行することが予想される。そこで，Tさんはキーワードでの検索が必要な商品名及び商品説明の列には全文検索エンジンを用いるとともに，その他の列に対しては適切なインデックスを設定し，性能上の懸念を解消することを検討した。
インデックスの方式には，B-treeインデックスを採用することにした。Tさんは，各表の表定義を確認し，インデックスを設定すべき列を検討した。出品表の表定義を表2に，カテゴリ表の表定義を表3に示す。
表2及び表3のデータ型欄は，データ型，長さ，精度，位取りを示す。PK欄は主キー制約，UK欄はUNIQUE制約，非NULL欄は非NULL制約の指定をするかどうかを示す。指定する場合にはYを，指定しない場合にはNが記入されている。ここで，主キーに対してはUNIQUE制約は指定せず，非NULL制約は指定するものとする。カーディナリティ欄は列に多数の異なる値をもつ場合には高を，少数の異なる値をもつ場合には低を記入する。そして，高と低の中間の数の異なる値をもつ場合には中を記入する。データ分布欄は列に含まれる値の確率分布の仮定を示す。
Tさんは，①B-treeインデックスの特性を踏まえて，特定の値を指定したときに行数を表全体の5%以下に絞り込める列だけにインデックスを設定することにした。
図の説明テキスト
表2 出品表の表定義。列名、データ型、PK、UK、非NULL、カーディナリティ、データ分布、定義内容が記載されている。
- 出品ID (INT, PK:Y, カーディナリティ:高, 一様分布)
- 出品者ID (INT, PK:N, カーディナリティ:高, 一様分布)
- カテゴリID (INT, PK:N, カーディナリティ:中, 一様分布)
- 商品名 (VARCHAR(255), PK:N, カーディナリティ:高, 一様分布)
- 商品説明 (CLOB, PK:N, カーディナリティ:高, 一様分布)
- 出品価格 (DECIMAL(10,2), PK:N, カーディナリティ:高, 対数正規分布 確率密度の最大値:0.00003)
- 商品状態 (SMALLINT, PK:N, カーディナリティ:低, 一様分布, 1〜3の整数)
- 出品状況 (SMALLINT, PK:N, カーディナリティ:低, 一様分布, 1〜3の整数)
図の説明テキスト
表3 カテゴリ表の表定義。列名、データ型、PK、UK、非NULL、カーディナリティ、データ分布、定義内容が記載されている。
- カテゴリID (INT, PK:Y, カーディナリティ:高, 一様分布, 1〜10000の整数)
- カテゴリ名 (VARCHAR(100), PK:N, カーディナリティ:高, 一様分布)
- 上位カテゴリID (INT, PK:N, カーディナリティ:中, 一様分布)
Tさんは，必要なインデックスを設定後にテストデータを用いて図2のSQL文の実行性能を検証し，実用的な性能であることを確認した。ただし，表2及び表3のデータ分布は新規事業立上げ前の時点における仮定でしかない。今後実際に運用する際にはデータ分布が仮定とは異なる場合があるので，定期的にインデックスを見直すことを申し送り事項の一つとして，本システムのデータベースの設計及び開発を完了した。
設問と解答・解説
設問1
(1)
空欄 a に入れる適切なエンティティ間の関連を答えよ。
模範解答
↩
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 正しい記号を過不足なく解答している。
- 1点: 解答に関連する記号が含まれるが、指定された凡例や表記に完全には従っていない。
- 0点: 誤った記号を解答している、または無解答。
解説
本問は、E-R図における交差エンティティと多対多の関連の分解に関する理解を問う問題です。
- 交差エンティティを用いて多対多の関連を分解する際、交差エンティティは元のエンティティの主キーを外部キーとして受け継ぎます。
- このため、交差エンティティは元のエンティティに依存する存在となり、図1の凡例に従うと 依存リレーションシップ（↩） で表現されます。
高得点のポイント
- E-R図の凡例に示された記号の意味を正確に理解していること。
- 依存関係の有無を正しく判断し、「↩」を導き出せること。
(2)
空欄 b に入れる適切なエンティティ間の関連を答えよ。
模範解答
―
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 正しい記号を過不足なく解答している。
- 1点: 解答に関連する記号が含まれるが、指定された凡例や表記に完全には従っていない。
- 0点: 誤った記号を解答している、または無解答。
解説
本問は、E-R図におけるエンティティ間のリレーションシップの種別を問う問題です。
- 対象となるエンティティ間の関連において、片方のエンティティの存在が他方に依存しない場合、非依存リレーションシップ となります。
- 図1の凡例に従い、非依存関係を表す記号 「―」 を選択する必要があります。
高得点のポイント
- エンティティ間の依存関係を正しく見極めること。
- 凡例から適切な非依存リレーションシップの記号「―」を正確に解答すること。
(3)
空欄 c に入れる適切な属性名を答えよ。
模範解答
追跡番号
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 正しい属性名を過不足なく解答している。
- 1点: 解答に関連する用語が含まれるが、指定された凡例や表記に完全には従っていない。
- 0点: 誤った属性名を解答している、または無解答。
解説
本問は、配送サービスに関するエンティティの適切な属性名を導き出す問題です。
- 個人間売買サイトにおいて、商品の配送状況を確認するためには 追跡番号 を管理することが一般的です。
- システムの要件や他のエンティティとの関係から、配送を特定・追跡するためのキーとなる属性を特定します。
高得点のポイント
- 問題文の業務要件から、配送管理に不可欠な属性が「追跡番号」であることを読み取ること。
- 用語を正確に抜き出し、過不足なく解答すること。
(4)
空欄 d に入れる適切なエンティティ間の関連を答えよ。
模範解答
→
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 正しい記号を過不足なく解答している。
- 1点: 解答に関連する記号が含まれるが、指定された凡例や表記に完全には従っていない。
- 0点: 誤った記号を解答している、または無解答。
解説
本問は、E-R図における「1対多」の関連の記述に関する問題です。
- エンティティ間のカーディナリティにおいて、片方が「1」で他方が「多」となる関係を表現します。
- 図1の凡例に基づき、1対多の関連を示す記号 「→」 を用いてモデルを完成させます。
高得点のポイント
- エンティティ間のカーディナリティ（1対多など）を正確に分析できること。
- 凡例の指定に完全に倣い、「→」を解答すること。
設問1は，dの正答率が低かった。交差エンティティを用いて多対多の関連を分解して関係データベースで管理できるようにすることは必要な手法であり，同様の構造をもつデータは世の中に多数存在するので，応用できるように理解を深めてほしい。
設問2
(1)
空欄 e に入れる適切な字句又は式を答えよ。
模範解答
WITH RECURSIVE
採点基準(配点 1点)
正確性（内容）(1点)
- 1点: 適切な字句を完全に正しく記述している。
- 0点: 部分的に正しいが完全ではない。
- 0点: 誤った字句を解答している、または無解答。
解説
本問は、SQLにおける 再帰的問合せ を記述するための構文に関する知識を問う問題です。
- 階層構造を持つデータ（例：カテゴリ階層）を再帰的に検索する場合、共通表式（CTE）を用います。
- SQL標準において、再帰的な共通表式を定義するためには
WITH RECURSIVE句を使用します。
高得点のポイント
- 再帰的問合せを実現するSQL構文
WITH RECURSIVEを正確に記述できること。 - 構文のスペルミスなく解答すること。
(2)
空欄 f に入れる適切な字句又は式を答えよ。
模範解答
UNION ALL
採点基準(配点 1点)
正確性（内容）(1点)
- 1点: 適切な字句を完全に正しく記述している。
- 0点: 部分的に正しいが完全ではない。
- 0点: 誤った字句を解答している、または無解答。
解説
本問は、再帰的問合せの共通表式内において、非再帰項と再帰項を結合するための演算子を問う問題です。
- 再帰的問合せでは、起点となるデータ（非再帰項）と、そこから再帰的に取得するデータ（再帰項）を結合して一つの結果セットにします。
- 重複排除を行わず、全ての行を単純に結合する必要があるため、
UNION ALLを使用するのが適切です。
高得点のポイント
- 再帰的問合せにおける
UNION ALLの役割と動作を正しく理解していること。 - 単なる
UNIONではなく、要件に適したUNION ALLを指定できること。
(3)
空欄 g に入れる適切な字句又は式を答えよ。
模範解答
出品.カテゴリID = 指定カテゴリ.カテゴリID
採点基準(配点 1点)
正確性（内容）(1点)
- 1点: 適切な式を完全に正しく記述している。
- 0点: 部分的に正しいが完全ではない。
- 0点: 誤った式を解答している、または無解答。
解説
本問は、再帰的に取得したカテゴリ情報を用いて、対象となる出品データを結合するための条件式を問う問題です。
- 抽出したいのは、指定されたカテゴリおよびその下位カテゴリに属する商品の出品情報です。
- 再帰的に作成した「指定カテゴリ」表の
カテゴリIDと、「出品」表のカテゴリIDを一致させる必要があるため、結合条件は出品.カテゴリID = 指定カテゴリ.カテゴリIDとなります。
高得点のポイント
- 表の結合において、正しい表のエイリアス（または表名）と列名を指定できること。
- 結合の意図を理解し、正確な条件式を記述できること。
(4)
空欄 h に入れる適切な字句又は式を答えよ。
模範解答
LIKE '%' || :キーワード || '%'
採点基準(配点 1点)
正確性（内容）(1点)
- 1点: 適切な字句や式を完全に正しく記述している。
- 0点: 部分的に正しいが完全ではない。
- 0点: 誤った字句や式を解答している、または無解答。
解説
本問は、SQLにおける LIKE述語 を用いた部分一致検索（中間一致検索）の記述方法に関する問題です。
- 埋込み変数
:キーワードを用いて中間一致検索を行う場合、検索文字列の前後をワイルドカード%で囲む必要があります。 - 文字列の連結演算子
||を用いて、'%' || :キーワード || '%'のように文字値式を構築します。
高得点のポイント
- LIKE述語のオペランドにおいて、文字値式の連結が必要である点（ワイルドカードや埋込み変数の指定）を理解していること。
- SQLの文字列連結構文を正確に記述できること。
設問2は，hの正答率が低かった。LIKE述語のオペランドで文字パターンを指定する場合には，文字値式を記述する必要がある。また，エスケープ文字と埋込み変数を指定する場合には，文字値式を連結する必要がある。
設問3
〔性能の検証と改善〕について答えよ。
(1)
本文中の下線①について，B-treeインデックスの特性として適切なものを解答群の中から三つ選び，記号で答えよ。
模範解答
選択肢ア: インデックスを設定した各列に対する条件をAND演算子で組み合わせた検索は高速化できるが，NOT演算子を用いた条件による検索は高速化できない。
選択肢ウ: インデックスを設定した列に含まれる値の分布に偏りがない場合，検索性能が安定する。
選択肢オ: 行数nの表において，特定の行を検索するときの計算量はO(log n)である。
配点 2点
解説
本問は、関係データベースにおける代表的なインデックスである B-treeインデックス の特性に関する深い理解を問う問題です。
適切ではない列にインデックスを設定すると性能低下を招くおそれがあるため、特性を踏まえた設計が求められます。
- ア: 正しい。AND条件の検索には有効ですが、NOT演算子による否定条件はインデックスを有効に活用できず、全件走査になることが一般的です。
- ウ: 正しい。列に含まれる値の分布に偏りがない（カーディナリティが高い）場合、絞り込み効率が良く、検索性能が安定します。
- オ: 正しい。B-treeは平衡木構造を持つため、行数 に対して特定の行を検索する際の計算量は となります。
各選択肢の解説
- イ: 誤り。インデックスが設定された列に演算や関数を適用すると、通常のB-treeインデックスは使用されなくなります。
- エ: 誤り。カーディナリティが低い（値の種類が少ない）列に設定しても、十分に件数を絞り込めないため高速化の恩恵は薄いです。
- カ: 誤り。行の挿入時にもB-treeの木構造の更新（分割など）が行われるため、計算量は ではなく となります。
- キ: 誤り。B-treeインデックスは等号（完全一致）だけでなく、不等号や
BETWEENなどの 範囲検索 にも有効です。
(2)
出品表について，表2を基に，B-treeインデックスを設定することで図2のSQL文の実行性能の高速化に寄与する列名を全て答えよ。なお，本システムで使用する関係データベースでは，主キーに対して自動的にインデックスが設定される。
模範解答
カテゴリID，出品価格
採点基準(配点 3点)
正確性（内容）(3点)
- 3点: 要求される全ての列名を過不足なく正確に解答している。
- 1点: 一部の列名のみを解答している、または余分な列名が含まれている。
- 0点: 全く見当違いの列名を解答している、または無解答。
解説
本問は、SQL文の実行性能を向上させるために、B-treeインデックス を設定すべき「出品表」の列を特定する問題です。
- 図2のSQL文における「出品表」の検索条件（WHERE句および結合条件）を確認します。
カテゴリIDは、再帰問合せで取得した指定カテゴリ群との結合（出品.カテゴリID = 指定カテゴリ.カテゴリID）に使用されるため、インデックスが極めて有効です。出品価格は、BETWEEN :下限価格 AND :上限価格による範囲検索に使用されており、B-treeインデックスの特徴（範囲検索の高速化）を活かすことができます。- なお、
商品名はLIKE '%' || :キーワード || '%'による中間一致検索であり、B-treeインデックスによる高速化は見込めません。
高得点のポイント
- 実行されるSQL文のWHERE句や結合条件を正確に分析できること。
- B-treeインデックスが有効に機能する条件（等価結合や範囲検索）と機能しない条件（中間一致）を区別し、「カテゴリID，出品価格」を過不足なく解答できること。
(3)
カテゴリ表について，表3を基に，B-treeインデックスを設定することで図2のSQL文の実行性能の高速化に寄与する列名を全て答えよ。なお，本システムで使用する関係データベースでは，主キーに対して自動的にインデックスが設定される。
模範解答
上位カテゴリID
採点基準(配点 3点)
正確性（内容）(3点)
- 3点: 要求される列名を過不足なく正確に解答している。
- 1点: 解答に関連する列名が含まれるが、余分な列名が含まれている。
- 0点: 全く見当違いの列名を解答している、または無解答。
解説
本問は、再帰的問合せの処理性能を向上させるために、B-treeインデックス を設定すべき「カテゴリ表」の列を特定する問題です。
- 図2のSQLの再帰項（
UNION ALLの後）において、カテゴリ表を自己結合して下位階層を探索しています。 - 結合条件は
カテゴリ.上位カテゴリID = 指定カテゴリ.カテゴリIDとなっています。 - したがって、結合のたびにカテゴリ表の
上位カテゴリIDを条件として検索が行われるため、この列にインデックスを設定することで階層をたどる処理が大幅に高速化されます。
高得点のポイント
- 再帰的問合せの仕組みと、その中で実行される自己結合の条件を正しく把握できること。
- 結合の駆動表と内部表の関係から、探索対象となる列が「上位カテゴリID」であることを特定し、正確に解答できること。
設問3(2)は，出品表の正答率が低かった。適切ではない列にインデックスを設定すると性能低下を招くおそれがあるので，データ，実行するSQL文，インデックスの種類の特性を踏まえて，必要十分な列だけにインデックスを設定する必要がある。インデックスはデータベースを利用する際に必要な技術であり，B-treeインデックスは関係データベースにおける代表的なインデックスの種類の一つなので，これらの理解を深めてほしい。