応用情報技術者 2022年 秋期 午後 問06
スマートデバイス管理システムのデータベース設計に関する次の記述を読んで、設問に答えよ。
J社は、グループ連結で従業員約3万人を抱える自動車メーカーである。従来は事業継続性・災害時対応施策の一環として、本社の部長職以上にスマートフォン及びタブレットなどのスマートデバイス（以下、情報端末という）を貸与していた。昨今の働き方改革の一環として、従業員全員がいつでもどこでも作業できるようにするために、情報端末の配布対象をグループ企業も含む全従業員に拡大することになった。
現在は情報端末の貸与先が少人数なので、表計算ソフトでスマートデバイス管理台帳（以下、管理台帳という）を作成して貸与状況などを管理している。今後は貸与先が3万人を超えるので、スマートデバイス管理システム（以下、新システムという）を新たに構築することになった。情報システム部門のKさんは、新システムのデータ管理者として、新システム構築プロジェクトに参画した。
〔現在の管理台帳〕
現在の管理台帳の項目を表1に示す。管理台帳は、一つのワークシートで管理されている。
〔現在の管理方法における課題と新システムに対する要件〕
Kさんは、新システムの設計に際して、まず、現在の情報端末の運用について、運用管理担当者に対して課題と新システムに対する要件をヒアリングした。ヒアリング結果を表2に示す。
〔新システムのE-R図〕
Kさんは、表1の管理台帳の項目と表2のヒアリング結果を基に、新システムのE-R図を作成した。E-R図（抜粋）を図1に示す。なお、J社内の部署の階層構造は、自己参照の関連を用いて表現する。
〔表定義〕
このデータベースでは、E-R図のエンティティ名を表名にし、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。Kさんは、図1のE-R図を実装するために、詳細設計として表定義の内容を検討した。契約表の表定義を表3に、料金プラン表の表定義を表4に示す。
表3及び表4のデータ型欄には、適切なデータ型、適切な長さ、精度、位取りを記入する。PK欄は主キー制約、UK欄はUNIQUE制約、非NULL欄は非NULL制約の指定をするかどうかを記入する。指定する場合にはYを、指定しない場合にはNを記入する。ただし、主キーに対してはUNIQUE制約を指定せず、非NULL制約は指定するものとする。
〔表の作成とアクセス制御〕
Kさんは、実装に必要な各種SQL文を表定義に基づいて作成した。表3のアクセス
設問1：
図中のa〜fに入れる適切なエンティティ間の関連及び属性名を答え、E-R図を完成させよ。なお、エンティティ間の関連及び属性名の表記は、図1の凡例に倣うこと（cとdは順不同）。
模範解答
a：年月
b：↑
c：従業員ID
d：情報端末ID
e：↓
f：↩︎
解説
解答の論理構成
-
a の検討
- 要件②には「従業員の異動情報に基づいて請求を年月ごと、部署ごとに管理できるようにする。」とあります。
- 請求をまとめる最小粒度は “年月” であり、主キー候補にもなるので属性 a は「年月」と判断できます。
-
b 情報端末―契約の関連方向
- 説明欄には「同一の回線番号のままで2年ごとに旧情報端末から新情報端末への交換を行っており」とあります。
- “回線番号” は契約に属するため、1つの契約に対して複数台の情報端末が紐付きます。
- したがって多側（情報端末）から片側（契約）へ向けて矢印を書くので b は「↑」となります。
-
c、d 利用エンティティの外部キー
- 利用者と端末の組合せを管理するために、利用は「従業員」と「情報端末」の双方に従属します。
- よって c は「従業員ID」、d は「情報端末ID」です（順序はどちらでも可）。
-
e 利用―アプリ追加の関連方向
- 1回の利用（従業員✕端末）に対して複数のアプリ追加申請が出せる、という運用要件③より、 多側であるアプリ追加から利用に向けて矢印を書きます。
- したがって e は「↓」です。
-
f 請求―部署の関連方向
- 要件②より「請求を…部署ごとに管理できるようにする。」
- 1つの部署に毎月複数の請求レコードが発生するため、請求（多）→部署（1）の矢印を示します。
- ER 図の表記では多側から1側へ折れ返る矢印を使うので f は「↩︎」となります。
以上より模範解答と同じ結果になります。
誤りやすいポイント
- 交換ルールを読み落として「1契約＝1端末」と誤認し、b の矢印方向を逆にしてしまう。
- 「年月」を単なる日付型と捉え、属性名を「請求日」などと書き換えてしまう。数字や固有名詞は必ず原文どおりに記述する必要があります。
- 利用エンティティに従業員ID・情報端末IDの両方が必要である理由を忘れ、どちらか一方だけを入れてしまう。
- 部署単位での請求管理という要件を見落とし、請求―部署間を1対1と誤解する。
FAQ
Q: 「年月」は DATE 型ではいけませんか？
A: 請求を月単位で集計することが要件なので、YYYYMM のように日を含めない形式が望ましいです。DATE 型でも月切り替え処理で対応可能ですが、ER図の属性名は要件に合わせて「年月」とします。
A: 請求を月単位で集計することが要件なので、YYYYMM のように日を含めない形式が望ましいです。DATE 型でも月切り替え処理で対応可能ですが、ER図の属性名は要件に合わせて「年月」とします。
Q: 情報端末と契約が1対1になるケースはありませんか？
A: 物理的にはありますが、運用方針で「2年ごとに端末を交換しても回線番号（＝契約）は保持する」と定義されています。設計は運用ルールを優先して多対1とします。
A: 物理的にはありますが、運用方針で「2年ごとに端末を交換しても回線番号（＝契約）は保持する」と定義されています。設計は運用ルールを優先して多対1とします。
Q: アプリ追加と利用が多対多ではないのですか？
A: 申請は「従業員✕端末」という利用単位で出され、1つのアプリ追加行は必ず1つの利用に紐付きます。よって多対1（アプリ追加→利用）になります。
A: 申請は「従業員✕端末」という利用単位で出され、1つのアプリ追加行は必ず1つの利用に紐付きます。よって多対1（アプリ追加→利用）になります。
関連キーワード: E-R図、主キー、外部キー、カーディナリティ、アクセス制御
設問2：
表3、表4中のg〜jに入れる適切な字句の組合せを解答群の中から選び、記号で答えよ。
模範解答
オ
解説
解答の論理構成
- 主キー列であることの確認
表3の「契約ID」、表4の「料金プランコード」はどちらも列名の下線が実線で描かれており、さらに説明欄に「主キー制約、UNIQUE制約、非NULL制約の指定をするかどうかを記入する」とあるため、両列は明確に主キーです。 - 主キーに課すべき制約の条件
問題文には「ただし、主キーに対してはUNIQUE制約を指定せず、非NULL制約は指定するものとする。」と明記されています。
したがって
・PK（主キー制約） → “Y”
・UK（UNIQUE制約） → “N”（主キーなので自動的に一意性が保証されるため、重複指定は不可）
・非NULL → “Y”（主キーはNULLを許容しない） - 解答群との照合
解答群のうち
「オ g＝Y、h＝N、i＝Y」
が上記3条件をすべて満たしています。 - よって、正しい組合せは「オ」となります。
誤りやすいポイント
- 「主キーならUNIQUEも付けるはず」と思い込み、h に “Y” を入れてしまう。主キー制約自体が一意性を保証するので重複指定は不要です。
- 主キーだから非NULLは暗黙に付くと考え、i を “N” にしてしまう。問題文は “非NULL制約は指定する” と明示しているため、i＝“Y” を記入しなければ減点対象です。
- g／h／i の3つを列ごとに変えてしまう。設問は「列1も列2も同じ主キー構成」という前提なので、両方とも同じ値になります。
FAQ
Q: UNIQUE 制約を重ねてもエラーにならない DB もありますが、指定しない方が良いのですか？
A: はい。本問では「主キーに対してはUNIQUE制約を指定しない」と要件で定めています。要件に従うことが優先です。
A: はい。本問では「主キーに対してはUNIQUE制約を指定しない」と要件で定めています。要件に従うことが優先です。
Q: 非NULL は PK を “Y” にしたら自動的に付く実装もあります。明示は必要ですか？
A: 本問は「非NULL制約は指定するもの」と指示しているため、明示的に “Y” を記入します。
A: 本問は「非NULL制約は指定するもの」と指示しているため、明示的に “Y” を記入します。
Q: g/h/i を列ごとに違えても採点されますか？
A: 採点は各列個別ではなく「組合せ」で判定されます。いずれか1つでも誤れば誤答になりますので注意してください。
A: 採点は各列個別ではなく「組合せ」で判定されます。いずれか1つでも誤れば誤答になりますので注意してください。
関連キーワード: 主キー、UNIQUE制約、非NULL制約、データベース設計、制約定義
設問3：
図2、図3中のj〜mに入れる適切な字句又は式を答えよ。
模範解答
j：SELECT(契約ID, 暗証番号)
k：CHAR(4) DEFAULT '1234' NOT NULL
l：PRIMARY KEY
m：FOREIGN KEY
解説
解答の論理構成
- 【問題文】の表3で、列「契約ID」と列「暗証番号」のいずれも“上長（ユーザーアカウント名：ADMIN）による参照が必要”と指示されている。この2列だけを ADMIN に参照させればよいので、図2の GRANT 文は
GRANT SELECT(契約ID, 暗証番号) ON 契約 TO ADMIN
となり、j は SELECT(契約ID, 暗証番号) である。 - 表4の列「通信事業者コード」には“行挿入時に、初期値としてL社の通信事業者コード ’1234’ を設定する。”とある。また UK や PK 指定はなく非NULL 指定が Y。したがってデータ型は CHAR(4) に固定し、DEFAULT '1234' NOT NULL を付与するので k は CHAR(4) DEFAULT '1234' NOT NULL。
- 図3では列「料金プランコード」に主キー制約を付ける箇所 l と、列「通信事業者コード」に外部キー制約を付ける箇所 m が示されている。表4のPK列は「料金プランコード」、外部キーは「通信事業者表への外部キー」と明記されているため、 l は PRIMARY KEY、m は FOREIGN KEY となる。
誤りやすいポイント
- GRANT 文で列名を列挙する際に 半角カンマ直後の空白を忘れる と構文エラーになる DBMS があります。
- デフォルト値 '1234' を VARCHAR で定義してしまい、桁長が合わず余計な空白が入るケース。問題では CHAR(4) が要求されています。
- PRIMARY KEY と UNIQUE を 同時に付けてはいけないという注意書きを見落とし、重複指定してしまうミス。
- FOREIGN KEY 制約は列名と参照先をセットで書く必要があります。 m 部分だけを書くときでも REFERENCES 句が続くことを念頭に置くと混乱しません。
FAQ
Q: 列単位で権限を与える場合、GRANT SELECT の後に列名を付けないとどうなりますか？
A: 列名を省略するとテーブル全体の参照権限が付与され、運用要件「暗証番号は上長しか参照できない」を満たせなくなります。
A: 列名を省略するとテーブル全体の参照権限が付与され、運用要件「暗証番号は上長しか参照できない」を満たせなくなります。
Q: DEFAULT '1234' と NOT NULL の順序は決まっていますか？
A: ANSI SQL ではどちらが先でも構いませんが、実務ではベンダーのリファレンス例に合わせるのが無難です。本問の解答では DEFAULT '1234' NOT NULL とします。
A: ANSI SQL ではどちらが先でも構いませんが、実務ではベンダーのリファレンス例に合わせるのが無難です。本問の解答では DEFAULT '1234' NOT NULL とします。
Q: PRIMARY KEY を付けると自動的に NOT NULL と UNIQUE が付与されますか？
A: はい。従って問題文の指示通り、主キー列には追加で UNIQUE 制約を記述する必要はありません。
A: はい。従って問題文の指示通り、主キー列には追加で UNIQUE 制約を記述する必要はありません。
関連キーワード: GRANT文、デフォルト値、主キー制約、外部キー制約、列レベル権限
