掲載元 2011年11/12月

テクノロジー：SQLの基礎

リレーショナル・データのモデリングとアクセス

リレーショナル・データベースとSQLの基本に関するシリーズ記事のパート2

このシリーズ記事のパート1"情報の構造化"（Oracle Magazine、2011年9/10月）では、リレーショナル・データベースの概要と、リレーショナル・データベースの操作でおもに使用される言語であるStructured Query Language（SQL）について説明しました。この記事では、パート1で示したマテリアルに基づいて、リレーションシップの概念について詳しく説明し、新しいデータベースの設計プロセスについて概要を紹介します。次に、SQL経由でOracle Databaseインスタンス内のデータを参照し管理するために無償で使用できる3つのツールについて紹介します（パート1で説明した概念についても簡単に確認しますが、パート1をお読みになってからパート2に進むことをお勧めします）。

さまざまな方法での関連付け

リレーショナル・データベースでは、データは表として構造化されます。表は、列と行で構成される2次元マトリックスです。表の主キーとは、表のすべての行が一意に識別されることを保証する特定の列または列の組合せのことで、主キー制約によって制限が適用されます（主キー制約については、このシリーズの今後の記事で定義します）。共通の列がある2つの表には、リレーションシップがあるという言い方をします。この共通の列は、一方の表では外部キー、もう一方の表では主キーとなります。行/列の交差部に値が格納されている場合、その値のことをデータ要素と言います。 

リレーションシップのカーディナリティ（基数）とは、2つの表の関連する列におけるデータ要素の個数（オカレンスとも呼ばれる）の比のことです。リレーションシップのカーディナリティには、1対多、1対1、多対多の3種類があります。

1対多（1:M）：もっとも一般的なリレーションシップのカーディナリティは、1:Mリレーションシップです。図1のEMPLOYEE表とDEPARTMENT表のリレーションシップについて考えてみましょう。共通の列はDEPARTMENT_IDです（DEPARTMENT表の主キー、EMPLOYEE表の外部キー）。DEPARTMENT_IDの1つ1つは、EMPLOYEE表の多数の行に関連付けられます。これは、1つの部門は1人以上（0人の場合もある）の従業員に関連付けられる可能性があり、1人の従業員は1つの部門にのみ関連付けられる（または、どの部門にも関連付けられないこともある）というビジネス・ルールを表しています。このビジネス・ルールは次のように言いかえることができます。ある部門内の各従業員は1つの部門にのみ所属でき、その部門はDEPARTMENT表に存在していなければならない。

図1：1:Mリレーションシップを持つEMPLOYEE表とDEPARTMENT表

1対1（1:1）：DEPARTMENT表とMANAGER表の1:1リレーションシップを図2に示します。DEPARTMENT表のすべての行について、MANAGER表に一致する行が1つのみ存在します。このことをビジネス・ルールとして表すと、次のようになります。すべての部門に少なくとも1人、多くとも1人のマネージャーが存在する。逆に、各マネージャーは少なくとも1つ、多くとも1つの部門に割り当てられる。

図2：1:1リレーションシップを持つDEPARTMENT表とMANAGER表

1対1のデータ・リレーションシップの存在自体は可能ですが、2つの表としてモデリングすることはあっても、2つの表として実装することは通常はありません。そのようなデータは、単純化の目的で1つの表にまとめられます。

多対多（M:M）：図3のEMPLOYEE表とPROJECT表を見てみましょう。このビジネス・ルールは次のとおりです。1人の従業員を複数のプロジェクトに割り当てることができ、1つのプロジェクトを複数の従業員で支援できる。そのため、これら2つの表をリンクするには、M:Mリレーションシップを作成する必要があります。

図3：M:Mリレーションシップが必要なEMPLOYEE表とPROJECT表

このリレーショナル・データベース・モデルをサポートするには、M:Mリレーションシップを複数の1:Mリレーションシップに解決する必要があります。図4に、EMPLOYEE_PROJECTという連結表（中間表または交差表とも呼ばれる）の作成による解決方法を示します。

図4：M:Mリレーションシップを解決したEMPLOYEE_PROJECT連結表

