## Functional Dependencies Derived from Keys 

•Functional Dependency Special Cases 

•Functional Dependencies and Views 

•Combinations of Functional Dependencies 

The following query selects, for each country, a count of spoken languages: 

SELECT co.Name, COUNT(*) FROM countrylanguage cl, country co WHERE cl.CountryCode = co.Code GROUP BY co.Code; 

co.Code  is a primary key of co , so all columns of co  are functionally dependent on it, as expressed using this notation: 

{co.Code} -> {co.*} 

Thus, co.name  is functionally dependent on GROUP BY  columns and the query is valid. 

A UNIQUE  index over a NOT NULL  column could be used instead of a primary key and the same functional dependence would apply. (This is not true for a UNIQUE  index that permits NULL  values because it permits multiple NULL  values and in that case uniqueness is lost.) 

## Functional Dependencies Derived from Multiple-Column Keys and from Equalities 

This query selects, for each country, a list of all spoken languages and how many people speak them: 

SELECT co.Name, cl.Language, cl.Percentage * co.Population / 100.0 AS SpokenBy FROM countrylanguage cl, country co WHERE cl.CountryCode = co.Code GROUP BY cl.CountryCode, cl.Language; 

The pair ( cl.CountryCode , cl.Language ) is a two-column composite primary key of cl , so that column pair uniquely determines all columns of cl : 

{cl.CountryCode, cl.Language} -> {cl.*} 

Moreover, because of the equality in the WHERE  clause: 

{cl.CountryCode} -> {co.Code} 

And, because co.Code  is primary key of co : 

{co.Code} -> {co.*} 

“Uniquely determines”  relationships are transitive, therefore: 

{cl.CountryCode, cl.Language} -> {cl.*,co.*} 

As a result, the query is valid. 

As with the previous example, a UNIQUE  key over NOT NULL  columns could be used instead of a primary key. 

An INNER JOIN  condition can be used instead of WHERE . The same functional dependencies apply: 

SELECT co.Name, cl.Language, cl.Percentage * co.Population/100.0 AS SpokenBy FROM countrylanguage cl INNER JOIN country co 

