平成30年秋期試験午後問題　問6

問6　データベース

⇱問題PDF
入室管理システムの設計に関する次の記述を読んで，設問1～5に答えよ。
広告
　H社は中堅の食品会社で，社内システムのデータベースの統合を検討している。現在，社内システムごとにデータベースのサーバを用意して運用しているが，関係データベース管理システム(以下，RDBMSという)のライセンスコストと運用コストを削減するために1台のサーバに統合し，各社内システムのデータベースは，統合したサーバのRDBMSでスキーマを分けて管理することになった。

〔社員情報の共用〕
　全ての社内システムは，社員IDや氏名などの社員情報を使用する。現在は，人事システムが管理している社員情報のマスタデータを月次処理で各社内システムに配布して運用しているが，最新の情報が反映されるのが翌月になること，月次処理の運用負荷が大きいことなどから改善が望まれている。今回，サーバを統合するに当たり，各社内システムにデータを配布するのではなく，人事システムが管理する社員情報に関連する実表を参照する方式に変更することを検討している。人事システムの社員情報に関連する実表を表1に示す。
pm06_1.png
　セキュリティの観点から検討した結果，人事システム以外の社内システムから社員情報に関連する実表を直接参照するのではなく，社員情報を使用する社内システムごとに必要な列だけをビュー表として公開し，ビュー表を参照する方式を採用することに決定した。

〔入室管理システム〕
　会社内の特別な部屋の入退室管理を行う入室管理システムは，サーバ統合の対象となるシステムの一つである。入室管理システムで利用する主な実表とビュー表を表2に，E-R図を図1に，入室に関する主なユースケースを表3に示す。
pm06_2.png
　表3のユースケース"入室"で，入室可否をチェックし，否の場合は0を，可の場合は1以上を返すSQL文を図2に示す。ここで，":社員ID"は指定された社員IDを格納する埋込み変数，":室ID"は指定された室IDを格納する埋込み変数，":今日"はSQL文実行時の現在日付を格納する埋込み変数である。また，ROOMは入室管理システムのスキーマ名で，表は"スキーマ名.表名"で表記する。
pm06_3.png
〔各社内システムのRDBMSユーザ〕
　社内システムごとにデータベース管理者(以下，DBAという)が存在する。DBAは表の所有者であり，他のユーザに対して，自分が所有する表へのアクセス権限を付与することができる。DBAは，各社内システムのアプリケーションプログラム(以下，APという)が表のデータにアクセスすることができるようにAP用のユーザに対して，適切な権限を付与する。各社内システムのスキーマ名と，DBA用，AP用のRDBMSユーザ名を表4に示す。
pm06_4.png
〔RDBMSの表のアクセス権限に関する主な仕様〕
　使用しているRDBMSの表のアクセス権限に関する主な仕様を(1)，(2)に示す。
  • 表のデータに対して，所有者以外のユーザが参照，挿入，更新及び削除を行うためには，表に対して対応するアクセス権限(SELECT，INSERT，UPDATE及びDELETEの各権限)を所有者から付与してもらう必要がある。
  • ビュー表にアクセスする場合，そのビュー表が参照する表のアクセス権限は不要である。
〔入室管理システム用の社員ビュー表〕
　表2のビュー表"入室管理用社員"を定義するSQL文を図3に示す。
pm06_5.png
　このビュー表を入室管理システムのAPが参照だけできるように権限を付与するSQL文を図4に示す。
pm06_6.png
〔入室申請時の確認の強化〕
　管理者は，"申請者が入室希望社員の組織長であること"を確認することになった。そのため，ビュー表"入室管理用社員"に組織長の氏名が必要となり，図5に示すSQL文に変更した。
pm06_7.png
広告

設問1

図1に適切なエンティティ間の関連を記入し，E-R図を完成させよ。図1の凡例に倣うこと。

解答例・解答の要点

pm06_8.png

解説

入室管理システムの各表のうち社員ID及び室IDを主キーとすると、次のリレーションが成り立つことがわかります。
pm06_9.png
〔入室許可エンティティについて〕
① 入室管理用社員エンティティと共通の属性"社員ID"をもちます。この属性"社員ID"は、入室管理用社員エンティティ側では主キーであり、社員を一意に特定します。この"社員ID"は入室許可エンティティ側では外部キーであり、1人の社員に複数の室の入室許可がされることが考えられます。つまり、入室許可エンティティには同一の社員IDの値を持つ複数のレコードが存在しえるということです。よって、入室管理用社員エンティティと入室許可エンティティのカーディナリティは1対多です。

