問題プレビューID: eb35d062応用情報技術者試験 2024年(令和6年度)秋期 午後

問 6
応用情報技術者試験 2024年(令和6年度)秋期 午後

トレーディングカードの個人間売買サイトの構築に関する次の記述を読んで,設問に答えよ。

S社は,トレーディングカード販売業のチェーンを営む中堅企業である。トレーディングカードを個人から買い取り,販売する事業を営んでいる。トレーディングカードの個人間の売買が盛んな市場環境を受け,個人間の売買を安心かつ手軽に行える取引プラットフォームをサービスとして提供して,安定的な手数料収入を得る新規事業を立ち上げることにした。S社の情報システム部は新規事業の要となる取引プラットフォームのシステム(以下,本システムという)を新規で構築することになり,T さんがデータベースの設計及び開発を担当することになった。

〔新規事業の業務要件の確認〕

T さんは,まず,新規事業において実現する業務要件を確認した。新規事業の業務要件(抜粋)を表1に示す。

表1 新規事業の業務要件(抜粋)

項番業務要件
1本システムの利用者は個人である。利用者は販売したいトレーディングカードを商品として出品できる。出品した利用者を出品者と呼ぶ。出品する際にはカテゴリ,商品名,商品説明,出品価格,商品状態を登録する。カテゴリはトレーディングカードのブランドやシリーズによって階層化されている。
2利用者は全ての出品に対してカテゴリ,価格帯(下限価格と上限価格),商品状態,出品状況,キーワードを指定して検索できる。カテゴリを指定した場合,指定されたカテゴリ及びその下位にある全てのカテゴリの出品が検索の対象となる。キーワードは,商品名及び商品説明を部分一致で検索したい場合に指定する。検索した結果を表示する際に並び順を変更できる。
3利用者は出品された商品に対して取引を希望する価格(以下,オファー価格という)を出品者に打診できる。この行為をオファーと呼ぶ。オファー価格での取引に出品者が合意した場合,その価格はオファーした利用者と出品者との間での当該商品の出品に対してだけ有効となる。
4利用者が商品を購入した場合,購入した利用者を購入者と呼ぶ。出品者は配送方法に基づいて送料を算出し,購入者に送料を通知する。購入者は出品価格(オファーが合意済みの状態の場合はオファー価格)と送料の合計金額を支払期日までに S 社に対して支払う。一つの取引に関する合計金額を分割して支払うことや,複数の取引に関する合計金額をまとめて支払うことはできない。
5出品者は購入者の支払が完了したことを S 社からの通知で確認した上で,購入者の住所に商品を発送する。一つの取引に関する商品を分割して発送することや,複数の取引に関する商品をまとめて発送することはできない。S社は発送した商品の追跡番号を管理し,配送業者の Web サイトと連携することで,出品者や購入者が商品の配送状況を確認できるようにする。
6購入者は商品を受領後,商品に問題がないことを確認した上で,受取連絡と出品者の評価を行う。購入者からの評価を受けて,出品者も購入者の評価を行う。
7購入者と出品者の双方の評価が完了した後に,S社は購入者から入金された代金から手数料を差し引いた金額を出品者に支払う。

表1 新規事業の業務要件(抜粋)(続き)

項番業務要件
8利用者はお気に入りの利用者をフォローできる。フォロー先の利用者が新たな商品を出品した場合,フォロー元の利用者は通知を受け取ることができる。

〔概念データモデルの設計〕

T さんは,表1の業務要件に基づいて,E-R 図を用いて本システムの概念データモデルを設計した。本システムの概念データモデル(抜粋)を図1に示す。なお,カテゴリの階層構造は,自己参照の関連を用いて表現する。

図1 本システムの概念データモデル(抜粋)

