Hello

I am still not sure about meaning of 3NF

Let's say I've got a table, named USERS:

Id   Login   Pass   Description

Id was selected as a key, but login has to be unique, so it's also a key.
There are dependencies:

Id -> Pass, Description
Login -> Pass, Description
Id -> Login
Login -> Id

is this 3NF or should it be decomposed to two tables like this:

Id Login
Login Pass Description

thanks in advance, Elves

[ Post a follow-up to this message ]

Elves wrote:
> Hello
>
> I am still not sure about meaning of 3NF
>
> Let's say I've got a table, named USERS:
>
> Id   Login   Pass   Description
>
> Id was selected as a key, but login has to be unique, so it's also a
> key. There are dependencies:
>
> Id -> Pass, Description
> Login -> Pass, Description
> Id -> Login
> Login -> Id
>
> is this 3NF or should it be decomposed to two tables like this:
>
> Id Login
> Login Pass Description
>
> thanks in advance, Elves

In your example ... ID is without purpose which is pretty much the
case for any column misnamed ID. Try this instead:

LOGIN_ID
LOGIN_PWD

If you want to get a bit more complete add:

EXPIRATION_DATE
STATUS_FLAG
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[ Post a follow-up to this message ]

There is no such thing as a"Magical, universal id number"; newbies use
IDENTITY or other proprietary auto-numbering devices to mimick a
sequential tape file when they do not understand RDBMS and keys.

I like to tell people that do this kind of programmng that God put a 17
digit Hebrew on the bottom of everything in creation and they ought to
use it.

Your rule seems to be:
Login -> Pass, Description

or:
CREATE TABLE Passwords
(log_in VARCHAR (8) NOT NULL PRIMARY KEY
CHECK(??),
password VARCHAR (8) NOT NULL
CHECK(??),
user_descr VARCHAR(25) NOT NULL);

You saw how haivng that ID in the table messed up normalization and
would have destroyed your data integrity.

[ Post a follow-up to this message ]

-CELKO- wrote:
> There is no such thing as a"Magical, universal id number"; newbies
use
> IDENTITY or other proprietary auto-numbering devices to mimick a
> sequential tape file when they do not understand RDBMS and keys.

I respect what you have said. On the otherhand, I would like to say
that I believe ID can be an acceptable attribute for an object, even if
a sequential identity scheme is deemed unsuitable. Furthermore, I
wouldn't mind making the Login/Password table employ an ID foreign key
to refer to the ID property. When asked what benefit this expensive
indirection offered, i could only say "maybe i get to have one or more
login/password combinations for each user (feebly suggesting additional
design freedom (ouch!) ) or, worse, that an ID would somehow be better
protected by preventing its everyday use. Well, at least the FDs looked
pretty, I thought.

Additionally, I don't believe that one should reject a sequential
identity scheme based soley upon a distaste for that which is
proprietary (Yes, I enjoyed the BIBLE and I remember other good
objections were also made). It seems reasonable that one could have no
requirement placed upon ID generation other than that it occur quickly.
And quick ID generation is desirable.

And even if you insist upon the dismissal of proprietary Sequences, let
us reflect upon alternatives.

INSER INTO Foobar ( keycol , ... )
VALUES ( COALESCE ( ( SELECT MAX ( keycold ) FROM Foobar ) , 0 ) + 1 ,
... );

It might be the case that I am incorrect, but I don't see how several
threads running the same operation woud never receive the same value
returned by the subquery. I see a race condition here. But I don't
know.

I can only think that some sordid bits of PL/SQL or Transact SQL code
might be needed to perform several operations corresponding to ID
generation, uniqueness verification and possible storage. I haven't
even come up with my own implementation yet.
Eats, shoots, and leaves, and falls down,
jonnie savell

[ Post a follow-up to this message ]

>> I believe ID can be an acceptable attribute for an object, even if a
sequential identity scheme is deemed unsuitable. <<

