令和6年春期試験午後問題　問6

問6　データベース

⇱問題PDF
人事評価システムの設計と実装に関する次の記述を読んで，設問に答えよ。
広告
　K社は，人事評価システムを中小企業に提供するSaaS事業者である。現在は，契約している会社ごとに仮想サーバを作成して，その中にデータベースを個別に作成している。現在のシステムのOSやフレームワークのサポート期限が迫ってきたのを機に，機能は変更せずにサーバリソース最適化を目的として，システムを再構築することにした。

〔人事評価システムの機能概要〕
　人事評価システムの機能概要を表1に示す。
pm06_1.png
〔単一データベース・単一スキーマ方式の検討〕
　データベースのリソースを最適化するために，会社ごとに個別に作成していたデータベース及びスキーマを一つにまとめることを考える。検討したE-R図を図1に示す。
　なお，再構築するシステムでは，E-R図のエンティティ名を表名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
pm06_2.png
　図1を関係データベースに実装した際のSQL文を考える。
  • 指定された会社と年度における，国民の祝日と会社記念日の一覧を日付の昇順に出力するSQL文を図2に示す。ここで":会社番号"は指定された会社の会社番号を，":年度開始日"，":年度終了日"は，それぞれ指定された年度の開始日，終了日を表す埋込み変数である。
    pm06_3.png
  • 指定された管理者が評価する対象の従業員の一覧を部署番号，従業員番号の昇順に出力するSQL文を図3に示す。ここで":会社番号"と":管理者番号"は，それぞれ指定された管理者の会社番号と従業員番号を表す埋込み変数である。
    pm06_4.png
〔単一データベース・単一スキーマ方式のレビュー〕
　検討した単一データベース単一スキーマ方式のレビューを受けたところ，次の指摘とアドバイスを受けた。
  • 指摘
    この検討案は，サーバリソース最適化を実現することができるが，SQLインジェクションの脆弱性が見つかってしまった場合，多くの情報が漏えいしてしまうおそれがある。
  • アドバイス
    データベースは一つのまま，システム全体で共有するデータだけを格納する共有用のスキーマと，①システム利用者の会社ごとのスキーマに分ける方式にするとよい。共有用のスキーマに作成した表は，会社ごとのスキーマに対象の表と同じ名前のビューを作成して照会できるようにすると，現在のシステムのSQL文への修正を少なくすることができる。

〔単一データベース・個別スキーマ方式の検討〕
　〔単一データベース・単一スキーマ方式のレビュー〕のアドバイスを受け，複数のスキーマを作成して各スキーマに表とビューを配置する。検討したスキーマを整理した結果を表2に示す。
pm06_5.png
　次に，ビューを作成するSQL文について考える。
　スキーマC001に国民の祝日ビューを作成するSQL文を図4に示す。
pm06_6.png
〔単一データベース・個別スキーマ方式のレビュー〕
　検討した単一データベース・個別スキーマ方式のレビューを受けたところ，次の指摘を受けた。
  • システム利用者ごとに，利用するスキーマを指定するために，g表にh列を追加する必要がある。
  • 表2の表とビューの配置のままでは②利用できない機能があるので，③配置を一部見直す必要がある。
　レビューで受けた指摘に全て対応することで，システムを再構築することができた。
広告

設問1

〔単一データベース・単一スキーマ方式の検討〕について答えよ。
  • 図1中のaに入れる適切なエンティティ間の関連を答え，E-R図を完成させよ。
    なお，エンティティ間の関連の表記は，図1の凡例に倣うこと。
  • 図2中のb，図2及び図3中のcに入れる適切な字句を答えよ。
  • 図3中のdに入れる適切な字句を答えよ。

解答例・解答の要点

  • a：→
  • b：BETWEEN :年度開始日 AND :年度終了日
    c：ORDER BY
  • d：EMP.部署番号 = DEP.部署番号

