スマートデバイス管理システムのデータベース設計に関する次の記述を読んで,設問に答えよ。
J社は,グループ連結で従業員約3万人を抱える自動車メーカーである。従来は事業継続性・災害時対応施策の一環として,本社の部長職以上にスマートフォン及びタブレットなどのスマートデバイス(以下,情報端末という)を貸与していた。昨今の働き方改革の一環として,従業員全員がいつでもどこでも作業できるようにするために,情報端末の配布対象をグループ企業も含む全従業員に拡大することになった。
現在は情報端末の貸与先が少人数なので,表計算ソフトでスマートデバイス管理台帳(以下,管理台帳という)を作成して貸与状況などを管理している。今後は貸与先が3万人を超えるので,スマートデバイス管理システム(以下,新システムという)を新たに構築することになった。情報システム部門のKさんは,新システムのデータ管理者として,新システム構築プロジェクトに参画した。
〔現在の管理台帳〕
現在の管理台帳の項目を表1に示す。管理台帳は,一つのワークシートで管理されている。
項目名 | 説明 | 記入例 |
情報端末ID | 情報端末ごとに一意に付与される固有の識別子 | G6TF809G0D4Q |
機種名 | 情報端末の機種の型名 | 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 | 契約ごとに異なる暗証番号を設定することで利用者による不正な契約変更の防止を図っているが,運用管理担当者は全ての契約の暗証番号を自由に参照できてしまうので,運用管理担当者による不正な契約変更が発生するリスクが残っている。 | 暗証番号は運用管理担当者の上長(以下,上長という)しか参照できないようにアクセスを制御する。運用管理担当者は契約変更が必要な都度,上長に申請し,上長が契約変更を行う仕組みにする。 |
Kさんは,表1の管理台帳の項目と表2のヒアリング結果を基に,新システムのE-R図を作成した。E-R図(抜粋)を図1に示す。なお,J社内の部署の階層構造は,自己参照の関連を用いて表現する。
図1 新システムのE-R図(抜粋)
〔表定義〕
このデータベースでは,E-R図のエンティティ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理する。Kさんは,図1のE-R図を実装するために,詳細設計として表定義の内容を検討した。契約表の表定義を表3に,料金プラン表の表定義を表4に示す。
表3及び表4のデータ型欄には,適切なデータ型,適切な長さ,精度,位取りを記入する。PK欄は主キー制約,UK欄はUNIQUE制約,非NULL欄は非NULL制約の指定をするかどうかを記入する。指定する場合にはYを,指定しない場合にはNを記入する。ただし,主キーに対してはUNIQUE制約を指定せず,非NULL制約は指定するものとする。
項番 | 列名 | データ型 | PK | UK | 非NULL | 初期値 | アクセス制御 | その他の指定内容 |
1 | 契約ID | CHAR(8) | g | h | i | 上長(ユーザーアカウント名:ADMIN)による参照が必要 | (省略) | |
2 | 料金プランコード | CHAR(8) | 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 | (省略) |
Kさんは,実装に必要な各種SQL文を表定義に基づいて作成した。表3のアクセス制御を設定するためのSQL文を図2に,表4の料金プラン表を作成するためのSQL文を図3に示す。なお,運用管理担当者のユーザーアカウントに対しては適切なアクセス制御が設定されているものとする。
GRANT j ON 契約 TO ADMIN
図2 表3のアクセス制御を設定するためのSQL文
CREATE TABLE 料金プラン
(料金プランコード CHAR(8) NOT NULL,
通信事業者コード k ,
料金プラン名 VARCHAR(30) NOTNULL,
基本料金 DECIMAL(5,0) NOT NULL,
通話単価 DECIMAL(5,2) NOT NULL,
通信単価 DECIMAL(5,4) NOT NULL,
l (料金プランコード),
m (通信事業者コード) REFERENCES 通信事業者(通信事業者コード))
図3 表4の料金プラン表を作成するためのSQL文
出典:令和4年度秋期 問6
設問1 図1中の a 〜 f に入れる適切なエンティティ間の関連及び属性名を答え,E-R図を完成させよ。なお,エンティティ間の関連及び属性名の表記は,図1の凡例に倣うこと。
解答・解説
解答例
a:年月
b:↑
c:従業員ID
d:情報端末ID
e:↓
f:↩︎
解説
設問2 表3,表4中の g 〜 i に入れる適切な字句の組合せを解答群の中から選び,記号で答えよ。
記号 | g | h | i |
ア | N | N | N |
イ | N | N | Y |
ウ | N | Y | N |
エ | N | Y | Y |
オ | Y | N | Y |
カ | Y | Y | Y |
解答・解説
解答例
オ
解説
設問3 図2,図3中の j 〜 m に入れる適切な字句又は式を答えよ。
解答・解説
解答例
j:SELECT(契約ID, 暗証番号)
k:CHAR(4 )DEFAULT '1234' NOT NULL
l:PRIMARY KEY
m:FOREIGN KEY
解説
IPA公開情報
出題趣旨
近年,働き方改革及びリモートワークの普及に伴い,企業におけるスマートデバイスの活用が増えている。
本問では,スマートデバイス管理システムを題材に,E-R図や表定義,SQL文(データ定義言語,データ制御言語)の基本的な理解と,関係モデルを設計・実装する能力を問う。
採点講評
問6では,スマートデバイス管理システムを題材に,E-R図や表定義,SQL文について出題した。全体として正答率はやや低かった。
設問1のaは,正答率がやや低かった。“部署ID”と誤って解答した受験者が多かった。請求を部署ごとだけではなく年月ごとでも管理するという要件の理解が不十分であると考えられる。落ち着いて要件を把握するよう心掛けてもらいたい。
設問1のbは,正答率がやや高かった。一方で,情報端末と契約の関係を1対1とする解答が散見された。同一の回線番号のままで定期的に情報端末を交換するという管理方法が示す意味の理解が不十分であることの結果と思われる。関連のカーディナリティを正しく捉えて表現することはデータベースの概念設計を行う上で重要なので,注意深く読んで,正答を導き出してほしい。
設問3は,正答率が低かった。GRANT文は表に対する権限を付与するために使用される基本的なDCL(データ制御言語)であり,またCREATE TABLE文は表を作成するために使用される基本的なDDL(データ定義言語)である。いずれも基本的なSQL文であり,構文を覚えておいてほしい。