太字は主キー(原図の実線の下線)を表す。外部キー(原図の破線の下線)は省略している。

  • カテゴリ(カテゴリID,カテゴリ名,上位カテゴリID)
  • 支払(支払ID,取引ID,合計金額,支払期日,支払状態)
  • 配送業者(配送業者ID,配送業者名,WebサイトURL)
  • 配送方法(配送方法ID,配送業者ID,配送方法名)
  • 出品(出品ID,出品者ID,カテゴリID,商品名,商品説明,出品価格,商品状態,出品状況)
  • 取引(取引ID,出品ID,購入者ID,取引状態,購入者評価,出品者評価)
  • 発送(発送ID,取引ID,配送方法ID,送料,[c])
  • 利用者(利用者ID,表示名,氏名,メールアドレス,住所)
  • オファー(オファーID,出品ID,購入希望者ID,オファー価格,オファー状態)
  • フォロー(フォロー元利用者ID,フォロー先利用者ID)

エンティティ間の関連は次のとおりである。

  • カテゴリ → カテゴリ(1対多,自己参照)[a]
  • カテゴリ → 出品(1対多)
  • 出品 — 取引(1対1)
  • 出品 → オファー(1対多)
  • 取引 — 支払(1対1)
  • 取引 — 発送(1対1)[b]
  • 配送業者 → 配送方法(1対多)
  • 配送方法 → 発送(1対多)
  • 利用者 → 出品(1対多)
  • 利用者 → 取引(1対多)
  • 利用者 → オファー(1対多)
  • 利用者 → フォロー(1対多)[d](フォロー元・フォロー先としての二つの関連)

凡例 —:1対1 →:1対多 ←→:多対多

注記 属性名の実線の下線は主キー,破線の下線は外部キーを示す。主キーの実線が付いている属性名には,外部キーの破線を付けない。

本システムのデータベースでは,E-R 図のエンティティ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理する。

〔SQL の作成〕

T さんは,表1の項番2の業務要件を実現するための検索の SQL 文を作成した。作成した SQL 文を図2に示す。なお,":カテゴリID",":下限価格",":上限価格",":商品状態",":出品状況",":キーワード" は,該当の値を格納する埋込み変数である。また,最上位であるカテゴリの上位カテゴリID には NULL が設定されている。

図2 作成した SQL 文

[e]指定カテゴリ(カテゴリID, カテゴリ名, 上位カテゴリID) AS (
SELECT A.カテゴリID, A.カテゴリ名, A.上位カテゴリID
  FROM カテゴリ A WHERE A.カテゴリID = :カテゴリID
[f]
SELECT B.カテゴリID, B.カテゴリ名, B.上位カテゴリID
  FROM カテゴリ B, 指定カテゴリ C WHERE B.上位カテゴリID = C.カテゴリID
)
SELECT * FROM 出品
  INNER JOIN 指定カテゴリ
  ON [g]
  WHERE 出品.出品価格 BETWEEN :下限価格 AND :上限価格
    AND 出品.商品状態 = :商品状態
    AND 出品.出品状況 = :出品状況
    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 出品表の表定義

項番列名データ型PKUK非 NULLカーディナリティデータ分布定義内容
1出品IDINTYNY高一様分布1~2000000000 の整数
2出品者IDINTNNY高一様分布1~2000000000 の整数
3カテゴリIDINTNNY中一様分布外部キー(カテゴリ)
4商品名VARCHAR(255)NNY高一様分布最大 255 字の文字列
5商品説明CLOBNNN高一様分布(省略)
6出品価格DECIMAL(10,2)NNY高対数正規分布(確率密度の最大値:0.00003)0.00~99999999.99 の固定小数点数
7商品状態SMALLINTNNY低一様分布1~3 の整数(1 は未使用,2 は美品,3 は傷ありを示す)
8出品状況SMALLINTNNY低一様分布1~3 の整数(1 は下書き,2 は出品中,3 は購入済みを示す)

表3 カテゴリ表の表定義

項番列名データ型PKUK非 NULLカーディナリティデータ分布定義内容
1カテゴリIDINTYNY高一様分布1~10000 の整数
2カテゴリ名VARCHAR(100)NNY高一様分布最大 100 字の文字列
3上位カテゴリIDINTNNN中一様分布外部キー(カテゴリ)

