2 Introduction To Car-Racing Duality Views Example
Data for Formula 1 car races is used here to present the features of JSON-relational duality views. This use-case example starts from an analysis of the kinds of JSON documents needed. It then defines corresponding entities and their relationships, relational tables, and duality views built on those tables.
Note:
An alternative approach to creating duality views is available to migrate an application that has existing sets of related documents, so that it uses duality views.
For that you can use the JSON-to-duality migrator, which automatically infers and generates the appropriate duality views. No need to manually analyze the different kinds of documents to discover implicit entities and relationships, and then define and populate the relevant duality views and their underlying normalized tables.
The migrator does all of that for you. By default, whatever document parts can be shared within or across views are shared, and the views are defined for maximum updatability.
See From JSON To Duality.
For the car-racing example we suppose a document-centric application that uses three kinds of JSON documents: driver, race, and team. Each of these kinds shares some data with another kind. For example:
-
A driver document includes, in its information about a driver, identification of the driver's team and information about the races the driver has participated in.
-
A race document includes, in its information about a particular race, information about the podium standings (first-, second-, and third-place winners), and the results for each driver in the race. Both of these include driver and team names. The racing data is for a single season of racing.
-
A team document includes, in its information about a team, information about the drivers on the team.
Operations the application might perform on this data include the following:
-
Adding or removing a driver, race, or team to/from the database
-
Updating the information for a driver, race, or team
-
Adding a driver to a team, removing a driver from a team, or moving a driver from one team to another
-
Adding race results to the driver and race information
The intention in this example is that all common information be shared, so that, say, the driver with identification number 302 in the driver duality view is the same as driver number 302 in the team view.
You specify the sharing of data that's common between two duality views by defining relations between them. You do this by specifying primary and foreign keys for the tables that underlie the duality views.
When you define a given duality view you can control whether it's possible to insert into, delete from, or update the documents supported by the view and, overriding those constraints, whether it's possible to insert, delete, or update a given field in a supported document. By default, a duality view is read-only: no inserting, deleting, or updating documents.
- Car-Racing Example, JSON Documents
The car-racing example has three kinds of documents: a team document, a driver document, and a race document. - Car-Racing Example, Entity Relationships
Driver, car-race, and team entities are presented, together with the relationships among them. You define entities that correspond to your application documents in order to help you determine the tables needed to define the duality views for your application. - Car-Racing Example, Tables
Normalized entities are modeled as database tables. Entity relationships are modeled as links (constraints) between primary-key and foreign-key columns. Tablesteam
,driver
, andrace
are used to implement the duality views that provide and support the team, driver, and race JSON documents used by the car-racing application.
See Also:
-
Working with JSON Relational Duality Views using SQL, a SQL script that mirrors the examples in this document
2.1 Car-Racing Example, JSON Documents
The car-racing example has three kinds of documents: a team document, a driver document, and a race document.
A document supported by a duality view always includes, at its top (root)
level, a document-identifier field,
_id
, that corresponds to the primary-key columns of the
tables that underlie the view. See Document-Identifier Field for Duality Views. (In the car-racing example each such table has a single primary-key
column.)
The following naming convention is followed in this documentation:
-
The document-identifier field (
_id
) of each kind of document (team, driver, or race) corresponds to the root-table primary-key column of the duality view that supports those documents. For example, field_id
of a team document corresponds to primary-key columnteam_id
of tableteam
, which is the root table underlying duality viewteam_dv
. -
Documents of one kind (e.g. team), supported by one duality view (e.g.
team_dv
) can include other fields named...Id
(e.g.driverId
), which represent foreign-key references to primary-key columns in tables underlying other duality views — columns that contain data that's shared. For example, in a team document, fielddriverId
represents a foreign key that refers to the document-identifier field (_id
) of a driver document.
Note:
Only the application-logic document content, or payload of each document, is shown here. That is, the documents shown
here do not include the automatically generated and maintained, top-level field
_metadata
(whose value is an object with fields
etag
and asof
). However, this
document-handling field is always included in documents supported by a
duality view. See Creating Duality Views for information about field _metadata
.
Example 2-1 A Team Document
A team document includes information about the drivers on the team, in addition to information that's relevant to the team but not necessarily relevant to its drivers.
-
Top-level field
_id
uniquely identifies a team document. It is the document-identifier field. Columnteam_id
of tableteam
corresponds to this field; it is the table's primary key. -
The team information that's not shared with driver documents is in field
_id
and top-level fieldsname
andpoints
. -
The team information that's shared with driver documents is in fields
driverId
,name
, andpoints
, under fielddriver
. The value of fielddriverId
is that of the document-identifier field (_id
) of a driver document.
{"_id" : 302,
"name" : "Ferrari",
"points" : 300,
"driver" : [ {"driverId" : 103,
"name" : "Charles Leclerc",
"points" : 192},
{"driverId" : 104,
"name" : "Carlos Sainz Jr",
"points" : 118} ]}
Example 2-2 A Driver Document
A driver document includes identification of the driver's team and information about the races the driver has participated in, in addition to information that's relevant to the driver but not necessarily relevant to its team or races.
-
Top-level field
_id
uniquely identifies a driver document. It is the document-identifier field. Columndriver_id
of thedriver
table corresponds to this field; it is that table's primary key. -
The driver information that's not shared with race or team documents is in fields
_id
,name
, andpoints
. -
The driver information that's shared with race documents is in field
race
. The value of fieldraceId
is that of the document-identifier field (_id
) of a race document. -
The driver information that's shared with a team document is in fields such as
teamId
, whose value is that of the document-identifier field (_id
) of a team document.
Two alternative versions of a driver document are shown, with and without nested team and race information.
Driver document, with nested team and race information:
Field teamInfo
contains the nested team information
(fields teamId
and name
). Field
raceInfo
contains the nested race information (fields
raceId
and name
).
{"_id" : 101,
"name" : "Max Verstappen",
"points" : 258,
"teamInfo" : {"teamId" : 301, "name" : "Red Bull"},
"race" : [ {"driverRaceMapId" : 3,
"raceInfo" : {"raceId" : 201,
"name" : "Bahrain Grand Prix"},
"finalPosition" : 19},
{"driverRaceMapId" : 11,
"raceInfo" : {"raceId" : 202,
"name" : "Saudi Arabian Grand Prix"},
"finalPosition" : 1} ]}
Driver document, without nested team and race information:
Fields teamId
and team
are not nested in a
teamInfo
object. Fields raceId
and
name
are not nested in a raceInfo
object.
{"_id" : 101,
"name" : "Max Verstappen",
"points" : 25,
"teamId" : 301,
"team" : "Red Bull",
"race" : [ {"driverRaceMapId" : 3,
"raceId" : 201,
"name" : "Bahrain Grand Prix",
"finalPosition" : 19},
{"driverRaceMapId" : 11,
"raceId" : 202,
"name" : "Saudi Arabian Grand Prix",
"finalPosition" : 1} ]}
Example 2-3 A Car-Race Document
A race document includes, in its information about a particular race, information about the podium standings (first, second, and third place), and the results for each driver in the race. The podium standings include the driver and team names. The result for each driver includes the driver's name.
Both of these include driver and team names.
-
Top-level field
_id
uniquely identifies a race document. It is the document-identifier field. Columnrace_id
of therace
table corresponds to this field; it is that table's primary key. -
The race information that's not shared with driver or team documents is in fields
_id
,name
(top-level),laps
,date
,time
, andposition
. -
The race information that's shared with driver documents is in fields such as
driverId
, whose value is that of the document-identifier field (_id
) of a driver document. -
The race information that's shared with team documents is in field
team
(underwinner
,firstRunnerUp
, andsecondRunnerUp
, which are underpodium
).
Two alternative versions of a race document are shown, with and without nested driver information.
Race document, with nested driver information:
{"_id" : 201,
"name" : "Bahrain Grand Prix",
"laps" : 57,
"date" : "2022-03-20T00:00:00",
"podium" : {"winner" : {"name" : "Charles Leclerc",
"team" : "Ferrari",
"time" : "02:00:05.3476"},
"firstRunnerUp" : {"name" : "Carlos Sainz Jr",
"team" : "Ferrari",
"time" : "02:00:15.1356"},
"secondRunnerUp" : {"name" : "Max Verstappen",
"team" : "Red Bull",
"time" : "02:01:01.9253"}},
"result" : [ {"driverRaceMapId" : 3,
"position" : 1,
"driverInfo" : {"driverId" : 103,
"name" : "Charles Leclerc"},
{"driverRaceMapId" : 4,
"position" : 2,
"driverInfo" : {"driverId" : 104,
"name" : "Carlos Sainz Jr"},
{"driverRaceMapId" : 9,
"position" : 3,
"driverInfo" : {"driverId" : 101,
"name" : "Max Verstappen"},
{"driverRaceMapId" : 10,
"position" : 4,
"driverInfo" : {"driverId" : 102,
"name" : "Sergio Perez"} ]}
Race document, without nested driver information:
{"_id" : 201,
"name" : "Bahrain Grand Prix",
"laps" : 57,
"date" : "2022-03-20T00:00:00",
"podium" : {"winner" : {"name" : "Charles Leclerc",
"team" : "Ferrari",
"time" : "02:00:05.3476"},
"firstRunnerUp" : {"name" : "Carlos Sainz Jr",
"team" : "Ferrari",
"time" : "02:00:15.1356"},
"secondRunnerUp" : {"name" : "Max Verstappen",
"team" : "Red Bull",
"time" : "02:01:01.9253"}},
"result" : [ {"driverRaceMapId" : 3,
"position" : 1,
"driverId" : 103,
"name" : "Charles Leclerc"},
{"driverRaceMapId" : 4,
"position" : 2,
"driverId" : 104,
"name" : "Carlos Sainz Jr"},
{"driverRaceMapId" : 9,
"position" : 3,
"driverId" : 101,
"name" : "Max Verstappen"},
{"driverRaceMapId" : 10,
"position" : 4,
"driverId" : 102,
"name" : "Sergio Perez"} ]}
Related Topics
Parent topic: Introduction To Car-Racing Duality Views Example
2.2 Car-Racing Example, Entity Relationships
Driver, car-race, and team entities are presented, together with the relationships among them. You define entities that correspond to your application documents in order to help you determine the tables needed to define the duality views for your application.
From the documents to be used by your application you can establish entities and their relationships. Each entity corresponds to a document type: driver, race, team.
Unlike the corresponding documents, the entities we use have no content overlap — they're normalized. The content of an entity (what it represents) is only that which is specific to its corresponding document type; it doesn't include anything that's also part of another document type.
-
The driver entity represents only the content of a driver document that's not in a race or team document. It includes only the driver's name and points, corresponding to document fields
name
andpoints
. -
The race entity represents only the content of a race document that's not in a driver document or a team document. It includes only the race's name, number of laps, date, and podium information, corresponding to document fields
name
,laps
,date
, andpodium
. -
The team entity represents only the content of a team document that's not in a document or race document. It includes only the team's name and points, corresponding to document fields
name
andpoints
.
Two entities are related according to their cardinality. There are three types of such relationships:Foot 1
- One-to-one (1:1)
- An instance of entity A can only be associated with one instance of entity B. For example, a driver can only be on one team.
- One-to-many (1:N)
- An instance of entity A can be associated with one or more instances of entity B. For example, a team can have many drivers.
- Many-to-many (N:N)
- An instance of entity A can be associated with one or more instances of entity B, and conversely. For example, a race can have many drivers, and a driver can participate in many races.
See Also:
A many-to-one (N:1) relationship is just a one-to-many relationship looked at from the opposite viewpoint. We use only one-to-many.
See Figure 2-1. An arrow indicates the relationship direction, with the arrowhead pointing to the second cardinality. For example, the 1:N arrow from entity team to entity driver points toward driver, to show that one team relates to many drivers.
Figure 2-1 Car-Racing Example, Directed Entity-Relationship Diagram (1)
Description of "Figure 2-1 Car-Racing Example, Directed Entity-Relationship Diagram (1)"
A driver can only be associated with one team (1:1). A team can be associated with multiple drivers (1:N). A driver can be associated with multiple races (N:N). A race can be associated with multiple drivers (N:N).
Related Topics
See Also:
Database normalization (Wikipedia)
Parent topic: Introduction To Car-Racing Duality Views Example
2.3 Car-Racing Example, Tables
Normalized entities are modeled as database tables. Entity relationships are
modeled as links (constraints) between primary-key and foreign-key columns. 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):
-
The top-level (root) table for the view must have a primary key, composed of one or more columns that together uniquely identify a table row. This prevents any ambiguity that could arise from using a
NULL
able unique key or a unique key that has someNULL
columns.The primary-key column values correspond to the value of the document-identifier field,
_id
, of the JSON document that the table is designed to support — see Document-Identifier Field for Duality Views. (There is only one primary-key column for each of the tables used in the car-racing example.) -
Each of the other tables used to define a duality view must also have a primary key or a unique key. A unique key is a set of one or more columns that uniquely identify a row in the table. If there is no primary key then at least one column of the unique key must not be
NULL
. -
Each primary key and each unique key must have a unique index defined on it. 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-, 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.
Like unique keys, primary keys and foreign keys can be composite: composed of multiple columns. In this documentation we generally speak of them as single-column keys, but keep this possibility in mind wherever keys are mentioned.
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
NULL
able, then mark it as NOT NULL
in the table
definition.
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 columnteam_id
of tableteam
-
-
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 2
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 columnrace_id
of tablerace
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 columndriver_id
of tabledriver
-
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.
Example 2-4 Creating the Car-Racing Tables
This example creates each table with a primary-key column, whose values
are automatically generated as a sequence of integers, and a unique-key column,
name
. This implicitly also creates unique indexes on the
primary-key columns. The example also creates foreign-key indexes.
Column podium
of table race
has data
type JSON
. Its content is flexible: it need not conform to any
particular structure or field types. Alternatively, its content could be made to
conform to (that is, validate against) a particular JSON schema.
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);
Note:
Primary-key, unique-key, and foreign-key integrity constraints must be defined for the tables that underlie duality views (or else an error is raised), but they need not be enforced.
In some cases you might know that the conditions for a given constraint
are satisfied, so you don't need to validate or enforce it. You might nevertheless
want the constraint to be present, to improve query performance. In that case, you
can put the constraint in the RELY
state, which asserts that
the constraint is believed to be satisfied. See RELY Constraints in a Data Warehouse in Oracle Database Data
Warehousing Guide.
You can also make a foreign key constraint DEFERRABLE
, which
means that the validity check is done at the end of a transaction. See Deferrable Constraints in Oracle Database
Concepts
Note:
The SQL data types allowed for a column in a table underlying a duality
view are 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
, and VECTOR
. An error is raised if you specify
any other column data type.
Related Topics
See Also:
-
JSON Schema in Oracle Database JSON Developer’s Guide
-
CREATE TABLE in Oracle Database SQL Language Reference
Parent topic: Introduction To Car-Racing Duality Views Example
Footnote Legend
Footnote 1: In the notation used here, N does not represent a number; it's simply an abbreviation for "many", or more precisely, "one or more".Footnote 2: In the notation used here, N does not represent a number; it's simply an abbreviation for "many", or more precisely, "one or more".