応用情報技術者 2024年 春期 午後 問06

解答の論理構成

1. 【問題文】の「入社」の説明には「従業員が入社した際、…配属先の部署及び入社年月日を登録する。」とあります。ここから、1人の従業員は必ず1つの部署に所属することが読み取れます。
2. 一方、部門には複数の従業員が存在し得ることは、一般的な組織構造だけでなく【問題文】「評価者管理」の「部署の管理者を評価者として登録する。」という記載からも裏付けられます。部署に管理者が設定されるということは、部署が従業員を多数抱える前提です。
3. よって「部署」側が1、「従業員」側が 多 の関係になります。図1の凡例に従い、1対 多 を示す表記は「→」です。
4. 以上より、a には「→」を入れることでE-R図が整合します。

誤りやすいポイント

• 「従業員が複数部署を兼務する可能性があるのでは」と考え、多対多を選んでしまう。今回の要件には兼務を許容する記述がなく、従業員は1部署と読み取れる点に注意が必要です。
• 矢印の向きを逆にしてしまう。矢印の根元（1側）が「部署」、矢印の先（多側）が「従業員」となることをER図の凡例で再確認するとミスを防げます。
• 「評価」や「目標」といった他のエンティティとの関係と混同する。入社時の登録内容に着目すれば、本問で問われているのは部署と従業員の関係であると判断できます。

FAQ

解答の導き方

1. 出題箇所の確認
   図2を見ると上のSELECTに「SELECT 祝日 AS 日付, 祝日名 AS 日付名 FROM 国民の祝日 WHERE 祝日 b」、下のSELECTに「SELECT 会社記念日 AS 日付, 会社記念日名 AS 日付名 FROM 会社記念日 WHERE 会社番号 = :会社番号 AND 会社記念日 b」とあります。両方のSELECTで結果列名を「日付」「日付名」で揃えているため、UNION ALLで結合した後に結果全体を「日付」で並べ替える意図であると読み取れます。
2. b の導出理由
   ・図中で期間を示す埋込み変数が「:年度開始日」「:年度終了日」であることが分かります。したがって、祝日・会社記念日の「日付」をその期間内に絞る条件が必要です。
   ・SQLで期間を包括的に指定する簡潔な書き方は BETWEEN ... AND ... であり、BETWEEN は両端の値を含みます（開始日・終了日を含めて抽出したい意図に合致します）。
   以上から b は
   BETWEEN :年度開始日 AND :年度終了日
   となります。代替表現としては「祝日 >= :年度開始日 AND 祝日 <= :年度終了日」のように >=／<= を使う書き方も等価です。
3. c の導出理由
   図2の最終行に「c 日付」とあり、ここは並べ替えを指示する句であるため SQL の ORDER BY が当てはまります。図3の注記に「c には、図2中の c と同じ字句が入る」とあるので、図3でも c は ORDER BY になります。図3に入る具体例は次のようになります。
   ORDER BY DEP.部署番号, EMP.従業員番号
4. 完成イメージ（抜粋、空欄を埋めた例）
   図2（主要部分） SELECT 祝日 AS 日付, 祝日名 AS 日付名
   FROM 国民の祝日
   WHERE 祝日 BETWEEN :年度開始日 AND :年度終了日
   UNION ALL
   SELECT 会社記念日 AS 日付, 会社記念日名 AS 日付名
   FROM 会社記念日
   WHERE 会社番号 = :会社番号
   AND 会社記念日 BETWEEN :年度開始日 AND :年度終了日
   ORDER BY 日付
   図3（主要部分） SELECT DEP.部署番号, DEP.部署名, EMP.従業員番号, EMP.従業員氏名
   FROM 従業員 EMP INNER JOIN 部署 DEP
   ON EMP.会社番号 = DEP.会社番号
   AND EMP.部署番号 = DEP.部署番号
   AND EMP.会社番号 = :会社番号
   AND DEP.管理者番号 = :管理者番号
   ORDER BY DEP.部署番号, EMP.従業員番号
5. 最終的な空欄の答え
   b：BETWEEN :年度開始日 AND :年度終了日
   c：ORDER BY

誤りやすいポイント

• BETWEEN の意味を誤解する
  BETWEEN は両端を含む（inclusive）ので、開始日・終了日を含めて抽出したいかどうかを確認すること。端を含めたくないなら > / < を使う必要があります。
• 範囲指定の表記ゆれ
  「BETWEEN :開始 AND :終了」と「>= :開始 AND <= :終了」は等価ですが、書式やスペースの有無で見落としが起きやすいので統一して書くこと。
