ビューとインデックス

(2005.11.29更新)

先週に引き続き、ＳＱＬＳｅｒｖｅｒで実習を行う。

今回SQLサーバに接続するための　ＩＤ　と　パスワード　は、　先回用いたものと同じものである。

ビュー
　VIEW = 仮想表

１つあるいは幾つかのテーブルを組み合わせてＳＥＬＥＣＴ文で作成した一時的な表のことをビューと呼ぶ。

データベースで実際にレコードを記録するテーブルを実表と呼ぶ。
ビューは実表からselect文で作成された仮想表である。

上記は、
　2つの実表を、　商品番号フィールドで結合して、１つの表にした
例である。

ビューの作成方法：

・Accessでは、いつもの手順どおり選択クエリーを作成すればよい。　選択クエリーは、SQLのSelectを実行するクエリーである。つまり、選択クエリーを実行したときに表示される表がビューに相当する。
クエリーによるビューに名前を与え、他のクエリからテーブルとして参照することが出来る。
Ａｃｃｅｓｓでは、選択クエリーに付けた名前がビューの名前になり、他のクエリーからその名前で参照できるようになる。

・ＳＱＬＳｅｒｖｅｒでビューに名前を付けて保存しておくには、以下の構文を用いる。

　ＣＲＥＡＴＥ　ＶＩＥＷ ビューの名前　(表示項目,....) AS SELECT文

ＡＳ以下には以前に解説したSELECT文を書くことが出来る。
（）の部分は、ＡＳ以下のSELECTの実行の結果、得られる表に（）内で指定したフィールド名を付け直して、新たなフィールド名を与える為の項目である。

ビューの参照：

select や delet や update　などで、ＦＲＯＭキーワードで表名を指定する代わりに、ビューを指定することができる。

ビューの必要性

データベースの利用時に実際に表示したり印刷する表は、実はビューである場合が多い。
では、なぜ、ビューをテーブル（実表）として最初からＤＢＭＳに用意しないのだろうか？
ビューは次のような場合に必要となる。

　・テーブルの正規化により分割されたテーブルを元のテーブルに戻るように結合して表示する
　・テーブルを利用者の用途に応じて必要な部分だけ表示する

もし、データベースマネジメントシステム（ＤＢＭＳ）中に、予め必要となるテーブルを実表として全て用意しておくと、データの重複を生じ無駄であるばかりでなくデータの追加・更新・削除においても非常に手間を生じる。
そこで、ＤＢＭＳ中には必要最小限の実表だけを用意し、実表以外のテーブルが必要な場合はビューとしてその都度クエリーで実表から組み立てなおして表示する方が良い解決法となる。
このように、利用時とデータの格納時で別々にデータを扱うために考案されたのが、３層スキーマと呼ばれるＤＢＭＳの構造である。

３層スキーマ構造：
ＤＢＭＳを設計する際の基本的概念。ＤＢＭＳを運用する際には、各スキーマ間で独立性が保たれていることが望ましい。

（ユーザー側）	外部スキーマ（ビュー）	概念スキーマＲＤBＭＳでは、テーブルとリレーション	内部スキーマ	（ハードウェア側）
（ユーザー側）	職員：履修登録（記録・修正）学生：自分の成績表（表示のみ）教員：担当科目成績表（記録・修正）	開講科目表（科目ＩＤ、担当者、開講日時、教室）科目表（科目ＩＤ、科目名、単位数、種別）学籍簿（学籍番号、氏名）履修表（履修時期、科目ＩＤ、担当者、学籍番号、成績）教員表（氏名、所属、研究室）	ディスクドライブファイルネットワークセキュリティバックアップ検索システム（インデックス）	（ハードウェア側）

