応用情報技術者過去問題令和3年秋期午後問6(データベース)

応用情報技術者過去問題令和3年秋期午後問6

問6　データベース

企業向け電子書籍サービスの追加設計と実装に関する次の記述を読んで，設問1～4に答えよ。

　H社は，個人会員向けに電子書籍の販売及び閲覧サービス(以下，既存サービスという)を提供する中堅企業である。近年，テレワークの普及に伴い，企業での電子書籍の需要が高まってきた。そこで，既存サービスに加え，企業向け電子書籍サービス(以下，新サービスという)を開発することになった。
　新サービスの開始に向けて，企業向け書籍購入サイトを新たに作成し，既存サービスで提供している電子書籍リーダーを改修する。新サービスの機能概要を表1に，検討したデータベースのE-R図の抜粋を図1に示す。
　このデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。

〔一括購入機能の社員割当処理の作成〕
　表1中の一括購入機能の概要(2)にある，社員が割当依頼した電子書籍を割り当てる処理を考える。検討した処理の流れを表2に示す。ここで，":一括購入ID"は割当依頼された一括購入IDを，":企業ID"及び":社員ID"は割当依頼した社員の企業IDと社員IDを格納する埋込み変数である。

　表2のレビューを実施したところ，処理の流れやSQL文に問題はないが，①トランザクションの同時実行制御には専有ロックを用いるように，とのアドバイスを受けた。

〔書籍閲覧機能の作成〕
　電子書籍リーダーに，社員がログインした際，閲覧可能な重複を含まない書籍の一覧を取得するSQL文を図2に示す。ここで，":企業ID"及び":社員ID"は，ログインした社員の企業IDと社員IDを格納する埋込み変数である。また，図2のcには，図1のcと同じ字句が入る。

〔書籍閲覧機能の改善〕
　書籍閲覧機能のレビューを実施したところ，既存サービスを個人で利用している社員は，電子書籍リーダーのログインIDを個人会員IDから企業IDと社員IDに切り替えて利用しなければならず煩雑である，との指摘を受けた。
　そこで，電子書籍リーダーに個人会員IDを用いてログインした際，社員として閲覧できる書籍も一覧に追加して閲覧できるように，E-R図に新たに②一つエンティティを追加し，電子書籍リーダーに③一つ画面を追加した上で書籍閲覧機能に改修を施した。

設問1

図1中のa～cに入れる適切なエンティティ間の関連及び属性名を答え，E-R図を完成させよ。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください)

解答入力欄

a：
b：
c：

解答例・解答の要点

a：→
b：割引率
c：書籍ID

解説

〔aについて〕
書籍分類エンティティと企業補助エンティティには共通の属性「書籍分類ID」があります。この属性「書籍分類ID」は、書籍分類エンティティ側では主キーであり、書籍分類を一意に特定します。また、企業補助エンティティ側では外部キーであり、異なる企業の社員が同一の書籍分類の書籍の購入を行うことが考えられます。つまり、企業補助エンティティには同一の書籍分類IDの値を持つ複数のレコードが存在しえるということです。以上より、書籍分類エンティティと企業補助エンティティのカーディナリティは1対多です。空欄aには「→」が当てはまります。

∴a＝→

〔bについて〕
表1の割引購入の概要には「それぞれの企業がH社と契約した一定の割引率を適用した価格で購入できる」とあります。つまり、企業ごとに割引率が設定されるということですが、企業エンティティには割引率を表す属性がないので追加する必要があります。したがって、空欄bには「割引率」が当てはまります。

∴b＝割引率

〔cについて〕
書類エンティティと社員書籍購入エンティティには1対多の関係があります。多側の社員書籍購入エンティティには1側の書類エンティティの主キーである属性"書籍ID"を参照する外部キーがあるはずですが、書籍IDに相当する属性がないので追加する必要があります。したがって、空欄cには「書籍ID」が当てはまります。

∴c＝書籍ID

設問2

〔一括購入機能の社員割当処理の作成〕について，(1)，(2)に答えよ。

表2中のd～fに入れる適切な字句を答えよ。
本文中の下線①の専有ロックを用いなかった場合，どのような問題が発生するか。30字以内で述べよ。

解答入力欄

- d：
- e：
- f：

解答例・解答の要点

d：COUNT(*)
e：大きい
f：VALUES(:一括購入ID，:社員ID，:企業ID)

一括購入数量より多い数量の書籍を割り当ててしまう問題 (26文字)

解説

〔dについて〕
一括購入割当エンティティは、一括購入された電子書籍とそれが割り当てられた社員との関係を保持するエンティティです。「WHERE 一括購入ID = ：一括購入ID」で、ある一括購入IDをキーにして一括購入割当テーブルを検索すれば、その一括購入IDと関連付けられている社員IDが列挙されます。手順2ではある一括購入IDのうち、現在割り当てられている数量を取得したいので、抽出されたレコードの件数を数えれば良いことになります。したがって、空欄dには「COUNT(*)」が当てはまります（*でなく一括購入IDなどでも正解です）。

∴d＝COUNT(*)

〔eについて〕
手順1で取得した数量は当該一括購入の一括購入数量、手順2で取得したのはそのうち現在割り当てられている数量です。「eの場合，手順4に進む」とあるので手順4を見ると「社員に一括購入IDを割り当てる」とあります。社員に電子書籍を割り当てるには、未割当の電子書籍が存在しなくてはなりませんから、手順4に進むための条件として「一括購入数量＞現在割り当てられている数量」、すなわち「手順1の数量＞手順2の数量」となっている必要があります。したがって、空欄eには「大きい」や「多い」が当てはまります。

