Re: Extending my question. Was: The relational model and relational
Date: Tue, 18 Feb 2003 13:08:54 +0000
>> I collect books and as sometimes happens I have some duplicates. I have
>> two copies of the same book, quite often both of them in mint condition.
>> I have two database entries for that edition. I have two copies of the
>> book on the shelf. Suppose I give one copy away? I take one copy off of
>> the shelf and I delete one record from the database.
>You seem to have missed the point that there is logically no way to delete
>only one of the records without also deleting the other record. One must
>rely on proprietary, non-portable features that directly expose the physical
>storage model. One cannot simply issue a statement to delete the book. One
>must first query the dbms for the physical pointers to matching books then
>arbitrarily choose one of the physical pointers to navigate for the
That's correct if you use a relational database. This is because relational databases operate under the axiom that there cannot be two identical records.
>This means that all delete processes must follow this two-step method even
>when no duplicate books exist.
>> It makes no difference whether I delete the record that was created when
>> I acquired the first book or the second. It makes no difference whether
>> I remove the first book or the second.
>It makes a difference whether one can logically address each of the records
>independently, though. How would you construct an SQL delete statement to
>delete just one of the identical book rows?
I wouldn't try to do it using SQL. SQL was designed to work on relational databases, where there cannot be two identical records.
>> I could create a new entity and call it "Acquisition Number" and that's
>> what most librarians do. Acquisition numbers are simply identity fields.
>> They should not be considered part of the logical data structure.
>Logical identifiers are absolutely required for the logical data model.
>That's where logical identifiers are most important. By adding a logical
>identifier, you no longer have a multiset and you might as well use a
>relational dbms instead.
From a purely pragmatic viewpoint I invariably choose databases that at least claim to be relational. My book collection database has an acquisition number field.
-- Bernard Peek bap_at_shrdlu.com www.diversebooks.com: SF & Computing book reviews and more..... In search of cognoscentiReceived on Tue Feb 18 2003 - 14:08:54 CET