Re: satisfies algorithm
Date: Mon, 28 Jul 2008 14:58:07 -0300
Message-ID: <488e08b3$0$4033$9a566e8b_at_news.aliant.net>
David Cressey wrote:
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message
> news:vJ6dnRSku8yechfVnZ2dneKdnZydnZ2d_at_giganews.com...
>
>>"Brian Selzer" <brian_at_selzer-software.com> wrote in message >>news:92kik.14667$cW3.1512_at_nlpi064.nbdc.sbc.com... >> >>>Yes. Normalize. A schema that is in BCNF does not have any nontrivial >>>functional dependencies where the determinant is not also a key. Where >>>there is a key, there should also be a unique index of some sort, making >>>it impossible for there to be two tuples with the same determinant. >>> >> >>Unique indexes have nothing to do with keys. A key is a logical construct >>whereas an index is merely one possible physical structure used by some >>DBMSs. A key does not require an index.
>
> True in theory, but very misleading to any newbies who may read it.
>
> A declared primary key with no index to back it up is generally a
> performance nightmare.
The problem with generalizations is they are invariably wrong. Indexing a relation that fits on a single database page is almost always a performance nightmare. Relatively speaking.
> Enforcing uniqueness of keys without an index requires a full table scan.
Which is very fast for small tables.
> For this reason most DBMS builders provide an automatic index when a primary
> key is declared.
Sadly, true.
> To be sure, the concept of "key" and "primary key" are different concepts.
> Also a key would be a key, even if it were undeclared.
>
> Relying on a key that is not declared either via a PRIMARY KEY constraint or
> UNIQUE and NOT NULL constraints, is an invitation to database damage done
> by a rogue process, possibly as the result of carelees programming coupled
> with data.
>
> Newbies need to understand all this.
>
> Having said this, I repeat that you original comment is correct in theory.
> And this is, after all, the database theory newsgroup.
His original comment was correct in all respects. Received on Mon Jul 28 2008 - 19:58:07 CEST