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

共通の文章

データベース設計に関する次の記述を読んで,設問1,2に答えよ。 A社は,関東圏に展開している食料品スーパマーケットチェーンである。4社が取 り扱う商品には,青果,鮮魚,精肉などがあるが,その中の自社商品の弁当・総藤類 について,商品配送管理システムを用いて配送業務を実施してきた。A社は,デザー ト・ケーキ類の追加を計画しており,データベース設計を見直すことにした。 [現状業務の概要) 1.拠点 (1)拠点は,拠点コードで識別し,拠点名,所在地,代表電話番号をもつ。 (2)拠点には生産工場と店舗があり,拠点区分で分類する。 (3)生産工場は,A社の自社商品だけを生産する。A社には3か所の生産工場があ る。生産工場には,自社商品を生産する役割と,自社商品を仕分けして各店舗へ 配送する役割がある。生産工場は,生産能力と操業開始年月日をもつ。 (4)店舗は,約70あり,店舗基本情報をもつ。 QO生産工場から店舗への配送では,配送ルートを設定している。一つの配送ル ートは,1台のトラックで2~3時間で配送できる3一8の店舗を配送先として いる。店舗への配送順序をあらかじめ決めている。 ②配送ルートは,ルート番号で識別し,ルート名称と一つの配送元の拠点コー ドをもつ。 @店舗は,一つの配送ルートに属し,そのルート番号をもつ。また,配送ルー ト上何番目に配送されるかを表す配送順序をもつ。 2.自社商品 (1)自社商品は,A社の商品仕様に基づく弁当,総菜,おにぎりなどである。 (2)自社商品は,商品コードで識別し,商品名,商品価格,商品仕様をもつ。 3)各生産工場は,全ての自社商品を生産する。 (4)自社商品ごとに生産ロットサイズを決めている。 3.発注から配送まで (1)A社本部は,店舗からの発注を,昼食前と夕食前の時間帯に合わせて受け付け る。 ①店舗は,必要な自社商品とその発注数量を設定して発注する。 ー6一 ②発注は,配送を受ける時間帯に対する締め時刻(以下,締め時刻という)ま で,複数回に分けて行うこともある。一つの発注の中で同一の自社商品を複数 回登録することができる。店舗が発注数量を減らす又は取り消す場合,当該自 社商品の発注数量をマイナスの値で設定して発注する。 ③発注は発注番号で識別し,発注明細は発注番号と発注明細番号で識別する。 ④A社本部は,店舗からの発注について,店舗の拠点コード,発注登録日時を 確認し,配送予定日時を記録する。 (2)A社本部は,店舗からの発注に基づき生産の指示を行う。生産工場は,生産の 1指示に基づき生産する。 ;①A社本部は,締め時刻の対象となる発注について,生産工場ごとに配送先の 店舗の自社商品ごとの発注数量を集計し,生産の指示とする。 ②生産は,生産番号で識別し,生産工場の拠点コード,生産完了予定日時を記 録する。 ③生産明細は,生産番号と商品コードで識別し,生産数量を記録する。生産数 量は,集計した発注数量を満たすように,自社商品の生産ロットサイズの倍数 で設定する。 ④生産の対象とした発注明細に対して,生産番号を記録する。 ⑤生産工場は,生産完了後に生産完了日時を記録する。 (3)生産工場は,自社商品を店舗ごとに仕分けて配送する。 ①A社本部は,締め時刻の対象となる発注に対して店舗ごとに自社商品別に発 注を集計し,配送の指示を行う。 ②配送は,配送番号で識別し,配送完了予定日時と配送先の拠点コードを記録 する。 ③配送明細は,配送番号と商品コードで識別し,配送数量を記録する。配送数 量は,実際の配送数量である。 ④配送の対象とした発注明細に対して,配送番号を記録する。 ⑤店舗は,配送された自社商品を受領し,配送に対して,店舗受領日時,店舗 受領担当者を記録する。 ーァー [概念データモデルと関係スキーマの設計】 [現状業務の概要)についての概念データモデルを図1に,関係スキーマを図2 に示す。 ハ ルー 図1概念データモデル(未完成) 拠点(拠点コード,拠点区分,拠点名,所在地。代表電話番号) 生産工場(拠点コード,生産能力,操業開始年月日) 店舗(拠点コード,店舗基本情報,[。]) 自社商品(商品コード,商品名,商品価格,商品仕様[bp]) 配送ルート(ルート番号,ルート名称,[<c]) (Cia) (Ce) 生産(生産番号,|],生産完了予定日時,生産完了日時) 生産明細(生産番号,[g|,生産数量) 配送(配送番号,配送完了予定日時,|h|店舗受領日時,店舗受領担当者) 配送明細(配送番号,|g],配送数量) 図2関係スキーマ(未完成) ーー8 wee 〔新たな商品の追加] 1.新たな商品及び委託先 (1)A社は,新たな商品として,デザート・ケーキ類を追加することにした。 (2)デザート・ケーキ類は,B社に生産を委託する。委託して生産する商品を委託 商品と呼ぶ。委託商品は,A社の商品仕様に基づいて生産する。自社商品と委託 商品を併せて自社仕様商品と呼ぶ。 (3)B社の工場を委託工場と呼び,委託工場は委託開始年月日をもつ。委託工場は 5か所ある。個々の委託商品を生産する委託工場は一つに決まっている。また, 委託工場の追加に伴って,生産工場を自社工場と呼ぶことにする。自社工場と委 託工場を併せて工場と呼ぶ。 (4)A社は,既存の配送ルートを使って自社商品と委託商品を併せて店舗へ配送す る。 (5)これまで自社工場内で仕分けと配送を行っていた役割に,工場の拠点コードと は別に物流センタとしての拠点コードを付与する。 (66)自社工場から物流センタへ,委託工場から物流センタへ,自社仕様商品を運ぶ ことを納入と呼ぶ。 2.物流センタ追加に伴う納入ルートの追加と配送ルートの変更 (1)納入の指示及び納入は,次のように行う。 ①各自社工場と自社工場内の物流センタ,各委託工場と各物流センタの組を納 入ルートと呼ぶ。納入ルートは,ルート番号で識別する。 ②A社本部は,締め時刻の対象となる発注について,次のように納入の指示を 行う。 ・自社商品については,物流センタごとに配送先の店舗の発注数量を自社商品 別に集計して,納入の指示とする。 ・委託商品については,物流センタごとに配送先の店舗の発注数量を委託商品 別に集計し,委託商品を生産する委託工場ごとに分けて,納入の指示とす る。 ③納入は,納入番号で識別し,納入するルート番号と納入予定日時を記録する。 納入が完了後,納入完了日時を記録する。 ④納入明細は,納入番号と商品コードで識別し,納入数量を記録する。 ー9一 ©納入の対象となる発注明細に対して,納入番号を記録する。 (2)配送ルートの配送元を自社工場から物流センタに変更し,物流センタに対する 配送の指示及び店舗への配送は,現状業務と同様に行う。 (3)配送ルートと納入ルートを併せてルートと呼ぶ。 (4)生産の指示及び生産は,次のように行う。 ①自社工場に対する生産の指示及び生産は,現状業務と同様に行う。 ②委託工場に対する生産の指示は,全店舗の委託商品ごとの発注数量を集計し CHT Ds. ③委託工場は,生産の指示に基づいて,生産を行い,自社工場と同様の記録を AT Dig 新たな商品の追加に対応するために,LB,ルート及び自社仕様商品をサブタイプ に分割した。新たな商品を追加した概念データモデルを図3に,工場,物流センタ, ルート及び納入の関係スキーマを図4に示す。 /\ 物流 /\ 図3新たな商品を追加した概念データモデル(未完成) ー10一 工場(拠点コード,生産能力) (Li) (拠点コード,操業開始年月日) 物流センタ(拠点コード,区画面積) ルート(ルート番号,ルート名称) (ルート番号.[」]) (ルート番号, 納入(納入番号,納入ルート番号,納入予定日時,納入完了日時) 納入明細(納入番号,商品コード,納入数量) 図4工場,物流センタ,ルート及び納入の関係スキーマ(未完成) 解答に当たっては,巻頭の表記ルールに従うこと。ただし,エンティティタイプ間 の対応関係にゼロを含むか否かの表記は必要ない。 なお,エンティティタイプ間のリレーションシップとして"多対多"のリレーショ ンシップを用いないこと。エンティティタイプ名及び属性名は,それぞれ意味を識別 できる適切な名称とすること。また,識別可能なサブタイプが存在する場合,他のエ ンティティタイプとのリレーションシップは,スーパタイプ又はサブタイプのいずれ か適切な方との間に記述せよ。また,関係スキーマは第3正規形の条件を満たしてい るとと。

共通の文章

データベースの実装に関する次の記述を読んで,設問1ー3に答えよ。 劇場運営会社のY tlt,オンラインチケット販売システム(以下,チケット販売 システムという)を構築してインターネットでのチケットの申込みを受け付けている。 Y社ではチケット販売システムを刷新し,新たにプレイガイドなどでのチケット販売 委託を進めることになった。 (RDBMSの主な仕様〕 チケット販売システムに用いているRDBMSの主な仕様は,次のとおりである。 1.ISOLATIONレベル 選択できるトランザクションのISOLATIONレベルとその排他制御の内容は,表 1のとおりである。 ロックはは行単位で掛ける。共有ロックを掛けている間は,他のトランザクション からの対象行の参照は可能であり,変更は共有ロックの解放待ちとなる。専有ロッ クを掛けている間は,他のトランザクションからの対象行の参照,変更は専有ロッコ クの解放待ちとなる。 #1トランザクションのISOLATIONレベルとその排他制御の内容 HO データ参照時に共有ロックを掛け,参照終了時に解放する。 データ参照時に共有ロックを掛け,トランザクション終了時に解放する。 データ変更時に専有ロックを掛け,トランザクション終了時に解放する。 2.レプリケーション機能 (1)1か所のデータを複数か所に複製する機能,複数か所のデータを1か所に集約 する機能,及び両者を組み合わせて双方向に反映する機能がある。これらの機能 を使用すると,一方のテーブルへの挿入・更新・削除を他方に自動的に反映させ ることができる。 (2)トランザクションログを用いてトランザクションと非同期に一定間隔でデータ を反映するバッチ型と,。レプリケーション元のトランザクションと同期してデー タを反映するイベント型がある。 ①バッチ型では,テーブルごとに,レプリケーションの有効化,無効化をコ マンドによって指示することができる。無効化したレプリケーションを有効化 するときには,蓄積されたトランザクションログを用いてデータを反映する。 ②イベント型では,レプリケーション先への反映が失敗すると,レプリケー ション元の変更はロールバックされる。 (3)列の選択,行の選択及びその組合せによって,レプリケーション先のテーブル に必要とされるデータだけを反映することができる。 [チケット販売システムの概要〕 チケット販売システムは,空席管理システムと販売管理システムで構成される。オ ンラインチケット販売時には,空席管理システムで空席を確認した後に座席を確保し, 販売管理システムでチケット情報を保持する。 [チケット販売システムのテーブル〕 空席管理システムと販売管理システムのそれぞれの主なテーブルのテーブル構造は, Bl,2のとおりである。索引は,主キー及び外部キーに定義している。 会場(会場番号,会場名,都道府県,住所,座席図。…) 座席(会場番号,座席番号,席種) 公演(公演番号,公演名,公演概要,…) 公演開催明細(公演番号,公演日,開演時刻,開場時刻,公演会場番号,販売開始日,…) 席種料金(公演番号,席種,料金) 席種在庫(公演番号,公演日,開演時刻,席種,空席数) 座席状況(公演番号,公演日,開演時刻,座席番号,空席フラグ,仮予約フラグ,…) 図1空席管理システムの主なテーブルのテーブル構造(一部省略) 会員情報(会員番号,氏名,氏名カナ,郵便番号,住所,メールアドレス,…) 購入(購入番号,購入日,会員番号,公演番号,公演日,開演時刻,席種,枚数,支払方法, 受取り方法,合計金額,…) チケット(公演番号,、公演日,開演時刻,座席番号,席種,購入番号,購入フラグ) 図2販売管理システムの主なテーブルのテーブル構造(一部省略) 〔チケット販売業務の概要] 1.テーブル及び列の設定 (1)会場と座席 ①会場には,一意な会場番号を付与して,都道府県,住所,座席図などを設定 する。 ②座席には,会場ごとに一意な座席番号を付与して,席種('S,‘A’,‘B’ など)を設定する。 (2)公演と席種料金 QO公演には,一意な公演番号を付与する。公演開催明細には,公演日時(公演 A,開演時刻)ごとに,開場時刻,公演会場番号,販売開始日などを設定する。 ②席種料金には,公演の席種ごとに料金を設定する。 (3)席種在庫と座席状況 ①席種在庫には,席種ごとの空席数をもつ。座席の購入が確定したら空席数 を減らし,購入された座席がキャンセルされたら空席数を戻す。 ②座席状況には,公演開催明細ごとの全ての座席の状況そもつ。販売開始時 には,空席フラグはオン,仮予約フラグはオフとする。座席の購入処理中は仮 予約フラグをオンにする。座席の購入が確定したら,空席フラグをオフにして 仮予約フラグをオフにする。 (4)チケット ①チケットには,公演開催明細ごとに,全ての座席番号と席種を設定する。 ②未販売のチケットは,購入フラグをオフに,購入番号をNULLに設定する。 ③チケットの購入を申し込まれたら,購入フラグをオンにする。購入が確定 したら,購入番号を設定する。 2.チケットの購入 (1)チケットを購入するためには,会員登録をする必要がある。 (2)会員は,チケットの検索を行って,チケット情報一覧を表示する。チケット情 報は,公演名・公演日・開演時刻の昇順,料金の降順に出力される。 3)会員は,チケット情報一覧から,空席のある公演の席種を選択する。その後, 枚数を指定し,空席の座席番号を希望枚数分指定する。 (4)会員は,決済を行い,決済が成立すれば購入が確定する。 ー15一 [チケット情報一覧を出力するSQL MORE) 空席管理システムから,公演日が2020年4月かつ都道府県が東京のチケット情報 一覧の出力の例を図3,出力するSQL文の構文を図4に示す。 |ABCDEF|2020-0401|140O|1390|Xホール|東京|S|oo|O 開演時刻|開場時刻者道府県空席情報 |ABCDEF|2020-04-01|14:00|1330|Xホール|東京|B|6000|X| |ABCDEF|2020-04-01|18:00|1730|Xホール|東京|S[10,000]O| |GHABCD|2020-04-02|18:00|1730|Yホール|東京|S[12,000]O| |GHABCD|20200402|1800|1730|Yホール|東京|A|ieoo|O |GHABCD|2020-04-30|18:00|1730|2ホール|東京|S|12,000]O| |GHABCD|2020-0430|1800|17390|2ホール|東京|A|oo|O 注記空席情報は,〇:空席あり,X:売切れを表す。 図3チケット情報一覧の出力の例 SELECT公演名,AIBA,開演時刻,開場時刻,会場名,都道府県,BE,料金, (| 。|2.空褒数|b|rmEN'X'ELSE'O'END)AS空席情報 FROM会場, (SELECT*FROM公演NATURAL JOIN公演開催明細NATURAL JOIN席種料金)AS Al (SELECT*FROM席種在庫WHERE空席数>0)AS A2 USING(公演番号,公演日,開演時刻,席種) WHERE会場.会場番号=A1.公演会場番号 AND公演日|q|'2026-04-0'|e]'2020-04-39 AND都道府県="東京 公演名公演日,開演時刻料金 図4チケット情報一覧を出力するSQL文の構文(未完成) [オンラインチケット販売処理の設計] チケット販売委託のため,空席管理システムは,プレイガイドなどの外部委託先に も公開する。このために,空席管理システムの空席確認,仮予約の処理の見直しを行 った。見直しに当たって,同時実行されたトランザクションのやり直しが極力発生し ないようにする方針とした。 ある会員が複数のチケットを購入することを想定して,チケットの販売処理につい て検討した。その概要を図5に示す。 図5では,会員の意思で購入を途中でキャンセルした場合,空席でない座席があ り購入が失敗した場合,又はその他のエラーが発生した場合の,途中までの処理を取 り消すための例外処理を省略している。 全ての購入希望チケットの情報を受け取り,KOO~OO 処理を実行する。 ①トランザクションを開始する。 ②全ての購入希望チバケットの購入番号がNULLか確認す る。(SELECT文) ③全ての購入希望チバケットの購入フラグをオンにする。 (UPDATE X) ③④空席管理システムへ,全ての購入希望座席の仮予約を|(4)トランザクションを開始する。 依頼する。(⑪)座席数分,次の処理を繰り返す。 ・空席フラグがオンか確認する。 ・仮予約フラグがオフか確認する。 ・仮予約フラグをオンにする。 (c)コミットする。 ⑥決済処理を行う。MAS eae ©空席管理システムへ,全ての購入希望座席の購入確定|4))トランザクションを開始する。 を依頼する。(⑪)座席数分,次の処理を繰り返す。 ・空慮フラグをオフにする。 ・仮予約フラグをオフにする。 (c)空席数を更新する。 (④コミットする。 ⑦全ての購入希望チケバットの購入情報を登録し,購入番 号を設定する。 ミッツ下する。 図5チケットの販売処理の概要(未完成) 図5の内容のレビューを行った。レビューでの指摘内容と対策を表2に示す。 表2指摘内容と対策(未完成) 複数の会員が,ほぼ同時に,販売管理システムでの空席確認が,そのままチケッ を購入しようとした場合,排他制御によっ|購入中となるように,②の処理をやめて,OOM て,会員に不便を強いるおそれがある。|理の購入フラグをオンにする条件に|う|が 例えば,後から購入しようとした会員は,||え|であることを追加する。さらに,全ての 先に購入しようとした会員の購入処理が完|対象座席を②こ⑦でまとめて処理しているのを,ロ 了するまで待たされてから,ックの期間を短縮するために,1座席ごとに処理し ことが判明する。て[|お|]するよう変更する。 〔レプリケーションの設計〕 チケット販売委託先に,空席管理システムを介して,空席情報を表示するサービス も提供する。そのため,データベースへの大量のアクセスによるロックの解放待ちの 多発が見込まれるので,空席情報表示用のレプリケーション先のテーブル(以下,レ プリカデータという)を作成することにした。レプリカデータのテープル構造は,図 1の空席管理システムの主なテーブルと同等なものとし,サービスの提供先ごとにレ プリカデータを用意する。レプリカデータの運用について図6に示す。さらに,ME の内容のレビューを行った。レビューでの指摘内容と対策を表3に示す。 公演を計画し,チケットを販売するに当たって,空席管理システムのテーブル(以下,オリジナ ルという)に登録する各種データを作成する。 チケットの販売開始前に,バッチ型のレプリケーション機能を使用して,対象となるチケットに 関連するテーブルの行を,一定間隔で反映するように設定した上で,各種データをオリジナルに 入力する。 具体的には,空席確認。購入などのトランザクションを阻害しないように,オリジナルをレプリ ケーション元,レプリカデータをレプリケーション先として,“"テーブルと [|イ|]テーブルを対象に,|ウ|する機能を使用する。 図6レプリカデータの運用(未完成) 表3指摘内容と対策(未完成) レプリカデータにアクセスするタイミングによっ|イベント型のレプリケーション機能を適用す て,次のように,表示する空席情報が不正になる場|る。対象とするテーブルとその列を表AOE 合がある。うに設定する。ただし,レプリケーションの 1.購入された座席が空席として表示される。性能への影響を抑えるため,対象は必要最低 限のものとする。 表4イベント型レプリケーション機能の設定内容(未完成) —— a a pT

共通の文章

データウェアハウスに関する次の記述を読んで,設問1ー3に答えよ。 A社は,家庭日用品,DIY用品などを販売するホームセンタを,全国の主要都市に 展開している。A社では,RDBMSの機能を用いた販売情報分析システムを運用して おり,Fさんがテーブルの設計を見直すことになった。 [業務の概要〕 1①)A社の営業本部は,全国を10地域に分けて販売情報を分析している。 (2)A社の店舗は,全部で300店あり,店舗が一つもない地域はない。 (3)分析対象の商品は100,000点あり,商品分類によって分類される。商品には,時 期と地域によってよく売れるものもあれば,全く売れないものもある。 (4)商品分類には10個の大分類と200個の小分類がある。大分類が家庭日用品なら ば,小分類の一つは鍋である。小分類が一つもない大分類,商品が一つもない小分 類はない。商品は,小分類に分類後,その分類が変更されることはない。 (5)会員には個人会員と法人会員があり,会員地域コードが設定される。法人会員に は,担当する社員が登録後に1名決められる。分析対象は10,000会員である。 [見直し前の主なテーブル〕] 見直し前の主なテーブル構造を図1に,主な列の意味・制約を表1に示す。 カレンダ(年,A,日,曜日名,四半期名,…) 地域(地域コード,地域名,…) 店舗(店舗コード,店舗名,店舗地域コード,…) 社員(社員ID,社員名,店舗コード,…) 大分類(大分類コード,大分類名) 小分類(小分類コード,小分類名,大分類コード) 商品(商品コード,商品名,小分類コード,…) 年代(年代,年齢下限年齢上限) 個人会員(個人会員番号,氏名,会員地域コード,性別,AER,…) 法人会員(法人会員番号,社名,会員地域コード,担当社員ID,…) 個人売上(|。|,個人会員番号,商品コード,販売額,クーポン額,数量,…) 法人売上(Lo),法人会員番号,商品コード,販売額,クーポン額,数量,…) 注記網掛け部分は,主キーであるが表示していない。 図1見直し前の主なテーブル構造(一部省略) 表1主な列の意味・制約 Foe ie oe 3列で日付を表す。それぞれ‘yyyy’BX,‘MM’BH,‘DD’形式の on CHAR#4, 個人会員番号,|個人会員又は法人会員をそれぞれ識別する8桁の番号。ただし,個人会員番号 法人会員番号|及び法人会員番号の付与は,互いに無関係でそれぞれ独自に行っている。 Bie Me,aie: 年代年代は,個人会員の年齢の層を識別する数値で,年齢の下限値と上限値によっ egて区別する。年代には,次のいずれかが設定される。 pe ie 20 meARHH:1,20歳代:2,30歳代:3,40歳代:4,50歳代:5,60歳代: 6,70歳以上:7 会員がレジで受け取るレシートに商品ごとに印字された金額。販売額は商品の 販売額店頭価格。クーポン額は,会員が広告チラシ又はスマートフォンでクーポンを aE提示したときに適用した金額で,適用しなかった場合,NULLが設定される。 なお,売上額は,会員が支払った金額(販売額からクーポン額を差し引いた金 i)CHS, [見直しの方針] 1.テーブルの統合 これまで個人会員及び法人会員に関する情報をそれぞれ別テーブルに記録してい たが,販売情報を分析するSQL文を簡素にするため,次のように統合する。 ・個人会員",“法人会員"”テーブルを“会員"”テーブルに統合する。 "個人売上",“法人売上テーブルを“SEL”テーブルに統合する。 なお,会員番号の付与方法は変えたいものとし,EK,統合に伴うテーブルの定 HWS,データ型,制約など)の変更は必要最小限とする。 2.サマリテーブルの作成 これまで分析用SQL文は,図1中のテーブルを直接アクセスしていたが,処理 時間を改善するため,“SEE”テーブルを集計したサマリテーブルを作成する。 〔テーブルの統合〕 “個人会員",“法人会員”テーブルに定義されていた制約は,それぞれ表2,3の とおりであった。 表2“個人会員”テーブルの制約の定義(未完成) FOREIGN KEY年代REFERENCES年代(年代)ON DELETE RESTRICT 表3“法人会員”テーブルの制約の定義(未完成) 約FOREIGN KEY担当社員ID REFERENCES[|b|ON DELETE SET NULL 見直し後の“会員",“GE”テーブルのテーブル構造を,図2に示す。 Fさんが調べたところ,既存の会員番号をそのまま移行したのでは不都合が起きる ことが分かったので,“BA”テーブルに会員区分を追加し,個人会員には‘A’の 値を,法人会員にはB'の値をそれぞれ設定することにした。 売上(年,A,日,店舗コード,レジ番号,レシート番号,レシート明細番号, BARD,会員番号,商品コード,販売額,クーボン額, 図2見直し後の“会員",“RL”テーブルのテーブル構造(一部省略) Fさんは,次の規則に基づいてテーブル定義表を作成し,テーブルを定義した。 (1)データ型欄には,データ型を記入する。 (2)NOT NULL欄には,NOT NULL制約を設定する場合にYを記入し,そうでなけ ればNを記入する。 (3)格納長欄には,RDBMSの仕様に従って格納長を記入する。 (4)索引の種類と構成列欄には,作成する索引を記入する。 ・索引の種類には,P(主キー索引),U(ユニーク索引),NU(非ユニーク索引) のいずれかを記入し,各索引の構成列には構成列の順番に1からの連番を記入 する。 ・制約欄には,参照制約,検査制約を,SQLの構文で記入する。 Fさんが作成した見直し後の"会員"”テーブルのテーブル定義表を,表4に示す。 oo Ge 表4見直し後の“会員"テーブルのテーブル定義表(未完成・一部省略) ie Eee|sor|ine A 項目ーー。索引の種類と構成列 janes lcmaeo=|Ee|fit||| 会員番号。|CHARG| Y|7 f2/||| 会員名。|WRcHAR⑩O| Y[|||||| 全員地載コード|CHARO_|し[しょ]||fil|| 人cur|Ge[|fT||| 和和人[swat|Ce|ff mp foo|Ce)||||il FOREIGN KEY会員地域コードREFERENCES||ON DELETE RESTRICT 参照制約|FOREIGN KEY年代REFERENCES ON DELETE i FOREIGN KEY担当社員ID REFERENCES ON DELETE}i| weaetalyg|CHECK((ma="8 AND[」|ann AND[」]) OR(会員区分='B'AND性別IS NULL AND 7 注記網掛け部分は表示していない。 [見直し後の販売情報の分析] 販売情報の分析では,例えば,販売実績が非常に少なかかったケースを調べる目的で, 次のような分析(分析B1)を行っている。テーブルの設計を見直した後の分析B1 用SQL文の構文を図3に,実行結果を表5に示す。 分析B1 2020年3月の店舗コード別商品コード別売上額を調べる。ただし,店舗 コードはM1,M2及びM3に,商品コードはP1及びP2に限定する。 SELECT M.店舗コード,COALESCE(U.商品コード,'なし')商品コード, SUM(COALESCE(U.販売額,0)-COALESCE(U.クーポン額,0))売上額 FROM LEFT OUTER JOIN ON= AND AND AND WHERE GROUP BY M.店舗コード,U.商品コード 図3分析B1用SQL文の構文(未完成) 表5分析B1用SQL文の実行結果 |Ma|ae fC 〔サマリテーブルの作成 Fさんが,処理時間の改善を要望された分析用SQL文の目的を調べ,最大結果行 数を見積もった結果を,表6に示す。また,表6中の分析のうち,分析B2用SQL 文の構文を,図4に示す。 表6処理時間の改善を要望された分析用SOL文を調べた結果(AoC) 分析名分析用SQL文の目的最大結果行数 2019年の四半期名別会員区分別会員地域コード別売上額|80 2020年3月と2019年3月の店舗地域コード別大分類コード別売上額||イィ]| 2019年の月別店舗コード別大分類コード別売上客目還im| 個人会員について2020年3月の店舗コード別性別年代別売上額|しん| |B6|過去5年間の年月別小分類コード別売上人客 SELECT C.四半期名,K.会員区分,K.会員地域コード, SUM(COALESCE(U.販売額,.0)-COALESCE(U.クーポン額,9))売上額 FROM売上U INNER JOINカレンダC ONU.年=C.年ANDU.月=C.月ANDU.日=C.日 INNER JOIN会員K ONU.会員区分=K.会員区分AND U.会員番号=K.会員番号 WHERE U.年=’2019° GROUP BY C.四半期名,K.会員区分,K.会員地域コード 図4分析B2用SQL文の構文