この関連は、入室管理用社員エンティティ(主キー側)から入室許可エンティティ(外部キー側)に向けた矢印で示します。

② 室エンティティとの関係についても同様です。"室ID"は、室エンティティ側では主キーであり、室を一意に特定します。この"室ID"は入室許可エンティティ側では外部キーであり、1つの室に複数の社員が入室許可がされることが考えられます。よって、室エンティティと入室許可エンティティのカーディナリティは1対多です。

この関連は、室エンティティ(主キー側)から入室許可エンティティ(外部キー側)に向けた矢印で示します。

〔入退室ログエンティティについて〕
③ 入室管理用社員エンティティと共通の属性"社員ID"をもちます。先程の2つと同様の考え方なので説明は割愛しますが、入室管理用社員エンティティが主キー、入退室ログエンティティが外部キーの関係となるので、入室管理用社員エンティティと入退室ログエンティティのカーディナリティは1対多です。

この関連は、入室管理用社員エンティティ(主キー側)から入退室ログエンティティ(外部キー側)に向けた矢印で示します。

④ 室エンティティと共通の属性"室ID"をもちます。室エンティティが主キー、入退室ログエンティティが外部キーの関係となるので、室エンティティと入退室ログエンティティのカーディナリティは1対多です。

この関係は、室エンティティ(主キー側)から入退室ログエンティティ(外部キー側)に向けた矢印で示します。

以上の4つの関連を記入した下図が正解となります。
pm06_10.png
原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ（多重度）は、主キー側を"1"、外部キー側を"多"とした「1対多」になります。これを利用して表間のリレーションを見て機械的に判別することも可能です。
広告

設問2

表2に示した実表"入室許可"における，主キーを答えよ。

解答例・解答の要点

社員ID，室ID，入室許可開始年月日

解説

入室許可エンティティでは、
  • 1人の社員に対して複数の室の入室許可が存在できること
  • 1つの室に対して複数の社員の入室許可が存在できること
が求められますが、同一社員・同一室の組み合わせで2回以上入室申請が行われることが考えられます。入室可能期間が始まる前に次の入室許可を申請しておくような場合です。
新たな入室申請が行われた際の入室許可開始年月日が、過去の入室許可の入室許可終了年月日より後の日付の場合に、過去の入室許可のレコードを削除（ないしは上書き）するという記述はないため、
  • 社員と室の1つの組に対して複数の期間の入室許可が存在できること
も求められます。よって、(複合)主キーとして社員IDと室IDだけでなく、入室許可開始年月日と入室許可終了年月日も必要であると考えられます。しかし、表3のユースケース名「入室許可登録」の概要には、「既に実表"入室許可"に同じ社員ID，室ID，入室許可開始年月日の行が存在する場合は，入室許可終了年月日を更新する」とあり、同一社員・同一室・同一入室許可開始年月日のレコードは1つしか存在しえないことがわかります。このため、主キーに入室許可開始年月日だけを加えれば足り、入室許可終了年月日を加えるのは冗長であるといえます。したがって、入室許可エンティティの主キーは「社員ID，室ID，入室許可開始年月日」の組合せとなります。

∴社員ID，室ID，入室許可開始年月日
広告

設問3

図2中のaに入れる適切な字句を答えよ。

解答例・解答の要点

a：COUNT(*)

解説

〔aについて〕
図2のSQL文では、ROOMスキーマの入室許可表から「社員IDと室IDが指定のものであり、今日が入室許可期間内である」という条件のレコードを抽出します。この条件を満たすレコードが存在する（つまり、レコードが1つ以上）ということは本日の入室が許可されていて、条件を満たさない（つまり、レコードがない）ということは本日の入室が許可されていないということです。

aにレコードの個数を数える関数を入れれば、本文で説明されている結果、すなわち前者に1以上を後者に0を返すことができます。したがって、aには「COUNT(*)」が当てはまります。

∴a＝COUNT(*)
広告

設問4

