- 問1
- 問2
- 問3
公式サイトの問題冊子はこちら。
https://www.ipa.go.jp/shiken/mondai-kaiotu/gmcbt8000000fzx1-att/2017h29h_db_pm1_qs.pdf
問1-設問1-(1)
関係"電子会議投稿"の候補キーを全て答えよ
- 図4「電子会議投稿(電子会議番号、議題、分野番号、分野名、表示順、作成者ユーザID、投稿番号、投稿本文、投稿者ユーザID)
| 属性名 | 意味・制約 |
|---|---|
| 電子会議番号 | 電子会議を一意に識別する番号」 |
| 投稿番号 | 電子会議番号との組合せで投稿を一意に識別する番号 |
- 電子会議投稿は、電子会議番号と投稿番号で一意に識別
- 「電子会議は、いずれか一つの分野に属し、分野ごとに定められた表示順に従って一覧表示される」
- 電子会議は分野と表示順によっても電子会議を一意に識別する
候補キー: {電子会議番号、投稿番号} {分野番号、表示順、投稿番号}
| 属性名 | 意味・制約 |
|---|---|
| 分野番号 | 分野を一意に識別する番号」 |
| 分野名 | 分野の名称 |
| 議題 | 電子会議のタイトル |
| 作成者ユーザID | 電子会議を作成したユーザのユーザID |
- 「分野とは、電子会議を分類する単位である。」
- 分野番号は電子会議番号に部分従属
- 分野名は分野番号に従属
- 議題は電子会議番号に部分従属
- 作成者ユーザIDは電子会議番号に部分従属
| 関数従属性 | 回答 |
|---|---|
| 部分関数従属性 | 電子会議番号→議題、電子会議番号→作成者ユーザID 等」 |
| 推移的関数従属性 | 電子会議番号→分野番号→分野名 |
問1-設問1-(2)
関係"電子会議投稿"は、第1正規形、第2正規形、第3正規形のうち、どこまで正規化されているか答えよ
- 列の繰り返しがない→第1正規形以上
- 部分関数従属性あり→第2正規形未満
- 推移的関数従属性あり→第3正規形未満
第1正規形
第3正規形に分解し、主キー及び外部キーを明記した関係スキーマを示せ
| 従属先 | 属性 |
|---|---|
| 電子会議番号に従属 | 議題、分野番号、表示順、作成者ユーザID |
| 分野番号に従属 | 分野名 |
| 電子会議番号と投稿番号に従属 | 投稿本文、投稿者ユーザID |
- 電子会議(電子会議番号、議題、分野番号(FK)、表示順、作成者ユーザID(FK))
- 投稿(投稿番号、投稿本文、投稿者ユーザID(FK))
- 分野(分野番号、分野名)
問1-設問2-(1)
図4中のa～fに入れる適切な属性名を答えよ
a
- 「ユーザは、一つのグループに必ず所属し、これを主務グループと呼ぶ」
a: 主務グループID(FK)
b
- 「設備には、必要に応じて、当該設備の管理を行うグループを一つ定めることができる」
b: 管理グループID(FK)
c
- 「ユーザは、1人又は複数のユーザにメッセージを送信することができる」
- 図3/4をふまえると、メッセージとメッセージ送信先は1対多
- メッセージには送信元のユーザが必要
c: 送信元ユーザID(FK)
d、e
- メッセージ送信先には送信先のユーザが必要
- 「送信先のユーザがメッセージを開封すると、開封日時が記録される」
d: 送信先ユーザID(FK)
e: 開封日時
f
- 「スケジュールを予約されたユーザは、そのスケジュールに参加するか否かを回答することができる」
f: 参加可否
問1-設問2-(2)
図3のエンティティタイプ間のリレーションシップを全て記入せよ
- 「ユーザは、1人又は複数のユーザにメッセージを送信することができる」
- 図3/4をふまえると、メッセージとメッセージ送信先は1対多
メッセージ●-->●メッセージ送信先
- 図4より、設備予約先は、予約と設備それぞれと1対多のリレーションシップをもつ
設備予約先●-->●設備
- 図4より、予約先ユーザIDは予約の外部キーなので、ユーザと予約との間に1対多のリレーションシップをもつ
- 予約の外部キーに該当しないユーザが存在しうる
ユーザ●-->◯予約
- 図4より、グループには上位グループIDを外部キーにもつので、1対多の再帰リレーションシップをもつ
- 上位グループを持たないグループが存在しうる
グループ◯-->◯グループ
- 「設備には、必要に応じて、当該設備の管理を行うグループを一つ定めることができる」
グループ◯-->◯設備
- 「ユーザは、1人又は複数のユーザにメッセージを送信することができる」
- メッセージを送信しないユーザが存在しうる
ユーザ●-->◯メッセージ
問1-設問3-(1)
新たに追加する関係の主キー及び外部キーを明記した関係スキーマ、属性を追加する関係名及び追加する属性名を答えよ
- 指摘事項①「ロールを管理するデータ構造が設計されていないので、ロールを用いて承認者を指定することができない」
- 「ロールは、ロールIDで一意に識別し、ロール名をもつ」
- 「また、ユーザには、必要に応じて一つ又は複数のロールを付与でき、一つのロールを複数のユーザに付与することもできる」
- ロールとユーザは多対多のリレーションシップをもつので、連関エンティティ:ユーザロールが必要
- 「(決済ルートの)ステップには、承認可能なユーザ、グループ又はロールを指定する」
新たに追加する関係:
- ロール(ロールID、ロール名)
- ユーザロール(ユーザID、ロールID)
属性を追加する関係: 決済ルート
追加する属性名: 承認ロールID
問1-設問3-(2)
指摘事項②の不具合はどのようなときに発生するか。その状況を、具体的に40字以内で述べよ
- 図1より、ステップ1が承認ならステップ2に行く
- 図1より、ステップ2が差戻しならステップ1に行く
- 二つの条件が揃うと、ステップ1と2を無限に繰り返してしまう
承認者Aが承認した後、承認者Bが差戻ししたとき
修正後の関係の主キー及び外部キーを明記した関係スキーマを答えよ
- 修正前の主キー{申請ひな形番号、申請連番、ステップ番号}
- 表1「承認連番:申請ひな形番号ごと・申請連番ごとに1から始まり、承認処理を行うごとに1ずつ加算される番号」
- ステップ番号の代わりに承認連番を主キーの一部にした上で、小さい承認連番に遷移しないようにすることで同一ステップ番号への遷移にも対応できる
承認(申請ひな形番号、申請連番、ステップ番号(FK)、承認連番、承認処理結果、コメント、承認者ユーザID(FK)、承認日時)
問2-設問1
表2中のa～eに入れる適切な字句を答えよ
a,c
- 「SQL1は、各部品の出庫年月日ごとの出庫数量を集計する」
a: SUM(S.出庫数量)
c: GROUP BY B.部品番号、S.出庫年月日
b
- 「また、SQL1では、出庫が全くない部品も集計対象とする」なので、出庫をLEFT OUTER JOINで結合する
b: LEFT OUTER JOIN
d,e
- 「SQL2は、各部品の倉庫間の出庫について出庫年月日ごとの出庫数量を集計する」
- 「定期便は、倉庫間で部品を配送する便であり、便番号で一意に識別される」
- 出庫便番号がNULLでないなら、倉庫間出庫
d: 出庫便番号
e: NOT NULL
b
- 「また、SQL1では、出庫が全くない部品も集計対象とする」なので、出庫をLEFT OUTER JOINで結合する
b: LEFT OUTER JOIN
問2-設問2-(1)
どのような場合にデッドロックが発生するか、AP間のSQLの実行状況を、図2中の丸数字を用いて60字以内で述べよ
| 丸数字 | 在庫 | 出庫 |
|---|---|---|
| ① | 共有ロック | - |
| ② | 占有ロック | - |
| ③ | 占有ロック | 占有ロック |
| ④ | - | - |
- ２つのトランザクションで共有ロックをかけている状態で、どちらかが占有ロックを掛けようとするとデッドロックする
①と②の間に別のトランザクションが①を実施した場合
問2-設問2-(2)
不正になるのは、AP2の①～④の各SQLが、t2,t4,t6,t8のどの時間帯で実行された場合か、該当する時間帯に①～④を記入せよ
- 更新前の在庫数量を他方のトランザクションが参照すると不正になる
- ①と②の間に別のトランザクションが①を実施すると不正になる
- ②～④の間は、別のトランザクションが②を実施できない
| t1 | t2 | t3 | t4 | t5 | t6 | t7 | t8 | |
|---|---|---|---|---|---|---|---|---|
| AP1 | ① | - | ② | - | ③ | - | ④ | - |
| AP2 | - | ① | - | - | - | ②③④ |
また、この状況が発生した場合の、在庫数量が不正とは具体的にどのような状態か、30字で述べよ
- 図2「hv4-hv5とhv3を比較し、出庫が可能な場合だけ以降を実施する
②UPDATE 在庫 SET 出庫対象在庫数量 = 出庫対象在庫数量 + :hv3」 - hv3: 出庫数量
- hv4: 倉庫内在庫数量
- hv5: 出庫対象在庫数量
- hv4-hv5が出庫数量ギリギリの場合にこの状況になると、t1の出庫数量がt2の出庫対象在庫数量に加算されなくなる
t1の出庫数量が出庫対象在庫数量に加算されなくなる
問2-設問2-(3)
図3中のfに入れる適切な字句を答えよ
- CURSORの中で、現在値をUPDATEするので、WHERE CURRENT OF を使う
f: CURRENT
問2-設問3-(1)
ア～エに入れる適切な字句を答えよ
- 在庫の主キーは{倉庫コード、部品番号}
- 違う出庫の間で、在庫が同じ場合が想定され、その場合はロック解放を待つことになる
ア: 倉庫コード
イ: 部品番号
ウ: 在庫
エ: ロック解放待ち
問2-設問3-(2)
オ～ケに入れる適切な字句を答えよ
- 「出庫は多頻度で行われるので、出庫ごとに在庫は更新されず、出庫確定APでまとめて更新される」
- 「出庫確定APは、毎日の業務終了時に実行される」
- 毎日一回だけ実行されるので、同じ出庫年月日であれば、{出庫元倉庫コード、部品番号}は重複しない
- 在庫への索引は参照元の出庫に設定する
- 索引が無い場合は、全文検索になる
- 占有ロックがかかると、別のトランザクションからの参照が止まる(共有ロックなら参照は可能)
オ: 出庫元倉庫コード
カ: 部品番号
キ: 出庫
ク: 全文検索
ケ: 占有ロック
問3-設問1-(1)
ア～コに入れる適切な字句を答えよ
ア、イ
- 「月別売上テーブルには、行が主索引のキー順にロードされている。その全行をアンロードしたファイルを、月別売上Bテーブルの構造に従って変換し、月別売上Bテーブルに主索引のキー順にロードした」
- 月別売上、月別売上Bともに主索引のキー順に並んでいる
- 表1より、主索引のほかに副次索引があることが読み取れる
- アクセス経路が主索引の場合はページを順次に読み込むが、副次索引の場合はそうならない
ア: 主
イ: 副次
ウ
- 60か月=5年
\frac{360000000行}{5年×200店舗}=360000行
ウ: 360000
エ
- 「分析処理1 指定した1店舗について、任意の1年間の売上データを分析する」
- 主索引を使うので、
\frac{360000行}{100行/ページ}=3600行
エ: 3600
オ
- 月別売上は月毎、月別売上Bは年毎のレコードなので12分の1
\frac{360000000行}{12か月×5年×200店舗}=30000行
オ: 30000
カ
- 主索引を使うので
\frac{30000行}{30行/ページ}=1000行
カ: 1000
キ
- 年を跨ぐともう一年分の読み込みが必要
キ: 2
ク: 2000
ケ
- 「分析処理2 指定した1商品について、任意の月の売上データを分析する」
\frac{360000000行}{30000商品×60か月}=200行
ケ: 200
コ
- 売上年月と商品コードに条件を設定するので、副次索引を使用
- 「副次索引のとき、1行あたり1ページがランダムに読み込まれる」
コ: 200
問3-設問1-(2)
表2中のa、bに入れる適切な字句を答えよ
- 「2017年1月と2017年2月の売上の差を求めるSQL」
- 「テーブル構造を変更した後で、SQL1と同じ結果行を得るために、実行の都度、比較する年月に対応したSQLの構文を組み立て、動的SQLで実行することにした」
- 月別売上Bには売上額1月と売上額2月がある
- 売上年は2017
a: 売上額2月-売上額1月
b: 売上年='2017'
問3-設問1-(3)
Fさんは、なぜ表2中のSQL2を動的SQLで実行することにしたのか。その理由を40字以内で述べよ
- 表2中のSQL2だと、2017年1月と2月の比較しか出来ないが、「実行の都度、比較する年月に対応したSQLの構文を組み立て、動的SQLで実行することにした」とある
- 売上額1月や売上額2月を売上額6月や売上額7月等に差し替える必要がある
比較する年月に対応した属性名を選択する必要があるから
問3-設問2-(1)
社員連絡先Bテーブルの電話番号列にNOT NULL制約を定義した理由を、本文中の字句を用いて25字以内で述べよ
- 「担当者は店舗の社員に電話をかけて販売状況を問い合わせることがある」
- 社員の電話番号は必須なので、電話番号の無い社員の情報は不要
電話番号の無い社員のレコードは不要だから
問3-設問2-(2)
社員連絡先Bテーブルの一意性制約に定義すべき列名又は列名の組合せを答えよ
- 「電話番号1列と電話番号2列は、異なる電話番号であるべきところ、同じ電話番号が設定されている行があった」とあるので、同一社員の電話番号重複を回避する必要がある
社員ID、電話番号
問3-設問2-(3)
図3中のc～eに入れる適切な述語を、①～④の中からそれぞれ重複なく一つずつ選んで答えよ
- 電話番号1はいかなる場合もNOT NULL
- 電話番号2はいかなる場合も電話番号1と不一致
- NOT NULLの場合は不等号で比較可能だか、NULL の場合は無効になるので不等号での比較を行わないようにする
c: ①
d: ③
e: ④
問3-設問2-(4)
図4中のf～hに入れる適切な述語を、(3)に倣って①～④の中からそれぞれ重複なく一つずつ選んで答えよ
- 最初のSELECTでは、電話番号1がNOT NULLのもの
- 次のUNION SELECTでは、電話番号1、電話番号2ともにNOT NULLかつは不一致の電話番号2
- 最後のUNION SELECTでは、電話番号1がNULLかつ電話番号2がNOT NULL
f: ①
g: ②
h: ③
問3-設問2-(5)
表4に記入されている1行目の例に倣って、全ての結果行を埋めよ
- 図4より次のレコードが追加される
①電話番号1がNOT NULLのもの
②電話番号1、電話番号2ともにNOT NULLかつは不一致の電話番号2
③電話番号1がNULLかつ電話番号2がNOT NULL
| 社員ID | 表示順 | 電話番号 |
|---|---|---|
| E1 | 1 | 1111 |
| E2 | 1 | 2222 |
| E3 | 1 | 3333 |
| E1 | 2 | 3333 |
| E4 | 1 | 4444 |