この例では、連結表の主キー（Employee_ID列とProject_ID列のコンポジット）は、M:Mリレーションシップを解決するために、外部キーによりそれぞれの表にリンクされています。この表は、1人の従業員を複数のプロジェクトに割り当てられることを反映しており、さらにこの例では、1人の従業員に異なる複数の職務（その従業員が割り当てられた各プロジェクトでの職務）を割り当てることができます。この例では、EMPLOYEE_IDの値が1234である従業員が2つのプロジェクトに割り当てられていますが、それぞれのプロジェクトで職務が異なっていることに注意してください。

リレーショナル・ロードマップの表現

概念を把握する目的で、データベース・スキーマ・ダイアグラムを使用して異なる種類の表のリレーションシップを表現すると便利です（一般的に、スキーマとは、類似したビジネス機能を扱う表などのオブジェクトのグループのことです）。スキーマ・ダイアグラムは、物理データ・モデルとも呼ばれ、複数の標準的な表記法を使用できます。図5のスキーマ・ダイアグラムでは、クロウズフット（カラスの足跡）表記法と呼ばれる表記規則を使用しています。これは、データベース・モデリング/設計ツールであるOracle SQL Developer Data Modelerで標準的に使用される表記法です。クロウズフット表記法のカラスの足跡、つまりフォークの部分はリレーションシップの"多"の側を示し、矢尻のある1本の直線部分はリレーションシップの"1"の側を示します。

図5：EMPLOYEE表とDEPARTMENT表の間に必須の外部キー・リレーションシップがあることを示したデータベース・スキーマ・ダイアグラム

図5のスキーマ・ダイアグラムは、EMPLOYEE表とDEPARTMENT表の間に必須のリレーションシップがあることを示しています。必須のリレーションシップとは、外部キー列に値が存在しなければならないリレーションシップのことであり、カラスの足跡と矢尻の間の実線で示されます。このことは図5において、すべての従業員を多くとも1つの部門、かつ少なくとも1つの部門に割り当てなければならないことを意味します。必須ではないリレーションシップは、実線ではなく点線で表されます。これは、従業員を0または1つの部門に割り当てられることを示します（図5では、左側の余白部のPは主キー、Fは外部キーを示しています）。

分析と設計による下準備

開発者、DBA、またはデータベース・アーキテクトは、表を作成する前や、スキーマ・ダイアグラムを作成する前でも、システム・ユーザーのニーズを特定するデータ要件を収集します。特に、要件収集では、ユーザーが重要視し保存する必要のある個別のデータ要素の一覧が最終的に作成されます。その後、ユーザーがこの一覧の中で最重要だと考えるデータ要素を、開発者またはDBAがエンティティとしてグループ化します。個々のデータ要素はエンティティの属性と呼ばれます。

このようにエンティティやその属性を、必要なエンティティのリレーションシップとともにビジネス・ルールに基づいて作成することを、論理データ・モデリングと呼ぶこともよくあります。"論理"である理由は、特定の技術的（つまり物理的）実装については考慮していないためです。エンティティは、物理的実装における表に対応する論理的な用語です。また、属性（フィールドとも呼ばれる）は、列に対応する論理的な用語です。論理データ・モデルを示したダイアグラムは、エンティティ関連ダイアグラム（ERD）と呼ばれます。ERDは、Oracle SQL Developer Data Modelerなどの専用ツールにより簡単に生成できます。

論理データ・モデルを作成して、Oracle Database 11gなどの物理的な技術的実装を選択すれば、データベース・スキーマ・ダイアグラム（物理モデル）の作成に移ることができます。スキーマ・ダイアグラムの作成時には、表の各列にデータ型を割り当てます。データ型とは、各列に格納できるデータの種類（数値、文字、日付など）を表したものです。

正規化と非正規化

