問題プレビューID: ad2f4c45応用情報技術者試験 2022年(令和4年度)秋期 午後

問 6
応用情報技術者試験 2022年(令和4年度)秋期 午後

スマートデバイス管理システムのデータベース設計に関する次の記述を読んで,設問に答えよ。

J社は,グループ連結で従業員約3万人を抱える自動車メーカーである。従来は事業継続性・災害時対応施策の一環として,本社の部長職以上にスマートフォン及びタブレットなどのスマートデバイス(以下,情報端末という)を貸与していた。昨今の働き方改革の一環として,従業員全員がいつでもどこでも作業できるようにするために,情報端末の配布対象をグループ企業も含む全従業員に拡大することになった。

現在は情報端末の貸与先が少人数なので,表計算ソフトでスマートデバイス管理台帳(以下,管理台帳という)を作成して貸与状況などを管理している。今後は貸与先が3万人を超えるので,スマートデバイス管理システム(以下,新システムという)を新たに構築することになった。情報システム部門のKさんは,新システムのデータ管理者として,新システム構築プロジェクトに参画した。

〔現在の管理台帳〕

現在の管理台帳の項目を表1に示す。管理台帳は,一つのワークシートで管理されている。

表1 管理台帳の項目

項目名説明記入例
情報端末ID情報端末ごとに一意に付与される固有の識別子G6TF809G0D4Q
機種名情報端末の機種の型名IP12PM
回線番号契約に割り当てられた外線電話番号080-0000-0000
内線電話番号内線電話を情報端末で発着信できるように回線番号と紐づけられている内線電話の番号1234-567890
通信事業者名契約先の通信事業者の名称L社
料金プラン名契約している料金プランの名称プランM
暗証番号契約の変更手続を行う際に必要となる番号0000
利用者所属部署名利用者が所属する部署の名称N部
利用者氏名利用者の氏名試験 太郎
利用者メールアドレス利用者への業務連絡が可能なメールアドレスshiken.taro@example.co.jp
利用開始日J社の情報端末の運用管理担当者(以下,運用管理担当者という)から利用者に対して情報端末を払い出した日2020-09-10

表1 管理台帳の項目(続き)

項目名説明記入例
利用終了日利用者から運用管理担当者に対して情報端末を返却した日2022-09-10
交換予定日J社では情報セキュリティ対策の観点から同一の回線番号のままで2年ごとに旧情報端末から新情報端末への交換を行っており,新情報端末に交換する予定の日2022-09-10
廃棄日情報端末を廃棄事業者に引き渡した日2022-10-20

〔現在の管理方法における課題と新システムに対する要件〕

Kさんは,新システムの設計に際して,まず,現在の情報端末の運用について,運用管理担当者に対して課題と新システムに対する要件をヒアリングした。ヒアリング結果を表2に示す。

表2 ヒアリング結果

項番課題要件
1利用者が情報端末ごとに通信事業者や料金プランを選択できるので,結果として高い料金プランを契約して利用しているケースがある。通信事業者を原則としてL社に統一し,かつ,より低価格の料金プランで契約できるようにする。
2情報端末に関する費用は本社の総務部で一括して負担しており,利用者のコスト意識が低く,利用状況次第で高額な請求が発生するケースがある。従業員の異動情報に基づいて請求を年月ごと,部署ごとに管理できるようにする。
3情報端末に対しては利用可能な機能やアプリケーションプログラム(以下,アプリという)に制限を設けており,利用者から機能制限解除の依頼やアプリ追加の依頼があっても,管理が煩雑となるので認められない状況である。業務上必要な機能やアプリについては,利用者に使用目的を確認し,従業員と情報端末の組合せごとに個別に許可できる仕組みにする。
4契約ごとに異なる暗証番号を設定することで利用者による不正な契約変更の防止を図っているが,運用管理担当者は全ての契約の暗証番号を自由に参照できてしまうので,運用管理担当者による不正な契約変更が発生するリスクが残っている。暗証番号は運用管理担当者の上長(以下,上長という)しか参照できないようにアクセスを制御する。運用管理担当者は契約変更が必要な都度,上長に申請し,上長が契約変更を行う仕組みにする。

〔新システムのE-R図〕

Kさんは,表1の管理台帳の項目と表2のヒアリング結果を基に,新システムのE-R図を作成した。E-R図(抜粋)を図1に示す。なお,J社内の部署の階層構造は,自己参照の関連を用いて表現する。

図1 新システムのE-R図(抜粋)

凡例 —:1対1 →:1対多 ←→:多対多

注記 属性名の実線の下線は主キー,破線の下線は外部キーを示す。主キーの実線が付いている属性名には,外部キーの破線を付けない。

〔表定義〕

このデータベースでは,E-R図のエンティティ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理する。Kさんは,図1のE-R図を実装するために,詳細設計として表定義の内容を検討した。契約表の表定義を表3に,料金プラン表の表定義を表4に示す。

