-1

I've been learning about database Normalisation, and I have a question regarding a users table I have for my web app.

The table schema is as follows:

users (user_id, username, password, first_name, last_name, ver_code, verified)

I want to know if this is in 3NF. It is in 1NF (obviously) because the intersection of row-column contains single values. It is in 2NF automatically because the primary key is not composite. Is it 3NF?

As I understand it, a transitive dependency is when a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key. So, user_id is the primary key, and username isn't a primary key but is a prime attribute. So the table is in 3NF.

Is this right? Or am I missing something? Is the definition of transitive dependency above correct?

EDIT:

I thought Normalisation was objective, which is why I didn't bother stating my reference. I want to get my table in 3NF as per the Database Systems: A Practical Approach to Design, Implementation, and Management book by Connolly and Begg.

Here are the functional dependencies. Both the user_id and the username are unique fields, so they're both candidate keys.

fd1: user_id -> username, password, first_name, last_name, ver_code, verified

fd2: username -> user_id, password, first_name, last_name, ver_code, verified

  • 1
    Hi. Your 2NF "automatically" is wrong because 1. you haven't shown all CKs are simple & 2. all CKs simple does not imply 2NF unless no FD has {} as determinant. No, your definition of transitive FD is not correct, you are muddling it with the definition of 2NF. "As I understand it" means you think you *don't* understand it. Please read hits googling 'stackexchange homework'. Say what reference you are following and/or quote the definitions & processes you are following & show where you are stuck. If your reference talks about PKs instead of CKs then it is junk. PS Can't normalize without FDs. – philipxy Mar 01 '18 at 21:05
  • PS "1NF" [has no single meaning](https://stackoverflow.com/a/40640962/3404097). So again, what is the one in your reference? – philipxy Mar 01 '18 at 21:17
  • "*I thought Normalisation was objective . . .*" The word *normalization* has multiple meaning. It might mean the decomposing a relation to get a higher normal form. It might mean the formal theory that governs how you decompose relations. It has other meanings in other contexts, as in statistics. In the relational theory of data, the process of normalizing a relation is deterministic: everyone who starts with the same set of functional dependencies should get the same results. *Results*, because, in the general case, a set of FDs might let you decompose a relation in two or more valid ways. – Mike Sherrill 'Cat Recall' Mar 03 '18 at 16:12
  • [Re that textbook & another by the same authors having very poor presentations of normalization.](https://stackoverflow.com/a/49122615/3404097) – philipxy Mar 08 '18 at 10:09

1 Answers1

0

The normal forms through BCNF are determined by functional dependencies (FDs), not by attribute names. You don't show us any FDs.

It is in 2NF automatically because the primary key is not composite.

No. Every normal form takes every candidate key into account. Are there other candidate keys? You can't know without knowing the FDs. (We can't know unless you tell us what the FDs are.)

I can imagine how the combination of {username, ver_code} or {username, password} might be unique.

As I understand it, a transitive dependency is when a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key.

If one or more non-prime attributes determine one or more non-prime attributes, you have a transitive dependency. (In relational theory, you see functional dependencies written like A->B. But A and B refer to sets of attributes.) Prime attributes are those attributes that are part of any candidate key, not just the primary key.

So, user_id is the primary key, and username isn't a primary key but is a prime attribute.

The attribute "username" can be a prime attribute only if it's part of a candidate key. Any candidate key. You haven't supported the (unspoken) claim that "username" is part of a candidate key.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • @CesarF Forget web sites, literally dozens of published academic database textbook pdfs are free online. Although the one you mention is poor. On the other hand the 4th edition has a definition on "transitive dependency" in a little blue box and it is just not what you write in your question--it has nothing to do with prime attributes & as I said in my comment on the question you are muddling its definition with one of the 3NF ones. Get definitions right. Eg "are unique fields, so they're both candidate keys" is wrong reasoning. Unique & no smaller contained unique (here, {}) means CK. – philipxy Mar 03 '18 at 21:08