データの正規化は、広く受け入れられたルールに基づいて、開発者やDBAが表のデータから冗長性を排除するために利用するプロセスです。反対に、非正規化はデータを冗長化する作業を指します。物理データベース・モデルを設計するときに、データベース設計者はすべての冗長性を排除すること（この結果、データが多数の表に分割される）による利点と、それらの表の一部、あるいは大部分をSQLによって結合する場合に問合せ（データ取得）のパフォーマンスが低下する可能性とを比較検討する必要があります（SQL問合せのJOIN句の役割について詳しくは、このシリーズで後ほど説明します）。非正規化は、経験豊かなデータベース設計者のみが扱ってください。冗長化により、問合せのパフォーマンスがわずかに向上する可能性がありますが、全体的なプログラミング量や複雑性がかならず増加します。これは、同じデータに関する複数のコピーの同期状態を維持する必要があるからです。データの複数のコピーを同期するプロセスは、データ整合性を脅かすことになります。

データ・アクセス/SQL実行環境

Oracleソフトウェアは多種多様なハードウェア・アーキテクチャおよびオペレーティング・システム上で稼働します。Oracle DatabaseソフトウェアがインストールされたコンピュータのことをOracle Databaseサーバーと言います。また、Oracle DatabaseソフトウェアやそのデータをOracle Databaseサーバーと呼ぶこともできます（この記事ではこれ以降、後者の定義を使用します）。Oracle Databaseサーバーのデータには、ユーザーのコンピュータにインストールされた専用ツールを使用してアクセスできます。これらのツールは、クライアントまたはフロントエンドと呼ばれ、SQLコマンドをサーバー、つまりバックエンドに送信するために使用されます。Oracle SQL Developer、オラクルのSQL*Plus、Oracle Application Express SQL Workshopという3つの専用ツールがあります。

SQLコマンドは、データベース内で特定のアクションを実行するようにサーバーに指示するためのものです。コマンドによって、表の作成、表の問合せ、表の変更、新しいデータの追加、既存のデータの更新など、さまざまな操作を実行できます。たとえば、問合せのリクエストへの応答として、サーバーはクライアントに結果セットを返し、クライアントはその結果セットをユーザーに対して表示します。

これらの専用ツールのいずれかを使用してSQLリクエストをOracle Databaseサーバーに送信するには、データベース接続を作成しておく必要があります。SQL Developerを使用している場合、データベース接続のセットアップ方法については、Oracle® SQL Developerユーザーズ・ガイド リリース2.1の"1.4 データベース接続"の項を参照してください。SQL*PlusまたはOracle Application ExpressのSQL Workshopを使用している場合は、自分用のデータベース接続を作成するようにデータベース管理者に依頼してください。また、データベース管理者に自分用のユーザー名とパスワードを作成してもらい、独自のオブジェクトを作成するための適切な権限を付与してもらう必要もあります。Oracle® Database SQL言語リファレンス 11gリリース2 (11.2)の"CREATE USER"の項を参照してください。

Oracle SQL Developerの使用

データベースに接続した後は、Oracle SQL Developer環境でのデータの参照は比較的簡単です。図6に、Connections NavigatorのTablesノードを示します。Connections Navigatorは、Oracle SQL Developerにおけるツリー形式のオブジェクト・ブラウザ・ペインです。

図6：Oracle SQL DeveloperのConnections NavigatorでのTablesノード

任意の表の詳細情報を表示するには、プラス記号をクリックしてTablesノードを展開し、次に個々の表の名前をダブルクリックします。図7に、Connections NavigatorでEMPLOYEE表をダブルクリックした結果を示します。Connections Navigatorペインには表の列の名前が縦に表示され、さらにそのペインの右側に、表の詳細情報を示す複数のタブが表示されます。

図7：Oracle SQL DeveloperでのEMPLOYEE表の列の一覧と詳細を表すタブ

列とデータ型

デフォルトでは、まずColumnsタブが表示されます。このタブには、表に含まれる列の名前とデータ型が一覧表示されます。さらに、NULL値（つまり、値が存在しないこと）を許可する列、表の主キーとして定義された列（複数可）、列に関するコメントも表示されます（図7のEMPLOYEE表には、主キーが定義されていません。主キーの作成方法については、このシリーズ記事で後ほど取り上げます）。

