データベーススペシャリスト 2022年 秋期 午後I 問題

共通の文章

アフターサービス業務に関する次の記述を読んで,設問に答えよ。 住宅設備メーカーのA tlt,アフターサービス業務(以下,AS業務という)OY ステム再構築で,業務分析を行って概念データモデルと関係スキーマを設計した。 [現状業務の分析結果] 1.社内外の組織,人的資源の特性 (1)カスタマーセンターWE,CCという)は,A社に一つだけある組織である。 (2)CCの要員であるカスタマー係(以下,CC要員という)は,社員番号で識別し, 氏名をもつ。 (3)ビジネスパートナー(以下,BPという)は,A社の協業先企業で,BPコード で識別し,BP名,所在地をもつ。AS業務の範囲のBPには,販売パートナー(以 下,SLPという)と点検修理パートナー(以下,ASPという)がある。 @SLPは,販売店,工務店など,A社の製品をエンドユーザー(以下,EUとい う)に販売,設置をする企業であり,後述する問合せの登録を行う。SLPは SLPフラグで分類し,業種と前年販売高をもつ。 ②ASPは,点検修理の委託先企業で,全国を数百のサービス地域に分け,サー ビス地域の幾つかごとに1社と契約している。ASPはASPフラグで分類し,後 述するカスタマーエンジニア(以下,CEという)の人数であるCE数をもつ。 (4)CEは,ASPに所属する技術者で,ASPごとのCE番号であらかじめ登録してい る。氏名をもつ。 (5)EUは,製品の利用者で,EU番号で識別し,氏名,住所,住所から定まるサー ビス地域,電話番号,更新年月日をもつ。 2.製品などのもの,点検修理項目の特性 (1)製品は,A社が製造販売する製品で,製品コードで識別し,製品名をもつ。 (2)製品シリーズは,製品の上位の分類で,床暖房パネル,乾燥機などがある。 製品シリーズコードで識別し,製品シリーズ名をもつ。 (3)登録製品には,販売した製品を利用するEUを登録する。 ①登録製品は,製品製造番号で識別する。登録製品には,製品コード,利用 者のEU番号,登録製品の更新年月日を記録している。 =6= ②登録製品の利用者は,集合住宅での入退居や住宅の売買で変わり得るので, 把握の都度,利用しているEUを登録又は更新する。 (4)点検修理項目は,出張による点検修理で発生し得るCEの作業項目で,。メンテ ナンスコードCAE,MTコードという)で識別し,点検修理項目名をもつ。動 作確認,分解点検,ユニット交換などがある。 3.問合せの登録 (1)製品使用者の使用上の不具合や違和感がA社に対する問合せとなる。 (2)問合せは,製品使用者から直接又はSLP経由でCCに入る。 i(3)問合せの媒体は,Web上の問合せフォームか電話による通話である。いずれで あるか媒体区分で分類する。 (4)一つの問合せは,問合せフォームから入る1件の問合せ文叉は1回の通話で, 問合せ番号で識別し,問合せ年月日時刻,問合せ内容のほかに,製品使用者へ の連絡のための情報として,お名前と電話番号を記録する。この段階での連絡 のための情報は,登録されているEUのものとは関連付けない。 (5)製品使用者が直接入れる問合せは通話と問合せフォームの両方があり得るが, SLP経由の場合は問合せフォームからに限定している。 (6)入ったWeb問合せに対してCC要員が製品使用者に電話をかける。そのWeb問 合せがSLP経由だった場合,製品使用者にどのSLPから受け継いだかを伝えるた めに,Web問合せに経由したSLPのBPコードを記録している。 (7)通話は,成立しなくても1回の通話としている。通話が成立しないケースは., 受信の場合はCC要員の応答前に切れるケース,発信の場合は相手が話し中又は 応答がないケースである。通話の成立は通話成立フラグで分類する。 (8)通話の場合,通話したCC要員の社員番号,通話時間,受信か発信かの受発区 分,音声データである通話音声を記録している。 (9)問合せは,製品使用者が勘違いしていたり他社製品であったりすることもあ り,この場合の問合せは,後述する案件化をすることなく終わる。 4.問合せの案件化 (1)問合せに対して,回答のためにCCから電話をかける必要又は点検修理の必要 があれば,問合せを案件化し,案件番号を発番して案件を登録する。 (2)tit,対象製品が登録済みの登録製品に合致すればその登録製品と,合致 しなければ新たな登録製品を登録して関連付ける。その際,EUが未登録又は更 新が必要であれば,EUの登録又は更新も併せて行う。 (3)案件には,案件の登録年月日と更新年月日,EUに対する回答内容,案件の完 結を判断するための完結フラグをもつ。 (4)案件化した問合せ及びその後の問合せは案件に従属させる。 5.出張の手配 (1)案件に対して,どのような内容で点検修理を要するか決まると出張手配を行 er (2)出張手配は,案件に対して1回行い,EUに了解を得て出張年月日と出張時間 帯を決める。 6.出張の実施 (1)手配された出張を実施すると,実施年月日と実施時間帯,担当したCE,解決 したか判断するための解決フラグを記録する。 (2)また,点検修理の内訳をAS実施記録として,実施したMTコード,実施金額を 記録する。 [修正改善要望の分析結果] 1.ユニット及び要管理機能部品の追加 (1)ユニットは,部品の集合で,ユニットコードで識別し,ユニット名,ユニッ ト概要,製造開始時期,製造終了時期をもつ。熱交換器,水流制御器おどがあ Bi, ①製品の故障は,いずれかのユニットで発生する。 ②製品シリーズごとに,用いているユニットを登録する。 (2)機能部品は,主要な部品で,機能部品番号で識別し,機能部品名,後述する 要管理内容,製造開始時期,製造終了時期をもつ。ポンプや液晶板などがある。 ①機能部品は,複数ユニット間で共通化を進めている。 ②機能部品に起因する故障の頻発を予見した場合,その機能部品を要管理機 能部品として要管理内容を登録し,組み込んでいるユニットと関連付ける。 2.FAQ及びキーワードの整備 (1)既出の問合せ内容と回答内容の組をFAQとして登録することで,新たな問合せ に対してFAQを確認して迅速に正しい回答ができるようにする。 ニーきま ①FAQは,FAQ番号で識別し,問合せ内容,回答内容,点検修理の必要性を分 類する要点検修理フラグ,発生度ランクをもつ。 ②FAQは,点検修理が必要となる要点検修理FIAOとその必要のないその他の FAQに分類し,要点検修理フラグで分類する。 ③要点検修理FAQには,対象のユニットが何か設定するとともに,対応する点 検修理項目を関連付けておく。 @FAQには,問合せ内容の解釈によって類似のFAQが複数存在し得るので,類 似するFAQを関連FAQとして関連付け,関連度合いをA~Cの3段階に分けて 関連度ランクとして設定する。 (2)FAQ中に存在するキーワード(以下,KWという)をあらかじめ登録し,FAQと その中で用いられるKWを関連付ける。KWはKMそのもので識別し,補足説明をも つ。 (3)案件でEUへの回答に適用したFAQは,案件適用FAQとして案件に関連付け, 可能性の高いFAQの順に可能性順位を記録する。 〔概念データモデルと関係スキーマの設計]〕 1.概念データモデル及び関係スキーマの設計方針 (1)概念データモデル及び関係スキーマの設計は,まず現状業務について実施し, その後に修正改善要望に関する部分を実施する。 (2)関係スキーマは第3正規形にし,多対多のリレーションシップは用いない。 (3)概念データモデルでは,リレーションシップについて,対応関係にゼロを含 むか否かを表す“O”Mik“@”は記述しない。 (4)サブタイプが存在する場合,他のエンティティタイプとのリレーションシッ プは,スーパータイプ又はいずれかのサブタイプの適切な方との間に設定する。 (5)スーパータイプに相当する関係スキーマには,必ずサブタイプを分類する属 性を明示する。 (6)同一のエンティティタイプ間に異なる役割をもつ複数のリレーションシップ が存在する場合,役割の数だけリレーションシップを表す線を引く。 2.[現状業務の分析結果〕に基づく設計 現状の概念データモデルを図1に,現状の関係スキーマを図2に示す。 ー9一 |®|4 A 製品点検修理 サービスyo &AS Web— 人問合せ PT 図1現状の概念データモデル(未完成) CC要員(社員番号,社員氏名) BP(BPコード,BP名,所在地,SLPフラグ,ASPフラグ) SLP(BPコード,業種,前年販売高) ASP(BPコード,CE数) CE(BPコード,CE番号,CE氏名) サービス地域(サービス地域コード,MAS,所在地。BPコード) EU(EU番号,氏名,住所,サービス地域コード,電話番号,更新年月日 製品シリーズ(製品シリーズコード,製品シリーズ名) 製品(製品コード,製品名,製品シリーズコード) 登録製品(製品製造番号,製品コード,UBS,更新年月日 ASA(MTコード,点検修理項目名) 問合せ(問合せ番号) Web問合せ(問合せ番号,) SLPweb問合せ(問合せ番号,) 通話(問合せ番号,) 発信通話(問合せ番号,) 案件(案件番号,製品製造番号,BRERA,BAAR,GEAR,完結フラグ) 出張手配(案件番号) 出張実施(GRABS,実施年月日,RHO,担当BPコード,担当CE番号,解決フラグ) AS実施記録(案件番号,実施MTコード,実施金額 図2現状の関係スキーマ(未完成) 3.[修正改善要望の分析結果]に関する設計 修正改善要望に関する概念データモデルを図3に,修正改善要望に関する関係ス キーマを図4に示す。 A 要点検修理 FAQ 注記設問の都合上,網掛け部分は表示していない。 図3修正改善要望に関する概念データモデル(ARM) ユニット(ユニットコード,ユニット名,ユニット概要,製造開始時期,製造終了時期) 要管理機能部品(機能部品番号,機能部品名,要管理内容,製造開始時期,製造終了時期) KW(KW,補足説明) FAQ(FAQ番号,問合せ内容,回答内容,要点検修理フラグ,発生度ランク) 要点検修理FAQ(FAQ番号,ユニットコード) Lょ|」([まコ) pes)«(_2_]) ims)«((_2_)) Coa«(0ュ]) a td a© (しシ]) 注記1図中の[am|~[ft|には.図3o[a|-[|f]と同じ字名が入る。 注記2設問の都合上,網掛け部分は表示していない。 図4修正改善要望に関する関係スキーマ(未完成) 解答に当たっては,巻頭の表記ルールに従うこと。また,エンティティタイプ 名,関係名,BHA,それぞれ意味を識別できる適切な名称とすること。関係 スキーマに入れる属性名を答える場合,主キーを表す下線,外部キーを表す破線 の下線についても答えること。

共通の文章

データベースの実装に関する次の記述を読んで,設問に答えよ。 専門商社のB社では,見積業務で利用するシステム(以下,見積システムという) の,マスター保守に伴う調査業務を改善中である。また,見積システムのパブリッ ククラウドへの移行を計画している。 〔パブリッククラウドが提供するサービスの主な仕様 1.オブジェクトストレージ オブジェクトストレージには,任意のファイルを保存することができる。RDBMS とは独立して稼働し,RDBMSの障害時にも影響を受けずに,ファイルにアクセスす ることができる。 2.RDBMS PaaSとして提供されるRDBMS Id,インスタンスごとに割り当てられた仮想マシ ンで稼働する。 (1)ログ ログはログファイルに記録する。ログファイルの切替え時に,切替え前に使 用していたログファイル(以下,アーカイブログという)を,オブジェクトス トレージに保存する。ログ切替えの時間間隔は,任意に設定することができる。 (2)バックアップ QDデータベース全体のフルバックアップを,オブジェクトストレージに保存 する。バックアップは,データベースを停止して,オフラインで取得する。 バックアップを取るタイミングは,任意に設定することができる。 (オブジェクトストレージに保存したフルバックアップとアーカイブログを 使って,データベースを回復することができる。 (3)レプリケーション ログを使って,RDBMSのデータをほかのRDBMSに複製する。複製元のテーブル に対する変更操作(挿入・更新・削除)を複製逢のテーブルに自動的に反映す る。 レプリケーションには,同期型と非同期型がある。 ①④同期型では,複製先でログをディスクに出力した後,複製元のトランザク ー13一 ションがコミットされる。 ②非同期型では,複製先へのログの到達を待たずに,複製元のトランザクシ ョンがコミットされる。 (4)トリガー テーブルに対する変更操作(挿入・更新・削除)を契機に,あらかじめ定義 した処理を実行する。 OM実行タイミングを定義むせすることができる。BEFOREトリガーは,テーブルに 対する変更操作の前に実行され,更新中又は挿入中の値を実際の反映前に修 正することができる。AFTERトリガーは,変更操作の後に実行され,ほかのテ ーブルに対する変更操作を行うことができる。 ②トリガーを実行する契機となった変更操作を行う前と後の行を参照すること ができる。参照するには,操作前と操作後の行に対する相関名をそれぞれ定 義し,相関名で列名を修飾する。 [見積システムの概要〕 1.テーブル 主なテーブルのテーブル構造を図1に示す。 社員(社員コード,社員名,…) 商品(商品コード,メーカー名,商品名,モデル名,定価,更新日) 見積依頼(見積依頼番号,仕和先コード,社員コード,見積依頼日) 見積依頼明細(見積依頼番号,明細番号,商品コード,数量) BROS(見積依頼番号,仕和先コード,社員コード,見積回答日) 見積回答明細(見積依頼番号,明細番号,商品コード,数量,仕入単価,モデル名,定価) 図1主なテーブルのテーブル構造(一部省略) 2.仕入先への見積依頼業務 (1)B社の社員は,顧客からの引き合いを受けて,仕入先への見積依頼を入力する。 見積依頼番号を採番し,"見積依頼",“見積依頼明細"テーブルに見積依頼の内 容を登録する。 (2)仕入先に見積りを依頼し,回答を受け取る。 4 ae (3)仕入先からの回答を入力する。対応する見積依頼の見積依頼番号を参照し, “見積回答",“見積回答明細"テーブルに見積回答の内容を登録する。商品の モデル名,定価が変更されたことが分かることがある。この場合,当該商品は, "見積回答明細"テーブルに変更後の内容を登録する。ただし,“商品”テーブ ルへの反映は後日行う。 “ima”テーブルの履歴管理] モデル名又は定価のいずれかが変更されたが,変更が“商品"テーブルへ反映さ れていない商品を調べるため,図2に示すSQL文を定期的に実行している。 SELECT A.モデル名AS新モデル名,C.モデル名AS旧モデル名,A.定価AS新定価,C.定価AS 旧定価 FROM見積回答明細A INNER JOIN見積回答B ON A.見積依頼番号=B.見積依頼番号 INNER JOIN商品CONA|sa|=c.|ぉ』| WHERE B.見積回答日>C.更新日 AND(A、モデル名ぐC.モデル名|bd|AM©C.定価) 図2商品の変更を調べるSQL文(未完成) 1.“商品”テーブルの設計変更 “商品”テーブルを更新すると,過去の属性情報は失われてしまう。そこで, 商品属性情報の変更を履歴として保存するために,“商品テーブルの設計変更を 行うことにした。ただし,既存のアプリケーションプログラムには,極力影響を 与えないようにする必要がある。表1に示す2案を検討した結果,案2を採用し だ。 表1“商品”テーブルの設計変更案 変更後のテーブル構造 商品(商品コード,メーカー名,商品名。モデル名定価,更新日適用開始日,適用終了日 案2商品(商品コード,メーカー名,BAA,モデル名,定価,更新日,適用開始日,適用終了日) 商品履歴(商品コード,メーカー名,商品名,モデル名,定価,更新日,適用開始日,適用終了日) 注記1“商品履歴"テーブルの主キーは表示していない。 注記2適用開始日は,その行の適用が開始される日,適用終了日は,その行の適用が終了される日。適用終 了日が未定の場合はNULLが設定される。 案2の実装に当たり,“商品”テーブルへの列の追加,“商品履歴"テーブルの作成, 及び主キーの追加を表2のSQL1に示すSOL文で行った。 また,同一の適用開始日に同一の商品を複数回更新することはない前提で,“商品" テーブルの更新時に行う追加の処理を,表2のSQL2に示すトリガーで実装した。 表2商品”テーブルを変更するSQL文及びトリガーを定義するSQL文(未完成) SQL(ERR:目的,下段:SOL文) "商品”テーブルへ適用開始日列,適用終了日列を追加する。 “商品履歴"テーブルを作成し,主キーを追加する。 gout|ALTER TABLE商品ADD COLUMN適用開始日DATE DEFAULT CURRENT DATE NOT NULL; ALTER TABLE商品ADD COLUMN適用終了日DATE: CREATE TABLE商品履歴LIKE商品: ALTER TABLE商品履歴ADD primary KEY(|c|,|g|): 次のトリガーを定義する。 ・“商品”テーブルの更新時に,適用開始日がNULL OWA,現在日付に更新する。 ・“商品”テーブルの更新時に,対象行の更新前の行を“商品履歴"テーブルに挿入す る。このとき,挿入行の適用終了日には,更新後の行の適用開始日の前日を設定する。 CREATE TRIGGERトリガー1| e ]UPDATE ON商品 REFERENCING OLD AS OLD1 NEW AS NEW]FOR EACH ROW SQL2 SET NEW1.適用開始日=COALESCE(NEW1.適用開始日,CURRENT_DATE): CREATE TRIGGERトリガー2 UPDATE ON商品 REFERENCING OLD AS OLD2 NEW AS NEW2 FOR EACH ROW INSERT INTO商品履歴 VALUES(OLD2.商品コード,OLD2.メーカー名,OLD2.商品名,0LD2.モデル名, OLD2.定価,0LD2.更新日,| g|.適用開始日, ADD DAYS(|h|.適用開始日-1)); 注記1 CREATE TABLE A LIKE Bid,テーブルBを基にして同じ列構成のテーブルAを定義する。 注記2 0LD1,OLD2,NEW1,NEW2は,トリガーを実行する契機となったテーブルに対する変更操作 が行われた行を参照する相関名を表す。0LD1及び0LD2は変更前,NEW1及びNEW2は変更後の 行を参照する。 注記3 ADD DAYS(引数1,引数2)は,引数1(日付型)から引数2(整数型)日後の日付を返すユ ーザー定義関数である。ただし,引数1がNULLの場合はNULLを返す。 2.データ移行 “Ames”,“見積回答明細"テーブルから"商品","商品履歴テーブルへヘ データを移行するため,商品のモデル名又は定価のいずれかが変更されたことの 履歴を,図3のSOL文で調べた。“AMOS”,"見積回答明細"テーブルの内容を 表3,表4に示す。SQL文の結果を表5に示す。 表5の内容を基に,“商品"テーブルを更新,又は“商品履歴"テーブルへ挿入 することでデータを移行した。移行前の“商品”テーブルの状態によらず,変更 があった全商品を更新した。また,表2のSQL2に示すトリガーは未定義の状態で 行った。 WITH Q1 AS(SELECT A.商品コード,A.モデル名,A.定価,B.見積回答日, LAG(A.モデル名)OVER(PARTITION BY A.商品コードORDER BY B.見積回答日)AS前行モデル名, LAG(A.定価)OVER(PARTITION BY A.商品コードORDER BY B.見積回答日)AS前行定価 FROM見積回答明細A INNER JOIN見積回答B ON A.見積依頼番号=B.見積依頼番号), Q2 AS(SELECT Q1.*FROM Q1 WHERE Q1.前行定価IS NULL OR 01.モデル名©01.前行モデル名|b|01.定価©01.前行定価) SELECT ROW_NUMBER()OVER(ORDER BY Q2.商品コード,Q2.見積回答日)AS行番号, Q2.商品コード,02.定価,02.モデル名,02.見積回答日AS適用開始日, ADD_DAYS(LEAD(Q2.見積回答日) OVER(PARTITION BY Q2.商品コードORDER BY Q2.見積回答日),-1)AS適用終了日 FROM Q2 ORDER BY Q2.商品コード,適用開始日: 注記1 ADD_DAYS(引数1,引数2)は,引数1(日付型)から引数2(整数型)日後の日付を返すユーザー定義関 数である。ただし,引数1がNULLの場合はNULLを返す。 注記2図中の|b|には,図2の|b|と同じ字句が入る。 注記3 LAG関数は,ウィンドウ区画内で順序付けられた各行に対して,現在行の1行前の行の列の値を返す。 1行前の行がない場合はNULLを返す。 注記4 LEAD関数は。ウィンドウ区画内で順序付けられた各行に対して,現在行の1行後の行の列の値を返す。 1行後の行がない場合はNULLを返す。 図3商品の変更履歴を調べるSQL文(未完成) 表3見積回答テーブルの内容 見積依頼番号|仕入先コードRMSE 2019000101 AB490656 2019-04-01 2020001201 AHO00032 2020-09-01 2022000201 AA000232 2022-05-01 2022001201 AH000657 2022-09-01 表4見積回答明細"テーブルの内容 上信和|EN 表5 SQL文の結果(未完成) |」|!|mwl w|secwo|還計= >|)[wlwml wwww|トコ" Pe|5|20|wm|oe時 |5|2|cw|ei|sen[| Le|3|se||wwwn|| 注記網掛け部分は表示していない。 〔基盤設計〕 1,RPO,RTOの見積り 見積システムをパブリッククラウドに移行した場合の,RDBMSのディスク障害時 のRP0及びRT0を,次のように見積もった。 (1)利用するパブリッククラウドの仕様に基づいて,データベースのフルバック アップは1日に1回取得し,ログの切替えは5分に1回行い,回復時にはオブジ ェクトストレージに保存したフルバックアップとアーカイブログを使って回復 する,という前提で見積もる。 (2)RPOは,障害発生時に失われる|ア|に依存するので,最大|イ| 分とみなせる。 (3)RTOのうち,データベースの回復に掛かる時間は,フルバックアップからのリ ストア時間と,ログを適用するのに掛かる時間の合計である。 (4)フルバックアップからのリストア時間は,データベース容量が180Gバイト,リ ストア時のディスク転送速度を100 Mバイトノ秒と仮定すると|ウ|秒でぁ る。ここで,16バイトは19?バイト,1Mバイトは106バイトとする。 (5)ログを適用する期間が最大になるのは,フルバックアップ取得後の経過時間 が最大になる24時間である。ログが毎秒10ページ出力されると仮定すると,適 用するログの量は最大|エ|ページである。ログを適用するのに掛かる時 Mit,バッファヒット率を9%,同期入出力時間がページ当たり2ミリ秒と仮定 すると最大|オlocas. 2.参照専有インスタンス 商品の変更履歴を調べるために実行するSOL文の負荷が大きく,見積システムヘへ ー18— の影響が懸念された。そこで,影響を最小化するために,参照専用シンスタンス を本番インスタンスとは別に作成し,調査は参照専用ンスタンスで行うことに した。また,全テーブルについて,本番インスタンスから参照専用インスタンス へ,非同期型のレプリケーションを行うことにした。 3.参照専用ンスタンスへのフェイルオーバーによる業務継続 RPO及びRTOを短くするために,本番インスタンスが障害になった場合,参照専 用インスタンスにフェイルオーバーして,参照専有ンスタンスを使用して業務 を継続できるかを検討した。検討の結果,非同期型のレプリケーションを行う前 提だと,参照専有イシスタンスでは,本番インスタンスでコミット済みの変更が 失われる可能性があることが分かった。