問題プレビューID: 4af5588a応用情報技術者試験 2021年(令和3年度)春期 午後

問 6
応用情報技術者試験 2021年(令和3年度)春期 午後

経営分析システムのためのデータベース設計に関する次の記述を読んで,設問1~4に答えよ。

P社は,個人向けのカーシェアリングサービスを運営するMaaS(Mobility as a Service)事業者である。シェアリングのニーズが高い大都市の地区を中心に,500駐車場で約2,000台の自動車(以下,車両という)を貸し出している。P社には本社のほかに,各地区でのサービス運営を担当する支社が10社ある。本社はサービス全体を統括しており,新サービスの企画やマーケティングなどを行っている。支社は貸出管理システムを用いて現場で車両の貸出管理業務を行っている。

本社では,サービス運営状況を多角的な観点でタイムリーに把握して,適切な意思決定を行うために,貸出管理システムのデータをソースとする経営分析システムを構築することになった。本社の情報システム部のQさんはデータエンジニアに任命され,データサイエンティストであるRさんとプロジェクトを推進することになった。

〔データソースの調査〕

貸出管理システムには,貸出予約及び貸出実績のデータが過去5年間分蓄積されている。貸出管理システムのデータモデルの抜粋を図1に示す。

図 1 貸出管理システムのデータモデル(抜粋)

プラン

プラン
プランID
プラン名
基本料金
従量料金単価
延長料金単価

支社

支社
支社ID
支社名
地区

駐車場

駐車場
駐車場ID
支社ID
駐車場名

車種

車種
車種ID
車種名
乗車可能人数

車両

車両
車両ID
駐車場ID
車種ID
車両ナンバ

会員

会員
会員ID
プランID
氏名
性別
生年月日
住所
電話番号

貸出予約

貸出予約
貸出予約コード
駐車場ID
車種ID
会員ID
予約時刻
キャンセル時刻
貸出予定時刻
返却予定時刻
貸出予定時間
従量料金合計

貸出実績

貸出実績
貸出実績コード
貸出予約コード
車両ID
貸出実績時刻
返却実績時刻
貸出実績時間
延長料金合計

凡例 —— : 1対1  ⟶ : 1対多  ⟷ : 多対多

エンティティ名
属性名
属性名
属性名
⋮

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

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

  • プラン → 会員(1対多)
  • 支社 → 駐車場(1対多)
  • 駐車場 → 車両(1対多)
  • 車種 → 車両(1対多)
  • 会員 → 貸出予約(1対多)
  • 車両 → 貸出実績(1対多)
  • 貸出予約 ― 貸出実績(関連線あり。図では貸出予約の右辺に ← の矢印が示されている)

利用希望者はあらかじめP社の会員になり,いずれかのプランに加入しておく必要がある。プランごとに基本料金(月額),従量料金及び延長料金(いずれも10分単位)の単価が決まっている。会員が車両を借りたいときは,P社のホームページで借りたい日時や駐車場,車種などを選択し,貸出を予約する。貸出や返却の実績時刻が予約時の内容と異なる場合であっても,貸出予約の情報は修正しない。従量料金合計は予約時に指定された貸出予定時間を基に算出する。予約時に指定した返却予定時刻より早い時刻に返却しても,従量料金合計は減算しない。予約時に指定した返却予定時刻より遅い時刻に返却した場合は遅延返却として扱う。遅延返却は後の時間帯に予約している別の会員の迷惑となるので,超過した時間については従量料金よりも高い延長料金によって延長料金合計を算出する。これによって,遅延返却の発生件数(以下,遅延返却発生件数という)の低減を図っている。毎月末に当月の基本料金,従量料金合計及び延長料金合計を合算して,翌月に会員に請求する。

貸出管理システムのデータベースでは,データモデルのエンティティ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理している。時刻はTIMESTAMP型,年月日はDATE型で定義されている。

また,P社ではKPIの一つとして車両稼働率を重視している。車両稼働率とは,各車両における1日当たりの貸出実績時間の割合である。平均車両稼働率の目標データは,表計算ソフトのデータとして,年月日別・駐車場別・車種別に過去3年間分が蓄積されており,それ以前のデータは破棄されている。

〔業務要件の把握〕

P社の経営企画部では,車両の追加整備計画の立案を検討している。Rさんは経営企画部にヒアリングを行い,経営分析システムの業務要件を把握した。業務要件の抜粋を図2に示す。

Qさんは,データソースの調査結果を踏まえて,図2の業務要件の実現可能性を評価した。その結果,①業務要件の一部は経営分析システムの運用開始直後には実現できないことが判明した。対応方針を経営企画部と協議した結果,業務要件は変更せず,運用開始後の分析は,実現可能な範囲で行うことで合意した。

図 2 経営分析システムの業務要件(抜粋)

  • 地区別の人気車種,会員の性別・年代別の人気車種,駐車場別・車種別の平均車両稼働率,駐車場別・会員別の遅延返却発生件数を分析できること。なお,貸出実績の件数(以下,貸出実績件数という)が多い場合を人気車種であるとみなす。
  • 表計算ソフトのデータを用いて,平均車両稼働率の目標比や前年同期比を分析できること。
  • これらのいずれにおいても,年別,月別,日別,週別,曜日別といった時間軸で傾向を分析できること。
  • 過去5年間について,分析対象期間を柔軟に変更して,期間による傾向の違いを分析できること。
  • 毎週月曜日の朝に最新のデータを確認できること。ただし,遅延返却発生件数については前日までの実績を翌営業日の朝に確認できること。
  • 貸出実績件数及び遅延返却発生件数は貸出予定の日付で集計すること。

〔経営分析システムのデータモデル設計〕

