平成29年度午後Ⅱ試験問1：SQL、整合性制約、トリガ、レプリケーション、テーブル設計に関する問題

連載 [第4回] :

2018年3月19日(月)

はじめに

今回は、平成29年度午後Ⅱ試験問1の解き方について解説します。午後Ⅱ試験のポイントは問題選択にあります。問題文が13～4ページに及ぶため、問題選択を誤ると合格の可能性はほとんどなくなってしまいます。そのため問題文を2問ともざっと読み、合格に近づける問題を選択することが重要です。

今回解説する問題はSQLや整合性制約、トリガ、レプリケーション、テーブル設計に関するものです。頻出問題ではないので試験対策として学習する上での優先度は低いのですが、このような問題が得意な方は効率良く解答できると思います。

平成29年度午後Ⅱ試験問1に挑戦！

それでは、早速平成29年度午後Ⅱ試験問1に挑戦しましょう。問題文はこちらからダウンロードしてください。また、解答用紙はこちらに用意しました。解答例はこちらをご覧ください。

「表4 テーブルの配置方法」について

設問1の解説に入る前に、設問1を解答するために理解しておく必要がある、表4 テーブルの配置方法について説明します。

・配置方法①
配置方法①とするテーブルは、現行店舗販売システムのDB（以下、店舗DB）と現行OL販売システムのDB（以下、OLDB）間でデータを連携する必要がないデータの配置方法です。店舗DBにあり、連携不要なテーブルのイメージは以下の通りです。現行APは店舗DBのテーブルにアクセスしています。

・配置方法②
配置方法②とするテーブルは、APによって連携DBを追加・更新し、連携DBから現行DBに対してバッチで同期させます。

・配置方法③
配置方法③とするテーブルは、現行APによって現行DBを追加・更新し、現行DBから連携DBに対してバッチで同期させます。

・配置方法④
配置方法④とするテーブルは、現行APによって現行DBを追加・更新し、現行DBから連携DBに対してリアルタイムで同期させます。

・配置方法⑤
配置方法⑤とするテーブルは、連携DBのみに配置し、現行DBからは廃止します。

設問1の解説

(1)①配置方法④としたとき、会員テーブルに双方向の同期が必要となる場合
配置方法④において、会員テーブルに双方向の同期が必要となるのは、以下の条件を満たす場合です。

同一会員が店舗とOLの両方を利用する場合
→〔販売業務改善の要件〕(2)①のポイント統合をした場合
会員テーブルに更新処理を実行した結果を他方のDBに対してリアルタイムで更新処理をする必要がある場合
→〔現在の業務内容〕1.(4) ③の「使用したポイントは，即座に使用可能ポイントから差し引かれる」と〔現在の業務内容〕2.(4)②の「ポイント使用時の取扱いは店舗業務と同じである」より、顧客が購入時にポイントを使用した場合

以上のことから、解答は「ポイント統合後の会員が購入時にポイントを使用した場合」となります。

(1)②配置方法④とした場合、OL販売システムの現行DBへの同期が失敗した場合に更新がロールバックされる店舗販売システムの処理
表2 現行店舗販売システムのCRUDより、会員テーブルへの更新処理がロールバックの対象となるため、解答は「会員登録、POS入力、ポイント反映」となります(順不同)。

(2)表5の適切な配置方法

(a)(b)
〔販売業務改善の要件〕(1)①に「店舗販売業務とOL販売業務を統括する販売統括部を新設し，両業務に共通する商品情報及びそれに関連する商品分類を管理する」との記載があります。また、表5より商品テーブルは配置方法②であることから、大分類・中分類・小分類テーブルと商品分類テーブルはともに配置方法②となります。したがって、(a)(b)ともに「配置方法②」となります。
(c)(e)
表2と表3より、店舗在庫テーブルと出荷指示・出荷指示明細テーブルは店舗販売業務のみで使用するテーブルなので、OL販売システムとの連携は必要ありません。したがって、(c)(e)ともに「配置方法①」となります。
(d)
〔現在の業務内容〕1.(5)②に「エリアでは，倉庫ごと，商品ごとに実在庫数，引当済数を倉庫在庫として管理している」、〔現在の業務内容〕2.(6)に「倉庫では，商品ごとの実在庫数，引当済数を倉庫在庫として管理している」と、それぞれ記載されています。さらに、〔販売業務改善の要件〕(3)①に、「全エリアの倉庫からの配送を可能にして，店舗販売業務の取扱商品の多くをOL販売で取り扱えるようにする」と記載されています。これらのことから、店舗販売業務とOL販売業務の両方で、店舗在庫テーブルのデータを同時に更新する可能性があることがわかります。したがって、〔テーブル配置の検討〕(ⅴ)より、(d)は「配置方法⑤」となります。

