Book Review of Database in Depth by C.J Date

This book is not a book for beginners. A good exposure to different SQL products will help you learn what concepts run on the database products that you use. Although the book doesn’t reference specific products, it focuses on relational model concepts, which form the theoretical foundation of all RDBMS. The book presents C.J Date’s detailed arguments on E.F. Codd’s who is the creator of the relational model, a mathematician at IBM.

Notable C.J Date’s detailed arguments:

• Nulls have no place in the relational model.

• Views must be updatable, because if they aren’t, we violate the principle of interchangeability.

• Database constraint check must be immediate. — this one particularly resonates with me, so I’ll elaborate.

“C.J Date implies that some constraints must be deferred because of real-world modeling, but he didn’t think that the deferred checking should be the default”.

For example, consider a self-referencing table (loosely defined here):

EMP | EmpNo | MgrNo

Constraint: MgrNo ⊆ EmpNo

The constraint is that MgrNo has to be an existing employee

Now, how can we insert the first employee? MgrNo must reference an existing EmpNo because the row doesn’t exist yet. Therefore, we need NULL or deferred checking.

The SQL solution is to allow it to be NULL and defer the foreign key until COMMIT. But date argue that the correct relational solution is to create a new relation.

INSERT INTO EMP VALUES (1, NULL); — Inserting temporary null values on EMP

Creating two new tables called MANAGES — that represent higher position employee.

EMPLOYEE { EmpNo }

MANAGES { EmpNo, MgrNo }

Now using a query INSERT INTO EMPLOYEE VALUES (1); and INSERT INTO MANAGES VALUES (1, 1); will work because the employee exists.

Now there are two points made here.

• “ Date implies that some constraints must be deferred because of real-world modeling, but he didn’t think that the deferred checking should be the default”.

  • “Database constraints must be satisfied at the statement boundaries” — it should be evaluated at semicolons, then we might not need TRANSACTIONS.“ — Consequently, patterns like Unit of Work (which batch multiple operations into a single transaction to maintain consistency) are not required to handle simple constraint enforcement.

This book is good for anyone who is a practitioner — a backend engineer, dba , data analyst , etc. Having a good grasp of history and knowing your tools concepts will always help you to learn.