@bstorer said:
...
I know Hugh Darwen's work, as well as work of Data, Codd a lot of other theoreticians and practitioners of relational model. And I have my oppinion, which doesn't neccessary fits in one frame that fits all (as well as all others).
The problem with you is, although you red some theory, you don't understand it. You just use it to show "your knowlege".
I'll try to explain you how it looks like using relational theory (not SQL), once more.
There is relation "people" that covers name, surname, date of birth - all mandatory fields.
There is another relation with SSN numbers. It has less tuples because not every person has SSN.
There's no nulls at all.
There is relationship established between people and SSNs.
There's still no nulls.
Based on those two relations, two additional projections can be created:
- people with SSN
- people without SSN
This could be done in different way too. You can have two relations: people with SSN and people without SSN, and two projections: people and SSNs - it doesn't matter. Projections and relations by relational theory must be interchangable without affecting nor data neither users of data. But this is not important.
Important is that when you ask "I want list of people with SSN different than '1234' you are addresing relation or projection that contains only and only people with SSN.
Why? Because tuple with null inside is not a tuple (check Darwen's work, or Codd or Data). You can not expect that people from other relation will appear.
If you want all of them, you have to make union of two projections - People with SSN different than '1234' and people without SSN.
There's no nulls.
There's no ternary logic.
There's no missinterpretations.
You can do that in all existing RDBMSes.
Where is the problem? It is in the fact that you are allowed to use nulls and you use them. And you do that even you are against them. Much worse is that you use them in a completly wrong manner and without understanding basic principles of relational theory.
Computers are stupid. They do exactly what they are told to do. They can't read mind and they can't read between lines. They can not know there's difference in command "Give me the list of the people with SSN different than '1234'" based on who's asking. You or me. Or sameone else. They answer just following the logic: If you asked me to check people's SSNs, I'll check only relation representing people that has SSN. And that's the only right relation/projection/set to be checked. People without SSN does not belong here. They are "pizzas".Don't mix them with 9s.
And, as you don't understand theory, your answer is - I need something better. There are flaws in implementations of relational model, especially in implemantations of SQL. But to say
@bstorer said:
Frankly, we ought to be able to work directly with the database programmatically instead of using an intermediate langauge anyway.
just shows once more your deep not understanding of relational theory and Hugh Darwen's work.
No one should be allowed to work directly with the database
programmatically, because in that case that's not database anymore. It's a file with bunch of data inside. If you want to do that way, you can. You just don't need database.