データベーススペシャリスト午後I 事例記述平成30年 春期
午後I 問3
問3 物理データベースの設計及び実装
テーマ：物理データベースの設計及び実装、制約・索引・表領域・アクセスパス・作業工程の検討
左右2分割表示：左側の問題文をスクロールしながら右側の設問に解答できます
長文事例本文
図表クリックで拡大問3 物理データベースの設計及び実装に関する次の記述を読んで，設問1，2に答えよ。
A社は，都市近郊で複数の日帰り温泉施設（以下，施設という）を運営している。A社では，リピータを増やすために，全施設で利用できる会員カードを希望する客に発行し，新たにシニア向けの割安な利用料金を設定することにした。また，この計画に伴いシステムを拡張することが決まり，Fさんが物理データベースの設計及び実装（以下，物理DB設計及び実装という）を担当することになった。
〔RDBMSの主な仕様〕
(1) テーブル及び索引のストレージ上の物理的な格納場所を，表領域という。
(2) RDBMSとストレージ間の入出力単位を，ページという。同じページに異なるテーブルの行が格納されることはない。
(3) 索引は，ユニーク索引と非ユニーク索引に分けられる。
(4) 索引は，クラスタ性という性質によって，高クラスタな索引と低クラスタな索引に分けられる。
・高クラスタな索引：キー値の順番と，キーが指す行の物理的な並び順が一致しているか，完全に一致していなくても，隣接するキーが指す行が同じページに格納されている割合が高い。
・低クラスタな索引：キー値の順番と，キーが指す行の物理的な並び順が一致している割合が低く，行へのアクセスがランダムになる。
(5) DMLのアクセスパスは，RDBMSによってテーブル及び索引に関する統計情報に基づいて索引探索又は表探索に決められる。ただし，次の場合は除く。
・WHERE句のANDだけで結ばれた等値比較の対象列がユニーク索引のキー列に一致している場合：統計情報にかかわらず，その索引の索引探索に決められる。
・統計情報からテーブルが空（0行）と判断した場合：表探索に決められる。
(6) 主キー制約及びUNIQUE制約は，制約を定義する列の構成に一致したユニーク索引がRDBMSによって自動的に作成されることで一意性が保証される。
〔施設運営及び会員カードの概要〕
1. 施設運営
(1) 営業時間帯は，9:00〜24:00である。
(2) 各施設には，エステ，理容，食事処，売店など，一つ以上の店舗がある。
(3) 受付では，客が一人1枚ずつ入館券を購入し，入館券ごとに腕輪付きロッカー鍵（以下，鍵という）を一つ受け取り，帰るときに返却する。
(4) 客は，鍵のバーコードに記録されている鍵番号を店舗のレジに読み取らせることで，店舗の有料サービスを含む商品を利用できる。レジに記録されたデータは，客が精算するまでにシステムのデータベースに送られる。
(5) 未精算の利用額は，退館時に複数台のいずれかの精算機で精算する。
(6) 同じ客が同じ日に，同じ施設を複数回，利用することができる。この場合，入館券を再度，購入する必要がある。
2. 会員カード
(1) 客は，会員カード申込書（以下，申込書という）に必要な情報（氏名，生年月日など）を記入する。未就学児の入館は無料なので申込対象外である。
(2) 客は，会員IDが刻印された会員カード（申込書に添付）を受け取り，その日から利用することができる。会員には，鍵を用いた利用の額に応じてポイントを付与する。ポイントは，次回以降の施設利用に使うことができる。
(3) システムは，翌朝の開館までに申込書に基づいて会員情報を登録し，また，毎日の営業時間外に，会員の生年月日に基づいて年齢及び年齢区分を設定する。
(4) 登録の翌日以降，客は，会員カードを提示して，年齢に応じた入館券を購入する。その際，年齢を示す証明書の提示を求められることはない。
〔物理DB設計及び実装〕
1. 物理DB設計及び実装の前提
(1) テーブル構造は，既に決まっている。
(2) テーブルごとの行数は，過去の実績及び伸び率の予想に基づいて見積もる。
(3) 利用実績データは，分析のために1年分を保存する。
(4) システムで用いられるユーザ，ロールは，定義済みである。
(5) プログラム開発者は，DMLを設計し，プログラムの開発・単体テストを行う。
(6) 索引は，プログラム開発者が設計したDMLに基づいてFさんが設計を行う。
2. 主なテーブル構造及び主な列の意味と制約
主なテーブル構造を図1に，主な列の意味・制約を表1に示す。また，“会員”テーブルの年齢区分と年齢の組合せを限定する検査制約を，図2に示す。
拡大
施設（施設ID，施設名，住所，代表電話番号，…）
店舗（施設ID，店舗ID，店舗名，内線番号，…）
商品（商品ID，商品名，価格，ポイント数，商品説明，…）
会員（会員ID，氏名，性別，年齢区分，年齢，生年月日，住所，ポイント繰越数，…）
券（利用年月日，施設ID，券番号，入館時刻，年齢区分，入館料，会員ID）
鍵（施設ID，鍵番号，券番号，使用中フラグ）
店舗利用（利用時刻，施設ID，店舗ID，鍵番号，商品ID，利用額，ポイント数，未精算フラグ）
精算（利用年月日，施設ID，券番号，精算時刻，利用額合計，会員ID，ポイント消費数）
精算明細（利用年月日，利用時刻，施設ID，券番号，店舗ID，商品ID，利用額，ポイント数）
注記 年齢列のデータ型はSMALLINT型，商品説明列及び住所列はVARCHAR型である。
拡大
表1 主な列の意味・制約
・店舗ID：施設内の各店舗（エステ，理容，食事処，売店など）を識別する文字列
・内線番号：各店舗に設置されている内線電話を施設内で識別する番号
・商品ID：全店舗で提供される商品を識別する文字列
・会員ID：会員を識別する文字列（会員カードの裏面にバーコードで刻印されている）
・年齢区分：子供（12歳未満）：'1'，大人（12歳以上60歳未満）：'2'，シニア（60歳以上）：'3'
・券番号：入館券を発行する都度，日ごと施設ごとに付与される1から始まる連番
・鍵番号：施設内のロッカーを識別する番号。客の精算後，鍵は再利用される。
・使用中フラグ：鍵が使用中の場合：'Y'，鍵が使用中でない場合：'N'
・未精算フラグ：利用額が未精算の場合：'Y'，精算済みの場合：'N'
```sql
CHECK( ( 年齢区分 = '1' AND a ) OR ( b AND c ) OR ( 年齢区分 = '3' AND d ) )
```
図2 年齢区分と年齢の組合せを限定する検査制約（未完成）
3. 物理DB設計及び実装の作業工程表
Fさんが作成中の物理DB設計及び実装の作業工程表を，表2に示す。
拡大
表2 物理DB設計及び実装の作業工程表（未完成）
・作業順1，作業ID W1，作業名 テーブル設計：テーブル名，列名，列データ型，主キー制約及びNOT NULL制約を決めて，CREATE TABLE文を設計する。
・作業順2，作業ID W2，追加制約設計：UNIQUE制約，検査制約及び参照制約を決めて，これらの制約を追加するALTER TABLE文を設計する。
・作業順3，作業ID W3，アクセス権限設計：ユーザごと又はロールごとにテーブルのアクセス権限を決めて，GRANT文を設計する。
・作業順4，作業ID W4，追加索引設計：DMLのアクセスパスを想定し，性能向上のために追加索引を設計する。
・作業順5，作業ID W5，表領域設計：テーブル及び索引のストレージ所要量を見積もり，表領域をストレージに割り当てる設計を行う。
・作業順6，作業ID W6，DML性能予測：DMLの結果行数及び読込みページ数を机上で予測する。
・作業順7，作業ID W7，DDLなど実行：表領域をストレージに割り当て，CREATE TABLE文，ALTER TABLE文及びGRANT文を実行する。
・作業順8，作業ID W8，統計情報取得及びアクセスパス確認：テーブル及び索引に関する統計情報を取得し，DMLのアクセスパスが想定どおりかどうか確認する。
・作業順9，作業ID W9，性能測定用データ設計・データ生成：性能測定用データを生成するための設計書を作成し，必要なプログラムの開発後に性能測定用データを生成する。注1）
・作業順10，作業ID W10，ロード実行：テーブルに性能測定用データをロードする。
・作業順11，作業ID W11，DML性能測定：DMLの性能を測定し，目標を達成するかどうか確認する。
注1）“店舗利用”テーブルについては，1日分の性能測定用データを生成する。
(1) 作業W4（追加索引設計）で，利用額の精算時に実行されるDMLの例を表3に，FさんがDML2及びDML3のために追加した索引を表4に示す。
拡大
表3 利用額の精算時に実行されるDMLの例
・DML1：
`SELECT 券番号 FROM 鍵 WHERE 施設ID = :施設ID AND 鍵番号 = :鍵番号`
・DML2：
`INSERT INTO 精算明細( ) SELECT :利用年月日, 利用時刻, 施設ID, :券番号, 店舗ID, 商品ID, 利用額, ポイント数 FROM 店舗利用 WHERE 施設ID = :施設ID AND 鍵番号 = :鍵番号 AND 未精算フラグ = 'Y'`
・DML3：
`SELECT FROM 精算明細 WHERE 利用年月日 = :利用年月日 AND 施設ID = :施設ID AND 券番号 = :券番号`
注記1 網掛け部分は表示していない。
注記2 ホスト変数の利用年月日には，当日の現在日付が設定される。
表4 DML2及びDML3のために追加した索引
・索引1：設計対象のDML DML2，テーブル名 店舗利用，索引のキーの構成 施設ID，鍵番号，未精算フラグ
・索引2：設計対象のDML DML3，テーブル名 精算明細，索引のキーの構成 利用年月日，施設ID，券番号
(2) 作業W5（表領域設計）で，可変長列があるテーブルのストレージ所要量を見積もる計算の手順を，表5に示す。また，作業W6（DML性能予測）で，DML性能の指標としてDMLのテーブルからの読込みページ数を，表5の見積結果を用いて予測する計算の手順を，表6に示す。
拡大
表5 可変長列があるテーブルのストレージ所要量を見積もる計算の手順（未完成）
手順1：テーブルの見積行数，平均行長（バイト）及び最大行長（バイト）を得る。
手順2：RDBMSで指定できるページ長（バイト）の中から，アの行を1行以上格納できるページ長を決め，ページ長からヘッダ部を除いた有効ページ長（バイト）を得る。
手順3：テーブルに行をロードした後，ページ当たり1行以上挿入したい場合，次の不等式を満たす適切な空き領域率（%）を決める。
平均行長 ≦ イ × 空き領域率 ÷ 100
手順4：次の計算式によって，テーブルのストレージ所要量（バイト）を求める。
ページ当たりの平均行数 ＝ イ ÷ ウ の小数点以下を切捨て
必要ページ数 ＝ エ ÷ オ の小数点以下を切上げ
テーブルのストレージ所要量 ＝ カ × キ
表6 テーブルからの読込みページ数を予測する計算の手順（未完成）
手順1：DMLのアクセスパスを想定し，そのアクセスパスを用いてページから読み込む探索行数を予測する。結果行数＝探索行数とは限らない。
手順2：
・アクセスパスが表探索の場合：読込みページ数 ＝ ク
・アクセスパスが索引探索の場合，かつ，
索引が低クラスタな索引の場合：最大読込みページ数 ＝ ケ …A
索引が高クラスタな索引の場合：最小読込みページ数 ＝ ケ ÷ コ の小数点以下を切上げ …B
・アクセスパスが索引探索の場合，読込みページ数はAとBの間の値になる。
〔G部長の指摘〕
表2の作業工程表について，Fさんは，表に示した作業順で逐次，各作業を行うつもりであったが，G部長から次のような指摘があった。
指摘1 作業を並行して進めることで，作業工程期間を短縮できる。
指摘2 作業W8（統計情報取得及びアクセスパス確認）の作業順が8番目では，表3のDMLのアクセスパスが適切に決められない。作業W8を，作業[ あ ]の後に行うべきである。
拡大図1 主なテーブル構造（一部省略）
拡大表1 主な列の意味・制約
拡大表2 物理DB設計及び実装の作業工程表（未完成）
拡大表3 利用額の精算時に実行されるDMLの例
拡大表5 可変長列があるテーブルのストレージ所要量を見積もる計算の手順（未完成）
設問一覧 ＆ 解答・演習
まずは解答を作成してみましょう。「解答例を見る」をクリックするとIPA公式解答例と採点基準が開きます。
設問1 (1)記号・選択式
表2の作業工程表について、作業W2（追加制約設計）で“店舗”，“精算”の各テーブルにUNIQUE制約を設計する場合について，UNIQUE制約を定義する列の構成（列名又は列名の組合せ）を，それぞれ一つ答えよ。なお，UNIQUE制約がない場合，“なし”と答えよ。
※直接入力してください
設問1 (2)記号・選択式
作業W2（追加制約設計）について，図2中の a 〜 d に入れる適切な述語を一つずつ答えよ。
※直接入力してください
設問1 (3) ①記号・選択式
表4の索引について，索引1は，ユニーク索引又は非ユニーク索引のどちらに該当するか答えよ。
※直接入力してください
設問1 (3) ②記述問題
制限字数：35 字以内
表4の索引について，索引2は，高クラスタな索引である。その理由を35字以内で述べよ。
0 / 35 文字
設問1 (4)記号・選択式
作業W5（表領域設計）について，表5中の ア 〜 キ に入れる適切な字句を，表5中の下線部分の用語を用いて答えよ。
※直接入力してください
設問1 (5)記号・選択式
作業W6（DML性能予測）について，表6中の ク 〜 コ に入れる適切な字句を，表5，6中の下線部分の用語を用いて答えよ。
※直接入力してください
設問2 (1)記号・選択式
〔G部長の指摘〕について，指摘1について，Fさんは，他の三つ以上の作業と並行して進められる作業としてW3とW9の二つを選んだ。作業順を変えた後の，直前の作業及び直後の作業の作業IDを，それぞれ答えよ。
※直接入力してください
設問2 (2)記述問題
制限字数：40 字以内
指摘2について，アクセスパスが索引探索でなく表探索に決められるDMLを，表3から全て答え，表探索に決められる理由を，W8の作業順の観点から40字以内で述べよ。
0 / 40 文字
設問2 (3)記号・選択式
指摘2について， あ に入れる適切な作業IDを，一つ答えよ。
※直接入力してください
午後I長文記述の得点力を引き上げる定番対策書Amazon・楽天 公式提携
PR
IPA 厳選対策書徹底攻略 データベーススペシャリスト教科書 令和8年度株式会社わくわくスタディワールド 瀬戸 美月 著 / インプレス
DB徹底攻略株式会社わくわくスタディワールド 瀬戸 美月 著 / インプレス
徹底攻略 データベーススペシャリスト教科書 令和8年度
💡 推薦ポイント:午後I・午後IIの概念データモデル穴埋めや排他制御・性能設計の記述問題で確実に合格点を勝ち取る実践テクニックが満載です。
¥3,278（税込）
※本リンク先はプロモーション（アフィリエイト広告）が含まれています
【出題趣旨】IPA公式発表
【採点講評】IPA公式発表
本問に関連するデータベーススペシャリスト重要用語解説
本番形式・制限時間付きで演習する
平成30年 春期 午後I（3問中2問選択・90分）を実戦演習モードで通し解答し、総合採点を受けられます。