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

共通の文章

大学の学習管理システムの概念データモデリングに関する次の記述を読んで,設 問に答えよ。 T大学は,オンライン講義の増加が落ち着いたことを受けて,急造の学習管理シス テムを見直すために概念データモデルと関係スキーマを設計した。 [現状の学習管理の概要] 1.学部,学科,学期 (1)T大学は,4年制総合大学で,複数の学部と各学部の中に複数の学科がある。 (2)学部は学部コードで識別し,学科は学科コードで識別する。 (3)年度は1年間(4月始まり)で,学期は前期(4一9月)と後期(19~3月)の 各6か月間である。前期,後期を半期,前期と後期とを併せて通期と呼ぶ。 2.教員 (1)教員は,教員番号,KA,生年月日をもつ。 (2)教員は,科目を担当し,講義を実施する。 3.学生 (1)学生は,学籍番号,氏名,生年月日をもつ。 (2)学生は,年度ごとに,前期,後期,通期のいずれかに対して科目の履修登録 を行う。 4.科目,開講科目 (1)科目は科目IDで識別する。 (2)科目には,科目名,科目目標,科目概要,単位数,必須か選択かの区分,開 講期間(半期又は通期)を設定する。 (3)学科には,対象年度に開講する科目を定める。開講年度,学期,曜日,時限, 担当教員1名,及び後述するインターネット経由の講義を行うための接続先URL を開講科目として登録する。 (4)一つの学科では,同じ科目を並行して開講することはない。ただし,異なる 学科で同じ科目が開講されることはある。 5.講義 (1)科目の講義回数は,半期開講と通期開講でそれぞれ15回程度と36回程度で =8= HY,1回の講義時間は90分である。 (2)講義の開講形態には,教員と学生が集合して実施する対面講義インターネ ット経由で同時双方向のリアルタイム型講義,事前に準備してある動画視聴フ ァイルを学生が好きなときにインターネット経由で視聴するオンデマンド型講 義の3種類があり,開講形態区分で分類する。1回の講義に複数の開講形態を 組み合わせることはない。 (3)講義は,開講科目に講義番号を付与して識別し,講義概要,開講形態区分, 開講年月日設定要否を設定する。 |(4)オンデマンド型講義では,動画視聴ファイル名,公開の開始と終了の年月日 時分を設定する。 (5)リアルタイム型講義では,開講年月日,オンライン教室IDを設定する。 (6)対面講義では,開講年月日と開講場所を設定する。 (7)リアルタイム型講義とオンデマンド型講義とを併せてオンライン講義と呼び, リアルタイム型講義と対面講義とを併せて時間固定講義と呼ぶ。オンライン講 義に該当する場合オンライン講義フラグを,時間固定講義に該当する場合時間 固定講義フラグをそれぞれ有効にする。 (8)オンライン講義と時間固定講義は,講義がもつ開講形態区分を継承して明示 的にもつ。 (9)対面講義が演習又は実験の場合科目担当教員に加えて,複数の教員が補助 として入る場合がある。 6,デジタルコシテッツ (1)オンライン講義で用いる教材及びテスト問題であり,コンテンツIDで識別し, コンテンツ名,コンテンツ概要,作成年月日時分をもつ。 (2)デジタルコンテンツが教材かテスト問題かは,コンテンツ区分で分類する。 (3)教材は,MB,画像,動画のいずれかであり,ファイル形式をもつ。 (4)教材を用いるのはリアルタイム型講義とオンデマンド型講義であり,一つ又 は複数の教材を使用し,講義ごとにそれぞれの教材の公開期間を開始と終了の 年月日によって設定している。 (5)テスト問題は,解答時間をもつ。 (6)テスト問題を用いるのはリアルタイム型講義だけである。リアルタイム型講 義では,一つ又は複数のテストを実施し,講義ごとにテスト問題の解答期限時 MI 刻,解答入力ページのURLを設定している。 7.講義受講とテスト結果,成績の記録 (1)学生が履修登録した講義について,学生が出席すると受講年月日を講義受講 に記録する。 (2)時間固定講義では,遅刻の有無,早退の有無を記録する。 (3)オンデマンド型講義では,最後まで受講すると受講完了年月日を記録する。 (4)実施したテスト問題ごとに,採点した得点と評価年月日を記録する。 (5)通期又は半期の全講義終了後に,出席状況とテストの採点結果を基に科目の 履修登録に成績を記録する。 [概念データモデルと関係スキーマの設計]〕 現状の学習管理システムの概念データモデルを図1に,関係スキーマを図2に示す。 AMS x Nしっ!デジタル h ngオンライ>ンテンツ\ テスト問是 ハハ 図1現状の学習管理レステムの概念データモデル(未完成) ー8一 学部(学部コード,学部名) 学科(学科コード,学科名,学部コード) 科目(BBD,科目名。科目目標,科目概要,単位数必須選択区分,開講期間) 教員(BABS,氏名生年月日) 学生(学籍番号,氏名,生年月日) 開講科目(学科コード,科目ID年度,学期,開講曜日,開講時限.) 講義(学科コード,HED,年度,学期講義番号,講義概要,開講形態区分、オンライン講義フラグ. 時間固定講義フラグ,開講年月日設定要否) 時間固定講義(学科コード,HED,年度,学期,講義番号) オンライン講義(学科コード,HED,年度,学期,講義番号,) 対面講義(学科コード,科目ID年度,学期,講義番号) リアルタイム型講義(学科コード,MAID,年度,学期,講義番号,) オンデマンド型講義(学科コード,AID,年度,学期,講義番号) [|L_。|](|#_).sas) デジタルコンテンツ(コンテンツID,コンテンツ名,コンテンツ区分,コンテンツ概要,作成年月日時分) 教材(コンテンツID,ファイル形式) テスト問題(コンテンツID,解答時間) コンテンツ使用(学科コード,科目ID,年度,学期,講義番号,コンテンツiD,[コンテンツ区分]) 教材使用(学科コード,科目ID,年度,学期,講義番号,コンテンツID,) テスト問題使用(学科コード,科目ID,年度,学期,講義番号,コンテンツID, 科目履修登録(|コ|) 講義受講(学科コード,HAD,年度,学期,講義番号,SHES,講義受講区分, 時間固定講義受講(学科コード,AAI,SH,SM,MES,SHES,) オンデマンド型講義受講(学科コード,ABD,年度,学期,講義番号,学籍番号 テスト結果(学科コード,科目ID年度,学期,講義番号,コンテンツID,SHES,SA,EAA) 注記図中の|』|]には,図1中の[|。|]と同じ字句が入る。 図2現状の学習管理システムの関係スキーマ(REM) 〔学習管理システムの拡充〕 学習管理システムを用いてオンデマンド型講義でレポート課題を実施することに した。そのやり方について次のように検討し,関係スキーマを設計した。追加及び 変更する関係スキーマを図3に示す。 (1)デジタルコンテンツにレポート課題を加える。デジタルコンテンツがレポート 課題に該当することは,コンテンツ区分に値を追加して分類する。 (2)レポート課題は.。レポートすべき観点を説明した課題観点記述をもつ。 (3)レポート課題を用いるのはオンデマンド型講義だけである。レポート課題は, 教員の判断で必要なオンデマンド型講義の回に用いる。1回のオンデマンド型講 義で課すレポート課題はーつだけである。 に9= (4)レポート課題の出題では,その講義でのレポート提出先URL,提出期間の開始 と終了の年月日を設定する。 (5)学生が提出したレポートに対して,レポート評価と評価年月日をレポート評価 結果として記録する。レポート評価結果とテスト結果を汎化して評価結果とする。 陸SNWWiHii= 評価結果(学科コード,科目ID,年度,学期,講義番号,コンテンツID,学籍番号,評価結果区分, レポート課題出題(学科コード,科目ID年度,学期,講義番号.コンテンツ1D,) テスト結果(学科コード,科目ID,年度,学期講義番号,コンテンツ1D,FHSS,) レポート評価結果(学科コード,科目ID,年度,学期,講義番号,コンテンツ1D,学籍番号,|ツ|) 図3追加及び変更する関係スキーマ(未完成) 解答に当たっては,巻頭の表記ルール及び次に従うこと。ただし,エンティテ ィタイプ間の対応関係にゼロを含むか否かの表記は必要ない。 ①関係スキーマは第3正規形にし,“SHS”のリレーションシップは用いない こと。 ②識別可能なサブタイプが存在する場合,他のエンティティタイプとのリレー ションシップは,スーパータイプ又はサブタイプのいずれか適切な方との間に 記述すること。 ③同一のエンティティタイプ間に異なる役割をもつ複数のリレーションシップ が存在する場合,役割の数だけリレーションシップを表す線を引くこと。 ④エンティティタイプ名,関係名,属性名は,それぞれ意味を識別できる適切 な名称とすること。 ⑤関係スキーマに入れる属性名を答える場合,主キーを表す下線,外部キーを 表す破線の下線についても示すこと。 ©スーパータイプには,対応するサブタイプを識別する切り口の役割の属性を Boeを。 なお,スーパータイプに対応するサブタイプを識別する切り口の役割の属性 には,冗長な属性が必要になる場合がある。この場合,冗長にもたせた属性の 属性名の前後を“["と“」で挟んで明示する。 ー10一

共通の文章

化学メーカーの営業販売管理システムの概念データモデリングに関する次の記述 を読んで,設問に答えよ。 A社は,樹脂や繊維などを扱う化学メーカーである。営業販売管理システムを新た に導入することになり,Bさんが現状業務の分析とデータベース設計を行った。 [Bさんが分析した現状業務] 1.A社及び得意先の人・組織 (1)A社の従業員は,従業員コードで識別し,従業員氏名をもつ。同姓同名の従 業員は存在し得る。 (2)A社と取引を行っている,又はこれから取引を行う可能性のある事業者を得 意先と呼ぶ。得意先は,得意先コードで識別し,得意先名と所在地情報をもつ。 (3)得意先担当者は,得意先ごとに担当者コードで識別し,担当者氏名,所属先 組織名,及び役職名をもつ。 2.製品 (1)A社で扱う製品は,原料の配合比率,添加剤の種類などの仕様が異なる製品 ごとに製品型番をもつ。 (2)製品は,公表仕様をもつ。 (3)A社は,後述する契約が成立すると,数か月にわたって製品を各月均等の数 量で得意先へ納品する。各月の納品は,1回又は複数回行う。 3.案件 (1)得意先に対する営業活動の対象を案件と呼ぶ。 ①案件は,案件コードで識別し,案件名,年間見込売上金額,当該案件が受 注できたかどうかを記録する受注フラグ,案件開始年月日,及び案件終了年 月日をもつ。案件開始年月日は,得意先から製品に関する問合せを受けた日 付であり,案件終了年月日は,得意先に対して当該案件で取り扱う製品の販 売を終えた日付である。 ②一つの案件で扱う製品はーーつであり,営業活動中に得意先担当者から別の 製品に関する問合せがあった場合,新たな案件として扱う。 ③案件の初期段階で,対象の製品が確定していない場合,製品は未登録とし = ておき,確定し次第登録する。 (2)1名又は複数名のA社の従業員が,案件を担当する。 ①1名の従業員が,複数案件に携わることもある。 ②案件を担当する従業員に対しては,対象案件の担当開始年月日,担当終了 年月日,及び担当業務内容を記録する。 4.商談 (1)案件担当従業員と得意先担当者との間で,1回又は複数回の商談を実施する。 (2)各商談の実施後,商談開始年月日時分,商談終了年月日時分,商談場所,議 事内容,及び商談参加者を記録する。 ©同一案件について,複数の商談を同一年月日時分に開始することはない。 ②商談場所には,A社会議室,得意先会議室,オンライン会議などがある。 ③1名以上の案件担当従業員と,1名以上の得意先担当者が商談に参加する。 5.見積り (1)得意先担当者から見積りの依頼があった場合,案件担当従業員が見積りを行 う。見積りは,見積番号で識別し,見積年月日をもつ。 (2)見積りでは,当該案件で扱う製品の納品条件(Mae,納品先,納品期間, 月間納品数量,納品頻度など)を基に,販売単価と諸経費(輸送費など)を算 出し,納品期間における合計金額を得意先担当者へ提示する。納品条件の違い によって,複数の見積りを提示することもある。 (3)同一の納品条件を適用する見積対象期間は最長1年である。得意先が,見積 対象期間後も継続して同一の納品条件で取引を希望する場合がある。原料や輸 送費などの変動によって,販売単価や諸経費を変更することがあるので,継続 期間について再度見積りを行う。また,案件としては同じ案件を継続する。 6.契約 (1)得意先と見積りに合意した後,得意先と契約を締結する。 (2)案件担当従業員は,契約情報として,得意先と合意した見積番号,納品荷姿, 納品先の所在地情報,納品期間,月間納品数量,納品頻度,及び初回納品年月 日時分とその数量を記録する。契約は,契約番号で識別し,契約年月日をもつ。 (3)契約情報を基に,工場に対して製品の生産を指示する。 a= 7.得意先納品要求 得意先から,契約に対して納品要求を受ける。具体的には,要求納期の2週間前 までに,要求納期年月日時分と要求数量の連絡を受けて記録する。 8.納品 (1)案件担当従業員は,要求納期年月日時分に到着するように納品を手配する。 (2)案件担当従業員は,納品が完了したら,納品完了アフラグを立てる。 [現状業務の概念データモデルと関係スキーマ]〕 現状業務の概念データモデルを図1に,関係スキーマを図2に示す。 eee aデー 得意先担当才納品要求|| 3: !得意先納品要求に関するトランザクションの領域 図1現状業務の概念データモデル(未完成) 得意先(得意先コード,得意先名,所在地情報) 得意先担当者(得意先コード,担当者コード,担当者氏名,所属先組織名,役職名) 製品(製品型番,製品名|。|) 案件(案件コード,RES,年間見込売上金額.受注フラグ,案件開始年月日,案件終了年月日 Lo»)) 商談(商談終了年月日時分,商談場所議事内容,) 商談参加従業員(案件コード,従業員コード,従業員氏名商談開始年月日時分案件担当開始年月日 案件担当終了年月日,担当業務内容) 商談参加得意先担当者(目千|) 見積り(見積番号,納品条件販売単価,BER,見積年月日,見積対象期間|q]) 契約(契約番号,AMES,納品荷姿,納品先の所在地情報,納品期間,月間納品数量,WRIA, 初回納品年月日時分,初回納品数量,契約年月日) 得意先納品要求(契約番号,要求番号,要求納期年月日時分,SRA,納品完了フラグ) 注記設問の都合上,網掛け部分は表示していない。 図2現状業務の関係スキーマ(ASEM) [指摘事項] 営業部長のCさんは,Bさんのデータベース設計に対して,次の納期調整業務及び 出荷業務を加味して再設計するように依頼した。 1.納期調整 Bさんは,得意先が要求する納期年月日時分と数量どおりに納品している前提で 設計しているが,実際は,当社及び得意先の現場の都合で,得意先納品要求の一 部又は全量について,前倒し又は先送りをすることがある。そこで,案件担当従 業員は,生産管理担当従業員と相談し,納品する数量と納期を調整する。その際, 図3に示す納期調整シートを用いて納期の交渉を行う。納期調整シートには,左側 に得意先納品要求,右側に得意先納品要求に対応する納期回答(納品可能年月日 時分と納品可能数量)を記載する。納期調整シートを基に,案件担当従業員は得 意先担当者と納期の交渉を行い,同意を得られたら人確定フラグを立てて出荷へ進 む。 2.出荷 納期調整の結果,同じ納品可能年月日時分の納品は,まとめて自社倉庫から出 fig>,出荷完了後,案件担当従業員は,出荷数量と出荷年月日時分を記録する。 出荷は,出荷番号で識別する。図3に示す納期調整シートには,出荷が完了した出 荷番号を記載する。 要求要求納期要求|納期調整納品可能納品可能|確定|出荷 番号年月日時分数量番号年月日時分数量フラグ|番号 |003|2025-01-14 11:00|59|0|2025-01-12 14:00|50|mee ||2925-e-1eiti99|soo|| 図3納期調整シートの帳票サンプル [指摘事項への対応】 Cさんからの指摘を受けて,Bさんは図1中の得意先納品要求に関するトランザク ションの領域に,新たに納期調整及び出荷を追加し,概念データモデルを図4のよう に修正した。 ee ee eee eee ! : !! ! し人に関するトランザクションの信吐| 図4修正後の概念データモデル(ASM) 解答に当たっては,巻頭の表記ルールに従うこと。ただし,エンティティタイプ 間の対応関係にゼロを含むか否かの表記は必要ない。エンティティタイプ間のリレ ーションシップとして“多対多"のリレーションシップを用いないこと。属性名は, 意味を識別できる適切な名称とすること。

共通の文章

記述を読んで,設問に答えよ。 オフィスじゅう器メーカーのY社は,RDBMSを用いた在庫管理システムを稼働させ てから10年が経ち,システム更改を予定している。そこで,Kさんが改善点の検討 を任された。 (RDBMSの主な仕様 1.アクセス経路 (1)アクセス経路は,RDBMNSによって表探索又は索引探索に決められる。表探索 では,索引を使わずに先頭ページから順に全行を読み込む。索引探索では,索 引によって絞り込んでから表の行を読み込む。 (2)索引探索を選択するためには,WHERE句又はON YO ANDだけで結ばれた一つ 以上の等値比較の述語の対象列が,索引キーの全体又は先頭から連続した一つ 以上の列に一致していなければならない。 (3)索引探索のとき,SELECT文で取得対象とする列と,探索条件で絞込みに利用 する列が,索引キーに全て含まれている場合は,表の行を読み込まずに,索引 へのアクセスだけで結果を返す。 2.排他制御 (1)排他制御では行単位で共有ロック又は専有ロックを取得する。共有ロックを 取得している間,他のトランザクションから対象行への共有ロックの取得は可 能であり,専有ロックの取得は共有ロックの解放待ちとなる。専有ロックを取 得している間,他のトランザクションから対象行への共有ロック及び専有ロッ クの取得は,専有ロックの解放待ちとなる。 (2)アクセス経路に従って,探索した行をロックの対象とする。 (3)SELECT文にFOR UPDATE句を指定すると,ISOLATIOUNレベルにかかわらず,対 象行の専有ロックを取得し,トランザクション終了時に解放する。 3.時刻印型の列 (1)テーブル定義で,時刻印型の列(LAE,TS列という)のDEFAULT句に CURRENT TIMESTAMPを指定することで,行が挿入されたとき,TS列に現在時刻 ー稲ご 印を自動的に設定することができる。 (2)行が更新されたときにTS列をCURRENT_TIMESTAMPで更新するトリガーを定義 することで,TS列を現在時刻印で自動的に更新することができる。 (在庫管理システムの概要〕 1.テーブル構造 主なテーブルのテーブル構造を図1に示す。主キーには主索引が定義されている。 es 部品(部品番号,部品名,部品単価) 在庫(倉庫コード,部品番号,在庫数量,引当済在庫数量,最終更新TS) 入庫(入庫番号,入庫年月日入庫倉庫コード,部品番号,入庫数量,最終更新TS) 出庫(出庫番号,出庫年月日,出庫食庫コード,部品番号,出庫数量,処理状況,最終更新TS) 注記1年月日を示す列のデータ型は,日付型とする。 注記2最終更新TS列は,挿入・更新時の現在時刻印で自動的に更新されるTS列とする。 図1主なテーブルのテーブル構造 2.業務の概要 (1)各地の生産拠点には,組立工場と,これに隣接する倉庫がある。 (2)倉庫からの部品の出庫には,倉庫から隣接する組立工場に出庫する場合と, 倉庫から他の生産拠点の倉庫に出庫する場合がある。 (3)倉庫は,倉庫コードで識別する。部品は,部品番号で識別する。 (4)部品の在庫は,倉庫と部品の組合せで管理する。倉庫内に存在する在庫の数 量を在庫数量と呼ぶ。このうち,出庫対象として引き当てた数量を,引当済在 庫数量と呼ぶ。また,在庫数量から引当済在庫数量を引いた数量を,出庫可能 在庫数量と呼ぶ。 (5)部品の出庫は,出庫要求,在庫引当,出庫,在庫反映の流れで,それぞれの アプリケーションプログラム(LAF,AP EWS)を用いて処理し,“出庫”テー ブルに処理状況を記録する。 ・出庫要求とは,部品の出庫要求を受け付けて,“出庫'”テーブルに行を追加す る処理である。処理状況を要求発生にする。 ・在庫引当とは,出庫要求に応じて倉庫内の在庫を引き当てる処理である。指 =Gc 定された倉庫コード,部品番号,出庫数量の出庫が可能かどうかチェックし, 出庫が可能であれば“在庫"テーブルの引当済在庫数量を更新し,処理状況 を"引当実施た有にする。 ・出庫とは,倉庫から在庫の引当ができた部品を出す処理である。処理状況を 出庫済、にする。 ・在庫反映とは,処理状況が“出庫済の全ての出庫を対象に,“在庫"テーブ ルの在庫数量及び引当済在庫数量から引き落として,処理状況を"在庫反映 済にする一括処理である。"在庫反映"APは,毎日の業務終了時に実行する。 (6)入庫とは,部品メーカーから納品された,又は他の生産拠点の倉庫から出庫 された部品を倉庫に入れることである。運ばれてきた部品を倉庫内の集積所に BRL,当日の入庫予定分がそろったら順に入庫を処理し,在庫を更新する。 入庫による在庫の更新は,在庫反映と同時に実施しない。 〔在庫履歴分析手段の見直し〕 日別の在庫数量を集計するAPを用いて,在庫数量の日別の推移状況を分析してい る。このAPでは,“ER”,“HB”及び“入庫"テーブルを参照して,在庫数量を集 計しており,データ量の増加に伴って処理時間が長くなっていた。この対策として, 図2に示す"在庫履歴"テーブルを作成して,在庫数量の推移を蓄積していくことに じた。 注記年月日を示す列のデータ型は,日付型とする。 図2“在庫履歴"テーブルのテーブル構造 Kさんは,“EB”,“出庫"及び“入庫"テーブルからこれまでの在庫履歴を作成 する方法を検討した。 全ての倉庫の全ての部品について,在庫履歴を作成する初日時点の在庫数量の行 をあらかじめ作成しておき,在庫履歴を蓄積する年月日分繰り返し実行するSQL1を 設計した。 また,区間数7の中央移動平均のグラフを作成したいとの要望があった。区間数7 ー29一 の中央移動平均は,ある年月日とその前後3日間の計7日間の平均値である。計7日 間のデータがそろわない期間は対象外である。Kさんは,作成した在庫履歴のデータ から区間数7の中央移動平均用データを加工するSQL2を設計した。 設計したSQL文を,表1に示す。 表1在庫履歴用のSQL文(未完成) SOL文の構文(上段:目的,下段:構文) 全ての倉庫の全ての部品について,指定した年月日の業務終了時点の在庫数量を算出する。 INSERT INTO在庫履歴(倉庫コード,部品番号,履歴年月日,在庫数量) WITH前日在庫AS( SELECT倉庫コード,部品番号,履歴年月日,在庫数量 FROM在庫履歴 WHERE履歴年月日PREVIOUS DAY(:hv1)), 出庫状況AS( SELECT出庫倉庫コード,部品番号,出庫年月日|qa|AS当日出庫数量 FROM出庫 WHERE出庫年月日=CAST(:hv1 AS DATE)AND処理状況='在庫反映済' GROUP BY出庫倉庫コード,部品番号,出庫年月日), SQL1入庫状況AS( SELECT入庫倉庫コード,部品番号,入庫年月日,|b|AS当日入庫数量 FROM入庫 WHERE入庫年月日=CAST(:hv1 AS DATE) GROUP BY入庫倉庫コード,部品番号,入庫年月日) SELECT A.倉庫コード,A.部品番号,CAST(:hv1 AS DATE), A.在庫数量-COALESCE(|c|],0)+COALESCE([d』|,0)AS在庫数量 FROM前日在庫A LEFT OUTER JOIN出庫状況B ON A.倉庫コード=B.出庫倉庫コード AND A.部品番号=B.部品番号 LEFT OUTER JOIN入庫状況C ON A.倉庫コード=C.入庫倉庫コード AND A.部品番号=C.部品番号 在庫履歴の倉庫コードごと部品番号ごとに,在庫数量の区間数7の中央移動平均を算出する。 SELECT倉庫コード,部品番号,履歴年月日, @CASE WHEN(COUNT(*)OVER移動平均区間)=7 THEN csr(L_e OVER移動平均区間)AS INTEGER) SQL2 ELSE NULL END AS中央移動平均 FROM在庫履歴 WINDOW移動平均区間AS( PARTITION BY orver By|se| ROWS BETWEEN PRECEDING AND|」|FOLLOWING) 注記1ホスト変数hv1には,年月日を設定する。 注記2 PREVIOUS_DAY(引数)は,引数(文字型)に指定された日付の前日(日付型)を返すユーザー定義関数で ある。 注記3 WINDOW句は,ウィンドウ関数のウィンドウを定義して名前を付ける。OVER句の後に名前を指定して参 照する。 [トランザクションの排他制御の見直し〕 既存システムの実行ログを分析すると,複数の“在庫引当"APが,"在庫テーブ ルの同じ行に対して同時実行されたときに,デッドロックによってAPのリトライが 発生していることが判明した。"在庫引当"APの処理の流れとSQL文を図3に示す。 ISOLATIONレベルはREPEATABLE READで実行している。 ①SELECT在庫数量,引当済在庫数量INTO:hv5,:hv6 FROM在庫 WHERE倉庫コード=:hv2 AND部品番号=:hv3 出庫可能在庫数量(hv5-hv6)とhv4を比較し,出庫が可能な場合だけ以降を実行する。 @UPDATE在庫SET引当済在庫数量=引当済在庫数量+:hv4 WHERE倉庫コード=:hv2 AND部品番号=:hv3 ③UPDATE出庫SET処理状況='引当実施'WHERE出庫番号=:hv1 ④COMMIT 注記ホスト変数hv1,hv2,hv3,hv4には,それぞれ出庫番号,出庫倉庫コード,部品番号,BK する出庫数量を設定する。ホスト変数hv5とhv6には,それぞれ在庫数量,引当済在庫数量の 検索結果を返す。 図3“在庫引当”APの処理の流れとSQL文 Kさんはデッドロックの原因を調査し,上司であるL氏と対応について議論した。 LE:デッドロックの原因は何でしょうか。 Kさん:ある"在庫引当"APが| 」|で共有ロックを取得した後, で専有ロックを取得し直す前に,WO“在庫引当"APが|」|で共有 ロックを取得してしまい,互いに専有ロックを取得しようとしてロックの解 放待ちになっていました。 L氏:どのような改善案がありますか。 KSA:"在庫テーブルの同じ行に対して,“在庫引当"APが同時実行されないよう に最終更新TS列で制御する案があります。"在庫引当”APを次のように変更 します。①のSELECT文で|||するように変更し,①の後にCOMMIT文 を追加します。②のUPDATE文のWHERE ic,|m|ことを条件として 追加します。②の後に,②のUPDATE文で該当する行がなかったら, に戻る制御を追加します。 L氏:その案ではAPの大幅な変更が必要になります。変更を最小限にとどめる方 as 22= 法はありますか。 Kさん:ISOLATIONレベルをREAD COMMITTEDに変更して,|」|の共有ロック がすぐに解放されるようにする案もあります。 L氏 :単純にREAD COMMITTEDに変更すると,引当済在庫数量が不正になってしまう おそれがあります。同じ倉庫の同じ部品に対して,"在庫引当"APが複数同 時に実行されるとき,それぞれの要求する出庫数量の合計が,| o| していると,引当済在庫数量が不正になってしまいます。1ISOLATIONレベル を変更しない方法はありますか。 Kさん:①のSELECT文に,|p|]する案があります。 〔索引の見直し〕 毎日の業務終了時に実行される"在庫反映"APの処理時間の短縮要望を受け,KS んが対策を検討した。その結果,出庫倉庫コードと部品番号の組合せの値の範囲で 分割し,分割した値の範囲それぞれを各プロセスに配分して並列実行するように変 更することにした。変更後の“在庫反映"APの処理の流れを図(CMT,ISOLATION レベルはREPEATABLE READで実行する。 せの値の昇順に,行ごとに次の2の処理を行う。 2.“出庫”テーブルを参照して,処理要否を判定する。処理状況が"出庫済の場合だけ次の 2-1~2-3を実行する。 2-1.“在庫テーブルの在庫数量を更新(出庫数量分を減算)する。 2-2.“在庫”テーブルの引当済在庫数量を更新(出庫数量分を減算)する。 2-3.“出庫'”テーブルの処理状況を"在庫反映済に更新する。 3.“出庫”テーブルの指定された範囲内の全ての行の処理が完了したら,COMMITする。 図4変更後の“在庫反映"APの処理の流れ これを確認したL KIS,索引の見直しを指示した。 L氏:"|q|"テーブルの[|r Joel。|列の組合せをこの順 で索引キーとする複数列素引を定義しておく必要があります。この索引を 定義していないと,自プロセスの対象行かどうかを判定するための参照が ー23一 表探索となります。その場合,他APが|t|した行を参照しようとし て| 』|が生じた結果,処理時間が長くなるおそれがあります。デッ ドロックが発生しないかの検証も必要です。 Kさん:処理要否の判定も,表の行の読込みを不要にして性能を向上させるために, “Tg|"テーブルの|v ]列もこの複数列素引の索引キーに 加えてもよいでしょうか。 LR:そのメリットと,そうすることで“| q』|”テーブルを更新するAPが, の索引の更新頻度の増加によって性能が低下するデメリットとのトレード オフを検証してください。