How to determine the functional dependencies

Question

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 1

Functional dependencies are defined from a theoretical perspective as follows (Wikipedia):

Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value is associated with precisely one Y value; R is then said to satisfy the functional dependency X → Y.

From a technical perspective, you are trying to find attributes that uniquely identify other attributes. As a shortcut, determine your candidate keys and the attributes that depend on them. Your examples are correct because a username, regDate, type, and subscription all depend on the value of user_id. If username is unique and not null, it is a candidate key and also identifies the set of attributes.

Answer 2

In addition to what others have said, if an attribute (or a set of attributes) is a candidate key, then all the attributes must functionally depend on it.

• A "functional dependency" A->B simply means that no two different values of B are ever related to the same A. Slightly more formal definition is given on Wikipedia, but that's essentially it.
• Since a key must be unique, even if two tuples contain the same value of some attribute(s), the key values must be different nonetheless. So, different values can never relate to the same key value.

Since all attributes are functionally dependent on the key(s), if there is any other functional dependency, you automatically have a transitive dependency and a violation of the 3NF. So a "non-key" dependency can act as a red flag for spotting normalization errors.

---

You can think of it from the opposite direction as well: first figure out which functional dependencies make sense in your domain, then use them to identify which attributes could act as keys.

Answer 3

I will assume you are using MySQL, but if not, you can implement your idea in any other RDBMS.

Run the following command to get all your tables:

show tables;

Then iterate all the tables and run the following command for each of them:

show columns;

FDs can be described as follows:

Determinant -> Dependent,
Determinant = {A1, ..., Am},
Dependent = {B1, ..., Bn}

where Ai and Bj are columns. You need to generate all possible scenarios for Determinant and Dependent. For each scenario you will need to view whether exists at least two separate records where the determinant columns match and at least one of the dependent columns do not match. If so, then the scenario is not an FD, otherwise it is an FD. Example: Let's assume, that m = 3 and n = 2:

select count(*) from mytable t1, mytable t2 where ((t1.A1 = t2.A1) and (t1.A2 = t2.A2) and (t1.A3 = t2.A3)) and ((t1.B1 <> t2.B1) or (t1.B2 <> t2.B2))

will return the number of records which break the FD-rule. If the value is 0, then the scenario is an FD.

Of course, in your particular case you can omit a few steps, and you have your columns instead of Ai and Bj, but you hopefully understand the idea.

Answer 4

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.)