カーレースの例、表

2.3 カーレースの例、表

正規化されたエンティティは、データベース表としてモデル化されます。エンティティ関係は、参加している複数の表の間の結合としてモデル化されます。表team、driverおよびraceは、カーレース・アプリケーションで使用されるチーム、ドライバおよびレースJSONドキュメントを提供およびサポートする二面性ビューを実装するために使用されます。

正規化されたエンティティでは、コンテンツは重複していません。ただし、別の表に格納されているコンテンツを参照する表として、論理的に重複させるためにエンティティを実装するデータベース表が必要です。これを実現するには、外部キー制約を使用して、他の表にリンクされている列を追加します。これは、共通のコンテンツの共有を実装する表間の外部キー関係です。

二面性ビューの定義に使用する表は、次の要件を満たす必要があります(それ以外の場合、ビューを作成しようとするとエラーが発生します)。

二面性ビューの基になる表を間接的に更新可能にする場合は、そのビューを介して(つまり、そのビューでサポートされているドキュメントの更新によって)表の個々の行を識別できる必要があります。

この要件のためには、その表に対して列を1つ以上定義し(識別列と呼ばれる)、それらすべてによって1つの行を識別します。識別列とは、主キー列、アイデンティティ列、または一意制約か一意索引がある列です。

一意制約がある列と一意索引がある列は、一意キー列と呼ばれることもあります。したがって、一意キーまたは主キーは、表内の行を一意に識別する1つ以上の列のセットです。

二面性ビューのルート表では、この目的のために1つ以上の一意キー列が使用されている場合に、それらのうち少なくとも1つをNOT NULLとマークする必要もあります。これにより、NULL値可能な一意キー、またはNULL列がある一意キーを使用することによって生じる曖昧さを回避できます。

二面性ビューでのルート表の識別列は、そのビューでサポートするようになっているJSONドキュメントのドキュメント識別子フィールド_idに対応付けられています。「二面性ビューのドキュメント識別子フィールド」を参照してください。
各外部キー列にも索引を定義することをお薦めします。主キーと外部キー間の参照(リンク)を定義する必要がありますが、適用する必要はありません。

ノート:

通常、1次索引および一意索引は、主キーおよび一意キーの整合性制約を定義するときに暗黙的に作成されます。ただし、これは保証されておらず、索引の作成後に削除できます。必要な索引が存在することを確認する必要があります。『Oracle Database管理者ガイド』の索引の作成に関する項を参照してください。

一般に、外部キー列の値はNULLでもかまいません。前述の要件以外に、外部キー列をNULL値可能にしない場合は、表定義でNOT NULLとしてマークします。

カーレースの例で使用される各表の識別列は、1つのみであり、主キー列です。このマニュアルでは、主キー、外部キーおよび一意キーを単一列キーと呼ぶことがありますが、それらは通常は複数の列からなる複合であることを覚えておいてください。

カーレースの例では、チーム、ドライバおよびレースのエンティティは、表team、driverおよびraceによって実装され、次の列があります:

team表:
- team_id - 主キー
- name - 一意キー
- points
driver表:
- driver_id - 主キー
- name - 一意キー
- points
- team_id - 表teamの列team_idにリンクする外部キー
race表:
- race_id - 主キー
- name - 一意キー(そのため、表には重複行がありません。同じ名前で2つのレースは存在できません)
- laps
- race_date
- podium

カーレース・アプリケーションのロジックでは、特定のチーム名を持つ1つのチームのみ、特定のドライバ名を持つ1つのドライバのみ、および特定のレース名を持つ1つのレースのみが存在することが要求されるため、これらの各表のname列は一意キーになります。(これは、特定のnameフィールド値を持つチーム・ドキュメントが1つのみで、特定のnameを持つドライバ・ドキュメントが1つのみで、特定のnameを持つレース・ドキュメントが1つのみであることを意味します。)

表driverには、表teamと論理的に共有されるデータである追加列team_idがあります(チーム・ドキュメントのドキュメント識別子フィールド_idに対応します)。この共有は、表teamの(主キー)列team_idにリンクする表driverの外部キーとして列を宣言することで定義されます。このリンクは、ドライバからチームへの1:1の関係と、チームからドライバへの1:Nの関係の両方を実装します。

しかし、他の共有、すなわち、レース・ドキュメントと共有されているドライバ・ドキュメント内のレース情報、およびドライバ・ドキュメントまたはチーム・ドキュメントと共有されているレース・ドキュメント内の情報についてはどうでしょうか。

この情報共有は、エンティティ・ドライバとレース間の多対多(N:N)関係に対応しています。データベースはN:N関係を直接実装しません。かわりに、マッピング表(または関連表)と呼ばれる別の表を追加して、表driverとrace間の関係の橋渡しをする必要があります。マッピング表には、外部キーとして、関連付けられている2つの表の主キー列が含まれています。

