データベーススペシャリスト 2017年 春期 午後Iの問題文8問です。図・表・別冊資料と正解は掲載していませんので、出典の公表資料またはアプリでご確認ください。
データベースの設計に関する次の記述を読んで,設問1ユー3に答えよ。 D社は,グループウェア(以下,GWという)を主力商品とするソフトウェア開発 会社である。D社では現在,RIO GWを開発しており,S君がデータベースの設計 を行っている。 (GWの主な機能] 1.利用者管理機能 GWでは,ユーザ,グループなどを用いてGWの利用者の情報を管理する。 (1)ユーザとは,GW上の利用者である。GWの利用者は,GW上でユーザ登録を 行い,ユーザID及びパスワードを使用してGWにログインし,GWの各機能を 利用する。 (2)グループとは,GW上の組織である。例えば,営業部,経理部などである。 グループには,上位のグループを一定めることができる。 (3)ロールとは,GW上の役割である。例えば,経理担当者,経理責任者などで ある。ロールは,ロールIDで一意に識別し,ロール名をもつ。 (4)ユーザは,一つのグループに必ず所属し,これを主務グループと呼ぶ、ユー ザは,一つ又は複数のグループに兼務として所属することができる。また,ユー ザには,必要に応じて一つ又は複数のロールを付与でき,一つのロールを複数の ユーザに付与することもできる。 なお,上位のグループの中には,ユーザが一人も所属しないグループが存在す る。 2.予約機能 GWでは,スケジュール予約及び設備予約を行うことができる。例えば,打合せ を行う場合に,出席者のスケジュール予約と会議室の設備予約を行うことができる。 (1)スケジュール予約とは,ユーザ自身又は他のユーザのスケジュールを予約す る機能である。スケジュールを予約されたユーザは,そのスケジュールに参加す るか否かを回答することができる。 (2)設備予約とは,会議室,プロジェクタなど,あらかじめGWに登録された設 備を予約する機能である。設備には,必要に応じて,当該設備の管理を行うグル =a= ープを一つ定めることができる。 (3)スケジュール予約及び設備予約は,それぞれを同時に予約することも,いず れか一方を予約することもできる。 3.コミュニケーション機能 GWには,ユーザ間で直接メッセージをやり取りするメッセージ機能,及び特定 のテーマに関してユーザ同士で議論できる電子会議機能が備えられている。 (1)ユーザは,1人又は複数のユーザにメッセージを送信することができる。送信 先のユーザがメッセージを開封すると,開封日時が記録される。 (2)電子会議とは,GW上の会議の単位である。電子会議には,例えば“プロジ ェクタの利用について"などの議題が定められる。ユーザは,新たな電子会議を 作成することができる。 (3)投稿とは,ユーザが電子会議上に文章を書き込むことである。 (4)分野とは,電子会議を分類する単位である。例えば,総務,営業などである。 電子会議は,いずれかーーつの分野に属し,分野ごとに定められた表示順に従って 一覧表示される。 4.ワークフロー機能 GWには,簡易なおワークフロー機能があり,申請及び承認の流れを定義し,定型 業務として利用できる。 (1)申請ひな形とは,各種申請のテンプレートである。例えば,経費申請,交通 費申請などの種類がある。 (2)決裁ルートとは,申請ひな形ごとに定められた,申請を処理する承認経路で あり,一つの以上のステップによって構成される。 3)ステップには,承認可能なユーザ,グループ又はロールを指定する。ユーザ, グループ又はロールのいずれで指定されているかは,承認者区分で識別する。 (4)ユーザは,申請ひな形を指定して各種申請を行うことができる。申請を行う と,決裁ルートの最初のステップに進む。 (5)決裁ルートの各ステップに指定されている承認者は,自身が処理すべき申請 に対して,承認処理として次のいずれかの処理を行う。 ‘KB:最後のステップでは,申請状態を決裁済にする。それ以外のステッ プでは,次のステップに進める。 ーッァー ・差戻し:一つ前のステップに戻す。ただし,最初のステップでは,差戻しが できない。 Bi:申請状態を否認済にする。 (6)申請を行ったユーザは,申請中の申請を取り消すことができる。取消しを行 うと,申請状態は取消済となる。 (7)承認処理を行うと,その都度処理内容がデータベースに新規登録される。 なお,ステップの承認者をグループ又はロールで指定している場合,そのステ ップで複数のユーザが同時に承認処理を行うことはできない。 ワークフロー機能の決哉ルートの例を図1に,承認画面の例を図2に示す。 申請「ステッ-テッステップョ|me 2承認承認_ 申請者ーーウタブレーコステップ2|ニーーンタパトーー 承認者A承認者BーーにいらBY,3 H差戻し差戻し決裁済 Lend OWE>(RIL ee[取消し LB 1 i_ 承認者の処理eeーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーートーーーーーーーーーーーーーつ6 最終申請状態:@取消済 図1決裁ルートの例 交通費申請申請連番:123456申請者:鈴木一郎申請日時:2017-03-21 16:18 申請内容:OORRPAAER 195円 〇Aビルでの先方との打合せ(2017年3月21日) 承認状況: 承認処理結果 山田大朗 承認者:高橋三郎ステップ番号:3 コメント:|承認します| 図2承認画面の例 —8— 〔データモデルの設計) S君は,概念データモデル(図3)及び関係スキーマ(図4)の設計を行った。 (のQ) SY 21h fits=Uh兼務メッセージ 設備予約先予約先グループ送信先 ベの 図3 S君が設計した概念データモデル(未完成) グループ(グループID,グループ名,上位グループIW) ユーザ(ユーザTD,氏名,メールアドレス,パスワード,[a_]) 兼務グループ(ユーザID,グループWD) 設備(設備D,設備名,|b]) メッセージ(メッセージID,メッセージ本文,| c ],BA) メッセージ送信先(メッセージD,|q』|,|es|) 電子会議投稿(電子会議番号,議題,分野番号,分野名,表示順,作成者ユーザpw, 投稿番号,投稿本文」投稿者ユーザDD) 予約(予約番号,予約開始日時,予約終了日時,予約内容,予約者ユーザTD) 設備予約先(予約番号,設備ID) スケジュール予約先(予約番号,予約先テコーザD,|1|) 申請ひな形(申請ひな形番号,申請種類) 決裁ルート(申請ひな形番号,ステップ番号,承認者区分,承認ユーザID,承認グループD) 申請(申請ひな形番号,申請連番,申請内容,申請者ユーザID,申請日時,申請状態 取消日時) 承認(申請ひな形番号,申請連番,ステップ番号,承認連番,承認処理結果,コメント, 承認者ユーザD,承認日時) 図4$S君が設計した関係スキーマ(未完成) —9—_ 図4の関係スキーマの主な属性とその意味・制約を,表1に示す。 表1主な属性とその意味・制約 複することはない。表示順の見直しによって値が変更されることがある。 ルートにおける承認順序を定義する番号 に1ずつ加算される番号 ーー 承認者ユーザIDール指定の場合は,実際に承認処理を行ったユーザのユーザIDが記録さ れる。 (T部長の指摘事項] S君の上司である部長は,S君が設計した成果物を確認し,次の事項を指摘した。 指摘事項①:ロールを管理するデータ構造が設計されていないので,ロールを用い て承認者を指定することができない。 指摘事項②:承認処理を行う際に,不具合が発生するおそれがある。 ー10一
設問1関係“電子会議投稿"について,(1),②⑫)に答えよ。 (1)関係“電子会議投稿"の候補キーを全て答えよ。また,部分関数従属性, 推移的関数従属性の有無を,“HU”又は“なし"で答えよ。“HV”の場合は, 次の表記法に従って,その関数従属性の具体例を一つ示せ。 なお,候補キー及び表記法に示されている属性1,属性3,属性4が複数の 属性から構成される場合は,{)でくくること。 (2)関係“電子会議投稿"は,第1 TERUG,第2正規形,第3正規形のうち, どこまで正規化されているか答えよ。また,第3正規形でない場合は,第3 正規形に分解し,主キー及び外部キーを明記した関係スキーマを示せ。
設問2 M3,4及び表1について,(1),⑫)に答えよ。 a図4中の|』]~[|f|]に入れる適切な属性名を答えよ。また, 主キーを構成する属性の場合は実線の下線を,外部キーを構成する属性の場合 は破線の下線を付けること。 Q)図3のエンティティタイプ間のリレーションシップを全て記入せよ。また。, リレーションシップには,エンティティタイプ間の対応関係にゼロを含むか否 かの表記(““O”Rik“@")も記入すること。 なお,図3に表示されていないエンティイィティタイプは考慮しなくてよい。
設問3[部長の指摘事項}]について,①,(②)に答えよ。 (1)指摘事項のに対応するために,新たな関係を二つ追加し,既存の関係に属 性を一つ追加することにした。新たに追加する関係の主キー及び外部キーを明 記した関係スキーマ,属性を追加する関係名及び追加する属性名を答えよ。 (2)指摘事項②の不具合はどのようなときに発生するか。その状況を,具体的 に40字以内で述べよ。また,不具合に対応するために,関係を一つ修正する ことにした。修正後の関係の主キー及び外部キーを明記した関係スキーマを答 えよ。 なお,修正後の関係スキーマは,第3正規形の条件を満たしていること。 =ye
トランザクションの排他制御に関する次の記述を読んで,設問1ー3に答えよ。 Y社は,オフィスじゅう器メーカである。現在,在庫管理システムのアプリケーシ ョンプログラム(以下,APという)の改修を実施している。 〔在庫管理システムのテーブル〕 在庫管理システムの主なテーブル構造を,図1に示す。各テーブルには主索引が 定義されている。 ee 組立工場(工場コード,工場名,所在地,BeBe—k) 部品(部品番号,部品名,部品単価) 在庫(倉庫コード,部品番号,倉庫内在庫数量,出庫対象在庫数量) 定期便(RES,発送年月日,発送元倉庫コード,発送先食庫コード) 出庫(出庫番号,出庫年月日,出庫元倉庫コード,出庫先倉庫コード,出庫先工場コード, 部品番号,出庫数量,出庫便番号,処理状況) 図1主なテーブル構造 〔在庫管理業務の概要] (1)各地の生産拠点には,組立工場と,これに隣接する倉庫がそれぞれ一つ配置さ れている。 (2)倉庫からの部品の出庫には,倉庫から隣接する組立工場に出庫する場合と,倉 庫から他の生産拠点の倉庫に出庫する場合がある。 (3)倉庫は,倉庫コードで一意に識別され,組立工場は,工場コードで一意に識別 される。生産拠点を識別するコードは存在しない。 (4)定期便は,倉庫間で部品を配送する便であり,便番号で一意に識別される。 (5)部品は,部品番号で一意に識別される。 (6)部品の在庫は,倉庫と部品の組合せで,その数量をもつ。倉庫内に存在する在 庫を,倉庫内在庫と呼ぶ。このうち,隣接する組立工場又は他の生産拠点の倉庫に 向けて出庫対象となったものを,出庫対象在庫と呼ぶ。 (7)出庫要求とは,倉庫に対して部品の出庫を要求することである。“出庫”テーブ ルに出庫要求の内容が登録され,処理状況に"要求発生"が記録される。出庫番号 ー12一 は,出庫要求の発生順の一意な連番である。組立工場が出庫要求する場合,出庫先 倉庫コード及び出庫便番号の値はNULLとなり,出庫先工場コードが記録される。 他の生産拠点の倉庫が出庫要求する場合,出庫先工場コードはNULLとなり,出 庫先倉庫コードが記録され,出庫便番号には該当する定期便の便番号が記録される。 (8)在庫引当とは,出庫要求に応じて倉庫内の在庫を引き当てることである。在庫 引当APは,毎日の業務中に定期的に実行され,その時点で登録されている出庫要 求を処理する。指定された倉庫コード,部品番号,出庫数量の出庫が可能かどうか チェックし,出庫可能であれば出庫対象在庫数量を更新する。在庫引当が完了した ら,処理状況は"引当実施"に更新される。 (9)出庫とは,出庫要求に従って,倉庫から部品を出すことである。出庫は多頻度 で行われるので,出庫ごとに在庫は更新されず,出庫確定APでまとめて更新され る。 (10)出庫確定APは,毎日の業務終了時に実行される。“出庫”テーブルの処理状況 が引当実施のものを対象に,倉庫内在庫数量及び出庫対象在庫数量が更新され, 処理が完了したら,処理状況は"出庫実施'に更新される。 (11)入庫とは,他の生産拠点の倉庫で出庫された部品を倉庫に入れることである。 入庫による在庫の更新は,在庫引当AP及び出庫確定APと同時に実行されること はない。 (RDBMSの排他制御 (1)在庫管理システムのRDBMSで選択できるトランザクションのISOLATIONレベ ルとその排他制御は,表1のとおりである。 ロックは行単位で掛ける。共有ロックが掛かっている間,他のトランザクション からの対象行の参照は可能であり,更新は共有ロックの解放待ちとなる。専有ロッ クが掛かっている間,他のトランザクションからの対象行の参照,更新は専有ロッ クの解放待ちとなる。 =Ba 表1トランザクションのISOLATIONレベルとその排他制御 排他制御 データ参照時に共有ロックを掛け,参照終了時に解放する。 データ参照時に共有ロックを掛け,トランザクション終了時に解放する。 (2)索引を使わずに,テーブルスキャンで全ての行に順次アクセスする場合,検索 条件に合致するか否かにかかわらず全行がロック対象となる。索引スキャンの場合, 索引から読み込んだ行だけがロック対象となる。 分析機能の追加] 適切な生産計画を立てるために,部品ごとに在庫数量,出庫数量の日別の推移状況 を見たいという要望があり,そのための集計APを追加した。集計APで実行する SQLの一部を,表2に示す。SQL1は各部品の出庫年月日ごとの出庫数量を集計する。 また,SQL1では,出庫が全くない部品も集計対象とする。SQL2は,各部品の倉庫 _聞の出庫について,出庫年月日,出庫元倉庫,出庫先倉庫ごとに出庫数量を集計する。 表2集計APで実行するSOLの構文(未完成) SQLの構文 SELECT B.部品番号,$.出庫年月日|。| FROM在庫ASB|b|HiME AS S SQL1|ON B.倉庫コード=S.出庫元倉庫コードAN B.部品番号=S.部品番号AND S.出庫年月日>>’2016-04-01’AND S.出庫年月日く72017-94-91' SELECT部品番号,出庫年月日,出庫元倉庫コード,出庫先倉庫コード,SUM(出庫数量) gao出庫WHERE[q]is|<] FROM WHERE 1S AND 出庫年月日>='2016-94-91'′AND出庫年月日’2017-04-01’ GROUP BY部品番号,出庫年月日,出庫元倉庫コード,出庫先倉庫コード [在庫引当APの改修] 在庫管理システムでは,トランザクションのISOLATIONレベルをREPEATABLE READとして設計,運用していた。システムの改修に当たり,在庫引当APのトラン ザクションのISOLATIONレベルをREAD COMMITTEDに変更することにした。 ISOLATIONレベルの変更で問題が発生しないように在庫引当APを改修した。 改修前の在庫引当APは図2,改修後の在庫引当APは図3のとおりである。これ らのAPの実行に先立って,“出庫”テーブルの処理状況が"要求発生'の行を抽出 し,出庫先倉庫ごとに分割したファイルを作成する。それぞれのファイルのレコード は,出庫番号順に記録されている。作成したファイルを入力として,在庫引当APを 並列実行している。在庫引当APは,入力ファイルのレコードごとに繰り返し実行さ れる。 なお,2,3中のホスト変数hv0は出庫番号,hv1は出庫元倉庫コード,hv2は 部品番号,hv3は出庫数量を表す。hv4とhv5は,検索結果を返す出力ホスト変数を 表す。 入力ファイルからhv9,hv1,hv2,hv3の値を設定する。 ①SELECT倉庫内在庫数量,出庫対象在庫数量NTO:hv4,:hv5 FROM在庫 WHERE倉庫コード=:hv1 AND部品番号=:hv2 hv4-hv5とhv3を比較し,出庫が可能な場合だけ以降を実行する。 ②UPDATE在庫SET出庫対象在庫数量=出庫対象在庫数量+:hv3 WHERE倉庫コード=:hv1 AND部品番号=:hv2 @UPDATE出庫SET処理状況='引当実施'WHERE出庫番号=:hv9 ④COMMIT 図2 REPEATABLE READで実行していた改修前の在庫引当AP 入力ファイルからhv9,hv1,hv2,hv3の値を設定する。 DECLARE在庫カーソルCURSOR FOR SELECT倉庫内在庫数量,出庫対象在庫数量 FROM在庫WHERE倉庫コード=:hv1 AND部品番号=:hv2 FOR UPDATE OPEN在庫カーソル FETCH在庫カーソルINTO:hv4,:hv5 hv4-hv5とhv3を比較し,出庫が可能な場合だけ以降を実行する。 UPDATE在庫SET出庫対象在庫数量=出庫対象在庫数量+:hv3 WHERE OF在庫カーソル CLOSE在庫カーソル UPDATE出庫SET処理状況='引当実施'WHERE出庫番号=:hv9 COMMIT 注記在庫カーソルにFOR UPDATEを指定した場合,FETCHされた行に専有ロックが掛かる。 B43 READ COMMITTEDで実行する改修後の在庫引当AP(未完成) ー15一 [出庫確定APの改修 出庫確定APの処理に掛かる時間を短縮するために,出庫確定APを並列に多重プ ロセスで実行するように変更することにした。“出庫”テーブルの出庫番号の値の範 囲指定で各プロセスに均等に配分して,REPEATABLE READで並列実行する。出庫 確定APの概要は,図4のとおりである。 oe 2.“出庫”テーブルを参照して,処理要否を判定する。処理状況が"引当実施の場合だけ 3.“出庫”テーブルの指定された範囲内の全ての行の処理が完了したら,COMMITする。 次の2の処理を行う。 次の2-1~2-3を実行する。 2-1.“在庫”テーブルの倉庫内在庫数量を更新(出庫数量分を減算)する。 2-2.“在庫”テーブルの出庫対象在庫数量を更新(出庫数量分を減算)する。 2-3.“出庫”テーブルの処理状況を"出庫実施'に更新する。 図4出庫確定APの概要
設問1分析機能の追加について,表2中の|a|~|ec|に入れる 適切な字旬を答えよ。
設問2[在庫引当APの改修)】について,(①⑪~て(3)に答えよ。 (1)図2の改修前の在庫引当APが,REPEATABLE READで複数同時に実行さ れるとデッドロックが発生するおそれがある。どのような場合にデッドロック が発生するか,AP HO SQLの実行状況を,図2中の丸数字を用いて60 FLA 内で述べよ。 (2)図2の改修前の在庫引当APが,READ COMMITTEDで同じ倉庫の同じ部 品に対して複数同時に実行されると,在庫数量が不正になるおそれがある。在 庫数量が不正になるAPの実行状況を図5に示す。不正になるのは,AP2の① て③④の各SQLが,t2,t4,t6,t8のどの時間帯で実行された場合か,該当する 時間帯にDー④を記入せよ。ここで,一つの時間帯に複数のSQLを実行でき る。 また,この状況が発生した場合の,在庫数量が不正とは具体的にどのよう な状態か,30字以内で述べよ。 ー16一 ||ag|—"fe|4w|6|6||s| = 注記APL:先行しているAP AP2:問題を引き起こすAP CREA) t1~t8:時間帯を示す記号。t1からt8の向きに時間が流れる。 O~@:図2中のSQL ERT ABE 図5在庫数量が不正になるAPの実行状況 @)図3中の[|fj|]に入れる適切な字句を答えよ。
設問3[出庫確定APの改修]について,(①②)に答えよ。 Q①)並列に実行するように変更したが,スループットはさほど向上しなかった。 ボトルネックはどこにあるかを説明する次の記述について,|アァ|~ |エ|に入れる適切な字名を答えよ。 複数の出庫を並列に処理することになるが,同じ[ア|]と|ィ| に対する出庫が複数存在するので,“"テーブルの更新で [=|が発生する。 2)(①のボトルネックを解消するためには出庫確定APをどのように変更する 必要があるかを説明する次の記述について,|オ|~|ヶJen れる適切な字句を答えよ。 出庫番号ではなく,との組合せの値の範囲指定で各 プロセスに配分するように変更する。また,図4の1で,出庫番号の昇順で はなく,との昇順に処理を行うように変更する。 "しキー|]テーテルの[オォ|we“a”az,ow 列素引を定義しておく。 なお,この引を定義していない場合,自プロセスの対象行かを判定する ための参照が[|ク|]となるので,他プロセスが[|ヶケ|した行を 照しようとしてロックの解放待ちとなり,別のボトルネックが生じる。 ーィター
テーブル及びSQLの設計に関する次の記述を読んで,設問1,2に答えよ。 A社は,全国の主要都市に家電販売チェーン店を展開している。A社では, RDBMSの機能を用いて販売分析支援システム(以下,システムという)を運用して BV,Fさんがテーブル及びSQLの設計を見直すことになっつた。 〔業務の概要〕 1)店舗は,営業本部の下で全国展開され,店舗コードで識別される。 (2)店舗で販売を担当する社員は,いずれか一つの店舗に配属され,社員IDで識別 される。各店舗には複数の社員が配属される。 (3)商品は,商品コードで識別される。 [システムの概要〕 1.主なテーブル構造 主なテーブル構造を,図1に示す。ここで,テーブルの行は追加された順に並 び,同じページに異なるテーブルの行が格納されることはない。また,索引のキー 順に,ページ単位で順次又はランダムに磁気ディスク装置AE,ディスクという) からバッフアァに読み込まれる。 Saeee 商品(商品コード,商品名,商品分類コード,…) 社員配属先(社員ID,社員名,店舗コード,…) 月別売上(売上年月,店舗コード,商品コード,売上額,販売数,…) 社員連絡先(社員ID,電話番号1,電話番号2) 注記売上年月列のデータ型は,YYYYMM形式のCHAR(⑥型である。 図1主なテーブル構造(一部省略) 2.システムの運用の概要 (1)各店舗は,閉店後の夜間に当日の売上明細ファイルを,システムに送信する。 (QQ)システムは,各店舗から送信された売上明細ファイルのデータを店舗コード 別商品コード別に集計し,翌朝までに月別売上:テーブルに反映させる。 ee (3)営業本部の担当者は,システムを用いて販売分析を行う。また,担当者は店 舗の社員に電話をかけて販売状況を問い合わせることがある。 3.営業本部からの要望及び対応の方針 営業本部からの要望のうち,Fさんに対応を任せられた要望と,Fさんによる対 応の方針は,次のとおりである。Fさんがこれらの方針に従って変更した二つのテ ーブル構造を,図2に示す。 要望1売上データの分析を行うための照会の応答時間を改善してほしい。 方針1月別売上"テーブルを,“月別売上B"”テーブルのように変更する。 要望2社員連絡先の電話番号を3個以上登録できるようにしてほしい。 方針2社員連絡先"テーブルに新たな列を追加するのではなく,“社員連絡 先B”テーブルのように変更する。 販売数2月,売上額3月,販売数3月,…,売上額12月,販売数12月,…) 社員連絡先B(社員ID,表示順,電話番号) 注記売上年列のデータ型はYYYY形式のCHAR(④型で,表示順列はSMALLINT WETS, 図2変更した二つのテーブル構造(一部省略) 〔“月別売上"テーブルの構造の変更] Fさんは,月別売上:テーブルの構造の変更を,次のように検討した。 1.月別売上:テーブルには,行が主索引のキー順にロードされている。その全行 をアンロードしたファイルを,“月別売上B"”テーブルの構造に従って変換し,“A 別売上B"テーブルに主索引のキー順にロードした。 2.RDBMSの機能を用いて,テーブルの統計情報を取得した。"月別売上に"テーブル と「月別売上B"テーブルの統計情報及び索引定義情報を,表1に示す。 3.次の二つの分析処理を選び,照会の応答時間を評価した。その指標として,各分 析処理に必要なディスクからの読込み行数及び読込みページ数を,表1の統計情 報を基に比較した。 分析処理1指定した1店舗について,任意の1年間の売上データを分析する。 分析処理2指定した1商品について,任意の月の売上データを分析する。 ー19一 表1"月別売上:"テーブルと“月別売上8"テーブルの統計情報及び索引定義情報 pes[nee]ier SE 月別売上|360,000,000 100 even|wlwls =垢叶 注記1 ERE,副次索引の数字は索引キーに定義される列の順番を示し,Aは昇順を示す。 注記2網掛け部分は表示していない。9 (1)表1の二つのテーブルでは,複数行を索引のキー順に読み込む場合,アクセ ス経路が|ア|索引のとき,ページは順次に読み込まれるが,77%AK 路が[|イ|索引のとき,1行当たり1ページがランダムに読み込まれる。 (2)分析処理1では,分析に必要な"月別売上”テーブルの1店舗当たりの年間 平均行数は。|ウ|行である。これらの行を,主索引を用いてディスクか ら読み込むとき,最小限|エ|]ページ読み込む必要がある。 一方,“月別売上B"”テーブルの1店舗当たりの年間平均行数は,指定した1 年間が年をまたがらなければ,|オ|行である。これらの行を,ERIE 用いてディスクから読み込むとき,最小限|カ|ページを読み込めばよい。 しかし,その1年間が年をまたがれば,読込みページ数は[キ|年間分の |ク|]ページに増える。 (3)分析処理2では,分析に必要な行数は,二つのテーブルとも1商品コード当 たり最大|ケ|行である。これらの行を,副次索引を用いてディスクから 読み込むとき,最大|コ|]ページ読み込む必要がある。 4.プログラム中のSQLへの影響を調べた。調べたのは,同じ年の二つの月,例えば, 2017年1月と2017年2月の売上額の差を求めるSQLで,その構文を表2中の SQL1に示す。テーブル構造を変更した後で,SQL1と同じ結果行を得るために, 実行の都度,比較する年月に対応したSQLの構文を組み立て,動的SQLで実行す ることにした。その構文を表2中のSQL2に示す。 表2同じ年の二つの月の売上額の差を求めるSQLの構文(未完成) に SQL1 ON X.店舗コード=Y.店舗コードAND X.商品コード=Y.商品コード WHERE X.売上年月=:hv1 AND Y.売上年月=:hv2 ORDER BY Y.店舗コード,Y.商品コード FROM月別売上X INNER JOIN月別売上Y SELECT店舗コード,商品コード,FROM月別売上B 注記ホスト変数のhv1及びhv2には,それぞれ“201701′及び“201702′”が設定されている。 社員連絡先”テーブルの構造の変更] Fさんは,『社員連絡先"テーブルの構造の変更を,次のように検討した。 1.“社員連絡先"テーブルの電話番号1列と電話番号2列の値を調べたところ,プ ログラムの不備による次のような問題の行があることが分かった。 問題1電話番号1列と電話番号2 Wit,異なる電話番号であるべきところ, 同じ電話番号が設定されている行があった。 問題2電話番号1列だけに電話番号を設定すべきところ,電話番号1列に NULLが,電話番号2列に電話番号が設定されている行があった。 問題3電話番号が設定されている場合だけ行を登録すべきところ,電話番号1 列と電話番号2列の両方にNULLが設定されている行があった。 2.問題ユー3を防ぐには,“社員連絡先"テーブルに,図3に示す検査制約を定義す べきであった。ここで,検査制約は,次ののて④のいずれかの述語を組み合わせて 指定する。 ①電話番号1 IS NOT NULL ②電話番号1 IS NULL ③電話番号2 IS NOT NULL ④電話番号2 IS NULL o(|c|Aw[|。|)) 図3“社員連絡先”テーブルに定義むすべきだった検査制約(未完成) ー21一 3.“社員連絡先B”テーブルの要件を,次のように整理した。 要件1問題1ー3を解決すること 要件2社員1人当たりの電話番号を3個以上登録できること なお,同じ電話番号が複数の社員で使われることがある。 4.“社員連絡先B”テーブルの電話番号列にNOT NULL制約を定義し,テーブルに 一意性制約を定義した。 5.要件1,2を満たすために,図4に示すINSERT文を用いて,“社員連絡先"テー ブルから“社員連絡先B”テーブルに行を移行することにした。その移行試験を行 ったときの,移行元である“社員連絡先”テーブルの問題1て3を含む行を表3に, 移行先である“社員連絡先B"”テーブルの行を表4に示す。ここで,表3及び表4 の見出しは列名を表す。 INSERT INTO社員連絡先B SELECT社員ID,1,電話番号1 FROM社員連絡先 UNION SELECT社員ID,2,電話番号2 FROM社員連絡先 WHERE電話番号1ぐ電話番号2 UNION SELECT社員ID,1,電話番号2 FROM社員連絡先 WiERE[|gz]Ap|ヵ| 図4“社員連絡先B"”テーブルに行を移行するINSERT文(未完成) 表3.移行元の行表4移行先の行(未完成) EE oo se eeーー |||| ||||
設問1「"月別売上テーブルの構造の変更]について,①~て(3③)に答えよ。 ①)分析処理に関する記述中の|ア|]|コ|に入れる適切な字句 を答えよ。 なお,索引のバッファヒット率は100%であり,ページ中の行をアクセスす るとき,次にアクセスするページはパッファにないものとする。 @表2中の|』|],|b|]に入れる適切な字句を答えよ。 (3)Fさんは,なぜ表2中のSQL2を動的SQLで実行することにしたのか。そ の理由を40字以内で述べよ。
設問2社員連絡先"テーブルの構造の変更]について,(①)て(5)に答えよ。 (1)社員連絡先B”テーブルの電話番号列にNOT NULL制約を定義した理由 を,本文中の字旬を用いて25字以内で述べよ。 (2②)社員連絡先B"”テーブルの一意性制約に定義すべき列名又は列名の組合 せを答えよ。ここで,主キー制約を除くこと。 @図3中の|c|]~[|ec|に入れる適切な述語を,①こ④の中か らそれぞれ重複なく一つずつ選んで答えよ。 @図4中の|{|~|j』|に入れる適切な六語を,3に條って① ー④⑧の中からそれぞれ重複なく一つずつ選んで答えよ。 (6)表4に記入されている1行目の例に依って,全ての結果行を埋めよ。ここ で,行の並び順は問わない。また,表4の全ての行が埋まるとは限らない。 ー93—
各問題の下に出典を記載しています。