How to determine the functional dependencies

Question

I am to create a logical data model based on my own project specification and also determine the functional dependencies.

Table User example data:

user_id username    regDate          type subscription
      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

Primary key: user_id
Unique key: username
Foreign key: subscription

How do I determine the functional dependencies?

I get two functional dependencies:
user_id -> username, regDate, type, subscription
username -> user_id, regDate, type, subscription

Answer 1

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

Answer 2

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

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.