表3及び表4のデータ型欄には,適切なデータ型,適切な長さ,精度,位取りを記入する。PK欄は主キー制約,UK欄はUNIQUE制約,非NULL欄は非NULL制約の指定をするかどうかを記入する。指定する場合にはYを,指定しない場合にはNを記入する。ただし,主キーに対してはUNIQUE制約を指定せず,非NULL制約は指定するものとする。

表3 契約表の表定義

項番列名データ型PKUK非NULL初期値アクセス制御その他の指定内容
1契約IDCHAR(8)[g][h][i]上長(ユーザアカウント名:ADMIN)による参照が必要(省略)
2料金プランコードCHAR(8)NNY料金プラン表への外部キー
3回線番号CHAR(13)NNY(省略)
4内線電話番号CHAR(11)NNNNULL(省略)
5暗証番号CHAR(4)NNY上長(ユーザアカウント名:ADMIN)による参照が必要(省略)

表4 料金プラン表の表定義

項番列名データ型PKUK非NULL初期値アクセス制御その他の指定内容
1料金プランコードCHAR(8)[g][h][i](省略)
2通信事業者コードCHAR(4)NNY1234通信事業者表への外部キー。行挿入時に,初期値としてL社の通信事業者コード'1234'を設定する。
3料金プラン名VARCHAR(30)NNY(省略)
4基本料金DECIMAL(5,0)NNY(省略)
5通話単価DECIMAL(5,2)NNY(省略)
6通信単価DECIMAL(5,4)NNY(省略)

〔表の作成とアクセス制御〕

Kさんは,実装に必要な各種SQL文を表定義に基づいて作成した。表3のアクセス制御を設定するためのSQL文を図2に,表4の料金プラン表を作成するためのSQL文を図3に示す。なお,運用管理担当者のユーザアカウントに対しては適切なアクセス制御が設定されているものとする。

図2 表3のアクセス制御を設定するためのSQL文

GRANT [j] ON 契約 TO ADMIN

図3 表4の料金プラン表を作成するためのSQL文

CREATE TABLE 料金プラン
(料金プランコード CHAR(8) NOT NULL,
通信事業者コード [k],
料金プラン名 VARCHAR(30) NOT NULL,
基本料金 DECIMAL(5,0) NOT NULL,
通話単価 DECIMAL(5,2) NOT NULL,
通信単価 DECIMAL(5,4) NOT NULL,
[l](料金プランコード),
[m](通信事業者コード) REFERENCES 通信事業者(通信事業者コード))

設問1

図1中の[a]~[f]に入れる適切なエンティティ間の関連及び属性名を答え,E-R図を完成させよ。なお,エンティティ間の関連及び属性名の表記は,図1の凡例に倣うこと。

設問2

表3,表4中の[g]~[i]に入れる適切な字句の組合せを解答群の中から選び,記号で答えよ。

解答群

記号ghi
アNNN
イNNY
ウNYN
エNYY
オYNY
カYYY

設問3

図2,図3中の[j]~[m]に入れる適切な字句又は式を答えよ。

模範解答

設問1 a 年月,b ↑,c 従業員ID,d 情報端末ID(c,dは順不同),e ↓,f 部署の自己参照の関連(1対多の折返し矢印)

設問2 オ

設問3 j SELECT(契約ID,暗証番号),k CHAR(4) DEFAULT '1234' NOT NULL,l PRIMARY KEY,m FOREIGN KEY

解説

設問1では,E-R図の空欄を問う。[a]は表2の項番2の要件にある請求を年月ごとに管理するための年月である。[b]は契約と情報端末との間の関連であり,一つの契約に対して2年ごとの交換で複数の情報端末が対応するので,情報端末側を多とする1対多の関連(↑)である。[c],[d]は利用エンティティの外部キーであり,従業員IDと情報端末IDである(順不同)。[e]は利用とアプリ追加との間の関連であり,アプリ追加側を多とする1対多の関連(↓)である。[f]は部署の階層構造を表す自己参照の関連である。

設問2では,主キーである契約ID及び料金プランコードの制約指定を問う。主キーなのでPKはY,主キーにはUNIQUE制約を指定しないのでUKはN,主キーには非NULL制約を指定するので非NULLはYであり,組合せはオである。

設問3では,SQL文の空欄を問う。[j]は上長(ADMIN)に契約IDと暗証番号の参照だけを許可する列レベル権限であり,SELECT(契約ID,暗証番号)である。[k]は通信事業者コードの列定義であり,CHAR(4) DEFAULT '1234' NOT NULLである。[l]は料金プランコードの主キー制約であり,PRIMARY KEYである。[m]は通信事業者コードの外部キー制約であり,FOREIGN KEYである。

関連問題

本文が同じ・似ている問題を表示しています