Normalized entities are modeled as database tables. Entity relationships are modeled as joins between participating tables. Tables team, driver, and race are used to implement the duality views that provide and support the team, driver, and race JSON documents used by the car-racing application.

The normalized entities have no content overlap. But we need the database tables that implement the entities to overlap logically, in the sense of a table referring to some content that is stored in another table. To realize this we add columns that are linked to other tables using foreign-key constraints. It is these foreign-key relations among tables that implement their sharing of common content.

The tables used to define a duality view must satisfy these requirements (otherwise an error is raised when you try to create the view):

• For a table underlying a duality view to be updatable indirectly, through the view (that is, by updating documents supported by the view), the individual rows of the table must be identifiable.

  For this requirement, you define one or more columns, called identifying columns for the table, which together identify a row. Identifying columns are primary-key columns, identity columns, or columns with unique constraints or unique indexes.

  Columns with unique constraints and columns with unique indexes are sometimes called unique-key columns. A unique key or a primary key is thus a set of one or more columns that uniquely identify a row in a table.

  For the root table of a duality view, if one or more unique-key columns are used for this purpose, then at least one of them must, in addition, be marked NOT NULL. This prevents any ambiguity that could arise from using a NULLable unique key or a unique key that has some NULL columns.

  The identifying columns for the root table in a duality view correspond to the document-identifier field, _id, of the JSON documents that the view is designed to support — see Document-Identifier Field for Duality Views.

• Oracle recommends that you also define an index on each foreign-key column. References (links) between primary and foreign keys must be defined, but they need not be enforced.

  Note:

  Primary and unique indexes are generally created implicitly when you define primary-key and unique-key integrity constraints. But this is not guaranteed, and indexes can be dropped after their creation. It's up to you to ensure that the necessary indexes are present. See Creating Indexes in Oracle Database Administrator’s Guide.

In general, a value in a foreign-key column can be NULL. Besides the above requirements, if you want a foreign-key column to not be NULLable, then mark it as NOT NULL in the table definition.

There's only one identifying column for each of the tables used in the car-racing example, and it is a primary-key column. In this documentation we sometimes speak of primary, foreign, and unique keys as single-column keys, but keep in mind that they can in general be composite: composed of multiple columns.

In the car-racing example, entities team, driver, and race are implemented by tables team, driver, and race, which have the following columns:

• team table:

  • team_id — primary key

  • name — unique key

  • points

• driver table:

  • driver_id — primary key

  • name — unique key

  • points

  • team_id — foreign key that links to column team_id of table team

• race table:

  • race_id — primary key

  • name — unique key (so the table has no duplicate rows: there can't be two races with the same name)

  • laps

  • race_date

  • podium

The logic of the car-racing application mandates that there be only one team with a given team name, only one driver with a given driver name, and only one race with a given race name, so column name of each of these tables is made a unique key. (This in turn means that there is only one team document with a given name field value, only one driver document with a given name, and only one race document with a given name.)

Table driver has an additional column, team_id, which is data that's logically shared with table team (it corresponds to document-identifier field _id of the team document). This sharing is defined by declaring the column to be a foreign key in table driver, which links to (primary-key) column team_id of table team. That link implements both the 1:1 relationship from driver to team and the 1:N relationship from team to driver.

But what about the other sharing: the race information in a driver document that's shared with a race document, and the information in a race document that's shared with a driver document or with a team document?

That information sharing corresponds to the many-to-many (N:N) relationships between entities driver and race. The database doesn't implement N:N relationships directly. Instead, we need to add another table, called a mapping table (or an associative table), to bridge the relationship between tables driver and race. A mapping table includes, as foreign keys, the primary-key columns of the two tables that it associates.

An N:N entity relationship is equivalent to a 1:N relationship followed by a 1:1 relationship. We use this equivalence to implement an N:N entity relationship using database tables, by adding mapping table driver_race_map between tables driver and race.

Figure 2-2 is equivalent to Figure 2-1. Intermediate entity d-r-map is added to expand each N:N relationship to a 1:N relationship followed by a 1:1 relationship.^Foot 1

Figure 2-2 Car-Racing Example, Directed Entity-Relationship Diagram (2)

Description of "Figure 2-2 Car-Racing Example, Directed Entity-Relationship Diagram (2)"

Mapping table driver_race_map implements intermediate entity d-r-map. It has the following columns:

• driver_race_map_id — primary key

• race_id — (1) foreign key that links to primary-key column race_id of table race and (2) unique key (so the table has no duplicate rows: there can't be two entries for the same driver for a particular race)

• driver_id — foreign key that links to primary-key column driver_id of table driver

• position

Together with the relations defined by their foreign-key and primary-key links, the car-racing tables form a dependency graph. This is shown in Figure 3-1.

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);