N:Nのエンティティ関係は、1:Nの関係と同等で、その後に1:1の関係が続きます。この等価性により、表driverとraceの間にマッピング表driver_race_mapを追加して、データベース表を使用したN:Nのエンティティ関係が実装されます。

図2-2は、図2-1と同等です。中間エンティティd-r-mapが追加されて、各N:N関係が1:N関係に拡張され、その後に1:1関係が続きます。^脚注1

図2-2 カーレースの例、方向を示したエンティティ関係図(2)

「図2-2 カーレースの例、方向を示したエンティティ関係図(2)」の説明

マッピング表driver_race_mapは、中間エンティティd-r-mapを実装します。次のような列があります。

driver_race_map_id - 主キー
race_id — (1)表raceの主キー列race_idにリンクする外部キーと、(2)一意キー(そのため、表には重複行がありません。特定のレースに対して同じドライバに2つのエントリは存在できません)
driver_id - 表driverの主キー列driver_idにリンクする外部キー
position

外部キー・リンクおよび主キー・リンクで定義された関係とともに、カーレース表は依存関係グラフを形成します。図3-1を参照してください。

例2-4 カーレース表の作成

この例では、各表を1つの主キー列を含めて作成します。その値は一連の整数として自動的に生成され、一意キー列nameも生成されます。これにより、主キー列に一意索引も暗黙的に作成されます。この例では、外部キー索引も作成します。

表raceの列podiumのデータ型はJSONです。その内容は柔軟性があり、特定の構造やフィールド・タイプに準拠する必要はありません。あるいは、そのコンテンツを特定のJSONスキーマに準拠(つまり検証)するようにもできます。


CREATE TABLE team
  (team_id    INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name       VARCHAR2(255) NOT NULL UNIQUE,
   points     INTEGER NOT NULL,
   CONSTRAINT team_pk PRIMARY KEY(team_id));

CREATE TABLE driver 
  (driver_id  INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name       VARCHAR2(255) NOT NULL UNIQUE,
   points     INTEGER NOT NULL,
   team_id    INTEGER,
   CONSTRAINT driver_pk PRIMARY KEY(driver_id),
   CONSTRAINT driver_fk FOREIGN KEY(team_id) REFERENCES team(team_id));

CREATE TABLE race
  (race_id    INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name       VARCHAR2(255) NOT NULL UNIQUE,
   laps       INTEGER NOT NULL,
   race_date  DATE,
   podium     JSON,
   CONSTRAINT race_pk PRIMARY KEY(race_id));

-- Mapping table, to bridge the tables DRIVER and RACE.
--
CREATE TABLE driver_race_map
  (driver_race_map_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   race_id            INTEGER NOT NULL,
   driver_id          INTEGER NOT NULL,
   position           INTEGER,
   CONSTRAINT driver_race_map_uk  UNIQUE (race_id, driver_id),
   CONSTRAINT driver_race_map_pk  PRIMARY KEY(driver_race_map_id),
   CONSTRAINT driver_race_map_fk1 FOREIGN KEY(race_id)
                                    REFERENCES race(race_id),
   CONSTRAINT driver_race_map_fk2 FOREIGN KEY(driver_id)
                                    REFERENCES driver(driver_id));
-- Create foreign-key indexes
--
CREATE INDEX driver_fk_idx ON driver (team_id);
CREATE INDEX driver_race_map_fk1_idx ON driver_race_map (race_id);
CREATE INDEX driver_race_map_fk2_idx ON driver_race_map (driver_id);

ノート:

主キー、一意キーおよび外部キー整合性制約は、二面性ビューの基礎となる表に対して定義する必要があります(そうしないと、エラーが発生します)が、これらは強制する必要はありません。

場合によっては、特定の制約の条件を満たしていることがわかっているため、その制約を検証または強制する必要はありません。ただし、問合せのパフォーマンスを向上させるために制約が存在することが必要な場合もあります。その場合、制約はRELY状態に設定できます。これは、その制約が満たされていると考えられていることを示します。Oracle Databaseデータ・ウェアハウス・ガイドのデータ・ウェアハウスにおけるRELY制約に関する項を参照してください。

外部キー制約をDEFERRABLEにすることもできます。これは、トランザクションの最後に妥当性チェックが実行されるということです。『Oracle Database概要』の遅延可能制約を参照してください

ノート:

二面性ビューの基礎となる表の列で使用できるSQLデータ型は、BINARY_DOUBLE、BINARY_FLOAT、BLOB、BOOLEAN、CHAR、CLOB、DATE、JSON、INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH、NCHAR、NCLOB、NUMBER、NVARCHAR2、VARCHAR2、RAW、TIMESTAMP、TIMESTAMP WITH TIME ZONEおよびVECTORです。他の列データ型を指定すると、エラーが発生します。

関連トピック