令和4年度 秋期 応用情報技術者試験 午後 問6 スマートデバイス管理システムのE-R図と表定義
テクノロジデータベース
学習ガイド
自動車メーカーが情報端末の貸与を全従業員に広げるために構築する管理システムのデータベース設計です。管理台帳とヒアリング結果からE-R図の属性と関連を補い、部署の階層を自己参照の関連で表します。表定義の制約指定と、列単位のGRANT文やCREATE TABLE文の構文まで、要件がどのSQLに落ちるかをたどります。
この記事で押さえる論点
- 管理台帳の項目とヒアリング結果から、E-R図の欠けた属性（外部キー）と関連（1対多・自己参照）を導ける
- 部署の階層構造を上位部署IDによる自己参照の関連で表現できる
- 主キー・UNIQUE制約・非NULL制約の指定の組合せを、表定義のルールから判断できる
- GRANT文の列指定、CREATE TABLEのDEFAULT・NOT NULL・PRIMARY KEY・FOREIGN KEYの構文を書ける
出題情報
- 出題
- 2022(R4)秋 応用情報技術者 午後 問6
- 配点
- 20点満点
- 模範解答
- 公表(設問ごとに掲載)
出題趣旨・採点講評(IPA 公表)
近年，働き方改革及びリモートワークの普及に伴い，企業におけるスマートデバイスの活用が増えている。本問では，スマートデバイス管理システムを題材に，E-R図や表定義，SQL文（データ定義言語，データ制御言語）の基本的な理解と，関係モデルを設計・実装する能力を問う。
問6では，スマートデバイス管理システムを題材に，E-R図や表定義，SQL文について出題した。全体として正答率はやや低かった。
問題本文
スマートデバイス管理システムのデータベース設計に関する次の記述を読んで，設問に答えよ。
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 | 契約ごとに異なる暗証番号を設定することで利用者による不正な契約変更の防止を図っているが，運用管理担当者は全ての契約の暗証番号を自由に参照できてしまうので，運用管理担当者による不正な契約変更が発生するリスクが残っている。 | 暗証番号は運用管理担当者の上長（以下，上長という）しか参照できないようにアクセスを制御する。運用管理担当者は契約変更が必要な都度，上長に申請し，上長が契約変更を行う仕組みにする。 |
〔新システムのE-R図〕
Kさんは，表1の管理台帳の項目と表2のヒアリング結果を基に，新システムのE-R図を作成した。E-R図（抜粋）を図1に示す。なお，J社内の部署の階層構造は，自己参照の関連を用いて表現する。
図の説明テキスト
エンティティ、属性、およびリレーションを示すE-R図。
【凡例・注記】
- 実線：1対1、片矢印：1対多、両矢印：多対多を示す。
- 属性名の実線の下線は主キー、破線の下線は外部キーを示す。
- 主キーの実線が付いている属性名には、外部キーの破線を付けない。
【エンティティと属性】
- 請求：請求番号(主キー), 契約ID(外部キー), a, 請求先部署ID(外部キー), 基本料金, 通話料金, 通信料金, その他料金, 請求金額合計
- 情報端末：情報端末ID(主キー), 契約ID(外部キー), 機種名, 交換予定日, 廃棄日
- 契約：契約ID(主キー), 料金プランコード(外部キー), 回線番号, 内線電話番号, 暗証番号
- 料金プラン：料金プランコード(主キー), 通信事業者コード(外部キー), 料金プラン名, 基本料金, 通話単価, 通信単価
- 通信事業者：通信事業者コード(主キー), 通信事業者名
- 部署：部署ID(主キー), 部署名, 上位部署ID(外部キー)
- 機能制限解除：機能制限解除番号(主キー), 機能制限ID(外部キー), 利用ID(外部キー), 使用目的
- 機能制限：機能制限ID(主キー), 機能制限名, 制限内容
- 利用：利用ID(主キー), c, d, 利用開始日, 利用終了日
- 従業員：従業員ID(主キー), 所属部署ID(外部キー), 氏名, メールアドレス
- アプリ追加：アプリ追加番号(主キー), アプリID(外部キー), 利用ID(外部キー), 使用目的
- アプリ：アプリID(主キー), アプリ名, アプリ提供会社
【リレーションと図中の空欄】
- 情報端末から契約へ線が引かれており、その途中にbの枠がある。
- 契約から請求へ1対多の矢印。
- 料金プランから契約へ1対多の矢印。
- 通信事業者から料金プランへ1対多の矢印。
- 部署から請求へ線が引かれ、途中にfの枠があり、請求へ1対多の矢印。
- 機能制限から機能制限解除へ1対多の矢印。
- 利用から機能制限解除へ1対多の矢印。
- 従業員から利用へ1対多の矢印。
- 利用からアプリ追加へ線が引かれ、途中にeの枠があり、アプリ追加へ1対多の矢印。
- アプリからアプリ追加へ1対多の矢印。
- 従業員から部署へ1対多の矢印。
〔表定義〕
このデータベースでは，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
図の説明テキスト
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 通信事業者(通信事業者コード))
設問と解答・解説
空欄 aに入れる適切なエンティティ間の関連又は属性名を答えよ。
模範解答
年月
採点基準
- 2点: 要件を完全に理解し、正答を導いている。
- 1点: 概ね理解しているが、軽微な誤りがある。
- 1点: 部分的な理解に留まる。
- 0点: 理解が不十分であり、誤答となっている。
- 0点: 無解答、または全く関係のない記述。
- 0点: 指定された形式に従い、論理的に表現されている。
- 0点: 概ね論理的に表現されている。
- 0点: やや論理性に欠ける表現である。
- 0点: 論理性が不十分である。
- 0点: 論理的な表現が全くなされていない。
解説
解説
請求を部署ごとだけでなく年月ごとでも管理するという要件から、年月がエンティティ間の関連または属性として適切です。
高得点のポイント
請求情報の管理単位（年月ごと）を正確に把握できていること
問題文の要件に基づいてE-R図の属性を導出できていること
空欄 bに入れる適切なエンティティ間の関連又は属性名を答えよ。
模範解答
↑
採点基準
- 2点: 要件を完全に理解し、正答を導いている。
- 1点: 概ね理解しているが、軽微な誤りがある。
- 1点: 部分的な理解に留まる。
- 0点: 理解が不十分であり、誤答となっている。
- 0点: 無解答、または全く関係のない記述。
- 0点: 指定された形式に従い、論理的に表現されている。
- 0点: 概ね論理的に表現されている。
- 0点: やや論理性に欠ける表現である。
- 0点: 論理性が不十分である。
- 0点: 論理的な表現が全くなされていない。
解説
解説
情報端末と契約の関係を表す関連（カーディナリティ）を問う問題です。同一の回線番号のままで定期的に情報端末を交換するという管理方法から、1つの契約に対して複数の情報端末が関連付けられるため、情報端末側が「多」となります。
高得点のポイント
情報端末と契約の1対多の関係を正しく捉えていること
凡例に従い、カーディナリティの記号を適切に選択できていること
空欄 cに入れる適切なエンティティ間の関連又は属性名を答えよ。
模範解答
従業員ID
情報端末ID
採点基準
- 2点: 従業員ID または 情報端末ID（順不同。c・d の両方で同じ属性を答えていないこと）を、外部キーとして正しく導いている。
- 1点: 概ね理解しているが、軽微な誤りがある。
- 1点: 部分的な理解に留まる。
- 0点: 理解が不十分であり、誤答となっている。
- 0点: 無解答、または全く関係のない記述。
- 0点: 指定された形式に従い、論理的に表現されている。
- 0点: 概ね論理的に表現されている。
- 0点: やや論理性に欠ける表現である。
- 0点: 論理性が不十分である。
- 0点: 論理的な表現が全くなされていない。
解説
解説
「利用」エンティティは、表2の要件3「従業員と情報端末の組合せごとに個別に許可できる仕組みにする」を受けて、どの従業員がどの情報端末をいつからいつまで利用したかを表すエンティティです。図1では従業員から利用へ、利用から機能制限解除・アプリ追加へ1対多の矢印が引かれているため、利用は従業員と情報端末を参照する外部キーを持つ必要があります。したがって空欄 c・d には 従業員ID と 情報端末ID（いずれも外部キーなので破線の下線）が入ります。公式解答例では c・d は順不同です。
高得点のポイント
利用が「従業員と情報端末の組合せ」を表すエンティティであることを、表2の要件と図1の関連から読み取れていること
外部キーであることを凡例に倣って破線の下線で表せていること（属性名の正確さ: 従業員ID／情報端末ID）
空欄 dに入れる適切なエンティティ間の関連又は属性名を答えよ。
模範解答
従業員ID
情報端末ID
採点基準
- 2点: 従業員ID または 情報端末ID（順不同。c・d の両方で同じ属性を答えていないこと）を、外部キーとして正しく導いている。
- 1点: 概ね理解しているが、軽微な誤りがある。
- 1点: 部分的な理解に留まる。
- 0点: 理解が不十分であり、誤答となっている。
- 0点: 無解答、または全く関係のない記述。
- 0点: 指定された形式に従い、論理的に表現されている。
- 0点: 概ね論理的に表現されている。
- 0点: やや論理性に欠ける表現である。
- 0点: 論理性が不十分である。
- 0点: 論理的な表現が全くなされていない。
解説
解説
「利用」エンティティは、表2の要件3「従業員と情報端末の組合せごとに個別に許可できる仕組みにする」を受けて、どの従業員がどの情報端末をいつからいつまで利用したかを表すエンティティです。図1では従業員から利用へ、利用から機能制限解除・アプリ追加へ1対多の矢印が引かれているため、利用は従業員と情報端末を参照する外部キーを持つ必要があります。したがって空欄 c・d には 従業員ID と 情報端末ID（いずれも外部キーなので破線の下線）が入ります。公式解答例では c・d は順不同です。
高得点のポイント
利用が「従業員と情報端末の組合せ」を表すエンティティであることを、表2の要件と図1の関連から読み取れていること
外部キーであることを凡例に倣って破線の下線で表せていること（属性名の正確さ: 従業員ID／情報端末ID）
空欄 eに入れる適切なエンティティ間の関連又は属性名を答えよ。
模範解答
↓
採点基準
- 2点: 要件を完全に理解し、正答を導いている。
- 1点: 概ね理解しているが、軽微な誤りがある。
- 1点: 部分的な理解に留まる。
- 0点: 理解が不十分であり、誤答となっている。
- 0点: 無解答、または全く関係のない記述。
- 0点: 指定された形式に従い、論理的に表現されている。
- 0点: 概ね論理的に表現されている。
- 0点: やや論理性に欠ける表現である。
- 0点: 論理性が不十分である。
- 0点: 論理的な表現が全くなされていない。
解説
解説
エンティティ間の関連の向きとカーディナリティを示す記号として、1対多の「多」側を示す矢印の向きを決定します。
高得点のポイント
- 関連の方向性とカーディナリティを正確に表現できていること
空欄 fに入れる適切なエンティティ間の関連又は属性名を答えよ。
模範解答
部署から部署自身へ折り返す1対多の矢印（自己参照の関連。上位の部署から下位の部署へ向かう片矢印）
採点基準
- 1点: 部署から部署自身への自己参照の関連を、上位部署→下位部署の1対多（片矢印の折り返し）として正しく表している。
- 1点: 自己参照の関連であることは表しているが、多重度（1対多）や向きの表現に軽微な誤りがある。
- 0点: 部分的な理解に留まる。
- 0点: 理解が不十分であり、誤答となっている。
- 0点: 無解答、または全く関係のない記述。
- 0点: 指定された形式に従い、論理的に表現されている。
- 0点: 概ね論理的に表現されている。
- 0点: やや論理性に欠ける表現である。
- 0点: 論理性が不十分である。
- 0点: 論理的な表現が全くなされていない。
解説
解説
本文の〔新システムのE-R図〕に「J社内の部署の階層構造は，自己参照の関連を用いて表現する」とあり、部署エンティティは主キーの部署IDのほかに外部キーの上位部署IDを持っています。上位部署IDは同じ部署エンティティの部署IDを参照するので、部署から部署自身への関連（自己参照）を図に表す必要があります。1つの上位部署の下に複数の下位部署が属するため、関連は上位部署→下位部署の1対多です。
公式解答例では、空欄 f の位置に部署から出て部署自身に戻る折り返しの片矢印（凡例の「1対多」の矢印を同じエンティティに向けたもの）が描かれています。テキストで答える場合は「部署から部署への自己参照の1対多の関連」のように、参照先が部署自身であることと1対多の向きが分かるように記述してください。
高得点のポイント
上位部署IDが部署自身を参照する外部キーであることから、自己参照の関連だと導けていること
関連の多重度を、上位部署（1）→下位部署（多）の1対多として表せていること
設問1のaは，正答率がやや低かった。“部署ID”と誤って解答した受験者が多かった。請求を部署ごとだけではなく年月ごとでも管理するという要件の理解が不十分であると考えられる。落ち着いて要件を把握するよう心掛けてもらいたい。 設問1のbは，正答率がやや高かった。一方で，情報端末と契約の関係を1対1とする解答が散見された。同一の回線番号のままで定期的に情報端末を交換するという管理方法が示す意味の理解が不十分であることの結果と思われる。関連のカーディナリティを正しく捉えて表現することはデータベースの概念設計を行う上で重要なので，注意深く読んで，正答を導き出してほしい。
設問2
空欄 jに入れる字句又は式を答えよ。
模範解答
SELECT (契約ID, 暗証番号)
採点基準
- 2点: 要件を完全に理解し、正答を導いている。
- 1点: 概ね理解しているが、軽微な誤りがある。
- 1点: 部分的な理解に留まる。
- 0点: 理解が不十分であり、誤答となっている。
- 0点: 無解答、または全く関係のない記述。
- 0点: 指定された形式に従い、論理的に表現されている。
- 0点: 概ね論理的に表現されている。
- 0点: やや論理性に欠ける表現である。
- 0点: 論理性が不十分である。
- 0点: 論理的な表現が全くなされていない。
解説
解説
GRANT文を用いて、特定の列に対するアクセス権限を付与する構文です。表に対する権限付与では、特定の列のみを対象とする場合 GRANT 権限 (列名, ...) ON 表名 TO ユーザ のように指定します。
高得点のポイント
GRANT文における列指定の正しい構文を使用できていること
対象となる列（契約ID、暗証番号）を正しく選択できていること
空欄 kに入れる字句又は式を答えよ。
模範解答
CHAR(4) DEFAULT '1234' NOT NULL
採点基準
- 2点: 要件を完全に理解し、正答を導いている。
- 1点: 概ね理解しているが、軽微な誤りがある。
- 1点: 部分的な理解に留まる。
- 0点: 理解が不十分であり、誤答となっている。
- 0点: 無解答、または全く関係のない記述。
- 0点: 指定された形式に従い、論理的に表現されている。
- 0点: 概ね論理的に表現されている。
- 0点: やや論理性に欠ける表現である。
- 0点: 論理性が不十分である。
- 0点: 論理的な表現が全くなされていない。
解説
解説
CREATE TABLE文における列定義です。データ型を CHAR(4) とし、初期値を設定する DEFAULT '1234'、および必須入力とする NOT NULL 制約を付与する必要があります。
高得点のポイント
適切なデータ型とサイズを指定できていること
DEFAULT句およびNOT NULL制約を正しい構文で記述できていること
空欄 lに入れる字句又は式を答えよ。
模範解答
PRIMARY KEY
採点基準
- 2点: 要件を完全に理解し、正答を導いている。
- 1点: 概ね理解しているが、軽微な誤りがある。
- 1点: 部分的な理解に留まる。
- 0点: 理解が不十分であり、誤答となっている。
- 0点: 無解答、または全く関係のない記述。
- 0点: 指定された形式に従い、論理的に表現されている。
- 0点: 概ね論理的に表現されている。
- 0点: やや論理性に欠ける表現である。
- 0点: 論理性が不十分である。
- 0点: 論理的な表現が全くなされていない。
解説
解説
CREATE TABLE文において、表の主キーを定義するための制約として PRIMARY KEY を指定します。
高得点のポイント
- DDLにおける主キー制約のキーワードを正確に記述できていること
空欄 mに入れる字句又は式を答えよ。
模範解答
FOREIGN KEY
採点基準
- 2点: 要件を完全に理解し、正答を導いている。
- 1点: 概ね理解しているが、軽微な誤りがある。
- 1点: 部分的な理解に留まる。
- 0点: 理解が不十分であり、誤答となっている。
- 0点: 無解答、または全く関係のない記述。
- 0点: 指定された形式に従い、論理的に表現されている。
- 0点: 概ね論理的に表現されている。
- 0点: やや論理性に欠ける表現である。
- 0点: 論理性が不十分である。
- 0点: 論理的な表現が全くなされていない。
解説
解説
CREATE TABLE文において、他の表の主キーを参照する外部キーを定義するための制約として FOREIGN KEY を指定します。
高得点のポイント
- DDLにおける外部キー制約のキーワードを正確に記述できていること
設問3は，正答率が低かった。GRANT文は表に対する権限を付与するために使用される基本的なDCL（データ制御言語）であり，またCREATE TABLE文は表を作成するために使用される基本的なDDL（データ定義言語）である。いずれも基本的なSQL文であり，構文を覚えておいてほしい。