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

共通の文章

データベース設計に関する次の記述を読んで,設問1ー3に答えよ。 コピー機メーカの系列販売会社のX HC,販売管理システムのデータベース設 計を行っている。 【業務の概要] 1.地域 日本全国を幾つかの地域に分割し,地域コードで識別する。 2.顧客 (1)顧客(見込み客を含む)は,顧客コードで識別し,名称,住所,電話番号を 登録する。 Q)設置事業所(コピー機を設置する顧客の事業所)は,顧客コードと設置事業 所コードで識別し,名称,住所,その住所を基にした地域コードを登録する。 3.組織 (1)組織は,組織コードで識別する。 (2)組織には,コピー機を販売する営業所と,コピー機の設置と保守を行うサー ビスセンタ(以下,SCという)があり,組織区分で分類する。 3)顧客ごとに,担当営業所を決めている。担当営業所は,組織変更によって変 更する場合がある。 (4)地域ごとに,担当SCを決めている。 4.営業担当者 (1)営業担当者は,X社の社員であり,社員番号で識別する。 (2)営業担当者は,いずれか一つの営業所に所属する。所属する営業所は,人事 異動によって変更する場合がある。 5.商品 QQ)商品は,商品コードで識別する。 (2)商品には,製品と設置サービスがあり,商品区分で分類する。製品は,コピ ー機メーカの物流センタから出荷し,設置サービスは,X社のSCが提供する。 (3)製品には,単体製品とセット製品がある。 ①単体製品かセット製品かは,単体セット製品区分で分類する。 =iG= ②単体製品には,製品サイズを登録する。 (4)単体製品には,本体製品(コピー機本体)とオプション製品がある。 ①本体製品かオプション製品かは,製品区分で分類する。 ②本体製品には,製品のシリーズを表すシリーズコードを登録する。 ③オプション製品には,給紙オプション,排紙オプションなどがあり,オプシ ョン区分で分類する。 (5)セット製品は,X社が販売用に登録する。セット製品は,一つの本体製品と ーつ以上のオプション製品を組み合わせたもので,どのオプション製品で構成さ れるかについて,セット製品構成に登録する。 (6)設置サービスには複数種類があり,製品ごとに,どの設置サービスを適用す るかを決めている。設置サービスには標準設置時間を登録する。セット製品の場 合,セット製品自体で決まる設置サービスを適用し,セット製品を構成する単体 製品で決まる設置サービスは適用しない。 6.見積り (1)顧客から見積依頼があると,担当営業所の営業担当者が見積りを行う。 2)見積りは,見積番号で識別し,案件名,顧客コード,担当営業所の組織コー ド,営業担当者の社員番号,見積年月日,納期年月日,見積有効期限年月日,商 品コード,商品コードごとの数量,見積単価を登録する。 7.受注 GQ)成約に至ったときに,見積りと同じ単位で受注登録を行う。 (2)受注は,受注番号で識別し,該当する見積番号,受注年月日を登録する。 (3)受注明細は,設置の単位であり,本体製品1台単位,又はセット製品1セッ ト単位に作成し,設置事業所,設置場所詳細(設置する階・部屋の位置),設置 補足(設置に関する注意事項など)を登録する。(5)に後述する受注明細内訳番 号を登録する。 (4)受注明細内訳には,商品コードと数量を登録する。ただし,商品がセット製 品の場合,そのセット製品自体と,セット製品を構成する製品を展開した内訳を 登録する。 ①受注明細が本体製品1台単位の場合,単体製品である本体製品とオプショ ン製品,及び必要な設置サービスをそれぞれ登録する。 ーァー ②受注明細がセット製品1セット単位の場合,次のように登録する。 ・セット製品と,セット製品に必要な設置サービスをそれぞれ登録する。 ・セット製品を構成する単体製品に展開し,単体製品の商品コード,展開元受 注明細内訳番号(セット製品の商品コードを登録した受注明細内訳番号) を登録する。 (5)本体製品の受注明細内訳番号を受注明細に登録する。 [概念データモデルと関係スキーマの設計 [業務の概要)に基づいて設計した概念データモデルを図1に,関係スキーマを 図2に示す。 wo) ノヽ セット製品構成 し 』 ||し 図1概念データモデル(未完成) —問一 組織(組織コード,組織名,|。|) 営業所(営業所組織コード,||) サービスセンタ(SC組織コード,|。|) 地域(地域コード,地域名|bp]) 営業担当者(社員番号,社員氏名,|c|) we(顧客コード,顧客名,顧客住所,康客電話番号,|q』|) 設置事業所(|c|,設置事業所コード,設置事業所名設置事業所住所|i|) 商品(商品コード,商品名,商品区分,製品区分,オプション区分,単体セット製品区分 シリーズコード,製品サイズ,標準設置時間,設置サービス商品コード, ギット製品本体製品商品コード) セット製品構成(||) 見積(見積番号,見積年月日,見積有効期限年月日,案件名,納期年月日|g|, Ce.) 見積明細(見積番号,商品コード,数量,見積単価) 受注(受注番号,受注年月日,|」|) 受注明細(受注番号,受注明細番号,|k|,設置事業所コード,設置場所詳細, 設置補足,本体製品受注明細内訳番号) 受注明細内訳(受注番号,受注明細番号,受注明細内訳番号,商品コード,Bet, 展開移受注明細内訳番号) 注記網掛け部分は表示していない。 図2関係スキーマ(未完成) [出荷指示の追加】 販売管理システムに,次のように出荷指示の機能を追加することにした。 Q)SEB,営業所で次のように出荷指示ができるようにする。 ①出荷指示は,コピー機メーカの物流センタから出荷する製品を対象とする。 ②同じ設置事業所,同じタイミングで出荷できる場合は,受注明細をまとめて出 荷指示を行う。 (2)出荷指示は,出荷指示番号で識別し,出荷指示年月日を登録する。 解答に当たっては,巻頭の表記ルールに従うこと。エンティティタイプ間の対応関 係にゼロを含むか否かの表記は必要ない。 なお,関係スキーマの表記では,主キー及び外部キーを明記せよ。 ーー9ーー

