2

As it is said in the book of Database Solutions Second Edition written by Thomas Connolly and Carolyn Begg page 180:

Third normal form (3NF)
A table that is already in 1NF and 2NF, and in which the values in all non-primary-key columns can be worked out from only the primary key column(s) and no other columns.

I have seen many scenarios where people use an identity column though they already have a primary key column in their table. A record can also be worked out from the identity column, so isn't it a violation of 3NF if we use an auto-incremented identity column along with a primary key in a table?

UPDATE: If it is not so which column should be referenced as a foreign key in another table.The primary key column or the Identity column?

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Elham Kohestani
  • 3,013
  • 3
  • 20
  • 29
  • 1
    PKs don't matter, CKs do. Also its not "non-PK" columns that matter but "non-prime" ie "non-CK". Also the rest of that "definition" is so sloppy it's useless. Auto-increment is irrelevant. Read some other textbooks. Dozens are free online in pdf. Also beware that in SQL a PK declaration means UNIQUE NOT NULL, ie superkey, not necessarily a relational/actual PK. – philipxy Feb 13 '18 at 04:29
  • Please ask a new question by posting a new question. But find out what a relational FK is & what an SQL FK is. The latter is a column set whose subrow values must appear elsewhere as an SQL PK or UNIQUE NOT NULL when the subrow value contains no nulls. Declare any FKs that aren't already enforced transitively via other FK declarations. Again, you need to learn correct definitions. Then apply them. Don't worry about things that aren't in the definitions. – philipxy Feb 13 '18 at 11:41
  • 1
    A foreign key can reference any candidate key. The relational model doesn't say anything about which candidate key is the "better" target for a foreign key reference. If you're using a framework or an ORM to build software, it might insist on referencing an identity column. – Mike Sherrill 'Cat Recall' Feb 15 '18 at 00:49

3 Answers3

6

No. The 'official' wording of the definition of 3NF usually uses the term "prime attribute" or "non-prime attribute". If your book suggests that this means "attribute of the primary key" then throw your book in the bin. It is wrong. "prime attribute" means "attribute that is part of ANY of the keys" and "non-prime attribute" means "attribute that is not part of ANY of the keys". So introduction in the relation schema of your kind of "autoincrement attribute" (and of all of the applicable FDs that will make it a key) cannot possibly introduce a 3NF violation because it won't introduce a non-prime attribute.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • 2
    @ElhamKohestani Forget about constraints & learn how to choose table meanings that capture your business/application state. Your (poor) texbook has subtitle "A step-by-step approach [...]". What does it say? – philipxy Feb 13 '18 at 11:51
5

The paragraph you quoted is wrong - or at least it is so informal that it's useless as an explanation.

A relation R is in third normal form if it is in second normal form and every non-prime attribute of R is non-transitively dependent on each candidate key of R.

Codd E. F., “Further Normalization of the Data Base Relational Model”

Candidate keys are what matter. Nothing wrong with a table having more than one key.

nvogel
  • 24,981
  • 1
  • 44
  • 82
4

That book Database Solutions: A Step by Step Guide to Building Databases 2nd 2004 Edition is a mess. Unfortunately it says wrong things, it misleads, and a lot of their wording is extremely poor--like "work out"--which is informal & never defined.

Third normal form (3NF)
A table that is already in 1NF and 2NF, and in which the values in all non-primary-key columns can be worked out from only the primary key column(s) and no other columns.

That wrong definition is actually intended to be informal and for when a table has just one CK (candidate key). But the book doesn't say that except indirectly and later when it gives another wrong definition involving PKs (primary keys):

The formal definition for third normal form (3NF) is a table that is in first and second normal forms and in which no non-primary-key column is transitively dependent on the primary key.

Later still it says that there can be multiple CKs but it still gives a wrong definition:

Therefore, for tables with more than one candidate key you can use the generalized definition for 3NF, which is a table that is in 1NF and 2NF, and in which the values in all the non-primary-key columns can be worked out from only candidate key column(s) and no other columns.

It is wrongly saying "primary-key columns" where prime columns ie CK columns would be correct.

Their other book Database Systems 4th Edition 2005 also introduces special cases of definitions for when there is just one CK without saying so then later gives "general" definitions. At least those get "prime attribute" correct.

General definition for Third Normal Form (3NF) is a relation that is in First and Second Normal Form in which no non-candidate-key attribute is transitively dependent on any candidate key.

There is nothing unusual about a table having multiple CKs in any normal form.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • According to the answer to this question https://stackoverflow.com/questions/377375/a-beginners-guide-to-sql-database-design I want to read these books series. What do you suggest which would be efficient than this book? I have the latest book in this series (the sixth edition). What do you suggest should I read it or use some other text book? – Elham Kohestani Mar 06 '18 at 06:56
  • Which book? The 2nd book in DD59's answer is now 6th ed of the poor 'Database Systems' in my answer by the same authors as the even worse 'Database Solutions' in your question. The *very first thing* I just read in the 6th is "if A and B are attributes of relation R, B is functionally dependent on A (denoted A -> B), if each value of A is associated with exactly one value of B. (A and B may each consist of one or more attributes.)"--they *contradict themselves*--A & B are attributes then they are *sets* of attributes. Same warped writing. Same normalization presentation bad in many ways. – philipxy Mar 06 '18 at 07:47
  • DD59's other book (3rd ed) is also full of normalization misconceptions & sloppiness. [I suggest](https://stackoverflow.com/a/24007275/3404097) everyone read Date re normalization, Darwen on relational DBs (relations & querying) (& basic SQL) & Halpin re info modeling. (ORM2 & related methods lead to largely 5NF designs.) But those are just parts of database system issues. I've found about 2 dozen textbooks free online. I'm not familiar with them all & re normalization can't suggest particular ones as truly *good* yet. – philipxy Mar 06 '18 at 08:34