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

共通の文章

データベースの設計,実装に関する次の記述を読んで,設問1ー3に答えよ。 B銀行では,支店の窓口において,預金の入出金,税金・公共料金の収納,振込な どの金融サービス業務(以下,窓口業務という)を行っている。現在,行員が使用 する窓口業務専用の端末(以下,窓口端末という)で記録されたログを,障害調査 監査などに利用している。今後は,蓄積したログを更に活用して,事務手続,アプ リケーションソフトウェア(以下,APという)の改善に役立てるために,ログ分析 システムを構築することにした。 〔システムの現状〕 1.現行システムの構成 (1)支店は60支店あり,店番で識別している。各支店には,複数の窓口端末があ る。窓口端末は,PC,モニタ,スキャナ,プリンタ,現金処理機などを組み合 わせた端末で,支店ごとに一意な機番で識別している。 (2)データセンタが1拠点ある。データセンタには,窓口端末サーバ,ホストコ ンピュータがある。窓口端末サーバでは,全支店の窓口端末の監視,ログ収集, APの配布などを行う運用管理システムが稼働しており,ホストコンピュータで は,業務処理を行う勘定系システムが稼働している。 (3)支店内,データセンタ内の機器はLANで接続されており,LAN間はWANで 接続されている。 (4)窓口端末のPCでは,窓口端末用のAP(以下,端末APという)が稼働して いる。端末APは,業務ロジックをもたず,勘定系システムと連動しながら,画 面の入出力及び接続機器の制御を行っている。 (5)端末APでは,預金入金,預金出金,振替などの取引ごとに取引メニューが用 意されており,取引は,取引番号で識別している。取引メニューに対応する手 続用の画面が複数ある。画面は,全体で一意な画面番号で識別している。 2.ログ収集・利用状況 (1)窓口端末には,ログを自動的に記録する機能が備わっている。窓口端末のPC lt,ログをログファイルに保存し,約15分ごとに窓口端末サーバに転送してい る。 こす= (2)ログには,店番,機番,.タイムスタンプ(以下,TSという),及びログテキ ストを記録する。ログテキストは,取引番号,画面番号,画面ごとに固有な項 目とその値を[項目ID:項目名=値:値の説明]”(値の説明の表示は任意)の形式 で編集した文字列を連結したものである。表1にログの例を示す。 表1ログの例(一部省略) 1234|2501|2019-03-15 11:24:11.120[201:取引番号=3322:預金出金][301:画面番号=0777: 口座出金依頼][155:処理区分=001:取引開始][101:行 員番号=1122334:情報花子][017:科目=2:普通][005:口 座店番=543:〇〇支店][006:口座番号=00578212:]… 1234|2501|2019-03-15 11:24:20.098[201:取引番号=3322:預金出金][301:画面番号=0001: 依頼書等受領確認][155:処理区分=055:残高確認依 頼][315:受領結果=1:OK]… 1234|2501|2019-03-15 11:24:27.211[201:取引番号=3322:預金出金][301:画面番号=0028: 通由受領確認][018:出金依頼金額=10000:][155:処理 区分=055:残高確認依頼]… (3)窓口端末サーバでは,RDBMSが稼働していて,窓口端末のPCから収集した ログファイルのデータを,約1時間ごとに“ログ収集"”テーブルに格納している。 (4)利用者である行員(以下,利用者という)は,GR,機番,及びTSの範囲を 指定してテーブルの行を選択し,TS及びログテキストを射影する問合せを行っ て,障害調査,監査の際に利用している。 [新たなログ分析システムの構築〕 1.ログ分析システムの主な機能 1)蓄積した60か月分のログを対象に,障害調査,BA,事務手続改善,AP改 善に用いるデータを抽出するログ分析処理の機能を提供する。表2に,ログ分析 処理の例を示す。 (2)ログ分析システムの利用時間帯は,FAD 8:00~22:00である。毎月の最終営 業日の22:00から,翌営業日の8:00までの間に,過去データの削除,再編成な どの処理(以下,月末処理という)を行う。 —ゲーー 表2ログ分析処理の例 利用者が指定した年月に一致する1か月分のログを対象に,店番,店名,機番,TS,行 BSS,行員氏名,取引番号,取引種別コードを出力する。利用者の権限レベル(1, 処理1‘9’,"3")によって参照可能なログが異なり,"1'′の場合は,当該利用者がその時点 で所属する支店かつ当該利用者のログだけを,"2′の場合は,当該利用者がその時点で 所属する支店のログだけを,"3′の場合は,全ログを対象にする。 全ログを対象に,店番,機番ごとに,TS順に連続する二つのログの画面番号の組を前 処理2画面番号,後画面番号としてログ間の経過時間(ミリ秒単位の整数)の平均値を求め 前画面番号,後画面番号,平均経過時間を,平均経過時間の降順に出力する。同じ画面 番号が連続する場合は,後画面番号と前画面番号は同じになる。 利用者が指定した店番,AS,RO TSの範囲(開始年月て終了年月)に該当するログ 処理3|のTS,取引番号,取引名を,操作の分岐に従ってツリー形式で表示する。また,利用 者がTSを一つ選択すると,HE,機番,TSに対応するログテキストを表示する。 利用者が指定した一つの店番について,年月が前年4月から前年9月までの6か月分の 処理4|ログを対象に,年月,行員番号,取引種別コードごとに伝票金額を集計して,店番,年 月,行員番号,取引種別コード,取引種別名,合計伝票金額の一覧を出力する。 利用者が指定した前月以前の年月に一致する1か月分のログを対象に,店番,端末種別 処理5|コードごとに明細件数を集計して,店番,店名,端末種別コード,端末種別名,合計明 細件数を出力する。 利用者が指定した一つの画面番号について,年月が前々年4月から前年3月までの12 処理6|か月分のログを対象に,店名,取引種別名ごとに,明細件数を集計して,画面番号,タ イトル,店名,取引種別名,合計明細件数を出力する。 2.ログ分析システムの構成 (1)窓口端末サーバで稼働中のRDBMSをログ分析システムに利用する。 (2)分析用データの作成・参照・削除に用いるAP(以下,分析APという)を新 たに開発し,窓口端末及び窓口端末サーバ上で稼働させる。 3.ログ分析システムのテーブル 次の方針でデータベースにデータを格納することにし,図1のログ分析システム のテーブル構造,表3の主なテーブルのデータ量見積りを作成した。 (1)支店から収集したログは,現状どおり“ログ収集"”テーブルに格納する。格 納処理は,ログファイルごとに,並行して実行する。新たに発生したログを追 加するだけで,更新は行わない。 (2)“ログ基本"テーブルには,“ログ収集"テーブルの各行に対応する行を格納 し,一意な文字列であるログIDで識別する。検索,集計のキーとなる項目を列 として定義し,ログテキストから取り出した値を設定する。年月には,TSの年 ee 8 cas 月を設定する。 (3)ログテキスト内に記録されている複数の操作を,操作ごとに分割して“ログ ARM”テーブルに格納する。 (4)取引の開始から終了までの手続は,複数の操作に分岐していくので,ログは 木構造をもつ。分析APによってログを解析し,取引開始ログをルートノードに, その他のログをノードとする木構造の関連を“ログ関連"”テーブルに格納する。 (5)マスタ情報(支店,端末種別,窓口端末,取引種別,取引,画面,行員,行 員所属)を各テーブルに格納する。マスタ情報は,データ量が少なく,更新は 月1回なので,月末処理で変更を反映する。 |(6)“ログ収集",“ログ基本",“ログ明細”",“OT BEE”テーブルは,月末処理 I において,TSが翌月の60か月前の月以前の行を削除して,59か月分のログが 保存された状態にする。月が変わった後のログを合わせて60か月分を保有する。 支店(店番,店名,所在地) 端末種別(端末種別コード,端末種別名) 宿口端末(店番,機番,端末種別コード,設置場所) 取引種別(取引種別コード,取引種別名) 取引(取引番号,取引種別コード,取引名) 画面(画面番号,タイトル) 行員(行員番号,行員氏名,…) 行員所属(行員番号,適用開始日,所属店番,権限レベル,適用終了日) ログ収集(店番,ME,TS,ログテキスト) ログ基本(ログID,店番,機番,TS,行員番号,取引番号,画面番号,年月,処理区分, ORGS,科目,口座番号,明細件数) ログ明細(ログID,明細番号,店番,BS,TS,年月,開始時刻,終了時刻,伝票金額, ホスト送信データ,ホスト受信データ,終了状態,…) ログ関連()) 注記各テーブルの主キーには,索引が定義されている。 網掛け部分は,表示していない。 図1ログ分析システムのテーブル構造(一部省略) 表3主なテーブルのデータ量見積り ページ長(バイト)|平均行長(バイト) |ログ収集。|540M| "4000|900|135M 注記表中の単位Mは100万を表す。 ーー9ーー (RDBMSの主な仕様〕 ログ分析システムに利用するRDBMSの主な仕様は次のとおりである。 i,A=? RDBMSとストレージ間の入出力単位をページという。同じページに異なるテー ブルの行が格納されることはない。 2.オプティマイザの仕様 (1)LIKE述語の検索パターンがABC%′"のように前方一致の場合は索引探索を 選択し,‘%ABC%’,‘%ABC’のように部分一致,後方一致の場合は表探索を選 択する。 (2)WHERE名の述語が関数を含む場合,表探索を選択する。 3.再帰的な問合せの構文のサポート WITH句にRECURSIVEを指定した再帰的な問合せの構文をサポートする。再帰 的な問合せの構文を用いると,例えば,階層構造の組織について,ある組織を起 点として上位又は下位の組織を,階層に沿って連続的に検索することができる。 4.ウィンドウ関数のサポート ウィンドウ関数をサポートする。例えば,選択する行ごとに,その直前の行の 列値を参照したい場合には,LAG関数が使用できる。 5.テーブルの物理分割 (1)テーブルごとに一つつ又は複数の列を区分キーとし,区分キーの値に基づいて 物理的な格納領域を分ける。これを物理分割という。 (2)区分方法には,ハッシュとレンジの二つがある。ハッシュは,区分キー値を 基にRDBMS内部で生成するハッシュ値によって,一定数の区分に行を分配する 方法である。レンジは,区分キー値によって決められる区分に行を分配する方 法で,分配する条件を,値の範囲又は値のリストで指定する。 (3)物理分割されたテーブルには,区分キーの値に基づいて分割された索引(以 下,ローカル索引という)を定義できる。ローカル索引のキー列には,区分キ ーを構成する列(以下,区分キー列という)が全て含まれていなければならな い。 (4)テーブルを検索するSQL文のWHERE旬の述語に区分キー列を指定すると, 区分キー列で特定した区分だけを探索する。また,WHERE名の述語に,ローカ ー10一 ル索引の先頭列を指定すると,ローカル索引によって区分内を探索することが できる。 (65)問合せの実行時に,一つのテーブルの複数の区分を並行して同時に探索する。 同一サーバ上では,問合せごとの同時並行探索数の上限は20である。 (6)指定した区分を削除するコマンドがある。区分内の格納行数が多い場合,コ マンドによる区分の削除は,DELETE文よりも高速である。 6.クラスタ構成のサポート (1)シェアードナッシング方式のクラスタ構成をサポートする。クラスタは複数 のノードで構成され,各ノードには,当該ノードだけがアクセス可能なディス ク装置をもつ。 (2)各ノードへのデータの配置方法には,次に示す分散と複製があり,テーブル ごとにいずれかを指定する。 ・分散による配置方法は,一つ又は複数の列を分散キーとして指定し,分散キ ーの値に基づいてRDBMS内部で生成するハッシュ値によって各ノードにデー タを分散する。分散キーに指定する列は,主キーを構成する全て又は一部の列 である必要がある。 ・複製による配置方法は,全ノードにテーブルの複製を保持する。 (3)データベースへの要求は.いずれか一つのノードで受け付ける。要求を受け 付けたノードは,要求を解析し,自ノードに配置されているデータへの処理は 自ノードで処理を行う。自ノードに配置されていないデータへの処理は,当該 データが配置されている他ノードに処理を依頼し,結果を受け取る。特に,テ ーブル間の結合では,他ノードに処理を依頼するので,自ノード内で処理する 場合と比べて,ノード間通信のオーバーヘッドが発生する。 [問合せの検討] 1.問合せの傾向分析・索引設計 表2の処理1~6の参照テーブルを表4にまとめて,問合せの傾向を分析した。 また,図1中のマスタ情報を格納するテーブルについて,処理1て6で結合に用い られる主キー以外の列に索引を定義することにし,その対象テーブル名・列名を 表5にまとめた。 ー]ロロー 表4処理1一6の参照テーブル(未完成) 支|端1窓|取|取|画|行|行「(ロ[ロロ(ロロ 店|末|口|引|引|面|員|具|グ|グ|グ|グ 種|vig|種所|収|基|BA|OBS 処理名別|末|別属|集|本|細|連 me.fot||fof olol[Jo]|| 処召2。|||||||||lol|| |処還3。||||lo|||lolo|lol 処理4。|||lolol|||lolol 処旭5。|||||||||lol|| eT|||||||||]lol|| 注記〇:テーブルが処理で参照すれることを表す。 表5主キー以外の索引定義対象テーブル名・列名(未完成) 索引を定義する列名 2.処理1の問合せ検討 表2の処理1の問合せに用いる図2のSQL文を作成した。 WITH TEMP AS(SELECT CASE WHEN権限レベル[|_。]THEN所属店番ELSE NULL END AS検索店番, CASE WHEN権限レベル|5ゎTHEN行員番号ELSE NULL END AS検索行員番号 FROM行員所属 WHERE行員番号=:hv1 AND CURRENT_DATE>=適用開始日 AND(適用終了日IS NULL OR CURRENT_DATE<適用終了日)) SELECT A.店番,B.店名,A.機番,A.TS,A.行員番号,C.行員氏名,A.取引番号,D.取引種別コード FROMログ基本A,支店B,TAC,取引D,TEMPE WHERE A.年月=:hv2 AND A.店番=B.店番 AN A.行員番号=C.行具番号 AND A.取引番号=D.取引番号 AND(E.栓索店番OR E.検索店番=ん.店番) AND(E.検索行員番号OR E.検索行員番号=A行員番号) 注記hv1は利用者の行員番号のホスト変数を,hv2は利用者が指定した年月のホスト変数を表す。 図2処理1の問合せに用いるSQL文(未完成) 3.処理2の問合せ検討 表2の処理2の問合せに用いるSQL文の例を,図3て5に作成した。ここで, GET LAPSE(TS1,TS2)は,TS1からTS2までの経過時間を,ミリ秒単位の整数で 返却するユーザ定義関数である。また,図5中の|ア|は,図3.4のsor 文と同じ結果を返すように,“ログ基本"”テーブルに追加した列の列名である。 なお,図3て5の結果行には,分析対象でない前画面番号と後画面番号の組が含 まれるが,これらは,分析AP上で別途除外されるものとする。 SELECT B.画面番号AS前画面番号,A.画面番号AS後画面番号, AVG(GET_LAPSE(B.TS,A.TS))AS平均経過時間 FROMログ基本A LEFT JOINログ基本B ON ALES=B.店番AND A.機番=B.機番 AND B.TS=(SELECT MAX(Z.TS)FROMログ基本7 WHERE A.店番=Z.店番AND A.機番=7.機番AND Z.TS<A.TS) WHERE B.画面番号IS NOT NULL GROUP BY B.画面番号,A.画面番号 図3処理2の問合せに用いるSQL文の例1(未完成) SELECT前画面番号,後画面番号,AVG(経過時間)AS平均経過時間FROM (SELECT LAG(画面番号)OVER(PARTITION BY店番,機番ORDER BY TS)AS前画面番号, 画面番号AS後画面番号, GET_LAPSE(LAG(TS)OVER(PARTITION BY店番,機番ORDER BY TS),TS)AS経過時間 FROMログ基本)TEMP WHERE前画面番号IS NOT NULL GROUP BY後画面番号,前画面番号 図4処理2の問合せに用いるSQL文の例2(未完成) SELECT B.画面番号AS前画面番号,A.画面番号AS後画面番号, AVG(GET_LAPSE(B.TS,A.TS))AS平均経過時間 FROMログ基本A LEFT JOINログ基本B ON A.店番=B.店番AND ARE=B.機番 wo(7-0=8[7] WHERE 8B.画面番号IS NOT NULL GROUP BY A.画面番号,B.画面番号 図5処理2の問合せに用いるSQL文の例3(KER) “ログ関連”テーブルの検討 1.テーブル構造の案 木構造をもつデータを取り扱う場合,テーブル構造によって,行の追加・削除 の効率,問合せに用いるSQLの構文,問合せの性能,データの制限が異なる。そ こで,表6の“ログ関連"”テーブルのテーブル構造案13を検討することにした。 検討に当たって,木構造をもつログの例を用いて案を比較することにし,各案に おけるテーブルの行を表7-9に,案3の左端番号・右端番号付与の例を図6にま とめた。 表6“ログ関連”テーブルのテーブル構造案 各ノードの上位ノードのログIDを,親ログIDに設定 案1|ログ関連(ログID,親ログID)|する。ルートノードの親ログIDはNULLにする。親ロ グIDには,索引を定義する。 ルートノードから各ノードに至る全ノードのログD 案2|ログ関連(ログID,経路)を,接続文字("/")で連結した文字列値を経路に設定 する。経路には,索引を定義する。 左端番号及び右端番号は,両方を合わせて連続する一 意な番号であり,あるノードの左端番号と右端番号 ーー、lk,その下位の全ノードに対して,“上位ノードの左端 eg[OZR EAD Ee<KUL)—KORRES"PO“下位ノードの右 alain端番号く上位ノードの右端番号"となるように設定す る。左端番号,右端番号にはそれぞれ索引を定義す る。 表7案1の行表8案2の行RI案3の行 EES|右端 Il104。|5|6 Il100。|7?7|Bsl fig。|9|10| ュー(1)(12)ae_-3)(18)K—-~、 J ia”ae\ ② @Y-$@ apk-~__a4)a5)(16)__(27) ーージーーNeューeo の\ (5)(6)||(7)(8)}|Q)(10) 凡例[|ノードを表す。上部中央の数字はログID,左下の括弧内の,--.左端番号・右端 数字は左端番号,右下の括弧内の数字は右端番号~~番号付与の順序 図6案3の左端番号・右端番号付与の例 2.テーブル構造案の検討 表6の案ごとに,次の問合せ[A],[B]に用いるSQL文の案を,表10にまとめた。 ・問合せ[A]ログID=‘101’及びその下位の全てのログTIDを抽出する。 ・問合せ[B]ログID=‘105’及びその上位の全てのログIDを抽出する。 表19問合せに用いるSQL文の案(未完成) 問合せ[A]問合せ[B] WITH RECURSIVE TEMP(ログID)AS WITH RECURSIVE TEMP(ログID,親ログID)AS (SELECTログID(SELECTログID,親ログID FROMログ関連FROMログ関連 WHEREログID=’101’WHERE[|s| 案1 UNION ALL UNION ALL SELECT A.ログID SELECT A.ログID,A.親ログID FROMログ関連A,TEMP B FROMログ関連A,TEMP B WHERE A.親ログID=B.ログ1D)WHERE A.ログID=[gi)) SELECTログID FROM TEMP SELECTログID FROM TEMP SELECTログID FROMログ関連SELECT B.ログID Tv WHERE経路LIKE’101%FROMログ関連A ORS B WHERE A.ログID=105’ AND POSITION(B.経路IN A.経路)=1 SELECT B.ログID SELECT B.ログID FROMログ関連4ログ関連B FROMログ関連Aログ関連B 案3|WHERE A.ID='101'WHERE A.ID=’105° AND A.左端番号<=B.左端番号AND AND A.右端番号>=B.右端番号AND 注記POSITION(S1 IN S2)は文字列S2内で,文字列S1が最初に出現する文字位置を,S2の最初の 文字位置を1とする整数で返す関数。S1が出現しない場合は0を返す。 3.テーブル構造案の評価 行の追加・削除の効率の観点では,例えば,ログID=199の新たなログを ‘101’の下位,‘103’の上位に追加し,"103′の階層の深さが一つ下がる場合, 199'の行追加以外の更新行敷は,案1では1行,案2では|ia,KB では| ]j|行である。案|k|の負荷が最も高いが,本業務では, |」|ので,変更時の負荷は間題にならない。 問合せの難易度の観点では,処理3の問合せにSQL文を用いる場合,案1では, |mn|の構文を用いるが,案2,3では,選択,射影|nm|の関係演 算を行う構文を用いればよい。案2と案3の問合せの性能を比較すると,RDBMS のオプティマイザの仕様から,表10の問合せ[Alではほぼ同等であるが,問合せ [BI CISL 2が劣る。 これらの評価から,案3による実装が最適と判断されるが,左端番号,右端番号 の列を整数型で定義する場合,整数型の上限を超えないように留意する必要があ る。 〔テーブルの物理分割・クラスタ構成の検討〕 ログデータを格納するテーブルは,格納行数が多いので,データの追加,参照, 削除の性能が懸念される。そこで,テーブルの物理分割を行うことにした。 1.“ログ収集”テーブルの物理分割 表11の“ログ収集"”テーブルの物理分割案AーCを作成し,性能評価を行った。 各案の性能を相対的に評価し,性能が低い案から順に0,1,2の点数を付ける。評 価が同じ案は,同じ点数とする。 表11“ログ収集"テーブルの物理分割案・性能評価 ニービニー a Seaactchetton EE[a Lo[e gceewgee9[LL 8|8 [SewgckewgsesW[Lo|8| TSの年月ごとに60区分に分割 店番ごとに60区分に分割 ハッシュ値ごとに60区分に分割 注記{)内は,索引の列を定義順に記述したリストである。 ー16一 2.“ログ基本"テーブルの物理分割 (1)物理分割案とその評価 “ログ収集"”テーブルと同様に,表12の“ログ基本"テーブルの物理分割案 D~Fを作成し,性能評価を行った。追加,削除の性能評価は“ログ収集”テー ブルと同様であったが,参照の性能評価は処理によって異なるので,更に処理 ごとに性能評価を行うことにした。 表12“OPER”テーブルの物理分割案・性能評価 ||So eee 区分方法(区分キー)性能評価 案Dハッシュ(ログDD){07 ID}i ハッシュ値ごとに60区分に分割|{店番,BE,FA,U7 ID} ueレンジ(店番)(a,OZ ID}‘ 店番ごとに60区分に分割 家Fレンジ(年月)(年月,ログID} 年月ごとに60区分に分割 注記(}Alt,索引の列を定義順に記述したリストである。 網掛け部分は表示していない。 (2)探索対象ページ数試算による性能評価 表2の処理4て6における物理分割案DFについて,次の①て③④を前提条件 CUT,探索対象の最大区分数(以下,探索区分数という),テーブルからの読 込みページ数(以下,探索ページ数という)を試算し,表13を作成した。 ①“ログ基本"”"テーブルへのアクセスだけを対象とする。どの処理でも,検 索条件に合致する行は,30,000行あるものとする。 ②月末営業日の処理を想定し,60か月分のログが全て鞭積されていて,ログ は各支店,各年月に均一に分布しているものとする。 ③どの区分方法でも,ページ総数は同じで,どのページにも最大行数のデー タが格納され,ページは各区分に均等に配置されているものとする。また, テーブルの行が複数ページに分割されて格納されることはないものとする。 ④バッファヒット率は,索引のページでは100%,テーブルのページでは0% &oS. 表13表2の処理4て6における探索区分数・探索ページ数試算(未完成) 区分数。 。。。|eo|eoO|60| 前提 1区分当たりのページ数 処理4ーー一 探索区分数|60|1|6l man|uy|近区分数。。。。。|o]|el+ Be mame|しょ|wl sw nme REAR Pf] mare|eswl wlにuH 注記表中の単位Mは100万を表す。 3.クラスタ構成の検討 テーブルの物理分割だけでは,将来的なデータ量の増加に対応できないので, RDBMSがサポートしているクラスタ構成の検討を行った。クラスタ構成への変更 に当たり,各テーブルのデータの配置方法に分散を指定し,主キー列を分散キー として設定することにした。 クラスタ構成への変更後,表2の処理2,4の性能を試算したところ,処理2は 同時並行探索数の上限がなくなることで性能が改善されたが,処理4は性能が低下 することが判明した。そこで,処理4の性能低下への対策として,“取引種別”テ ーブル,“取引”テーブル及び“ログ明細"”テーブルのデータの配置方法を見直す it呈もだ。

共通の文章

製パン業務に関する次の記述を読んで,設問1,2に答えよ。 Aホテルは,宿泊・料飲・宴会のサービスを提供するフルサービス型のホテルであ る。Aホテルでは,製パン業務の業務改革に向けて,現状と目指す姿の業務分析を行 い,概念データモデル及び関係スキーマを設計した。 現状の業務分析の結果] 1.自社組織,食材業者,品目 (1)自社組織 ①ホテルには本館と新館があり,この二つをロケーションという。 @本館には,メインダイニングルームというレストラン(LAF,MDという) がある。 @新館には,カジュアルダイニングルームというレストラン(LF,CDとい う),カフェ(以下,CFという)及び宴会場(以下,BQという)がある。 ④営業の時間帯には,朝食,BR,夕食があり,MDは昼食時間帯及び夕食時 間帯に,CDは朝食時間帯,昼食時間帯,夕食時間帯に営業する。また,CF 及びBQは朝食夕食の連続した時間帯に営業する。 ©機能単位の組織を部門と呼び,部門コードで識別する。部門はいずれかの ロケーションに属する。 ©部門には,製造部門,貯蔵庫,要求元部門の種類があり,部門種別で分類 する。本館と新館には,それぞれ製造部門,貯蔵庫,要求元部門が存在する。 ⑦製造部門は,製パンの3工程に対応した次の3部門の総称である。これら3 部門は,工程区分で分類する。 REPEBPS(以下,BHP E Mixという)NEM,S17,パターなどを提ね て生地材料を製造するMix工程を担う。 ・成型部門:生地材料を切り分けて成型材料を製造する成型工程を担う。 ・焼成部門:成型材料を天板に並べて焼いて製品を製造する焼成工程を担う。 要求元部門は,パンの製造を要求する部門であり,MD,CD,CF及びBQ である。要求元部門には,要求先の焼成部門を設定する。 ©本館と新館の製造部門は,同じロケーション内の要求元部門にパンの供給 ce BE mas を行う。 ⑩焼成部門全体の能力よりも,成型部門全体の能力が小さいので,成型の能 力が不足する場合,後述する外注成型材料で不足分を補う。ただし,要求元 部門の中には内製の成型材料だけを用いなければならない部門があり,その 可否を示す内製限定フラグを設定する。 ⑪製造部門の3部門には,次の項目を設定する。 ・Mix部門:同時並行で生地材料を製造できるMixライン数 ・成型部門:同時並行で成型できる成型ライン数 ・焼成部門:同時並行で焼成できる窯の保有有段数 ⑫貯蔵庫は,後述する貯蔵品目の在庫をもち,貯蔵品目の受払いを担う部門 である。貯蔵庫からの払出しは,同じロケーション内の部門に限って行う。 (2)食材業者 食材業者は,原材料及び外注成型材料の仕入先業者である。 (3)品目 ①原材料,生地材料,成型材料,製品を品目と呼ぶ。 ②品目は,品目コードで識別し,品目名,計量単位及び次を設定する。 ・原材料,生地材料,成型材料及び製品のいずれかを表す品目分類 ・調達又は内製のいずれかを表す調達内製区分 ・貯蔵対象かどうかを表す貯蔵区分 @成型材料には,成型部門が成型する内製成型材料と,食材業者から調達す る外注成型材料がある。内製成型材料には,対応する代替外注成型材料を一 つ決めて設定する。外注成型材料が代替できる内製成型材料は,一つだけで ある。 ④品目のうちの貯蔵品目には,原材料,生地材料及び外注成型材料が含まれ る。貯蔵品目には,出庫のロットサイズを設定する。 ©品目のうちの調達品目には,原材料及び外注成型材料が含まれる。調達品 目には,調達先食材業者,調達ロットサイズ,調達単価を設定する。 ©品目のうちの内製品目には,生地材料,内製成型材料及び製品が含まれる。 内製品目には,製造仕様書番号を設定する。 ⑦原材料には,粉類,ミルク類などの分類を表す原材料分類を設定する。 iG一 ⑧生地材料には,1回の製造単位としての生地材料ロットサイズを設定する。 ⑨外注成型材料には,食材業者に成型材料の製造を依頼するための指定製法 番号を設定する。 ⑩製品には,1回の製造単位としての焼成ロットサイズ,及び焼成に用いる内 製成型材料を設定する。一つの内製成型材料からは,一つの製品だけ製造す る。 QD内製成型材料を作るロットサイズは,焼成ロットサイズに等しい。 @生地材料には,そのレシピとして,1回の製造に使用する,幾つかの原材料 とその使用量を設定する。 ⑬内製成型材料には,そのレシピとして,1回の製造に使用する,幾つかの品 目(生地材料又は原材料)とその使用量を設定する。例えば,レーズンパン の成型材料には,イギリス食パン用の生地材料の使用量と原材料のレーズン の使用量を決めている。 2.在庫補充のやり方 ①)在庫確認において,在庫数量が基準在庫数量を下回った貯蔵品目について, その品目ごとに決めているロットサイズの補充要求をかける。 (2)補充要求をかけたら補充要求済みフラグをセットし,入庫したら補充要求済 みフラグをリセットする。補充要求済あみフラグを見ることで,補充要求の重複 を防いでいる。 3.物流パターン,物流の指示・実績の情報 1)物流パターン 部門及び食材業者間の物流パターンを表1に示す。表1では,物流の始点と終 点の1組を単位に説明している。例えば,行番号5は次の物流パターンを表して いる。 ・この物流は,本館貯蔵庫から本館Mix部門に原材料の払出しを行うものであ る。 ・この物流は,払出依頼書(本館Mix部門が発行)に基づいて行う。 ・この物流の実績として,払出伝票(本館貯蔵庫が発行)を記録する。 ー23— 表1部門及び食材業者間の物流パターン 物流の始点・終点物流の対象物 ab|lee|e]All R|A RR|) welt|e||S|S(Sel Blalalol.|z/P/a/2 we||KR LK LK LK Le Le De|SLO}{Op R]H|e hlIFIT||||||||||||O||||拉計% HzlrlT||||||||||||||O||拉充要求書 lslF|IT||||||||||fol|||捕充要求書 4lm|[rt||||||||||||O|disse eit ls|Im||IT|||||||||fot|[ists le|lm|||IT||||||||IOIO|||私出條頼 7?|[et||ITl|||||||||O|[ait払出伝票 ls|IT|IF||||||||||IO|||拉充要求書 rot|Tet||IT||||||IO|||tease I pol|IF|||||IT||||||OIO|||反則依頼 mn||IF|||||IT|||||||O||色出依頼 pat|IT|||lr||||||||O|[aoa Mix SCR Is||||IFIT|||||||]||]O|[peateacereiiar[成型実績 |||||||IFIT||||||]O||成桂料製造依頼書成型実績 hs|||||jI還|||||||||||O|-|焼成実績上 ie|||||Im||Fe)Ee|供給人 |||||||||斬|||||||O|-パパ|焼成実績 ls||||||||IF|IT||||||O|委伝票。|供給人 he||||||||IF||IT|||||O|要伝票|供給伝味| ll|||||||Im||IT|||fol ie| 注記1物流の始点・終点欄は,'F'が始点,‘CTが終点,‘FTが始点かつ終点であることを表す。 注記2物流の対象物欄は,‘O’が物流の対象物を表す。 注記3指示情報欄は,物流を起こす指示となる情報を表す。ただし,"一′の場合は該当する情報はない。 注記4実績情報欄は,物流の結果を記録する情報を表す。 (2)表1中の指示情報及び実績情報 ①BORER 各時間帯終了後に,3回先の時間帯を対象に,BERTI D 5 BRO MER 部門に,パンの製造を要求する伝票で,要求番号で識別する。要求する製品 とその数量を明細に記載する。 ②供給伝可 要求に対してどのように供給したかを記録する伝票で,供給番号で識別す る。要求明細に対応させて供給明細を起こし,実際の供給数量を記録する。 製品は焼成ロット単位に製造するので,実際の供給数量は要求数量と異なる ことがある。供給明細に対して,どの焼成実績から幾つ引き当てたかを記録 する。 ③焼成実績票 焼成ロットごとの焼成実績を記録する伝票で,製造番号で識別する。 ④成型材料製造依頼書 焼成に必要な成型材料の製造依頼を行う伝票で,。1ロットごとに発番する成 型材料製造依頼番号で識別する。依頼は成型部門の成型能力を超えることが あるので,成型部門からの製造可否の回答を記録する。 ⑤成型実績票 成型材料製造依頼に対して製造可否が可となった分について,成型ロット 単位の成型実績を記録する伝票で,製造番号で識別する。 ⑥払出依頼書 次の場合に,貯蔵庫に対する払出しを依頼する伝票で,払出番号で識別す る。 ・成型材料製造依頼に対して製造可否が可となった分について,成型に必要 な生地材料及び原材料の払出しを依頼する。 ・成型材料製造依頼に対して製造可否が否となった分について,焼成に必要 な外注成型材料の払出しを依頼する。 ・後述する生地材料補充要求について,その生地材料の製造に必要な原材料 の払出しを依頼する。 ⑦払出伝票 払出依頼の明細について,払出実績数量を記録する。 ⑧補充要求書 補充を要求する伝票で,補充要求番号で識別する。貯蔵庫が在庫確認を行 って貯蔵品目ごとに発行する。 ・貯蔵庫では,品目分類ごとに保管場所が分かれており,原材料及び外注成 型材料は毎夕食時間帯終了後に,生地材料は毎時間帯終了後に,在庫確認 を行う。 ・発行した補充要求書を,調達品目の要求(調達品目補充要求)と生地材料 の要求(生地材料補充要求)に分類する。 ・調達品目補充要求は,食材業者ごとにくくって注文を発行する。調達品目 補充要求は,注文に対する注文明細に位置付ける。 ex$B ・生地材料補充要求は,在庫確認の都度,Mix部門に送る。Mix部門では,生 地材料補充要求を,,1ロット分のMix指示として受け取る。 ⑨Mix実績票 生地材料補充要求に対するMixの実績で,製造番号で識別する。Mixの実績 を記録して入庫実績とする。 O納品書 食材業者からの納品の際に受領する伝票で,納品番号で識別する。注文に 対する納品は,ものによって複数回に分かれることがあるが,明細の単位は 維持される。各貯蔵庫が検品し,納品数量を記録して入庫実績とする。 [目指す姿の業務分析の結果] 1.業務改革策とその背景 現状は,現場の判断で,何をどれだけ製造するかを決めているので,欠品,在 庫偏在という問題が発生している。そこで,次の業務改革策によって問題解決を 図る。 (1)集約可能な部門を集約することで,在庫偏在を減らす。 (2)要求から,製品ごとに必要な焼成ロット数を求め,焼成指示を作成する。そ の焼成指示をどの要求に引き当てるかを決める際,内製限定の部門の要求を先 に引き当てる。それによって,焼成ロットの端数の無駄と,内製限定となる焼 成ロットを最小にする。焼成指示に基づいて焼成を実施する。 2.業務改革策に基づく業務 (1)新館Mix部門と新館成型部門を廃止し,それぞれ本館Mix部門,本館成型部 門に集約する。それ以外の部門は現状のままとする。 (2)製造計画の立て方 ①BRE,要求先焼成部門別,製品別に集計し,必要な焼成ロット数を算出 する。 ②①で算出したロット数分の焼成指示を作成する。焼成指示では,対象の年 月日,時間帯について,使用する焼成部門ごとの窯の段を割り当てて焼成す る製品を決め,1枚の天板の焼成ごとに焼成番号を発番する。 ③②で作成した焼成指示について,要求元部門からの要求を,同じ製品につ ー26一 いて,内製限定の分を先に引き当てる。引き当てた要求が内製限定の場合, 引き当てられた焼成指示を内製限定にする。 ④内製限定にした焼成指示を先に,成型部門に対して成型材料製造依頼をか ける。成型部門は,成型能力がある限り製造可否を"可と回答し,成型能 力が不足する分について製造可否を"否′と回答する。成型部門が製造可否 を否′と回答した分は,焼成部門が,同じロケーション内の貯蔵庫に対し て,代替の外注成型材料の払出依頼をかける。 [概念データモデルと関係スキーマの設計】 1.概念データモデル及び関係スキーマの設計方針 概念データモデル及び関係スキーマの設計は,次の方針に基づいて行う。 ①)関係スキーマは,第3正規形にする。 (2)概念データモデルでは,多対多のリレーションシップは用いない。 (3)リレーションシップが1対1の場合,意味的に後からインスタンスが発生す る側のエンティティタイプに外部キー属性を配置する。 (4)リレーションシップについて,対応関係にゼロを含むか否かを表す“O”又 は“@”は記述しない。 (5)認識可能なサブタイプにおいて,そのサブタイプ固有の属性がある場合,DB ずそのサブタイプの属性とする。 (6)サブタイプが存在する場合,ほかのエンティティタイプとのリレーションシ ップは,スーパタイプ又はサブタイプのいずれか適切な方との間に設定する。 (7)サブタイプに継承するスーパタイプの属性は,サブタイプにおいて外部キー の役割をもつことができる。この場合,継承した属性は,サブタイプの関係ス キーマ上で,前後を“["と“」で挟んで明示する。 2.設計した概念データモデル及び関係スキーマ まず,現状を対象に設計した。現状について確認を行った後,業務改革策に基 づいてどのような修正が必要か検討した。 なお,概念データモデル及び関係スキーマは,マスタ及び在庫領域と,トラン ザクション領域を分けて作成し,マスタとトランザクションの間のリレーション シップは記述していない。 ー27一 (1)現状を対象に設計した概念データモデル及び関係スキーマ マスタ及び在庫領域の概念データモデルを図1に,トランザクション領域の概 念デゲータモデルを図2に,マスタ及び在庫領域の関係スキーマを図3に,トラン ザクション領域の関係スキーマを図4に示す。 ロケーロコ原材料 ! 」要求元7 HH 成型材料 内製 レシピレシピ成型材料 図1現状のマスタ及び人在庫領域の概念データモデル(未完成) ハ ini成型材料払出依頼 成型実績ー 焼成実績 払出依頼 供給引当iil 払出実績||生地材料 図2現状のトランザクション領域の概念データモデル(未完成) ロケーション(ロケーションコード,ロケーション名) 部門(部門コード,部門名,ロケーションコード,部門種別) 製造部門(部門コード,工程区分) 焼成部門(部門コード,保有有数) 成型部門(部問コード,成型ライン数) Mix部門(部門コード,Mixライン数) 貯蔵庫(部門コード,冷凍容量,冷蔵容量,常温容量 要求元部門(部門コード,内製限定フラグ,[。|) 食材業者(食材業者コード,食材業者名) 品目分類(品目分類コード,品目分類名) 原材料分類(原材料分類コード,原材料分類名) 品目(品目コード,品目名,品目分類コード,計量単位|b|) 調達品目(品目コード,調達先食材業者コード,MOY bX,調達単価) 内製品目(品目コード,製造仕様書番号) AA(品目コード,出庫ロットサイズ) 原材料(品目コード,原材料分類コード) 生地材料(品目コード,生地材料ロットサイズ) 成型材料(品目コード) 内製成型材料(品目コード,|<c|) 外注成型材料(品目コード,指定製法番号) 製品(品目コード,焼成ロットサイズ,|q|) 生地材料レシビ(|。|) 成型材料レシビ(|1|) A EM(REP—ik,貯蔵品目コード,在庫数量,基準在庫数量,補充要求済みフラグ) 図3現状のマスタ及び人在庫領域の関係スキーマ(未完成) 要求(要求番号,対象年月日,対象時間帯,要求年月日要求元部門コード) 要求明細(要求番号,要求明細番号,製品品目コード,要求数量) 供給(供給番号,供給元部門コード,供給先部門コード) 供給明細(供給番号,供給明細番号,供給数量,要求番号,要求明細番号) 製造実績(製造番号,対象年月日対象時間帯,実績数量,実績区分) 焼成実績(製造番号,焼成部門コード) 成型実績(製造番号,成型材料品目コード,成型部門コード,成型材料製造依頼番号) Mix実績(製造番号,Mix部門コード,補充要求番号) 焼成実績供給引当(|gz]) 成型材料製造依頼(成型材料製造依頼番号,対象年月日対象時間帯,依頼元部門コード, 払出依頼(払出番号,対象年月日,対象時間帯,払出依頼元製造部門コード, 内製成型材料品目コード,製造可否) 信和肛門コード,|h|) 払出依頼明細(払出番号,払出明細番号,貯蔵品目コード) 払出実績明細(払出番号,払出明細番号,払出実績数量) 注文(注文番号,注文年月日,注文貯蔵庫部門コード,食材業者コード) 補充要求(補充要求番号,要求元貯蔵庫部門コード,補充要求年月日,貯蔵品目コード,補充品目区分) 生地材料補充要求(|ji|) 調達品目補充要求(|」1) 納品(納品番号,納品年月日,納品貯蔵庫部門コード,食材業者コード) 納品明細(納品番号,納品明細番号,納品数量,|k ]) 図4現状のトランザクション領域の関係スキーマREM) (2)業務改革策に基づいて設計した概念データモデル及び関係スキーマ 業務改革策に基づいて,製造計画の結果である焼成指示を追加したトランザ クション領域の概念データモデル及び関係スキーマを設計した。焼成指示に関 わる範囲の概念データモデルを図5に,関係スキーマを図6に示す。 A 図5焼成指示に関わる範囲の概念データモデル(未完成) See 焼成指示(対象年月日,対象時間帯,焼成部門コード,焼成番号,製品品目コード,段記述。内製限定フラグ) 焼成指示要求引当(|」]) 成型材料製造依頼(成型材料製造依頼番号,|m|,製造可否) 製造実績(製造番号,対象年月日,対象時間帯,実績数量,実績区分) 要求明細(要求番号,要求明細番号,製品品目コード,要求数量) mentee(Gis,焼成部門コード,[|n)) 図6焼成指示に関わる範囲の関係スキーマ(未完成) 解答に当たっては,巻頭の表記ルールに従うこと。ただし,エンティティタイプ 間の対応関係にゼロを含むか否かの表記は必要ない。 なお,属性名は,それぞれ意味を識別できる適切な名称とすること。また,関係 スキーマに入れる属性名を答える場合,主キーを表す下線,外部キーを表す破線の 下線についても答えること。