Chapter
Perspectives on the Relational Paradigm of Data
The Temporal SQL Standards: ISO 9075:2011 and TSQL2
Enterprise Data Architects
Database, Data Warehouse, and Data Mart Developers
Computer Science Students
A Companion Volume to Managing Time in Relational Databases
1 Bitemporal Data: Preliminaries
Nontemporal, Unitemporal and Bitemporal Data
Foreign Keys and Primary Keys
Stand-Alone Representation of Rows
Temporal Foreign Keys and Temporal Primary Keys
Stand-Alone Representation of Temporal Rows
A Note on Assertion-Time Tables
Temporal Foreign Keys and Temporal Primary Keys
Semantics and its Implementations
2 Time and Temporal Terminology
Hardware Clock Ticks and DBMS Clock Ticks
DBMS Clock Ticks and Chronons
Chronons, Time Periods, and Other Dependent Concepts
Four Conventions for Time Period Representation With Delimiters
Representing Open Time Periods
The TSQL2 and Computer Science Terminology
The ISO and IBM Terminology
The Asserted Versioning Terminology
The Computer Science Terminology
The Asserted Versioning Terminology
A Choice of Terminologies
3 The Relational Paradigm: Mathematics
Functions and Primary Keys
4 The Relational Paradigm: Logic
Statements and Statement Schemas
Logic and the Relational Paradigm
5 The Relational Paradigm: Ontology
A Data Modeling Perspective
A Philosophical Perspective
A Set Theoretic Perspective
A Logic and Language Perspective
The Relational Paradigm Ontology: Aristotelian Roots
Beyond the Aristotelian Roots
The Relational Paradigm Ontology
A Middle Level Extension to the Relational Paradigm Ontology
Primary Keys Natural Keys, Foreign Keys
Objects, Events and Change
Integrating the Mathematics and Ontology of the Relational Paradigm
6 The Relational Paradigm: Semantics
Rows, Statements, Assertions and Kindred Notions
Rows, Inscriptions and Sentences
Disambiguating Statements
Statements and Statement Schemas
Statements and Assertions
Expressing Assertions Explicitly
7 The Allen Relationships
Why the Allen Relationships are Important
A Taxonomy of the Allen Relationships
The Basic Allen Relationships
Combinations of the Allen Relationships
A Binary Partitioning of the Allen Relationships Taxonomy
Common Timeline Time Periods: [Includes] or [Excludes]
[Includes]: [Contains] or [Overlaps]
[Contains]: [Equals] or [Encloses]
[Encloses]: [Aligns With] or [During]
[Aligns With]: [Starts] or [Finishes]
[Excludes]: [Before] or [Meets]
An Allen Relationship Thought Experiment
8 Temporal Integrity Concepts and Notations
Cubes, Slices and Cells: Data in Three-Dimensional Temporal Space
Semantically Anomalous Relational Tables
9 Temporal Entity Integrity
Bitemporal Entity Integrity
Some Bitemporal Transactions
State-Time Entity Integrity
Conventional Entity Integrity
10 Temporal Referential Integrity
State-Time Referential Integrity
A State-Time Delete: Block Mode
A State-Time Delete: Cascade Mode
A State-Time Delete: Set Null Mode
Bitemporal Referential Integrity
Conventional Referential Integrity
An Overview Of Temporal Transactions
Basic Temporal Transactions on State-Time Tables
A State-Time Insert With Default Time
A State-Time Update With Default Time
A State-Time Update With Specified State Time
A State-Time Delete With Default Time
Basic Temporal Transactions on Bitemporal Tables
A Bitemporal Insert With Default Time
A Bitemporal Update With Default Time
A Bitemporal Update With Specified State Time
A Bitemporal Delete With Default Time
Whenever Temporal Transactions
A Whenever Insert Transaction
A Whenever Update Transaction
A Whenever Delete Transaction
Temporal Merge Transactions
12 Basic Temporal Queries
Bitemporal Tables and Views
The Conventional Table View
Point-In-Time Range Queries
13 Advanced Temporal Queries
A Basic Temporal Range Multi-Table Query
Step 1: Decoalesce and Restrict on Assertion Time
Step 2: Decoalesce and Restrict on State Time
Step 3: Drop Assertion-Time Period Columns
Step 4: Align on State-Time Boundaries
Step 5: Join on RefId and State Time
A Complex Temporal Range Multi-Table Query
Step 1: Decoalesce and Restrict on Assertion Time
Step 2: Decoalesce and Restrict on State Time
Step 3: Drop Assertion-Time Period Columns
Step 4: Align on State-Time Boundaries
Step 5: Join on RefId and State-Time
Why Temporal Range Multi-Table Queries are Complex
Future Assertion Time: Semantics
Challenging the Six-Fold Way
The Incompleteness of the Six-Fold Way
Future Assertion Time and Future State Time
Future Assertion Time: Implementation
Future Assertion Time Locking
Future Transactions With Assertion-Time Locking
Updates and Corrections to Conventional Tables
Double-Timestamped Tables
Double-Timestamps and Corrections
The Double-Timestamped Dilemma
The Bitemporal Data Solution
16 Bitemporal Data and the Inmon Data Warehouse
A Brief History of the Data Warehouse
What is an Inmon Data Warehouse?
Support for Management Decision-Making
Why Unitemporal Tables Cannot Be Both Time-Variant and Nonvolatile
The Enterprise Data Warehouse Redefined
The Semantics of the EDW and the Question of its Physical Instantiation
Inmon’s Arguments for a Physical EDW
17 Semantic Integration via Messaging
The Objectives of an Enterprise Database
Two Paths to Semantic Integration
The Enterprise Data Model as a Canonical Message Model
The Failed Mission of the Enterprise Data Model
A New Mission for the Enterprise Data Model
A Mapping Rules Dictionary
The Extended Enterprise Data Model
18 Bitemporal Data and the Kimball Data Warehouse
Star Schemas and Relational Databases
The Star Schema Data Warehouse Architecture
The Star Schema Design Pattern
Reconceptualizing Star Schemas: Fact Tables and Dimension Tables
Surrogate Keys and Natural Keys
A Bitemporal Dimension Case Study
Summary of the Case Study
Bitemporal Dimensions Versus Slowly-Changing Dimensions
19 Time, Types and the Future of Relational Databases
Tritemporal Data and Statement Provenance
Inscription Time, State Time, Speech Act Time
Ontologizing Relational Databases
The Extended Relational Paradigm Ontology
The Extended Relational Paradigm Metamodel
The Semantic Component of the Extended Relational Paradigm Metamodel
Referent/Statement-Schema
Attribute/Statement-Schema
Statement-Schema/Atemporal-State
Atemporal-State/Statement
Inscription/Inscription-Act
The Integrated Metamodel: Semantic and Pragmatic Components
Atomic Statements and Binary Tables
Recommendations for IT Professionals in End-User IT Organizations
Recommendations for Standards Committees and Vendors
Remove the Ability to Correct Data in State-Time Tables
Specify Referent Identifiers in SQL Table Definitions
Specify Temporal Unique Identifiers in SQL Table Definitions
Package the Bitemporalization of Conventional Tables
Modify SQL Query Syntax to Clarify Semantics
Add Whenever Temporal Transactions to the Standard
Add Future Transaction Time to the Standard
Afterword: Reflections on Mindfulness and Bitemporality
Bitemporal Data
：Theory and Practice
Disclaimer: Any content in publications that violate the sovereignty, the constitution or regulations of the PRC is not accepted or approved by CNPIEC.
