問3 テーブル及びSQLの設計に関するデータベース設計・実装

テーマ：横持ち・縦持ちテーブル設計，SQL設計及びデータ移行

左右2分割表示：左側の問題文をスクロールしながら右側の設問に解答できます

長文事例本文

図表クリックで拡大

問3　テーブル及びSQLの設計に関する次の記述を読んで，設問1，2に答えよ。

A社は，全国の主要都市に家電販売チェーン店を展開している。A社では，RDBMSの機能を用いて販売分析支援システム（以下，システムという）を運用しており，Fさんがテーブル及びSQLの設計を見直すことになった。

〔業務の概要〕
(1) 店舗は，営業本部の下で全国展開され，店舗コードで識別される。
(2) 店舗で販売を担当する社員は，いずれか一つの店舗に配属され，社員IDで識別される。各店舗には複数の社員が配属される。
(3) 商品は，商品コードで識別される。

〔システムの概要〕
1. 主なテーブル構造
主なテーブル構造を，図1に示す。ここで，テーブルの行は追加された順に並び，同じページに異なるテーブルの行が格納されることはない。また，索引のキー順に，ページ単位で順次又はランダムに磁気ディスク装置（以下，ディスクという）からバッファに読み込まれる。

 拡大
図1 主なテーブル構造（一部省略）


2. システムの運用の概要
(1) 各店舗は，閉店後の夜間に当日の売上明細ファイルを，システムに送信する。
(2) システムは，各店舗から送信された売上明細ファイルのデータを店舗コード別商品コード別に集計し，翌朝までに“月別売上”テーブルに反映させる。
(3) 営業本部の担当者は，システムを用いて販売分析を行う。また，担当者は店舗の社員に電話をかけて販売状況を問い合わせることがある。

3. 営業本部からの要望及び対応の方針
営業本部からの要望のうち，Fさんに対応を任せられた要望と，Fさんによる対応の方針は，次のとおりである。Fさんがこれらの方針に従って変更した二つのテーブル構造を，図2に示す。
要望1　売上データの分析を行うための照会の応答時間を改善してほしい。
方針1　“月別売上”テーブルを，“月別売上B”テーブルのように変更する。
要望2　社員連絡先の電話番号を3個以上登録できるようにしてほしい。
方針2　“社員連絡先”テーブルに新たな列を追加するのではなく，“社員連絡先B”テーブルのように変更する。

 拡大
図2 変更した二つのテーブル構造（一部省略）


〔“月別売上”テーブルの構造の変更〕
Fさんは，“月別売上”テーブルの構造の変更を，次のように検討した。
1. “月別売上”テーブルには，行が主索引のキー順にロードされている。その全行をアンロードしたファイルを，“月別売上B”テーブルの構造に従って変換し，“月別売上B”テーブルに主索引のキー順にロードした。
2. RDBMSの機能を用いて，テーブルの統計情報を取得した。“月別売上”テーブルと“月別売上B”テーブルの統計情報及び索引定義情報を，表1に示す。
3. 次の二つの分析処理を選び，照会の応答時間を評価した。その指標として，各分析処理に必要なディスクからの読込み行数及び読込みページ数を，表1の統計情報を基に比較した。
分析処理1　指定した1店舗について，任意の1年間の売上データを分析する。
分析処理2　指定した1商品について，任意の月の売上データを分析する。

 拡大
表1 “月別売上”テーブルと“月別売上B”テーブルの統計情報及び索引定義情報


(1) 表1の二つのテーブルでは，複数行を索引のキー順に読み込む場合，アクセス経路が ア 索引のとき，ページは順次に読み込まれるが，アクセス経路が イ 索引のとき，1行当たり1ページがランダムに読み込まれる。
(2) 分析処理1では，分析に必要な“月別売上”テーブルの1店舗当たりの年間平均行数は， ウ 行である。これらの行を，主索引を用いてディスクから読み込むとき，最小限 エ ページ読み込む必要がある。
一方，“月別売上B”テーブルの1店舗当たりの年間平均行数は，指定した1年間が年をまたがらなければ， オ 行である。これらの行を，主索引を用いてディスクから読み込むとき，最小限 カ ページを読み込めばよい。しかし，その1年間が年をまたがれば，読込みページ数は キ 年間分の ク ページに増える。
(3) 分析処理2では，分析に必要な行数は，二つのテーブルとも1商品コード当たり最大 ケ 行である。これらの行を，副次索引を用いてディスクから読み込むとき，最大 コ ページ読み込む必要がある。

4. プログラム中のSQLへの影響を調べた。調べたのは，同じ年の二つの月，例えば，2017年1月と2017年2月の売上額の差を求めるSQLで，その構文を表2中のSQL1に示す。テーブル構造を変更した後で，SQL1と同じ結果行を得るために，実行の都度，比較する年月に対応したSQLの構文を組み立て，動的SQLで実行することにした。その構文を表2中のSQL2に示す。

 拡大
表2 同じ年の二つの月の売上額の差を求めるSQLの構文（未完成）


〔“社員連絡先”テーブルの構造の変更〕
Fさんは，“社員連絡先”テーブルの構造の変更を，次のように検討した。
1. “社員連絡先”テーブルの電話番号1列と電話番号2列の値を調べたところ，プログラムの不備による次のような問題の行があることが分かった。
問題1　電話番号1列と電話番号2列は，異なる電話番号であるべきところ，同じ電話番号が設定されている行があった。
問題2　電話番号1列だけに電話番号を設定すべきところ，電話番号1列にNULLが，電話番号2列に電話番号が設定されている行があった。
問題3　電話番号が設定されている場合だけ行を登録すべきところ，電話番号1列と電話番号2列の両方にNULLが設定されている行があった。
2. 問題1～3を防ぐには，“社員連絡先”テーブルに，図3に示す検査制約を定義すべきであった。ここで，検査制約は，次の①～④のいずれかの述語を組み合わせて指定する。
①　電話番号1 IS NOT NULL
②　電話番号1 IS NULL
③　電話番号2 IS NOT NULL
④　電話番号2 IS NULL

```sql
CHECK ( ( c AND d AND 電話番号1 <> 電話番号2 )
     OR ( c AND e ) )
```
図3 “社員連絡先”テーブルに定義すべきだった検査制約（未完成）

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の見出しは列名を表す。

 拡大
図4 “社員連絡先B”テーブルに行を移行するINSERT文（未完成）


表3 移行元の行
| 社員ID | 電話番号1 | 電話番号2 |
| :--- | :--- | :--- |
| E1 | 1111 | 3333 |
| E2 | 2222 | 2222 |
| E3 | 3333 | NULL |
| E4 | NULL | 4444 |
| E5 | NULL | NULL |

表4 移行先の行（未完成）
| 社員ID | 表示順 | 電話番号 |
| :--- | :--- | :--- |
| E1 | 1 | 1111 |