Acording to ISO-11179 and basic data modeling, "id" is too vague to be
a data element name and RDBMS does not have objects in it.
[vbcol=seagreen] 
based soley upon a distaste for that which is proprietary (Yes, I
enjoyed the BIBLE and I remember other good objections were also made).
<<

Okay, if IT Standards and portability are not enough, then how about
data integrity?  Validation?  Verification?

As part of my joke about the Hebrew numbers, I tell people that unlike
IDENTITY, et al, you can verify it by geeeting an Ultra Orthodox rabbi
who knows the Kabbala to read it to you.
[vbcol=seagreen] 
ID generation other than that it occur quickly. <<

Well, if you don't care if your data is right, you can be VERY fast 
The answer is 42; what was the question?  Who cares!

The "MAX(keycol) +1" trick is good, but you have to watch your
isolation levels.  The nice part is that you can add a check digit,
too.

But the trouble with any of these auto-numberings is that they cannot
be verified in reality.  I add a row and it gets 12 as its generated
pseudo-key; I drop the row; I insert the identical row back; it gets 13
as its generated pseudo-key.  All references to 12 are screwed up
beyond repair.
[vbcol=seagreen] 
code
might be needed to perform several operations corresponding to ID
generation, uniqueness verification and possible storage. <<

Set up the equivalent of a old manual "Forms Manager" which has the
numbers to be used and which tracks the "who, what, where and when" as
they are issued.  The code is not bad at all, you do not have to use a
sequential order, and the check digits can be as complex as you wish.
The audit trail also makes the Trolls in Accounting happier.

[ Post a follow-up to this message ]

"jonnie" <jsavell@gmail.com> wrote in message
news:1105242337.662624.124020@c13g2000cwb.googlegroups.com...
>
> -CELKO- wrote: 
> use 
>
> I respect what you have said. On the otherhand, I would like to say
> that I believe ID can be an acceptable attribute for an object, even if
> a sequential identity scheme is deemed unsuitable. Furthermore, I
> wouldn't mind making the Login/Password table employ an ID foreign key
> to refer to the ID property. When asked what benefit this expensive
> indirection offered, i could only say "maybe i get to have one or more
> login/password combinations for each user (feebly suggesting additional
> design freedom (ouch!) ) or, worse, that an ID would somehow be better
> protected by preventing its everyday use. Well, at least the FDs looked
> pretty, I thought.
>
> Additionally, I don't believe that one should reject a sequential
> identity scheme based soley upon a distaste for that which is
> proprietary (Yes, I enjoyed the BIBLE and I remember other good
> objections were also made). It seems reasonable that one could have no
> requirement placed upon ID generation other than that it occur quickly.
> And quick ID generation is desirable.
>
> And even if you insist upon the dismissal of proprietary Sequences, let
> us reflect upon alternatives.
>
> INSER INTO Foobar ( keycol , ... )
> VALUES ( COALESCE ( ( SELECT MAX ( keycold ) FROM Foobar ) , 0 ) + 1 ,
> ... );
>
> It might be the case that I am incorrect, but I don't see how several
> threads running the same operation woud never receive the same value
> returned by the subquery. I see a race condition here. But I don't
> know.
>
> I can only think that some sordid bits of PL/SQL or Transact SQL code
> might be needed to perform several operations corresponding to ID
> generation, uniqueness verification and possible storage. I haven't
> even come up with my own implementation yet.
> Eats, shoots, and leaves, and falls down,
> jonnie savell
>

Yes, there are appropriate times to use sequences for PKs, but it is a
slippery slope. All too often, inexperienced (or even experienced)
programmers resort to using them because they think its an easy way to do
things. They should only be used when there is no natural key in the data,
IOW, as a last resort. Purchase Order numbers are an example, and even them,
I can come up with a system that would only require partial use of a
sequence. I can't think of an occasion where speed of user ID generation is
an overriding concern.

[ Post a follow-up to this message ]