∴e＝大きい

〔fについて〕
テーブルにレコードを挿入するSQL文について確認します。以下が基本形です。
INSERT INTO テーブル名 (列名1, 列名2, …)
VALUES (列名1の値, 列名2の値, …)
手順4のSQL文にはVALUES句が不足しているので、空欄fには挿入するレコードの列値を指定する文が入ると判断できます。

割当依頼された一括購入IDは「:一括購入ID」に、割当依頼した社員の企業IDと社員IDは「:企業ID」及び「:社員ID」に格納されています。また、列名は一括購入ID，社員ID，企業IDの順に記述されているので、列値もこれに合わせて並べる必要があります。したがって、空欄fには「VALUES(:一括購入ID，:社員ID，:企業ID)」が当てはまります。

∴f＝VALUES(:一括購入ID，:社員ID，:企業ID)
残り未割当数が1だったときに複数の社員から同時に割当依頼があると、以下の処理手順によって一括購入数量を超える割当てが行われてしまう可能性があります。
1. 一括購入数量が10、現在割り当てられている数が9の状態で、トランザクションA・Bが並行して実行される
2. Aの手順1の取得結果は"10"
3. Bの手順1の取得結果は"10"
4. Aの手順2の取得結果は"9"
5. Bの手順2の取得結果は"9"
6. Aの手順3の判定は手順4を行う
7. Bの手順3の判定は手順4を行う
8. Aは手順4を実行し、当該一括購入IDに対する一括購入割当テーブルのレコード件数は10になる
9. Bは手順4を実行し、当該一括購入IDに対する一括購入割当テーブルのレコード件数は11になる
この誤動作を回避するためには、少なくとも手順2を実行する前に一括購入割当テーブルを専有ロックする必要があります。専有ロックをかければAがコミットされてからBが実行されるので、Bが手順2で取得する数は"10"となり、手順3で正しく処理を終了することができます。

したがって、専有ロックを用いなかった場合に発生する問題としては、「一括購入数量より多い数量の書籍を割り当ててしまう問題」「一括購入数量を超える割当てが行われてしまう問題」などの解答が適切となります。

∴一括購入数量より多い数量の書籍を割り当ててしまう問題

設問3

図2中のg，hに入れる適切な字句又は式を答えよ。
なお，表の列名には必ずその表の相関名を付けて答えよ。

解答入力欄

g：
h：

解答例・解答の要点

g：UNION
h：ON ik.一括購入ID = iw.一括購入ID

解説

空欄cには「書籍ID」が入るので、これを埋めると図2のSQLは次のようになります。

〔gについて〕
図2のSQL文では、SELECT文が2つ続けて記載されています。この場合、SELECT文の間に2つの結果を結び付ける集合演算子を記述する必要があります。

ある社員が閲覧可能な電子書籍は、

当該社員が購入した書籍（1つ目のSELECT文）
一括購入により当該社員に割り当てられた書籍（2つ目のSELECT文）

を合わせたものですから、閲覧可能な重複を含まない書籍の一覧を取得するためには、2つの和集合を取ればよいと考えられます。和集合を取るにはUNION句を用いますので、空欄gには「UNION」が当てはまります。

∴g＝UNION

〔hについて〕
空欄hはINNER JOIN句の直後にあることからON句であることがわかります。INNER JOIN句の構文は以下のとおりです。

テーブルA INNER JOIN テーブルB ON A.列名 = B.列名

結合対象のテーブルは一括購入テーブルと一括購入割当テーブルで、それぞれ ik 及び iw というエイリアスが付けられています。また、一括購入割当テーブルの外部キー"一括購入ID"は、一括購入テーブルの主キー"一括購入ID"を参照しています。2つのテーブルを"一括購入ID"で結合したいので、空欄hには「ON ik.一括購入ID = iw.一括購入ID」が当てはまります。

∴h＝ON ik.一括購入ID = iw.一括購入ID

設問4

〔書籍閲覧機能の改善〕について，(1)，(2)に答えよ。

本文中の下線②で追加したエンティティの属性名を全て列挙せよ。
なお，エンティティの属性名に主キーや外部キーを示す下線は付けなくてよい。
本文中の下線③とは，どのような画面か。25字以内で述べよ。

解答入力欄

解答例・解答の要点

個人会員ID，企業ID，社員ID

個人会員が企業IDと社員IDを登録する画面 (21文字)

解説

ログインIDの切り替え不要で社員として閲覧可能な書籍を一覧に追加にするには、個人会員と社員を紐づけ、社員に割り当てられている電子書籍をDBから引っ張ってくる必要があります。このためには個人会員と社員の対応関係を保持するエンティティが必要ですが、図1のE-R図には存在していません。よって、これが追加するエンティティだとわかります。個人会員は個人会員IDで、社員は企業ID・社員IDで特定できるので、この3つの属性を持たせればよいということになります。したがって、答えは「個人会員ID，企業ID，社員ID」です。

なお、個人会員ID，企業ID，社員IDすべて主キーです。

∴個人会員ID，企業ID，社員ID
個人会員と社員の対応関係は自動で登録されるわけではないので、(1)のエンティティにレコードを追加するための機能が必要です。既存サービスを利用している社員が必要に応じて登録することになるので、対応関係を登録するために、個人会員に社員IDと企業IDを入力してもらう画面が新たに必要となります。したがって、答えは「個人会員が企業IDと社員IDを登録する画面」です。

∴個人会員が企業IDと社員IDを登録する画面

令和3年秋期午後問題一覧

問1　問2　問3　問4　問5　問6　問7　問8　問9　問10　問11　採点講評