>Seems like we've got a different interpretation of the word redundant. By redundant I mean information that is also stored elsewhere. Since the information stored in both the employee and travel table, this information is redundant so I would call these fields redundant fields.

Yes, redundant data is data that appears in more than one place, but it must be the same data (exactly the same, it has the same domain). With employee and travel, it is possible that Mary Smith goes on a trip, later she marries and becomes Mary Jones. Mary Smith is her name at the time of the trip and Mary Jones is her current name, you see two different pieces of data, not redundant.

>Also note that the values in the fields last_name and first_name for a matching
>employee must match also, because they're enforced by constraints. I added this fields because I want to sort the travel table by the name of the employee natively. Following your rules about normalization (at the logical design) this is normalized (or am I missing something) but on the physical layer it's not.

If the onlye reason that the name appears in the trips table is for sortiung by name, then it is redundant, But I propose that the two names are NOT the same data, see above paragraph.

>>The rule that is broken is the 3rd Normal form, "All fields are dependent on ONLY the primary key and not on any non-key attribute other than alternate keys." The employee name field in the travle table are dependent on the primary key, yes. But they are also dependend on the emp_id which is not an alternate key for that table. Thus the design is denormalized.
>
>No it's only dependend on the Empid (foreign) key. Note that this has not anything to do with history.

I disagree, you must know which trip you are interested in to find out which employee was on that trip, so the name IS dependent of the PK. It also dependent on the EmpID because if the EmpID on a trip changes then the name must also change.