Comment by talaketu | Hacker News Reader

Comment by talaketu | Hacker News Reader

> Referential integrity implies a closed-world assumption where transactions on the database are serialized by (conceptually) suspending the world synchronously, applying the required changes, and resuming the world again when referential integrity has been restored successfully, rolling back any changes otherwise. Assuming a closed world is, we claim, both a strength and a weakness of the relational model. [...] The closed-world assumption, however, is in direct contradiction with distribution and scale-out. The bigger the world, the harder it is to keep closed.

This is the first time I have heard the expression "Closed World Assumption" used to refer to transactional atomicity.

For example, the Terry Halpin textbook "Information Modeling and Relational Databases" describes the CWA thus:

> When modeling a business domain, one may take different positions with respect to the completeness of the information captured in the model. The Closed World Assumption (CWA) is the assumption that all relevant facts are known (i.e, appear in the model, either as asserted facts or derived facts) and that all the relevant business rules are known.)

I find this discrepancy in academic terminology difficult to reconcile.

I am not sure what you are calling a discrepancy. For a Database to guaranty the assumption in Halpin's definition, a database must guaranty all business rules and relevant facts are known, and followed.

Say we are going to insert a row in a database that has a foreign key, for Halpin's assumption to hold the database must verify that the foreign key is present and valid in the data set. If it doesn't we have violated the assumption. Same for the transaction quote you mention.

It is an assumption you make can make because it is a guaranty your database provides.

The discrepancy is that Halpin's use of the phrase relates to the correspondence between the real world and the database - to the modelling relationship itself, whereas Meijer and Bierman's use solely relates to the valid states of the database - separate from any meaning that might be taken.

You highlighted the element of referential integrity within their description, whereas I highlighted atomicity. But both are just operational characteristics of the database.

Halpin's CWA comes into play even within a database that is not being updated and has only one relation. Eg. Consider a relation P that models the fact type that person with NAME was born on DOB. P = { {NAME: Bob, DOB: 2/2/22} }

CWA answers the query "Was Mary born on 2/2/22?" with False.

I would argue that it is also difficult to reconcile the idea that these worlds are flip sides of a coin with the idea that there are fundamentally different assumptions made about the data.

Indeed I would further argue that the the awkward handling of NULLs in SQL is in part because it assumes too open of a world.

SQL is not relational algebra. I think the more mathematic ideas in the article should be viewed in the context of relational alebra and not in the context of SQL.

So, the problems with NULLs is an SQL problem not a relational algebra problem. The 'flip side' is really the flip side of relational algebra and not SQL even though the article is not very clear in keeping the two separate.

SQL is not relational algebra but rather an attempt to bridge a relational algebra/programming gap. The null issue arises in SQL due to it being used in a relational algebra context in one case and a software development context in another. The point though is that these are treated as equivalents because of an open world assumption.

Reply on news.ycombinator.com