３ データベースの定義とデータの制御・投入

(1)　スキーマとは

データベースの定義情報をスキーマというが、スキーマはデータ定義言語のスキーマ定義命令によって指定されます。スキーマの定義は、表の定義・ビューの定義および権限の定義からなります。

スキーマに関する定義情報はDBMSの機能により、DD/D（Database Dictionary/Directory; データディクショナリ／データディレクトリ）に自動的に登録されます。

(2)　認可識別子

スキーマを定義する際には、まず誰がスキーマを定義するかを明確にし、その人を識別できるようにする必要があります。そのため、スキーマ認可識別子を利用する。認可識別子を持つ利用者は、スキーマ内で作成された表やビューに対して処理権限を持ちます。また、認可識別子を持たない人はデータベースにアクセスできないため、認可識別子はデータベースの保護機能も担っていることになります。ネットワークシステムにおける対話型処理の場合、認可識別子はユーザＩＤを兼ねる場合が多くあります。

スキーマ認可識別子は、データ定義言語の CREATE SCHEMA 文で指定します。

CREATE SCHEMA
   AUTHORIZATION 認可識別子
    

(1)　表名

データベースの定義情報をスキーマといいますが、スキーマはデータ定義言語のスキーマ定義命令によって指定されます。スキーマの定義は、表の定義・ビューの定義および権限の定義からなります。

表の定義は、データ定義言語の CREATE TABLE 文で指定します。

CREATE TABLE 表名
    

(2)　データ型

表は行（タプル）と列（属性）から成り立っているが、表を定義するには属性（データ型）を定義します。

  列名 データ型
    

SQLで定義できるデータ型は下表のようになります。ただし、各メーカが提供しているSQLにより機能を拡張しているものもあるので、注意する必要があります。

データベースではデータ型の定義において、「空白値（null value）」を設定することもできます。空白値とは、値を持たないか、もしくは値がまだ決まっていないものをいいます。データ型を定義する際には、空白値を許すか、許さないかを決め、キー項目のように空白値を許さない場合は、「NOT NULL」と指定します。後述しますが、空白値は検索条件としても利用できます。

(3)　基本キー（PRIMARY KEY）

表の中で、レコードキー項目となる属性を基本キー（Access では主キー）として指定します。SQLでは、PRIMARY KEY 句で定義します。

  PRIMARY KEY (列名)
    

(4)　外部キー（FOREIGN KEY）

外部キーとは、ある表では基本キーとして使用されませんが、他の表では基本キーとして使用されているデータ項目です。SQLでは、FOREIGN KEY 句で定義し、その後にどの表で基本キーとして使用されるかを指定します。

  FOREIGN KEY (列名)
    REFERENCES 表名
    

例えば、「３．１　データベースの定義」に示した四つの表を定義すると、次のようになります。

なお、右上には Access の QBE方式での定義方法を示しています。

●受注

CREATE TABLE 受注 (
  受注番号   CHAR(5)     NOT NULL,
  受注年月日 DATE        NOT NULL,
  顧客番号   CHAR(4)     NOT NULL,
  受注合計   NUMBER(8)   NOT NULL,
  PRIMARY KEY (受注番号),
  FOREIGN KEY (顧客番号) REFERENCES 顧客
)
    

●受注明細

CREATE TABLE 受注明細 (
  受注番号   CHAR(5)     NOT NULL,
  商品番号   CHAR(3)     NOT NULL,
  数量       NUMBER(3)   NOT NULL,
  受注小計   NUMBER(7)   NOT NULL,
  PRIMARY KEY (受注番号, 行番号),
  FOREIGN KEY (受注番号) REFERENCES 受注,
  FOREIGN KEY (商品番号) REFERENCES 商品
)
    

●顧客

CREATE TABLE 顧客 (
  顧客番号 CHAR(4)       NOT NULL,
  顧客名   NVARCHAR2(10) NOT NULL,
  住所 NVARCHAR2(20) NOT NULL,
  電話番号 NVARCHAR2(13),
  PRIMARY KEY (顧客番号)
)
    

●商品

CREATE TABLE 商品 (
  商品番号   CHAR(3)       NOT NULL,
  商品名     NVARCHAR2(10) NOT NULL,
  単価       NUMBER(7)     NOT NULL,
  PRIMARY KEY (商品番号)
)
    

(1)　ビューの特性

ビューとは、視点という意味で、実在表の一部分を取り出したもの、または複数の表から必要なデータ項目を結合して、あたかも一つの表であるかのように見せる仮想の表のことです。関係データモデル型のデータモデルと比べて優れている点の一つは、このビューを使用していることであり、時と場合により自在に作成できるため、定型業務のみならず、不定型業務にも対応できます。

ビューに対しては、一定の制限のもとではあるが、表と同じようにいろいろなデータ操作を行うことができます。例えば、ビューに対して照会、変更の処理を表と同様に行うことができる。ただし、複数の表をもとに作成したビューに対しては、変更処理はできません。また、元の表にデータ変更があった場合、その変更結果を直ちにビューに反映することができます。

ビューの利用によって、次のようなことが可能となります。

使いやすさの向上

表から必要な列だけを取り出して新しい表（ビュー）を作ることによって、表のデータが見やすくなる。また、複数の表を結合して新しい表を作っておくこともできる。これらのビューに対するデータ操作のSQL文は、元の表のSQL文に比べて簡単になる。

データの利用範囲を制限することによる安全確保

特定の列あるいは行からなるビューを作り、そのビューに対するアクセス権限を与えることにより利用範囲を制限することができ、セキュリティを確保できる。

データからの独立性の向上

元の表の定義が変更（例えば、列の追加、表の分割）されてもビューに対する操作命令を変更する必要がない。

(2)　ビューの定義

ビューの定義では、ビュー名を付ける必要があるが、この名前は同一スキーマ内の表名および他のビュー名と区別できるようにします。

SQLでは、ビュー定義を CREATE VIEW 文により指定します。

    CREATE VIEW ビュー名
      AS SELECT 列名 FROM 表名 
    

例えば、「［顧客］から、［顧客番号］と［顧客名］だけで構成せれる顧客名表というビューを定義する」は、次のようになります。

    CREATE VIEW 顧客名
      AS SELECT 顧客番号, 顧客名 FROM 顧客
    

(1)　対話式

この方式では、独立言語方式の会話型SQLにより、1行ずつデータを投入していきます。詳しくは後述しますが、SQLのINSERT文で指定します。

ただし、この方式は1行ずつ投入するため、大量のデータを投入する場合には向いていません。

(2)　親言語方式

この方式では、親言語方式の埋込み型SQLにより、別に準備したデータを投入していく。その場合、あらかじめSQL文（INSERT文）を組み込んだデータ投入量プログラムを用意する必要があります。

親言語方式は、別に準備したデータを加工したり、ある条件のもとでデータを選択したりしながらデータを投入する場合などに向いています。

(3)　ユーティリティプログラム方式

この方式では、データ投入用のユーティリティプログラムを利用して、別に準備したデータを投入していきます。この方式は、準備したデータに何の加工もせずに、大量のデータをただ単に投入する場合に向いています。