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

共通の文章

データベースの実装に関する次の記述を読んで,設問1ー3に答えよ。 D社は,マンションの開発及び販売を手掛ける不動産会社である。D社では,販売 業務で利用している商談管理システムの老朽化に伴い,システムの刷新を行ってい る。 【業務の概要] 1.物件 (1)販売対象のマヤンション全体を物件という。物件内の建物を棟といい,HAD 個々の住居を住戸という。 (2)物件内の住戸を幾つかの販売期に分けて販売する。 2.組織 (1)物件の販売を行う拠点として,物件の近隣に販売センタを設営している。 (2)販売センタには,販売業務を行う担当者を配置している。 (3)販売センタの業務時間は毎日10て19 FF CHS, 3.販売業務 (1)物件に興味をもった人を顧客といい,顧客ごと物件ごとに,商談の進捗度, 接触回数,受注確度,希望面積購入予算などを記録する。 (2)担当者は,資料請求の受付,資料の送付,販売センタへの来場予約受付,来 場時の応接,希望条件・購入予算などを記した要望書の取得,購入申込みの受 付,抽選会の案内・実施などの営業活動を行う。担当者が顧客との間で行うこ れらの活動を追客といい,“受付”(資料請求。来場予約など),“実施(資料 送付,来場時の応接など),“応対"(購入申込受領,問合せ対応など)の区分に 分けて記録する。 (3)追客を行うたびに商談管理システムにその内容を登録する。 4.販売分析業務 販売促進のための分析業務である。業務に用いるミスマッチ分析表の例を図1に 示す。図1は,住戸の仕様(販売価格,専有面積)と顧客の要望(購入予算,希望 面積)との一致度合いを確認するための帳票で,販売価格,購入予算を範囲によ って価格帯に,専有面積,希望面積を範囲によって面積帯に分け,価格帯と面積 ー6一 帯の組ごとに“住戸数(戸)ノ顧客数(名)”を表示する。空白は該当なしを表す。 物件コード:1234567物件名:〇〇マンション ie cot|So|roar|me ES 面積帯ke 50項以上|60 nik|70可以上ge sramam。|4戸28名|22名|||| Takata|[ae Zee[eR Zoe]25名| Tames TanAM|[se AAR Aelia,AB sTammketamaM||RZ pr Aa|2の名 erm OT|||hノOCP |図1ミスマッチ分析表の例 [商談管理システムの概要] 1.関係スキーマ 商談管理業務の関係スキーマを図2に,主な属性とその意味・制約を表1に示 す。 大エリア(大エリアコード,大エリア名) 小エリア(小エリアコード,小エリア名,大エリアコード) 住所(住所コード,都道府県名,市区町村名,TA,小エリアコード) 鉄道事業者(鉄道事業者コード,鉄道事業者名) 沿線(治線コード,沿線名,鉄道事業者コード) 駅(沿線コード,駅コード,駅名,住所コード) 担当者(担当者コード,販売センタコード,氏名) 販売センタ(販売センタコード,販売センタ名,所在地住所コード,番地その他) 物件(物件コード,物件名,販売センタコード,所在地住所コード,番地その他,…) 最寄り駅(物件コード,沿線コード,駅コード,徒歩分数) 販売期(物件コード,販売期コード,販売期名,販売開始日,販売終了日,…) 棟(物件コード,棟コード,棟名称) 住戸(物件コード,棟コード,住戸コード,販売価格,販売期コード,間取り,専有面積。…) 顧客(顧客コード,氏名,住所コード,メールアドレス,電話番号,生年月日, 名寄せ先顧客コード,…) 商談(顧客コード,物件コード,商談ステータス,最新接触日時,受注確度,希望面積, 購入予算,…) 追客(顧客コード,物件コード,連番,追客区分,接触日時,追客種別,…) 受付(顧客コード,物件コード,連番,開始予定日時,終了予定日時,…) 実施(顧客コード,物件コード,連番,実施結果メモ,…) 応対(顧客コード,物件コード,連番,応対内容,…) 契約(顧客コード,物件コード,棟コード,住戸コード,契約ステータス,成約価格,…) 図2関係スキーマ(一部省略) 0 ae 表1主な属性とその意味・制約 ok 願客を一意に識別するコード(6桁の半角敵数字) 物件コード物件を一意に識別するコード(7桁の半角英数字) 販売価格,購入予算|販売価格,購入予算の金額(単位:BA,0~99,999) 専有面積希望面積住戸の面積(単位:平方メートル,0.00て999.99) +(接触開始),2'(商談中),“3'(要望書取得) 商談ステータス‘ay’(購入申込済),‘5°(抽選済),‘6(仮契約済) の(本契約済),"8(引渡済),9(追客中止)のいずれか |契約ステータス|"@'(仮契約済),7(本契約済),"8'(引渡済)のいずれか fase|"(受付),②(実施),"3'(応対)のいずれか =など,追客の種類を識別するコード(2桁の半角英数字) seen資料請求,資料送付,来場予約来場応接,購入申込受領,問合せ対応 接触日時追容ごとに顧客との接触を開始した日時 最新接触日時商談に紺付く追客の最新の接触日時 2.外部システムとの連携 商談管理システムは,次のように外部システムとのデータ連携を行っている。 ①)物件管理システムから物件情報(大エリア,小エリア,EM,鉄道事業者, 沿線,駅,物件,最寄り駅,販売期,棟,住戸)の変更データを受領する。 (2)自社のWebサイトから資料請求及び来場予約データを受領する。 (3)契約システムから毎日複数回,契約データを受領する。 (4)受領したデータは専用のアプリケーションソフトウェア(以下,APという) を用いて商談管理システムのデータベースに反映する。 3.処理の例 商談管理システムにおける処理の例を表2に示す。 —8ーー 表2処理の例 処理1物件管理システムから受領した物件情報の変更データを反映する。APは,関係ごとに 追加,更新を記録したファイルを読み込み,データベースへの追加,更新を行う。 Webサイトから受信する資料請求データには,資料請求日時,物件コード,顧客コー 処理2ド,氏名,住所コードなどが記録されている。同様に,来場予約データには,開始予定 日時,物件コード,顧客コード,氏名などが記録されている。APは,追客の追加,MH 規顧客の追加,商談の追加又は最新接触日時の更新を行う。 処理3同一の顧客を名寄せする。APは,一定の条件に基づいて同一の顧客を抽出し,名寄せ 元の名寄せ先顧客コードを更新する。 契約システムから受領した契約データを基に,契約の契約ステータスを更新し,BRE 処理4|、 追加する。 指定した物件コード,顧客コードに該当する全ての追客を対象に,接触日時,追客種 処理5別,追客概要を商談管理画面に表示する。追客概要は,本処理において追客区分ごとに 固有な属性値を基にして作成した文字列である。例えば,来場予約受付の追客概要は, “予約日時:2021-10-10 11:00~12:00”のようになる。 指定した物件コード,期間に該当する追客状況表を出力する。追客種別ごと接触日時の 処理6|日ごとに件数を集計し,物件コード,年月日,資料請求数,来場予約数,来場応接数, 購入申込受領数を出力する。 図1のミスマッチ分析表を出力するために,指定した物件コードに該当する住戸の販売 価格から価格帯を,専有面積から面積帯を求め,両者の組ごとに住戸数を集計する。同 処理7|じように,商談の購入予算から価格帯を,希望面積から面積帯を求め,両者の組ごとに 顧客数を集計する。価格帯と面積帯の組ごとに,該当する住戸数と顧客数の片方又は両 方を対応させて,価格帯,面積帯,住戸数,顧客数を抽出する。 (RDBMSの主な仕様] 商談管理システムで利用するRDBMSの主な仕様は次のとおりである。 1.。ページ RDBMSとストレージ間の入出力単位をページという。同じページに異なるテー ブルの行が格納されることはない。 2.テーブル・索引 (1)テーブルの列にはNOT NULL制約を指定することができる。NOT NULL制約 を指定しない列にはNULLか否かを表す1バイトのフラグが付加される。 (2)主キー制約には,主キーを構成する列名を指定する。 (3)参照制約には,列名,参照先テーブル名,参照先列名を指定する。制約に違 反する追加,更新,削除は拒否される。 (4)検査制約には,同一行の列に対する制約を記述する。 にース9—— (5)索引には,ユニーク索引と非ユニーク索引がある。 3.トリガ機能 テーブルに対する変更操作(追加,更新,削除)を契機に,あらかじめ定義し た処理を実行する。 (1)実行タイミング(変更操作の前又は後。前者のトリガをBEFOREトリカ,後 者のトリガをAFTERトリガという),列値による実行条件を定義することがで きる。 (2)トリガ内では,変更操作を行う前の行,変更操作を行った後の行のそれぞれ に相関名を指定することで,行の旧値,新値を参照することができる。 (3)BEFOREトリガの処理開始から終了までの同一トランザクション内では,© のテーブルに対しても変更操作を行うことはできない。 (4)トリガ内で例外を発生させることができる。 (5)トリガ内で発生した例外は,実行の契機となった変更操作に返却される。ト リガ内で例外が発生した場合,変更操作を行うトランザクションは終了しない ので,明示的なコミット又はロールバックが必要である。 4.バックアップ機能 (1)バックアップの単位には,データベース単位,テーブル単位がある。 (②)バックアップには,取得するページの範囲によって,全体,増分,BOO 3 fl 類がある。 ①全体バックアップには,全ページが含まれる。 ②増分バックアップには,前回の全体バックアップ取得後に変更されたペー ジが含まれる。ただし,前回の全体バックアップ取得以降に増分バックアッ プを取得していた場合は,前回の増分バックアップ取得後に変更されたペー ジだけが含まれる。 ③差分バックアップには,前回の全体バックアップ取得後に変更された全て のページが含まれる。 (3)全体及び増分バックアップでは,取得ごとにバックアップファイルが作成さ れる。差分バックアップでは,2回目以降の差分バックアップ取得ごとに,前回 の差分バックアップファイルが最新の差分バックアップファイルで置き換えら れる。 ー10一 5.復元機能 (1)バックアップを用いて,バックアップ取得時点の状態に復元できる。 (2)復元の単位はバックアップの単位と同じである。 (3)データベース単位の全体バパックアップは,取得元とは異なる環境に復元する ことができる。 6.更新ログによる回復機能 (1)バックアップを用いて復元した後,更新ログを用いたロールフォワード処理 によって,障害発生直前又は指定の時刻の状態に回復できる。データベース単 位の全体バックアップを取得元と異なる環境に復元した場合も同様である。 1(2)一つのテーブルの回復に要する時間は,変更対象ページのストレージからの 読込み回数に比例する。行の追加時には,バッファ上のページに順次追加し, 空き領域を確保してページが一杯になるごとに空白ページを読み込む。行の更 新時には,ログ1件ごとに対象ページを読み込む。バッファ上のページのストレ ージへの書込みは,非同期に行われるので,回復時間に影響しない。 7.エクスポート機能 (1)テーブル単位にデータをファイルに抽出することができる。 (2)エクスポート対象テーブルの行の抽出条件を指定することができる。 8.インポート機能 (1)エクスポート機能で抽出したファイルを,インポート先の環境の同じ構造の テーブルに格納することができる。 (2)インポート先のテーブルに主キーが一致するデータが存在する場合の動作と して,処理しない(SKIP),又は更新する(MERGE)のいずれかのオプション を指定することができる。未指定時はSKIPを指定した場合と同等となる。 〔実装するテーブルの設計〕 1.テーブルの列の追加 各テーブルには,登録担当者コード,登録TS,更新担当者コード,更新TSの各 列を追加する。登録TSには行追加時の時刻印を,更新TSには行更新時の時刻印 をそれぞれ設定する。また,行の削除は論理削除によって行うこととし,削除フ ラグ列(0:未削除,1:削除済)を追加する。 =ij= 2.関係“追客”及びそのサブタイプの実装方法検討 関係“追客”と,関係“受付",“実施”,“応対"は,概念データモデルにおい てスーパタイプとサブタイプの関係にある。スーパタイプ,サブタイプのテーブ ルへの実装には次の方法わて③があり,それぞれに,他の方法に比べて格納効率 が悪い,他の方法では不要な選択・結合・和集合が必要になる,などの短所があ る。そこで,表3を作成し,テーブル構造の特質に由来する実装上の短所をまとめ た。短所がない場合は“ー"”を記入する。 方法スーパタイプと全てのサブタイプを一つのテーブルにする。 方法のスーパタイプ,サブタイプごとにテーブルにする。 方法サブタイプだけを,それぞれテーブルにする。スーパタイプの属性は, 列として各テーブルに保有する。 表3実装方法の短所(未完成) 項目実装方法の短所 人 テーブル定義による一意性制、 サプタイプの固有属スーパタイプの属 c|スーパタイプを参照する処理 の性能 単一つのサブタイプを参照する 処理の性能 全部のサブタイプを参照するwin Aan スーパタイプと単一のサプタsm 6スーパタイプと全部のサブタ イプを参照する処理の性能 表3を基に,BR SER”の実装方法を次のように判断した。 ・方法③は,項目Aの短所からサブタイプ全体での主キーの一意性を,テーブルの 一意性制約以外の方法によってチェックする必要があり採用し難い。 ・表2中の処理5は表3の項目|=|に,処理6は表3の項目|オ|に 該当する。方法@では,処理5の応答時間に不安がある。 ・方法やは,項目Bの短所による不利益はあるものの,BAD,Fに該当する処理 は少なく,影響が限定的であることから方法①を採用する。 3.制約の実装検討 外部システムとの連携によってデータの整合性が失われることのないように, データベースの制約を定義する。 (1)参照制約 図2の関係スキーマのうち,物件情報である大エリア,小エリア,住所,鉄道 事業者,沿線,駅,物件,最寄り駅,販売期,棟及び住戸をそのままテーブル として実装し,参照制約を定義するとしたら,表2中の処理1では,制約に違反 しないように各テーブルへの変更反映処理を行わなければならない。そこで, テーブルを順序付けしてグループ番号1ご6に分け,参照制約に違反しない限り できるだけ小さいグループ番号に配置することにして表4を作成した。グループ 番号順に直列に処理を行い,同じグループ内のテーブルへの処理は並行して行 Dy 表4変更データ反映処理のグループとテーブル(未完成) 時一 (2)参照制約以外の制約 連携データの反映による整合性の喪失を防ぐための制約を,一意性制約, NOT NULL制約,検査制約,BEFOREトリガ,AFTERトリガのうち適切な方法 を用いて定義する。制約とその定義内容を表5にまとめた。ここで,トリガ内の 行の旧値を参照する相関名をOROW,新値を参照する相関名をNROWと定義す るものとする。 ー13一 表5制約とその定義内容(未完成) 制約①住戸には必ず0よりも大きい販売価格が設|“住戸”テーブルに,"販売価格>0”を 定されていなければならない。チェックする検査制約を定義する。 同じ顧客,接触日時及び追客種別の追客を|“追客”テーブルに, 制約②|行うことはない。例えば,重複する資料請 求データがあればエラーを通知する。 契約ステータスが後退する(例:本契約済|“契約”テーブルの行の更新を契機に, 制約③|が仮契約に戻も)ことはない。そのような 契約の変更にはエラーを通知する。 契約ステータスはできるだけ最新の状態で|“契約”テーブルの行の更新を契機に, 制約のなければならず,契約手続の開始後は,契INROW.契約ステータスに対応する商談ス ー約ステータスと商談の商談ステータスは一|テータスの値を設定して“商談”テーブル 致していなければならない。を更新するAFTERトリガを定義する。 [問合せの検討〕 表2の処理7に用いる問合せの内容を,次の要領で表6に整理した。 (1)行ごとに構成要素となる問合せを記述する。結果を他の問合せで参照する場合 は,行に固有の名前(以下,問合せ名という)を付ける。 (2)列名又は演算には,テーブルから射影する列名又は演算によって求まる項目を “項目名[演算の内容]”の形式で記述する。 (3)テーブル名又は問合せ名には,参照するテーブル名又は問合せ名を記入する。 (4)選択叉は結合の内容には,テーブル名又は問合せ名ごとの選択条件,結合の具 体的な方法と結合条件を記入する。 表6表2の処理7に用いる問合せの内容(未完成) 問合せ名列名又は演算TOTNES ayy gee ony 問合せ名 棟コード,住戸コード, 価格帯三[販売価格3000未満:1, 4000未満:2,5000未満:3,mon Heme aay Ml 6000未満:4,6000以上:5],住戸athlon:ie 面積帯=[専有面積50未満:1,~ 60未満:2,70未満:3,80未満:4, 80以上:5] 価格帯,面積帯,住戸数=[価格帯,面2 積準でグループ化した行数]ii 顧客コード, 価格帯=ニ[購入予算3000未満:1, 4000未満:2,5000未満:3,ca ais T1 6000未満4,6000以上:51,商談EMMA 面積帯=[希望面積50未満:1,-+ 60未満:2,70未満:3 80未満:4, 80 LAE:5] 価格帯,面積帯,RRR eH,fees aes=([サ|j M2の全行とT2の全行を選 R1面積帯=|di.M2,T2択し,価格帯,面積帯で完 EA,顧客数BINGE 注記網掛け部分は表示していない。 表6中の“M1”の問合せを実装するためのSQL文の案を図3に示す。案1は,A 合せをそのまま実装するもので,案2 ld,“価格帯"”及び“面積帯”"テーブルを追加 することで,SQL文の変更なしに価格帯,面積帯の範囲を変えられるようにするも のである。案2の①は“価格帯”及び“面積帯"”テーブルを作成するSQL文,②は “価格帯"テーブルに行を追加するSQL文,③は追加したテーブルを用いて問合せ の結果行を得るSQL文である。 HR PAA OBRAEEOS ERT ee SELECT棟コード,住戸コード, CASE WHEN販売価格3000 THEN 1 WHEN販売価格く4000 THEN 2 WHEN販売価格く5999 THEN 3 WHEN販売価格6000 THEN 4 ELSE 5 END AS価格帯, CASE WHEN専有面積50 THEN 1 WHEN専有面積<69 THEN 2 WHEN専有面積70 THEN 3 WHEN専有面積80 THEN 4 ELSE 5 END AS面積帯 FROM住戸WHERE物件コード=:hv1: 案2 SQL文の変更なしに価格帯,面積帯の範囲の変更を可能にする。 |①“価格帯”テーブル及び"面積帯"テーブルを作成する。 CREATE TABLE価格帯(価格帯SMALLINT,下限値INTEGER,上限値INTEGER, PRIMARY KEY(価格帯)): CREATE TABLE面積帯(面積帯SMALLINT,下限値DECIMAL(5,2),上限値DECIMAL(5,2), PRIMARY KEY(面積帯)): |②RAE”TRF OVILAT BINT el INSERT INTO価格帯VALUES(1,); INSERT INTO価格帯VALUES(2,Y's :(中略) INSERT INTO価格帯VALUES(5): |“価格帯",“面積帯”テーブルを使用して問合せの結果行を得る。| SELECT A.棟コード,A.住戸コード,B.価格帯,C.面積帯FROM住戸A INNER JOIN価格帯B ON INNER JOIN面積帯C ON ees WHERE A.物件コード=:hv1: 注記1ホスト変数hyv1には,指定された物件コードが設定される。 注記2網掛け部分は表示していない。 図3“MI”の問合せを実装するSQL文の検討例(未完成・一部省略) [バックアップ・リカバリの検討] 1.障害発生の想定 バックアップ・リカバリの検討に当たり,本番環境のデータベース(以下,本 番DBという)で想定される障害ケースを検討し,表7に整理した。ここで,デー タベース単位の全体バックアップを毎日0時に取得しているものとする。 表7想定した障害ケース EE ディスク装置に障害が発生し,テーブルのデータが全損した。 表2の処理2で,外部システムの不具合によって誤った来場予約データが大量に 登録された。誤登録の行の更新担当者コードはSYSRAI01,誤登録の期間は 障害ケース②|2021-07-20の15:05:00~15:10:00である。論登録発生の前後の時間帯では,断続 的に追客を記録する業務を行っている。また,誤登録の期間中,当該担当者コ ードによって誤登録以外の処理は行われていない。 表2の処理3で,“顧客"テーブルのうち,名寄せ先顧客コードがNULL OFF が誤更新によって不正な状態となった。誤更新が行われた直前の日時は2021- 障害ケース③|07-21 13:45:11 CHV,誤更新の発生中にも“顧客”テーブルへの追加及び更新 が行われている。また,誤更新の発生後,商談管理システムは参照系機能だけ を利用可能な状態で稼働させ,後続の処理への影響はない。 2.障害ケース①の検討 障害ケース①のリカバリは,ディスク装置の復旧,バックアップからの復元, 更新ログによる回復によって行う。このうち,“商談”及び“追客"テーブルの更 新ログによる回復がリカバリに要する時間の大半を占めるので,回復に掛かる時 間を試算し,業務への影響を調べることにした。試算に先立って,試算対象テー ブルのデータ量及び更新ログ件数見積りを表8にまとめた。 表8試算対象テーブルのデータ量及び更新ログ件数見積り ーー=平均行長|1ページ当た|1時間当たりの更新ログ件数 BME|イト)[vows 注記テーブルを格納する表領域のページサイズを8.000バイト,空き領域率を10%とする。 (1)回復に要する時間の試算 障害が18:00に発生し,10:00から8時間分の各テーブルへの追加,更新ログ による回復を想定し,更新ログが毎時均等に発生すると考える。また,1ページ 当たりのストレージへのTOを10ミリ秒(0.01秒)とし,ストレージへの1O以 外のCPU処理,索引探索,ネットワーク通信などに掛かる時間を考慮しないも のとして,次のように試算した。 ①障害発生時点で,“商談"テーブルには,追加40,000件,更新400.000件の更 新ログがある。回復の際,ストレージへのTOは,追加では1ページ当たりの 平均行数まではバッファ上で処理するので4,000回,更新ではバッファヒット 率を0とすると400,000回,合わせて404,000回発生する。よって,回復に必 要な時間は| 。|秒になる。 ②同様に,“追窒"テーブルでは,回復時のストレージへのIOは,追加では |5 dm,更新では[|<|回発生するので,回復に必要な時間は [a Joes. (2)回復時間短縮の対策 (1)の試算では回復時間が長い。回復時間を短縮するために,毎日12:00, 14:00,16:00に“商談”及び“追容”テーブルのテーブル単位の増分又は差分バ ックアップを取ることを検討し,次の結論を得た。 ©(Lc|時間分の更新ログを適用すればよいので,“商談”テーブルの回 復時間は,|{|秒になる。 ②バックアップからの復元に要する時間を考慮すると,“追客”テーブルは, 増分と差分のどちらの方式でもほとんど変わらないが,“商談”テーブルは, 増分バックアップよりも時間が掛からない差分バックアップを採用する。 3.障害ケース②の検討 障害ケース②は,バックアップの復元及び更新ログによる回復によって論登録 発生直前の日時の状態にする方法では問題を解決できない。そこで,MAMA データを削除するため,次の処理を順次実行することにした。 ・誤登録した“追窒”テーブルの行を削除状態に更新する。 ・誤登録によって作成された“商談"テーブルの行を削除状態に更新する。 ・誤登録によって作成された“顧客”テーブルの行を削除状態に更新する。 ・“商談”テーブルの最新接触日時を更新する。 “商談”テーブルの最新接触日時を更新するSQL文を図4に示す。ここで,更 新担当者コード,更新TSを更新する処理は省略している。 ー18一 UPDATE商談S SET S.最新接触日時=( SELECT[gz FRou追客T WHERE[_』|Apl:]Am[」|) WHERE更新TS BETWEEN CAST('2021-07-20 15:05:00,000000’AS TIMESTAMP) AND CAST('2021-07-20 15:10:00.000000’AS TIMESTAMP) AND更新担当者コード=’SYSRAION AND削除フラグ=0; 図4"商談テーブルの最新接触日時を更新するSOL文(未完成) 4.障害ケース③の検討 障害ケース③では,誤更新が行われた行だけを誤更新前の状態に戻し,誤更新 の原因を排除した上で処理3を再実行することとした。そこで,本番DBとは別に, 作業用のデータベース(以下,作業用DBという)を用意し,作業ミスに備えて本 番DBの“顧客”テーブルの全ての行を退避した上で,RDBMSの機能を利用した 復旧手順を検討し,表9に整理した。 表9障害ケース③の復旧手順(ATER) 人DB RAB |手順1|エクスポート機能“顧客”テーブルの全ての行を抽出する。 手順2|復元機能作業用DBーー ese 2021-07-21の0時に取得した本番DBのバック EE a較|meme mmsa間間間間間還 ET em較較|aa|mr間間還還還間 手順6|[-。。|-。。|吉本新の原因を排除して処理3を再実行する。 a mem|mir還還還還還 注記1 RDBMSの機能には,(RDBMSの主な仕様〕に記載されている機能名を一つ記入する。 注記2作業対象DBには,作業用DB,本番DBのいずれかを記入する。

共通の文章

製品物流業務に関する次の記述を読んで,設問1,2に答えよ。 E社は中堅市販薬メーカである。社の顧客には,医薬品卸業と医薬品の量販店チ ェーンがある。以前は医薬品卸業が主な顧客であったが,近年は量販店チェーンと の取引が増えている。両者の取引のやり方は大きく異なるので,今回量販店チェー ン専用のシステムを開発することにして,概念データモデル及び関係スキーマを設 評した。 【設計の前提となる業務】 1.社内の組織の特性 (1)物流拠点 ©社の製品物流の拠点であり,商品の在庫Ma,出荷などの機能をもつ。 ②物流拠点は,全国に6拠点あり,物流拠点ごとに複数の配送地域をもつ。 (②)配送地域 ①物流拠点から顧客の納入先へ,1台のトラックで1日に配送できる範囲の地 域であり,配送地域コードによって識別し,配送地域名をもつ。 ②配送地域は,隣接する複数の郵便番号の地域を合わせた範囲に設定してい る。一つの郵便番号の地域が幾つかの配送地域に含まれることはない。 2.顧客の組織の特性 GO rar RA ①量販店チェーンとは,ブランド,外観,サービス内容などに統一性をもた せて多店舗展開している医薬品の小売業であり,全国又は一部地方に集中し て店舗展開している。 Qチェーン法人はその法人であり,チェーン法人コードによって識別する。 2)チェーンDC(DCは,物流センタの英語(Distribution Center)の頭文字) ①量販店チェーンの物流センタである。チェーンDCコード及びチェーンDC 名を顧客から知らされ,立地するE社の配送地域を設定して登録し.チェーン 法人コードとチェーンDCコードによって識別する。 @チェーンDCは,次に記すチェーン店舗の注文をまとめて社を含む仕入先 に発注(E社にとっての受注)する。 ③E社は,受注した商品の全てを受注したチェーンDCに対して納入する。 ®チェーンDCH,社を含む仕入先から納入を受けた商品をチェーン店舗に 配送する役割を果たしている。 (3)チェーン店舗 ①量販店チェーンの個々の店舗である。チェーン店舗コード及びチェーン店 舗名を顧客から知らされて登録し,チェーン法人コードとチェーン店舗コー ドによって識別する。 @チェーン店舗は.いずれかーーつのチェーンDCに属している。チェーンDC には,通常数十から百数十のチェーン店舗が属している。 3.商品の特性 (1)商品 ①EE社が製造販売する医薬品であり,商品コードによって識別する。 ②商品には,PB商品とNB商品があり,流通方法区分で分類している。 ・PB商品は,E社と特定の量販店が協業する量販店独自ブランドの商品であ る。E社が広告宣伝費を掛けない代わりに,量販店に低価格で販売すること ができる商品である。PB商品は,どのチェーン法人のものかをもつ。 ・NB商品は,E社が製造するメーカブランドの商品である。E社が広告宣伝 費を掛けて消費者の認知を形成する商品である。NB商品は,売上金額のラ ンクをもつ。 ③商品の外観を荷姿と呼ぶ。荷次にはケースとピースがある。人荷姿は荷姿区 分によって識別する。 ・商品ごとに定まった数で箱詰めしたものをケース,ケースを開梱し,Ha ら出した一つ一つのものをピースと呼ぶ。 ・商品ごとに,ケースに入っているピースの数を表す入数をもつ。 (2)商品の製造ロット ①商品ごとの製造単位である。製造ロットには,商品ごとに昇順な製造ロッ ト番号を付与している。 ②製造ロットには,いつ製造したか分かるように製造年月日を,いつまで使 用できるか分かるように使用期限年月を記録している。 =gg= 4,締め契機 ①社は受注を随時受け付けているが,受注後すぐに出荷するのではなく,受け 付けた受注を締めて出荷指示を出すタイミングを定めている。このタイミング を締め契機という。 ②締め契機は,平日に1日5回,土曜日に1日3回,時刻を定めて設けており, 年月日とその日の何回目の締めかを示す"回目"で識別している。 ⑨③チェーン法人ごとに適用する締め契機は.チェーン法人と協議の上で,週3回 程度に設定している。 5.物流拠点の在庫 E社では,在庫を引当在庫と払出在庫で把握している。 ①引当在庫は,物流拠点,商品,製造ロットの別に,その時点の在庫数,引当 済数,引当可能数を記録するもので,商品の引当てに用いる。 ②払出在庫は,物流拠点,商品,製造ロット,荷姿の別に,その時点の在庫数 (荷姿別在庫数)を記録するもので,商品の出庫の記録に用いる。 6.引当てのやり方 ①古い製造ロットの商品から順に引き当てる。 ②顧客によっては,ロット逆転禁止の取決めを交わしている。ロット逆転禁止 とは,チェーンDCごとに,前回納入した製造ロットより古い商品を納入するこ とを禁じることである。この取決めを交わしていることは,チェーン法人ごと に設定するロット逆転禁止フラグで判別する。ロット逆転禁止の取決めを交わ している顧客の場合,チェーンDCと商品の組合せに対して,最終で引き当てた 製造ロット番号を記録する。 ③引当ては,同じ締め契機の受注について,早く入った受注から順に行う。 7.出庫のやり方 ①E社では,出庫を種まき方式で行っている。一般に種まき方式とは,行き先に かかわらず同じ品をまとめて出庫し,それを種に見立てて行き先別に仕分ける ことを品ごとに行うやり方である。 ②出庫は,物流拠点ごとに締め契機の対象の受注に対して行う。 ③出庫指示は商品別製造ロット別に出し,出庫実績は商品別製造ロット別荷次 別に記録する。 ー24一 8.梱包のやり方 商品の梱包のやり方は,顧客の方針で店舗別梱包又は商品カテゴリ別梱包のい ずれかの指定を受ける。指定はチェーンDCごとにされ,梱包方法区分で判別する。 ①店舗別梱包は,チェーンDCの仕分け作業の効率を優先するやり方である。 ・チェーンDCは,E社を含む仕入先から納入された梱包を崩さずにチェーン店 舗へ送る。 ・これを可能にするために,チェーンDCが担当するチェーン店舗ごとの梱包に よる納入が求められる。 ・店舗別梱包では,受注で指定される梱包対象の店舗はーつである。 ②商品カテゴリ別梱包は,チェーン店舗での品出し作業の効率を優先するやり 方である。 ・チェーン店舗の棚は,風邪薬,胃腸薬,目薬など商品カテゴリ別である。 ・チェーンDCは,E社を含む仕入先から納入を受けた商品をまとめて,HLA じ商品カテゴリ別に梱包し直して店舗へ送る。 ・これを可能にし,かつ,チェーンDCでの仕分け作業を簡易にするために,商 品カテゴリ別の梱包による納入が求められる。 ・商品カテゴリは,どの顧客も似ているが微妙に異なり,チェーン法人コード と商品カテゴリコードによって識別する。 ・商品カテゴリコード及び商品カテゴリ名は顧客が使っている値を用いる。 ・また,あらかじめどの商品をどの商品カテゴリにするかを知らされているの で,商品カテゴリの明細として商品を設定している。 9.業務の流れと情報 業務の流れを図1に示す。業務の流れの中で用いられる情報を次に述べる。 受注出荷指示出庫指示出庫実績梱包実績出荷実績 凡例||業務機能一ヶ情報の流れ 図1業務の流れ ー95一 Q)受注 D BEDS MIF TE ZUM,締め契機まで著積する。 ©1回の受注に複数の商品を指定できる。 ®受注ごとに,受注番号を付与し,受注年月日時刻を記録する。 ④受注明細では,受注明細番号を付与し,商品とその数を指定する。 ©在庫引当ての成知は,受注明細に記録する。 (2)出荷指示 OD炊め契機で在庫引当てに成功した受注を集約した情報である。出荷指示に 基づいて,一連で出庫。梱包,出荷を実施する。 Q出荷指は,納入先別に行う。梱包を分ける単位を示す出荷指示柚包明細 梱包を分けた中に入れる商品を示す出荷指示梱包内商品明組の3階層の形式を8 とる。 出荷指示ごとに,出荷指示番号を付与し適用した締め契機。出荷指示対 象の納入先を記録する。対象の受注に出荷指示番号を記録する。 ©出荷指示梱包明細ごとに,出荷指示梱包明細番号を付与し,梱包方法によ って,店千別梱包の場合は梱包対象チェーン店舗を,商品カテゴリ別梱包の 場合は商品カテゴリを設定する。 ©出荷指示梱包内商品明細ごとに,梱包すべき商品について製造ロット別に 出区指示数を設定する。 (3)出庫指示 O物流拠点ごとに,同じ締め契機の,全ての納入先の出荷指示を集約し物 流拠点の倉庫から出荷対象の商品を出すための情報である。 ©出庫指示には,出庫指示番号を付与し,対象の物流拠点と適用した締め契 機を記録する。対象の出荷指示に出庫指示番号を記録する。 @由庫指示明細には,出庫指示明細番号を付与し,製造ロット別の商品と出 庫指示数を設定する。 ④出庫実績 O出庫指示明細で指示された商品の出庫指示数を,北つのケースと幾つのピ ースで出庫したかの実績である。 OQ出庫実績には出誠実績番号を付与し,荷交区分で分類して,ケースを出庫 ーー した出庫実績には出庫ケース数を,ピースを出庫した出庫実績には出庫ピー ス数を記録する。 (5)梱包実績 ①出庫された商品を,出荷指示梱包明細に基づいて配送できるように段ボー ル箱に詰めた実績であり,段ボール箱ごとに梱包実績番号を付与する。 ②梱包実績にはケース梱包実績とピース梱包実績がある。いずれの実績かは, 段ボール箱区分で分類する。 ③ケース梱包実績は,どのケース出庫実績によるものかを関連付ける。 ④ピース梱包実績は,一つ又は複数の種類の商品の詰め合わせであり,どの ピース出庫実績から幾つの商品を構成したかのピース梱包内訳を記録する。 (6)出荷実績 ①出荷指示の単位に梱包を納入先別に配送した実績である。 ②出荷実績には,車両番号と出荷年月日時刻を記録する。 ⑧出荷実績に対応する梱包実績に,どの出荷実績で出荷されたかを記録する。 [概念データモデル及び関係スキーマの設計〕 1.概念データモデル及び関係スキーマの設計方針 (1)関係スキーマは第3正規形にし,多対多のリレーションシップは用いない。 2)リレーションシップが1対1の場合,意味的に後からインスタンスが発生する 側に外部キー属性を配置する。 (3)概念データモデルでは,リレーションシップについて,対応関係にゼロを含 むか否かを表す“O”Mik“O”は記述しない。 (4)実体の部分集合が認識できる場合,その部分集合の関係に固有の属性がある ときは部分集合をサブタイプとして切り出す。 (5)サブタイプが存在する場合,他のエンティティタイプとのリレーションシッ プは,スーパタイプ又はいずれかのサブタイプの適切な方との間に設定する。 (6)概念データモデル及び関係スキーマは,マスタ及び在庫の領域と,トランザ クションの領域を分けて作成し,マスタとトランザクションの間のリレーショ ンシップは記述しない。 2.設計した概念データモデル及び関係スキーマ マスタ及び在庫の領域の概念データモデルを図2に,トランザクションの領域の ー27一 概念データモデルを図3に,マスタ及び在庫の領域の関係スキーマを図4に,トラ ンザクションの領域の関係スキーマを図5に示す。 DCカテゴリ明細 oe 払出在庫 図2マスタ及び在庫の領域の概念データモデル(未完成) een 出荷指示 店舗別梱包出荷指示||出荷指示 a ween 梱包明細||別梱包明細: a = 実績実績 実績 図3トランザクションの領域の概念データモデル(未完成) 物流拠点(拠点コード,拠点名) 配送地域(配送地域コード,配送地域名拠点コード) 郵便番号(郵便番号,配送地域コード) 配送車両(車両番号,拠点コード) チェーン法人(チェーン法人コード,チェーン法人名業界シェア,ロット逆転禁止フラグ) チェーンDC(チェーン法人コード,チェーンDCコード,梱包方法区分。チェーンDC名 配送地域コード) チェーン店舗(チェーン法人コード,チェーン店舗コード,チェーン.DC.コード,チェーン店舗名) 商品分類(商品分類コード,商品分類名) 商品(商品コード,商品名販売単価,商品分類コード,流通方法区分。ケース内ピース和数) PB商品([。|]) 製造ロット(商品コード,製造ロット番号,製造年月日使用期限年月) NB商品([5|) 商品カテゴリ(|<|) 商品カテゴリ明細(チェーン法人コード,商品カテゴリコード,商品コード) 納入商品最終ロット(チェーン法人コード,チェーンDCコード,商品コード,製造ロット番号) WAED(MRED,WEA) ia(締め年月日,回目,締め時刻) (L』|) 引当在庫(|se|) 払出在庫(|f) 注記図中の|ア|には,図2の[|ア|と同じ字句が入る。 図4マスタ及び在庫の領域の関係スキーマ(未完成) 2 LES,受注年月日時刻。チェーン法人コード,チェニシDcコード|』]) 店舗別梱包指定受注(|nh]) AiR(出待指示番号,チェーン法人コード,チェューコードLi人) 出荷指示梱包明細(出荷指示番号,出荷指示梱包明細番号) 出荷指示店舗別梱包明細(出荷指示番号,出荷指示梱包明細番号,) 出荷指示商品カテゴリ別梱包明細(出荷指示番号,出荷指示梱包明細番号,|」]) |出荷指示梱包内商品明細(出荷指示番号出荷指示梱包明細番号 出庫指示(出庫指示番号,|n]) 出庫指示明細(出庫指示番号,出庫指示明細番号,商品コード,|oc]) 出誠実績(出誠実績番号,|p|) ケース出庫実績(|q|) ピース出庫実績(|:]) 梱包実績(梱包実績番号.|s|) ケース梱包実績(梱包実績番号 ビース梱包実績(梱包実績番号,緩衝材使用量) (Lal) TSE(出荷実績番号 注記図中の|イ|には,図3の[イ|と同じ字句が入る。 図5トランザクションの領域の関係スキーマ(未完成) 〔設計変更の内容〕 設計は,全ての量販店がチェーンDCをもち,チェーンDCから受注し,チェーン DCに納入することを前提にしてきた。しかし,大手の量販店が,地方の量販店と合 併することによって,暫定的又は恒久的にチェーンDCのないチェーン店舗が発生す ることが判明した。このような場合,量販店の本部又は支部から受注し,チェーン 店舗に直接納入する必要がある。そこで,当初の検討と合わせて運用できるように, 顧客の組織について次の設計変更を行うことにした。 1.顧客の組織の設計変更 (1)チェーン本支部 ①新たな受注先である量販店の本部又は支部をチェーン本支部と呼ぶ。 ②受注し得るチェーン本支部について,コード及び名称を顧客から知らされ CBRL,チェーン法人コードとチェーン本支部コードによって識別する。 (2)チェーン組織 ①受注は,チェーンDC又はチェーン本支部から受けることになったので,こ の両者を併せて受注先と呼ぶことにする。 ②納入は,チェーンDC又は直接納入する対象のチェーン店舗(以下,直納対 象チェーン店舗という)に行うので,この両者を併せて納入先と呼ぶことに する。 ③さらに,受注先と納入先を併せてチェーン組織と呼ぶことにする。 ④チェーン組織には,チェーン法人を超えて一意に識別できるチェーン組織 コードを付与し,どのチェーン法人のチェーン組織なのかを設定する。 ©受注先と納入先は,それぞれチェーン組織の一部なので,受注先に該当す るチェーン組織には受注先フラグを,納入先に該当するチェーン組織には納 入先フラグを設定する。 ©受注先は,受注の対象の納入先が全て分かるようにする。また,その受注 先がチェーンDCかチェーン本支部のいずれかを示す受注先区分を設定する。 ⑦配送地域は,納入先に設定し,チェーンDCからは外す。 (3)チェーン店舗 ①設計変更前に対象にしていたチェーン店舗を,店舗別梱包対象チェーン店 舗に呼び替える。 ー30一 ②チェーン店舗は,スコープを広げて,店舗別梱包対象チェーン店舗と直納 対象チェーン店舗を併せたものにする。また,そのチェーン店舗が店舗別梱 包対象チェーン店舗か直納対象チェーン店舗のいずれかを示すチェーン店舗 区分を設定する。 2.梱包のやり方についての設計変更 直納対象チェーン店舗への納入では,梱包方法の指定は受けない。これに伴っ て,梱包方法区分に指定なしの分類を追加する。 3.顧客についての概念データモデル及び関係スキーマの検討内容 設計変更の内容に基づいて,顧客に関する部分を切り出して検討した。設計変 更した顧客の概念データモデルを図6に,設計変更した顧客の関係スキーマを図7 に示す。 —a= チェーン組織 チェーン店舗チェーン店舗 図6設計変更した願容の概念データモデル(RIM) チェーン法人(チェーン法人コード,チェーン法人名,業界シェア,ロット逆転禁止フラグ) チェーン組織([あ|,チェーン法人コード) suse(Co) 納入先(|う|,梱包方法区分) チェーツDC(チェーン法人コード,チェージDCコード,チェーンDCH,) チェーン本支部(チェーン法人コード,チェーン本支部コード,) チェーン店舗(チェーン法人コード,チェーン店舗コード, 店舗別机対象チェーン店舗(|き|,チェーンDCコード) 直納対象チェーン店舗(|<]) 図7設計変更した顧客の関係スキーマ(未完成) 解答に当たっては,巻頭の表記ルールに従うこと。ただし,エンティティタイ プ間の対応関係にゼロを含むか否かの表記は必要ない。 なお,属性名は,それぞれ意味を識別できる適切な名称とすること。また,A 係スキーマに入れる属性名を答える場合,主キーを表す下線,外部キーを表す破 線の下線についても答えること。 ーー一32一