第 1 章 「論理データモデルの開発」～ MCDBA をめざそう!! MCP 70-229 SQL Server 2000 開発編 ～

  • [アーティクル]

NEC

Eラーニング事業部

鈴木 智行

2002 年 6 月 4 日

目次

1. エンティティの定義 1. エンティティの定義
2. ドメイン整合性の属性の設計 2. ドメイン整合性の属性の設計
3. エンティティキーの設計 3. エンティティキーの設計
4. 復習問題 4. 復習問題

1. エンティティの定義

エンティティとは情報資源、データ資源として管理すべき対象のことであり実体ともいいます。

1-1 エンティティとインスタンス

エンティティとエンティティタイプとエンティティインスタンスの 2 つの意味をもつ用語です。エンティティタイプとは同じ対象の実体集合の総称名であり、エンティティと呼んでます。エンティティインスタンスとはその実体集合の1件ごとのデータであり、インスタンスと呼んでます。

図

図 1-1-1 エンティティとインスタンスの例

1-2 正規化

重複データを排除し、更新時にデータ間の矛盾を防ぐことを目的として、管理するデータを分類することを正規化と呼びます。データ中心アプローチとしてシステム開発をする場合に、データ分析の技法として使用されるようになりました。リレーショナルデータベースではデータ正規化を行うことで、一つの事実を一つの場所にまとめます。データ正規化には第一正規化、第二正規化、第三正規化、ボイスコッド正規化、第四正規化、第五正規化の 6 種類がありますが、一般的には第三正規化までを行うだけで構いません。

  • 第一正規化

    第一正規化とは繰り返し部分をなくして平坦な表にすることです。

図

図 1-2-1 繰り返しのあるエンティティ（非正規形）

上記の例では商品コード、商品名、発注単価、発注数量、発注単位、倉庫コード、倉庫名が繰り返し部分になります。したがって繰り返し部分を別エンティティにし、分割したエンティティとつながりを示すために、インスタンスを識別できる属性（識別キー）を埋め込んでいきます。この場合、分割された別エンティティの識別キーは｛元のエンティティのキー、繰り返し部分のキー｝の連結キーとなります。

図

図 1-2-2 第一正規化されたエンティティ

  • 第二正規化

    第一正規化で分割したエンティティに対し、連結キーの一部に部分関数従属する属性があれば取り除きます。これを第二正規化といいます。

    図1-2-2では連結キーの一部である商品コードに対して、商品名、発注単位、倉庫コード倉庫名が決まる（部分関数従属）ので、これを分解します。

図

図 1-2-3 第二正規化されたエンティティ

  • 第三正規化

    第二正規形のエンティティに対し、推移的関数従属性を取り除くようにします。これを第三正規化といいます。例えば図1-2-3では識別キーである商品コード以外の全ての属性が、属性間に関数従属しないようにします。商品コードによって倉庫コードが決まり、倉庫コードによって倉庫名が決まる（推移関数従属）ので、これを分解します。

図

図 1-2-4 第三正規化されたエンティティ

2. ドメイン整合性の属性の設計

属性とはエンティティの特性を具体的に表現する要素であり、エンティティが何を表すかを具体的に示します（図2-1）。属性は値を持ち（これを属性値と呼びます）、属性値として許される値の集まりをドメインといいます。ドメインはその範囲の値であるかどうかを検証することで（CHECK 制約）、データの整合性を保つことができます。また属性に対してデータタイプ、桁数（データ型）や NULL または NOT NULL の設定を行います。

図

図 2-0-1 エンティティと属性の表現例

2-1 CHECK 制約

CHECK 制約は、列に入力できる値を制限してドメイン整合性を設定します。

(例) ALTER TABLE dbo.在庫
　　　 ADD
　　　 CONSTRAINT CK_在庫
　　　 CHECK (在庫数>=0 and 在庫数<=100)

上記の設定をすることで、在庫数には 0～100 の正しいデータしか入らないことが保証されます。

2-2 データ型

データ型は、列で使用できるデータ値を定義します。パフォーマンスを最適化し、ディスク領域を節約するために適切なデータ型を選択する必要があります。

種類

システムデータ型

バイト数

整数

Tinyint

Smallint

Int

bigint　※

1

2

