1

As I was designing a database for my Database course, I came across a question that made me hesitant about my design.

The exercise asked us about an arbitrary design that should be at least in 3NF. I am sure that the design is already in 1NF and 2NF, but now I am checking for 3NF.

In one of my tables (Employees), I have two columns: 1. EmployeeID, and 2. Email, and possibly some other attributes related to employee entity.

I am now wondering if the uniqueness of email (for each employee) violates the third normal form. Because I can just drop EmplooyeID and put Email as the primary key in this design.

philipxy
  • 14,867
  • 6
  • 39
  • 83
inverted_index
  • 2,329
  • 21
  • 40
  • An employee's email can change over time, so it is better as an attribute than as a primary key. – Gordon Linoff Oct 04 '19 at 19:04
  • @GordonLinoff Sounds very true, but would like to generalize this case. If we have a unique attribute in our table, would taking another unique attribute (like auto-incrementing ID) violate 3NF? – inverted_index Oct 04 '19 at 19:10
  • 2
    Hi. What is your one clear question? What you ask in the title or something you wonder in the body? What does "unique" mean in the title--unique in a relation (superkey), as normally understood unqualified--or "for each" value of some other column(s), as in the body--a determined attribute of a FD? PS There's no point in wondering about some arbitrary condition that isn't mentioned in definitions or algorithms for 3NF. Wondering is not normalizing. PS See [ask] & the voting arrow mouseover texts. If you want to normalize this then that is a faq; ask a question where stuck in some presentation. – philipxy Oct 04 '19 at 22:58
  • 1
    Are you asking what standard definitions of 3NF say violates it? There are 2 main definitions, what are they & what do they say? If you really want to know whether some clear statement is a theorem (maybe with proof or disproof) then give some clear research & attempts. Your "because" reasoning is not clear & it's not clear how it supports the title or a violation in your example. You don't even show whether your example is in 3NF. Quote a definition of 3NF & apply it. But likely such a question about an arbitrary claim being true is "too broad". – philipxy Oct 04 '19 at 22:58
  • 1
    Oh & there's a third (unclear) question in a comment. (What does "taking" mean? Typo for taking out?) Please clarify via edits, not comments. PS Again, quote definitions & identify notions of violating FDs. And how is it connected to the other questions? – philipxy Oct 04 '19 at 23:50

2 Answers2

2

Normalization is concerned with dependencies and candidate keys in relations. A candidate key is a set of a attributes which is irreducibly unique and every relation has at least one such key but it can certainly have more than one key. No normal form prohibits a relation from having more than one key.

The title of this question is contradictory. If a set of attributes is unique (and non-nullable) then that set of attributes is a superkey and some some subset of the attributes of a superkey must be a key. The idea of non-key attributes having "uniqueness" doesn't make any sense if uniqueness is defined by a dependency being satisfied by a relation. The designation of a key as primary or secondary is irrelevant because normal forms are equally concerned with all keys, not just one.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • What do you mean, 'uniqueness is defined by a dependency being satisfied by a relation'? And how does it imply 'The idea of non-key attributes having "uniqueness" doesn't make any sense'? I can guess you mean 'uniqueness of an attribute is defined as some dependency determining it being satisfied by a relation'. But then the 1st *only* make[s] any sense if the 2nd. Could you rephrase the whole? PS If a relation is constrained to at most one row then {} is the only CK & every attribute is non-prime & unique (a superkey)! Ironically surely nothing to do with the asker's wonderings! – philipxy Oct 05 '19 at 04:37
  • I was just trying to draw attention to the difference between unique values in some relation at a point in time and the set of attributes on which we want to enforce a dependency by implementing a key constraint. – nvogel Oct 06 '19 at 10:43
-1

Normalization refers to how the data within the table is related to each other. A lookup table like you describe (ID column with a value/attribute column) is a key feature of normalization. Using a surrogate key is fine and does not violate 3NF.

Microsoft describes the basics of normalization in this article. The example at the end talks about the 3NF and says:

Third Normal Form: Eliminate Data Not Dependent On Key

It doesn't apply to the key itself. The email address in your example is dependent on the key and thus satisfies 3NF.

philipxy
  • 14,867
  • 6
  • 39
  • 83
VeritableHero
  • 5
  • 7
  • 1
    3NF means a particular thing & this isn't it. The quoted phrase just a vague fragment of everyday words that if it were clear might at best be something true about 3NF. Most of the rest of this is unclear. And it's not clear how this answers the (unclear) question. – philipxy Oct 04 '19 at 22:34