データベーススペシャリスト試験 2016年 午後1 問03

模範解答

解説

解答の論理構成

1. ロール命名規則
   【問題文】「(1) 部門番号をロール名として,ロールを定義する。」
   → 部門番号そのものがロール名。
2. ビューと部門番号の対応
   • “営業１課ビュー” 定義：
     【表1 SQL1】WHERE 担当部門番号 = 'B11'
     → (a) はロール「B11」。
   • “営業２課ビュー” 定義（SQL2 は伏せ字だが, 営業2課であることは文脈より明白）。部門番号は図1より「B12」。
     → (b) はロール「B12」。
3. 上位ロールへの付与
   【表2】GRANT ROLE (a), (b) TO (c) ;
   営業1課(B11), 営業2課(B12) のロールをまとめて付与されるのは営業部。図1で営業部の部門番号は「B10」。
   → (c) はロール「B10」。

誤りやすいポイント

• ビュー名だけを見て “営業１課ビュー＝ロールB10?” と混同する。ビューは課単位, ロールB10は部全体です。
• 「GRANT SELECT ON … TO (a)」の (a) をユーザIDと誤読し, E113 など社員番号を入れてしまう。ここはロールです。
• GRANT ROLE … TO … の文法で, 右側が下位ロールと早合点し逆に配置する。右側は付与先（上位）です。

FAQ

模範解答

解説

解答の論理構成

1. ロールの作成
   「CREATE ROLE B10 ;」「CREATE ROLE B11 ;」「CREATE ROLE B12 ;」はロールの定義そのものなので最優先。
   よって (オ)，(カ)，(キ) を最初に置く。
2. ロールをユーザへ付与
   ロールが存在した後でなければ付与できないため，
   「GRANT ROLE B10 TO E111 ;」「GRANT ROLE B11 TO E112, E113, E114, E115 ;」
   「GRANT ROLE B12 TO E116, E117, E118, E119 ;」を続ける。これが (イ)，(ウ)，(エ)。
3. ロール階層（部長ロールが課ロールを包含）
   「GRANT ROLE (a), (b) TO (c) ;」は【問題文】「② 部門長は、部下がアクセスできる全ての情報にアクセスできる。」を実現するために，
   子ロール「B11」「B12」を親ロール「B10」に付与する操作。(ア) は両ロールが既に作成・付与済みである必要がある。
4. ビューへの権限付与
   最後に「GRANT SELECT ON 営業１課ビュー TO (a) ;」「GRANT SELECT ON 営業２課ビュー TO (b) ;」でビューの参照権限をロールに付与する。(ク)，(ケ)。
   これで個人情報を格納するビューに直接アクセスさせつつ，テーブルを隠蔽する【問題文】③を満たす。

誤りやすいポイント

• (ア) をロール作成前に置いてしまい，存在しないロールに対して GRANT してエラーになる。
• 「CREATE ROLE」と「GRANT ROLE TO USER」の順序を混同しがち。作成前に付与は不可。
• ビューへの GRANT をロール付与より先に書くと「権限は付与できても結局誰もそのロールを持っていない」状態になる。

FAQ

模範解答

解説

解答の論理構成

1. 抽出条件の再確認
   “社員が過去に登録した訪問予定のうち, その社員が予定日に訪問しなかった顧客” を探す―これは予定行は必須、実績行は存在しない（HJ.訪問実施日 IS NULL）状況です。
2. 顧客 K と 訪問予定 HY の結合
   予定に登場しない顧客は対象外なので、行の組み合わせは 1 対 1 もしくは 1 対 多ですが「予定を持つ顧客だけ欲しい」。したがって外部結合は不要で内部結合を行います。
   引用：【問題文】「顧客 K…訪問予定 HY…K.顧客番号 = HY.顧客番号」
   → (d) に INNER JOIN を設定。
3. 訪問予定 HY と 訪問実績 HJ の結合
   実績が無い行を残したいので外部結合が必要です。外側にするのは予定表側で、実績表を NULL で保持します。
   引用：【問題文】「WHERE HJ.訪問実施日 IS NULL」
   → (e) に LEFT OUTER JOIN を設定。
