AP過去問 令和6年度秋期 午後 問6 データベース
AP 過去問題 午後に戻る。
AP過去問_令和6年度秋期_午後_問5_ネットワークの同じ回の前の問題へ移動。
AP過去問_令和6年度秋期_午後_問7_組込みシステム開発の同じ回の次の問題へ移動。
令和6年度秋期 午後 問6 データベース(AIプロンプト向け)
■トレーディングカードの個人間売買サイトの構築に関する次の記述を読んで設問に答えよ。
S社は、トレーディングカード販売業のチェーンを営む中堅企業である。トレーディングカードを個人から買い取り、販売する事業を営んでいる。トレーディングカードの個人間の売買が盛んな市場環境を受け、個人間の売買を安心かつ手軽に行える取引プラットフォームをサービスとして提供して、安定的な手数料収入を得る新規事業を立ち上げることにした。S社の情報システム部は新規事業の要となる取引プラットフォームのシステム(以下、本システムという)を新規で構築することになり、Tさんがデータベースの設計及び開発を担当することになった。
[新規事業の業務要件の確認]
Tさんは、まず、新規事業において実現する業務要件を確認した。新規事業の業務要件(抜粋)を表1に示す。
表1 新規事業の業務要件(抜粋)ここから
・項番
・業務要件
の順番で表1の終わりまで繰り返します。
・1
・本システムの利用者は個人である。利用者は販売したいトレーディングカードを商品として出品できる。出品した利用者を出品者と呼ぶ。出品する際にはカテゴリ、商品名、商品説明、出品価格、商品状態を登録する。カテゴリはトレーディングカードのブランドやシリーズによって階層化されている。
・2
・利用者は全ての出品に対してカテゴリ、価格帯(下限価格と上限価格)、商品状態、出品状況、キーワードを指定して検索できる。カテゴリを指定する場合、指定されたカテゴリ及びその下位にある全てのカテゴリの出品が検索の対象となる。キーワードは、商品名及び商品説明を部分一致で検索したい場合に指定する。検索した結果を表示する際に並び順を変更できる。
・3
・利用者は出品された商品に対して取引を希望する価格(以下、オファー価格という)を出品者に打診できる。この行為をオファーと呼ぶ。オファー価格での取引に出品者が合意した場合、その価格はオファーした利用者と出品者との間での当該商品の出品に対してだけ有効となる。
・4
・利用者が商品を購入した場合、購入した利用者を購入者と呼ぶ。出品者は配送方法に基づいて送料を算出し、購入者に送料を通知する。購入者は出品価格(オファーが合意済みの状態の場合はオファー価格)と送料の合計金額を支払期日までにS社に対して支払う。一つの取引に関する合計金額を分割して支払うことや、複数の取引に関する合計金額をまとめて支払うことはできない。
・5
・出品者は購入者の支払が完了したことをS社からの通知で確認した上で、購入者の住所に商品を発送する。一つの取引に関する商品を分割して発送することや、複数の取引に関する商品をまとめて発送することはできない。S社は発送した商品の追跡番号を管理し、配送業者のWebサイトと連携することで、出品者や購入者が商品の配送状況を確認できるようにする。
・6
・購入者は商品を受領後、商品に問題がないことを確認した上で、受取連絡と出品者の評価を行う。購入者からの評価を受けて、出品者も購入者の評価を行う。
・7
・購入者と出品者の双方の評価が完了した後に、S社は購入者から入金された代金から手数料を差し引いた金額を出品者に支払う。
・8
・利用者はお気に入りの利用者をフォローできる。フォロー先の利用者が新たな商品を出品した場合、フォロー元の利用者は通知を受け取ることができる。
表1 ここまで
[概念データモデルの設計]
Tさんは、表1の業務要件に基づいて、E-R図を用いて本システムの概念データモデルを設計した。本システムの概念データモデル(抜粋)を図1に示す。なお、カテゴリの階層構造は、自己参照の関連を用いて表現する。
図1 本システムの概念データモデル(抜粋) ここから
凡例ここから
・1対1ならば、－
・1対多ならば、ー＞
・多対1ならば、＜ー
・多対多ならば、＜ー＞
・主キー列名ならば、列名の後ろに(主キー)
・外部キー列名ならば、列名の後ろに(外部キー) 凡例ここまで
表名:カテゴリ
表名:カテゴリに含まれる列名ここから
・カテゴリID(主キー)
・カテゴリ名
・上位カテゴリID
表名:カテゴリに含まれる列名ここまで
表名:カテゴリの列名:カテゴリIDと表名:カテゴリの列名:上位カテゴリの関係は[ａ]
表名:出品
表名:出品に含まれる列名ここから
・出品ID(主キー)
・出品者ID(外部キー)
・カテゴリID(外部キー)
・商品名
・商品説明
・出品価格
・商品状態
・出品状況
表名:出品に含まれる列名ここまで
表名:カテゴリの列名:カテゴリIDと表名:出品の列名:カテゴリIDの関係は[ー＞]
表名:オファー
表名:オファーに含まれる列名ここから
・オファーID(主キー)
・出品ID(外部キー)
・購入希望者ID(外部キー)
・オファー価格
・オファー状態
表名:オファーに含まれる列名ここまで
表名:出品の列名:出品IDと表名:オファーの列名:出品IDの関係は[ー＞]
表名:支払
表名:支払に含まれる列名ここから
・支払ID(主キー)
・取引ID(外部キー)
・合計金額
・支払期日
・支払状態
表名:支払に含まれる列名ここまで
表名:取引
表名:取引に含まれる列名ここから
・取引ID(主キー)
・出品ID(外部キー)
・購入者ID(外部キー)
・取引状態
・購入者評価
・出品者評価
表名:取引に含まれる列名ここまで
表名:支払の列名:取引IDと表名:取引の列名:取引IDの関係は[ー]
表名:出品の列名:出品IDと表名:取引の列名:出品IDの関係は[ー]
表名:利用者
表名:利用者に含まれる列名ここから
・利用者ID(主キー)
・表示名
・氏名
・メールアドレス
・住所
表名:利用者に含まれる列名ここまで
表名:利用者の列名:利用者IDと表名:出品の列名:出品者IDの関係は[ー＞]
表名:利用者の列名:利用者IDと表名:オファーの列名:購入希望者IDの関係は[ー＞]
表名:利用者の列名:利用者IDと表名:取引の列名:購入者IDの関係は[ー＞]
表名:配送業者
表名:配送業者に含まれる列名ここから
・配送業者ID(主キー)
・配送業者名
・WebサイトURL
表名:配送業者に含まれる列名ここまで
表名:配送方法
表名:配送方法に含まれる列名ここから
・配送方法ID(主キー)
・配送業者ID(外部キー)
・配送方法名
表名:配送方法に含まれる列名ここまで
表名:配送業者の列名:配送業者IDと表名:配送方法の列名:配送業者IDの関係は[ー＞]
表名:発送
表名:発送に含まれる列名ここから
・発送ID(主キー)
・取引ID(外部キー)
・配送方法ID(外部キー)
・送料
・[ｃ]
表名:発送に含まれる列名ここまで
表名:配送方法の列名:配送方法IDと表名:発送の列名:発送方法IDの関係は[ー＞]
表名:取引の列名:取引IDと表名:発送の列名:取引IDの関係は[ｂ]
表名:フォロー
表名:フォローに含まれる列名ここから
・フォロー元利用者ID(主キー)
・フォロー先利用者ID(主キー)
表名:フォローに含まれる列名ここまで
表名:利用者の列名:利用者IDと表名:フォローの列名:フォロー元利用者IDの関係は[ー＞]
表名:利用者の列名:利用者IDと表名:フォローの列名:フォロー元利用者IDの関係は[ｄ]
図1 本システムの概念データモデル(抜粋) ここまで
本システムのデータベースでは、E-R図のエンティティ名を表明にし、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。
[SQLの作成]
Tさんは、表1の項番2の業務要件を実現するための検索のSQL文を作成した。作成したSQL文を図2に示す。なお、“:カテゴリID”、“:下限価格”、“:商品状態”、“:出品状況”、“:キーワード” は、該当の値を格納する埋込み変数である。また、最上位であるカテゴリの上位カテゴリIDにはNULLが設定されている。
図2 作成したSQL文 ここから (日本語の列名や表名を使っているSQLです。全角スペースは半角スペースと同じ意味です。)
[ｅ] 指定カテゴリ(カテゴリID, カテゴリ名, 上位カテゴリID) AS (
SELECT A.カテゴリID, A.カテゴリ名, A.上位カテゴリID
FROM カテゴリA WHERE A.カテゴリID = :カテゴリID
[ｆ]
SELECT B.カテゴリID, B.カテゴリ名, B.上位カテゴリID
FROM カテゴリB, 指示カテゴリ C WHERE B.上位カテゴリID = C.カテゴリID
)
SELECT * FROM 出品
INNER JOIN 指定カテゴリ
ON [ｇ]
WHERE 出品.出品価格 BETWEEN :下限価格 AND :上限価格
AND 出品.商品状態 = :商品状態
AND 出品.出品状況 = :出品状況
AND (出品.商品名 [ｈ] OR 出品.商品説明 [ｈ] )
図2 作成したSQL文 ここまで
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
・カーディナリティ
・データ分布
・定義内容
表1の終わりまで上記の項目順で繰り返します。
・1
・出品ID
・INT
・Y
・N
・Y
・高
・一様分布
・1～20000000000の整数
・2
・出品者ID
・INT
・N
・N
・Y
・高
・一様分布
・1～20000000000の整数
・3
・カテゴリID
・INT
・N
・N
・Y
・中
・一様分布
・外部キー(カテゴリ)
・4
・商品名
・VARCHAR(255)
・N
・N
・Y
・高
・一様分布
・最大255字の文字列
・5
・商品説明
・CLOB
・N
・N
・N
・高
・一様分布
・(省略)
・6
・出品価格
・DECIMAL(10,2)
・N
・N
・Y
・高
・対数正規分布(確率密度の最大値:0.00003)
・0.00～99999999.99の固定小数点数
・7
・商品状態
・SMALLINT
・N
・N
・Y
・低
・一様分布
・1～3の整数(1は未使用、2は美品、3は傷ありを示す)
・8
・出品状況
・SMALLINT
・N
・N
・Y
・低
・一様分布
・1～3の整数(1は下書き、2は出品中、3は購入済みを示す)
表2 出品表の表定義 ここまで
表3 カテゴリ表の表定義 ここから
・項番
・列名
・データ型
・PK
・UK
・非NULL
・カーディナリティ
・データ分布
・定義内容
表3の終わりまで上記の項目順で繰り返します。
・1
・カテゴリID
・INT
・Y
・N
・Y
・高
・一様分布
・1～10000の整数
・2
・カテゴリ名
・VARCHAR(100)
・N
・N
・Y
・高
・一様分布
・最大100字の文字列
・3
・上位カテゴリID
・INT
・N
・N
・N
・中
・一様分布
・外部キー(カテゴリ)
表3 カテゴリ表の表定義 ここまで
Tさんは、必要なインデックスを設定後にテストデータを用いて図2のSQL文の実行性能を検証し、実用的な性能であることを確認した。ただし、表2及び表3のデータ分布は新規事業立上げ前の時点における仮定でしかない。今後実際に運用する際にはデータ分布が仮定とは異なる場合があるので、定期的にインデックスを見直すことを申し送り事項の一つとして、本システムのデータベースの設計及び開発を完了した。
設問1 図1中の [ａ] ～ [ｄ] に入れる適切なエンティティ間の関連及び属性名を答え、概念データモデルを完成させよ。なお、エンティティ間の関連及び属性名の表記は、図1の凡例に倣うこと。
設問2 図2中の [ｅ] ～ [ｈ] に入れる適切な字句又は式を答えよ。
設問3 [性能の検証と改善]について答えよ。
(1) 本文中の下線①について、B-treeインデックスの特性として適切なものを回答群の中から三つ選び、記号で答えよ。
回答群
ア インデックスを設定した各列に対する条件をAND演算子で組み合わせた検索は高速化できるが、NOT演算子を用いた条件による検索は高速化できない。
イ インデックスを設定した列に対して演算や型変換を行った上で検索条件に指定した場合、検索を高速化できる。
ウ インデックスを設定した列に含まれる値の分布に偏りがない場合、検索性能が安定する。
エ カーディナリティが低い列にインデックスを設定すると、検索を高速化できる。
オ 行数nの表において、特定の行を検索するときの計算量はＯ(log n)である。
カ 行数nの表において、特定の行を検索するときの計算量はＯ(n)である。
キ 等号演算子を用いた条件による検索は高速化できるが、値の範囲を指定した条件による検索は高速化できない。
(2) 出品表及びカテゴリ表のそれぞれについて、表2及び表3を基に、B-treeインデックスを設定することで、図2のSQL文の実行性能の高速化に寄与する列名を全て答えよ。なお、本システムで使用する関係データベースでは、主キーに対して自動的にインデックスが設定される。
令和6年度秋期 午後 問6 データベース(問題原文)
■トレーディングカードの個人間売買サイトの構築に関する次の記述を読んで設問に答えよ。
S社は、トレーディングカード販売業のチェーンを営む中堅企業である。トレーディングカードを個人から買い取り、販売する事業を営んでいる。トレーディングカードの個人間の売買が盛んな市場環境を受け、個人間の売買を安心かつ手軽に行える取引プラットフォームをサービスとして提供して、安定的な手数料収入を得る新規事業を立ち上げることにした。S社の情報システム部は新規事業の要となる取引プラットフォームのシステム(以下、本システムという)を新規で構築することになり、Tさんがデータベースの設計及び開発を担当することになった。
[新規事業の業務要件の確認]
Tさんは、まず、新規事業において実現する業務要件を確認した。新規事業の業務要件(抜粋)を表1に示す。
| 項番 | 業務要件 |
| 1 | 本システムの利用者は個人である。利用者は販売したいトレーディングカードを商品として出品できる。出品した利用者を出品者と呼ぶ。出品する際にはカテゴリ、商品名、商品説明、出品価格、商品状態を登録する。カテゴリはトレーディングカードのブランドやシリーズによって階層化されている。 |
| 2 | 利用者は全ての出品に対してカテゴリ、価格帯(下限価格と上限価格)、商品状態、出品状況、キーワードを指定して検索できる。カテゴリを指定する場合、指定されたカテゴリ及びその下位にある全てのカテゴリの出品が検索の対象となる。キーワードは、商品名及び商品説明を部分一致で検索したい場合に指定する。検索した結果を表示する際に並び順を変更できる。 |
| 3 | 利用者は出品された商品に対して取引を希望する価格(以下、オファー価格という)を出品者に打診できる。この行為をオファーと呼ぶ。オファー価格での取引に出品者が合意した場合、その価格はオファーした利用者と出品者との間での当該商品の出品に対してだけ有効となる。 |
| 4 | 利用者が商品を購入した場合、購入した利用者を購入者と呼ぶ。出品者は配送方法に基づいて送料を算出し、購入者に送料を通知する。購入者は出品価格(オファーが合意済みの状態の場合はオファー価格)と送料の合計金額を支払期日までにS社に対して支払う。一つの取引に関する合計金額を分割して支払うことや、複数の取引に関する合計金額をまとめて支払うことはできない。 |
| 5 | 出品者は購入者の支払が完了したことをS社からの通知で確認した上で、購入者の住所に商品を発送する。一つの取引に関する商品を分割して発送することや、複数の取引に関する商品をまとめて発送することはできない。S社は発送した商品の追跡番号を管理し、配送業者のWebサイトと連携することで、出品者や購入者が商品の配送状況を確認できるようにする。 |
| 6 | 購入者は商品を受領後、商品に問題がないことを確認した上で、受取連絡と出品者の評価を行う。購入者からの評価を受けて、出品者も購入者の評価を行う。 |
| 7 | 購入者と出品者の双方の評価が完了した後に、S社は購入者から入金された代金から手数料を差し引いた金額を出品者に支払う。 |
| 8 | 利用者はお気に入りの利用者をフォローできる。フォロー先の利用者が新たな商品を出品した場合、フォロー元の利用者は通知を受け取ることができる。 |
[概念データモデルの設計]
Tさんは、表1の業務要件に基づいて、E-R図を用いて本システムの概念データモデルを設計した。本システムの概念データモデル(抜粋)を図1に示す。なお、カテゴリの階層構造は、自己参照の関連を用いて表現する。
本システムのデータベースでは、E-R図のエンティティ名を表明にし、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。
[SQLの作成]
Tさんは、表1の項番2の業務要件を実現するための検索のSQL文を作成した。作成したSQL文を図2に示す。なお、“:カテゴリID”、“:下限価格”、“:商品状態”、“:出品状況”、“:キーワード” は、該当の値を格納する埋込み変数である。また、最上位であるカテゴリの上位カテゴリIDにはNULLが設定されている。
|
ｅ 指定カテゴリ(カテゴリID, カテゴリ名, 上位カテゴリID) AS ( |
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%以下に絞り込める列だけにインデックスを設定することにした。
| 項番 | 列名 | データ型 | PK | UK | 非NULL | カーディナリティ | データ分布 | 定義内容 |
| 1 | 出品ID | INT | Y | N | Y | 高 | 一様分布 | 1～20000000000の整数 |
| 2 | 出品者ID | INT | N | N | Y | 高 | 一様分布 | 1～20000000000の整数 |
| 3 | カテゴリID | INT | N | N | Y | 中 | 一様分布 | 外部キー(カテゴリ) |
| 4 | 商品名 | VARCHAR(255) | N | N | Y | 高 | 一様分布 | 最大255字の文字列 |
| 5 | 商品説明 | CLOB | N | N | N | 高 | 一様分布 | (省略) |
| 6 | 出品価格 | DECIMAL(10,2) | N | N | Y | 高 | 対数正規分布(確率密度の最大値:0.00003) | 0.00～99999999.99の固定小数点数 |
| 7 | 商品状態 | SMALLINT | N | N | Y | 低 | 一様分布 | 1～3の整数(1は未使用、2は美品、3は傷ありを示す) |
| 8 | 出品状況 | SMALLINT | N | N | Y | 低 | 一様分布 | 1～3の整数(1は下書き、2は出品中、3は購入済みを示す) |
| 項番 | 列名 | データ型 | PK | UK | 非NULL | カーディナリティ | データ分布 | 定義内容 |
| 1 | カテゴリID | INT | Y | N | Y | 高 | 一様分布 | 1～10000の整数 |
| 2 | カテゴリ名 | VARCHAR(100) | N | N | Y | 高 | 一様分布 | 最大100字の文字列 |
| 3 | 上位カテゴリID | INT | N | N | N | 中 | 一様分布 | 外部キー(カテゴリ) |
Tさんは、必要なインデックスを設定後にテストデータを用いて図2のSQL文の実行性能を検証し、実用的な性能であることを確認した。ただし、表2及び表3のデータ分布は新規事業立上げ前の時点における仮定でしかない。今後実際に運用する際にはデータ分布が仮定とは異なる場合があるので、定期的にインデックスを見直すことを申し送り事項の一つとして、本システムのデータベースの設計及び開発を完了した。
設問1 図1中の ａ ～ ｄ に入れる適切なエンティティ間の関連及び属性名を答え、概念データモデルを完成させよ。なお、エンティティ間の関連及び属性名の表記は、図1の凡例に倣うこと。
設問2 図2中の ｅ ～ ｈ に入れる適切な字句又は式を答えよ。
設問3 [性能の検証と改善]について答えよ。
(1) 本文中の下線①について、B-treeインデックスの特性として適切なものを回答群の中から三つ選び、記号で答えよ。
回答群
ア インデックスを設定した各列に対する条件をAND演算子で組み合わせた検索は高速化できるが、NOT演算子を用いた条件による検索は高速化できない。
イ インデックスを設定した列に対して演算や型変換を行った上で検索条件に指定した場合、検索を高速化できる。
ウ インデックスを設定した列に含まれる値の分布に偏りがない場合、検索性能が安定する。
エ カーディナリティが低い列にインデックスを設定すると、検索を高速化できる。
オ 行数nの表において、特定の行を検索するときの計算量はＯ(log n)である。
カ 行数nの表において、特定の行を検索するときの計算量はＯ(n)である。
キ 等号演算子を用いた条件による検索は高速化できるが、値の範囲を指定した条件による検索は高速化できない。
(2) 出品表及びカテゴリ表のそれぞれについて、表2及び表3を基に、B-treeインデックスを設定することで、図2のSQL文の実行性能の高速化に寄与する列名を全て答えよ。なお、本システムで使用する関係データベースでは、主キーに対して自動的にインデックスが設定される。
回答・解説
この回のデータベースは難しめだったんじゃないでしょうか？再帰問い合わせをするDB句を使うのですが、そんな構造をしているDBを扱う経験というのは大規模なシステムを使ったことがある人でも巡り合わないかもしれない構造のDBだと思います。管理人もそんな方法もあったの？と、なってしまいました。知ってる人は知ってるんでしょうけどね。WITH RECURSIVE～UNION ALL～句。手ごわいねぇ。
設問1
まずは、恒例のE-R図(Entity-Relation図)の穴埋め問題です。一対ーなのか、一対多なのか？抜けている列名は何かを問うています。
ａ
ａは表:カテゴリの自己参照を示す。線が入ります。コの字型の矢印ですね。別に角ばっていなくても、丸まっていてもいいんでしょうけど。凡例には自己参照の描き方について取り決めもないし、IPAから、こうしろという指示も聞いたことはないです。表:カテゴリの列名:上位カテゴリが表:カテゴリの列名:カテゴリを参照する部分です。カテゴリはトレーディングカードのブランドやシリーズによって階層化されている。という問題文にも記述のあるところです。上位カテゴリは複数のカテゴリを保持していることが予想できます。ですので、自己参照の始まりと終わりのどちらかが矢印になっていればよいと思います。したがって答えは
ａ ←┘
となります。
ｂ
ｂは表:取引の列名:取引IDと表:発送の列名:取引IDの関係です。問題文に一つの取引に関する商品を分割して発送することや、複数の取引に関する商品をまとめて発送することはできない。とありますので1対1の関係になります。よくあるネットショッピングのように取引の複数の商品が含まれていて、複数の発送に分割するという常識があるのは通用しません。1対1です。したがって
ｂ ─
となります。
ｃ
ｃは欠けた列名を考えるモノで、表:発送に対して列名:発送ID(主キー)、取引ID(外部キー)、配送方法ID、送料の他に何が足りないかですね。列名の一番最後が穴埋めなので、いやらしい配置でなければ、主キーや外部キーではない列名ということになりますが、念のため外部キーの何かでないか確認しましょう。つながり自体は、表:配送方法、取引とつながりがあり、それぞれの余っているキーは、配送業者IDと出品ID、購入者IDです。表:発送にこれらの情報が必要かですが、既に取引IDと配送方法IDで二つの表の主キーが設定されているので、不足がなく、必要にはならなさそうです。ということは発送にだけ必要な送料レベルの情報と同じ何かが必要になります。問題文の中で送料と出てくる部分をくまなく見渡すと、電子媒体で書かれた問題なら検索で送料という語句を探せるのですが、人力だと精度が落ちますが頑張るしかないです。問題文が長すぎるという感じもあるので、大変な作業です。
図1の発送IDと発送の他に語句が出てくるのは、表1の項番5にしかありません。出品者は購入者の支払が完了したことをS社からの通知で確認した上で、購入者の住所に商品を発送する。一つの取引に関する商品を分割して発送することや、複数の取引に関する商品をまとめて発送することはできない。S社は発送した商品の追跡番号を管理し、配送業者のWebサイトと連携することで、出品者や購入者が商品の配送状況を確認できるようにする。とあります。
一つの取引～発送することはできない。ｂの穴埋めを解くのに必要な文章なので省くと出品者は購入者の支払が完了したことをS社からの通知で確認した上で、購入者の住所に商品を発送する。S社は発送した商品の追跡番号を管理し、配送業者のWebサイトと連携することで、出品者や購入者が商品の配送状況を確認できるようにする。
となります。購入者の住所に商品を発送する。発送した商品の追跡番号を管理し、配送業者のWebサイトと連携する。出品者や購入者が商品の配送状況を確認。この内容からすると発送には住所やWebサイトも必要ですが、追跡番号も必要です。住所は、表:取引の取引IDに対応するレコードの購入者IDから住所を結び付けることはできますし、Webサイトも配送方法IDからさかのぼっていけば、Webサイトも結び付けができます。しかしながら、追跡番号だけは何処にもなく、この表:発送の中に割り当てられていないと追跡番号は管理できないことになっていそうです。したがって、
ｃ 追跡番号
が答えとなります。
ｄ
ｄは表:利用者の列名:利用者IDと表:フォローの列名フォロー先利用者IDとの関係です。フォロー元利用者ID毎に複数のフォローが発生します。フォロー元利用者IDの情報も複数になっていますので、dの上に利用者側が1のフォロー側が多の矢印が引かれています。フォロー先利用者IDも複数でそれに対して、利用者が複数あるわけではなく、利用者側はやっぱり1で、フォロー側が多になります。したがって、
ｄ ─→
が答えになります。いつもより少し難しい穴埋めになっていますね。ｃのような列名穴埋めは違う表のキーを外部キーとするような問いが従来おおかったのに、問題文から考えるという少し難しいものでしたし、ｄのような同じ表どおしで関係性を問う問題もいままでなかったと思うしａは自己参照ってすぐわかる例のアレなので楽かな。珍しくｂで一対一の答えを求めてきたり。一体多で答えるのが多かった印象だけどね。今回は違うことやってやろうって感じが問題作者のモチベーションを感じさせる出だしですね。やだな。
設問2
SQLの穴埋めです。なんじゃこれ！ってなった人も多かったんじゃないかな。しかも、なんじゃこれのｅとｆは対になる語句なので、知らなければ答えられません。ｅとｆの間にかかれているのが、新しく作った表の名前を伴うモノで、表:指定カテゴリを作ってFROM カテゴリ Aとあるので表:カテゴリをもとにしているっぽい。表:指定カテゴリの列としてはSELECT A.カテゴリID, A.カテゴリ名, A.上位カテゴリIDをそれぞれ(カテゴリID, カテゴリ名, 上位カテゴリID)としている表を作っているので、近年の午後問題でも出たものを知っているだけの人ならWITH句っぽいなと思ったと思いますが、惜しいっす。残念。表:カテゴリが自己参照をすることをふまえれば、WITH RECURSIVE～UNION ALL～句だ！ってならないと答えれない問題でした。豊富なSQL句への知識を問われる問題だったと思います。対のｆの後の検索自体も同じ表のカテゴリを使った検索になっていますので、これでピンとこない人には無理な問題でした。WITH RECURSIVE～UNION ALL～句です。再帰問い合わせをするwith句です。おぼえるしかないね。
ｆの後も整理しておくと、またFROM カテゴリ Bと別名がBになっているだけの同じ表の参照になっています。そして、FROMの中にWITHっぽいもの、つまりWITH RECURSIVEなわけですが、上の部分で作った指定カテゴリ Cという表を参照することになっています。そして、B.上位カテゴリ=C.カテゴリIDと一致するレコードを対象にしています。C.カテゴリIDまさに今作った自分自身の列名なわけで、ここまで来たらWITH RECURSIVE～UNION ALL～句一択になります。再帰で呼ばれる全部を足し合わせたレコードなので、UNION ALLという句が出てくる可能性はあるわけで、WITH RECURSIVEが出てこなくてもｆだけでも正解できたという猛者もいた可能性はあります。なんやわからんけど、どうせ重複は除去して足し合わせるんでしょ。という思考だった人はUNIONとだけ書いて不正解になった可能性もあるのかもしれません。というわけで、
ｅ WITH RECURSIVE
ｆ UNION ALL
これが答えですね。むっず。これは難しいわと各情報処理技術試験の資格講座なんかをやっている企業も総評を出していました。次の組込みも含めて、最難関だったかもしれない。WITH RECURSIVE～UNION ALL～句は業界でも使わない企業は使わないし、ひとつのDBの表で何階層あるかわからないカテゴリのようなものを自己参照で管理すること自体も珍しいことだと思います。しかもWITH RECURSIVE～UNION ALL～句をという技術を紹介している人やWebサイト自体も少ないし、まとめテキストにも載っていないレベルです。いいテキスト使ってる人は知っていたのかもしれないし、WITH RECURSIVE～UNION ALL～句を使っているプロジェクトに参画しているような運のいい人もいたのかもしれないと思います。いずれにしてもむっずです。答えれた人、さすが！って感じ。
ｇは出品テーブルと今作った再帰構造から作成した、表:指定カテゴリを使って、商品検索をする部分になります。FROM で表:出品を参照していますし、何をやろうとしているか察知できれば、上の二問がわからなくても、しきりなおしで答えれる可能性があります。ONなのでJOINで表:指定カテゴリと結合させるための条件を書くのだなと理解できれば、作成した表:指定カテゴリのキーと表:出品の外部キーが同じものを条件としてあげればいいということしか思いつかないと思います。したがって
ｇ 出品.カテゴリID=指定カテゴリ.カテゴリID
となります。
ｈは2箇所あって表:商品の列名:商品名と列名商品名のふたつに共通する条件を答えます。おなじ条件？あ、検索するっていってたあれかな？と、問題文の
AP過去問_令和6年度秋期_午後_問5_ネットワークの同じ回の前の問題へ移動。
AP過去問_令和6年度秋期_午後_問7_組込みシステム開発の同じ回の次の問題へ移動。
AP 過去問題 午後に戻る。