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

解答の論理構成

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

誤りやすいポイント

• ビュー名だけを見て “営業1課ビュー＝ロール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 営業1課ビュー TO (a) ;」「GRANT SELECT ON 営業2課ビュー 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('2016-01-01')
  GROUP BY S.社員番号, S.社員名
  HAVING COUNT(*) >= ?

解答の導き方

1. 「? は実行時に与えられる動的パラメタである」と明示されているので、ここに入る値はビュー定義時に確定しないことが分かります。
2. ビューは CREATE VIEW 文で永続的に定義される「静的な」SELECT文であり、定義内にプレースホルダ（パラメタマーカー）を含めることはできません。プレースホルダは実行時に値をバインドする仕組みであり、ビュー定義に混在させることは構文上／意味上許されません。
3. SQL4の該当箇所は HAVING 節であり、そこに動的パラメタが使われているため、ビュー定義にそのまま入れられません。したがって「HAVING COUNT(*) >= ?」を二重線で消すのが正解です。
4. 実務的な回避策としては、ビュー側では社員ごとの訪問回数を集計しておき（HAVING を使わずに GROUP BY で集計だけを行う）、実行時フィルタ（閾値 N）を外側のクエリで適用します。具体例：

誤りやすいポイント

• HAVING 句自体がビューで使えないと誤解する。HAVING 句はビュー内で使えますが、問題は「HAVING に動的パラメタ '?' を含めている点」です。パラメタ無しの HAVING はビューに含めても問題ありません。
• WHERE 節（例：HJ.訪問実施日 >= ISODATE('2016-01-01')）を消してしまう誤り。ISODATE('2016-01-01') は定数的な式であり、ビュー内にそのまま置けます。
• 演算子の符号（>= と >）を取り違える。問題文の符号（>=）に合わせること。
• 「DBMSによってはプレースホルダがビュー内で使える」と思い込む誤り。試験文脈ではプレースホルダは実行時にバインドするものであり、CREATE VIEW 定義内に直接含めることは想定されていません。
• GROUP BY や COUNT(*) をビューで使えないと誤って消す。集約はビュー内で集計結果を作る目的で普通に使えます。

FAQ

解答の導き方

SELECT 社員番号, 社員名, 訪問回数 FROM 社員別訪問回数ビュー
WHERE 訪問回数 >= ?

1. SQL4の要求内容を確認する
   表3のSQL4を見ると、主要な部分は次のとおりです（要約）。
   「SELECT S.社員番号、 S.社員名、 COUNT() 訪問回数 … WHERE HJ.訪問実施日 >= ISODATE('2016-01-01') … GROUP BY S.社員番号、 S.社員名 HAVING COUNT() >= ?」
   また問題文に「Nは実行時に与えられ、 SQL文の動的パラメタの? に設定される」とあります。これにより、閾値N（?）は実行時に決まる値であり、ビュー定義の段階で固定できないことが分かります。
2. ビューに何を持たせるかを決める
   問題は「社員別訪問回数ビュー(社員番号、社員名、 訪問回数)」を作ることを想定しています。したがって、ビューは社員ごとの訪問回数（COUNT() を訪問回数という列名で）を返すように定義します。SQL4 の「WHERE HJ.訪問実施日 >= ISODATE('2016-01-01')」は固定条件なのでビュー内に含めてよく、集計（GROUP BY）もビュー内で行えます。しかし「HAVING COUNT() >= ?」の部分は「? が実行時に与えられる」ため、ビューに含めるべきではありません（問題文の指示どおり「で指定できないとした箇所を除いてビューを定義する」）。
3. ビュー定義の形（考え方）
   ビューは次のようにして社員ごとの訪問回数を返すようにするのが自然です（要旨）。
   • FROM と JOIN：社員と訪問実績を結合する
   • WHERE：訪問実施日 >= ISODATE('2016-01-01') を適用する
   • GROUP BY：S.社員番号、S.社員名
   • SELECT：COUNT(*) を 訪問回数 として返す
   こうして作ったビューは各社員の訪問回数を列として持つため、外側の SQL（SQL5）ではその列に対して通常の WHERE 句で閾値による絞り込みができます。これは、同一クエリ内で集計結果に対して値で絞る場合は HAVING を使う必要がある一方、集計済みの結果を返すビューに対しては WHERE を使って良い、という SQL の扱いに合致します。