T さんは,必要なインデックスを設定後にテストデータを用いて図2の SQL 文の実行性能を検証し,実用的な性能であることを確認した。ただし,表2及び表3のデータ分布は新規事業立上げ前の時点における仮定でしかない。今後実際に運用する際にはデータ分布が仮定とは異なる場合があるので,定期的にインデックスを見直すことを申し送り事項の一つとして,本システムのデータベースの設計及び開発を完了した。

設問1

図1中の[a]~[d]に入れる適切なエンティティ間の関連及び属性名を答え,概念データモデルを完成させよ。なお,エンティティ間の関連及び属性名の表記は,図1の凡例に倣うこと。

設問2

図2中の[e]~[h]に入れる適切な字句又は式を答えよ。

設問3

〔性能の検証と改善〕について答えよ。

(1) 本文中の下線①について,B-tree インデックスの特性として適切なものを解答群の中から三つ選び,記号で答えよ。

解答群

ア インデックスを設定した各列に対する条件を AND 演算子で組み合わせた検索は高速化できるが,NOT 演算子を用いた条件による検索は高速化できない。

イ インデックスを設定した列に対して演算や型変換を行った上で検索条件に指定した場合,検索を高速化できる。

ウ インデックスを設定した列に含まれる値の分布に偏りがない場合,検索性能が安定する。

エ カーディナリティが低い列にインデックスを設定すると,検索を高速化できる。

オ 行数 n の表において,特定の行を検索するときの計算量は O(log⁡n)O(\log n) である。

カ 行数 n の表において,特定の行を挿入するときの計算量は O(n)O(n) である。

キ 等号演算子を用いた条件による検索は高速化できるが,値の範囲を指定した条件による検索は高速化できない。

(2) 出品表及びカテゴリ表のそれぞれについて,表2及び表3を基に,B-tree インデックスを設定することで図2の SQL 文の実行性能の高速化に寄与する列名を全て答えよ。なお,本システムで使用する関係データベースでは,主キーに対して自動的にインデックスが設定される。

模範解答

設問1 a →(自己参照),b —,c 追跡番号,d →

設問2 e WITH RECURSIVE,f UNION ALL,g 出品.カテゴリID = 指定カテゴリ.カテゴリID,h LIKE '%' || :キーワード || '%'

設問3 (1) ア,ウ,オ (2) 出品表 カテゴリID,出品価格 カテゴリ表 上位カテゴリID

解説

問6では,トレーディングカードの個人間売買サイトの構築を題材に,E-R 図,SQL 文による再帰的な問合せの記述,及びインデックスの原理に関する理解を問うた。

設問1について,カテゴリの階層構造は自己参照の 1対多の関連(a →)で表現する。取引と発送は 1対1 の関連(b —)である。発送には業務要件の追跡番号の管理のための属性(c 追跡番号)を追加する。利用者とフォローは 1対多の関連(d →)である。

設問2について,下位カテゴリを再帰的に求める問合せなので,e は WITH RECURSIVE,f は UNION ALL である。g は出品と指定カテゴリの結合条件であり,出品.カテゴリID = 指定カテゴリ.カテゴリID である。h は商品名及び商品説明の部分一致条件であり,LIKE '%' || :キーワード || '%' である。

設問3(1)について,B-tree インデックスの特性として適切なのは,ア(AND 条件の高速化),ウ(分布に偏りがない場合の性能の安定),オ(検索の計算量 O(log⁡n)O(\log n))の三つである。(2)について,特定の値の指定で行数を 5%以下に絞り込める列に設定するので,出品表ではカテゴリIDと出品価格,カテゴリ表では上位カテゴリID が対象となる。商品状態・出品状況は値の種類が少なく絞り込めないため対象外であり,商品名・商品説明は全文検索エンジンを用いるため対象外である。

関連問題

本文が同じ・似ている問題を表示しています