解説

  • 〔aについて〕
    空欄には従業員エンティティと部署エンティティの関連を表す線が入ります。1つの部署には（当然に）複数の従業員が所属できるので、すでにE-R図には部署と従業員のカーディナリティが「1対多」であることを示す「←」が引かれています。もう1つ空欄があるということは、これとは別に2つのエンティティ間に関連が存在するということを意味しています。

    表1の機能名"評価者管理"には「1人の従業員が複数の部署を管理する場合がある」とあり、この記述をE-R図に照らし合わせると次の2点が確認できます。
    • 部署エンティティの外部キーとして管理者番号があり、従業員エンティティの従業員番号を参照している
    • 1人の従業員が複数の部署を管理するということは、部署エンティティには同一の管理者番号の値をもつ複数のレコードが存在しえる
    以上より、従業員エンティティと部署エンティティのカーディナリティは「1対多」とわかるため、空欄aには「→」が当てはまります。

    ∴a＝→

  • 図2のSQL文は「指定された会社と年度における，国民の祝日と会社記念日の一覧を日付の昇順に出力する」ものです。このSQL文にはUNION ALL句が使われており、2つのSELECT文の結果を結合（和演算）するものとなっています。

    〔bについて〕
    空欄bで問われているのはWHERE句の条件式です。指定された会社の部分は「会社番号 = :会社番号」で実現されていますが、もう1つの抽出条件である年度を指定している部分がないので、空欄には特定の年度で絞る条件式が入ります。具体的な日付を値にもつ列は、国民の祝日テーブルは祝日列、会社記念日テーブルは会社記念日列です。本文中に「":年度開始日"，":年度終了日"は，それぞれ指定された年度の開始日，終了日を表す埋込み変数である」とあるので、ある年度に属する祝日（会社記念日）のレコードだけを選択するために、この埋込み変数を使うことができます。値の範囲の条件とするにはBETWEEN句を使用します。A以上B以下を指定するには「BETWEEN A and B」と記述するので、本問に当てはめると「BETWEEN :年度開始日 and :年度終了日」となります。

    ∴b＝BETWEEN :年度開始日 AND :年度終了日

    〔cについて〕
    SQL文の最後に位置すること、空欄の後ろに「日付」が続いていることから「日付の昇順に出力する」処理を実現する文が入ることがわかります。ある列の値をキーとしたレコードの並替えはORDER BY句で行います。したがって、空欄cには「ORDER BY」が当てはまります。なお、昇順はASC、降順はDESCをそれぞれ列名の後ろに付けますが、ASCは省略可能で、本問のように指定がない場合は自動的に昇順として扱われます。

    ∴c＝ORDER BY

  • 〔dについて〕
    図3のSQL文は「指定された管理者が評価する対象の従業員の一覧を部署番号，従業員番号の昇順に出力する」ものです。従業員と管理者を紐づけるには、従業員表と部署表を結合し、管理者番号を参照する必要があります。ある従業員に関連付けられた部署は、会社番号と部署番号の組合せで特定するので、この2つの列が結合属性となります。図3を見るとINNER JOINのON句で会社番号での結合が指示されていますが、部署番号での結合の指定がないため空欄dにはこれが当てはまります。従業表のエイリアスはEMP、部署表はDEPですから、両者の部署番号を結合する指示は「EMP.部署番号 = DEP.部署番号」です。

    ∴EMP.部署番号 = DEP.部署番号
広告

設問2

本文中の下線①の方式にする利点は何か。20字以内で答えよ。

解答例・解答の要点

漏れる情報を会社単位に制限できる (16文字)

解説

スキーマ※は、プログラミングにおける名前空間やファイルシステムのディレクトリのように、データベース内を論理的に区分けした単位です。スキーマにはテーブル、ビュー、インデックス、制約、トリガー、プロシージャなどデータベースの構成要素を含みます。本問のように1つのデータベースに複数の会社のデータを格納している場合でも、会社ごとに異なるスキーマで管理していれば、「会社A.従業員」と「会社B.従業員」という同名の2つのテーブルが存在できます。同一のデータベース内ではあるものの、アプリケーション側からはスキーマ・ユーザーの指定を変更することであたかも独立したデータベースであるかのように扱うことが可能です。

単一データベース・単一スキーマ方式に対するレビューでは、「サーバリソース最適化を実現することができるが，SQLインジェクションの脆弱性が見つかってしまった場合，多くの情報が漏えいしてしまうおそれがある」という指摘を受けています。この課題に対して、会社ごとのスキーマに分ける方式が提案されています。つまり、この方式はSQLインジェクション対策として効果的であるということです。

単一データベース・単一スキーマ方式では、どのシステム利用者もすべてのテーブルに対してアクセス権があります。このため、SQLインジェクションの脆弱性があった場合、システム利用者または正規のユーザーになりました攻撃者が、すべてのテーブルの情報を不正取得したり、破壊したりといった大きな被害が生じるリスクがあります。これに対して会社ごとのスキーマに分ける方式では、システム利用者とDBMSのユーザーを紐づけ、そのユーザーに同社のスキーマへのアクセスを許可すると考えられるので、他社のスキーマに対するアクセスは制限されます。これにより、SQLインジェクションの被害範囲をそのシステム利用者がアクセス可能なテーブルだけに限定することが可能です。

