Skip to content
Advertisements

DBMS/U4 Topic 2 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
Advertisements
Advertisements
Advertisements

2 Comments »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: