データベーススペシャリスト　令和6年 午後1　解説解答

- 問1
  - [設問1-(1)](#問1-設問1-1)
  - [設問1-(2)](#問1-設問1-2)
  - [設問2-(1)](#問1-設問2-1)
  - [設問2-(2)](#問1-設問2-2)
  - [設問3-(1)](#問1-設問3-1)
  - [設問3-(2)](#問1-設問3-2)
- 問2
  - [設問1-(1)](#問2-設問1-1)
  - [設問1-(2)](#問2-設問1-2)
  - [設問1-(3)](#問2-設問1-3)
  - [設問2-(1)](#問2-設問2-1)
  - [設問2-(2)](#問2-設問2-2)
  - [設問2-(3)](#問2-設問2-3)
- 問3
  - [設問1-(1)](#問3-設問1-1)
  - [設問1-(2)](#問3-設問1-2)
  - [設問1-(3)](#問3-設問1-3)
  - [設問2](#問3-設問2)
  - [設問3-(1)](#問3-設問3-1)
  - [設問3-(2)](#問3-設問3-2)
  - [設問3-(3)](#問3-設問3-3)
  - [設問3-(4)](#問3-設問3-4)

公式サイトの問題冊子はこちら。
https://www.ipa.go.jp/shiken/mondai-kaiotu/m42obm000000afqx-att/2024r06a_db_pm1_qs.pdf
# 問1-設問1-(1)
*図1の概念データモデルは未完成である。欠落しているリレーションシップを7本補って図を完成させよ*
- 「講師としてのアカウントを既に保持している場合、**再度アカウントを登録する必要はなく**、支払情報を追加で登録する」より、１つのアカウントが「講師」「受験生」のどちらともサブタイプに持つ場合があるので、**切り口を持たないサブタイプのリレーションシップ**をそれぞれ描く
::: note info
```mermaid
graph TB
  アカウント-->|サブタイプ|講師
  アカウント-->|サブタイプ|受講生
```
:::
- 「セクションは、一つ又は複数の講義動画と、講義動画を視聴した後の理解度を確認する**高々一つのテスト**から成る」とあるので、テストにセクションを1対1で紐付ける
::: note info
```mermaid
graph TB
  セクション---テスト
```
:::
- 「**講義動画とテストとを併せてコンテンツ**と呼び、」
::: note info
```mermaid
graph TB
  セクション-->コンテンツ
```
:::
- 「コースの購入は、コース購入#で識別する。購入情報(購入年月日、購入価格及び**受講生アカウント#**)は、PF上に記録される」とあるので、コース購入にアカウントを多対1で紐付ける
::: note info
```mermaid
graph TB
  アカウント-->|購入者|コース購入
```
:::
- 「クーポンを使用する際は、クーポン#を指定して、購入の手続きを行う」とあるので、コース購入にクーポンを多対1で紐付ける
::: note info
```mermaid
graph TB
  クーポン-->コース購入
```
:::
- 「受講生がコースをプレゼントするには、**相手のアカウント**と対象のコースをそれぞれ一つずつ指定して購入の手続を行う」とあるので、コース購入にアカウントを多対1で紐付ける
::: note info
```mermaid
graph TB
  アカウント-->|受講者|コース購入
```
:::
# 問1-設問1-(2)
*図2中のaとbに入れる一つ又は複数の適切な属性名を補って関係スキーマを完成させよ*
- 「コースの購入は、コース購入#で識別する。購入情報(購入年月日、購入価格及び**受講生アカウント#**)は、PF上に記録される」とあるので、コース購入に受講生アカウントを記録する
- 「クーポンを使用する際は、クーポン#を指定して、購入の手続きを行う」とあるので、コース購入にクーポン#を記録する
- 「受講生がコースをプレゼントするには、**相手のアカウント**と対象のコースをそれぞれ一つずつ指定して購入の手続を行う」とあるので、コース購入に受講生アカウントとは別に購入者アカウントを記録する
::: note info
a:受講生アカウント#、クーポン#、購入者アカウント#
:::
- 「受験生は、自分が最後に実施したテストで選択した**各設問の選択肢**と回答日時を確認することができる」とあるので、**設問#と選択肢#**を記録する
- テスト結果詳細をテスト設問に1対1で紐付けるために**設問#を主キー**にする
::: note info
b:<INS>設問#</INS>、選択肢#(FK)
:::
# 問1-設問2-(1)
*図3の欠落しているリレーションシップを4本補って図を完成させよ*
- 「企業は、受講させる**一つ以上のコースをまとめた研修カリキュラム**を作成する」とあるので、コースと研修カリキュラムは多対多
- 多対多を実装するための連関エンティティとしては「あ」があてはまる
::: note info
```mermaid
graph TB
  コース-->あ
  研修カリキュラム==>あ
```
:::
- 「研修カリキュラムへの法人受講生の割り当てでは、あらかじめ**複数の法人受講生を束ねたグループを作成**し、**グループ単位で割当て**を行う」
- 法人受講生とグループは多対多で、連関エンティティは「い」
- グループと研修カリキュラムは多対多で、連関エンティティは「う」
::: note info
```mermaid
graph TB
  法人受講生-->い
  グループ==>い
  グループ-->う
  研修カリキュラム==>う
```
:::
- 「**テストを含むコースについては、研修カリキュラムごとに**各テストの合格基準点を設定できる」
- テストと研修カリキュラムは多対多で、連関エンティティは「え」
::: note info
```mermaid
graph TB
  法人受講生==>え
  テスト-->え
```
:::
# 問1-設問2-(2)
*図4中のcとdに入れる一つ又は複数の適切な属性名を補って関係スキーマを完成させよ*
- 「あ」は、多対多のコースと研修カリキュラムを実装するための連関エンティテイなので、コース#と**研修カリキュラム**#を主キーにもつ
- 「研修カリキュラムに含まれるコードには、**受講順**を設定する」
::: note info
  c: <INS>研修カリキュラム#</INS>、受講順
:::
- 「い」は、多対多の法人受講生とグループを実装するための連関エンティテイなので、アカウント#と**グループ**#を主キーにもつ
::: note info
  d: <INS>グループ#</INS>
:::
- 「う」は、多対多のグループと研修カリキュラムを実装するための連関エンティテイなので、グループ#と**研修カリキュラム**#を主キーにもつ
- 「また、同じ研修カリキュラムでも、割り当てられるグループによって、**受講開始可能年月日**及び**受講完了期限年月日**が異なる場合がある」
::: note info
  e: <INS>研修カリキュラム#</INS>、受講開始可能年月日、受講完了期限年月日
:::
- 「え」は、多対多の法人受講生と研修カリキュラムを実装するための連関エンティテイなので、**アカウント**#と**研修カリキュラム**#を主キーにもつ
::: note info
  f: <INS>アカウント#</INS>、<INS>研修カリキュラム#</INS>
:::
- 「お」は、多対多の研修カリキュラムとテストを実装するための連関エンティテイなので、**研修カリキュラム#**(とテストの主キー)を主キーにもつ
- 「テストを含むコースについては、研修カリキュラムごとに各テストの**合格基準点**を設定できる」
::: note info
  g: <INS>研修カリキュラム#</INS>、合格基準点
:::
# 問1-設問3-(1)
*図1の概念データモデル上のリレーションシップを一つ変更する。どのようにリレーションシップを変更すべきか。具体的なエンティテイタイプ名を挙げ、40字以内で答えよ*
- 「質問に対して**回数に制限なく**コメントできるように」とあるので、質問とコメントは1対1ではなく1対多
::: note info
質問とコメントの間のカーディナリティを1対多に変更
:::
# 問1-設問3-(2)
*図1の概念データモデル上に新しくリレーションシップを二つ追加する。どのようにリレーションシップを追加すべきか。具体的なエンティテイタイプ名を挙げ、それぞれ40字以内で答えよ*
- 「コースを提供する講師だけではなく、**他の講師及び受験生も**質問に対して制限なくコメントできるようにしてほしい」
- 「その時、**誰が**コメントしたのかを特定できるようにしてほしい」
::: note info
コメントとアカウントとの間に多対1のリレーションシップを追加
:::
- 「質問だけではなく、**既出のコメントを指定して**コメントできるようにしてほしい」
::: note info
コメントに多対1の再帰リレーションシップを追加
:::
# 問2-設問1-(1)
*表1中のa～dに入れる適切な字句を答えよ*
- 「SQL2を実行してエリアごとの在席者増減数(**入室した従業員と退室した従業員の差**)を取得する」
- 「入退室区分には **入室('I')又は退室('O')を**設定する」
- **入室ログ**のレコード数から**退室ログ**のレコードを数を引く
- 入室ログは入退室区分='I'
- 退室ログは入退室区分='O'
- **SUM**(CASE WHEN 条件 THEN 1 ELSE 0 END)で条件に合うレコード数を取得
::: note info
a: SUM
b: 入退室区分='I'
c: 入退室区分='O'
:::
- 「SQL4を実行して従業員ごとの最新の入退室ログの**ログID**を取得する」
- 「ログIDは時系列に**昇順**で採番し、」とあるので、ログID最大のものが最新
::: note info
d: MAX(ログID)
:::
# 問2-設問1-(2)
*デッドロックを引き起こすロックの状況について、時系列に、対象となるテーブルのロック名、ロックをかけるトランザクション、ロック種別、ロック状態を答えて、次に示す表2を完成させよ*

- 入退室ログ登録処理のロック順序

|図2|テーブル|ロック|
|---|---|---|
|①|入退室ログ|専有ロック|
|②|**所在情報**|共有ロック|
|③|**エリア状況**|共有ロック|

- 所在情報更新処理のロック順序　

|表1|テーブル|ロック|
|---|---|---|
|SQL1|入退室ログ|共有ロック|
|SQL2|入退室ログ|共有ロック|
|SQL3|**エリア状況**|専有ロック|
|SQL4|入退室ログ|共有ロック|
|SQL5|入退室ログ|共有ロック|
|SQL6|**所在情報**|専有ロック|

- 「図2入退室ログ登録処理の概要」では、所在情報→エリア状況の順に共有ロックをかけるのに対して、「表1所在情報更新処理の概要」では、エリア状況→所在情報の順に占有ロックをかけている
- この２つの処理では逆順にロックをかけているので、いずれかに他方が割り込むとデッドロックが発生する
- 既に時系列1に「エリア状況」が埋められているので、表1エリア状況(占有)を起点に図2の処理が割り込んでデッドロックするケースを解答する
::: note info
|時系列|テーブル名|ロックを掛けるトランザクション|ロック種別|ロック状態|
|---|---|---|---|---|
|1|エリア状況|**所在情報更新**|**専有ロック**|**ロック済み**|
|2|**所在情報**|**入退室ログ登録**|**共有ロック**|**ロック済み**|
|3|**エリア状況**|**入退室ログ登録**|**共有ロック**|**ロック解放待ち**|
|4|**所在情報**|**所在情報更新**|**専有ロック**|**ロック解放待ち**|
:::
# 問2-設問1-(3)
*変更内容を、図2中の①～③を用いて20字以内で答えよ*
- この２つの処理においてロックをかける順序をそろえればよいので、図2でロックをかける順序を表1と同じにする
- ②と③を入れ換えれば、エリア状況→所在情報の順になる
::: note info
②と③の順序を入れ換える
:::
# 問2-設問2-(1)
*図5中のe～hに入れる適切な字句を答えよ*
- 「処理対象の従業員番号の範囲を、HBEGINIDとHENDIDに設定する」より、従業員番号はHBEGINID～HENDID
- 「結果行が無い場合、①のHBEGINIDをHTARGETIDに設定し」「結果行がある場合、その再開従業員番号を、HTARGETIDに設定する」
- HTARGETIDは、読み込む際の最初の従業員番号と読み取れる
::: note info
e: HTARGETID 
f: HENDID 
:::
- 「監査未チェックの行はNULLを設定し」
- 監査未チェックの行に対して監査機能の処理を行う
::: note info
g: IS NULL
:::
- 「再実行時には記録していた**従業員番号**から処理を再開する」
- 図5「カーソルで読む行がある限り⑤～⑩の処理を繰り返し実行」
- **カーソルの並び順**で処理がすすむので、カーソルが従業員番号順に並んでいないと、処理されない従業員番号や繰り返し処理される従業員番号が発生することになる
- 「ORDER BY **h**, 年月日」によって**h**と年月日の順に並び替えられる
::: note info
h: 従業員番号
:::
# 問2-設問2-(2)
*図5中のアで行うべき処理を40字以内で答えよ*
- 「ジョブIDは**再利用**する」
- 「結果行がある場合、その**再開従業員番号**をHTARGETIDに設定する」
- ジョブIDを再利用する際に**古いジョブのレコードがそのまま残っていると**、間違った再開従業員番号を使ってカーソルを開いてしまう
::: note info
ア: 再開位置テーブルから当該ジョブIDのレコードを削除する
:::
# 問2-設問2-(3)
*AとBの対象となる処理に該当するものを、図5中の④～⑬から選べ*
- 「カーソルで読む行がある限り**⑤～⑩の処理を繰り返し実行**し、行がなくなれば⑪に進む。なお、処理行数をカウントし、**⑩の処理はN行おき**にだけ実行する」
- 次に⑩のコミットを行うまでの間に⑤～⑨をN回と⑩を1回行うので、タイムアウト時間はそれ以上で設定しなければならない
``` math
\begin{align}
T&>(⑤+⑥+⑦+⑧+⑨)×N+⑩\\
より\\
N&<(T-⑩)÷(⑤+⑥+⑦+⑧+⑨)\\
\end{align}
```
::: note info
A: ⑩
B: ⑤ ⑥ ⑦ ⑧ ⑨
:::
# 問3-設問1-(1)
*本文中のa～eに入れる適切な字句を答えよ*
## a
*組織テーブルを定義する図2中から、[a]を外部キーとする指定を削除したCREATE TABLE 文を実行する*
- 「他の表が未定義の状態で"組織"テーブルを定義」するので、**他の表の列を参照するとエラー**になる
- 図2中において外部キーに指定されているのは、「**所属長従業員コード**」と「**上位組織コード**」
- 「**所属長従業員コード**」の参照先は従業員テーブルなので**他の表**
- 「**上位組織コード**」の参照先は**同じ組織テーブル**
::: note info
a: 所属長従業員コード
:::
## bcd
*従業員、役職、時間単価、ランクの各テーブルを定義するCREATE TABLE 文を[b]、[c]、役職、[d]の順番で実行する*
- 図1より、従業員、役職、時間単価、ランクが外部参照するキーは以下の通り

|テーブル名|参照キー|参照先テーブル|
|-|-|-|
|従業員|組織コード|組織|
|従業員|役職コード|役職|
|役職|ランクコード|ランク|
|時間単価|ランクコード|ランク|
|時間単価|組織コード|組織|
|ランクコード|なし|なし|
- **従業員**を定義する前に**役職**を定義しておく
- **役職**を定義する前に**ランク**を定義しておく
- **時間単価**を定義する前に**ランク**を定義しておく
- この条件を満足する定義順は
**ランク**→**時間単価**→役職→**従業員**
::: note info
b: ランク
c: 時間単価
d: 従業員
:::
## e
*組織テーブルに対する[e]文を用いて、[f]を外部キーとする指定の定義を追加する*
- ALTER TABLE テーブル名 ADD カラム でテーブルにカラムを挿入する
::: note info
e: ALTER TABLE 
:::
# 問3-設問1-(2)
*外部キーである組織長従業員コード及び上位組織コードのそれぞれについて、考慮すべき事項を一つずつ、25字以内で答えよ*
## 組織長従業員コード
- 組織長従業員コードは、従業員テーブルに存在するかNULLでないと参照エラーになる
- 「組織テーブルにINSERT文を用いて行を挿入した。次いで、従業員、役職、時間単価、ランクの各テーブルに対してもINSERT文を用いて行を挿入した。その後、UPDATE文で適宜列値を更新した」
```mermaid
graph TB
  組織に行挿入-->従業員に行挿入に
  従業員に行挿入-->従業員コードを更新
```
- 組織に行挿入するときには従業員コードは存在しないので、組織長従業員コードはNULLにしないとエラーになる
::: note info
組織長従業員コード: 行挿入の際にNULLを設定する
:::
## 上位組織コード
- 上位組織コードは、既に組織テーブルに存在するかNULLでないと参照エラーになる
- 上位組織から順に行挿入すれば、上位組織コードが紐付かなくなることはない
::: note info
上位組織コード: 上位組織から順に行挿入する
:::
# 問3-設問1-(3)
*なぜ制約に制約に違反するのか、理由をそれぞれ45字以内で具体的に答えよ*
## SET NULL 
- 組織コードは時間単価テーブルの主キーの一部
- 組織コードが削除されると主キーの一部がNULLに書き換えられる
::: note info
SET NULL: 時間単価テーブルの主キーの一部がNULLに書き換えられるから
:::
## RESTRICT
- 時間単価テーブルに存在する主キーと同じ組織コードが削除されるとエラーになる
::: note info
RESTRICT: 時間単価テーブルに削除された組織コードが存在する場合があるから
:::
# 問3-設問2
*表4中のSQL1のア～エに入れる適切な字句を答えよ*
## ア、イ
- 「**組織**ごとに**計画時間**の少ない順に順序付けし、順序に沿って従業員数が均等となるように1～3の番号を付与した時間階級」
::: note info
ア: 組織
イ: 計画時間
:::
## ウ、エ
- 「稼働計画にない従業員の計画時間はゼロで表示」より、全ての**従業員**に対して、**稼働計画**をLEFT OUTER JOINした結果を取得する
::: note info
ウ: 従業員
エ: 稼働計画
:::
# 問3-設問3-(1)
*表3の稼働実績テーブルの従業員コードの列値個数が表2の従業員テーブルの従業員コードの列値個数より少ないのはなぜか。本文中の用語を用いて、30字以内で答えよ*
- 「従業員は複数のPJに参加することがあり、**PJに参加していない従業員も一部いる**」
- 参加していない従業員は稼働実績テーブルに含まれない
::: note info
PJに参加していない従業員も一部いるから
:::
# 問3-設問3-(2)
*本文中のfに入れる適切な数値を答えよ*
- 「**低クラスタ**な索引は、キー値の順番と、キーが指す行の物理的な並び順が一致している割合が低く、行へのアクセスが**ランダム**になる」
- 最悪の場合、ヒット率が0で行数分ページングする
::: note info
22
:::
# 問3-設問3-(3)
*本文中の用語を用いて、30字以内で答えよ*
- 「従業員は、同じ日に複数PJの稼働時間を入力できる」ので、一人が同じ稼働年月日のデータを複数もつことがある
::: note info
従業員は、同じ日に複数PJの稼働時間を入力できるから
:::
# 問3-設問3-(4)
*本文中のg～kに入れる適切な数値を答えよ*
## g
- 「SQL2:指定した組織に所属する従業員の指定した月の**1か月分**の稼働時間の合計を調べる」
- 「SQL2のアクセス経路として、従業員テーブルを外表、**稼働実績**テーブルを**内表**とする入れ子ループ結合を想定する」
- 「内表の副次索引1を用いて①従業員１人当たりの稼働実績である**1,200行**を読み込み、行データの稼働年月日に対してBETWEEN述語を評価する。表3の稼働年月日の列値個数は1,000(**50か月分**)なので」
- １人当たり稼働実績1,200は50か月分なので、1か月分は
``` math
1200÷50=24
```
::: note info
g: 24
:::
## h
- 表3より、稼働実績を計上している従業員は、8,000人
- 表2より組織数は400
- 組織当たりに換算すると
``` math
\frac{8000}{400}=20
```
::: note info
h: 20
:::
## i
``` math
１人当たりg:24行×組織当たりh:20人=480
```
::: note info
i: 480
:::
## j
- 「従業員当たり1か月分の行が高々**2ページ**に格納されることが分かった」
- 「表3の稼働年月日列の列値個数は1,000(**50か月分**)なので、」
- 「稼働実績を計上している従業員は組織当たり**20人**なので、」
``` math
2[ページ/月人]×50[月]×20[人]=2000ページ
```
::: note info
j: 2000
:::
## k
- 「表3の稼働年月日列の列値個数は1,000(**50か月分**)なので、」

::: note info
j: 50
:::