Movatterモバイル変換

• Functional Dependencies Derived from Keys

• Functional Dependencies Derived from Multiple-Column Keys and fromEqualities

• 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 coWHERE cl.CountryCode = co.CodeGROUP BY co.Code;

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

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

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

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

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 SpokenByFROM countrylanguage cl, country coWHERE cl.CountryCode = co.CodeGROUP BY cl.CountryCode, cl.Language;

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

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

Moreover, because of the equality in theWHERE clause:

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

And, becauseco.Code is primary key ofco:

{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, aUNIQUE key overNOT NULL columns could be used instead of a primary key.

AnINNER JOIN condition can be used instead ofWHERE. The same functional dependencies apply:

SELECT co.Name, cl.Language,cl.Percentage * co.Population/100.0 AS SpokenByFROM countrylanguage cl INNER JOIN country coON cl.CountryCode = co.CodeGROUP BY cl.CountryCode, cl.Language;

Whereas an equality test in aWHERE condition orINNER JOIN condition is symmetric, an equality test in an outer join condition is not, because tables play different roles.

Assume that referential integrity has been accidentally broken and there exists a row ofcountrylanguage without a corresponding row incountry. Consider the same query as in the previous example, but with aLEFT JOIN:

SELECT co.Name, cl.Language,cl.Percentage * co.Population/100.0 AS SpokenByFROM countrylanguage cl LEFT JOIN country coON cl.CountryCode = co.CodeGROUP BY cl.CountryCode, cl.Language;

For a given value ofcl.CountryCode, the value ofco.Code in the join result is either found in a matching row (determined bycl.CountryCode) or isNULL-complemented if there is no match (also determined bycl.CountryCode). In each case, this relationship applies:

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

cl.CountryCode is itself functionally dependent on {cl.CountryCode,cl.Language} which is a primary key.

If in the join resultco.Code isNULL-complemented,co.Name is as well. Ifco.Code is notNULL-complemented, then becauseco.Code is a primary key, it determinesco.Name. Therefore, in all cases:

{co.Code} -> {co.Name}

Which yields:

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

As a result, the query is valid.

However, suppose that the tables are swapped, as in this query:

SELECT co.Name, cl.Language,cl.Percentage * co.Population/100.0 AS SpokenByFROM country co LEFT JOIN countrylanguage clON cl.CountryCode = co.CodeGROUP BY cl.CountryCode, cl.Language;

Now this relationship doesnot apply:

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

Indeed, allNULL-complemented rows made forcl is put into a single group (they have bothGROUP BY columns equal toNULL), and inside this group the value ofco.Name can vary. The query is invalid and MySQL rejects it.

Functional dependence in outer joins is thus linked to whether determinant columns belong to the left or right side of theLEFT JOIN. Determination of functional dependence becomes more complex if there are nested outer joins or the join condition does not consist entirely of equality comparisons.

Suppose that a view on countries produces their code, their name in uppercase, and how many different official languages they have:

CREATE VIEW country2 ASSELECT co.Code, UPPER(co.Name) AS UpperName,COUNT(cl.Language) AS OfficialLanguagesFROM country AS co JOIN countrylanguage AS clON cl.CountryCode = co.CodeWHERE cl.isOfficial = 'T'GROUP BY co.Code;

This definition is valid because:

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

In the view result, the first selected column isco.Code, which is also the group column and thus determines all other selected expressions:

{country2.Code} -> {country2.*}

MySQL understands this and uses this information, as described following.

This query displays countries, how many different official languages they have, and how many cities they have, by joining the view with thecity table:

SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,COUNT(*) AS CitiesFROM country2 AS co2 JOIN city ciON ci.CountryCode = co2.CodeGROUP BY co2.Code;

This query is valid because, as seen previously:

{co2.Code} -> {co2.*}

MySQL is able to discover a functional dependency in the result of a view and use that to validate a query which uses the view. The same would be true ifcountry2 were a derived table (or common table expression), as in:

SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,COUNT(*) AS CitiesFROM( SELECT co.Code, UPPER(co.Name) AS UpperName, COUNT(cl.Language) AS OfficialLanguages FROM country AS co JOIN countrylanguage AS cl ON cl.CountryCode=co.Code WHERE cl.isOfficial='T' GROUP BY co.Code) AS co2JOIN city ci ON ci.CountryCode = co2.CodeGROUP BY co2.Code;

MySQL is able to combine all of the preceding types of functional dependencies (key based, equality based, view based) to validate more complex queries.