設問2の解説

(1)図3中の会員テーブルに追加する列名と列値の内容
表7に記載されている列名が統合区分であることから、ポイント統合に関連する列が対象になると推測できます。〔販売業務改善の要件〕(2)③に「会員は，統合するポイントカードの店舗コードと店舗会員番号を指定する。複数のポイントカードのうち，1枚だけを存続させ，その他は廃止する」との記載から、ポイントカードの廃止有無を表す値を保持する列「廃止区分」と、ポイント統合の統合先の会員番号を保持する列「統合先会員番号」が必要となることがわかります。また、〔販売業務改善の要件〕(2)④の「ポイント統合の対象となったポイントカードの統合申請日，統合対象ポイントを記録し」より、ポイント統合の申請日を保持する列「統合申請日」と、ポイント統合時点の使用可能ポイントを保持する列「統合対象ポイント」が必要となることがわかります。したがって、解答は以下の通りとなります。

(1)会員テーブルの統合で追加した列間の検査制約
問題文には「一つ挙げ」と記載されていることから、解答は複数存在することが考えられます。

解1
〔連携DBの設計・実装〕1.(2)①に「店舗会員番号ごとに新たな会員番号を付与し，店舗会員とOL会員とを合わせて一意に識別する。店舗会員番号はそのまま使用するので，店舗会員番号と新たな会員番号とを関連付ける。さらに，会員区分の列を追加し，店舗会員とOL会員を識別する値を設定する」と記載されています。このことと図3の会員テーブルの構造から、会員区分が店舗会員の場合、店舗会員番号が必須となります。ここで注意すべきは、〔現在の業務内容〕1.(3)①に「店舗ごとに一意な店舗会員番号を付与する」と記載されていることです。以上から、解答は「会員区分が店舗会員の場合、店舗コード及び店舗会員番号はNULLでない」となります。
解2
別解として、(1) 図3中の会員テーブルに追加する列名と列値の内容の解答から、「会員区分が店舗会員かつ統合区分が統合済みの場合、統合先会員番号はNULLでない」でも構いません。

(2)“会員V1”ビュー，”会員V2“ビューの具体的なアクセス権限付与先

”会員V1“ビュー
〔連携DBの設計・実装〕2.(1)①に「会員番号，氏名，ポイントは，全従業員に全会員の情報参照を許す」と記載されています。図4の”会員V1“ビュー作成SQL文の内容は、会員テーブルの会員番号と氏名、およびポイントを参照する内容となっていることから、解答は「全従業員のユーザID」となります。
”会員V2“ビュー
図5の“会員V2”ビュー作成SQL文には「SELECT A.* FROM 会員 A」との記載があり、図3の会員テーブルの構造から、“会員V2”ビューは機密情報を参照できることがわかります。〔連携DBの設計・実装〕2.(2)④に「機密情報へのアクセス制御には，認可役職ロール，認可担当職務ロールを用いる」と記載されていることから、解答は「認可役職ロール及び認可担当職務ロール」となります。

(2)図5中の(g)～(j)に入れる適切な語句

