データベーススペシャリスト 2020年 10月 午後II 問題

共通の文章

データベースの設計,実装に関する次の記述を読んで,設問1ー3に答えよ。 太陽光発電設備,空調設備などの住宅設備メーカであるJ tlt,家庭の電気の使用 状況を可視化するサービスの提供に向け,節電支援システムの構築を行っている。 [業務の概要]〕 (1)社は,太陽光発電機器,配電盤,スマートメータ,空調機器などの機器との通 信機能をもつコントローラを提供している。ユーザは,住居にコントローラを設置 LC,節電支援システムに住居及びコントローラの情報を登録する。 2)コントローラは,J社とネットワーク接続し,住居内の機器から収集した買電, 発電,使用電力,機器のログなどの情報をJ社に送信する。J社では,情報をデー タベースに蓄積,加工してユーザに人節電支援情報を提供する。その一つとして,使 用電力量表示画面の例を図1に示す。図1では,使用電力量とその供給元の内訳 (買電,発電など)が示される。 (3)節電支援情報には,日射量を基に算出された標準発電量が含まれる。全国の800 観測点で計測された日射量データを外部から取得する。 (4)自治体,他の企業からの依頼を受けて,蓄積したデータを統計用に集計し,個人 情報を含まないアドホック分析用のデータを抽出して提供する。 年月日:[jl[2919年月。|M tetas 2029年3月25日(水)14:15 |1 aL.1 id 1 ti 59 s jama.lmmMH Ka lal tN ST ARAN A a ERM PUEPE LEE OL U BOOB ABD b AAA ABR AA A Abad ad AdUL| $k年月日は。年,月,‘Aのいずれかを選択し対応する年年月年月日を指定する。 注記2比較対象は,年月日が年の場合は前年,"前々年など,月の場合は“前月',"前年の同じ月 ae,日の場合は'前日,先月の同じ日'などから選択する。 図1使用電力量表示画面の例 gH (節電支援システムのテーブル構造〕 設計済みのテーブル構造を図2に,主な列とその意味・制約を表1に示す。 電力会社(電力会社番号,電力会社名) 料金プラン(電力会社番号,プラン番号,料金プラン名) 時間帯別料金(電力会社番号,プラン番号,時間帯番号、開始時,終了時,料金単価) ユーザ(ユーザ番号,メールアドレス,氏名,生年月日,…) 地域(地域コード,地域名) 住居(住居番号,ユーザ番号,地域コード,住所,世帯区分,電力会社番号,プラン番号 緯度,経度) 配電回路(住居番号,回路番号,回路名,入出力区分,…) 機器(住居番号,機器番号,メーカコード,機器種別,商品番号,設置場所,…) 機器ログ(住居番号,年月日,ログ番号,機器番号,時,分,ログ種別,ログテキスト) 日射量(年月日,時,緯度,経度,日射量,標準発電量) 買電(住居番号,年月日,時,分,買電量) 発電(住居番号,回路番号,年月日,時,分,発電量) 使用電力(住居番号,回路番号,年月日,時,分,使用電力量) M2節電支援システムのテーブル構造(一部省略) 表1主な列とその意味・制約 wees(1~99) ーーーーニーー一“| 開始時,終了時|間帯の終了時は24であり,最後の時間帯以外の終了時は,次の時間帯の開始時と一致す る。 にレーク7= 節電支援システムの処理〕 節電支援情報の提供.,アドホック分析の処理の例を,表2に示す。 表2処理の例 指定された住居番号について,図1の使用電力量表示画面の例に示されたグラフを表示 処理1|するためのデータを抽出する。当月の買電,発電,当月及び比較対象の使用電力を対象 に,年月日ごとにそれぞれ発電量,買電量,使用電力量を集計する。 指定された住居番号,年月日,比較対象住居(同じ地域,同じ世帯区分,同じ地域かつ 処理2|同じ世帯区分のいずれか)について,1日の使用電力量の比較対象住居中の順位を求め て,住居番号,対象住居数,順位を抽出する。 指定された住居番号,年月日,時について,住居番号,KER,平均標準発電量を抽出 処理3|する。平均標準発電量は,住居ごとに住居の半径10 km以内の地点のうち,住居から距 離が近い上位2位以上の地点の標準発電量の平均値である。 指定された年月について,全住居を対象に,地域ごとに,BEBO 1か月間の使用電力 ns量の合計を大きい方から順序付けし,順序に沿って住居数が均等になるように5階級に 分ける。階級に1ン5の階級番号を付け,地域コード,地域名,階級番号,平均使用電 力量を抽出する。 処理5指定された住居番号,年月日,ログ種別について,全機器の機器ログを対象に,住居番 号,年月日,ログ番号,メーカコード,機器種別,ログテキストを抽出する。 (RDBMSの仕様 1.テーブル・索引 (1)RDBMSとストレージ間の入出力単位をページという。 2)同じページに異なるテーブルのデータが格納されることはない。 (3)NOT NULL制約を指定しない列には,1バイトのフラグが付加される。 (4)列の主なデータ型は,表3のとおりである。 (5)索引にはユニーク索引と非ユニーク索引がある。 (6)DMLのアクセスパスは,RDBMSによって索引探索又は表探索が選択され る。索引探索が選択されるためには,WHERE名又はON MO ANDだけで結ば れた一つ以上の等値比較の述語の対象列が,索引キーの全体又は先頭から連続し た一つ以上の列に一致していなければならない。 ⑦)索引探索では,ページの読込みはバッファを介して行われ,バッファの置換え はLRU方式で行われる。同じページ長のテーブルは,一つのバッファを共有す る。バッファごとにバッファサイズを設定する。 =8 esis 8)索引のキー値の順番と,キー値が指す行の物理的な並び順が一致している割合 (AE,クラスタ率という)が高いほど,隣接するキー値が指す行が同じページ に格納されている割合が高い。 表3列の主なデータ型 CRAR n文字の半角固定長文字列(1ミnミ255)。文字列がn字未満の場合は,文字列 (0)の後方に半角の空白を埋めてnバイトの領域に格納される。 NCHAR(n)n文字の全角固定長文字列(1ミnミ127)。文字列がn字未満の場合は,文字列 aの後方に全角の空白を埋めて“nx2”バイトの領域に格納される。 NCHAR最大n文字の全角可変長文字列(1ミnミ4.000)。“値の文字数X2”"バイトの領 1 VARYINGQ)|域に格納され,4バイトの制御情報が付加される。 ー32,768て32,767の館囲の整数。2バイトの領域に格納される。 ~2,147,483,648~2,147,483,647の範囲の整数。4バイトの領域に格納される。 精度m(1ミmミ31),位取りn(OSnSm)の10進数。“m+2二1"の小数部 0001-01-01~9999-12-31の範囲内の日付。4バイトの領域に格納される。 2.ウィンドウ関数 RDBMSがサポートする主なウィンドウ関数を,表4に示す。 表4 RDBMSがサポートする主なウィンドウ関数 SOTA RANK()区画化列をPARTITION BY名のe1に,ランク化列をORDER BY WOM e2に OVER([PARTITION指定する。対象となる行の集まりを,区画化列の値が等しい部分(区画)に BY e1]ORDER BY分割し,各区画内で行をランク化列によって順序付けした順位を,1から始 まる番号で返す。同値は同順位として,同順位の数だけ順位をとばす。 e2[ASCIDESC]) (ill:122.4…) 階級数をNTILEの引数e1に,区画化列をPARTITION BY句のe2に,階級 NTILE(e1)化列をORDER BY句のe3に指定する。対象となる行の集まりを,区画化列 OVER([PARTITION|の値が等しい部分(区画)に分割し,各区画内で行を階級化列によって順序 BY e2]ORDER BY|付けし,行数が均等になるように順序に沿って階級数分の等間隔の部分(タ e3[ASCIDESC])イル)に分割する。各タイルに,順序に沿って1からの連続する階級番号を 付け,各行の該当する階級番号を返す。 注記1関数の構文の[]で囲われた部分は,省略可能であることを表す。 注記2 PARTITION BY句を省略した場合,関数の対象行の集まり全体が一つの区画となる。 注記3 ORDER BY句の[ASCIDESC]を省略した場合,ASCを指定した場合と同じ動作となる。 ーー9ーー 3.クラスタ構成のサポート (1)シェアードナッシング方式のクラスタ構成をサポートする。クラスタは複数の ノードで構成され,各ノードには専用のディスク装置をもつ。 (2②)ノードへのデータの配置方法には複製,分散の二つがあり,テーブルごとにとど ちらかを指定する。複製は,各ノードにテーブルの全行を配置する方法である。 分散は,一つ又は複数の列を分散キーとし,その値に基づいてRDBMS内部で 生成するハッシュ値によって,各ノードにデータを配置する方法である。 (3)データベースへの要求はいずれかーつのノードで受け付ける。有要求を受け付 けたノードは,要求を解析し,自ノードに配置されているデータへの処理は自ノ ードで処理を行う。自ノードに配置されていないデータへの処理は,当該データ が配置されている他ノードに処理を依頼し,結果を受け取る。 [システムの構成〕 (1)複数のAPサーバと複数のDBサーバから成る。ユーザは,APサーバを介して 操作を行う。APサーバはいずれか一つのDBサーバにアクセスする。 (2)データベースは,シェアードナッシングのクラスタ構成とし,20ノードを配置 する。各ノードには同じ仕様のDBサーバを配置する。クラスタ構成によるDBサ ーバ全体のTPSは,ノード数に比例することを確認している。 (3)機器ログ,買電,発電,使用電力の各テーブルは,配置方法を分散にし,住居番 号を分散キーに指定する。それ以外のテーブルの配置方法は複製にする。 〔データベースの物理設計〕 1.テーブルの行数,所要量見積り 次の前提で主なテーブルの行数,所要量を見積もり,表5を作成した。 ①)日射量,機器ログ,買電,発電,使用電力は,2年前の1月1日から現在ま で,最大3年分のデータを保存する。1年を360日として行数を見積もる。 2)どのテーブルもページ長を2.000バイト,空き領域率を10%とする。 (3)見積ページ数は,"見積行数ページ当たり平均行数”の小数部を切り上げ る。ページ当たり平均行数は,“ページ長X(1一空き領域率)+PTR”の 小数部を切り捨てる。 ー10一 (4)所要量は,“見積ページ数Xページ長”で算出する。 表5主なテーブルの見積行数・所要量 ーーoi=平均行長見積所要量 |地域”。|t000件。|Lt000|10|el uizk 8件/住選 10件住居 1日200件住居|2L600900000|80|98Lalalez| r964G| |日射量|1日12件観測点 Pea 1 A 1007 Ee Ene 1 EL 500(F/R 注記表中の単位kは1000,MIX 1005,Gは10億を表す。 2.テーブル定義表の作成 次の方針に基づいてテーブル定義表を作成した。その一部を表6て8に示す。 (1)データ型欄には,適切なデータ型,適切な長さ,精度,位取りを記入する。 (2)NOT NULL柚には,NOT NULL制約がある場合だけYを記入する。 (3)格納長欄には,RDBMSの仕様に従って,格納長を記入する。 (4)索引の種類には,P(主キーの索引),U(ユニーク索引),NU(非ユニーク索 引)のいずれかを記入し,各構成列欄には,構成列の定義順に1からの連番を 記入する。該当する索引がなければどちらも空欄にする。 (5)主キー及び外部キーには,索引を定義する。 表6“BB”テーブルのテーブル定義表 項目データ型NOT格納長索引の種類と構成列 ROLL|sth)|5||| [人居番号。|mrgeER|vy|』li||| [年月[pare|y|』lsl| 時。|SMALLINT|Y|2|3||| |分。。|SMALLINT|Y|2|4||| fwam lpgcmArdo3|vy|sl||| 表7“発電"テーブルのテーブル定義表 データ型NOT|格納長|Heal ORL HT Rorr lfD ImT|| |信居番号。|rNrgeER|Y|411 1 j ames|SMALLNT_|Y|2 2 1|| feng[pare|oy|4 3 1!| fe[lsMarkmr|vy|sl 4|l| fa lswarumT|Y|2215 1| eam|pgcmwardあ|y|sl||| 表8"使用電力"テーブルのテーブル定義表 データ弄NOT|格納長|索引の種類と構成列 NuLL|バイト)|5||| ames。|NTEeER|Y|41 1|| 回中番号|SMAuNT|vy||2||| feag[pare dT Y|4 3 1|| me。lsMarrT|y||4||| |分。lswArLNT|Y|2|5 1|| 人用電力量”|pgcmMArdO5|Y|||| 3.テーブル構造の検討 表2の処理1では,ストレージからの読込みに時間が掛かるおそれがあるので, 次の前提で読込みページ数の予測を行い,必要であれば対策を講じる。 (1)前提 ・図1の使用電力量表示画面で,年月日の区分を"月',対応する年月を2019 年4月',比較対象を“前年の同じ月として照会を行う。 ・“買電","発電",“使用電力"の各テーブルには,住居番号当たりの行数は均 BC,どのページにも最大行数分の行が格納されているものとする。 ・アクセスパスは,どのテーブルも索引探索が選択されるものとする。RIO ッファヒット率は,100%とする。 (2)予測 ・表9に使用電力量表示画面における読込みページ数の予測をまとめた。 ・探索行数は,選択条件に一致する行を求めるために読み込む行数である。 ・最小読込みページ数は,クラスタ率が最も高い場合の読込みページ数で, “Ta Jb]"の玉部を切り上げた値に等しい。 ・最大読込みページ数は,クラスタ率が最も低い場合の読込みページ数で, |<|に一致する。 ・クラスタ率50%の読込みページ数を平均読込みページ数といい,“(最小読込 みページ数十最大読込みページ数)X50%”の小数部を切り上げる。 表9使用電力量表示画面における読込みページ数の予測(未完成) RS|wl ol軸還還還NB CEES) janes|wo]De|Ce|Cel 注記網掛け部分は表示していない。 (3)対策 表9の結果から照会の応答時間が長すぎると判断した。そこで,次の対策を 行うことで,平均読込みページ数を100以下にすることにした。 ・照会対象となる電力量を集計した一つつ又は複数のテーブルを追加する。 ・追加したテーブルには,前日までの集計行を夜間バッチ処理で追加する。 ・当日の電力量は“AB”,発電”,“使用電力"の各テーブルから求め,それ以 外の電力量は追加したテーブルから求める。 [問合せの検討] 1.表2の処理3の問合せ 処理3の問合せ内容を表10に整理し,問合せに用いるSQL文を図3に作成した。 問合せ内容は,次の要領で記入し,内容のない欄は“ー"にする。 (1)行ごとに構成要素となる問合せを記述する。結果を他の問合せで参照する場合 lt,行に固有の名前(以下,問合せ名という)を付ける。 (2)列名又は演算には,テーブルから射影する列名又は演算(MAX関数,AVG関 数など)によって求まる項目を“項目名=[演算の概要]”の形式で記述する。 (3)テーブル名又は問合せ名には,参照するテーブル名又は問合せ名を記入する。 (4)選択又は結合の内容には,テーブル名又は問合せ名ごとの選択条件,結合の具 体的な方法と結合条件を記入する。 ー19一 表19処理3の問合せ内容 =ュー 住居番号,年月日,時,標準|住居,日射量|①住居から指定された住居番号に一致 発電量,距離=[住居と日射量する行を選択 観測虚の距離]②日射量から指定された年月日,時に W1一致する行を選択 ③①と②の結果の直積から住居と日身 量観測点の距離が10 km以下の行を選 択 回 W2発電量,ランク=[距離を昇順 住居番号,年月日,時,標準|W1 W1の全行を選択 住居番号,年月日,時,平均|W2ランクが2以下の行を選択 “8標準発電量=[住居番号,年月 日,時ごとの標準発電量の平 均] ES W4発電量=[住居番号,年月日,る行を選択 住居番号,年月日,時,時間|発電住居番号,年月日,時が指定値に一致す 準発電量,時間発電量②①の結果とW4を住居番号,年月 日,時で内結合 WITH W1 AS(SELECT A.住居番号,B.年月日,B.時,B.標準発電量, ST DISTANCE(ST_POINT(A.経度,A.緯度),ST_POINT(B.経度,B.緯度))AS距離 FROM住居A JOIN日射量B WHERE A.住居番号=:hv1 AND B.年月日=CAST(:hv2 AS DATE)AND B.時=:hv3 AND ST DISTANCE(ST_POINT(A.経度,A.緯度),ST_POINT(B.経度,B.紳度))<=10000), W2 AS(SELECT住居番号,年月日,時,標準発電量, RANK()OVER(ORDER BY ASC)ASランクFROM W1), W3 AS(SELECT住居番号,年月日,時,Ci FROM W2 WHEREランク<=2 W4 AS(SELECT住居番号,AA,85,FROM発電WHERE住居番号=:hv1 AND年月日=CAST(:hv2 AS DATE)AND時=:hv3 ) SELECT A.住居番号,A.年月日,A.時,A.平均標準発電量,B.時間発電量 FROM W3 A INNER JOIN W4 B ON A.住居番号=B.住居番号AND A.年月日=B.年月日 AND A.時=B.時 注記1 ST POINTGuy)は,地図上の経度x,緯度yに対応する地点情報を返す関数 注記2 ST DISTANCE(p1jp2)は,地図上の二つの位置p1,p2間の距離をメートル単位で返す関数 注記3 hv1,hv2,hv3は,ホスト変数 図3処理3の問合せに用いるSQL文(未完成) 2.表2の処理4の問合せ 処理3と同様に,処理4の問合せ内容を表11に整理し,問合せに用いるSQL文 を図4に作成した。 表11処理4の問合せ内容(未完成) 問合せcoテーブル名‘ae 列名又は演算Sea選択又は結合の内容 地域コード,住居番号,合計使|使用電力,住|①使用電力から年月日の年月が指定 W1用電力量=[地域コード,住居番|居された年月に一致する行を選択 号ごとの使用電力量の合計]@[」] 地域コード,住居番号,合計使|W1 W1の全行を選択 用電力量,階級番号=[地域コー ドによって分割した区画ごと Woに,各住居の合計使用電力量を 大きい方から順序付けし,順序 に沿って住居数が均等になるよ うに5階級に分けて付与した1 て5の番号] 地域コード,階級番号,W2の全行を選択 地域コード,地域名,階級番|W3,地域① 号,平均使用電力量@る|[。| WITH W1 AS(SELECT B.地域コード,B.住居番号,SUM(A.使用電力量)AS合計使用電力量 FROM WHERE GROUP BY B.地域コード,B.住居番号), W2 AS(SELECT地域コード,住居番号,合計使用電力量, NTILE(5)OVER([p])AS階級番号FROM W1), W3 AS(SELECT地域コード,階級番号,AVG(合計使用電力量)AS平均使用電力量 FROM W2 GROUP BY地域コード,階級番号) SELECT A.地域コード,B.地域名,A.階級番号,A.平均使用電力量 mmロゴ 注記網掛け部分は表示していない。 図4処理4の問合せに用いるSQL文(未完成) [性能テストの実施] ピーク時の処理性能を見積もった上で,性能テストを実施して検証する。 1.性能テストの方針 (1)DBサーバ2台による2ノードのクラスタ構成のテスト環境を使用する。各 DBサーバの仕様は本番のDBサーバと同じにする。また,ページ長が2,000バ イトのバッファには,480,000ページ分のサイズを設定する。 (2)ピーク時の5分間に,表2中の複数の処理が,それぞれ複数同時にDBサーバ 上で実行される状況を模してテストを行い,応答時間を計測して見積りと比較す る。処理は,それぞれピーク時の想定に基づきDBサーバごとに50~100の多重 度で実行する。 (3)表5の見積行数を基に,テスト環境の特性を踏まえて,適切な行数,適切な 列値,参照整合性を備えたテストデータを作成する。 2.応答時間の見積り 次の前提に基づいて,トランザクション当たりの応答時間見積りを表12にまと めた。 (1)ページ当たりのバッファへのアクセス時間は,平均0.1ミリ秒である。 (2)ページ当たりのストレージへのアクセス時間は,平均8ミリ秒である。 (3)各DBサーバは,本テストにおける最大の同時並行処理数を上回る並行処理能 力を備えている。 (4)DBサーバ上の実行待ち時間,ネットワーク待ち時間,CPU待ち時間は考慮し ない。 表12トランザクション当たりの応答時間見積り 処理3[tof om 3.テストデータの作成 主なテーブルのテストデータの作成要領を表13に示す。 ー16一 表13主なテーブルのテストデータの作成要領 テストデータの作成 見積行数の|・住居番号は,1からの連番を付与する。 10分の1・住所などの文字列型の列には,その列の平均的な文字数を基準に enランダムな文字列を生成して設定する。 ・外部キー列には,参照先のテーブルの主キー列に存在する値を設 定する。 ・地域コードには,適切な列値を設定する。 eam|A住居に存在する住居番号ごとに,1て8の回路番号を順に設定して行 を追加する。回路名など他の列には任意の値を設定する。 Ha同上住居に存在する住居番号ごと,3年分の年月日ごとに,一定間隔の ーー時,分を設定し,住居番号,年月日,時,分順に行を追加する。 gem.nea|AE配電回路に存在する住居番号,回路番号ごと,3年分の年月日ごと onに,一定間隔の時,分を設定し,住居番号,回路番号,年月日, “時,分順に行を追加する。 同上住居に存在する住居番号ごと,3年分の連続する年月日ごとに,連 機器ログ続するログ番号を設定して,住居番号,年月日順に,1日当たり平 均200行を追加する。 cee見積行数と|800 HAO,経度は,地図上均等に分散するようにし,緯度・ 同数経度の組ごとに,3年分の年月日,時の行を追加する。 4.性能テストの結果 性能テストの実行結果を図5に示す。図5の処理の実行時間帯の列は,10秒間 を表し,網掛け部分は,その処理が1回以上実行された時間帯を表す。応答時間 の実測値を表12の見積りと比較したところ,次の差異があった。 差異1:処理5は,どの時間帯でもバッファヒット率が90%を超え,応答時間 は見積りの約50分の1だった。 差異2:処理3は,図5中の①及び②の時間帯で応答時間が見積りよりも長か つた。 処理の実行時間帯 処理2|||TTT ETT TT LT EE Pd TE 処理3||]PPT Te ET TT Py Py EEE EET 5]|||1上目IIIIHTII四1 図5性能テストの実行結果

共通の文章

調達業務及び調達物流業務に関する次の記述を読んで,設問1ー3に答えよ。 機械メーカのA社は,調達業務及び調達物流業務のシステム再構築に向けて,業 務分析を行い,概念データモデル及び関係スキーマを設計している。 〔現状の業務分析の結果] 1.品目の特性 1)品目 ①品目には,製品,部品,素材がある。品目は品目コードで識別し,品目名, 評価額を設定する。部品と素材を併せて部材と呼ぶ。 ・製品は,産業用機械が主で,大型なものから小型なものまである。 ・部品には,切削部や搬送部などと呼ぶ,製品の主要な部位となる大型なも のから,組立てに用いる金具やパイプなど小型なものまである。 ・素材には,ロール状の鉄板やアルミ板,金属棒や塗料などがある。 @品目には,A社が設計する専用品と,それ以外の汎用品がある。専用品には 設計番号を設定し,汎用品には汎用品仕様として,メーカ名,カタログ名,カ タログ発行年月,カタログ品番を連結した文字列を設定する。 ③製品,Wah,素材が,それぞれ専用品と汎用品のいずれに該当するかは次の とおりである。 ・製品の全ては専用品に該当する。 ・部品は,専用品に該当するものと汎用品に該当するものがある。専用品に 該当する部品を専用部品,汎用品に該当する部品を汎用部品と呼ぶ。 ・素材の全ては汎用品に該当する。 ④専用部品には,その専用部品を輸送するときの個体重量を設定している。 ⑤部材には,その部材の在庫をもつときのために,次を設定している。 ・基準在庫数 ・調達する一定の数である調達ロットサイズ(以下,調達LSという) ・調達に要する日数である調達リードタイム(以下,調達LTという) (2)構成 ①品目のうち,製品と専用部品には,それを生産する上で必要となる下位の品 =0一 目があり,どの品目を幾つ用いるかの情報を,構成と呼ぶ。 ②下位の品目の多くは,複数の品目の構成に共通して用いられる。 ③製品の構成には,幾つかの専用部品,汎用部品,素材があり得る。 ④専用部品の構成にも,幾つかの専用部品,汎用部品,素材があり得る。 ©専用部品が,その構成に別の専用部品をもつ場合,構成から見て上位を親部 品,下位を子部品と呼ぶ。 ©Fim,その構成に,更に専用部品をもつことはない。 2.組織の特性 (1)社内の組織 ①A社の調達業務及び調達物流業務に関係する部門は次のとおりである。 ・一つの物流部 ・製品の種類ごとに3部門ある製品生産部 ・部品の種類ごとに5部門ある部品生産部 ②物流部は,調達手配,輸送手配及び在庫管理を行う。 ③物流部は,倉庫を管轄する。倉庫はA社に一つだけある。 ④製品生産部は,受注に基づいて,製品の生産に必要な部材の出庫指示を行い, 製品を生産する。また,生産ライン数を設定している。 ©部品生産部は,専用部品の生産指示に基づいて,A社が内製する専用部品を 生産する。また,専用部品の生産用に,部材の出庫指示を行う。 (②)社外の組織 ①部品及び素材を調達する先を調達先と呼び,調達先コードで識別する。 ②調達先のうち,専用部品を発注する先を協力会社(以下,BPという)と呼 び,BPフラグを設定している。 ③BPが,専用部品の生産に子部品を要する場合,その子部品はA社から支給 する。BPへの支給対象の子部品を支給部品とも呼ぶ。 ④調達先のうち,汎用品を購入する先を仕入先と呼び,仕入先フラグを設定し ている。 ⑤BPでかつ仕入先という調達先を禁じていない。 (3)社内と社外の組織を共通に見る見方 ①部品生産部とBPを総称して生産先と呼び,生産先コードを付与している。 ー21一 @倉庫とBPを総称して地点と呼び,地点コードを付与している。 (4)組織と品目の関係 ①汎用品は,その汎用品を調達する仕入先を一つに決めている。 ②専用部品は,その専用部品を生産する生産先を一つに決めている。 3.物流に関する資源の特性 1)車両 ①調達物流に用いるトラックを車両と呼ぶ。 ②車両は車両番号で識別し,最大積載重量を設定している。 (2)ルート ①A社では,専用部品の調達を,巡回集荷で行っている。巡回集荷とは,車両 が幾つかのBPを順に回って集荷するやり方である。 ②車両が,A社倉庫を出発し,4て8か所のBPを順に回り,再びA社倉庫に 戻る単位をルートと呼ぶよ、ルートはルート番号で識別し,標準で輸送する車両 を設定している。 ③ルートごとに,車両の出発地点の巡回順を1に,以降の到着する地点の巡 回順を2から付与し,到着予定時刻を設定している。 ④どのルートも,巡回順の最初と最後にA社倉庫を設定し,2 BALMICRO かのBPを設定している。ルートのイメージを図1に示す。 巡回順:2ソウ巡回順:5== 巡回順:3巡回順:4 巡回順:マー凡例WV teste ーーマルート中の経路 ト沙一のイメーダ 4.業務のやり方 Q)在庫のもち方 ①在庫をもつのは,倉庫と支給を行う対象のBPである。 ②在庫は,地点,品目ごとに把握している。 ③倉庫の在庫を倉庫在庫,BPの在庫をBP在庫と呼ぶ。 ー22一 ④倉庫在庫の在庫数は,入出庫の実績から求める。 ©BPでは,在庫の入出庫を記録しないので,在庫数は理論値で,次の入出庫 の実績から求める。この在庫数を理論在庫数と呼ぶ。 ・荷卸実績のうち支給部品のBPへの荷卸実績は,支給部品理論入庫実績でも ある。 ・親部品の発注に基づいて,BPは構成から求められる子部品を使用数分使う ので,その分の支給部品理論出庫実績を記録する。 (2)調達手配のやり方 ①調達手配は,部材を対象に行い,定量発注で行う。 Q定量発注とは,在庫数又は理論在庫数が基準在庫数を下回った場合,調達 LS分の手配を行うやり方である。 ③在庫数が基準在庫数を下回ったかどうかの確認は,毎営業日の営業時間終了 時に行う。 ④調達手配は物流部が行い,対象には部品生産部が内製する部品も含む。 ⑤調達手配は,次のように行う。 ・汎用品は,仕入先に発注する。 ・専用部品でかつ生産先がBPの場合,そのBPに発注する。 ・専用部品でかつ生産先が部品生産部の場合,その部品生産部に生産指示を 行う。 ・BPに支給する支給部品は,支給指示を行う。 (3)輸送のやり方 ①一つの輸送は,荷物をある地点で荷積みして別の地点で荷邊しするまでの 単位である。例えば,BPO c社に発注した専用部品の集荷の輸送は,c社で 荷積みしてA社倉庫で荷外しする。 ②輸送において,荷積みする地点を積地,荷外しする地点を卸地,それぞれ の巡回順を積地巡回順,卸地巡回順と呼ぶ。 ③輸送の必要な調達では,該当する調達手配に対応させて,輸送指示を行う。 ④輸送指示は輸送番号で識別する。輸送指示には,調達手配の日に調達LTの 日数を足した輸送日,積地ポ巡回順及び卸地芝回順を設定する。 ⑤輸送指示に基づき,荷積みした時点で荷積時刻の記録を行う。 =iy= ©輸送指示に基づき,荷邊しした時点で荷邊時刻の記録を行う。 5.業務の流れと情報 業務の流れを図2に,業務内容及び業務の流れにおける情報を表1に示す。 A:製品生産デーーーー OREし|]、=:easy ーー/ \ee⑧出庫実績 Aik B:汎用品調達 Os—<EE PANES a 1 C:専用部品調達|部品生産| ①生産指示CE出庫指示ie sala fs 専用部品用出庫指示OfLERm a|一 5 CBP>輸送指示[4かーーOAM 発注 ⑦理論出庫実績 BP在庫理論出庫 D:支給部品補充支給②輸送指示 ーー出庫指示 調遅手配支給部品出庫指示|ーー-骨支給部品上上庫上 !て@出庫実績 ①支給指示ーーに5) POS oP owe of) |1)ia Ca)ーーー時輸送Carer)に一 !\@pinseat 凡例(__]seas一と情報の流れ--->ものの流れ\在庫〈外部組織|)接続了 図2業務の流れ 表1業務内容及び業務の流れにおける情報 人 HIDEO MME)@|出庫指示|製品を生産するための部材の出庫指示 人出庫実績|Reta 5 Oe mE 生産実績|製品の生産実績 B:汎用品調達在庫数が基準在庫数を下回汎用品の仕入先への発注 +5.入庫実績|WA ROAD A mses った江用品を発注し,AE C:専用部品調達|在庫数が基準在庫数を下回専用部品の部品生産部への生産指示 った専用部品について,次専用部品を生産するための部材の出庫指示 うー|fmm[amis onmeR s生産先が部品生産部であ生産実績専用部品の生産実績 れば生産指示をかける。専用部品のBPへの発注 ・生産先がBPであれば発発注した専用部品を集荷する輸送指示 Bae Bs|eit|親部品の発注に伴って使用される子部品の 実績理論在庫数を減少させる数 |@⑧|入庫実績|専用部品の倉庫への入庫実績 D:支給部品補充支給|BPの理論在庫数が基準在子部品のBPへの支給指示 庫数を下回った部品につ支給部品を支給する輸送指示 出庫指示|支給する子部品の出庫指示 出庫実績|倉庫からの出庫実績 E:輸送輸送指示に基づいて輸送を|①|aca|荷積みの実績 行うiS|HNL SEA (Rat LABKOMAT—XEFVROMRAE—V) 概念データモデル及び関係スキーマは,VAR ROTEMORMRL,トランザクショ ンの領域を分けて作成し,マスタとトランザクションの間のリレーションシップは記 述しない。マスタ及び在庫領域の概念データモデルを図3に,トランザクション領 域の概念データモデルを図4に,マスタ及び在庫領域の関係スキーマを図5に,ト ランザクション領域の関係スキーマを図6に示す。 ルート“4;製品 |Be ハ\ ce一 ay ハハ 製品構成 at]ロ専用部品 図3マスタ及び在庫領域の概念データモデル(未完成) 生産実績共生実績|arenseat AA 発注I Aime a 専用部品||専用部品 生産指示生産実績gee論入庫実績 発注出庫実績 /\ 専用部品用出庫実績on出庫実% 出庫指示出庫指示出庫指示 M4トランザクション領域の概念データモデル(未完成) 車両(車両番号,最大積載重景) ルート(ルート番号,車両番号) ルート明細(ルート番号,巡回順,地点コード,到着予定時刻) 部門(部門コード,部門名,部門区分) 物流部(部門コード,部員数) 製品生産部(部門コード,) 部品生産部(部門コード,) 地点(地点コード,所在地。地点区分) 倉庫(地点コード,部門コード) 調達先(調達先コード,調達先名,BPフラグ,仕入先フラグ) BP(調達先コード,) 仕入先(調達先コード,信用ランク) 生産先(生産先コード,生産先区分) 品目(品目コード,品目名,評価額,品目区分,専汎区分) 製品(品目コード,製品種類) 部品(品目コード,部品種類) 素材(品目コード,規格内容) 専用品(品目コード,) 汎用品(品目コード,) 部材(品目コード,基準在庫数調達LS,調達LT,部材区分) 専用部品(品目コード,) 汎用部品(品目コード,メーカ部品名) 構成(上位品目コード,下位品目コード,下位品目使用数構成区分) 製品構成(製品品目コード,) 専用部品構成(専用部品品目コード,) 在庫(地点コード,品目コード,在庫区分) 倉庫在庫(倉庫地点コード,,在庫数) BP在庫(BP地点コード,,理論在庫数) 図5マスタ及び在庫領域の関係スキーマ(未完成) 受注(受注番号,製品品目コード,受注日,受注数) 製品生産実績(受注番号,生産完了日) 調達手配(WEES,FRA,調達予定日,手配数,FRED) 汎用品発注(調達番号,汎用品品目コード) 専用部品生産指示(調達番号,専用部品品目コード) 専用部品発注(調達番号,専用部品品目コード) 支給指示(EES,支給部品品目コード,支給先BP地点コード) 専用部品生産実績(調達番号、生産完了日) 輸送指示(輸送番号・較送日,輸送重量,ルート番号,積地世回順,外地芝回順 荷積実績(輸送番号,荷積時刻) HMM(輸送番号,荷卸時刻) 入庫実績(入庫番号,入庫実績区分) 倉庫入庫実績(入庫番号,調達番号,入庫地点コード,入庫日,入庫実績数) 支給部品理論入庫実績(入庫番号,) 出庫指示(出庫番号,出庫地点コード,出庫指示区分) 製品用出庫指示(出庫番号,,出庫指示数,出庫指示日) 専用部品用出庫指示(出庫番号,,出庫指示数) 支給部品出庫指示(出庫番号,) 出庫実績(出庫番号,出庫実績区分) 倉庫出庫実績(出庫番号,出庫実績数,出庫実績日) 支給部品理論出庫実績(出庫番号,) 図6トランザクション領域の関係スキーマ(未完成) [現状業務の間題と解決策] ①輸送時に荷物が車両の最大積載重量を超えないように,ルートの車両の大きさと 巡回する先を設定しているが,まれに最大積載重量を超え,問題となっている。 ②そこで,荷量計算という業務を,次のように追加して問題を解決する。 ・営業時間終了時に,翌営業日分の輸送指示について,ルート別巡回順別に輸送重 量の和を求める。 ・求めた輸送重量の和が,車両の最大積載重量を超えていた場合,巡回順と輸送番 号の順で,累計輸送重量が最大積載重量を超過した以降の輸送指示に対して,別 の車両を割り当て,車両を確定させる。人荷量計算のイメージを図7に示す。 ③現状の関係スキーマが,②に示した荷量計算が可能なデータ構造であることを検 証するために,関係スキーマ処理フローを作成した。関係スキーマ処理フローの表 記法を表2に,検証のために作成した関係スキーマ処理フローを図8に示す。 関係"輸送指示"に関係"ルート”を結合したビュー車両を確定させた情報 人 | -|6354 ee | -|6354 |3008|2019-04-22|1000|3|1|4|684|]| -|6854| |3004|2019-04-22|2000|3|2|6|689|| -|6354| |3011|2019-04-22|4000|3|3|6|689|| -|6354| |3012|2019-04-22|3000|3|4|6|6354| |3030|2019-04-22|2000|3|5|6|6854| 輸送指示を基にルート別巡回順別に輸送重量の和を求める。 前提として,標準の車両の最大積載重早は10.000kgである。 ルート|、荷積重量|荷卸重量|差引重量|累計輸送 |3|1|5,000]|soo|5000累計輸送重量が車両の最 |3|2|2000]|zoo|7.000 ee eee Boma a1)24°C,還 両を確定させる。 |3|5|2,000]2z000|0O|rooo |3|6|of 11,000]-uooo|ol 図7荷量計算のイメージ 表2関係スキーマ処理フローの表記法 EXT:抽出|条件に合う組を選び出す処理 抽出対象の関係スキーマ EXT:抽出対象属性名,抽出条件 抽出後の関係スキーマ FJOIN:|結合|関係スキーマの,参照元の属性と参照先の属性の値が等しいという条件で,参照元の組と参照先の 組を完全外結合する処理 参照元の関係スキーマ参照先の関係スキーマ FJOIN:参照元関係スキーマ名.参照元属性名1=参照先関係スキーマ名.参照先属性名1 AND参照元関係スキーマ名.参照元属性名2=参照先関係スキーマ名.参照先属性名2 完全外結合後の関係スキーマ SUM:集計|集計キーに指定した属性の値が同じ組について,集計対象の属性の合計値を求める処理 集計対象の関係スキーマ SUM:集計対象属性名AS集計によって導出する属性名 GROUP BY集計キー属性名1,集計キー属性名2,… 集計後の関係スキーマ GEN:演算|同一の組にある属性に演算を行い,結果を導出する処理 演算対象の関係スキーマ GEN:演算式AS演算によって導出する属性名 演算後の関係スキーマ TOT:累計|Me,区分キーごとに,ソートキーの昇順に並べ,累計対象属性の累計値を求める処理 累計対象の関係スキーマ TOT:累計対象属性名AS累計によって導出する属性名 PARTITION BY区分キー属性名1,区分キー属性名2,… ORDER BYソートキー属性名1,ソートキー属性名2,… 累計後の関係スキーマ UNION:|和|属性数が同じ二つの関係スキーマについて,全ての組の和集合を求める処理 関係スキーマ2 UNION: 関係スキーマ3 注記1集計又は演算によって導出した属性は“ぐ属性名>"と表記する。 注記2関係スキーマ処理フローの関係スキーマには,主キーを表す下線及び外部キーを表す破線の下線は記述しない。 注記3各処理記号の処理は,射影操作を含むものとする。 注記4 GEN:では,演算式中のNULLは0に置き換えて演算する。 輸送重量,ルート番号,積地巡回順,輸送重量,ルート番号,積地芝回順, 卸地芝回順)卸地芝回順) EXT:[| 。|,選営業日に等しいEXT:|b|,翌営業日に等しい 輸送重量,ルート番号,輸送重量,ルート番号, 積地巡回順,卸地巡回順)積地巡回順,卸地人巡回順) SUM:AS荷積重量SUM:[sg|AS荷卸重量 GROUP BY Ca]GROUP BY FIOIN:荷生計算,|gz|]=vimats.[ln] AND荷積計算,|」|=wats.」| EXT:積地タ巡回順,NULLでないEXT:積地巡回順,NULLである 荷積重量,NALA)荷積重量,荷卸重量) UNION: GEN:AS差引重量 TOT:差引重量AS累計輸送重量PARTITION BYルート番号ORDER BY巡回順 注記網掛け部分のサには,図6のサと同じ字句が入る。 図8検証のために作成した関係スキーマ処理フロー(未完成) 解答に当たっては,巻頭の表記ルールに従うこと。ただし,エンティティタイプ間 の対応関係にゼロを含むか否かの表記は必要ない。 なお,次のひー④についても従うこと。 ①リレーションシップの対応関係は1対1又は1対多とし,多対多としないこと。 ②属性名は意味を識別できる適切な名称とし,他の属性と区別できること。 ③識別可能なサブタイプにおいて,他のエンティティタイプとのリレーションシッ Flt,スーパタイプ又はサブタイプのいずれか適切な方との間に記述すること。ま ー30一 た,サブタイプ固有の属性がある場合,必ずそのサブタイプの属性とすること。 ④関係スキーマ中の属性名を答える場合,対象の関係スキーマは第3正規形を満た し,主キーを表す実線の下線,外部キーを表す破線の下線についても答えること。