Do you agree that this table is normalized in 3NF

Question

I was checking out tables normalization and see what I came to:

Describe and illustrate the process of normalizing the data shown in this table to third normal form (3NF) :

BRANCH_NO(PK)  BRANCH_ADDRESS    TELL_NO    MANAGER_ID    MANAGER_NAME
B001           ADDRESS 1         TELL 1     S1500         TOM DANIELS
B002           ADDRESS 2         TELL 2     S0010         MARY MARTINEZ
B003           ADDRESS 3         TELL 3     S0145         ART PETERS
B004           ADDRESS 4         TELL 4     S2250         SALLY STEM 

After thei transformation they end up with these two tables that they claim both are in 3NF:

BRANCH_NO(PK)  BRANCH_ADDRESS    TELL_NO    MANAGER_ID(FK)
B001           ADDRESS 1         TELL 1     S1500     
B002           ADDRESS 2         TELL 2     S0010     
B003           ADDRESS 3         TELL 3     S0145     
B004           ADDRESS 4         TELL 4     S2250

and

 MANAGER_ID(PK)    MANAGER_NAME
    S1500             TOM DANIELS
    S0010             MARY MARTINEZ
    S0145             ART PETERS
    S2250             SALLY STEM

I think that it is obvious that the first table is not a 3NF. E.g. : tell_no is dependent on branch_addres which is not the primary key but the primary key functionally identifies the branch_address which is a conflict with the transitional functional dependency.

Answer 1

Normalization is all about ensuring that a database schema accurately represents a given set of dependencies. If you aren't given the dependencies to start with then such an exercise really comes down to guesswork and supposition based on a set of attribute names and a few rows of sample data. So there can't be any definitive answer about what is right and what is wrong. It's more a case of understanding what assumptions are being made and what the consequences might be. Write down what dependencies you expect to apply and then ensure that the schema is normalized with respect to those dependencies.

Let's suppose that each Branch is required to have a unique branch number and a unique address and therefore we want to enforce these FDs:

BRANCH_NO -> BRANCH_ADDRESS
BRANCH_ADDRESS -> BRANCH_NO
BRANCH_NO -> TEL_NO
BRANCH_NO -> MANAGER_ID -> MANAGER_NAME

Your two-table design satisfies 3NF with respect to these dependencies, assuming that BRANCH_NO and BRANCH_ADDRESS are both going to be candidate keys (you need to consider all the keys and not just one primary key).

Now that does assume that the implied dependencies on BRANCH_ADDRESS are accurate and important enough that it makes sense to enforce the uniqueness of BRANCH_ADDRESS. That may or may not be the case but that's why you need to determine such things before you can answer the question.