指摘内容として「多くの情報が漏えいしてしまうおそれ」とあるため、情報漏えいの範囲を抑えられる旨の説明を含めるのが肝要です。解答例は「漏れる情報を会社単位に制限できる」です。他にも「情報漏えいの被害を会社単位に限定できる」「データ漏えいを会社ごとに抑えられる」などの文章でも良いと思います。

∴漏れる情報を会社単位に制限できる

※ このスキーマはANSI/SPARC 3層スキーマの概念スキーマ、外部スキーマ、内部スキーマとは異なるものです。
広告

設問3

図4中のe，fに入れる適切な字句を答えよ。

解答例・解答の要点

e：C001.国民の祝日
f：PUB.国民の祝日

解説

SQLで表やビューの名前を記述するときは、スキーマ名が明示されていればそのスキーマ内のオブジェクトを参照し、明示されていなければデフォルトのスキーマ内のオブジェクトを参照します。SQL文でスキーマを明示するには「スキーマ名.表名」の形式で指定します（スキーマ修飾）。

〔eについて〕
CREATE VIEW文は以下の構文で、ビューを作成するSQL文です。
CREATE VIEW <ビュー名> [(<列名1>, <列名2>, ...)] AS <SELECT文>
図4のSQL文は、スキーマC001に国民の祝日ビューを作成するものです。CREATE VIEWに続いて指定する字句は、ビューの名前です。複数のスキーマが存在する場合、ビューが作成されるスキーマを明示する必要があるので、スキーマ修飾が必要となります。ビューの名前は国民の祝日、ビューが作成されるスキーマ名はC001なので、これを組み合わせた「C001.国民の祝日」が当てはまります。

∴e＝C001.国民の祝日

〔fについて〕
FROM句の後ろなのでビューの元となる実表（またはビュー）を指定します。表2より、国民の祝日表はスキーマPUBに属しているため、表名の国民の祝日をPUBでスキーマ修飾した「PUB.国民の祝日」が当てはまります。

∴f＝PUB.国民の祝日
広告

設問4

〔単一データベース・個別スキーマ方式のレビュー〕について答えよ。
  • 本文中のg，hに入れる適切な字句を答えよ。
  • 本文中の下線②の機能を，表1の機能名から答えよ。
  • 本文中の下線③の見直した内容を，20字以内で答えよ。

解答例・解答の要点

  • g：会社
    h：スキーマ名
  • 退職分析
  • 退職表を共有用スキーマに配置する (16文字)

解説

  • 〔gfについて〕
    単一データベース・個別スキーマ方式では、システムの利用者である会社ごとのスキーマに分けるアプローチです。設問2の解説で述べたように、この方式ではシステム利用者がデータベースにアクセスする際に、どの会社用のスキーマを使用するかを特定できる必要があります。利用するスキーマは会社単位で異なるため、会社表の属性として持たせるのが適切です。追加される属性は、各会社が利用するスキーマの識別を目的とするため、名称としては「スキーマ名」が妥当です。したがって、「会社」表に「スキーマ名」列を追加することになります。別の名称として「会社スキーマ名」や「利用スキーマ名」なども考えられます。

    ∴g＝会社
    　h＝スキーマ名

  • 単一データベース・個別スキーマ方式にした場合に利用できなくなった機能を特定する問題です。表1の機能概要に解答のヒントがあります。

    スキーマで会社ごとにデータが分離されたため、他社のテーブルにはアクセスはできなくなりました。表1の"祝日管理"から"退職"までの各機能は自社のデータのみで完結していますが、"退職分析"だけは「自社及び自社と同じ業種の退職者について，在籍期間と退職理由を分析する」とあるため、他社のデータへのアクセスが必要と判断できます。業種は共有用スキーマに配置された会社表から取得できますが、在籍期間と退職理由が格納されている退職表は各社のスキーマに属しているため、他の会社からアクセスすることができません。したがって、表2の配置では「退職分析」の機能を利用することができません。

    ∴退職分析

  • (2)で説明したとおり、退職分析機能を実現するには、他社の業種と退職者の在籍期間、退職理由にアクセスできる必要があります。共有用スキーマの会社表には業種列があるので、業種が同じである他社の会社番号はわかります。後は退職者の在籍期間と退職理由が格納されている、各会社の退職表にアクセスできるようにすればよいわけです。どの会社からでもデータにアクセスするためには、関係するテーブルを共有用スキーマに移動する方法が考えられます。したがって、解答は「退職表を共有用スキーマに配置する」旨の対策が適切となります。

    ∴退職表を共有用スキーマに配置する
広告
模範解答
広告

前の問題次の問題

▲
Pagetop