데이터모델링이론 - 엔터티의 논리적 연결 관계를 표현하는 조인

데이터모델링이론 - 엔터티의 논리적 연결 관계를 표현하는 조인

 

엔터티는 관계를 통해 식별자를 다른 엔터티로 상속합니다. 식별자를 상속하는 엔터티를 부모엔터티, 상속받는 엔터티를 자식엔터티라고 볼 때, 부모의 식별자를 자식의 식별자에 포함하면 식별관계, 일반속성에 포함하면 비식별관계가 됩니다. 엔터티 간 관계 맺음은 상속된 속성을 통해 데이터를 연결하는 행위입니다. 이런 데이터의 연결을 SQL로 표현한 것을 join(조인)이라 합니다.

 

각 고객은 때때로 여러 번 주문할 수 있습니다. (선택)
각 주문은 반드시 한 명의 고객에 의해 발생합니다. (필수)

 

 

고객 엔터티와 주문 엔터티는 관계를 맺으면서 고객엔터티의 식별자인 고객번호를 주문엔터티에 상속합니다. 이를 통해 데이터를 결합합니다. 관계 맺은 두 엔터티의 데이터에서 특정 칼럼의 값을 찾는 프로세스는 다음과 같습니다.

 

1. 주문 데이터에서 주문번호가 110001인 데이터를 찾는다.
2. 주문번호가 110001 인 데이터의 행에서 고객번호가 100 임을 찾는다
3. 고객 테이블에서 고객번호가 100 인 행을 찾는다.
4. 고객번호가 100인 행에서 고객명이 '정우진'이라는 것을 확인한다.

 

2번과 3번이 조인의 과정이고 조인과정에서 고객번호를 매핑키 즉, 조인키로 활용합니다. 조인을 sql로 표현하면 아래와 같습니다.

 

SELECT B.고객명 ---- 4
FROM  주문 A, 고객 B
WHERE A.주문번호 = '1100001' ----- 1
AND A.고객번호 = B.고객번호 ----- 2, 3

 

계층형 데이터모델의 조인

 

같은 엔터티 내의 칼럼끼리 계층구조를 갖는 엔터티를 계층형 데이터모델이라고 합니다. 사원엔터티의 사원번호와 관리자번호가 대표적인 예시입니다. 이를 SQL로 표현하면 아래와 같습니다.

 

SELECT B.ENAME --- 4
FROM EMP A, EMP B
WHERE A.ENAME = B.ENAME  --- 1
AND A.MGR = B.EMPNO --- 2, 3

 

조인과정은 다음과 같습니다.

 

1. EMP A 에서 ENAME 이 'SMITH'인 데이터를 찾는다.
2. EMP A에서 ENAME이 'SMITH'인 데이터의 행에서 MRG 가 '7902'라는 것을 확인한다.
3. EMP B에서 EMPNO 가 7902인 데이터를 찾는다.
4. EMP B 에서 EMPNO가 7902인 데이터 행의 ENAME 이 'FORM'라는 것을 확인한다.

 

동일한 엔터터를 조인하는 것을 셀프조인이라고 합니다. 셀프조인이 가능한 이유는 MGR 속성값에 EMPNO의 속성값이 들어가기 때문입니다. 즉, MGR과 EMPNO를 매핑키로 조인합니다.

 

상호배타적 관계의 조인

 

아래와 같은 관계를 갖는 엔터티를 상호배타적 관계라고 합니다.

 

 

개인엔터티와 법인엔터티는 상호배타적 관계이기 때문에 주문 엔터티는 두 엔터티 중 하나의 엔터티만 상속받을 수 있습니다. 주문 엔터티의 개인/법인 번호 칼럼은 상호 베타적 관계의 두 엔터티로부터 상속받은 식별자이고 고객구분코드 칼럼에 두 엔터티 중 어느 엔터티에서 상속받은 것인지에 대한 구분값이 있습니다. 개인/법인번호가 '110001'인 레코드를 찾는 쿼리는 다음과 같습니다.

 

SELECT B.개인고객명
FROM 주문 A, 개인고객 B
WHERE A.주문번호 = '1100001'
AND A.고객구분코드 = '01'
AND A.개인/법인번호 = B.개인번호
UNION ALL
SELECT B.법인고객명
FROM 주문 A, 법인고객 B
WHERE A.주문번호 = '1100001'
AND A.고객구분코드 = '02'
AND A.개인/법인번호 = B.법인번호

 

개인/법인번호가 '110001'인 레코드의 고객구분코드를 알 수 없다면 위와 같이 상호 배타적인 두 테이블과 각각 조인 후 결과를 결합하는 것이 최선의 방법입니다. 단, 개인번호와 법인번호의 값이 같은 경우 위의 sql은 정확한 결과를 도출할 수 없습니다. 개인번호와 법인번호가 중복되지 않는다는 보장이 있으면 아래와 같은 쿼리로도 결과도출이 가능합니다.

 

SELECT COALESCE(B.개인고객명, C.법인병) 고객명
FROM 주문 A LEFT OUTER JOIN 개인고객 B 
ON (A.개인/법인번호 = B.개인번호) -- B.개인번호와 일치하지 않는 개인/법인번호인 레코드도 조회)
LEFT OUTER JOIN 법인고객 C
ON (A.개인/법인번호 = C.개인번호) -- C.법인번호와 일치하지 않는 개인/법인번호인 레코드도 조회)
WHERE A.주문번호 = '1100001'

 

개인번호와 법인번호는 중복되지 않기 때문에 아우터 조인으로 연결할 수 있습니다. 주문 테이블의 레코드는 개인고객/법인고객 테이블 둘 중 하나와는 반드시 연결됩니다. 만약 주문번호 110001인 주문레코드가 데이터 정합성 오류로 인해 두 엔터티 모두와 조인하지 못하면 결괏값인 고객명은 COALESCE 함수로 인해 null로 반환됩니다.

 

COALESCE(A, B)

COALESCE 함수는 매개변수를 순서대로 평가하여 NULL이 아닌 첫 번째 매개변수를 리턴합니다.

모든 인수가 NULL인 경우에만 결과가 NULL입니다. 매개변수는 스칼라 유형일 수 있지만 모두 같은 유형일 필요는 없습니다.