各スキーマの独立性が高い場合、以下の利点がある。
　・データベースの設計者は、ＤＢＭＳがどの様なプログラムから出来ているか（内部スキーマ）、言い換えればＤＢＭＳのソフトの種類によらず、データベースを構築できる。
　　　⇒上の表でいうと、ＲＤＢＭＳであれば、どのＤＢＭＳを利用してもテーブルとリレーションという概念でデータベースを設計できる。
　・データベースの利用者は、どの様にデータベースが表現されているか（概念スキーマ）を意識せずにデータベースを利用できる。
　　　⇒上の表でいうと、ユーザはクエリーで予め用意されたビューを利用するだけでよく、テーブルの構造を意識しなくて済む。

例）
MS-ACCESSで製作された成績処理ＤＢＭＳがWindowsマシンのＣ：ドライブに存在するとする。

ユーザ”教学課員”は、このデータベースがどんなマシンのどこに存在するかやどんなＤＢＭＳを利用しているかに影響されずに、履修関係の業務をこなせたほうがよい。
ユーザ”教員”は、このデータベースがどんなマシンのどこに存在するかやどんなＤＢＭＳを利用しているかに影響されずに、成績を記録できた方がよい。
ＤＢＭＳの設計者”ＭＳ－Ａｃｃｅｓｓ開発者”は、このＤＢＭＳが誰がどんな用途で利用するかにとらわれずに、ＤＢＭＳを設計できた方がよい。

演習課題１：

SQLサーバに接続し、以下を行う。

SQLサーバーの利用２

演習室PCとは別の場所にあるSQLサーバにネットワークで接続し、サーバー上のデータベースで共同作業を行う。

ＳＱＬサーバー接続用のアカウントとパスワードの用紙を受け取る。
なお、この用紙についてを紛失・落とす・忘れてくる（次回も使用する）などの管理不行き届きがある場合は、データベース演習の単位は「不可」とする。
ログイン用パスワードで、ＳＱＬサーバーに接続する。
1. スタート→プログラム→コマンド・プロンプト
2. SQLサーバー（cyteen）に接続します。
  osql -S cyteen -U アカウント
3. パスワードを入力する
ここから先は先週のＳＱＬサーバーの演習を完了していないと出来ません。
出来ていないものは、先週の演習を見直すこと。

???????の部分は自分の学籍番号を利用すること。

use db???????
go
学籍簿を作成するcreate table STUDENT ( ID integer, NAME char(12) )
go
番号１、名前１は自分のものを利用する。名前には漢字を利用できる（ALT+半角全角キー） insert into STUDENT values (番号１, '名前１' )
go
もう一名、適当にデータを追加しておく
insert into STUDENT values ( 番号２, '名前２' )
go
科目表を作成する
create table CLASS ( CLASS_ID integer, NAME char(12) )
go
insert into CLASS values ( 1, 'DataBase' )
go
履修表を作成するcreate table STUDIES ( ID integer, CLASS_ID integer)
go
自分の番号で、科目番号１の科目の履修を記録するinsert into STUDIES values (番号, 1 )
go

ビューを作成する（名前と科目名の表）コンマやピリオドの打ち間違いに注意する。
※途中に入力ミスがあった場合は、create view の部分からやり直すこと。

create view STUDY_LIST (name , class) as
select student.name, class.name
from student, studies, class
where student.id=studies.id and studies.class_id=class.class_id

　↓　実表とビューの関係図(各テーブルのレコードは上の演習内容よりも多めに記録してある)

実表

STUDENT

ID	NAME
1122333 1122444 1122555	kobashi hasegawa yokota

CLASS

CLASS_ID	NAME
1 2	DataBase Program

STUDIES

ID	CLASS_ID
1122333 1122333 1122444 1122555	1 2 2 1

ビュー

STUDY_LIST

NAME	CLASS
kobashi kobashi hasegawa yokota	DataBase Program Program DataBase

