3

I have the following STUDENT relation in first normal form, where id has been identified as the primary key (email here is also unique for all tuples):

STUDENT(id, email, first_name, last_name)

I want to normalize this relation/table such that it is in third normal form (3NF).

The dependencies that I've been able to identify from the above STUDENT relation/table are the following

Full:

{ id } → { email }

Transitive:

{ email } → { first_name, last_name }

Here's the part where I'm confused. I've identified email as a transitive dependency as it can be identified by the primary key (id), and the attribute/column email itself can identify and determine the first_name and last_name.

...if I was to continue with this idea, I would eventually get to a 3NF which looks like so:

STUDENT(id, email)
STUDENT_INFO(email, first_name, last_name)

However, the STUDENT relation/table here seems very redundant, as all it does is point to another relation/table. Is my normalization process here correct? Or did I get my dependencies wrong? It seems that I will always run into this problem whenever the initial relation/table has two candidate keys which both could either be the primary key (id or email both could uniquely identify each row).

  • 1
    Let us continue this discussion in chat. – ypercubeᵀᴹ Sep 24 '19 at 12:52
  • "The dependencies that I've been able to identify" When some FDs hold, all the others implied per Armstrong's axioms do too. Where are those? The definition of 3NF refers to certain transitive FDs. Quote a definition & show your steps applying it & ask about where you are 1st stuck. PS What does "identified by the primary key" mean? Use technical term & use them correctly per their definitions. – philipxy May 3 at 2:29
1

Here is where I went wrong:

The important thing about transitive dependencies are these two key factors:

  1. If B depends on A, and C depends on B, then C also depends on A (A → B → C)
  2. B is not (or part of) a candidate key

In my example, I identified the following as a transitive dependency:

{ email } → { first_name, last_name }

However, this is not the case, as email is a candidate key, and so instead, if we use id as the primary key, we have a full dependency:

{ id } → { email, first_name, last_name }
| improve this answer | |
  • The generic notion of transitive binary relationship is not what is meant in "transitive FD". Find a definition. Also you are confusing the definition of "transitive FD" with other conditions in certain definitions of 3NF that mention transitive FDs. Also it's not clear how whether a FD is full is relevant here--FDs are either partial or full; being full doesn't imply not being transitive. Also the rest of the reasoning isn't clear. – philipxy May 3 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.