How to determine the functional dependencies
I am currently working for a University project and I'm a little bit confused now about the functional dependencies part. For this project I had to create a logical data model based on my own project specification and also determine the functional dependencies.
For example, I have given the 'User' table the following attributes.
R(user_id, username, regDate, type, subscription)
Primary key: user_id
Unique key: username
Foreign key: subscription
An example data set could be something like:
1, JohnS, 01-01-2012, Administrator, NULL
2, PeterB, 02-01-2012, Moderator, Movies
3, PeterA, 02-01-2012, User, Movies
4, Gary, 03-01-2012, User, Books
5, Irene, 03-01-2012, User, Movies
6, Stan, 03-01-2012, User, Movies
7, Isaac, 04-01-2012, User, Books
The part I don't understand is how I determine the functional dependencies. My initial feeling was that there are two functional dependencies and these are:
user_id -> username, regDate, type, subscription
username -> user_id, regDate, type, subscription
However, looking at the other examples in the lecture slides, I am having doubts whether this is correct or not.
Answer
If "username" is both unique and required (unique and not null), then it's a candidate key. In relational modeling, there's no theoretical difference between one candidate key and another. More specifically, in relational modeling, there's no theoretical reason to pick one candidate key and label it "primary key". A key is a key.
So you're right. There are two functional dependencies here. (Or 8, if you decompose the right-hand-side into individual columns. user_id -> username, user_id -> regDate, etc.)