令和元年秋期試験午後問題 問6
問6 データベース
⇱問題PDF
健康応援システムの構築に関する次の記述を読んで，設問1～3に答えよ。
健康応援システムの構築に関する次の記述を読んで，設問1～3に答えよ。
広告
W社は，ソフトウェアパッケージの開発を行う企業である。デスクワークが多いことから，従業員が生活習慣病に陥る比率が高く問題となっていた。そこでW社の人事部では，従業員の健康増進のために，通信機能をもつ体重計と，歩数や睡眠時間を記録するリストバンド型活動量計(以下，リストバンドという)を配布し，そのデータを活用する健康応援システム(以下，本システムという)を構築することになった。
〔本システムのシステム構成〕
本システムは，次の二つのサブシステムから構成される。
本システムでは，従業員の日々の体重や歩数，睡眠時間などを記録して，その推移を可視化する。さらに，従業員間で記録を競わせるイベントを開催することで，従業員の積極的な利用を狙う。その機能概要は次のとおりである。
このデータベースでは，E-R図のエンティテイ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
〔月次レポート作成機能の実装〕
月次レポートを作成する処理手順を次に示す。
なお，関数 COALESCE(A，B) は，AがNULLでないときはAを，AがNULLのときはBを返す。関数 TOYM は，年月日を年月に変換する関数である。
〔データ連携機能の不具合〕
リストバンドに記録された睡眠データを用いてデータ連携機能のテストを行ったところ，睡眠データの登録処理でエラーが発生した。その際に用いたデータを図4に示す。
なお，この睡眠データはCSV形式で，先頭行はヘッダーである。
まず，睡眠データの登録処理を確認したところ，その処理では，睡眠データの各行を順次取り出して，ヘッダーと同名の睡眠表の各列に値をセットし，1行ずつ睡眠表に挿入していた。
次に，睡眠データを調査したところ，二つの想定外のパターンが判明した。
一つ目は，今回のエラーの原因ではなかったが，就寝中にリストバンドが外れてしまい睡眠終了日時が取得できないパターンで，このパターンに対応するために月次レポート作成機能を修正した。
二つ目が①今回のエラーを引き起こしたパターンで，このエラーを回避して全ての睡眠データを登録するために，②ある表に列の追加以外の変更を加え，月次レポート作成機能を修正することで，今回のエラーを解消することができた。
〔本システムのシステム構成〕
本システムは，次の二つのサブシステムから構成される。
- 健康応援データサービス
本システムのデータを管理するプログラム。各データを登録・更新・削除するためのインタフェースと定期的にデータを集計する機能をもつ。 - 健康応援スマホアプリ
スマートフォン用のアプリケーションプログラム。体重計やリストバンドとデータ通信を行い，健康応援データサービスとデータ連携させる機能をもつ。
本システムでは，従業員の日々の体重や歩数，睡眠時間などを記録して，その推移を可視化する。さらに，従業員間で記録を競わせるイベントを開催することで，従業員の積極的な利用を狙う。その機能概要は次のとおりである。
- 手動データ登録機能
電子メールアドレスや身長をスマートフォンの画面から登録する。 - データ連携機能
体重計やリストバンドから取得したデータを登録する。 - データ公開機能
身長や体重などのそれぞれの情報について，自分以外の従業員にも閲覧を許可する場合，公開情報として設定する。 - 月次レポート作成機能
毎月，従業員ごとのBMI(肥満度を表す体格指数)と肥満度判定，月間総歩数，平均睡眠時間を集計する。 - 歩数対抗戦イベント
部署ごとの従業員一人当たリ平均の月間総歩数を競う。
このデータベースでは，E-R図のエンティテイ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
月次レポートを作成する処理手順を次に示す。
- 月次レポート表に従業員番号と集計する対象年月だけがセットされたレコードを挿入する。
- (1)で挿入したレコードについて，次の処理を行う。
- 身長と体重を，最新の測定値で更新する。
- BMIを算出して更新する。
- BMIから肥満度を判定してその結果を更新する。
- 対象年月の月間総歩数を集計して更新する。
- 対象年月の睡眠時間を集計して1日当たりの平均睡眠時間を求め，その値で更新する。
なお，関数 COALESCE(A，B) は，AがNULLでないときはAを，AがNULLのときはBを返す。関数 TOYM は，年月日を年月に変換する関数である。
リストバンドに記録された睡眠データを用いてデータ連携機能のテストを行ったところ，睡眠データの登録処理でエラーが発生した。その際に用いたデータを図4に示す。
なお，この睡眠データはCSV形式で，先頭行はヘッダーである。
次に，睡眠データを調査したところ，二つの想定外のパターンが判明した。
一つ目は，今回のエラーの原因ではなかったが，就寝中にリストバンドが外れてしまい睡眠終了日時が取得できないパターンで，このパターンに対応するために月次レポート作成機能を修正した。
二つ目が①今回のエラーを引き起こしたパターンで，このエラーを回避して全ての睡眠データを登録するために，②ある表に列の追加以外の変更を加え，月次レポート作成機能を修正することで，今回のエラーを解消することができた。
広告
設問1
図1中のa～dに入れる適切なエンテイティ間の関連及び属性名を答え，E-R図を完成させよ。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください)
なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください)
解答例・解答の要点
a：→
b：部署番号
c：従業員番号
d：↓
b：部署番号
c：従業員番号
d：↓
解説
〔bについて〕E-R図では、部署エンティティと従業員エンティティの間に関連が示されていますが、従業員エンティティには2つのエンティティを関連付ける属性がありません。このため、従業員エンティティ側に所属する部署を表す属性が存在すると考えるのが自然です。
部署エンティティでは主キー"部署番号"により部署を一意に特定することから、[b]を"部署番号"とすればよいと考えられます。また、その場合従業員エンティティの属性"部署番号"は、部署エンティティの主キーを参照する外部キーです。したがって、[b]には「部署番号」が当てはまります。
∴b＝部署番号
〔aについて〕
1つの部署には複数の従業員が所属することが考えられます（つまり、従業員エンティティには同一の部署番号の値を持つ複数のレコードが存在しえるということです）。原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ（多重度）は「1対多」になります。
したがって、部署エンティティと従業員エンティティのカーディナリティは1対多であり、[a]には「→」が当てはまります。
∴a＝→
〔cについて〕
従業員エンティティと月次レポートエンティティのカーディナリティが1対多であること、従業員エンティティの主キーが"従業員番号"であることから、[c]には外部キーである"従業員番号"が入ると判断できます。
〔本システムの機能概要〕には、「・月次レポート公開機能…毎月，従業員ごとのBMI（中略）を集計する」とあり、月次レポートは月ごと、従業員ごとに作成するため、月次レポートエンティティの主キーは"レポート年月"だけでは不十分です。よって、（複合）主キーを"従業員番号"と"レポート年月"の組にして、レポートを一意に特定することができるようにしなくてはなりません。したがって、[c]には「従業員番号」が当てはまります。
∴c＝従業員番号
※E-R図の凡例より、属性が主キーかつ外部キーのときは下線のみを付けます。
〔dについて〕
従業員エンティティと公開エンティティには共通の属性"従業員番号"があります。この属性"従業員番号"は、公開エンティティ側では属性"公開情報番号"とともに（複合）主キーであり、従業員別の公開情報を一意に特定します。また、外部キーでもあり、1人の従業員が複数の公開情報（身長や体重など）をもつことが考えられます（つまり、公開エンティティには同一の従業員番号の値を持つ複数のレコードが存在しえるということです）。
したがって、従業員エンティティと公開エンティティのカーディナリティは1対多であり、[d]には「↓」が当てはまります。
∴d＝↓
広告
設問2
〔月次レポート作成機能の実装〕について，(1)，(2)に答えよ。
- 図2中のeに入れる適切な字句又は式を答えよ。
- 図3中のf，gに入れる適切な字句又は式を答えよ。
解答例・解答の要点
- e：SELECT 従業員番号， :レポート年月
- f：SUM(歩数.歩数)
g：歩数.従業員番号 = 月次レポート.従業員番号
解説
- 〔eについて〕〔月次レポート作成機能の実装〕の処理手順(1)は「月次レポート表に従業員番号と集計する対象年月だけがセットされたレコードを挿入する」です。集計する対象年月は埋込み変数":レポート年月"です。図2のSQL文ではFROM句があるので、SELECT句で指定された列などの値をレコードに乗せて月次レポート表に挿入することがわかります。
INSERT INTO句の（ ）で列名を指定した場合には、その列の値だけを指定すれば足ります。今回は（従業員番号，レポート年月）となっているので、2つの値をこの順番で指定します。したがって、[e]に当てはまるのは「SELECT 従業員番号, :レポート年月」です。
∴e＝SELECT 従業員番号, :レポート年月 - 〔fについて〕
〔月次レポート作成機能の実装〕の(2)④は「対象年月の月間総歩数を集計して更新する」機能です。
月間総歩数を求めるには、対象月に記録された歩数表の歩数列をSUM()で集計すると考えられます。従業員によっては、その月の歩数が1件も記録されていない（リストバンドを付けていない、リストバンドが故障した）ことが想定され、集計結果がNULLとなる場合があります。NULLの場合に0とみなせるように、COALESCE関数を用いる必要があります。したがって、[f]には「SUM(歩数.歩数)」が当てはまります。
∴f＝SUM(歩数.歩数)
〔gについて〕
月間総歩数を従業員ごとに求めるために適切な条件を記述する必要があります。月間総歩数は、月ごと、従業員ごとに集計する必要があるので、歩数表から- 月次レポート表のレポート年月列に属する測定日をもつ
- 月次レポート表の当該行の従業員番号列の値と一致する従業員番号をもつ
歩数表のレコードのうち従業員番号の値が月次レポートの従業員番号と一致するレコードを選択したいので、[g]には「歩数.従業員番号 = 月次レポート.従業員番号」が当てはまります。
∴g＝歩数.従業員番号 = 月次レポート.従業員番号
広告
設問3
〔データ連携機能の不具合〕について，(1)，(2)に答えよ。
解答例・解答の要点
- 1日に2回以上睡眠を取得するパターン (18文字)
- 表名：睡眠
変更内容：主キーを"従業員番号，睡眠開始日時"に変更する (23文字)
解説
- 図4の3番目と4番目のデータは、従業員番号と測定日の値の組が一致しています。他方で、睡眠表の（複合）主キーは"従業員番号"と"測定日"であり、睡眠表では従業員番号列と測定日列の値の組が重複するレコードは認められません。主キーが同一のレコードを登録しようとすると実行時エラーとなります。
図4を見ると、睡眠データの測定日には睡眠開始日時の日付部分が使われています。よって、データ登録時にエラーが発生するのは同一の日付のうちに2回睡眠を開始した場合です。答えとしては「1日に2回以上睡眠を取得するパターン」などが適切となります。
∴1日に2回以上睡眠を取得するパターン - 登録時にエラーが発生するような「ある従業員が同一の日付のうちに2回睡眠を開始したデータ」を睡眠表に追加できるようにするためには、（複合）主キーを"従業員番号"と"睡眠開始日時"に変更すれば、主キーのUNIQUE制約を満たし実現できます（同一人物が同一日時に睡眠を開始することなどあり得ないので）。したがって、変更を加えた表名は「睡眠」で、変更内容は「主キーを"従業員番号，睡眠開始日時"に変更する」です。
∴表名：睡眠
変更内容：主キーを"従業員番号，睡眠開始日時"に変更する
広告
広告