共通の文章

データベースでの制約の実装に関する次の記述を読んで,設問ユー3に答えよ。 総合商社のY社は,人事情報管理にRDBMSを用いている。 (RDBMSの主な仕様] 人事情報管理データベースに用いているRDBMSの主な仕様は,次のとおりであ る。 1.参照制約 参照制約では,挙動モードと検査契機モードを指定できる。 (1)挙動モード 挙動モードには,次の二つがある。 ①NO ACTION:参照先のテーブルの行を削除又は更新したとき,参照元のテ ーブルの行に対して何もしない。 @CASCADE :参照逢のテーブルの行を削除又は更新したとき,参照元のテ ーブルの行にも削除又は更新を連鎖させる。 (2)検査契機モード 検査契機モードには,次の二つがある。 ①即時モード:SQL実行終了ごとに,対象となる全ての行の実行結果に対し て,制約を検査する。 ②MPE-E:トランザクション終了時に,トランザクション内の全てのSQL を実行した結果に対して,制約を検査する。 2.トリガ テーブルに対する変更操作(挿入・更新・削除)を契機に,あらかじめ定義さ れた処理を,操作対象の行ごとに実行する。実行タイミング(挿入・更新・削除 の前又は後),列値による実行条件を定義むすることができる。ただし,実行タイミ ングを挿入・更新・削除の前として定義したトリガの処理の中で,テーブルに対 する変更操作を行うことはできない。 〔人事情報管理データベースのテーブル〕 人事情報管理データベースの主なテーブルのテーブル構造は,図1のとおりであ ー12一 る。索引は,主キーだけに定義されている。 職位(職位コード,職位名) 従業員(従業員コード,氏名,生年月日,入社年月日,退職年月日,性別,部署コード, 職位コード,退職フラグ) 従業員家族(従業員コード,連番,続柄,家族氏名,生年月日,兵養フラグ) 注記年月日を示す列のデータ型は,DATE型とする。 Bl主なテーブルのテーブル構造 〔人事情報管理の業務概要〕 人事情報管理担当者は,業務上のイベント発生時にテーブルの更新を行うととも に,社内の各部署からの情報提供要求に対応するために,SQLで検索した結果をレ ポートにして提供している。業務上の主がイベントとテーブルの更新内容,及びレ ポート提供の一例は次のとおりである。 1.従業員の退職 満65歳での定年退職及び従業員の自己都合による退職が随時発生する。定年退 職の場合,退職年月日は,満65歳の誕生日の前日である。従業員を"従業員”テ ーブルに登録するときに,退職年月日に定年退職の年月日を設定する。自己都合 による退職の場合,退職年月日は申告された日に更新する。 退職すると,“HERA”テーブルの退職フラグを,あらかじめ設定している‘0’ (在籍)から1(退職)に更新する。この処理は,退職年月日の当日に行う。 給与計算は毎月20日締めで行う。また,毎月25日に,“HRA”テーブル及び “従業員家族”テーブルから,当月の20日までに退職した従業員の行を削除する。 削除した行を別テーブルに保存するために,実行タイミングを“従業員"テーブ ルの削除の後としたトリガを定義している。トリガに定義している処理は,次の とおりである。 ・削除した“HEB”テーブルの行を別テーブルに挿入する。 ・“従業員家族”テーブルのその従業員の家族の行について,別テーブルに挿入 し,その後削除する。 毎月25日に行を削除するための,三つの述語から成るSQLの構文は表1のとお ー13一 りである。 表1退職した従業員の行を削除するSOLの構文(未完成) —o SQL1|AND((DAYELEMENT(退職年月日)<[>|) OR(MONTHELEMENT(退職年月日)<MONTHELEMENT())) 注記1 DAYELEMENT関数は,指定されたDATE型の年月日の日の部分を数値として抽出する ユーザ定義関数とする。 注記2 MONTHELEMENT関数は,指定されたDATE型の年月日の年月の部分を数値として抽 出するユーザ定義関数とする。 2.従業員家族構成の変更 従業員ごとの事由によって,家族の増減,扶養対象者数の変更などが随時発生 する。このとき,“従業員家族”テーブルへの行挿入,主キー以外の列値の更新 |(扶養フラグは,扶養対象者でない場合は"0',扶養対象者の場合は'1'に更 新),行削除を行う。 3.定期的な組織変更及び人事異動 毎年4月1日及び10月1日に組織変更及び人事異動を実施している。 組織変更では,部署の新設,廃止が発生する。このとき,“部署"テーブルに対 する行挿入,行削除を行う。 人事異動で,従業員が所属する部署が変更になった場合,“従業員"テーブルの 部署コードの更新を行う。 4.レポート提供の一例 福利厚生担当者から人事情報管理担当者に対して,従業員家族向けのレクリエ ーション企画のために,部署コード順に2007年1月1日以降に生まれた扶養対象 者をもつづ従業員数と扶養対象者数の一覧表が欲しいとの依頼があった。その例を 図2に示す。 人事情報管理担当者は,表2のSQL文を用いて,図2の一覧表を作成した。 ~it— K*部署別扶養対象者数(2007年1月1日以降生まれ)一覧** 部署コード従業員数扶養対象者数 図2福利厚生担当者から求められた一覧表の例 表2一覧表を作成するSQLの構文(未完成) SQLの構文 SELECT|』|,COUNT([<|従業員家族.従業員コード)AS従業員数, COUNT(*)AS扶養対象者数 FROM従業員,従業員家族 WHERE従業員.従業員コード=従業員家族.従業員コード SQL2|AND従業員家族扶養フラグ='1 AND従業員家族.生年月日>>TO_DATE(’2007-01-01’) GROUP BY|ai Loa] 注記TO DATE関数は,指定された文字型の年月日をDATE型に変換するユーザ定義関数とす る。 [人事情報管理データベースで発生している問題点] 定型的な業務上のイベントはアプリケーションプログラムで実装している。しか し,組織変更及び人事異動のイベントで,部署コードなどの更新する列の内容が変 動するものは,次のような運用を行っている。 ・人事情報管理担当者が,イベント別に用意してある更新SQL文のバッチジョブ を利用して,業務上のイベントに対応したテーブルの更新を実施する。 ・人事情報管理担当者はイベントに応じてバッチジョブへの入力データを作成し ている。 現状の問題点として,入力データの作成ミスによって,実際に存在しないコード でテーブルを更新することがあり,給与計算システムでトラブルが起きている。こ の問題点を解決するために,RDBMSの参照制約機能を利用することにした。参照制 約機能の利用案を表3に示す。 表3参照制約機能の利用案 AEF we coms—~)LP— “ _ mae(REF)Byモード 従業員家族ER cae(従業員コー|UP——_ ERR[HORA GRAS|uppaTE|NoacrioN| D 部署saa oa(従業員コUPDATE|NO ACTION即時モード 管理者従業|RA(HERAT|UPDATE|NO ACTION| 見コード| 注"参照先には,参照先のテーブル名(参照先の列名)を示す。 参照制約機能の利用の検討 参照制約機能を利用する以前には,定期的な組織変更及び人事異動に対応する処 理は,図3に示すように"部署"テーブルの行を更新した後,“従業員"テーブルの 行を更新していた。 参照制約機能を利用するに当たって,図3中の①ゆて⑥の更新手順の変更,KOM 理時間の検討を行った。 ①“部署”テーブルから不要な行を削除する。 @コミットする。 ⑬“部署"テーブルに対して,新規の行を挿入する。 @コミットする。 ⑥“tA”テーブルの部署コードを更新する。 ⑥コミットする。 図3参照制約機能を利用する以前の更新手順

共通の文章

物理データベースの設計及び実装に関する次の記述を読んで,設問1.2に答えよ。 A社は,都市近郊で複数の日帰り温泉施設(以下,施設という)を運営している。 A社では,リピータを増やすために,全施設で利用できる会員カードを希望する客に FTL,新たにシニア向けの割安な利用料金を設定することにした。また,この計 画に伴いシステムを拡張することが決まり,Fさんが物理データベースの設計及び実 装(以下,物理DB設計及び実装という)を担当することになった。 (RDBMSの主な仕様] (1)テーブル及び索引のストレージ上の物理的な格納場所を,表領域という。 (2)RDBMSとストレージ間の入出力単位を,ページという。同じページに異なるテ ーブルの行が格納されることはない。 (3)索引は,ユニーク索引と非ユニーク索引に分けられる。 (4)allt,クラスタ性という性質によって,高クラスタな索引と低クラスタな秋 引に分けられる。 ・高クラスタな索引:キー値の順番と,キーが指す行の物理的な並び順が一致し ているか,完全に一致していなくても,隣接するキーが指す行が同じページに 格納されている割合が高い。 ・低クラスタな索引:キー値の順番と,キーが指す行の物理的な並び順が一致し ている割合が低く,行へのアクセスがランダムになる。 (5)DMLのアクセスパスは,RDBMSによってテーブル及び索引に関する統計情報 に基づいて索引探索又は表探索に決められる。ただし,次の場合は除く。 ・WHERE 4J®ANDだけで結ばれた等値比較の対象列がユニーク索引のキー列に 一致している場合:統計情報にかかわらず,その索引の索引探索に決められる。 ・統計情報からテーブルが空(0行)と判断した場合:表探索に決められる。 (6)主キー制約及びUNIQUE制約は,制約を定義する列の構成に一致したユニーク 索引がRDBMSによって自動的に作成されることで一意性が保証される。 〔施設運営及び会員カードの概要〕 1.施設運営 ー18一 (1)営業時間帯は,9:00~24:00 CHS. (2)各施設には,LAT,BA,食事処,売店など,一つ以上の店舗がある。 (3)受付では,客が一人1枚ずつ入館券を購入し,入館券ごとに腕輸付きロッカ ー鍵(以下,鍵という)を一受け取り,帰るときに返却する。 (4)客は,鍵のバーコードに記録されている鍵番号を店舗のレジに読み取らせる ことで,店舗の有料サービスを含む商品を利用できる。レジに記録されたデー タは,客が精算するまでにシステムのデータベースに送られる。 (5)未精算の利用額は,退館時に複数台のいずれかの精算機で精算する。 6)同じ客が同じ日に,同じ施設を複数回,利用することができる。この場合, 入館券を再度,購入する必要がある。 2.会員カード (1)客は,会員カード申込書(以下,申込書という)に必要な情報(氏名,生年 月日など)を記入する。未就学児の入館は無料なので申込対象外である。 (2)客は,会員IDが刻印された会員カード(申込書に添付)を受け取り,その日 から利用することができる。会員には,鍵を用いた利用の額に応じてポイント を付与する。ポイントは,次回以降の施設利用に使うことができる。 (3)システムは,翌朝の開館までに申込書に基づいて会員情報を登録し,また, 毎日の営業時間外に,会員の生年月日に基づいて年齢及び年齢区分を設定する。 (4)登録の翌日以降,客は,会員カードを提示して,年齢に応じた入館券を購入 する。その際,年齢を示す証明書の提示を求められることはない。 〔物理DB設計及び実装] 1.物理DB設計及び実装の前提 Q)テーブル構造は,既に決まっている。 (2)テーブルごとの行数は,過去の実績及び伸び率の予想に基づいて見積もる。 (3)利用実績データは,分析のために1年分を保存する。 (4)システムで用いられるユーザ,ロールは,定義済みである。 (5)プログラム開発者は,DMLを設計し,プログラムの開発・単体テストを行う。 (6)allt,プログラム開発者が設計したDMLに基づいてFさんが設計を行う。 2.主なテーブル構造及び主な列の意味と制約 ー19一 主なテーブル構造を図1に,主な列の意味・制約を表1に示す。また,“AB” テーブルの年齢区分と年齢の組合せを限定する検査制約を,図2に示す。 施設(施設ID,施設名,住所,代表電話番号,…) 店舗(施設ID,店舗D,店舗名,内線番号,…) 商品(商品D,商品名,価格,ポイント数,商品説明,…) Se(会員ID,氏名,YER,年齢区分,年齢,生年月日住所,ポイント繰越数。…) 券(利用年月日,施設ID,券番号,入館時刻,年齢区分,入館料,SB ID) gt(施設ID,HES,SES,使用中フラグ) 店舗利用(利用時刻,施設1D,店舗ID,BEES,商品ID,利用額,ポイント数, 未精算フラグ)rf 精算(利用年月日施設ID,券番号,精算時刻,利用額合計,SAID,ポイント消費数)5 精算明細(利用年月日,利用時刻,施設D,券番号,店舗ID,商品ID,利用額,ポイント数) 注記年齢列のデータ型はSMALLINT型,商品説明列及び住所列はVARCHAR型である。 図1主なテーブル構造(一部省略) 表1主な列の意味・制約 施設内の各店舗(エステ,理容,食事処,売店など)を識別する文字列 各店舗に設置されている内線電話を施設内で識別する番号 全店舗で提供される商品を識別する文字列 会員を識別する文字列(会員カードの裏面にバーコードで刻印されている) 年齢区分ne a歳未満):1',KA(12歳以上60 mA):‘2’,シニア(60歳以 He a。fy?ヽ\-9?PF set bs 入館券を発行する都度,日ごと施設ごとに付与される1から始まる連番 施設内のロッカーを識別する番号。客の精算後鍵は再利用される。 使用中フラグ|鍵が使用中の場合:'Y,鍵が使用中でないり場合:"N' 利用額が未精算の場合:'Y',精算済みの場合:N gem(grpしにゃ>mw(しょleにLe)) OR(年齢区分='3'AD|q|)) 図2年齢区分と年齢の組合せを限定する検査制約(未完成) 3物理DB設計及び実装の作業工程表 Fさんが作成中の物理DB設計及び実装の作業工程表を,表2に示す。 表2物理DB設計及び実装の作業工程表(未完成) 作業|作業開 作業名作業内容 Toテーブル名,WH,列データ型,主キー制約及びNOT is—NULL制約を決めて,CREATE TABLE文を設計する。 追加制約設計|を追加するALTER TABLE文を設計する。 のwus UNIQUE制約,検査制約及び参照制約を決めて,これらの制 アクセス権限設計|GRANT KE BETS, eve,|ユーザごと又はロールごとにテーブルのアクセス権限を決め 4 wa|3eusea1 seat Dis WT eet Ae定し,性能向上のために追加索引 パスを想定し,性能店とめに追加索 を設計する。 問間隊テーブル及び索引のストレージ所要量を見積もり,表領域を 人)ストレージに割り当てる設計を行う。 |6|We|DML性能予洋|DMLの結果行数及び読込みページ数を机上で予測する。 など実f —表領域をストレージに割り当て,CREATE TABLE X, 人 We統計情報取得及び|テーブル及び索引に関する統計情報を取得し,DMLのアク アクセスパス確認|セスパスが想定どおりかどうか確認する。 wo|任能測定用データ|性能測定用データを生成するための設計書を作成し,必要な 設計・データ生成|プログラムの開発後に性能測定用データを生成する。リ テーブルに性能測定用データをロードする。 DMLの性能を測定し,目標を達成するかどうか確認する。 注り“店舗利用"”テーブルについては,1日分の性能測定用データを生成する。 (1)作業W4(追加索引設計)で,利用額の精算時に実行されるDMLの例を表3 IZ,FさんがDML2及びDML3のために追加した索引を表4に示す。 表3利用額の精算時に実行されるDMLの例 SELECT券番号FROM鍵WHERE施設ID=:施設D AND鍵番号=:鍵番号 DML2|INSERT INTO精算明細(ees|)SELECT:利用年月日,利用時刻,施設ID, :券番号,店舗ID,商品ID,利用額,ポイント数FROM店舗利用 WHERE施設ID=:施設ID AND鍵番号=:鍵番号AN未精算フラグ='Y' DML3|SELECT|eee FROM精算明細WHERE利用年月日=:利用年月日AND 施設ID=:施設ID AND券番号=:券番号 注記1網掛け部分は表示していない。 注記2ホスト変数の利用年月日には,当日の現在日付が設定される。 表4 DML2及びDML3のために追加した索引 店舗利用|施設ID,MES,未精算フラグ 精算明細|利用年月日,施設ID,券番号 (2)作業W5(表領域設計)で,可変長列があるテーブルのストレージ所要量を見 積もる計算の手順を,表5に示す。また,作業WE(DML性能予測)で,DML 性能の指標としてDMLのテーブルからの読込みページ数を,表5の見積結果を 用いて予測する計算の手順を,表6に示す。 表5可変長列があるテーブルのストレージ所要量を見積もる計算の手順(未完成) 手順の内容 テーブルの見積行数,PGE(バイト)及び最大行長(バイト)を得る。 。|RDBMSで指定できるページ長(バイト)の中から,|ア ]の行を1行以上格納で きるページ長を決め,ページ長からヘッダ部を除いた有効ページ長(バイト)を得る。 テーブルに行をロードした後,ページ当たり1行以上挿入したい場合,次の不等式を満 3|たす適切な空き領域率(%)を決める。 |平均行長ミ[|イ ]*空き領域率=100 次の計算式によって,テーブルのストレージ所要量(バイト)を求める。 ページ当たりの平均行数=+の小数点以下を切捨て *|必要ページ数=+の小数点以下を切上げ テーブルのストレージ所要量[|ヵ|x 表6テーブルからの読込みページ数を予測する計算の手順(未完成) 手順の内容 t DMLのアクセスパスを想定し,そのアクセスパスを用いてページから読み込む探索行数 を予測する。結果行数探索行数とは限らない。 -アクセスパスが表探索の場合:読込みページ数|ク] ・アクセスパスが守引探索の場合,かつ, 。索引が低クラスタな索引の場合:最大読込みページ数=[ヶケ|…A 索引が高クラスタな索引の場合: 最小読込みページ数=[ヶ|の小数点以下を切上げ…B ・アクセスパスが索引探索の場合,読込みページ数はAとBの間の値になる。 【G部長の指摘 表2の作業工程表について,Fさんは,表に示した作業順で逐次,各作業を行うつ もりであったが,G部長から次のような指摘があった。 指摘1作業を並行して進めることで,作業工程期間を短縮できる。 指摘2作業W8(統計情報取得及びアクセスパス確認)の作業順が8番目では, 表3のDMLのアクセスパスが適切に決められない。作業W8を,作業 |あ|の後に行うべきである。