Re: What predicates the following relation represents

"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message news:<c4u8li$106q$1_at_gazette.almaden.ibm.com>...
> > Here's another question:
> > Suppose I have a relation containing the tuple ('nuts', 25).
> > If I then do an INSERT of the tuple ('nuts', 25) in SQL I get an key
> > violation error (assuming the primary key is defined). In a truly
> > relational DBMS, should it silently accept this, but just do nothing?
>
> In a truly relational database, INSERT is a derived concept. Specifically it
> is a shorthand for an assignment. Whether or not you include a check for
> 'key violation' in that shorthand is of little import IMO.

OK I think you understand what I'm saying but I think some others in the thread maybe don't. I'm in agreement with Date that saying something twice doesn't make it any truer. What I'm also saying is that saying something twice doesn't make it wrong.

Perhaps an example will clarify:

Suppose I have a relation with columns (employeeID, name, dept) where employeeID is the primary key.
Say it contains the tuples as follows:
{(1, Alan, 10), (2, Bill, 10), (3, Chas, 20)}

Now if I try to insert the tuple (1, Dave, 30) I want to get an error, because I can only have one occurrence of the primary key. I'm asserting a proposition that is incompatible with an existing proposition.

But say I try to insert the tuple (1, Alan, 10). Although I'm still trying to insert a tuple that has the same primary key as an existing tuple, *the entire tuple is the same*. All I'm doing is repeating a true proposition, I'm not contradicting any of my existing propositions.

So what I'm saying is that in this case the DBMS should say "OK, I've accepted that proposition no problem" i.e. not give an error.

Of course, after this the relation will still be {(1, Alan, 10), (2, Bill, 10), (3, Chas, 20)} because relations are sets and duplicates aren't meaningful for sets. So the outcome of my insert will be to leave the relation the same as it was.

I can think of one problem that might occur if this doesn't give an error:
Suppose I have a new employee also called Alan due to start in dept 10.
Now I might mistakenly think the ID no.1 is unused and do my insert, thinking I have successfully added a new employee to my database. I guess the DBMS tells you how many rows have been added though, so the client application could display this.

Paul. Received on Wed Apr 07 2004 - 10:44:22 CEST