平成29年度春期 データベーススペシャリスト 午後Ⅱ 問1

問1　データベースの設計，実装に関する次の記述を読んで，設問1～3に答えよ。 N社は，家具・日用雑貨の小売業を営む企業であり，全国に直営店舗を展開するほか，Webサイトのオンラインショップでも販売を行っている。直営店舗における販売（以下，店舗販売という）とオンラインショップにおける販売（以下，OL販売という）の各業務は，それぞれ運営する組織も，利用するシステムも異なっている。そこでN社では，かねてからの課題であった相互の情報共有を実現するために，各システムで保有する情報の一部を統合して，業務の改善を図ることにした。 〔現在の業務内容〕 1．店舗販売業務 (1) 運営組織 ① 全国を20のエリアに分割し，各エリアの管理担当者が，担当地域内の複数の倉庫，複数の店舗を管理している。 ② 店舗の従業員は，一つの店舗に所属し，役職及び担当職務が定められている。 (2) 商品 ① 取扱商品は，約5万点である。商品は商品コードで一意に識別される。 ② 商品分類は，大分類，中分類，小分類の3階層構成である。 (3) 会員 ① 店舗で会員登録を行った顧客（以下，店舗会員という）に，店舗ごとに一意な店舗会員番号を付与する。会員登録時に氏名，住所，電話番号を記録する。店舗会員には，ポイントカードを発行する。ポイントカードには，発行した店舗の店舗コードと店舗会員番号が印字されている。 ② ポイントカードの紛失時・不携帯時は，新たな店舗会員としてポイントカードが発行されるので，同一顧客が複数のポイントカードをもつこともある。 (4) 販売 ① 店舗のPOSレジで，精算ごとに販売，販売明細を記録する。 ② 店舗会員がポイントカードを提示すると，購入金額に応じてポイントが付与され，使用可能ポイントとして蓄積される。 ③ ポイントは，取得した翌日以降の購入時の支払に使用できる。使用したポイントは，即座に使用可能ポイントから差し引かれる。 ④ 蓄積されたポイントは，取得した年の2年後の年末まで有効である。 (5) 在庫 ① 店舗では，商品ごとに実在庫数を店舗在庫として管理している。 ② エリアでは，倉庫ごと，商品ごとに実在庫数，引当済数を倉庫在庫として管理している。 (6) 店舗への出荷 ① エリアの管理担当者は，店舗在庫を定期的に調べて，実在庫数が一定水準を下回る商品がある場合は，倉庫に対して店舗への出荷指示を行う。 ② 倉庫は，出荷指示を受けると，店舗ごとに商品をまとめて出荷する。 (7) 販売分析 エリアの管理担当者は，蓄積されている販売データを，専用の多次元分析ツールを用いて検索，集計し，その結果を販売促進，商品企画の参考にしている。 2．OL販売業務 (1) 運営組織 ① オンライン事業部が，オンラインショップを運営している。 ② OL販売専用の倉庫が一つある。 (2) 商品 ① 店舗での取扱商品の一部を販売する。商品は，店舗販売業務と同じ商品コードで識別される。 ② 商品分類は，3～5階層構成である。 (3) 会員 ① オンラインショップの利用には，会員登録が必要である。Webサイトから会員登録を行った顧客（以下，OL会員という）に，OL販売で一意な会員番号を付与する。会員登録時に，氏名，住所，電話番号，生年月日，性別，メールアドレスを記録する。 ② 会員登録時に，同一人の複数登録がないようにチェックする。 (4) 販売 ① 一つの注文で，複数商品の購入を受け付ける。商品の配送前であれば，注文の取消しができる。 ② 注文ごとに，購入金額に応じてポイントが付与され，使用可能ポイントとして蓄積される。ポイント使用時の取扱い，ポイント有効期限は店舗販売業務と同じである。 (5) 配送 ① オンライン事業部は，注文に応じて，倉庫に配送指示を行う。 ② 倉庫は，配送指示を受けると，商品を梱包し，外部の配送業者に委託して注文者の指定場所に配送する。 (6) 在庫 倉庫では，商品ごとの実在庫数，引当済数を倉庫在庫として管理している。 (7) 販売分析 オンライン事業部の管理担当者は，蓄積されている注文データを直接参照して検索，集計し，その結果を販売促進，商品企画の参考にしている。 〔販売業務改善の要件〕 (1) 共通情報の管理 ① 店舗販売業務とOL販売業務を統括する販売統括部を新設し，両業務に共通する商品情報及びそれに関連する商品分類情報を管理する。 ② 店舗会員とOL会員の情報を統合し，会員区分で識別する。会員情報漏えい防止対策として，参照者の範囲を限定する。 (2) ポイントの取扱い ① 会員が所持している1枚又は複数のポイントカードのポイントと，OL販売のポイントを，まとめて取り扱えるようにする。これをポイント統合という。 ② 会員から申請があれば，ポイント統合を行う。統合後は，OL販売の会員番号を正規の会員番号とし，ポイントを集約する。 ③ 会員は，統合するポイントカードの店舗コードと店舗会員番号を指定する。複数のポイントカードのうち，1枚だけを存続させ，その他は廃止する。 ④ ポイント統合は申請の翌日以降に有効となり，店舗販売とOL販売のどちらでもポイントを使用できるようにする。ポイント統合の対象となったポイントカードの統合申請日，統合対象ポイント（統合時点の使用可能ポイント）を記録し，照会画面から確認できるようにする。 (3) OL販売の取扱商品拡大と在庫回転率向上 ① 全エリアの倉庫からの配送を可能にして，店舗販売業務の取扱商品の多くをOL販売で取り扱えるようにする。 ② エリアの倉庫在庫から他エリアの店舗へ出荷できるようにする。 ③ 店舗で販売可能な商品とオンラインショップで販売可能な商品を区分する。 (4) 販売分析 ① 店舗販売とOL販売をチャネル区分で識別し，チャネル横断的に販売分析処理を行えるようにする。販売分析処理の例を表1に示す。 ② 会員の性別，居住地域（関東，東海など），年齢層（10代，20代など）を用いて分析を行う。 表1 販売分析処理の例 〔現行システムの概要〕 N社では，現行店舗販売システムと現行OL販売システムを個別に運用し，両システム間のデータ連携はなく，マスタ情報もそれぞれのシステムで管理している。 1．現行店舗販売システムのテーブル構造とCRUD 現行店舗販売システムのテーブル構造，CRUDを図1，表2に示す。 図1 現行店舗販売システムのテーブル構造（一部省略） 表2 現行店舗販売システムのCRUD 2．現行OL販売システムのテーブル構造とCRUD 現行OL販売システムのテーブル構造，CRUDを図2，表3に示す。 図2 現行OL販売システムのテーブル構造（一部省略） 表3 現行OL販売システムのCRUD 〔連携システムの概要〕 N社では，販売業務改善に向けて，新たに連携システムを構築することにした。店舗販売システムとOL販売システムの間で直接データ交換を行うのではなく，連携システムに，両システムのデータの一部を統合，一部を複製した上で，連携システムを介して双方の情報連携を行う。 〔RDBMSの仕様〕 店舗販売システム，OL販売システム及び連携システムに共通して用いられるRDBMSの仕様は，次のとおりである。 1．権限 (1) テーブル，ビューへのアクセスには，アクセス権限が必要である。 (2) ビューにアクセスする場合，そのビューが参照するテーブル又は他のビューのアクセス権限は不要である。 (3) テーブル，ビューのアクセス権限は，ユーザID，ロールに付与される。ロールは，ユーザID及び他のロールに付与される。 2．トリガ テーブルに対する操作（追加・更新）を契機に，あらかじめ定義された処理を実行する。実行タイミング（追加・更新の前又は後），列値による実行条件を定義することができる。 3．DB相互接続機能 同じネットワーク上にある異なるサーバ上のDBを，相互接続する機能である。相互接続したDB上のテーブルは，一つのSQL文で操作することができる。 4．レプリケーション機能 (1) 1か所のデータを複数か所に分散する機能，複数か所のデータを1か所に集約する機能，及び両者を組み合わせて双方向に同期をとる機能がある。これらの機能を使用すると，一方のテーブルへの追加・更新を他方に自動的に反映させ，データの同期をとることができる。 (2) トランザクションログを用いて一定間隔でデータの同期をとるバッチ型と，レプリケーション元のトランザクションによってデータの同期をとるイベント型とがある。 ① イベント型では，レプリケーション先への同期が失敗すると，レプリケーション元の更新はロールバックされる。 ② バッチ型では，テーブルごとに，レプリケーションの有効化，無効化をコマンドによって指示することができる。無効化したレプリケーションを有効化するときには，蓄積されたトランザクションログを用いてデータの同期をとる。 (3) 必要となる列の選択，必要となる行の選択及びその組合せによって，レプリケーション先のテーブルに必要とされるデータだけを同期することができる。 〔テーブル配置の検討〕 現行店舗販売システムと現行OL販売システム（以下，二つのシステムを合わせて現行システムという）のテーブルを，表4の配置方法①～⑤のいずれかによって配置することにし，表5に示すテーブル配置検討表を次の手順(i)～(v)で作成した。以下，現行システムのアプリケーションを現行AP，現行システムのDBを現行DB，連携システムのDBを連携DB，連携システムのアプリケーションを連携APという。 (i) 〔販売業務改善の要件〕から，店舗販売業務とOL販売業務に共通する情報を保有していて，双方で情報の参照，追加，更新が発生するテーブルには，情報共有対象欄に“○”を記入する。 (ii) 情報共有対象でないテーブルのうち，情報共有対象のテーブルから参照されるテーブルには，参照先欄に“○”を記入する。 (iii) (i)又は(ii)に該当するテーブルについて，現行システムの処理で追加・更新されるものは現行APを，販売統括部によって追加・更新されるものは連携APを追加・更新の起点欄に記入する。追加・更新されない場合は空欄にする。 (iv) 追加・更新の起点が現行APであるテーブルについて，現行DB及び連携DBの両方にテーブルを配置した場合に，現行DBの一方での追加・更新を，他方にも同期させる必要があれば双方向の同期欄に“○”を記入する。 (v) (i)，(ii)のいずれにも該当しなければ配置方法①とし，該当する場合は，次のように配置方法を決める。 ・(iii)において，追加・更新の起点が連携APの場合は，配置方法②とする。 ・(iv)において，双方向の同期が必要でない場合は，配置方法③とする。 ・(iv)において，双方向の同期が必要な場合は，配置方法④とする。ただし，テーブルの主キーの値が等しい行に対する更新が同時に発生する可能性がある場合には，配置方法⑤とする。 表4 テーブルの配置方法 表5 テーブル配置検討表（未完成） 〔連携DBの設計・実装〕 1．連携DBのテーブル設計 表5のテーブル配置検討表に基づいて，情報共有対象のテーブル構造を検討した。“商品”，“会員”テーブルについては，データを統合した上で連携DBに配置することにして，図3のテーブル構造を作成した。 (1) “商品”テーブルの統合では，現行DBの“商品”テーブルの全情報をもつ。さらに，店舗販売区分，OL販売区分の列を追加し，各チャネルでの販売対象商品か否かを表す値を設定する。 (2) “会員”テーブルの統合では，次のようにテーブルを作成する。 ① OL販売システムの“会員”テーブルを連携DBに複製する。複製したテーブルに店舗販売システムの“会員”テーブルの全行を追加する。追加の際，店舗会員番号ごとに新たな会員番号を付与し，店舗会員とOL会員とを合わせて一意に識別する。店舗会員番号はそのまま使用するので，店舗会員番号と新たな会員番号とを関連付ける。さらに，会員区分の列を追加し，店舗会員とOL会員を識別する値を設定する。 ② 〔販売業務改善の要件〕におけるポイントの取扱いが可能になるように，必要な列を追加する。 図3 情報共有対象のテーブル構造（未完成） 2．会員の個人情報漏えい防止対策 “会員”テーブルを表4の配置方法⑤とする場合，店舗の従業員が全情報を参照することになり，情報漏えいのリスクが高まるので，次のアクセス制限を行う。 (1) アクセス制限の内容 ① 会員番号，氏名，ポイントは，全従業員に全会員の情報参照を許す。それ以外の情報（以下，機密情報という）の参照は，必要とする特定の役職及び特定の担当職務の従業員に限定する。 ② 機密情報の参照が認められた従業員であっても，参照できるのは従業員が所属する店舗の登録会員の情報だけに限定する。 (2) アクセス制限の実装 ① OL販売では，AP用のユーザIDとパスワードを用いてDBに接続する。ユーザIDとパスワードはAP内に隠ぺいする。 ② 店舗販売では，従業員ごとのユーザIDとパスワードを用いてDBに接続する。ユーザIDは従業員IDと同一で，パスワードは従業員が各自で管理する。 ③ “従業員”テーブルは，表4の配置方法③とし，連携DBにも配置する。 ④ 図4，5のSQL文を使ってビューを作成し，テーブル及びビューへの適切なアクセス権限を付与する。機密情報へのアクセス制御には，認可役職ロール，認可担当職務ロールを用いる。 ⑤ 図5のSQL文で作成したビューでは，従業員が所属する店舗の会員情報だけにアクセスを限定する。ただし，ポイント統合後の店舗会員については，統合先のOL会員情報も参照可能とする。 図4 “会員V1”ビュー作成SQL文 図5 “会員V2”ビュー作成SQL文（未完成） 3．販売分析データの作成（バッチ処理） 現行DBの“販売”，“販売明細”，“注文”，“注文明細”テーブルは，表4の配置方法③とし，連携DBにも配置する。これらのテーブルのデータを元に連携DBの販売ファクト，商品軸などの軸データをバッチ処理で作成する。 店舗販売業務，OL販売業務では，それぞれ，その日のうちに日締めを行っているが，日締時刻は日によって異なり，順序も決まっていない。バッチ処理中の元データ更新を避けるため，両業務とも日締めが完了したときに，該当テーブルのレプリケーションを自動的に無効化する。そのために，図6のテーブルを，表4の配置方法⑤として連携DBに配置し，日締め時に当該テーブルを更新する日締処理の追加，及び当該テーブルへの操作によって実行するトリガの定義を行うことにした。 図6 “日締管理”テーブルのテーブル構造 〔販売分析〕 1．販売分析業務のテーブル構造 現行店舗販売システムのテーブル構造（図1）を基に，連携DBに配置する販売分析業務のテーブル構造を設計する。“販売ファクト”テーブルは，伝票番号及び注文番号を一意に識別する販売IDを付与することにし，図7を作成した。 図7 連携DBに配置する販売分析業務のテーブル構造（未完成） 2．販売分析処理の検討 表1の処理例2について問合せの内容を整理し，表6を作成した。 表6 処理例2の問合せ（未完成） 設問1 〔テーブル配置の検討〕について，(1)，(2)に答えよ。 (1) 表5中の“会員”テーブルの配置検討について，①，②に答えよ。 ① 配置方法④とした場合，“会員”テーブルに双方向の同期が必要となるのは，業務上どのような場合か。具体的に30字以内で述べよ。 ② 配置方法④とした場合，OL販売システムの現行DBへの同期が失敗した場合に更新がロールバックされる店舗販売システムの処理がある。該当する処理名を表2の中から全て答えよ。 (2) 表5中の［a］～［e］に入れる適切な配置方法を答えよ。 設問2 〔連携DBの設計・実装〕について，(1)～(3)に答えよ。 (1) 図3中の［f］に追加する列名と列値の内容を表7にまとめた。記入済みの例に倣って空欄を埋め，表を完成させよ。 表7 追加する列名と列値の内容 また，“会員”テーブルの統合で追加した列間の検査制約を一つ挙げ，その内容を次の例に倣って40字以内で述べよ。 例 店舗販売区分が販売対象商品の場合，小分類コードはNULLでない。 (2) “会員V1”ビュー，“会員V2”ビューの具体的なアクセス権限付与先を答えよ。また，図5中の［g］～［j］に入れる適切な字句を答えよ。ここで，DB接続に用いられたユーザIDはCURRENT_USERで参照されるものとする。 (3) 図6の“日締管理”テーブルを用いたバッチ処理の制御について，処理年月日が現在日付に対応する行について，各業務の日締処理で行う操作を具体的に60字以内で述べよ。また，トリガの定義内容について，①実行の契機となる操作，②実行条件，③実行する処理の内容を答えよ。ここで，“日締管理”テーブルは，日締処理以外で追加，更新されることはないものとする。 設問3 〔販売分析〕について，(1)，(2)に答えよ。 (1) 〔販売業務改善の要件〕に従って，表1の販売分析処理が可能となるように，図7に追加すべきテーブル名を一つ挙げ，テーブル構造を答えよ。また，図7中の［k］～［m］に入れる列名を，それぞれ一つ又は複数答えよ。 なお，テーブル構造の解答については，主キーを表す実線の下線，外部キーを表す破線の下線も含めて，巻頭の表記ルールに従うこと。 (2) 表1の処理例2に用いる問合せについて，表6中の［ア］～［エ］に入れる適切なテーブル名又は内容を答えよ。