4. 句の選択肢
   SQL 標準では INNER／LEFT OUTER を省略して JOIN／LEFT JOIN と書けるため、両者とも正答扱いになります。

誤りやすいポイント

• “NULL を取るから RIGHT JOIN だ” と逆に考えてしまう
  必要なのは予定の行を基準に実績の有無を確認する LEFT JOIN です。
• すべて外部結合にしてしまい結果行が増える
  顧客－予定間を外部結合にすると、予定を持たない顧客まで対象になり誤答になります。
• WHERE 句で IS NULL を付けた後に内部結合に変換してしまう
  内部結合では NULL 行が消えるため目的を達成できません。

FAQ

模範解答

SELECT S.社員番号, S.社員名, COUNT(*) 訪問回数
  FROM 社員 S INNER JOIN 訪問実績 HJ ON S.社員番号 = HJ.社員番号
  WHERE HJ.訪問実施日 >= ISODATE('5416-01-01')
  GROUP BY S.社員番号, S.社員名
  HAVING COUNT(*) > ?

解説

解答の論理構成

1. 【問題文】では SQL4 をビュー化するとして「そのままビューの定義に指定できない箇所がある」と述べています。
2. SQL4 の可変要素は HAVING COUNT(*) >= ?（模範解答では HAVING COUNT(*) > ?）だけです。
3. ビューは「スキーマ定義」の一種であり、RDBMS は CREATE VIEW 時点で全文をパース・最適化します。? のような実行時バインド変数はこの段階では不定であり、「不定値を含む式」は構文エラーになります。
4. したがってビューに残せない箇所は HAVING COUNT(*) > ? となり、【模範解答】でも二重線で取り消されています。
5. ビュー化する場合は

   CREATE VIEW 社員別訪問回数ビュー AS
     SELECT S.社員番号, S.社員名, COUNT(*) 訪問回数
       FROM 社員 S INNER JOIN 訪問実績 HJ
         ON S.社員番号 = HJ.社員番号
      WHERE HJ.訪問実施日 >= ISODATE('5416-01-01')
      GROUP BY S.社員番号, S.社員名 ;

   としておき、呼び出し側で

   SELECT * FROM 社員別訪問回数ビュー
    WHERE 訪問回数 >= ? ;

   のように条件を移すのが典型的な実装パターンです。

誤りやすいポイント

• 「ビューでもプレースホルダは使えるはず」と思い込む。ビューはプリコンパイルされるため NG です。
• HAVING 句そのものが禁止と誤解し、集計列を SELECT から外してしまう。
• GROUP BY を残さずに削除箇所を誤る。GROUP BY は集計ビューの本質なので必要です。

FAQ

模範解答

解説

解答の論理構成

1. 出力列を決める
   要件より【問題文】「社員番号, 社員名,訪問回数を出力」→SELECT 句に3列すべてを書く必要がある。表3の SQL5 に下線が2か所あるのは SELECT 句が欠けているため。
2. 抽出条件を決める
   表3の SQL4 は
   HAVING COUNT() >= ?
   となっており、これをビュー使用後も引き継ぐ必要がある。ビュー側で COUNT() にエイリアス「訪問回数」を付けているので、SQL5 では
   WHERE 訪問回数 >= ?
   と書けば同じ意味になる。
3. GROUP BY／HAVING が不要な理由
   ビューで集計済み（社員別訪問回数ビュー：社員番号, 社員名, 訪問回数）なので、呼び出し側は通常の SELECT と WHERE だけで済む。

誤りやすいポイント

• HAVING をそのまま書いてしまう
  ビュー側で集計済みなので HAVING では構文エラーになる。
• COUNT(*) を直接参照する
  ビューでは列名が「訪問回数」になっている。
• 動的パラメタ「?」を書き忘れる／別名に置き換える
  要件に「? に設定される」と明示されている。

FAQ

模範解答

解説

解答の論理構成

