0

I am a bit stuck designing part of a database.

I have a table called Staff. It has different attributes: 

StaffID
First Name
Last Name
Job Title
Department Number
Telephone Number

StaffID is the primary key in this table.

My issue however, is that it is possible to find any information based on the telephone number (i.e. each staff member has a different, unique telephone number).

For example, this means that the First Name or Job Title can be found when we have the Phone Number. However, Phone Number is not a primary key, StaffID is.

I am not sure whether this is a transitive dependency and should fixed through 3NF by splitting up the table and having the Staff table without the Phone Number and another table with just StaffID and Telephone Number.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Johny
  • 129
  • 11
  • This is not a transitive dependency. – Buddhi Oct 19 '17 at 16:09
  • Would you mind expanding a bit? – Johny Oct 19 '17 at 16:26
  • Possible duplicate of [Normal forms - 2nd vs 3rd - is the difference just composite keys? non trivial dependency?](https://stackoverflow.com/questions/27474203/normal-forms-2nd-vs-3rd-is-the-difference-just-composite-keys-non-trivial-d). See also [Identifying Transitive Dependencies](https://stackoverflow.com/a/27402464/3404097). – philipxy Oct 20 '17 at 02:00
  • Hi. This has some unclear parts. "Find any information based on" & "can be found" are too vague to be any use. Use the technical terms from definitions, that properly refer to things & their parts. *Why* do you say "it is possible to find any information based on the telephone number"? Are you saying that that's per your application (maybe because you were given that or some FDs), or that you think it follows from the PK? If phone # determines 1st name & job title, *why* do you think this conflicts with staff id being PK? (It doesn't.) You seem to have some misconceptions re "find". – philipxy Oct 20 '17 at 02:24

4 Answers4

2

Transitive dependency occurs only if you have indirect relationship between more than 2 attributes that are not part of they key.

In your example, as you explained, the StaffID is part of your dependency, which is fine because it's the primary key.

Also you can look at this question that shows what is wrong with a transitive dependency. It could help put things into perspective.

In your table, if you delete staff member, you delete all the information (rightly so because you don't need it). If you leave phone number in a different table and, for instance, delete entry only in Staff, you're left with a wild phone number. But if your Staff table allowed multiple entries for the same person (but different departments) then the situation would be different.

Other sites that helped me in the past:

https://www.thoughtco.com/transitive-dependency-1019760 https://beginnersbook.com/2015/04/transitive-dependency-in-dbms/

Funnily they always follow the book example : )

Moseleyi
  • 2,585
  • 1
  • 24
  • 46
  • Makes sense. Thanks for the answer and for the links you provided :) – Johny Oct 19 '17 at 16:36
  • @Johny & Moseleyi The correct definition of "transitive dependency" does not involve PKs (primary keys) or CKs candidate keys)--as the 2nd "other site" correctly says). This answer confuses transitive FDs with transitive FDs on CKs--like the 1st "other site". It is very poorly restating the main illustrations of problems with transitive dependencies on CKs from the poor (despite misinformed upvotes) answers at "this question"--this answer & those are hopelessly vague for actually identifying & dealing with problem. Read an academic textbook. – philipxy Oct 20 '17 at 21:10
1

In design-theoretical terms, keys are implied by dependencies. If PhoneNumber→StaffID and if StaffID is known to be a key then we can infer that PhoneNumber is also a key. If that is the case then there is no violation of 3NF because the determinants are all keys. Note that the choice of StaffID as primary key is irrelevant here. Normalization treats all keys as equally significant.

In practical database design however, the question arises as to whether PhoneNumber really makes sense as a key. In other words, would you actually want to enforce dependencies like PhoneNumber→StaffID? If, after consideration, you decide that dependency is not applicable then you could discard that dependency (by not making PhoneNumber a key) and the table would still satisfy 3NF with respect to the set of dependencies you have left.

Here's a reason why a dependency like PhoneNumber→StaffID might not be a realistic choice: when I joined my present company I got a staff ID on my first day; I didn't get a phone number until two days later.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • Following logic from your last paragraph, best course of action would be to have a separate table for `PhoneNumber` and have it be in an optional relationship to `Staff` then? – Johny Oct 19 '17 at 20:10
  • Possibly yes. However, if you are designing a real database you need to investigate the actual circumstances and decide based on real requirements rather than hypothetical ones. There is no universally right or wrong answer. On the other hand if this is an academic exercise where the dependencies are already given to you then the single table design would be the correct one and there's no basis on which to say you need another table. – nvogel Oct 19 '17 at 20:37
  • @Johny If you want to know what action to take when then you must *memorize precise correct definitions & methods/algorithms from a textbook*. Examples & Q&A might indeed help. But you need to stop accepting fuzzy & fragmented writing (including by you) as "makes sense". "I am not sure whether this is a transitive dependency": Well, do you even have a *definition* in mind? I guess this answer tries to show that whatever your unclear worries are they are unsound. I wish it explicitly said so, & that thus you need to read & apply a definition, & that given that, to isolate & debunk them is moot. – philipxy Oct 20 '17 at 22:04
0

It is not because there is no dependency between phone and name or last name, if you know the name you can't know the phone number, it is not the same as for example, Model and Manufacturer, if you know the model is a mustang then you know the manufacturer is ford, and ther other way around, you know that ford makes mustangs

With the columns you mentioned I would have separate tables for departments and job titles, because they do not depend on the PK StaffID. Think about it as removing potential redundancies, you can have five thousand people in there and have job title as a string repeated one thousand times, that is a signal that it needs its own table (2NF).

Alex
  • 2,247
  • 1
  • 27
  • 37
  • Thanks for the answer. I'm not quite sure I understand what you mean. The `department` and `job title` does depend on the `StaffID`, as they cannot be found for someone unless you have the `StaffID`. Do you suggest having another table with, for example, `JobID` and `Job` and using the `JobID` as part of the Staff Table instead of just `Job` in order to replace string redundancies by integer redundancies? – Johny Oct 19 '17 at 16:35
  • Correct, foreign keys are perfect for that, if you want to update a job name or a department name you won't have to update all tables with that column, instead you just update the corresponding row on the departments table. – Alex Oct 19 '17 at 17:37
  • Makes total sense. Thanks! – Johny Oct 19 '17 at 20:07
0

Transitive dependency means that you have a (set of) attribute(s) that are completely determined by going from a (set of) attribute(s) A -> B and then from B -> C, while you cannot go from B -> A.

In your case, you do indeed have (StaffId) -> (PhoneNumber) and also (PhoneNumber) -> (StaffId). This means you have A -> B and B -> A and hence at this step you can already rule out the transitive dependency.

If you like, you could say that PhoneNumber would be another candidate for PK.

As a background, the problem with transitive dependencies is this: Assume you have a table consisting of "Book Title" (primary key), "Author" and "Gender of Author". Then you certainly have a transitive dependency BT -> A, A -> GoA, hence BT -> GoA.

Now assume that one of your authors is "Andy Smith", Andy being a short name for Andreas. Andreas goes and changes gender, and is now Andrea. Obviously you do not need to change the name, "Andy" works just fine for "Andrea". But you do have to change the Gender. You have to do it for many entries in your table, i.e. for all books from that author.

In this case, you would fix the problem by creating a new table "Author", obviously, and then you'd have only one row for Andy.

Hope that clears it up. It is easy to see that in your example there is no constellation where you have to change many rows due to a phone number change. It's a simple 1:1 relationship between StaffId and PhoneNumber, no problems whatsoever. Both are candidate keys.

AnoE
  • 8,048
  • 1
  • 21
  • 36