ビュー表"入室管理用社員"について，(1)，(2)に答えよ。
  • 図4中のb～eに入れる適切な字句を答えよ。
    なお，表は"スキーマ名.表名"で表記すること。
  • ビュー表を参照する権限を付与するSQL文を実行するユーザ名を答えよ。

解答例・解答の要点

  • b：GRANT
    c：SELECT
    d：HR.入室管理用社員
    e：ROOM_AP
  • HR_DBA

解説

  • ビュー表"入室管理用社員"を入室管理システムのAPが参照だけできるようにすることを考えます。

    〔bについて〕
    特定のユーザに表などのオブジェクトに関する権限を付与するためにはGRANT文を使用します。GRANT文の構文は以下のとおりです。
    GRANT 権限名 ON オブジェクト名
    　　TO { ユーザ名 | ロール名 | PUBLIC }
    　　[ WITH GRANT OPTION ]
    よって、SQL文の先頭であるbには「GRANT」が入ります。

    ∴b＝GRANT

    〔cについて〕
    <権限>にはSELECT・UPDATE・INSERT・DELETEなどから1つ以上（あるいはすべてを意味するALL）を記述することができますが、今回は「参照だけできるように」したいので参照権限を示すSELECTのみを付与します。したがって、cには「SELECT」が入ります。

    ∴c＝SELECT

    〔dについて〕
    権限付与の目的となる表は図3のビュー表です。これは人事システム内の実表の一部を入室管理ステムから参照できるようにしたものです。図3のSQL文及び表4のスキーマ名より、ビュー表"入室管理用社員"の属するスキーマは「HR」であることがわかります。したがって、dには「HR.入室管理用社員」が入ります。

    ∴d＝HR.入室管理用社員

    〔eについて〕
    今回は権限付与の対象システムが入室管理システムで、対象ユーザがAP用のため、<ユーザ名>には入室管理システムのAP用ユーザ名を記述します。表4より、入室管理システムのAP用ユーザ名は「ROOM_AP」とわかります。したがって、eには「ROOM_AP」が入ります。

    ∴e＝ROOM_AP

  • 〔各社内システムのRDBMSユーザ〕には「社内システムごとにデータベース管理者(以下，DBAという)が存在する。DBAは表の所有者であり，他のユーザに対して，自分が所有する表へのアクセス権限を付与することができる」とあります。前述したようにビュー表"入室管理用社員"の属するスキーマは「HR」ですから、アクセス権限の付与は人事システムのDBA用ユーザが実行します。表4より、人事システムのDBA用ユーザ名は「HR_DBA」とわかります。

    ∴HR_DBA
広告

設問5

図5中のfに入れる適切な式を答えよ。

解答例・解答の要点

f：T1.所属組織ID=T3.組織ID AND T3.組織長の社員ID=T2.社員ID

解説

〔fについて〕
WHERE句に記述する文ですから、結合条件または抽出条件が入ります。

図5のSQL文によると、ビュー表"入室管理用社員"の各列の対応は次のようになります。
  • 社員ID：社員表（T1）の社員ID列
  • 氏名：社員表（T1）の氏名列
  • 勤務区分：社員表（T1）の勤務区分列
  • 組織長氏名：社員表（T2）の氏名列
ある社員の組織長の氏名を得るためには、その社員の「社員ID→所属する組織の組織ID→その組織の組織長の社員ID→組織長の氏名」の順に参照すればよいと考えられます。それをSQL文で実現するには、
  1. 「社員ID、所属組織ID、氏名、勤務区分をもつ社員表(T1)」と「組織ID、組織長の社員IDをもつ組織表(T3)」を(所属)組織IDで結合する。
    　 →WHERE句に「T1.所属組織ID = T3.組織ID」を記述する。
  2. 「組織長の社員IDをもつ組織表(T3)」と「社員ID、氏名をもつ社員表(T2)」を(組織長の)社員IDで結合する。
    　 →WHERE句に「T3.組織長の社員ID = T2.社員ID」を記述する。
といった手続きを行います。
pm06_11.png
したがって、fには「T1.所属組織ID = T3.組織ID AND T3.組織長の社員ID = T2.社員ID」が入ります。
広告
模範解答
広告

前の問題次の問題

▲
Pagetop