드디어 테이블과 테이블을 연결하는 조인에 대해 배울 차례다. 미리 말하자면, 이 교육 과정에서 조인은 매우 중요하며 배우는 과정이 쉽지 만은 않을 것이다. 조인을 간단히 배워서는 제대로 데이터를 사용할 수 없기 때문이다. 중간에 이해 못하더라도 우선은 끝까지 완주하겠다는 각오를 갖고 공부에 임하기 바란다.
본격적인 조인 구문을 배우기 전에 테이블과 테이블 간의 관계, 다시 말해 데이터와 데이터의 간의 관계에 대한 이해가 필요하다.
PRIMARY KEY
PK(PRIMARY KEY)에 대해 간단히 복습하고 넘어가자. PK는 테이블의 UK(UNIQUE KEY) 중에 대표로 선정한 UK다. UK는 테이블의 데이터를 유일하게 식별할 수 있는 컬럼 또는 컬럼들을 뜻한다. 다음과 같이 데이터 집합별로 UK와 PK의 예를 들 수 있다.
•
학생 데이터 집합: 주민번호, 핸드폰번호, 학번 → 학번을 PK로 선정
•
백화점 회원 데이터 집합: 회원ID, 주민번호, 핸드폰번호, E-Mail → 회원ID를 PK로 선정
•
주식 종목 데이터 집합: 종목코드(Symbol), 거래소+종목명 → 종목코드를 PK로 선정
•
거래데이터 집합: 거래번호, 거래일시+거래상품 → 거래일시+거래상품을 PK로 선정
우리가 실습 중인 테이블들의 PK를 살펴보자.
FOREIGN KEY
FK(FOREIGN KEY)는 데이터 집합 간의 참조 관계(Relationship)를 보장하기 위한 Key다.
FK를 이해하기 위해 FK의 필요성을 알 필요가 있다. “배달의 민족”과 같은 배달앱 시스템을 설계한다고 가정해보자. 배달 서비스를 위해서는 회원과 주문 테이블이 필요하다. 아래와 같이 개념 설계를 할 수 있다. 회원 테이블의 PK는 (회원ID), 주문 테이블의 PK는 (주문번호)다.
회원과 주문 테이블에 대해 다음과 같은 주문 처리 시나리오가 추가되었다.
•
회원만 주문할 수 있다.
•
주문에는 어느 회원이 주문했는지 명확히 관리되어야 한다.
위 시나리오를 해결하기 위해서는 아래와 같이 주문 테이블에 (회원ID) 컬럼을 추가해야 한다.
위와 같이 주문 테이블에 (회원ID) 컬럼만 추가한 것으로는, 주문 테이블의 (회원ID)가 어느 테이블을 참조하는지 명확하게 알 수는 없다. 물론, 지금은 테이블이 두 개밖에 없으므로 주문 테이블의 (회원ID)는 당연히 회원 테이블의 (회원ID)를 참조한다는 것을 쉽게 추리할 수 있다. 하지만 테이블이 많아질수록, 테이블 간의 직접적인 관계선이 없으면 이와 같은 추리가 쉽지 않다.
주문 테이블에 (회원ID)를 추가하면서 아래와 같이 관계선도 그려주는 것이 좋다. 이를 통해 주문 테이블의 (회원ID)가 회원 테이블을 참조한다는 것을 명확하게 알 수 있다. 관계선을 그리는 방법에는 다양한 표기법이 있다. 여기서 사용한 표기법은 IE(Information Engineering) 표기법이다.
위와 같이 관계선을 만들게 되면,
•
관계선으로 연결된 두 테이블 중 한 쪽은 참조되는 쪽이 되고, 다른 한쪽은 참조하는 쪽이 된다.
•
편하게 이해하기 위해 참조되는 쪽은 부모, 참조하는 쪽은 자식이라고 생각해도 좋다.
•
위 ERD에서는 회원이 참조되는 쪽(부모)이고 주문이 참조하는 쪽(자식)이다.
•
부모가 자식을 낳고, 자식이 부모의 성(Last Name)을 물려받는 것처럼,
◦
부모 데이터가 먼저 만들어지고 자식 데이터가 나중에 만들어지며,
◦
자식 데이터는 부모 데이터의 PK 값을 물려 받는다.
▪
바꿔 말하면, 부모 데이터에 존재하지 않는 PK 값은 자식 데이터에 등록할 수 없다.
▪
상황에 따라서 PK 값을 NULL로 채워서 자식 데이터를 만들 수는 있다.
•
참조되는 쪽이 먼저 만들어지는 데이터, 참조하는 쪽이 나중에 만들어지는 데이터다.
이러한 참조 관계는 부모 테이블의 PK 컬럼을 사용해 이루어진다. 참조되는 쪽(부모)인 회원의 PK 컬럼은 (회원ID)다. 그러므로 주문이 회원을 참조한다는 것은, 주문 테이블의 (회원ID) 컬럼이 회원 테이블의 PK인 (회원ID) 컬럼을 참조한다는 뜻이다.
ERD에 표현한 관계선은 FOREIGN KEY(FK)라는 제약을 만들어 구현할 수 있다. (시스템에 따라 관계선만 그리고 FK 제약은 생략하기도 한다.) FK 제약은 참조하는 쪽 테이블에 만든다. 주문이 회원을 참조하므로 주문 테이블에 FK 제약을 만들어야 한다. 다음과 같이 FK 제약을 만들 수 있다.
ALTER TABLE 주문 ADD CONSTRAINT FK_주문_회원ID FOREIGN KEY(회원ID) REFERENCES 회원(회원ID);
SQL
복사
이러한 참조 관계를 설정하면, 회원과 주문 테이블들은 다음과 같이 동작에 제약 사항이 생긴다.
•
주문 테이블의 (회원ID)는 반드시 회원 테이블에 존재하는 (회원ID) 값만 사용할 수 있다.
◦
단, 설정에 따라 주문 테이블의 회원ID에 NULL 값을 허용할 수는 있다.
•
주문 테이블에서 사용 중인 (회원ID)는 회원 테이블에서 삭제할 수 없다.
◦
해당 (회원ID) 데이터를 삭제하려면, 주문부터 삭제한 후 회원 데이터를 삭제해야 한다.
위와 같은 제약으로 회원과 주문간에 데이터 일관성이 확보되며 결과적으로 데이터의 품질이 올라간다. 그럼에도 불구하고, FK 제약은 개발 과정을 불편하게 한다는 이유로 만들지 않고 진행하는 프로젝트가 매우 많다. 개인적으로 좀 안타깝게 생각하는 부분이다.
정리하면 FOREIGN KEY는 두 테이블 간의 참조 무결성을 보장해주는 역할을 한다. 이를 통해 데이터의 품질을 확보할 수 있다. 품질이 확보된 데이터는 가치있는 분석을 만들어 낸다. 데이터 분석을 조금이라도 공부해봤다면 “쓰레기를 집어 넣으면 쓰레기가 나온다.”라는 말을 들어봤을 것이다. 품질이 확보되지 않은 쓰레기 데이터로 분석을 한다면 쓰레기 결과가 계속 나오게 된다. 안타깝게도 FK만 만들었다고 데이터 품질이 좋아지는 것은 아니다. 데이터 품질은 어느 한 부분을 적용했다고 이룰 수 있는 것은 아니다. 모든 팀원들의 관심과 노력이 필요한 부분이다.
Tip. 무결성(Integrity)
무결성은 조금 어려운 말이다. 글자 하나하나 풀어보면 “결함이 없는 성질”을 뜻한다. 쉽게 생각하면 데이터에 결함이 없도록 하는 개념이라고 볼 수 있다. 조금 더 살을 붙여 이야기하면, 데이터의 정확성, 일관성, 유효성을 유지하는 개념이라 볼 수 있다.
관계 차수
관계선에는 관계 차수에 대한 의미도 담겨 있다. 관계 차수란 테이블 간에 짝을 이루는 개수다. 관계 차수에는 1:1, 1:M(Many) 또는 M:M(Many:Many)이 있다. 관계 차수를 파악하는 기본적인 방법은 관계선의 양쪽 끝 모양을 보는 것이다.
위 그림에서 관계선은 IE(Information Engineering) 표기법을 사용했다. IE 표기법에서는 관계선 양쪽 끝의 모양에 따라 관계 차수를 파악할 수 있다. 흔히 말하는 까치발(Crow’s Foot) 모양을 가진 쪽이 M(Many)쪽이다. 까치발이 아닌 모양은 1이 된다. 위 그림에서는 회원:주문을 다양한 관계 차수로 표현하고 있다. 관계차수에 따라 두 데이터 집합(테이블)이 어떻게 동작하는지 정리해보면 다음과 같다.
•
회원:주문 = 1:M
◦
회원(1쪽) 한 명이 여러 주문(M쪽)을 할 수 있다.
◦
하나의 주문(M쪽)에는 반드시 한 명의 회원(1쪽)만 매핑되어 있다.
•
회원:주문 = 1:1
◦
회원(1쪽) 한 명이 주문(1쪽)을 한 번만 할 수 있다.
◦
하나의 주문(1쪽)에는 한 명의 회원(1쪽)만 매핑되어 있다.
•
회원:주문 = M:M
◦
회원(M쪽) 한 명이 여러 주문(M쪽)을 할 수 있다.
◦
주문(M쪽) 한 건도 여러 회원(M쪽)을 가질 수 잇다.
◦
테이블 간에 M:M 관계는 만들지 않는 것이 정석이다.
◦
M:M 관계는 개념적으로만 표현할 뿐, 실제 M:M 관계가 나오지 않도록 설계를 해야 한다.
관계선의 양쪽 끝 모양을 통해 관계차수를 파악했다. 개인적으로 이와 같이 관계차수를 파악하는 것보다 상식적으로 또는 업무적으로 접근하는 것을 권장한다. 일반적으로는 한 명의 회원이 여러 주문을 할 수 있을 것이다. 그리고 주문은 보통 한 명의 회원에 속한다. (경우에 따라 여러 회원에 속할 수도 있겠지만, 보통은 한 명의 주된 회원이 있다.) 그러므로 회원:주문은 1:M이다. 이러한 업무적인 내용이 관계차수로 표현되는 것이기 때문에, ERD의 관계선을 이해하는 것보다 업무를 정확히 이해하는 것이 더 중요하다.
다양한 관계선
주문배달 시스템에 테이블 몇 개를 더 그려보았다. 테이블 간의 관계선을 통해 다양한 관계에 대해 알아보도록 하자.
1.
하나의 테이블은 여러 테이블을 참조
a.
주문 테이블은 회원과 가게 테이블을 동시 참조
2.
관계선의 양쪽 끝 모양으로 1:M 관계 파악
3.
하나의 테이블은 여러 테이블에 참조 당할 수 있다.
a.
가게 테이블은 주문과 가게별메뉴 테이블에서 동시 참조
4.
FK이면서 PK 일수도 있다.
a.
주문상세의 주문번호 컬럼은 FK이면서 PK
5.
참조한 컬럼(FK)을 PK로 사용하면 관계선은 실선
a.
참조한 컬럼을 일반 컬럼으로 사용하면 관계선은 점선
6.
동그라미(0)가 있으면 상대쪽 테이블에서 나는 없어도 되는 존재
a.
가게 입장에서 주문은 없어도 되는 존재
참조된다. 참조한다.
간단한 내용이지만, “참조된다”와 “참조한다”의 의미를 고민하고 넘어가도록 하자.
•
장르는 컨텐츠에 참조 ( 된다 / 한다 )
◦
장르의 PK 컬럼이 컨텐츠에서 참조되고 있다는 의미다.
•
컨텐츠는 장르를 대표장르로써 참조 ( 된다 / 한다 )
◦
컨텐츠의 대표장르ID 컬럼이 장르 테이블의 PK인 장르ID를 참조한다는 뜻이다.
•
시청은 컨텐츠를 참조 ( 된다 / 한다 )
◦
시청의 컨텐츠ID 컬럼이 컨텐츠의 PK인 컨텐츠ID를 참조한다는 의미다.
•
회원은 시청에 참조 ( 된다 / 한다 )
◦
회원의 PK인 회원ID가 시청 테이블에 참조된다는 의미다.
BOOSTER QUIZ
BOOSTER QUIZ 7-1-1
논리 ERD를 살펴보고 ( A ) 에 들어갈 내용을 채우시오.
•
( A ) 테이블은 매장 테이블을 참조한다.
•
그리고 ( A ) 테이블은 회원 테이블도 참조한다.
•
바꿔 말하면, 매장 테이블과 회원 테이블은 ( A ) 테이블에 참조된다는 뜻이다.
•
이 말은, ( A ) 테이블에 발생되는 ( A ) 데이터는 매장과 회원에 존재해야 한다는 뜻이다.
•
경우에 따라서는 ( A ) 테이블에 매장과 회원을 NULL 값으로 발생시키는 건 가능할 수 있다.
BOOSTER QUIZ 7-1-2
물리 ERD를 살펴보고 ( A ), ( B ), (C) 에 들어갈 내용을 채우시오.
•
( A ) 테이블은 Shop과 Member를 참조한다.
•
참조한다는 것은, Shop 테이블의 ( B ) 인 ShopId와 Member 테이블의 ( B )인 MemberId를
( A ) 테이블에서 ( C )로 사용하고 있다는 뜻이기도 하다.
BOOSTER QUIZ 7-1-3
논리 ERD를 살펴보고 ( A ), ( B ), ( C ) 에 들어갈 내용을 채우시오.
•
( A ) 테이블은 주문 테이블을 참조하고 있다.
•
( A ) 테이블의 PK는 주문번호와 ( B )다.
◦
이 뜻은, 하나의 주문에 여러 개의 상세 내역이 있을 수 있다는 뜻이다.
•
( A ) 테이블은 주문 테이블과 함께 ( C ) 테이블도 참조하고 있다.
•
( C ) 테이블의 PK 인 상품ID가 ( A ) 테이블에 일반 컬럼으로 존재한다.
•
정리하면, 주문에 대해 어떤 상품이 주문 되었는지 알려면 ( A ) 테이블을 확인해야 한다.
•
그리고 하나의 주문에는 여러 ( C )의 주문이 가능하다.
BOOSTER QUIZ 7-1-4
물리 ERD를 살펴보고 괄호안에 내용 중 적절한 것을 선택하시오.
•
Ord 테이블은 OrdDet 테이블에 참조 ( 된다. / 한다. )
◦
이는 Ord 테이블의 PK 인 OrdNo가 OrdDet 테이블에 ( 비사용 / 사용 ) 되고 있다는 뜻이다.
•
만약에 OrdNo가 100 번인 신규 주문이 저장되려면, ( Ord / OrdDet ) 에 먼저 저장되어야 한다.
◦
그래야만 ( Ord / OrdDet ) 에도 OrdNo 100 번을 저장할 수 있다.
•
OrdDet 테이블의 PK 는 OrdNo와 OrdDetNo로 구성되어 있다.
◦
이는 OrdNo 별로 여러 건의 상세 데이터를 저장할 수 ( 있다 / 없다 ) 는 뜻이다.
◦
그리고 OrdDet 테이블은 Item 테이블의 ItemId를 참조 ( 된다. / 한다. )
•
Ord, OrdDet, Item 관계를 종합해보면 발생된 주문 속한 상품 정보는 ( Ord / OrdDet / Item ) 에 관리되고 있다.
Upper: 7. JOIN