• ORDER BY の位置を誤る
  UNION ALL で結合した結果全体を並べ替える場合は、最後に1回だけ ORDER BY を置くこと。各SELECTに個別のORDER BYを書いても、全体の順序は保証されない／文法エラーになる場合があります。
• 項目名の修飾（QUALIFY）の混乱
  両テーブルに同名カラム（例：部署番号）がある場合、ORDER BY に書く列は DEP.部署番号 や EMP.部署番号 のようにどちらのテーブルの列か明示する必要があります。EMP.部署番号 を使うのが絶対に誤りというわけではなく、曖昧さを避けるためにどちらを基準にするかを明確にすることが重要です。
• UNION と UNION ALL の違い
  重複行を取り除く必要がないなら UNION ALL を用いるのが高速です。図2では同一日付が両方（国民の祝日と会社記念日）にある可能性があるため、要件に応じて使い分けます。
• 出力列のエイリアス利用に関するDB依存
  多くのDBでは ORDER BY に出力エイリアス（日付）を使えますが、稀に制限がある実装もあるため、問題文の意図にしたがって使うか、もしくは明示的な列名・修飾子を使うと安全です。

FAQ

解答の論理構成

1. 図3の INNER JOIN 句
   FROM 従業員 EMP INNER JOIN 部署 DEP
   ON EMP.会社番号 = DEP.会社番号
   AND d
   では、従業員表と部署表を結合しています。
2. 会社番号だけで結合すると「同じ会社に属している従業員と全部署」が混ざり、1 対 多の冗長なCartesian結合が発生します。部署単位に絞る追加条件が欠かせません。
3. 図1（E-R図）には「従業員―部署」は部署番号で結び付く1 対 多 関係であることが示されています。したがって結合キーは次のとおりです。
   EMP.部署番号 = DEP.部署番号
4. これを d に入れれば、会社番号と部署番号の2つのキーで正しく等価結合が成立し、「指定管理者が管轄する部署に所属する従業員一覧」が取得できます。

誤りやすいポイント

• 会社番号だけで結合してしまい、部署ごとの絞り込みを忘れる。これでは同一会社内の全従業員が戻り、意図しない結果になります。
• WHERE 句に部署番号条件を書いて ON 句を省略するケース。INNER JOIN では結合条件は ON に書くのが可読性・保守性に優れます。
• 別名を忘れて 部署.部署番号 などプレフィックスなしで記述し、曖昧列エラーになる。エイリアスと列名はセットで記述しましょう。

FAQ

解答の論理構成

1. レビュー段階で指摘されたリスク
   引用：
   ・指摘
   　「SQLインジェクションの脆弱性が見つかってしまった場合、多くの情報が漏えいしてしまうおそれがある。」
   単一スキーマでは全社のデータが同一空間にあるため、攻撃成功時の被害範囲が広いことが問題になっています。
2. アドバイスとして提示された対策
   引用：
   「共有用のスキーマと、①システム利用者の会社ごとのスキーマに分ける方式」
   会社ごとにスキーマを分離し、各社利用者には自社スキーマへの参照権限のみを与える運用を想定しています。
3. 分離の効果
   ・スキーマ単位のアクセス制御により、ある会社の利用者（または攻撃者）が別会社の表へ直接アクセスすることは権限上不可能になります。
   ・仮にSQLインジェクションが発生しても、被害はそのスキーマ＝その会社のデータに限定されます。
4. 以上より、方式①の利点は
   「漏れる情報を会社単位に制限できる。」
   となります。

誤りやすいポイント

• 単一データベース＝単一スキーマと誤解し、スキーマ分割でもインジェクション被害は変わらないと思い込む。
• 「共有用スキーマ」が存在する点を失念し、そこで漏えいが起こる可能性を考慮しない。
• 「マルチテナント化＝性能向上」のみを利点として挙げ、セキュリティ観点を回答に含め忘れる。

FAQ

解答の論理構成

1. 共有用スキーマと会社ごとのスキーマ
   • 問題文には「共有用のスキーマに作成した表は、会社ごとのスキーマに対象の表と同じ名前のビューを作成して照会できる」とあります。
   • 表2では共有用スキーマ名が“PUB”、個別会社用スキーマ名が“Cxxx（xxxは任意の英数字）”であると示されています。
2. ビューを作成する目的
   • 会社ごとのスキーマ側からは、共有用スキーマにある“国民の祝日”表をあたかも自分のスキーマに存在するかのように利用できるようにするため、同名のビューを作成する必要があります。
3. 図4の読み取り
   • 図4には「CREATE VIEW e （祝日, 祝日名） AS SELECT 祝日, 祝日名 FROM f」と提示されています。
   • ここで e には“ビュー名（スキーマ名.ビュー名）”を、 f には“基となる表名（スキーマ名.表名）”を入れます。
