第 1 回 データベースの概要と SQL Server のアーキテクチャー～ MCA をめざそう!! MCA データベース Microsoft SQL Server 2000 対応 編 ～
熊倉 克己
2002年3月11日
1. データベースの役割
1.1. データベースの概要
データベースは、コンピュータシステム上に構築された記憶保持システムです。データベースシステムはデータ、ハードウエア、ソフトウエア、利用者の ４ つの要素で構成されています。リレーショナルデータベースは実用化されはじめたのは、１９８０ 年代です。当時は高性能の汎用機でないと実用的に使用できるものではありませんでしたが、マイクロプロセッサの高性能化、記憶装置の大容量化・低価格化により、ノートパソコンでも、本格的なリレーショナルデータベースが使用できる様になりました。
リレーショナルデータベースが普及した背景には、他のデータベースシステムと比較して、以下の特長があったためです。
・ プログラムとデータとの独立性の高さ
FakePre-33d57ec4add947c3b599a7e5e2f87e5e-2e017b48b4a0486a9e5d268fbdf9c02a FakePre-be29e8049eff4277ad9d4d592aecb471-fac1d04b0c324314a5d0e91c6be5516f
SQL Server は Windows 上で動作する本格的なリレーショナルデータベースで、標準 SQL の規格である ANSI SQL92 に準拠しています。
リレーショナルデータベースはビジネス分野で広く使用されていますが、文字データや数値だけでなく、マルチメディアなどのデータも取り扱える様に機能が拡張されています。また、オンライントランザクション処理 （OLTP） を主体とする基幹系データベースからデータウエアハウス（OLAP）を取り扱う情報系データベースの利用と、利用範囲も拡大しています。
1.2. データの独立性
従来の情報システムは、COBOL などの言語で書かれたアプリケーションプログラムと、プログラムで使用されるファイルで構成されていました。ファイルはプログラムに依存して設計され、プログラム毎にファイルレイアウトが異なったり、データが重複したりします。又、ファイルレイアウトを変更する場合は、プログラムのソースコードを変更する必要があります。これらの問題を解決したのがデータベースです。データベースには以下の3種類があります。
・ 階層型データベース
FakePre-dd8550e92ffd4bd689c3c9be1a066b6c-0e561cbb89944028a497685e60f69489 FakePre-171fa3a2df23446cbc7c5401343c0c8b-dfda1d4a39ba400f884c3ed3de5c85b8
リレーショナルデータベースでは、プログラムとデータの独立性が実現されています。
1.3. データの統合化
また、従来のファイル処理では、プログラムの中で、ファイルとレコードのフォーマットを指定する必要がありましたが、リレーショナルデータベースでは、データ項目の指定だけで済みます。
データベースを使用することにより、データの統合化が図れます。データの統合化の目的は以下の２つです。
・ データの重複の排除
FakePre-7ffd87806a9d4ad99fb8c22ef50d4416-5ae0e5be37734782a11b42605f8aa3fc
データの統合化は、単にリレーショナルデータベースを使用すれば達成するものではありません。
どのようにデータベースを設計するかによります。従来の ｢プロセス中心アプローチ｣ ではなく、｢データ中心アプローチ｣もしくか、｢オブジェクト指向｣ で行う必要があります。
.jpg)
図 1. データの統合化とデータ中心アプローチ
2. リレーショナルモデル
2.1. リレーショナルモデルの構造
リレーショナルデータベースは、数学の集合論に基づき設計されています。リレーショナルモデルの役割は以下の 3 つです。
・ データ定義
FakePre-9a7f0a128e2e445982e33ec9f93a74fc-4a8316462fa94e9499e6c29316fd217d FakePre-939931c940484f148282779a97b3bcf7-98758e68af964568af986dc073df39c2
データ定義
リレーショナルモデルでは、互いに関係あるデータをリレーション（関係）として扱います。
リレーションは,論理的なデータの結びつきを表現します。ＳＱＬ の用語ではテーブル（表）といいます。リレーショナルデータベースでは、複数のテーブルの集まりのデータベースといいます。リレーショナルデータベースでは、ディスクの物理的構造を意識せずに使用できます。データの独立性が保証されています。
リレーショナルデータベースのテーブルはお互いに関連するデータ要素を集めた行と各データ要素を表す列から構成されます。リレーショナルモデルでは、行を組（タプル）、列を属性（アトリビュート）と表現します。列はテーブル上でユニークな名前を持ちます。テーブルは行の論理的な集合として構成されますが。行には順番はなく、又、列にも順番はありません。列は、単一の値でなければならず。複数の値は配列はゆるされません。列のとり得る値の範囲をドメイン（定義域）といいます。例えば、在庫数量であれば、０ 以上の値となります。
2.2. リレーショナルデータベースのキー
テーブルで行を一意に特定する為の属性（列）の集まりを候補キーといいます。１ つのテーブルには、候補キーが複数存在します。例えば、社員テーブルでは、社員番号やメールアドレスが考えられます。同姓同名の社員がいる場合、氏名は候補キーとはなりません。候補キーの中から、１ つ選択し主キー （Primary Key） とします。主キーに選択されなかったキーは代替キー （Alternate Key） といいます。
あるテーブルの候補キーを参照する属性の集まりを外部キー (Foreign Key) といいます。
2.3. 整合性制約
データベースのデータが現実の値を正確に反映していることをデータの整合性が保たれているといいます。例えば、在庫数は正の数値でなければならないし、性別は、男もしくは女でなければなりません。データの正しさを保証するのが、整合性制約 (Integrity Constraint) です。整合性制約には以下のものがあります。
制約
説明
主キー制約
行を一意に特定します。これを主キー制約 （Primary Key Constraint） といいます。主キーを構成する属性の値にNULL値は許されません。
参照制約
テーブルに外部キーがある場合、その値は参照するテーブルの候補キーの値か、またはNULL値である必要があります。これを参照制約 （Referential Constraint） といいます。
ドメイン制約
列のとり得る値の範囲 （ドメイン） を定義することを、ドメイン制約 （Domain Constraint） といいます。
2.4. データ操作言語
データの操作を行う SQL 言語を、データ操作言語 （DML：Data Manipulation Language） といいます。
データ操作言語を使ってデータベースから必要なデータを、テーブル形式で問合せることができます。この場合、ディスクに格納されているテーブルを実テーブル （Base Table） といい、問合せの結果を導出テーブル （Derived Table） といいます。また使用頻度が高い問合せは、あらかじめその内容をデータ操作言語で定義しておき、あたかもテーブルと同じように使うことができます。この仮想的なテーブルをビュー （View） といいます。
リレーショナル代数演算
データ操作言語の基本となる考え方に、リレーショナル代数があります。リレーショナル代数には演算が定義されおり、代表的な演算には以下のものがあります。
制約
説明
選択演算：Selection
選択は、リレーション（テーブル）から、ある属性（列）が特定の条件を満たすタプル（行）の集合を得ることです。
例えば「商品テーブルから単価が 1,000 円以上のものを抽出する」という場合です。
SELECT * FROM product WHERE unitprice >= 1000
射影演算：Projection
射影は、リレーション（テーブル）から、特定の属性（列）を抜き出して部分集合を得ることです。
例えば「商品テーブルから商品番号、商品名、単価を抽出する」という場合です。
SELECT productid, productname, unitprice FROM product
結合演算：Join
結合は、複数のリレーション（テーブル）において、同じ属性（結合列）の値をもつタプル（行）を足し合わせて集合を得ることです。
例えば「商品テーブルと注文テーブルを商品番号で結合して、商品注文リストを作成する」という場合です。
SELECT product.*, order.* FROM product, order WHERE product.productid = order.productid
3. データモデリング
データベースは現実の世界に存在するデータを意味のあるデータとして、コンピュータ上に写像したものです。例えば、ビジネスデータ処理であれば、顧客、商品、受発注、販売に係わる活動があります。これらをデータベースに写像する作業をデータモデリングといいます。
3.1. データモデル
データモデルには以下の 2 種類があります。
データモデル
説明
概念データ
モデル
データベース化の対象となる実世界の構造を記号系で用いて記述したものが概念データモデルです。この記号系の典型が ＥＲ（Entity Relationship）モデルです。概念データモデルが、データベース論理設計で最初に作成されます。
論理データ
モデル
概念データモデルをコンピュータに実装可能な形式に変換する必要があります。この形式を論理データモデルといいます。論理データモデルの主な記号系として、リレーショナルモデル、階層型データモデル、ネットワーク型データモデルなどがあります。
リレーショナルデータベースには論理データモデルとして、リレーショナルモデルが採用されています。
.jpg)
図 2. データモデルとモデリング
3.2. 正規化
リレーショナルデータベースにおいて、データをテーブルに展開することを正規化といいます。正規化はデータベース設計の重要な指針です。
正規化したテーブルの形式には、主に以下のような種類があります。
正規形
内容
第１正規形
リレーショナルデータベースでは、テーブルの １ つの列には 1 つの値しか格納できません。このように繰り返し項目を排除したテーブルを第 1 正規形といいます。
例えば、ある社員が 2 つの資格を保有している場合、テーブルの資格欄には 1 つの値しか入らないため、 2 つのタプル（行）に展開して格納します。このように繰り返し項目をなくすことを 第 1 正規化 するといいます。
第 ２ 正規形
第 1 正規形では部分的に同じ属性値をもつタプル（行）が複数存在し、冗長になる場合があります。そこで第1正規形のテーブルから、その一部の属性にだけ従属する属性の組みを外に出すようにテーブルを分割します。このように冗長性を排除したテーブルを第 2 正規形といいます。第 2 正規形は、テーブルが第 １ 正規形であり、かつ候補キーに含まれない属性（非キー属性）が候補キーに完全関数従属する場合をいいます。
例えば社員テーブルにおいて、社員番号（候補キー）とそれに従属する氏名、所属、所属場所を外に出すことができます。この場合、社員番号がわかれば氏名や所属などを一意に特定することができます。
このようにテーブルを分割して第2正規形にすることを 第 2 正規化 するといいます。
第３正規形
第2正規形でも属性の組み合わせによっては冗長になる場合があります。そこで第2正規形のテーブルから、非キー属性間の従属関係を外に出すようにテーブルを分割します。このようにさらに冗長性を排除したテーブルを第 3 正規化といいます。第 3 正規形は、テーブルが第2正規形であり、かつ非キー属性がいかなる候補キーにも推移的関数従属しない場合をいいます。
例えば第 2 正規形にした社員テーブルにおいて、非キー属性である所属と所属場所は、所属が決まれば所属場所が決まるという従属関係にある場合、所属と所属場所を外に出すことができます。このように第2正規形を第 3 正規形にすることを 第 3 正規形 するといいます。
3.3. ＥＲＤ
中規模以上のデータベースでは、設計が大変重要です。データベースの概念や論理設計の手法として、ER（Entity Relationship）モデルがあります。
ER モデルでは、データはすべてエンティティ（実体）とリレーションシップ（関連）で表現します。ER モデルの表現法としては、ERD（Entity Relationship Diagram）が用いられます。
ERD の構成要素
ERD の構成要素には、以下の 3 種類の要素があります。
構成要素
内容
エンティティ（実体）
分析対象範囲で、組織体の中で管理対象となるものです。企業であれば、例えば社員、顧客、商品、支店などがエンティティの候補となります。ERD では通常、長方形の中にエンティティ名を明記します。
リレーションシップ（関連）
エンティティ間の結びつき（関連）を表現します。例えば会社と従業員の間には、所属する、勤務するなどのリレーションシップが考えられます。ERD では通常、エンティティ間を線で結びます。
アトリビュート（属性）
エンティティの性質や特性を表すデータ要素です。例えば社員というエンティティは、氏名、入社年度、生年月日、住所等の属性で構成されます。エンティティには、必ずエンティティの実現値を一意に識別できる属性（主キー）が必要です。
リレーションシップの種類
エンティティ間のリレーションシップには、以下の 3 種類があります。
種類
内容
1 対 1
例えば 1 人の従業員につき 1 台のパソコンが割り当てられているような対応関係です。
1 対多
例えば部門と社員、担任と生徒の対応関係などです。リレーショナルデータベースでは、主キーと外部キーの参照制約に相当します。
多対多
例えば専攻科目と学生、商品と顧客の購入するという対応関係などです。このリレーションシップは、リレーショナルデータベースのテーブルに正規形を満足した形で実装できないため、論理データモデルの段階で、1 対多のリレーションシップに分解する必要があります。
サブタイプ
上記のリレーションシップ以外に、サブタイプとスーパタイプのリレーションシップが存在します。このリレーションシップを IS-A リレーションシップといいます。
例えば顧客の中に個人顧客と法人顧客があるとします。顧客エンティティに対して、個人顧客と法人顧客をサブタイプエンティティといいます。逆に個人顧客や法人顧客から見て、顧客エンティティをスーバタイプエンティティといいます。この場合、スーパタイプには共通属性が割り当てられ、サブタイプには固有属性が割り当てられます。
サブタイプとスーパタイプを用いることにより、ERD を階層化することができます。サブタイプをスーパタイプに統合することを汎化といいます。逆にスーパタイプをサブタイプにすることを特化といいます。
4. SQL Server のアーキテクチャ
SQL Server は SQL を使用する、クライアント／サーバー型のリレーショナル データベース管理システム （RDMS） です。SQL Server を使用するとトランザクション処理の実行、データの格納と分析、アプリケーションの構築が出来ます。
4.1. アーキテクチャ
SQL Server では、新規インストールによって既定でインストールされる ４ つのサービスがあります。
サービス
説明
MS SQL Server サービス
データベースエンジン
SQL Server Agent
ジョブスケジュール管理
Microsoft 分散トランザクションコーディネータ
分散トランザクション管理
Microsoft Search
フルテキストエンジン
SQL Server は複数のインスタンスを同時実行することが出来ます。既定のインスタンスは１つしか作成出来ませんが、名前付きインスタンスは最大１６作成出来ます。
4.2. データベースオブジェクト
データベースは、データの入っているテーブル、及びその他のオブジェクトの集まりです。
データベースオブジェクトの代表的なものとして、以下のものがあります。
データベースオブジェクト
説明
テーブル
列と関連付けられた行の集合です。
ビュー
テーブルの物理的な定義を隠し、アプリケーションに使い易い仮想的なテーブルを提供します。
ストアドプロシージャ
SQLステートメントの集合に名前が付けられたものです。
トリガ
ユーザーがテーブル内のデータを更新すると、自動的に実行される特殊な形式のストアドプロシージャです。
4.3. データベースの種類
SQL Server には、2 種類のデータベースがあります。
種類
説明
システムデータベース
SQL Server 全体の情報が格納されています。SQL Server はシステムの操作と管理にシステムデータベースを使用します。
ユーザーデータベース
ユーザーデータベースは、ユーザーが作成するデーベースです。
データベース内には、システムテーブル、ユーザーテーブル、インデックスの3種類のオブジェクトが存在します。そのうちシステムテーブルは、システムやデータベース内にあるオブジェクトに関するメタデータと呼ばれる情報が格納されます。メタデータとは、データについての情報です。
4.4. 導入
SQL Server 2000 の導入は、セットアッププログラムを実行して進めていきます。セットアッププログラムでは、インストールの種類として最小、標準、カスタムのどれかを選択できます。
セットアッププログラムは、プログラムファイルとデータファイルをディスク上のフォルダにインストールします。プログラムファイルとデータファイルの規定のインストールパスは以下の通りです。
プログラム |
\Program Files\Microsoft SQL Server\Mssql\Binn |
データ |
\ Program Files\Microsoft SQL Server\Mssql\Data |
操作と管理ツール
SQL Server の操作や管理は、SQL Server Enterprise Manager で行います。このツールは、ローカルまたはリモートの SQL Server を管理できる GUI のツールです。Enterprise Manager を使うと SQL を記述しなくても、データベースに対してほとんどすべての操作を行うことができます。
.jpg)
図 3. SQL Server Enterprise Manager
5. 物理データベース設計
論理設計をもとに、データベース管理システム上へ実際にどのようにデータベースを構築するかを設計するのが物理データベース設計です。物理データベース設計をする際、データベース管理システムでのデータ確報方法を知っておく必要があります。
SQL Server の場合、物理データベースの主要なデータ構造として、ページとエクステントがあります。ページは 8 KB の連続したディスク領域で、データの入出力の単位です。データは行単位にページへ格納されます。エクステントはページの集まりで、テーブルとインデックスに領域を割り当てる基本単位です。1エクステントは連続した 8 ページで構成され、サイズは 64 KB になります。
5.1. 逆正規化
物理データベース設計では、データアクセスのパフォーマンスについても考慮する必要があります。
論理データモデリングのところで正規化について説明しましたが、パフォーマンスの向上を目的に、故意に正規化を行わない場合があります。これを逆正規化といいます。正規化して複数に分割されたテーブルにクエリを実行した場合と、逆正規化による単一のテーブルにクエリを実行した場合とでは、逆正規化の方がテーブルの結合が不要な分だけパフォーマンスが向上します。
逆正規化の考慮点
逆正規化を行う際、以下のことを考慮する必要があります。
・ データの整合性に問題が発生する可能性がある
FakePre-0ae05ba706614ce386c6883d8617888e-9781820b76474968bd8fd0ed28a00abf
5.2. インデックス設計
インデックスとは、テーブルのデータを効率よく検索するために作成されるデータです。物理データベース設計では、インデックスを作成するかどうかや、作成する場合にはその方法についても検討します。
インデックスとは
インデックスは、例えば書籍でいうところの索引に相当します。 SQL Server のインデックスは、B-Tree といわれる方式を採用しています。B-tree はインデックスがツリー構造（階層構造）になっており、高速に目的のデータを検索することができます。インデックスにより、データの取得が高速化されます。その反面、ディスク領域を消費し、オーバーヘッドや保守コストがかかります。インデックスを設計する際は、これらのことを考慮する必要があります。
インデックスの実装上の種類
SQL Serverには、以下の 2 種類のインデックスがあります。
インデックス
説明
クラスタ化インデックス
1 つのテーブルに対して 1 つだけ持つことができます。データページはインデックス列の値でソートされています。
非クラスタ化インデックス
1 つのテーブルに対して最大 249 個まで持つことができます。データページはソートされません。
5.3. 問合せの最適化
SQL Server ではデータにアクセスする際、クエリオプティマイザにより、最も効率的なアクセス方法が選択されます。SQL Server で最初にインデックスの存在を判断し、次にクエリオプティマイザが、テーブルのスキャンとインデックスの使用のどちらが効率的かを判断します。オプティマイザはコストベースで動作します（コストベースオプティマイザ）。オプティマイザは各クエリのタスクに時間コストを割り当て、最もコストの低いタスクのリストを選択して目的の結果セットを生成します。
5.4. 結合の最適化
2 つ以上のテーブルの列と行を合わせた結果テーブルから、1 つの結果セットを返すことができます。これを表の結合といいます。
クエリに JOIN 句が含まれる場合、クエリオプティマイザはテーブル、インデックス、および結合の数を評価して、使用する最適な順序と結合方法を決定します。SQL Server の結合方法は以下の 3 種類です。
結合方法
説明
ネクスト化
ループ結合
ネクスト化ループは、一方のテーブルを外部入力テーブルとして使用し、もう一方を内部入力テーブルとして使用します。外部入力テーブルと内部入力テーブルはオプティマイザがコストを考慮して選択します。
マージ結合
マージ結合は２つの並び替えられた入力を使用し、その ２ つをマージします。
ハッシュ結合
ハッシュとは、指定された属性に基づいて、データを分割する方法です。グループ化されたデータは、特定のデータ項目が既存の値と一致するかどうかを判断するのに使用します。