(g)(h)
〔連携DBの設計・実装〕2.(2) ⑤に「図5のSQLで作成したビューでは，従業員が所属する店舗の会員情報だけにアクセスを限定する。ただし，ポイント統合後の店舗会員については，統合先のOL会員情報も参照可能とする」と記載されています。また、〔現在の業務内容〕1.(1) ②には、「店舗の従業員は，一つの店舗に所属し」と記載されています。さらに、図1の従業員テーブルの構造から、図5の前半のSELECT文は、会員テーブルと従業員テーブルを結合し、従業員IDがCURRENT_USERとなる従業員が所属する店舗の会員情報を検索する内容にすれば良いことがわかります。したがって、(g)は結合対象となるテーブル名の「従業員」とし、(h)にはテーブルの結合条件と検索条件を入れます。図1の従業員テーブルの構造と図3の会員テーブルの構造から、結合条件として、店舗コードを使用します。また、従業員IDがCURRENT_USERである条件を含め、(h)は「B.店舗コード = A.店舗コード AND B.従業員ID = CURRENT_USER」とします。
(i)(j)
図5の後半のSELECT文は、統合先のOL会員情報を検索するSQLとします。そこで、(i)は「A.会員番号 = B.統合先会員番号」とし、(j)は「C.店舗コード = B.店舗コード AND C.従業員ID = CURRENT_USER」とします。

(3)各業務の日締処理で行う操作
〔連携DBの設計・実装〕3.に「店舗販売業務，OL販売業務では，それぞれ，その日のうちに日締めを行っているが，日締時刻は日によって異なり，順序も決まっていない」と記載されています。また、図6の記載内容から、先に実行した日締処理が完了したタイミングで日締管理テーブルに行を追加し、完了した日締処理のフラグを完了、完了していない日締処理のフラグを未完了とします。また、後から実行した日締処理が完了したタイミングで、該当処理年月日の完了した日締処理のフラグを完了に更新します。したがって、解答は「行がなければ対応するフラグを完了、他方を未完了として行を追加し、行があれば対応するフラグを完了にして更新する」とします。

(3)トリガの定義内容
〔連携DBの設計・実装〕3.に「バッチ処理中の元データ更新を避けるため，両業務とも日締めが完了したときに，該当テーブルのレプリケーションを自動的に無効化する」と記載されています。つまり、図6の日締管理テーブルの店舗日締フラグとOL日締フラグが両方とも完了になったらレプリケーションを無効化するトリガを定義すれば良いことになります。したがって、①実行の契機となる操作は「更新」、②実行条件は「店舗日締フラグとOL日締フラグが共に完了であること」、③実行する処理は「レプリケーションを無効化する」となります。

設問3の解説

(1)図7に追加すべきテーブル構造
〔販売業務改善の要件〕(4)②に「会員の性別，居住地域，年齢層を用いて分析を行う」と記載されていますが、図7には会員軸のテーブルが存在しません。他の軸のテーブル構造を参考に、以下のテーブル構造を追加します。

会員軸 ( 会員軸ID, 性別, 居住地域, 年齢層 )

(1)図7の(k)～(m)に入れる列名

(k)
販売ファクトテーブルを会員軸で分析するためには、販売ファクトテーブルに会員軸と結合するための外部キーが必要です。したがって、解答は「会員軸ID」となります。
(l)
〔販売業務改善の要件〕(4)①に「店舗販売とOL販売をチャネル区分で識別し，チャネル横断的に販売分析処理を行えるようにする」と記載されています。このことから、解答は「チャネル区分」となります。
(m)
〔現在の業務内容〕2.(2)②に「商品分類は3～5階層構成である」と記載されています。その一方で、表1の処理例1に「OL販売分析で用いている商品分類を第1～3階層の範囲内から選択し」と記載されています。また、図2の現行OL販売システムにおける商品分類テーブルの主キーは分類コードであることから、(m)は「分類コード1, 分類コード2, 分類コード3」とします。

(2)表6中の(ア)～(エ)に入れるテーブル名または内容　※(ア)(イ)の組と(ウ)(エ)の組は順不同
表6の手順②の項目と内容を参考にしながら解答します。

(ア)(イ)
表1の処理例2に「関東地域に居住する30代の女性が購入した」と記載されており、これらの属性を保持するのは会員軸テーブルであることから、(ア)は「会員軸」、(イ)は「会員軸IDで等結合し、性別が女性、かつ、居住地域が関東地方、かつ、年齢層が30代と一致する行を選択」とします。
(ウ)(エ)
表1の処理例2に「店舗販売の大分類コードが’K001’の件数を」と記載されており、これらの属性を保持するのは商品軸テーブルであることから、(ウ)は「商品軸」、(エ)は「商品軸IDで等結合し、大分類コードが’K001’と一致する行を選択」とします。