4

8

文字

char(n)

varchar(n)

text

0-8000
0-8000
0-(2GB-2)

Unicode 文字

nchar(n)

nvarchar(n)

ntext

0-8000

0-8000

0-(2 GB-2)

グローバル識別子

uniqueidentifier

16

その他

Timestamp

table　※

sql_variant　※

8

-

0-8016

※ SQL Server 2000 からの新しいデータ型です。

図 2-2-2 代表的なシステムデータ型

2-3 NULL または NOT NULL の設定

NULL はデータベース内の特殊な値であり、未知の値という概念を表しています。NULL は、空白文字や 0 とは異なります。空白は実際に有効な文字であり、0 は有効な数値です。NULL は、その値が未知であるという概念を表しているにすぎません。NULL は長さが 0 の文字列とも異なります。NULLの指定はテーブル定義内で列ごとに行います。

(例) CREATE TABLE dbo.在庫 (
　　　 倉庫番号 nchar (8) NOT NULL,
　　　 商品コード nchar (20) NOT NULL,
　　　 在庫数 smallint NULL,
　　　 発注点 smallint NULL)

上記の設定をすることで、在庫数と発注点には NULL 値が許可されます。 NOT NULL が SQL Server のデフォルトですが、セッションまたはデータベースレベルでデフォルトを変更できます。

  • セッションレベルの設定

    SET ANSI_NULL_DFLT_ON ON

    (逆は SET ANSI_NULL_DFLT_OFF ON)

  • データベースレベルの設定

    ALTER DATABASE 商品 DB

    SET ANSI_NULL_DEFAULT ON

    (逆は SET ANSI_NULL_DEFAULT OFF)

これらの設定によってデフォルトは変更されるため、テーブル定義で NULL の設定が省略されている場合は特に注意してください。

3. エンティティキーの設計

インスタンスを識別できる属性を設定した後、それ以外の主要な属性を検討し、エンティティキーの設計を行います。

3-1 主キー制約

インスタンスを識別できる属性を識別キーまたは候補キーといいます。この識別キーの中で主として使用される代表的なものを主キーと呼びます。

図

図 3-1-1 主キーの例

主キー制約は主キーを実現するデータ整合性であり、テーブル定義内で指定します。

(例) CREATE TABLE dbo.顧客 (
　　　 顧客コード nchar(5) NOT NULL CONSTRAINT PK_顧客コード PRIMARY KEY ,
　　　 顧客名 nvarchar(50) NOT NULL ,
　　　 所在地 nvarchar(200) NOT NULL ,
　　　 電話番号 nchar(15) NULL )

主キーで指定された列には自動的に一意のインデックスが作成されます。デフォルトではクラスタ化インデックスが作成されます。

3-2 一意制約

必要に応じて、主キー以外の残りの識別キーから主キーに替わってインスタンスを識別するキーを決定します。これを代替候補キーと呼びます。

図

図 3-2-1 代替候補キーの例

一意制約は代替候補キーを実現するデータ整合性であり、テーブル定義内で指定します。

(例) ALTER TABLE dbo.顧客
　　　 ADD
　　　 CONSTRAINT U_顧客名
　　　 UNIQUE (顧客名)

代替候補キーで指定された列には自動的に一意のインデックスが作成されます。既定では非クラスタ化インデックスが作成されます。

3-3 外部キー制約

他のエンティティの主キーを属性としてもつとき、その属性のことを外部キーと呼びます。

図

図 3-3-1 外部キーの例

外部キー制約は外部キーを実現するデータ整合性であり、テーブル定義内で指定します。

(例) CREATE TABLE dbo.受注 (
　　　 受注番号 nchar(10) NOT NULL CONSTRAINT PK_受注番号 PRIMARY KEY,
　　　 受注日付 datetime NOT NULL ,
　　　 顧客コード nchar(5) NOT NULL ,
　　　 納期 datetime NOT NULL 
　　　 CONSTRAINT FK_受注_顧客 FOREIGN KEY (顧客コード)
　　　 REFERENCES dbo.顧客 (顧客コード)
　　　 )

外部キーで指定された列には自動的にインデックスが作成されません、注意してください。

4. 復習問題

解答、解説は次回に記述します、復習として考えてみてください。

