問題プレビューID: 84c6f4b0応用情報技術者試験 2019年(令和元年度)秋期 午後

問 6
応用情報技術者試験 2019年(令和元年度)秋期 午後

健康応援システムの構築に関する次の記述を読んで,設問1〜3に答えよ。

W社は,ソフトウェアパッケージの開発を行う企業である。デスクワークが多いことから,従業員が生活習慣病に陥る比率が高く問題となっていた。そこで W社の人事部では,従業員の健康増進のために,通信機能をもつ体重計と,歩数や睡眠時間を記録するリストバンド型活動量計(以下,リストバンドという)を配布し,そのデータを活用する健康応援システム(以下,本システムという)を構築することになった。

〔本システムのシステム構成〕

本システムは,次の二つのサブシステムから構成される。

・健康応援データサービス

 本システムのデータを管理するプログラム。各データを登録・更新・削除するためのインタフェースと定期的にデータを集計する機能をもつ。

・健康応援スマホアプリ

 スマートフォン用のアプリケーションプログラム。体重計やリストバンドとデータ通信を行い,健康応援データサービスとデータ連携させる機能をもつ。

〔本システムの機能概要〕

本システムでは,従業員の日々の体重や歩数,睡眠時間などを記録して,その推移を可視化する。さらに,従業員間で記録を競わせるイベントを開催することで,従業員の積極的な利用を狙う。その機能概要は次のとおりである。

・手動データ登録機能

 電子メールアドレスや身長をスマートフォンの画面から登録する。

・データ連携機能

 体重計やリストバンドから取得したデータを登録する。

・データ公開機能

 身長や体重などのそれぞれの情報について,自分以外の従業員にも閲覧を許可する場合,公開情報として設定する。

・月次レポート作成機能

 毎月,従業員ごとの BMI(肥満度を表す体格指数)と肥満度判定,月間総歩数,平均睡眠時間を集計する。

・歩数対抗戦イベント

 部署ごとの従業員一人当たり平均の月間総歩数を競う。

検討した健康応援データサービスで用いるデータベースの E-R 図を図1に示す。

このデータベースでは,E-R 図のエンティティ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理する。

図1 E-R 図

凡例 —:1 対 1 →:1 対多 ←→:多対多

エンティティと属性は次のとおりである(太字は原図の実線の下線(主キー)を表す)。

  • 部署(部署番号,部署名)
  • 従業員(従業員番号,従業員氏名,電子メールアドレス,[b])
  • 月次レポート([c],レポート年月,身長,体重,BMI,肥満度判定,月間総歩数,平均睡眠時間)
  • 身長(従業員番号,測定日時,身長)
  • 体重(従業員番号,測定日時,体重)
  • 歩数(従業員番号,測定日,歩数)
  • 睡眠(従業員番号,測定日,睡眠開始日時,睡眠終了日時)
  • 公開(従業員番号,公開情報番号)
  • 公開情報(公開情報番号,公開情報名)

エンティティ間の関連は次のとおりである。

  • 部署 [a] 従業員
  • 従業員 → 月次レポート(1 対多)
  • 従業員 → 身長(1 対多)
  • 従業員 → 体重(1 対多)
  • 従業員 → 歩数(1 対多)
  • 従業員 → 睡眠(1 対多)
  • 従業員 [d] 公開
  • 公開情報 → 公開(1 対多)

注記 属性名の実線の下線___は主キー,破線の下線-----は外部キーを示す。主キーの実線が付いている属性名には,外部キーの破線を付けない。

〔月次レポート作成機能の実装〕

月次レポートを作成する処理手順を次に示す。

(1) 月次レポート表に従業員番号と集計する対象年月だけがセットされたレコードを挿入する。

(2) (1)で挿入したレコードについて,次の処理を行う。

① 身長と体重を,最新の測定値で更新する。

② BMI を算出して更新する。

③ BMI から肥満度を判定してその結果を更新する。

④ 対象年月の月間総歩数を集計して更新する。

⑤ 対象年月の睡眠時間を集計して 1 日当たりの平均睡眠時間を求め,その値で更新する。

処理手順(1)及び(2)④で用いる SQL 文を,図2及び図3にそれぞれ示す。ここで,“:レポート年月” は,集計する対象年月を格納する埋込み変数である。

なお,関数 COALESCE(A, B)は,A が NULL でないときは A を,A が NULL のときは B を返す。関数 TOYM は,年月日を年月に変換する関数である。

図2 処理手順(1)で用いる SQL 文

INSERT INTO 月次レポート(従業員番号, レポート年月)
[e]
FROM 従業員

図3 処理手順(2)④で用いる SQL 文

UPDATE 月次レポート
SET 月間総歩数 =
    (SELECT COALESCE( [f] , 0)
     FROM 歩数
     WHERE [g]
     AND TOYM(歩数.測定日) = :レポート年月 )
