2

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).

0

Here is essentially 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 }

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.