問 1 下記の表はあるレンタルシステムで使用されている表です。この表は第何正規形でしょうか？（下線は主キーを示します）

表名：タイトル

店番号

タイトル番号

タイトル名

在庫本数

入庫日付

ジャンル

ア．正規化されていない

イ．第 1 正規形

ウ．第 2 正規形

エ．第 3 正規形

問 2 下記の条件を満たす表 （表名：タイトル） を作成する SQL 文はどれですか？

（条件） 店番号：固定 Unicode 文字 5 桁（NULL 値不可）
　　　　　　タイトル番号：整数型（将来的に 1 万種類になる予定あり：NULL 値不可）
　　　　　　タイトル名：可変長 Unicode 文字 50 桁（NULL 値不可）
　　　　　　在庫本数：整数型（50 本以下：NULL 値可）
　　　　　　入庫日付：日付型（NULL 値可）
　　　　　　ジャンル：固定 Unicode 文字 10 桁（NULL 値可）
ア．CREATE TABLE dbo. タイトル(
　　 店番号 nchar (5) NOT NULL,
　　 タイトル番号 tinyint NOT NULL ,
　　 タイトル名 nvarchar (50) NULL ,
　　 在庫本数 tinyint NULL,
　　 入庫日付 datetime NULL ,
　　 ジャンル nchar (10) NULL)
イ．CREATE TABLE dbo. タイトル(
　　 店番号 nchar (5) NOT NULL,
　　 タイトル番号 smallint NOT NULL ,
　　 タイトル名 varchar (50) NOT NULL ,
　　 在庫本数 tinyint NULL CONSTRAINT CK_在庫本数
　　 CHECK (在庫本数>=0 and 在庫本数<=50),
　　 入庫日付 datetime NULL ,
　　 ジャンル nchar (10) NULL)
ウ．CREATE TABLE dbo. タイトル(
　　 店番号 nchar (5) NOT NULL,
　　 タイトル番号 smallint NOT NULL ,
　　 タイトル名 nvarchar (50) NOT NULL ,
　　 在庫本数 tinyint NULL CONSTRAINT CK_在庫本数
　　 CHECK (在庫本数>=0 and 在庫本数<=50),
　　 入庫日付 datetime NULL ,
　　 ジャンル nchar (10) NULL)
エ．CREATE TABLE dbo. タイトル(
　　 店番号 nvarchar (5) NOT NULL,
　　 タイトル番号 smallint NOT NULL ,
　　 タイトル名 nvarchar (50) NOT NULL ,
　　 在庫本数 tinyint NULL CONSTRAINT CK_在庫本数
　　 CHECK (在庫本数<=50),
　　 入庫日付 datetime NULL ,
　　 ジャンル nchar (10) NULL)

問 3 上記問 2 の表 （表名：タイトル） では他表 （表名：店舗） の主キーである店番号を参照してます。外部キー制約を追加する SQL 文はどれですか？

ア．ALTER TABLE dbo.タイトル
　　 ADD
　　 CONSTRAINT FK_タイトル_店舗FOREIGN KEY (店番号)
　　 REFERENCES dbo.店舗 (店番号)
イ．ALTER TABLE dbo.タイトル
　　 ADD
　　 CONSTRAINT FK_タイトル_店舗 FOREIGN KEY (店番号)
　　 REFERENCES dbo.タイトル (店番号)
ウ．ALTER TABLE dbo.タイトル
　　 ADD
　　 CONSTRAINT FK_店舗_タイトル FOREIGN KEY (タイトル番号)
　　 REFERENCES dbo.店舗 (タイトル番号)
エ．ALTER TABLE dbo.店舗
　　 ADD
　　 CONSTRAINT FK_店舗_タイトル FOREIGN KEY (店番号)
　　 REFERENCES dbo.タイトル (店番号)

sysbuild.jpg

鈴木   智行 : NEC Eラーニング事業部に所属。 入社以来、インストラクタとして教育業務に従事。汎用機、UNIX を経て、1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。Windows 2000 および SQL Server 2000 での MCSA, MCSE, MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア （データベース） も取得済。最近は MCA の 3 科目 （データベース、OS／ネットワーク、アプリケーション構築） 全てに合格し、C# を勉強中。