14.19.4 Detection of Functional Dependence

14.19.4 Detection of Functional Dependence

다음 내용에서는 MySQL이 functional dependency를 감지하는 여러 예를 설명합니다. 예제에서는 다음 표기법을 사용합니다:

1{X} -> {Y}

이는 “_X_가 _Y_를 유일하게 결정한다(uniquely determines)”는 의미이며, 이는 또한 _Y_가 _X_에 함수적으로 종속(functionally dependent)된다는 것을 의미합니다.

예제에서는 world 데이터베이스를 사용합니다. 이 데이터베이스는
https://dev.mysql.com/doc/index-other.html에서 다운로드할 수 있습니다. 같은 페이지에서 데이터베이스를 설치하는 방법에 대한 상세 내용을 찾을 수 있습니다.

• Functional Dependencies Derived from Keys
• Functional Dependencies Derived from Multiple-Column Keys and from Equalities
• Functional Dependency Special Cases
• Functional Dependencies and Views
• Combinations of Functional Dependencies

Functional Dependencies Derived from Keys

다음 쿼리는 각 country에 대해, 사용되는 언어의 수를 선택합니다:

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

co.Code는 co의 primary key이므로, co의 모든 컬럼은 다음 표기법으로 표현되는 것처럼 co.Code에 함수적으로 종속합니다:

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

따라서 co.name은 GROUP BY 컬럼에 함수적으로 종속하며, 이 쿼리는 유효합니다.

NOT NULL 컬럼에 대한 UNIQUE 인덱스는 primary key 대신 사용할 수 있고, 동일한 함수적 종속성이 적용됩니다.
(이는 NULL 값을 허용하는 UNIQUE 인덱스에는 해당하지 않습니다. 그 경우 여러 개의 NULL 값을 허용하므로 유일성이 상실되기 때문입니다.)

Functional Dependencies Derived from Multiple-Column Keys and from Equalities

다음 쿼리는 각 country에 대해, 사용되는 모든 언어 목록과 각 언어를 사용하는 사람 수를 선택합니다:

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

(cl.CountryCode, cl.Language) 쌍은 cl의 두 컬럼으로 이루어진 복합 primary key이므로, 이 컬럼 쌍은 cl의 모든 컬럼을 유일하게 결정합니다:

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

또한, WHERE 절의 equality 때문에:

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

그리고 co.Code는 co의 primary key이므로:

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

“Uniquely determines” 관계는 전이적(transitive)이므로, 따라서:

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

그 결과, 이 쿼리는 유효합니다.

앞의 예와 마찬가지로, primary key 대신 NOT NULL 컬럼에 대한 UNIQUE key를 사용할 수 있습니다.

WHERE 대신 INNER JOIN 조건을 사용할 수 있습니다. 동일한 함수적 종속성이 적용됩니다:

1SELECT co.Name, cl.Language,
2cl.Percentage * co.Population/100.0 AS SpokenBy
3FROM countrylanguage cl INNER JOIN country co
4ON cl.CountryCode = co.Code
5GROUP BY cl.CountryCode, cl.Language;

Functional Dependency Special Cases

WHERE 조건이나 INNER JOIN 조건의 equality test는 대칭적이지만, outer join 조건의 equality test는 그렇지 않습니다. 이는 테이블이 서로 다른 역할을 하기 때문입니다.

참조 무결성(referential integrity)이 실수로 깨져서, country에 대응하는 row가 없는 countrylanguage의 row가 존재한다고 가정합니다. 이전 예제와 동일한 쿼리를 생각하되, 이번에는 LEFT JOIN을 사용합니다:

1SELECT co.Name, cl.Language,
2cl.Percentage * co.Population/100.0 AS SpokenBy
3FROM countrylanguage cl LEFT JOIN country co
4ON cl.CountryCode = co.Code
5GROUP BY cl.CountryCode, cl.Language;

주어진 cl.CountryCode 값에 대해, join 결과에서 co.Code 값은 (cl.CountryCode에 의해 결정되는) 매칭되는 row에서 찾거나, 매칭이 없으면 (cl.CountryCode에 의해 결정되는) NULL로 보충(NULL-complemented)됩니다. 각 경우에 다음 관계가 적용됩니다:

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