go
作成したビューを表示する。
select * from STUDY_LIST
go
(STUDY_LISTを間違えて登録した場合は、 drop view STUDY_LIST でビューを破棄して作成しなおす)
あと１件、科目を登録する（データは適当でよい。コマンドの項目も自分で考える）
ヒント：　insert into の構文確認
insert into ????? ?????? (? , '????????　')
学生の履修科目を増やす。（STUDIESに登録する。コマンドの項目も自分で考える）
insert into ??????? ?????? (? , ???????　)
先程登録したビューSTUDY_LISTを表示して、履修状況を確認する。
ヒント：　ビューの内容を表示するコマンドは少し上のほうで実行したものと同じ
select ? ???? ??????????
ビューをupdateで修正し、修正結果がclassテーブルにも反映されることを確認する。
update study_list set class = 'database2' where class = 'database'
go
select * from study_list
select * from class
go

インデックス

インデックス機能は、例えて言うと、データベース中のデータに見出し（目次）を付け、データの検索効率を上げるような仕組みのこと。

データベースには、大量のデータを効率よく処理するために、データの検索を高速で行う仕組みが必要である。
データ検索の高度なアルゴリズムを利用できるようにあらかじめDBMSにはインデックスと呼ばれる機能が用意されている。
データベースの設計者は、このアルゴリズムそのものを理解していなくても、処理の効率化を図るためにインデックスを利用することができる。

テーブルのフィールドに対してインデックスを設定すると、検索などの効率を上げることができる。

データ検索アルゴリズムについて

解説ページを用意したので時間があるときに参照しておくこと。

演習課題２：

SQLサーバに接続し、インデックスを設定した場合の効果を確認する。

インデックス利用上の方針：

・　単なるデータ記録用のテーブルには、データベースはインデックスを必要としない。
データの検索や並べ替えに利用しないフィールドについて、インデックスを作成しても無駄（インデックスの作成と構造の再編の負荷が増すだけ）だからである。

・　ある程度の規模のテーブルの、頻繁に参照されるフィールドに対してインデックスを設定すると処理の効率が改善される。

SQLサーバーの利用２（つづき）

インデックスの追加１
1. create index STUDENT_INDEX on STUDENT ( ID )
2. go

STUDENT表のIDフィールドにINDEXを設定することで、IDを利用したデータベースの処理効率が改善される。

ここまでで製作したデータ数ではインデックスの効果を測定するには少なすぎるので、大量のデータに対して処理を行わせてindexの効果を測定してみる。
処理内容：
・２００００個のデータの処理にかかる時間の計測

インデックスの追加２

新しく表を用意する
create table rand (num int)
go
他のデータベースの表からランダムな値（０～９９９９９）を２００００個読込む。
insert into rand ( num ) select num from testdb.dbo.rand
ここで、testdbは前回使用した共用データベース、dboはデータベース所有者を示すキーワード。
最後のrandは表名を示す。
go
読込んだデータ数の確認
select count(*) from rand
go
平均値の確認
select avg(num) from rand
go
先頭１０個のレコードの確認（topはTransact-SQLの機能。利用するＤＢＭＳによっては利用できない場合がある）
select top 10 num from rand
go

表randにある２００００個の値をグループ化し（同じ値ごとにまとめる）、3回以上記録されている数値を表示する。
その際、実行にかかった時間（ＣＰＵの消費時間。通信時間などは除く）を表示する。

declare @a as int
set @a = @@cpu_busy
select num from rand group by num having count(num)>3
print cast( @@cpu_busy - @a as char(2)) + 'ms'

変数宣言
実行前のＣＰＵ消費時間を記録

ＣＰＵ消費時間を表示

go
消費時間（実行にかかったＣＰＵ時間）を確認する。上のＳＱＬの実行で表示された時間を覚えておく。
テーブルの先頭から１０個の値を確認する（selectのgroup by句の実行だけではレコードの順序は変更されないことを確認）
select top 10 num from rand
go
インデックスを設定する。
create index num_index on rand ( num )
go
インデックスを作成した結果、レコードが昇順に整理されたことを確認する。
select top 10 num from rand
go

再び、先ほどの処理を実行して実行時間を計測する