4. SQL5の完成形
   以上より、外側のクエリ（SQL5）はビューの列 訪問回数 に対して実行時パラメタ ? を使って絞り込む形になります。従ってSQL5の空欄に入る字句は次のとおりです。
   SELECT 社員番号, 社員名, 訪問回数 FROM 社員別訪問回数ビュー WHERE 訪問回数 >= ?

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

誤りやすいポイント

• ビューに動的パラメタ「?」を入れようとする誤り。問題文にあるとおり「?」は実行時に与えられるため、ビュー定義には含めないこと。
• 同一の SELECT 文内で集約結果（COUNT(*) の別名）に対して WHERE を使おうとする誤り。これはできないので、その場合は HAVING を使う必要がある。ただし今回の解法は「ビューで集計してから外側で WHERE」を使うので問題ない点を押さえること。
• ビューに「訪問実施日 >= ISODATE('2016-01-01')」を入れ忘れると、集計期間が変わりSQL4と結果が一致しなくなる点に注意すること。
• 外側で HAVING を使おうとする誤り。外側に GROUP BY がないと意味を成さないので、今回は外側では WHERE を使うのが正しい。

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. 対応案A（チーム別ロールへの変更）での運用方法を見ると、問題文に「ロールの付与・剥奪については、課長が1営業日前までにデータベース管理者(以下,DBAという)に依頼する。 DBAは、課長からの依頼に基づいて、ロールの付与・剥奪をRDBMSに対して実施する。」とあります。つまりロールの剥奪は課長の指示→DBAの処理という手順を経るため、最短でも1営業日の遅れが生じます。これでは「直ちに実施する」という要件を満たしにくいです。
3. 対応案Bの設計を確認します。Bでは「チームメンバ」テーブルを追加し、当該テーブルへのアクセス権限（DELETE 権限以外）を課長に与え、さらに「(b) 営業課内のチームの社員ごとに、担当開始日及び担当終了日を設定した行を登録する。……」「(d) 担当開始日列又は担当終了日列を、いつでも変更することができる。」としています。つまり課長がチーム所属データ（行）の追加・更新を自ら行える点が重要です。
4. ビュー側の判定条件（表4の SQL6）を確認すると、WHERE に「T.社員番号 = CURRENT_USER AND T.担当開始日 <= CURRENT_DATE AND (T.担当終了日 >= CURRENT_DATE OR T.担当終了日 IS NULL)」とあります。ビューはこのチームメンバの行が現在のユーザ（CURRENT_USER）と日付条件を満たす場合に個人情報を出力します。したがってチームメンバの該当行が日付条件を満たさなくなれば、その社員はビュー経由で個人情報にアクセスできなくなります。
5. 即時性の具体的方法を整理します。SQL6の条件は「担当終了日 >= CURRENT_DATE」であるため、担当終了日を「当日」に設定しただけでは条件が残り、アクセスは継続します。直ちに除外するには課長が担当終了日をCURRENT_DATEより前の日付（例：前日）に更新して、条件を満たさない状態にすればよいです。Bでは「担当終了日列又は担当開始日列を、いつでも変更することができる。」と明記されているため、課長の操作により即時にビューの出力が変わり、アクセス制限が反映されます。
6. 結論（要点）
   　対応案Bが優れているのは、課長自身がチームメンバ行の日付を更新して「直ちに」ビューから除外でき、DBAにロール剥奪を依頼する運用（1営業日待ち）が不要になるためです。

誤りやすいポイント

• 担当終了日を「当日」にすれば即座に除外されると誤解する点。SQL6の条件は「担当終了日 >= CURRENT_DATE」であるため、当日設定では除外されません。即時除外には過去日（CURRENT_DATEより前）に更新する必要があります。
• 「ビューを使うからテーブル権限は関係ない」と考えて、チームメンバ管理の運用責任や権限配分を軽視する点。課長に適切な更新権限を与える設計が前提です。
• ロール運用が現場で即時変更できると安易に想定する点。設問ではロールの付与・剥奪はDBA経由で行う運用になっているため即時性が損なわれます。
• チーム編成が頻繁に変わる点を無視して「課単位のロールで十分」と判断する点。頻繁な変更がある場合は、ロール管理より行データでの管理が運用負荷・即時性の面で有利です。

FAQ