データベーススペシャリスト 2016年 春期 午後Iの問題文9問です。図・表・別冊資料と正解は掲載していませんので、出典の公表資料またはアプリでご確認ください。
データベースの設計に関する次の記述を読んで,設問1ー3に答えよ。 C社は,主力事業である駐車場の運営が好調で,現在,事業の拡大に伴い,駐車場 管理システムを再構築している。その一環として,情報システム部のN君がデータ ベースの設計を行っている。 駐車場の概要] 駐車場は,個々の自動車を駐車する場所として,駐車スペース(以下,車室という) に分けられている。駐車場は,月極駐車場と時間貸駐車場のいずれかに分類される。 (1)月極駐車場:契約期間を定め,契約期間中は自由に入出庫できる駐車場 ①利用できるのは,契約した会員だけである。 ②契約は,SADC社に契約書類を送付して初回の月額利用料金を振り込み,C 社がそれらの内容を確認して,初めて成立する。 (2)時間貸駐車場:時間帯別に料金が定められ,利用の都度精算する駐車場 ①会員でなくても利用することができる。 ②利用者は,空いている車室に入庫し,出庫する際に精算機で利用料金を支払 pr ③料金は,“月て金8.00て22:00 100円20分"のように,曜日ごと時間帯ごと に定められている。また,1日当たりの最大料金又は時間帯当たりの最大料金が 定められている場合がある。 会員及びポイントの概要〕 駐車場の利用者は,氏名,住所などの情報を登録して会員になることができる。 (1)会員登録を行うと,会員IDが記載された会員カードとパスワードが発行され る。会員は,会員IDとパスワードを使用して,C社のWebサイトの会員専用ペー ジにアクセスすることができる。 2)SAK,毎月の支払額(時間貸駐車場で会員カードを提示して支払つた支払額 十月極駐車場の支払額)に,所定の付与率を乗じて算出されたポイントが付与され る。ポイントが付与されると,ポイント付与の基となった支払データに対して,ポ イント付与済みであることが記録される。 ー6一 (3)会員は,ポイントを消費してポイント交換商品と交換することができる。交換を 行うと,付与年月が古いポイントから順に消費され,その内容が記録される。 (Webサイトの概要] C社のWebサイトの利用者は,駐車場名,施設名,エリア名などで絞り込んで, 駐車場の情報を検索することができる。 (1)施設とは,Webサイトの地図上に表示され,検索が可能な建造物である。例え ば,東京駅,〇〇病院,へ人へホテルなどである。 2)エリアとは,駐車場及び施設が属する一定の地域である。例えば,新宿エリア, 横浜エリアなどである。駐車場及び施設は,いずれかーつのエリアに属する。 (3)施設の分類をカテゴリという。例えば,駅,病院,ホテルなどである。施設は, いずれかーつのカテゴリに属する。 (4)駐車場から徒歩圏内にある主要な施設を周辺施設という。駐車場には,一つ又は 複数の周辺施設が定められている。一つの施設は,複数の駐車場の周辺施設として 定められる場合がある。 会員専用ページでは,駐車場利用履歴の閲覧,ポイント付与履歴の閲覧,ポイント の交換,ポイント交換履歴の閲覧などを行うことができる。ポイント付与履歴画面の 例を図1に,ポイント交換履歴画面の例を図2に示す。 ポイント付与履歴会員ID:T1234567ポイント交換履歴会員ID:T1234567 付与消費商品名 |201504|250|0|se|200 Bly THT= L201505[eof of]J|OEY||GS: [asco[lee| saline 100}6003)‘ 注記残ポイントは,付与年月ごとの未注記消費ポイントは,ポイント交換時に消費したポ 使用のポイント数を表す。イント数を表す。 図1ポイント付与履歴画面の例図2ポイント交換履歴画面の例 トー7 [データモデルの設計] N君は,概念データモデル(図3)及び関係スキーマ(図4)の設計を行った。 ;FPA a ie ARIUS ane 時間島 月極駐車電aint |E車i a=i DE ae 会員時間代ーーデー 駐車場利用 図3 N君が設計した概念データモデル(ATER) エリア(エリアコード,エリア名) 会員(AID,会員氏名,会員住所,メールアドレス,パスワード) 駐車場(駐車場ID,駐車場名駐車場区分,駐車場住所,| 。 ],総車室数 駐車場経度,BEALE) 月極駐車場(駐車場TD,月額利用料金,現在契約車室数 時間貸駐車場(駐車場ID,1日最大料金,現在利用車補数) 時間帯別料金パターン(駐車場ID,パターン番号,開始時分,終了時分,単位時間, 単位料金,時間帯最大料金) MN REM(駐車場D,パターン番号,曜日区分) 月極駐車場契約(契約番号,|b|,|c ],契約開始日,契約終了日) 月極駐車場利用(契約番号,利用年月支払年月日,支払額,|d|) 時間貸駐車場利用(駐車場D,利用連番,入庫日時,出庫日時,支払額) 会員時間食駐車場利用(駐車場ID,利用連番,|e|],ポイント付与フラグ) 周辺施設(カテゴリコード,カテゴリ名,施設ID,施設エリアコード,施設名 駐車場D,所要時間,施設経度,施設締度) ポイント交換商品(商品コード,商品名,交換ポイント) ポイント付与(会員TD,付与年月,付ちポイント) ポイント交換(会員TD,交換年月日,付与年月,商品コード,Bow) 図4 N君が設計した関係スキーマ(未完成) ーー8ーー 図4の関係スキーマの主な属性とその意味・制約を,表1に示す。 表1主な属性とその意味・制約 の表面上のこ数値。施設には,# nate車場又は施設が複数存在することはない。BEROME,再測定を行 い,修正されることがある。 ー9一
設問1関係“周辺施設"について,①⑪,②⑫②)に答えよ。 (1)関係“周辺施設”の候補キーを全て答えよ。また,部分関数従属性,推移 的関数従属性の有無を,“HV”又は“なし"CHALE“HV”の場合は,そ の関数従属性の具体例を一つ,次の表記法に従って示せ。 なお,候補キー及び表記法に示されている属性1,属性2が複数の属性から 構成される場合は,{}でくくること。 (2)関係“周辺施設”は,第1正規形,第2正規形,第3正規形のうち,どこ まで正規化されているか答えよ。また,第3正規形でない場合は,第3正規 形に分解し,主キー及び外部キーを明記した関係スキーマを示せ。
設問2図3 4について,1),②)に答えよ。 a図4中の|a|~| e|]に入れる適切な属性名を答えよ。ま た,主キー又は外部キーを構成する属性の場合,主キーを表す実線の下線,又 は外部キーを表す破線の下線を付けること。 2)図3中のエンティティタイプ間のリレーションシップを全て記入せよ。 BS,図に表示されていないエンテイティタイプは考慮しなくてよい。ま た,エンテイティタイプ間の対応関係にゼロを含むか否かの表記は不要であ る。
設問3関係“『ポイント付与",“ポイント交換"について,①,(②に答えよ。 1)BIR“RAY RATE”,“ポイント交換"には,ポイント管理上の不具合が ある。不具合の内容を50字以内で具体的に述べよ。 2)①⑪の不具合を解決するために,関係"ポイント交換"の属性を一つ削除し, 新たな関係"ポイント消費”を追加することにした。 (a)関係『ポイント交換”から削除する属性の属性名を答えよ。 (⑪b)関係『ポイント消費”の属性名を,表2の行⑪)の各列に本文又は図表中 の用語を用いて記入せよ。また,主キー又は外部キーを構成する属性の場 合,主キーを表す実線の下線,又は外部キーを表す破線の下線を付けるこ ws ー10一 BEB,表2の行(b)の列が全て埋まるとは限らない。 (c)図1及び図2に表示されているポイントは,関係“『ポイント消費"では どのような値となかるか。その値を,表2の行(⑪)に記入した属性と同じ列に 対応付くように,表2の(c)の各行及び各列に記入せよ。 なお,表2の(c)の行が全て埋まるとは限らない。 表2関係『ポイント消費”の具体例 (b){ (c)|
データベースの運用設計に関する次の記述を読んで,設問13に答えよ。 A社は,仕入れた商品を中小企業向けに小口販売している件売業者である。A社で は,受注,出荷,発注,在庫管理及び販売分析の業務を支援する受発注在庫管理シス テムを運用している。 [プログラムとテーブルの関係 受発注在庫管理システムの主要なプログラムとテーブルの参照・更新の関係は,表 1に示すとおりである。 表1主要なプログラムとテーブルの参照・更新の関係 マ|商|仕|販|受|出]出]在|発|需|日|月|分 Al dh|A|a|GE|te]me)|注|要|別|別|析 タ先|先指|%|ie|販|用 新デZ|]| z象|測|売|売|+ 登|績|績|タ nem”。||R||Rlcol|feu]||||| ,erm||RlR||||fee||||| y(n||]R]||IRlcl||||||| |下着理[et IRIRI||ll l ft ot ft|| 商量情報管理=|cCIR||||||||||| mom。。|||||||||||RIRIRI suman||R||RlR||||||||| 出荷圭と|TR]||Imlclul|||| mer UT|R||||||IR||CRIRIR|| バ|発注誰象データ作成||||||||RlcIRIR|]| チ|販売実績次集計||R|[rR]||Rl||lcl|| |販売守有次集計||||||||||[Riel| |分析用データ作成|||||||||||RIRleol |マスタ更新友映|R|culcolco|||||||||| 注記C:追加,R:参照,U:変更 (1)商品”テーブル,“仕入先”テーブル及び“販売先”テーブルの追加,変更 の要求は,"マスタ更新登録"テーブルに登録され,バッチプログラムで1件ず つコミットしながら反映される。 (2)“Em”テーブルは,在庫のうちの3割(売れ筋商品)に相当する約1,000行 が毎日頻繁に変更される。 (3)“出荷"テーブルには,毎日,約400,000行が追加される。 (4)"日別販売実績"テーブルには,当日分の販売実績が追加される。また,“A 別販売実績"テーブルには,当日分の販売実績が当月分の販売実績に反映される。 これらのテーブルには,直近3年分の販売実績が蓄積されている。 (5)分析用データ"”テーブルは,販売分析で使用する。当日分の販売実績が,前 日に作成された“分析用データ”テーブルに反映され,直近3年分の分析結果 となるように維持されている。 “分析用データ”テーブルは,バッチプログラム“分析用データ作成"を用い て,蓄積されている販売実績を基に,再作成することも可能である。 [バッチプログラムの実行スケジュール〕 バッチプログラムの実行スケジュールを,図1に示す。 Tl T2 TS T4 T5 T6 当ー更新反映||翌 a2 AME TAS A A 3販売実績発注対象オ 2プロー}em a en J? 販売実績Se paca)p—tl a分析用データ作成at 注記TI~T6は,各契機を示す。矢線は,バッチプログラムの実行の前後関係を示す。 図1バッチプログラムの実行スケジュール [データの入出力〕 (1)RDBMSがディスクとデータの入出力を行う単位を,ページという。1ページ ー13一 内にはテーブルの行が複数格納される。 2)同じページに,異なるテーブルの行が格納されることはない。 (3)受発注在庫管理システムでは,ページのサイズは4.096バイトとしている。ま た,“在庫"テーブル及び“出荷"テーブルは,1ページ内に40行格納できるも のとする。 (RDBMSのバックアップ機能・復元機能・更新ログによる回復機能] 1.バックアップ機能 (1)バックアップの単位には,データベース単位とテーブル単位がある。 2)バックアップの種類には,取得するページの範囲によって,全体バックア ップ,増分バックアップ及び差分バックアップがある。 ①全体バックアップには,全ページが含まれる。 ②増分バックアップには,前回の全体バックアップ取得後に変更された ページが含まれる。ただし,前回の全体バックアップ取得以降に増分バ ックアップを取得していた場合は,前回の増分バックアップ取得後に変 更されたページだけが含まれる。 ⑧差分バックアップには,前回の全体バックアップ取得後に変更された 全てのページが含まれる。 3)全体バックアップと増分バックアップの場合は,バックアップ取得ごとに バックアップファイルが作成される。差分バックアップの場合は,2回目以降 の差分バックアップ取得ごとに,前回の差分バックアップファイルが最新の差 分バックアップファイルで置き換えられる。 (44)バックアップ取得に要する時間は,バックアップを取得するページ数に比 例する。 2.復元機能 Q①)バックアップを用いて,バックアップ取得時点の状態に復元できる。 (2)復元の単位は,バックアップの単位と同一である。データベース単位のバ ックアップを用いて,特定のテーブルだけを復元することはできない。 3.更新ログによる回復機能 (1)バックアップを用いて復元した後,更新ログを用いたロールフォワード処 ー14一 理によって指定の時刻の状態に回復できる。 (2)更新ログによる回復に要する時間は,更新ログの量に比例する。 [バックアップ及び回復の方針] (1)オンライン時間帯直前に,データベース単位でバックアップを取得する。 (2)オンラインプログラムで更新され得るテーブルは,オンライン時間帯直後に, テーブル単位でバックアップを取得する。 3)それぞれのバッチプログラムを実行する直前に,更新対象のテーブルについて, テーブル単位でバックアップを取得する。ただし,⑫②でバックアップを取得し たテーブルは対象外とする。 (4)バックアップの種類は,全体バパックアップとする。 (5)回復は,受注,出荷,発注,在庫管理の業務に影響するテーブルを優先する。 [データ異常発生時の回復運用] バッチプログラム“出荷計上”にプログラム不良があり,“在庫”テーブルの当日 分の内容が不正になっていた。これが原因でバッチプログラム“発注対象データ作成" が中断した。バッチプログラムで更新された全てのテーブルを当日のオンライン時間 帯直後の状態に復元し,プログラム不良を修正後,全てのバッチプログラムを再実行 すれば回復可能であるが,時間が掛かるので,次の手順を立案した。 第1段階バックアップを用いて“出荷"テーブルと“在庫”テーブルを当日の オンライン時間帯直後の状態に復元し,プログラム不良を修正したバッ チプログラム“出荷計上”を再実行する。 第2段階バックアップを用いて“|ア|”テーブルを当日のオンライン時 間帯直後の状態に復元し,バッチプログラム“|イ|"を再実行す る。 第3段階バックアップを用いて“|ウ|”テーブルを当日のオンライン時 間帯直後の状態に復元し,バッチプログラム“|エ|"を再実行す る。 第4段階バッチプログラム“マスタ更新反映"”を実行する。 ー15一
設問1受発注在庫管理システムのバックアップ及び回復について,①),②⑫に答えよ。 1)図1中の各契機でのテーブル単位のバックアップについて,次の表でバッ クアップを取得するものに“〇"を記入せよ。 BB,バックアップを取得しないものは,空欄のままとすること。 Al mh)入|売|注|荷|荷|庫|注|要|別|別|析 マ|商|仕|販|受|出|出|在|発|雷|日|月|分 更図象|測|売|売|デ タ先|3指対|予|販|販|用 新デ実|実|| a績|績|タ 契機Beタ ITT U_U 1 1 1 l 1l l 1l|l 1 1l 1l|l| IT 1 1 1 1l l l 1l 1l 1l 1l l|l 1 If9 0 1 1 1 1l Ll 1 l 1l Ll 1 l 1l 1 IT 1 1 1 1l 1l 1l|l 1l 1l 1l l|l|| im|1||||l||||||| (2)図1で実行中のバッチプログラムがテーブルにアクセスしたとき,ディス ク障害を検知して異常終了する場合がある。障害を検知したバッチプログラム 名,ディスク障害の影響受けたテーブル名,及びディスク復旧後の回復手順 を,次の表にまとめた。表中の|a|~|f|に入れる適切な字 名を答えよ。 障害を検知ディスク障 or oS Aeディスク復旧後の回復手順 したバッチ|害の影響をっ— 名ブル名 バックアップを用いて“出荷"テーブルと“在庫′テーブルを復元 出荷計上|在庫し,バッチプログラム“出荷計上”を再実行する。 ②バパッチプログラム"受注明細書送信"を継続する。 ①バパックアップを用いて“出荷"テーブルを復元した後,| 。| 販売実績日によってバッチプログラム“出荷計上"終了時の状態に回復する。 次集計|出世パックアップを用いて“日別販売実績"テーブルを復元し,バッチ -ブプログラム“販売実績日次集計”を再実行する。 バッチプログラム“受注明細書送信”を継続する。 ①バパックアップを用いて"|p|′テーフブル,人[。|" マスタ更新|a F<DNRC"|』|'テデープルを化元し。バッチブログラ 反映ム“マスタ更新反映”を|ce|する。 ②バパッチプログラム“分析用データ作成”を|{|する。
設問2バックアップ運用の見直しについて,①~(3⑬)に答えよ。 Q)月初めだけ全体バックアップを取得し,日々の運用では増分バックアッ プ,差分バックアップも活用することにした。ただし,どちらのバックアップ でも同程度の容量の節約となる場合は,バックアップ取得に要する時間が短い 方を選択する。この場合,“在庫"テーブル及び“出荷"テーブルについて, 選択すべきバックアップの種類として“増分”又は“差分"のいずれかをのOP印 CH,Ek,その選択根拠として“容量”又は“時間"のいずれかを〇印で HA,その理由(容量が節約できる理由又は時間が短い理由)を,30字以内 で述べよ。 2)ある一つのテーブルのバックアップを月初めだけ取得し,日々のバックア ップは取得しないことにした。回復の方針に従って,どのテーブルを対象にす べきか,テーブル名を答えよ。 (3)②のテーブルに障害が発生し,PO,バックアップファイルが壊れていた 場合,どのように回復するか,40字以内で述べよ。
設問3[データ異常発生時の回復運用}】について,(1),(②に答えよ。 ①[|アァ|]~[|=|に入れる適切な字句を答えよ。 ②パッチプログラム“出荷計上",“|イ|",“Lz|"の再実行 及び“マスタ更新反映”の実行を除いて,その他のバッチプログラムの再実行 は不要である。その理由を40字以内で述べよ。 ー17一
RDBMSのセキュリティに関する次の記述を読んで,設問1ー3に答えよ。 B社は,個人顧客を対象にした保険会社である。B社では,顧客の個人情報の保護 を強化するために,営業支援システムにおけるセキュリティに関する設計を見直すこ とにした。情報システム部のFさんがその見直しを担当した。 (RDBMSのビュー及びセキュリティに関する主な仕様 (1)実テーブル(以下,テーブルという)又はビューのアクセス権限(SELECT, INSERT,UPDATE及びDELETEの各権限)をもつユーザは,テーブル又はビ ューにアクセスすることができる。 (2)ビピューにアクセスする場合,そのピューが参照するテーブル又は別のビピューの アクセス権限は不要である。 (3)テーブル又はビューのアクセス権限は,ユーザID,ロールに付与される。 (4)ロールは,ユーザIDに付与され,別のロールにも付与されることがある。 [営業部の組織・業務の概要] 営業部の組織・業務の概要は次のとおりである。組織の一部を図1に示す。 1)営業部及び営業課は,部門番号で識別される。 (2)社員は,社員番号で識別される。社員には,営業支援システムにログインする ためのユーザID(社員番号を使用)が付与されている。 (3)個人顧客(以下,顧客という)は,顧客番号で識別される。1人の顧客は,一 つの営業課によって担当される。 (4)課長は,部下社員から成る少人数の営業チーム(QE,チームという)を複数 編成する。経験豊かな社員については,複数チームに参加させることがある。 (5)チームは,顧客を訪問して面談し,保険に関わる様々な業務を行う。 (6)各チームは,複数顧客を担当する。同じ顧客を複数チームが担当することはな い。 (7)課長は,随時,チーム編成を変える。チームに編成される社員が変わったり チームから離れた社員が,また同じチームに戻ったりすることがある。 なお,チーム編成は,営業支援システムによって管理されていない。 ー18一 営業部(B10) BBE:LAK(E111) 営業1課(B11)営業2課(B12) 課長:P氏(E112)課長:TK(E116) QE(E113)UK(E117) RK(E114)V氏(E118) S氏(E115)WE(E119) 注記部門名の後ろのカッコ内は部門番号を表す。 社員名の後ろのカッコ内は社員番号を表す。 図1営業部の組織(一部) [営業支援システムの概要〕 1.主なテーブルの構造 営業支援システムで使用される主なテーブルの構造を図2に示す。 部門(部門番号,部門名,部門長社員番号,上位部門番号,所在地。…) 社員(社員番号,所属部門番号,社員名,電話番号,メールアドレス,FAX番号) 顧客(顧客番号,担当部門番号,BES,生年月日,住所,電話番号,性別。…) 訪問予定(顧客番号,社員番号,訪問予定日,訪問予定時刻,訪問予定時間,訪問目的) 訪問実績(顧客番号,社員番号,訪問実施日,訪問開始時刻,訪問終了時刻,訪問結果) 図2主なテーブルの構造(一部省略) 2.セキュリティ要件 B社での顧客の個人情報(以下,個人情報という)とは,顧客名,生年月日,そ の他の記述などによって特定の個人を識別することができるものをいう。セキュリ ティに関する設計見直し後の個人情報に関するセキュリティ要件は,次ののて④の とおりである。 ①営業課の社員は,その課が担当する顧客の個人情報にアクセスできる。 ②部門長は,部下がアクセスできる全ての情報にアクセスできる。 ③個人情報が格納されているテーブルを隠蔽するために,社員にはビューを使 わせ,テーブルには直接アクセスさせない。 ④個人情報にアクセスする必要がなくなった社員については,そのことを反映 するためのアクセス制限を直ちに実施する。 3.操作及び処理の概要 社員が自分のユーザIDを指定してログインした営業支援システムに対する操作, 及び営業支援システムによる処理の概要は,次のとおりである。 1)社員は,顧客訪問の前に予定を登録し,予定の変更は,TOME,反映す る。予定なしに顧客訪問することはない。 (2)社員は,予定日に顧客訪問を実施後,その実績を登録する。 (3)社員は,画面上でアクセスを許可されたテーブル名又はビュー名の一覧か ら一つを選び,選択・集計条件及び結果行の並び順を指定する。’ (4)営業支援システムは,(3)の指定に基づき,実行可能なSQL文を動的に組み1 立てて実行し,その実行結果を画面に出力する。 [ビュー及びロールの設計〕 Fさんは,個人情報を含む営業課別ビューのうち,営業1課及び営業2課のビュー を,表1のSQL1及びSQL2に示すように設計した。 表1営業1課及び営業2課のビューの定義 住所,電話番号,性別FROM顧客WHERE担当部門番号='B11 注記網掛け部分は表示していない。 Fさんは,ビューを用いることを前提に,次のようにロールを設計し,運用するこ とに決めた。営業課別ビューのアクセス権限をロールに付与する手順を,表2に示 す。 (1)部門番号をロール名として,ロールを定義する。 (2)営業課別ビューのアクセス権限をロールに付与する。 3)ロールの付与・剥奪については,課長が1営業日前までにデータベース管理 者(以下,DBAという)に依頼する。DBAは,課長からの依頼に基づいて,ロ ールの付与・剥礁をRDBMSに対して実施する。 表2営業課別ビューのアクセス権限をロールに付与する手順(未完成) 4|GRANT ROLE B10 TO E111: ウ|GRANT ROLE B11 T0 E112,£113,E114,E115; エ|GRANT ROLE B12 TO E116,E117,E118,E119; オ|CREATE ROLE B10; カ|CREATE ROLE B11; キ|CREATE ROLE B12; ク|GRANT SELECT ON営業1課ビューT0|。]: ケ|GRANT SELECT ON営業2課ビュー10|bp J; 注記セミコロンは,SGQL文の終端を示す。 ここで示した部門番号及び社員番号は,図1に示したものに限っている。 [ビューの設計変更〕 Fさんが,設計見直し前の営業支援システムの利用状況を分析したところ,動的に 組み立てて実行されたSQL文の中に,“顧客”テーブルに直接アクセスするSQL文, 及び複雑でかつ実行回数が多いSQL文があった。前者の例を照会1に,後者の例を 照会2に示す。 照会1社員が過去に登録した訪問予定のうち,その社員が予定日に訪問しなかっ た顧客の顧客番号,顧客名,社員番号及び訪問予定日を出力する(表3 のSQL3を参照)。 照会2年初からの訪問回数がN回以上の社員について,社員番号,社員名,訪問 回数を出力する。ここで,Nは実行時に与えられ,SQL文の動的パラメ タの?に設定される(表3のSQL4を参照)。 Fさんは,照会1についてはセキュリティ要件③を満たすために,照会2について はSQL文を簡単にするために,それぞれビューを使うことにした。 ー51一 表3営業支援システムで使用する主なSQLの構文(未完成) SELECT K.顧客番号,K.顧客名,HY.社員番号,HY.訪問予定日FROM顧客K | 』|訪問予定HY ONK.天客番号=WES SQL3[|。|訪問実績HJ ON HY.顧客番号=HJ.顧客番号 AND HY.社員番号=HJ.社員番号AND HY.訪問予定日=HJ.訪問実施日 WHERE HJ.訪問実施日IS NULL SELECT S.社員番号,S.社員名,COUNT(*)訪問回数 FROM社員S INNER JOIN訪問実績HJ ON S.社員番号=HJ.社員番号 SQL4|WHERE HJ.訪問実施日>=ISODATE(’2016-01-01’) GROUP BY S.社員番号,S.社員名 HAVING COUNT(*)>=? SELECT社員番号,社員名,訪問回数FROM社員別訪問回数ビュー SQL5|WHERE 注記ISODATE関数は,日付を表す文字列をDATE型に変換するユーザ定義関数とする。 [セキュリティ要件の強化〕 営業支援システムのセキュリティを更に強化するために,セキュリティ要件が, “チームの社員は,当該チームが担当する顧客の個人情報にアクセスできる。"に変 更された。Fさんは,営業課別のロールをチーム別のロールに変更するという対応 (対応案A)も考えたが,次のような対応(対応案B)を採用することにした。 1)営業支援システムに,新たに“チームメンバ"”テーブルを追加する。当該テー ブルへのアクセス権限(DELETE権限以外)を課長に与え,課長が次のような 操作を行える機能を追加する。ただし,操作は各営業課内に限られるものとする。 (a)営業課内で一意なチーム番号を付与する。 (b)営業課内のチームの社員ごとに,担当開始日及び担当終了日を設定した行 を登録する。担当終了日が未定の場合は,NULLを設定する。 (<)担当開始日の当日又は前日までに,行を登録する。 (d担当開始日列又は担当終了日列を,いつでも変更することができる。 (e)過去にどの社員がどのチームのメンバだったかを調べることができる。 ②)“顧客”テーブルにチーム番号列を追加し,営業課別だった表1のSQL及び SQL2を,営業課共通にするために,表4のSQL6のように変更する。 表4セキュリティ要件の強化後のビューの定義 EE ee CREATE VIEW営業課ビューAS WHERE T.社員番号=CURRENT_USER AND T.担当開始日<=CURRENT DATE SELECT顧客番号,MEL,生年月日,住所,電話番号,性別 AND(T.担当終了日>=CURRENT_DATE OR T.担当終了日IS NULL) ON K.担当部門番号=T.部門番号AND K.チーム番号=T.チーム番号
設問1〔ビュー及びロールの設計)について,(①,(②)に答えよ。 a)表2中の| 』|~|<|に入れる適切な字句を答えよ。 2)表2のアーケで示したSQL文を正しい順に並べ替えよ。 なお,正しい順は複数通りあるが,そのうちの一つを答えよ。 (ううプんうつて)さ(うつバイフル9て)テリりさ(好)
設問2〔ビューの設計変更]について,(①⑪~(3⑬)に答えよ。 a)表8中の| qd|,|=|に入れる適切な字旬を答えよ。 (2)表3中のSQL4において,そのままビューの定義に指定できない人箇所があ る。その人箇所を二重線で消せ。 (3) ②⑫で指定できないとした人箇所を除いてビューを定義むする。定義したビュー 構造を,社員別訪問回数ビュー(社員番号,社員名,訪問回数)とし,SQL4 と同じ結果行を得るために,表3中のSQLS(未完成)を作成した。SQL5の 空欄に適切な字旬を入れて完成させよ。ただし,結果行の並び順については, 考慮しなくてよい。
設問3〔セキュリティ要件の強化]について,①~(3③)に答えよ。 QQ)“チームメンバ”テーブルの構造を示せ。主キーには実線の下線を付ける こと。 (2)1中の社員のうち,個人情報へのアクセスが許可されているにもかかわ らず,表4のSQL6では期待した結果を得られない社員がいる。その社員の社 員番号を全て答えよ。また,解決策として,“チームメンバ”テーブルに対し て行うべき行の操作を,30字以内で具体的に述べよ。 (3)セキュリティ要件④におけるアクセス制限の実施について,対応案Bが対 応案Aに比べて優れている理由を,40字以内で具体的に述べよ。 ーEEー
各問題の下に出典を記載しています。