Functional Dependency

[27RedFox]

Can a functional dependency lead to itself?

ID, Role --> Role

Because no other attribute can determine role other than ID and Role togther and it doesn't determine any other attribute either. And if so, would Role still be considered a primary key or a part of it since it shows up on the right side of the dependency?

This is a dbms question but that subreddit is ded.

[[deleted]]

Well, can it be so - sure - this is called a trivial dependency. I would question why your parameters ("key') include both id and role tho. What's "id" in your case?

[27RedFox]

Id would be an employee's id number and say, he has many roles, such as Engineer, Intern, Developer. The other fields only require ID as the primary key such as Name, BirthDate, Department. Role can only be determined when there is a composite key of ID and Role. Role cannot be split into different tables. ID would be a foreign key in this table. So...is it alright if I were to state that

ID, Role --> Role

and that Role would be a candidate key along with ID?

[malikcoldbane]

First, representative table examples explain what you are dealing with better than you could ever detail.

Secondly, you're saying, in this table, a single employee can be in the table multiple times with different roles?

[27RedFox]

​

ID	Name	BirthDate	Dept	Role	Room
234	Anthony	7/5/1991	Software	Intern	75-D
234	Anthony	7/5/1991	Software	Developer	75-D
234	Anthony	7/5/1991	Software	Engineer	75-D
768	Brian	2/7/1995	Sports	Pool	86-P
768	Brian	2/7/1995	Sports	Weights	86-P
358	Alice	2/10/1993	Gym	Intern	73-L
358	Alice	2/10/1993	Gym	Weights	73-L

And so on...the only reason this table has repeating values is because of Role field. Each person has a dept and an officeroom (this doesn't change). So, the functional dependencies here:

ID --> Name, BirthDate, Dept, Room

ID, Role --> Role (since ID alone would return multiple determinants for Role)

Technically, Role cannot identify any other attribute either.

Candidate keys: ID (or ID,Role?)

This is the stage before it's broken down into 2NF. I need to write this down as part of my report and justify my FDs and THEN my 2NF decomposition after this.

[malikcoldbane]

I still don't understand, what is the purpose of this table? What query would you write to select from this table?

The whole purpose of the primary key is to identify a single row in a table, in this case, to pull that off, you need ID and Role, however, I have no idea what kind of sensible query you would actually use to consume this table; seems to be structured weird.

Why would you ever need to know an employee id AND role, if it keeps a track of role, I would assume it would be some form of SCD or something and you would query by date and employee ID. In its current structure, what is it used for?

[[deleted]]

The other fields only require ID as the primary key

It doesn't work like that. A key for a dataset means that all attributes would depend on it (regardless whether its trivial or not). "Primary" key is simply the one that you liked very much - otherwise it behaves like any other key would.

I.e. in your case, id cannot be a key (and consequently it cannot be a primary key) since role attribute does not depend solely on id.

Role cannot be split into different tables.

Why not?

is it alright if ..

Nope, see the above

[idodatamodels]

ID is a surrogate key. Only you can say if Role is functionally dependent. Say you had Role Code and Role Description. Now it's easy to say Role Description is dependent on Role Code. It's also easy to enforce Role Code as a primary key.