4. 字句を決定
   ① ビューを置くスキーマは「スキーマC001」と明示されているため、C001. が先頭に付きます。ビュー名は共有用表と同じ“国民の祝日”。
   ② 基となる表は共有用スキーマ“PUB”に存在する“国民の祝日”。
   以上より
   • e → C001.国民の祝日
   • f → PUB.国民の祝日

誤りやすいポイント

• ビュー名を単に“国民の祝日”と書き、スキーマ名を付け忘れる。アクセス権が会社スキーマに限定されるため必須です。
• 共有用スキーマ名を“PUB”ではなく“PUBLIC”などと誤記する。問題文で指定されている正確な名称を用いる必要があります。
• CREATE VIEW の列リストを省略できると誤解し、列名の並びを共有用表と変えてしまう。ビュー列名は表と一致させておく方が既存SQLの修正を最小化できます。

FAQ

解答の論理構成

1. 単一データベース・個別スキーマ方式では、各会社専用スキーマを用意し、利用者が自社スキーマ内の表・ビューだけを参照できるようにします。本文には「システム利用者ごとに、利用するスキーマを指定するために、g表にh列を追加する必要がある。」と記載されています。
2. どの会社がどのスキーマを使うかを管理できる表は「会社、国民の祝日」を持つ共有用スキーマPUBに存在する「会社」表です。ここへスキーマ識別子を登録すれば、ログイン後にその値で SET search_pathなどを行うだけで参照先を自社スキーマへ限定できます。
3. したがってgに入るのは「会社」です。
4. 次に追加する列名は会社とスキーマを1対1で結び付ける情報です。本文のアドバイスでは「会社ごとのスキーマ」と繰り返し説明されているため、列名にはスキーマそのものを示す語を置くのが自然です。
5. 以上よりhに入るのは「スキーマ名」です。
6. まとめると、g＝「会社」、h＝「スキーマ名」となります。

誤りやすいポイント

FAQ

解答の論理構成

1. 表2では、個別会社用スキーマCxxxに配置される表として
   「会社記念日、従業員、部署、目標、実績、評価、退職」
   が列挙されています。したがって、表「退職」は会社ごとに分割され、他社のデータを直接参照できません。
   （引用）「表2　スキーマを整理した結果」
2. 一方、機能一覧には次の説明があります。
   （引用）「退職分析：人事部の管理者が自社及び同じ業種の退職者について、在籍期間と退職理由を分析する。」
   ― 自社だけでなく “同じ業種の退職者” ＝ 他社の退職データを横断的に集計・分析する要件です。
3. よって、退職データが会社ごとに隔離されたままでは横断集計が不可能になり、機能要件を満たせません。
4. 以上より、下線②「利用できない機能」は「退職分析」であると論理的に導けます。

誤りやすいポイント

• 「退職」機能と混同する
  “退職” は各社内の登録処理で完結するため隔離しても問題なし。横断分析を行う“退職分析”と要件が異なることに注意。
• 「国民の祝日」「会社記念日」を想定する
  いずれもビューで全社共通参照が可能。スキーマ配置上の制約は受けにくい。
• “同じ業種” の読み落とし
  要件文にある “同じ業種の退職者” を見逃すと、他社データが不要と誤解してしまう。

FAQ

解答の論理構成

1. 機能要件の確認
   表1の機能概要には「退職分析」に関して
   “自社及び同じ業種の退職者について、在籍期間と退職理由を分析する。”
   と明記されています。分析対象が “自社” だけでなく “同じ業種” の他社にまで及ぶため、退職データは会社横断で参照できる必要があります。
2. 表配置案の問題点
   表2では “退職” が “個別会社用” スキーマCxxxに入っています。これではスキーマを切り替えない限り他社の退職データを参照できず、前述の「退職分析」は実行不可能です。
3. レビュー指摘との合致
   レビューで “表2の表とビューの配置のままでは②利用できない機能がある” と指摘されています。利用できない機能＝「退職分析」と判断できます。
4. 見直し内容の導出
   横断分析を可能にする最小限の修正は、退職データを全社共通で見える場所に移すことです。すでに共有スキーマ “PUB” が用意されているため、
   “退職表を共有スキーマに配置する。”
   が最も自然な対処となります。

誤りやすいポイント

• 退職分析を「自社限定の集計」と誤読し、退職を個別スキーマのままにしてしまう。
• 「会社記念日」や「国民の祝日」を移動対象と勘違いし、分析要件との整合を崩す。
• ビューを用いればどこに表があっても良いと思い込み、根本の配置要件を無視する。

FAQ