2022年 秋期 応用情報技術者試験 問6
スマートデバイス管理システムのデータベース設計
J社は，グループ連結で従業員約3万人を抱える自動車メーカーである。従来は事業継続性・災害時対応施策の一環として，本社の部長職以上にスマートフォン及びタブレットなどのスマートデバイス（以下，情報端末という）を貸与していた。昨今の働き方改革の一環として，従業員全員がいつでもどこでも作業できるようにするために，情報端末の配布対象をグループ企業も含む全従業員に拡大することになった。
現在は情報端末の貸与先が少人数なので，表計算ソフトでスマートデバイス管理台帳（以下，管理台帳という）を作成して貸与状況などを管理している。今後は貸与先が3万人を超えるので，スマートデバイス管理システム（以下，新システムという）を新たに構築することになった。情報システム部門のKさんは，新システムのデータ管理者として，新システム構築プロジェクトに参画した。
【現在の管理台帳】
現在の管理台帳の項目を表1に示す。管理台帳は，一つのワークシートで管理されている。
| 項目名 | 説明 | 記入例 |
|---|---|---|
| 情報端末ID | 情報端末ごとに一意に付与される固有の識別子 | 66TF809G0D40 |
| 機種名 | 情報端末の機種の型名 | IP12PM |
| 回線番号 | 契約に割り当てられた外線電話番号 | 080-0000-0000 |
| 内線電話番号 | 内線電話を情報端末で発着信できるように回線番号と紐づけられている内線電話の番号 | 1234-567890 |
| 通信事業者名 | 契約先の通信事業者の名称 | L社 |
| 料金プラン名 | 契約している料金プランの名称 | プランM |
| 暗証番号 | 契約の変更手続を行う際に必要となる番号 | 0000 |
| 利用者所属部署名 | 利用者が所属する部署の名称 | N部 |
| 利用者氏名 | 利用者の氏名 | 試験 太郎 |
| 利用者メールアドレス | 利用者への業務連絡が可能なメールアドレス | shiken.taro@example.co.jp |
| 利用開始日 | J社の情報端末の運用管理担当者（以下，運用管理担当者という）から利用者に対して情報端末を払い出した日 | 2020-09-10 |
| 項目名 | 説明 | 記入例 |
|---|---|---|
| 利用終了日 | 利用者から運用管理担当者に対して情報端末を返却した日 | 2022-09-10 |
| 交換予定日 | J社では情報セキュリティ対策の観点から同一の回線番号のままで2年ごとに旧情報端末から新情報端末への交換を行っており，新情報端末に交換する予定の日 | 2022-09-10 |
| 廃棄日 | 情報端末を廃棄事業者に引き渡した日 | 2022-10-20 |
【現在の管理方法における課題と新システムに対する要件】
Kさんは，新システムの設計に際して，まず，現在の情報端末の運用について，運用管理担当者に対して課題と新システムに対する要件をヒアリングした。ヒアリング結果を表2に示す。
| 項番 | 課題 | 要件 |
|---|---|---|
| 1 | 利用者が情報端末ごとに通信事業者や料金プランを選択できるので，結果として高い料金プランを契約して利用しているケースがある。 | 通信事業者を原則としてL社に統一し，かつ，より低価格の料金プランで契約できるようにする。 |
| 2 | 情報端末に関する費用は本社の総務部で一括して負担しており，利用状況が部長ごとに高額な請求が発生するケースがある。 | 従業員の費用情報に基づいて請求を年月ごとに管理できるようにする。 |
| 3 | 情報端末に対しては利用可能な機能やアプリケーションプログラム（以下，アプリという）に制限を設けており，利用者が制限を回避してアプリ追加などの依頼がある事象が発生している。従来は管理が煩雑となるので設けられない状況である。 | 業務上必要な機能やアプリについては，利用者に使用目的を確認し，従業員と情報端末の組み合わせごとに個別に許可できる仕組みにする。 |
| 4 | 契約ごとに異なる暗証番号を設定することで利用者による不正な契約変更の防止を図っているが，運用管理担当者による契約の暗証番号を自由に参照できてしまうので，運用管理担当者による不正な契約変更が発生するリスクが残っている。 | 暗証番号は運用管理担当者の上長（以下，上長という）しか参照できないようにアクセスを制御する。運用管理担当者は契約変更が必要な部署，上長に申請し，上長が契約変更を行う仕組みにする。 |
【新システムのE-R図】
Kさんは，表1の管理台帳の項目と表2のヒアリング結果を基に，新システムのE-R図を作成した。E-R図（抜粋）を図1に示す。なお，J社内の部署の階層構造は，自己参照の関連を用いて表現する。
【表定義】
このデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。Kさんは，図1のE-R図を実装するために，詳細設計として表定義の内容を検討した。契約表の表定義を表3に，料金プラン表の表定義を表4に示す。
| 項番 | 列名 | データ型 | PK | UK | 非NULL | 初期値 | アクセス制御 | その他の指定内容 |
|---|---|---|---|---|---|---|---|---|
| 1 | 契約ID | CHAR(8) | g | h | i | 上長（ユーザーアカウント名：ADMIN）による参照が必要 | （省略） | |
| 2 | 料金プランコード | CHAR(6) | N | N | Y | 料金プラン表への外部キー | ||
| 3 | 回線番号 | CHAR(13) | N | N | Y | （省略） | ||
| 4 | 内線電話番号 | CHAR(11) | N | N | N | NULL | （省略） | |
| 5 | 暗証番号 | CHAR(4) | N | N | Y | 上長（ユーザーアカウント名：ADMIN）による参照が必要 | （省略） |
| 項番 | 列名 | データ型 | PK | UK | 非NULL | 初期値 | アクセス制御 | その他の指定内容 |
|---|---|---|---|---|---|---|---|---|
| 1 | 料金プランコード | CHAR(8) | g | h | i | （省略） | ||
| 2 | 通信事業者コード | CHAR(4) | N | N | Y | 1234 | 通信事業者表への外部キー。行挿入時に，初期値としてL社の通信事業者コード'1234'を設定する。 | |
| 3 | 料金プラン名 | VARCHAR(30) | N | N | Y | （省略） | ||
| 4 | 基本料金 | DECIMAL(5,0) | N | N | Y | （省略） | ||
| 5 | 通話単価 | DECIMAL(5,2) | N | N | Y | （省略） | ||
| 6 | 通信単価 | DECIMAL(5,4) | N | N | Y | （省略） |
表3及び表4のデータ型欄には，適切なデータ型，適切な長さ，精度，位取りを記入する。PK欄は主キー制約，UK欄はUNIQUE制約，非NULL欄は非NULL制約の指定をするかどうかを記入する。指定する場合にはYを，指定しない場合にはNを記入する。ただし，主キーに対してはUNIQUE制約を指定せず，非NULL制約は指定するものとする。
【表の作成とアクセス制御】
Kさんは，実装に必要な各種SQL文を表定義に基づいて作成した。表3のアクセス制御を設定するためのSQL文を図2に，表4の料金プラン表を作成するためのSQL文を図3に示す。なお，運用管理担当者のユーザーアカウントに対しては適切なアクセス制御が設定されているものとする。
GRANT j ON 契約 TO ADMIN
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 通信事業者(通信事業者コード))
