Understanding 3NF

Question

I'm currently in a System Analysis class and I want to better understand the use of Normalization. For 3NF if I was working on a table that contained Student Id, Name, Phone Number, and Major. I would have to use only Student ID and Major because both student name and phone number can be found by the ID. Am I understanding this right?

Waht do you mean by 'use'? Major could also be found using only the student ID. — idstam, Nov 29 '11 at 06:17
I was thinking because there are different majors but One Student possessing one ID — Thomas, Nov 29 '11 at 06:19
Students also can share a phone number, especially if they live together. No, this way of reasoning isn't valid. — Jan, Nov 29 '11 at 06:22
so this table is 3nf would look like this Student table = ID,Phone #, Major — Thomas, Nov 29 '11 at 06:32

score 7 · Answer 1 · answered Dec 07 '11 at 05:55

Let's start with this . . .

student_id  name                 phone            major
--
1           Darcy Sailer         000-111-2345     Mathematics
2           Lonnie Seman         000-111-3456     Fine Art
3           Mathew Bradburn      000-112-9086     Education
4           Edwina Loflin        000-111-2345     Psychology
5           Clinton Rosebrook    000-111-1000     Chemistry
6           Lonnie Seman         000-113-2975     Chemistry

The only candidate key is student_id. That means names are not unique, phones are not unique, and majors are not unique.

In order for a table to be in 3NF, it has to meet these requirements.

It's in 2NF.
Every non-prime attribute is directly dependent on every candidate key. (No transitive dependencies.)

This table is in 2NF. (Proof is left to the reader.) There are three non-prime attributes; all are directly dependent on the only candidate key. That means that

name doesn't determine phone,
name doesn't determine major,
phone doesn't determine major,
phone doesn't determine name,
major doesn't determine name, and
major doesn't determine phone.

So it's in 3NF.

In identifying dependencies between two columns, you're trying to answer this question: "Given one value for the first column, do I always find one and only one value for the second column?"

For example, to determine whether there's a dependency between "name" and "major", you'd ask, "Given one value for 'name', do I always find one and only one value for 'major'?" And the answer is "No". Given the name 'Lonnie Seman', you find two majors--Fine Art and Chemistry.

It doesn't matter that, in the real world, a person can have more than one phone number and more than one major. Choosing whether to record only one phone number per person or only one major per person is an important part of database design, but it has nothing to do with normalization.

It doesn't matter that, in the real world, you rarely start with representative sample data. Database designers are expected to know things like

Two people can have the same name.
Two people can have the same phone number.
Two people can have the same major.
One person can have two or more phone numbers.
One person can have two majors.

Understanding 3NF

1 Answers1