WHERE レポート年月 = :レポート年月

〔データ連携機能の不具合〕

リストバンドに記録された睡眠データを用いてデータ連携機能のテストを行ったところ,睡眠データの登録処理でエラーが発生した。その際に用いたデータを図4に示す。

なお,この睡眠データは CSV 形式で,先頭行はヘッダである。

図4 睡眠データの登録処理で用いたデータ

”従業員番号”, ”測定日”, ”睡眠開始日時”, ”睡眠終了日時”
EMP00001, 2019-10-02, 2019-10-02 22:30:00, 2019-10-03 06:30:00
EMP00001, 2019-10-03, 2019-10-03 23:15:00, 2019-10-04 03:45:00
EMP00001, 2019-10-04, 2019-10-04 04:30:00, 2019-10-04 07:00:00
EMP00001, 2019-10-04, 2019-10-04 23:45:00, 2019-10-05 06:45:00
EMP00001, 2019-10-05, 2019-10-05 23:30:00,
EMP00001, 2019-10-06, 2019-10-06 22:30:00, 2019-10-07 05:45:00

まず,睡眠データの登録処理を確認したところ,その処理では,睡眠データの各行を順次取り出して,ヘッダと同名の睡眠表の各列に値をセットし,1 行ずつ睡眠表に挿入していた。

次に,睡眠データを調査したところ,二つの想定外のパターンが判明した。

一つ目は,今回のエラーの原因ではなかったが,就寝中にリストバンドが外れてしまい睡眠終了日時が取得できないパターンで,このパターンに対応するために月次レポート作成機能を修正した。

二つ目が①今回のエラーを引き起こしたパターンで,このエラーを回避して全ての睡眠データを登録するために,②ある表に列の追加以外の変更を加え,月次レポート作成機能を修正することで,今回のエラーを解消することができた。

設問1

図1中の[a]〜[d]に入れる適切なエンティティ間の関連及び属性名を答え,E-R 図を完成させよ。

なお,エンティティ間の関連及び属性名の表記は,図1の凡例に倣うこと。

設問2

〔月次レポート作成機能の実装〕について,(1),(2)に答えよ。

(1) 図2中の[e]に入れる適切な字句又は式を答えよ。

(2) 図3中の[f],[g]に入れる適切な字句又は式を答えよ。

設問3

〔データ連携機能の不具合〕について,(1),(2)に答えよ。

(1) 本文中の下線①のパターンとは,どのような睡眠データのパターンか。30字以内で述べよ。

(2) 本文中の下線②にある変更を加えた表の表名と,変更内容を答えよ。なお,変更内容は,30字以内で述べよ。

模範解答

設問1 a →,b 部署番号,c 従業員番号,d ↓

設問2 (1) e SELECT 従業員番号, :レポート年月 (2) f SUM(歩数.歩数),g 歩数.従業員番号 = 月次レポート.従業員番号

設問3 (1) 1 日に 2 回以上睡眠を取得するパターン (2) 表名 睡眠,変更内容 主キーを “従業員番号,睡眠開始日時” に変更する。

解説

健康応援システムのデータベース設計を題材に,E-R 図と SQL 文,実データからの不具合発見を問う問題である。

設問1では,E-R 図の空欄を補う。部署と従業員の関連は,1 人の従業員が 1 つの部署に所属し,1 つの部署に複数の従業員が所属するので,1 対多であり,[a]は「→」である。従業員は部署に所属するので,従業員に部署番号(外部キー)を追加する([b]は「部署番号」)。月次レポートは従業員ごとに作成されるので,主キーの一部として従業員番号を追加する([c]は「従業員番号」)。従業員と公開の関連は,1 人の従業員が複数の情報を公開するので 1 対多であり,図では下向きの矢印で表されているので,[d]は「↓」である。

設問2(1)では,月次レポート表に従業員番号と対象年月を挿入する INSERT 文を完成させる。挿入する値は従業員表から得るので,[e]は「SELECT 従業員番号, :レポート年月」である。設問2(2)では,対象年月の月間総歩数を集計する SQL 文を完成させる。歩数の合計を求めるので[f]は「SUM(歩数.歩数)」であり,歩数表と月次レポート表を従業員番号で対応付けるので[g]は「歩数.従業員番号 = 月次レポート.従業員番号」である。

設問3(1)では,エラーを引き起こしたパターンを問う。10-04 の行が 2 行あり,主キー(従業員番号,測定日)が重複する。これは 1 日に 2 回以上睡眠を取得したパターンである。設問3(2)では,この重複を解消するための変更を問う。睡眠表の主キーを「従業員番号,睡眠開始日時」に変更する(測定日を外す)。

関連問題

本文が同じ・似ている問題を表示しています