Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!uunet!pmafire!mica.inel.gov!gem-hy!cdm
From: cdm@gem-hy.Inel.GOV (Dale Cook)
Newsgroups: comp.databases
Subject: Re: SQL Duplicate Row Deletion ???
Message-ID: <1991Apr4.004438.15592@inel.gov>
Date: 4 Apr 91 00:44:38 GMT
References: <91091.141528SYSPMZT@GECRDVM1.BITNET> <1991Apr3.010838.2063@eng.ufl.edu> <1991Apr3.085710.57@cim-vax.honeywell.com>
Sender: news@inel.gov
Reply-To: cdm@gem-hy.Inel.GOV (Dale Cook)
Organization: Idaho National Engineering Laboratory, Idaho Falls, Idaho
Lines: 71

In article <1991Apr3.085710.57@cim-vax.honeywell.com>,
tdoyle@cim-vax.honeywell.com writes:
|> In article <1991Apr3.010838.2063@eng.ufl.edu>,
tsao@helios.tcad.ee.ufl.edu ( Tsao) writes:
|> > In article <91091.141528SYSPMZT@GECRDVM1.BITNET>,
SYSPMZT@gecrdvm1.crd.ge.com writes:
|> > |> I've made a nice mistake loading data twice over several days
into a table, and
|> > ......
|> > 
|> > 	I am now using Ingres, I am surprised that it allows the user to append 
|> > duplicated rows (including a null row) into a table unless you
define a key
|> > using create unique index.
|> > 
|> > 	Would anybody give me an example justifying the need for
duplicated tuples
|> > in a table ?
|> > 
|> >  
|> 
|> In a "clean" database implementation there is NO excuse for such behavior,
|> but alas it is simple/machine efficient? for DBMS vendors to do it this way.
|> 
|> Since the process of relational modelling means that ultimately the 
|> tuple/relation is supposed to be in third normal form (or a close
|> approximation) and thus should have one and only one set of attributes that
|> uniquely identify the object. This set of attributes then should
automatically
|> have a unique index. None of the popular DBMS enforce this.
|> 

Sorry, but I must disagree.  Attributes do not _identify_ an instance of an
entity, they merely describe it.  A subtle, but important, distinction.
There is nothing in data modelling that says multiple instances of an entity
or relationship cannot have identical attributes.  What data modelling says
you MUST have is an instance of an entity uniquely identifiable by a "primary
key".  It is sufficient to have a simple sequence number to satisfy this
rule.  Relational DBMS systems satisfy this requirement for you.  Suffice it 
to say that every row in the table has its own internal unique identifier.
If the sequence number is unimportant information, why not let the DBMS keep
track of it for you?

|> Another problem with the popular DBMS's is that they don't treat null values
|> correctly. If the attributes in the unique index are null, then multiple
|> entries with null should be permitted (using the theory that null !=
null, i.e
|> the two nulls are different).
|> 

I don't follow this.

|> Bipin Prasad      bprasad@honcim1.honeywell.com

----------------------------------------------------------------------
--- Dale Cook          "I was born at night, but by god, it wasn't 
    cdm@inel.gov         last night."
The opinions are mine.  The following disclaimer is my employers.
----------------------------------------------------------------------
========== long legal disclaimer follows, press n to skip ===========

Neither the United States Government or the Idaho National Engineering
Laboratory or any of their employees, makes any warranty, whatsoever,
implied, or assumes any legal liability or responsibility regarding any
information, disclosed, or represents that its use would not infringe
privately owned rights.  No specific reference constitutes or implies
endorsement, recommendation, or favoring by the United States
Government or the Idaho National Engineering Laboratory.  The views and
opinions expressed herein do not necessarily reflect those of the
United States Government or the Idaho National Engineering Laboratory,
and shall not be used for advertising or product endorsement purposes.