次に,Qさんは図2の業務要件を基に,経営分析システムのデータモデルを多次元データベースとして設計した。多次元データベースの実装には,データモデルのエンティティ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースを用いることにした。列指向データベースは用いず,データを行単位で扱う行指向データベースを用いることにした。問合せの処理性能を考慮して,データモデルの構造には[a]構造を採用した。経営分析システムのデータモデルの抜粋を図3に示す。

経営分析システムには,最長で過去5年間分のデータを蓄積することにした。

年月日の週と曜日は,事前に定義したSQLのユーザ定義関数を用いて取得できる。

図 3 経営分析システムのデータモデル(抜粋)

カレンダ

カレンダ
年月日
年
月
日
週
曜日

貸出

貸出
年月日
駐車場ID
車種ID
会員ID
[c]
[d]
遅延返却発生件数

車両稼働

車両稼働
[b]
駐車場ID
車種ID
平均車両稼働率
平均車両稼働率(目標)

車種

車種
車種ID
車種名

駐車場

駐車場
駐車場ID
駐車場名
地区

会員

会員
会員ID
氏名
性別

エンティティ間の関連は次のとおりである(いずれも1対多)。

  • カレンダ → 貸出
  • カレンダ → 車両稼働
  • 駐車場 → 貸出
  • 駐車場 → 車両稼働
  • 車種 → 車両稼働
  • 会員 → 貸出(関連[e]。図では貸出と会員の間の[e]に ← の矢印が示されている)

〔データ加工処理の開発〕

貸出管理システムのデータベースから経営分析システムのデータベースへのデータ連携時に,一部のデータを加工する必要がある。Qさんは,データ加工処理用のデータベースを用意し,データ加工を行うバッチ処理プログラムを開発した。図4のSQL文は,そこで用いられている図3の貸出表の遅延返却発生件数データを作成するためのものである。ここで,TIMESTAMP_TO_DATE関数は,指定されたTIMESTAMP型の時刻をDATE型の年月日に変換するユーザ定義関数である。

バッチ処理プログラムでは,図4のSQL文で作成したデータを貸出表に挿入する際,遅延返却発生件数が0件のレコードに対する処理も別途行うようになっている。

図 4 遅延返却発生件数データを作成するSQL文

SELECT R.貸出予定年月日, R.駐車場 ID, R.車種 ID, R.会員 ID, COUNT(*) AS 遅延返却発生件数
FROM (SELECT Y.貸出予約コード, Y.駐車場 ID, Y.車種 ID, Y.会員 ID,
TIMESTAMP_TO_DATE(Y.貸出予定時刻) AS 貸出予定年月日, Y.返却予定時刻 FROM 貸出予約 Y) R
[f] J.貸出予約コード
WHERE R.返却予定時刻 < J.返却実績時刻
[g]

〔分析のレスポンス性能の改善〕

性能検証を実施したところ,分析対象期間を過去複数年間,時間軸を月別として人気車種及び遅延返却発生件数を分析する場合,種々の分析に時間が掛かり過ぎるので改善してほしいという要望が経営企画部から挙がった。経営分析システムのデータベースのインデックスは既に適切に作成している。分析のレスポンス性能を改善するために,Qさんは②データマートとして集計表を追加した。

設問1

本文中の下線①について,実現できない業務要件を40字以内で具体的に答えよ。

設問2

〔経営分析システムのデータモデル設計〕について,(1),(2)に答えよ。

(1) 本文中の[a]に入れる適切な字句を解答群の中から選び,記号で答えよ。

解答群

ア 3層スキーマ  イ オブジェクト指向  ウ スタースキーマ  エ スノーフレークスキーマ  オ 第3正規形  カ 非正規形

(2) 図3中の[b]~[e]に入れる適切なエンティティ間の関連及び属性名を答えよ。なお,エンティティ間の関連及び属性名の表記は図1の凡例及び注記に倣うこと。

設問3

〔データ加工処理の開発〕について,(1),(2)に答えよ。

(1) 図4中の[f],[g]に入れる適切な字句を答えよ。なお,表の列名には必ずその表の相関名を付けて答えよ。

(2) 図4のSQL文を実行すべき頻度を2字以内で答えよ。

設問4

本文中の下線②について,追加した集計表の主キーを答えよ。

模範解答

設問1 システム稼働後2年間は,過去5年間分の平均車両稼働率の目標比を表示できない。

設問2 (1) a ウ (2) b 年月日,c 年代,d 貸出実績件数,e ←

設問3 (1) f INNER JOIN 貸出実績 J ON R.貸出予約コード =,g GROUP BY R.貸出予定年月日, R.駐車場 ID, R.車種 ID, R.会員 ID (2) 日次

設問4 年月,駐車場ID,車種ID,会員ID

解説

設問1は,平均車両稼働率の目標データが表計算ソフトに過去3年間分しか蓄積されていないため,運用開始直後は過去5年間分の目標比を表示できず,5年分の目標比が揃うのはシステム稼働後2年間を経てからである。

設問2(1)のaは,ファクト表(貸出,車両稼働)を中心にディメンション表を結合したウ(スタースキーマ)である。(2)のbは車両稼働表の主キーの年月日,cは会員の年代別の分析に用いる年代,dは人気車種の判定に用いる貸出実績件数であり,cとdは順不同である。eは会員から貸出への1対多の関連を示す ← である。

設問3(1)のfは,貸出予約と貸出実績を貸出予約コードで結合するINNER JOIN句であり,gは貸出予定年月日,駐車場ID,車種ID,会員IDを集計単位とするGROUP BY句である。(2)は,遅延返却発生件数を前日までの実績で翌営業日の朝に確認できるようにする日次である。

設問4は,月別の人気車種及び遅延返却発生件数を分析する集計表であり,主キーは年月,駐車場ID,車種ID,会員IDである。

関連問題

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