1. 主キー候補の抽出
   • “営業課内で一意なチーム番号” → “チーム番号” は “部門番号” と対で初めて全社的に一意。
   • “営業課内のチームの社員ごとに, 担当開始日及び担当終了日を設定” → 同じ社員が同じチームに再参加するケース (要件(d)) を許容するため，開始日を主キーに含める必要がある。
2. 列の確定
   【問題文】引用
   “(b) 営業課内のチームの社員ごとに, 担当開始日及び担当終了日を設定した行を登録する。”
   → 期間属性は “担当開始日”, “担当終了日”。
   “(e) 過去にどの社員がどのチームのメンバだったかを調べることができる。”
   → 履歴保持用に終了日を持たせる。
3. 主キー決定
   以上より，主キーは (部門番号, チーム番号, 社員番号, 担当開始日)。
   終了日は履歴検索用の通常列。

誤りやすいポイント

• “担当終了日” を主キーに含める誤り
  → NULL になり得る列は主キーにできません。
• “部門番号” を省く誤り
  → “営業課内で一意” の文言に注意。全社での一意性を保証するには部門番号が必要です。
• 開始日と終了日を 1 行で管理せず履歴を上書きする設計
  → 要件(e) の履歴参照が不可能になります。

FAQ

模範解答

解説

解答の論理構成

1. 権限の付与状況
   • 【問題文】表2 の「GRANT ROLE B10 TO E111」「GRANT ROLE B11 TO E112」「GRANT ROLE B12 TO E116」より、部長・課長は各営業課ビューへの SELECT 権限を保有します。
2. SQL6 のフィルタ条件
   • SQL6 の WHERE T.社員番号 = CURRENT_USER と日付条件により、チームメンバ表に “自分自身” が行として存在しなければ抽出されません。
3. 誰が登録されていないか
   • チームは【問題文】「(4) 課長は、部下社員から成る少人数の営業チーム…」で編成され、部長・課長自身がメンバになるとは書かれていません。
   • よって “チームメンバ” に行がない「E111」「E112」「E116」は SQL6 でヒットせず、期待結果を取得できません。
4. 必要な操作
   • 解決策は部長・課長をチームメンバ表へ登録すること。これにより CURRENT_USER 条件を満たしアクセスが可能になります。

誤りやすいポイント

• 「ロールを持っていれば SQL6 でも必ず見える」と思い込む。ビュー側で更に条件が課されている点を見落としやすいです。
• CURRENT_USER を“社員番号”ではなく“ユーザID”と誤解して別物と考えてしまう。問題設定では社員番号＝ユーザIDです。
• 担当終了日が NULL の場合の OR 条件を読む際に “過去の行まで見える” と誤解し、現時点の行のみ抽出されることを忘れる。

FAQ

模範解答

解説

解答の論理構成

1. セキュリティ要件④は「アクセスする必要がなくなった社員については,そのことを反映するためのアクセス制限を直ちに実施する。」
2. 対応案Ａ（ロールをチーム別に再設計）では、【問題文】「ロールの付与・剥奪については,課長が 1営業日前までに…DBA …実施」とあり、即時制限ができない。
3. 対応案Ｂは
   • 「新たに “チームメンバ” テーブルを追加」
   • 「担当終了日列を、いつでも変更することができる。」
   • ビュー SQL6 で WHERE T.担当終了日 >= CURRENT_DATE OR T.担当終了日 IS NULL と判定。
4. したがって課長は自席で担当終了日を当日に更新すれば即座にビューの結果から個人情報が除外される。
5. DBA への申請・反映待ちが不要になる分、迅速性と運用負荷の両面で優れている。

誤りやすいポイント

• 「担当終了日を NULL→日付」に更新するだけで良い点を見落とし、チームメンバ行の削除や INSERT を想定してしまう。
• ロール剥奪のリードタイムを“１日程度”と曖昧に書き、【問題文】の「1営業日前まで」の正確な引用を欠く。
• DBA という第三者操作が無くなるメリットではなく、チーム番号追加の可読性など別観点で答えてしまう。

FAQ