令和4年秋期試験午後問題 問6
問6 データベース
⇱問題PDF
スマートデバイス管理システムのデータベース設計に関する次の記述を読んで，設問に答えよ。
スマートデバイス管理システムのデータベース設計に関する次の記述を読んで，設問に答えよ。
広告
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のアクセス制御を設定するためのSQL文を図2に，表4の料金プラン表を作成するためのSQL文を図3に示す。なお，運用管理担当者のユーザーアカウントに対しては適切なアクセス制御が設定されているものとする。
現在は情報端末の貸与先が少人数なので，表計算ソフトでスマートデバイス管理台帳(以下，管理台帳という)を作成して貸与状況などを管理している。今後は貸与先が3万人を超えるので，スマートデバイス管理システム(以下，新システムという)を新たに構築することになった。情報システム部門のKさんは，新システムのデータ管理者として，新システム構築プロジェクトに参画した。
〔現在の管理台帳〕
現在の管理台帳の項目を表1に示す。管理台帳は，一つのワークシートで管理されている。
Kさんは，新システムの設計に際して，まず，現在の情報端末の運用について，運用管理担当者に対して課題と新システムに対する要件をヒアリングした。ヒアリング結果を表2に示す。
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のアクセス制御を設定するためのSQL文を図2に，表4の料金プラン表を作成するためのSQL文を図3に示す。なお，運用管理担当者のユーザーアカウントに対しては適切なアクセス制御が設定されているものとする。
広告
設問1
図1中のa～fに入れる適切なエンティティ間の関連及び属性名を答え，E-R図を完成させよ。なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください。)
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください。)
解答例・解答の要点
a：年月
b：↑
c：従業員ID
d：情報端末ID
e：↓
f：
b：↑
c：従業員ID
d：情報端末ID
e：↓
f：
解説
〔aについて〕請求エンティティは契約エンティティおよび部署エンティティと関連がありますが、既に関連付けのためのキーである「契約ID」「請求先ID」は記載されているため、当てはまるのは外部キー属性ではありません。
問題文より請求に関する情報として必要なものを探すと、表2項番2には「請求を年月ごと，部署ごとに管理できるようにする」という要件があります。請求エンティティには年月を表す属性がないので追加する必要があります。したがって、空欄aには「年月」が当てはまります。
∴a＝年月
〔bについて〕
情報端末エンティティと契約エンティティは、共通の属性「契約ID」で関連付けられています。この属性「契約ID」は、契約エンティティ側では主キーであり、契約を一意に特定します。一方、情報端末エンティティ側では、契約エンティティの主キー「契約ID」を参照する外部キーです。表1"交換予定日"の説明には「同一の回線番号のままで…旧情報端末から新情報端末への交換を行っており…」とあり、同一の契約のまま端末の交換が行われることがわかります。つまり、一つの契約エンティティに対して複数の情報端末エンティティが対応することになります。以上より、契約エンティティと情報端末エンティティのカーディナリティは1対多です。したがって、空欄bには「↑」が当てはまります。
※原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ(多重度)は「1対多」です。
∴b＝↑
〔cdについて〕
従業員エンティティと利用エンティティの間には1対多の関係があります。エンティティ同士は、同じ属性を主キーと外部キーに持たせることによって関連付けるため、"1"側のエンティティの主キーが"多"側のエンティティに外部キーとして存在する必要があります。
図1を見ると、"多"側である利用エンティティには"1"側の従業員エンティティの主キー「従業員ID」を参照する外部キー属性がないので、外部キーとして「従業員ID」を追加する必要があります（外部キーなので破線の下線を付けて解答します）。情報端末エンティティと利用エンティティの間にも1対多の関係があり、「情報端末ID」について同様のことが言えます。
したがって、空欄cには「従業員ID」、空欄dには「情報端末ID」が当てはまります（順不同）。
※一般にエンティティA,B間に関係があることがわかっている場合、エンティティAの主キーの属性がエンティティBでも存在すれば、エンティティBのその属性は外部キーであることが形式的に判断できます。
∴cd＝従業員ID、情報端末ID
〔eについて〕
利用エンティティとアプリ追加エンティティは、共通の属性「利用ID」で関連付けられています。この属性「利用ID」は、利用エンティティ側では主キーであり、利用を一意に特定します。一方、アプリ追加エンティティ側では、利用エンティティの主キー「利用ID」を参照する外部キーです。表2項番3には「アプリについては，…従業員と情報端末の組合せごとに個別に許可する」とあり、利用（従業員と情報端末の組合せ）に対して複数のアプリが追加されることは当然にあるものと考えられます。つまり、一つの利用エンティティに対して複数のアプリ追加エンティティが対応することになります。以上より、利用エンティティとアプリ追加エンティティのカーディナリティは1対多です。したがって、空欄eには「↓」が当てはまります。
※原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ(多重度)は「1対多」です。
∴e＝↓
〔fについて〕
〔新システムのE-R図〕には「J社内の部署の階層構造は，自己参照の関連を用いて表現する」とあります。部署エンティティには属性「上位部署ID」が外部キーとして存在していますが、これは別の部署エンティティの主キー「部署ID」を参照するものです。つまり、部署エンティティ同士の間にも関連があることになります。このように自己と同じエンティティに対する参照を「自己参照」といい、E-R図では自分自身に向けた矢印で表します。
J社のような大会社では、一つの上位部署に対して複数の下位部署が存在する階層構造になっていることが一般的であり、一つの部署エンティティに複数（0以上）の部署エンティティが対応することになります。以上より、部署エンティティの自己参照のカーディナリティは1対多とするのが適切です。したがって、空欄fには「
」が当てはまります。矢印の矢の位置は上下逆でも問題ありません。∴f＝
広告
設問2
表3，表4中のg～iに入れる適切な字句の組合せを解答群の中から選び，記号で答えよ。
解答群
解答例・解答の要点
オ
解説
〔g～iについて〕〔表定義〕後半の以下の記述に従って"Y"または"N"を決めます。
- PK欄は主キー制約，UK欄はUNIQUE制約，非NULL欄は非NULL制約の指定をするかどうかを記入する。指定する場合にはYを，指定しない場合にはNを記入する
- 主キーに対してはUNIQUE制約を指定せず，非NULL制約は指定するものとする
∴オ
広告
設問3
図2，図3中のj～mに入れる適切な字句又は式を答えよ。
解答例・解答の要点
j：SELECT(契約ID，暗証番号)
k：CHAR(4) DEFAULT '1234' NOT NULL
l：PRIMARY KEY
m：FOREIGN KEY
k：CHAR(4) DEFAULT '1234' NOT NULL
l：PRIMARY KEY
m：FOREIGN KEY
解説
〔jについて〕表2項番4には「暗証番号は…上長しか参照できないようにアクセスを制御する」という要件があり、その実装として、表3には"契約ID"列と"暗証番号"列に上長による参照のみを受け付けるアクセス制御が定義されることがわかります。
特定のユーザに表などのデータベースオブジェクトに対する権限を付与するためにはGRANT文を使用します。GRANT文の構文は以下のとおりです。
GRANT オブジェクトの権限 ON オブジェクト名
TO { ユーザ名 | ロール名 | PUBLIC }
[ WITH GRANT OPTION ]
権限にはSELECT・UPDATE・INSERT・DELETEなどから1つ以上（あるいはすべてを意味するALL PRIVILEGES）を記述することができますが、今回は「参照だけできるように」したいので参照権限を示すSELECTのみを付与します。また、参照できる列を限定したい場合、SELECTの直後に(列名1, 列名2, …)のように列のリストを記述することになります※。ADMIN（上長）に参照権限を付与すべき列は"契約ID"，"暗証番号"の2つなので、空欄jには「SELECT(契約ID, 暗証番号)」が当てはまります。TO { ユーザ名 | ロール名 | PUBLIC }
[ WITH GRANT OPTION ]
∴j＝SELECT(契約ID，暗証番号)
※試験が基準とするJIS X 3005-2:2015では、GRANT文中のオブジェクトの権限としてSELECT ( <権限列リスト> )形式で指定する方法が定義されています。
〔kについて〕
表4を見ると、"通信事業者コード"列には初期値'1234'と非NULL制約を設定しなければなりません。CREATE TABLE文では、以下の書式に従って列定義を行います。
<列名> <データ型又は定義域名> <DEFAULT句> <列制約定義>
- <列名> … 通信事業者コード
- <データ型又は定義域名> … CHAR(4)
- <DEFAULT句> … DEFAULT '1234'
- <列制約定義> … 非NULL制約 ⇒ NOT NULL
∴k＝CHAR(4) DEFAULT '1234' NOT NULL
〔lについて〕
"料金プランコード"列は主キーなので、主キー制約を設定しなければなりません。特定の列を主キーとして設定するには「PRIMARY KEY」句を使います。
PRIMARY KEY (列名)
∴i＝PRIMARY KEY〔mについて〕
"通信事業者コード"列は外部キーなので、外部キー制約を設定しなければなりません。特定の列を外部キーとして設定するには「FOREIGN KEY」句を使います。
FOREIGN KEY (列名) REFERENCES 外部表名 (参照する外部表の列名)
∴m＝FOREIGN KEY※PRIMARY KEY と FOREIGN KEY は空欄の穴埋めで出題される可能性があるので、書けるようになっておきましょう。
広告
広告