Skip to content

Functional Dependencies

In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.

In SQL, functional dependencies appear whenever there is a unique constraint (e.g. a primary key constraint). Let’s assume the following:

1

 

2

3

4

5

CREATE TABLE actor (

 

  actor_id BIGINT NOT NULL PRIMARY KEY,

  first_name VARCHAR(50) NOT NULL,

  last_name VARCHAR(50) NOT NULL

);

It can be said that both FIRST_NAME and LAST_NAME each have a functional dependency on the ACTOR_ID column.

Nice. So what?

This isn’t just some mathematical statement that can be applied to unique constraints. It’s extremely useful for SQL. It means that for every ACTOR_ID value, there can be only one (functionally dependent) FIRST_NAME and LAST_NAME value. The other way round, this isn’t true. For any given FIRST_NAME and/or LAST_NAME value, we can have multiple ACTOR_IDvalues, as we can have multiple actors by the same names.

Because there can be only one corresponding FIRST_NAME and LAST_NAME value for any given ACTOR_ID value, we can omit those columns in the GROUP BY clause. Let’s assume also:

1

 

2

3

4

5

6

7

8

CREATE TABLE film_actor (

 

  actor_id BIGINT NOT NULL,

  film_id BIGINT NOT NULL,

   

  PRIMARY KEY (actor_id, film_id),

  FOREIGN KEY (actor_id) REFERENCS actor (actor_id),

  FOREIGN KEY (film_id) REFERENCS film (film_id)

);

Now, if we want to count the number of films per actor, we can write:

1

 

2

3

4

5

6

SELECT

 

  actor_id, first_name, last_name, COUNT(*)

FROM actor

JOIN film_actor USING (actor_id)

GROUP BY actor_id

ORDER BY COUNT(*) DESC

This is extremely useful as it saves us from a lot of typing. In fact, the way GROUP BYsemantics is defined, we can put all sorts of column references in the SELECT clause, which are any of:

  • Column expressions that appear in the GROUP BYclause
  • Column expressions that are functionally dependent on the set of column expressions in the GROUP BYclause
  • Aggregate functions

3 Comments »

Leave a Reply

error: Content is protected !!
%d bloggers like this: