Normalization 3NF

Question

I an reading through some examples of normalization, however I have come across one that I do not understand.

The website the example is located here: http://cisnet.baruch.cuny.edu/holowczak/classes/3400/normalization/#allinone

The part I do not understand is "Third Normal Form"

In my head I see the transitive dependencies in EMPLOYEE_OFFICE_PHONE (Name, Office, Floor, Phone) as the following Name->->Office|Floor and Name->->Office|Phone

The author splits the table EMPLOYEE_OFFICE_PHONE (Name, Office, Floor, Phone) into EMPLOYEE_OFFICE (Name, Office, Floor) and EMPLOYEE_PHONE (Office, Phone)

From my judgement in the beginning, I still see the transitive dependency in Name->->Office|Floor so I don't understand why it is in 3NF. Was I wrong to state that there is a transitive dependency in Name->->Office|Floor?

Reasoning for transitivity: Here is my list of the functional dependencies

1. Name -> Office
2. Name -> Floor
3. Name -> Phone
4. Office -> Phone
5. Office -> Floor (Is this the incorrect one? and why?

Thank-you your help everyone!

Answer 1

5) you assume a naming sheme here ... offices 4xx have to be on floor 4 ... 5xx have to be on floor 5 ... if such a scheme exists, you can have your dependency ... as long as this is not part of the specification ... no. 5 is out of the game ...

Answer 2

First of all,let me define 3NF clearly :- A relation is in 3NF if following conditions are satisfied:- 1.)Relation is in 2NF 2.)No non prime attribute is transitively dependent on the primary key. In other words,a relation is in 3NF is one of the following conditions is satisfied for every functional dependency X->Y:- 1.)X is superkey 2.)Y is a prime attribute For Your Question,if the following FDs are present :-

 Name -> Office
 Name -> Floor
 Name -> Phone
 Office -> Phone

Then we cannot say anything about Office and Floor.You can verify this by applying and checking any of the Armstrong Inference Rules.When you apply these rules, you will find that you cannot infer anything about office and floor.

Answer 3

1. Name -> Office
2. Name -> Floor
3. Name -> Phone
4. Office -> Phone
5. Office -> Floor (Is this the incorrect one? and why?

(1) You and the author and I agree that Name->Office.

(2) You and the author agree that Name->Floor. While that's true based solely on the sample data, it's also true that Office->Floor. I'd explore this kind of issue by asking this question: "If an office is empty, do I still know what floor that office is on?" (Yes)

Those things suggest there's a transitive dependency, Name->Office, and Office->Floor. So I would disagree with you and with the author on this one.

(3) You say Name->Phone. The author says Office->Phone. The author also says that "each office has exactly one phone number." So given one value for Office, I know one and only one value for Phone. And given one value for Name, I know one and only one value for Phone. I'd explore this issue by asking, "If I move to a different office, does my phone number follow me?" If it does, then Name->Phone. If it doesn't, then Office->Phone.

There isn't enough information here to answer that question, and I've worked in offices that worked each of those two ways, so real-world experience doesn't help us very much, either. I'd have to side with the author in this case, although I think it's not very well thought through for a normalization example.

(4) This is really just an extension of (3) above.

(5) See (2) above. This doesn't have anything to do with a naming scheme, and you don't need to assume that offices numbered 5xx are on the 5th floor. The only relevant question is this: Given one value for Office, is there one and only one value for Floor? (Yes) I might explore this issue by asking "Can one office be on more than one floor?" (In the real world, that's remotely possible. But the sample data doesn't support that possibility.)

Some additional FDs, based solely on the sample data.

Phone->Office
Phone->Floor
Office->Name