jonnie wrote:
> -CELKO- wrote:
> 
>
> use
> 
>
>
> I respect what you have said. On the otherhand, I would like to say
> that I believe ID can be an acceptable attribute for an object, even if
> a sequential identity scheme is deemed unsuitable.

I disagree. A person table might have a person_id that uniquely
identifies that person within a specific application. But a column named
ID has no place in any set.

Furthermore, I
> wouldn't mind making the Login/Password table employ an ID foreign key
> to refer to the ID property.

If the login is unique any additional identifier is at best redundant
and at worst a guarantee of losing system integrity.

When asked what benefit this expensive
> indirection offered, i could only say "maybe i get to have one or more
> login/password combinations for each user (feebly suggesting additional
> design freedom (ouch!) ) or, worse, that an ID would somehow be better
> protected by preventing its everyday use. Well, at least the FDs looked
> pretty, I thought.

Maybe only works in poker if everyone else folds.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)


----== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet New
s==----
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000
 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

[ Post a follow-up to this message ]

You didn't make a mistake in using the ID column, but because you
tried to present two different entities as a single one (USERS). There
is actually an entity that represents users and an entity representing
access via login/password. The example you gave is closer to a
"relationship" rather than an "entity". In order to solve this
problem you need two entities: USER and LOGON, and a relationship:
USERLOG. Loosely speaking, this solution can be:
USER (userid, ...)
LOGON (logid, password,...)
USERLOG (userlogid, userid, logid,...)
Of course, this application can be very complex in a real life
situation.
Although the column "ID" is not a good solution in your example
(as above mentioned), generally speaking, it is a good idea to use
"ID" as a key. The fact that the name of this column is "ID" is
OK, because it is a reminder of an identifier, which, by the way, is
the essence of a key in a relational system. We prefer to say
"identifier for a tuple" in place of "name for tuple" because
this is more appropriate regarding the key's purpose. In a relational
database you can choose anything to be your key (you can use
auto-number logic, your own software, etc.) under one condition: that
it satisfies the definition of a key, which is simple. (you can create
a procedure which checks whether a certain name is ok for a key in your
application) So regarding your relational database, it isn't
necessary that the key be anything special - a key should be an
identifier.
"The reason candidate keys are so important is that they provide the
basic tuple-level addressing mechanism in a relational system" (C.J.
Date).
We often use some existing attributes for a key, that is, we don't
add a new column "ID". Although, this is a simpler approach, we
should be careful. Let UserName be a key, then for example the name
John Smith (from this column) has two meanings.  One is the real man
John Smith and the other meaning is that it is one tuple in the
database. This can cause problems within the functionality of the
database.
Lastly, I get the impression that your question about the meaning of
3NF was caused by the question about two (or more) keys in a single
table. I think that this is a long story in both relational and
conceptual models.

Vladimir Odrljin

[ Post a follow-up to this message ]

DA Morgan wrote:
 
even if[vbcol=seagreen] 
>
> I disagree. A person table might have a person_id that uniquely
> identifies that person within a specific application. But a column
named
> ID has no place in any set.

OK. If that's all I have to do to get off the fire, then fine. I would
like it to be called person_id instead of ID.

-jonnie

[ Post a follow-up to this message ]

tore.trollsaas_ XATX-xatx-@skedsmo.online.no wrote:
> On 9 Jan 2005 16:07:46 -0800, "jonnie" <jsavell@gmail.com> wrote:
>
> 
>
>
> ... and possibly you would even consider MemberNbr, EmployeeNbr , SSN,
> etc. Maybe even ZipCode  ;-)
>
> Consider what would happen if they were all named ID ? "ID What?"
>
> mvh Tore

One of my favorites is the "natural join" between two tables with
a column named "Comments."

Nothing quite so good at enhancing performance as a meaningless join
on a column containing kilobytes or megabytes of formatted text.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)


----== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet New
s==----
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000
 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

[ Post a follow-up to this message ]