cl.CountryCode 자체는 primary key인 {cl.CountryCode, cl.Language}에 함수적으로 종속합니다.

join 결과에서 co.Code가 NULL-complemented이면, co.Name도 마찬가지입니다. co.Code가 NULL-complemented가 아니라면, co.Code가 primary key이므로 co.Name을 결정합니다. 따라서 모든 경우에 다음이 성립합니다:

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

이는 다음을 이끌어냅니다:

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

그 결과, 이 쿼리는 유효합니다.

그러나 다음 쿼리와 같이 테이블의 위치가 바뀐 경우를 가정해 봅니다:

1SELECT co.Name, cl.Language,
2cl.Percentage * co.Population/100.0 AS SpokenBy
3FROM country co LEFT JOIN countrylanguage cl
4ON cl.CountryCode = co.Code
5GROUP BY cl.CountryCode, cl.Language;

이제 다음 관계는 더 이상 성립하지 않습니다:

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

실제로, cl에 대해 만들어진 모든 NULL-complemented row는 단일 group에 들어갑니다 (이들은 두 GROUP BY 컬럼이 모두 NULL입니다). 그리고 이 group 내부에서 co.Name 값은 변할 수 있습니다. 이 쿼리는 유효하지 않으며, MySQL은 이를 거부합니다.

outer join에서의 함수적 종속성은 결정자 컬럼이 LEFT JOIN의 왼쪽에 속하는지 오른쪽에 속하는지와 연관되어 있습니다. 중첩 outer join이 있거나 join 조건이 전부 equality 비교로만 구성되지 않는 경우에는 함수적 종속성 결정이 더 복잡해집니다.

Functional Dependencies and Views

country에 대한 view가, 그 code, 대문자로 변환된 name, 그리고 서로 다른 official language의 개수를 생성한다고 가정합니다:

1CREATE VIEW country2 AS
2SELECT co.Code, UPPER(co.Name) AS UpperName,
3COUNT(cl.Language) AS OfficialLanguages
4FROM country AS co JOIN countrylanguage AS cl
5ON cl.CountryCode = co.Code
6WHERE cl.isOfficial = 'T'
7GROUP BY co.Code;

이 정의는 다음 이유로 유효합니다:

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

view 결과에서, 첫 번째로 선택된 컬럼은 group 컬럼이기도 한 co.Code이며, 따라서 다른 모든 선택된 표현식을 결정합니다:

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

MySQL은 이를 이해하고 다음에 설명된 대로 이 정보를 사용합니다.

다음 쿼리는 view를 city 테이블과 join하여 country, 서로 다른 official language의 개수, 그리고 city의 개수를 표시합니다:

1SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,
2COUNT(*) AS Cities
3FROM country2 AS co2 JOIN city ci
4ON ci.CountryCode = co2.Code
5GROUP BY co2.Code;

이 쿼리는, 앞에서 본 것처럼 다음이 성립하므로 유효합니다:

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

MySQL은 view 결과에서 함수적 종속성을 찾아내고, 이를 사용하여 view를 사용하는 쿼리의 유효성을 검사할 수 있습니다. 다음과 같이 country2가 파생 테이블(derived table) (또는 공통 테이블 식, common table expression)인 경우에도 마찬가지입니다:

1SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,
2COUNT(*) AS Cities
3FROM
4(
5 SELECT co.Code, UPPER(co.Name) AS UpperName,
6 COUNT(cl.Language) AS OfficialLanguages
7 FROM country AS co JOIN countrylanguage AS cl
8 ON cl.CountryCode=co.Code
9 WHERE cl.isOfficial='T'
10 GROUP BY co.Code
11) AS co2
12JOIN city ci ON ci.CountryCode = co2.Code
13GROUP BY co2.Code;

Combinations of Functional Dependencies

MySQL은 위에서 설명한 모든 유형의 함수적 종속성(key 기반, equality 기반, view 기반)을 결합하여 더 복잡한 쿼리의 유효성을 검사할 수 있습니다.