すべての列にデータ型があります。これらのデータ型は、物理モデリングの際に決定し、表の作成時に定義したものです。たとえば、EMPLOYEE表のSALARY列のデータ型はNUMBERです。NUMBERデータ型として定義された列では、数値データのみが許可されます。このデータ型を定義した列に、テキストやアルファベット文字（通貨記号など）を格納することはできません。この例では、SALARY列のデータ型はNUMBER(9, 2)と定義されています。丸括弧内の最初の数値（この例では9）は精度と呼ばれ、2番目の数値（この例では2）は位取りと呼ばれます。この例の精度および位取りが意味しているのは、SALARY列は最大9桁であり、小数点以下は最大2桁であることです（この形式は、通貨データを格納する列に使用できます）。小数点以下が3桁以上の値をSALARY列に挿入してもエラーは発生せず、単にOracle Databaseサーバーによって値が自動的に丸められます。

お気づきかもしれませんが、VARCHAR2データ型として定義された列には、テキスト、数値、特殊文字を含む可変長の英数字が格納されます。丸括弧内には、このデータの最大長を指定します。VARCHAR2はもっともよく使用されるデータ型で、最大4,000バイトのデータを格納できます。対照的に、CHARデータ型（図7には記載なし）では、固定長の英数字データのみが許可され、最大2,000バイトを格納できます。テキストの列を定義するために英数字データ型を選択する際には、CHARよりもVARCHAR2を選択することをお勧めします。VARCHAR2を使用すると、特に列に対して適切な最大長を指定する場合に、記憶域を浪費せずに済みます（最大の4,000バイトの指定は、そのサイズ上限に達するデータ値を列に格納することがあると確信できる場合にのみ効果があります）。CHARは記憶域を浪費する可能性があります。データがかならず指定した固定長まで空白によりパディングされてから格納されるためです。

SQL*PlusとSQL Workshop

もっとも有効なツールを判断して、好みのツールを利用できない環境でも（たとえばコンサルタントとしてクライアントの拠点で作業する場合でも）Oracle Databaseデータにアクセスできるように、複数のデータ・アクセス・ツールに慣れておくと良いでしょう。SQL*Plusは、コマンドライン・ベースのユーティリティであり、昔からのOracle DatabaseプログラマーやDBAの多くが選択するツールです。このツールは、すべてのOracle Databaseインストールに搭載されているため、常に利用できます。SQL*Plus環境を起動すると、図8のような出力が表示されます。

図8：ログイン直後のSQL*Plus環境

表の列や各列のデータ型をSQL*Plusで参照するには、図9のようにDESCRIBEコマンド（短縮形desc）を使用します。

図9：SQL*PlusでのDESCRIBEコマンドの出力

Oracle Application Expressを使用している場合は、好みのデータ・アクセス・ツールを使用できますが、Oracle Application ExpressにはSQL Workshopという独自のデータ・アクセス機能が組み込まれています。SQL Workshopには複数の下位ユーティリティがあります。その1つがObject Browserで、Oracle SQL DeveloperのConnections Navigatorと同様に動作します。一覧の表の名前をダブルクリックすると、その表の詳細情報が右側のペインに表示されます。図10に、SQL Workshopに表示されたEMPLOYEE表および列の詳細情報の出力を示します。

図10：Oracle Application ExpressのSQL Workshopに表示されたEMPLOYEE表の詳細情報の出力

結論

要件の分析、データ・エンティティ相互の関連付け方法、データ・エンティティやそれらのリレーションシップの論理モデリングと物理モデリングはすべて、データベース設計における重要なステップです。ユーザーのビジネス・ニーズを把握することで、意味のあるエンティティとリレーションシップを作成し、合理的なサイズで十分な検討の上で選択されたデータ型を持つ適切な属性を定義できます。物理的な表を定義し作成した後には、この記事で説明したオラクルの3つの無償ツールを含む多数のオプションの中からデータ・アクセス方法を選択できます。

SQLの基礎に関する次回の記事では、SQL SELECT文について分析し、SQL文を作成するための複数のデータ・アクセス・ツールの使用方法について説明します。

Melanie Caffreyはオラクルの上級開発マネージャーです。Expert PL/SQL Practices for Oracle Developers and DBAs（Apress、2011年）およびExpert Oracle Practices: Oracle Database Administration from the Oak Table（Apress、2010年）の共著者でもあります。