[문과 코린이의 IT 기록장] RDBMS Modeling 기초 (1:M 관계, 1:M 재귀적 관계, N:M 관계)
RDBMS Modeling 기초 - 인프런 | 강의
본 강좌는 데이터베이스 설계 이론을 실습 위주로 쉽게 풀어냈습니다. 책 등을 통해서 경험하신 분들은 대부분 데이터베이스 이론이 어렵다고 느끼고 포기하신 경험들이 있을 겁니다. 저도 그
www.inflearn.com
1. 1:M 관계
1) 1:M 관계
- 한쪽이, 관계를 맺은 쪽의, 여러 객체를 갖는 것을 의미한다.
* 관계는, 두 개의 테이블에 대해서만 가진다. 즉, 세개의 테이블에 관계가 존재한다라는 말은 성립되지 않는다.1
- 가장 흔하게 나타나는, 매우 일반적인 형태이다.
ex 1. 부모 - 자식 관계
* 여기서 부모와 자식은 집합으로 구성되며, 이를 관계형 데이터베이스에서는 엔티티라고 한다.
ex 2. 컴퓨터 Directory 구조
2) 1:M 관계 예시
[ 부모 ]
| ID ( PK ) | 성명 |
| 1 | 홍길동 |
| 2 | 김종남 |
| 3 | 이하선 |
[ 자식 ]
| 부모 ID (FK) | ID ( PK ) | 성명 |
| 1 | A | 홍길자 |
| 1 | B | 홍난파 |
| 2 | C | 김상영 |
| 2 | D | 김상순 |
| 3 | E | 이필호 |
* [ 자식 ] 테이블의 부모 ID는, [ 부모 ] 테이블의 기본키인 ID를 참조하고 있다. 이를 외래키(Foriegn Key)라고 한다.
* 따라서 부모 ID에, [ 부모 ] 테이블의 ID에 없는 레코드는 입력할 수 없다. 즉 부모가 없는 자식을 넣으려고 하면, 에러가 발생한다.
3) DB 실습 1
* 데이터베이스 다이어그램 : ERD를 표현한 것. (ERD : Entity Relational Digagram)
(1) [ 학년 ] 테이블 데이터 입력
insert into 학년 values(1, '1학년');
insert into 학년 values(2, '2학년');
insert into 학년 values(3, '3학년');
select * from 학년;
(2) [ 반 ] 테이블, 외래키 설정
(3) [ 반 ] 테이블, 데이터 입력
insert into 반 values(1,1,'1반');
-- 수행되면 안됨. 4학년은 '학년'테이블에 존재하지 않기 때문 (오류 발생)
insert into 반 values(4,1,'1반');
insert into 반 values(2,1,'1반');
-- 결과 보기
select * from 반;
(4) [ 학년 ] 테이블과 [ 반 ] 테이블, inner join 수행
-- PK인 a.학년번호 는 같이 넘겨주는 것이 좋음.
select a.학년번호, a.학년이름, b.반이름
from 학년 a join 반 b
on a.학년번호 = b.학년번호
4) DB 실습 2
* 모델링을 여러 방법으로 하는 것이 좋음. 관계를 처음 배우면 어느 테이블에 바라봤을 때 1:M인가를 훈련해봐야 함.
ex. 회사 및 부서 테이블 설계 : 회사 모델링 - 부서 모델링 - 관계 설정
a. 회사 및 부서 스키마 설계
: 스키마를 설계한다는 것은, 테이블간 키를 결정하는 것이다. 따라서, 해당 테이블의 기본 키와, 기본 컬럼(Name) 하나만 설정해 두고, 테이블 간 관계 설정 작업을 진행한다.
cf ) CompanyId : Identitiy Specification으로, 자동 증분되도록 PK를 설정
b. 관계 설정
: 부서 테이블은 하나의 회사와 매칭되어야 하며, 하나의 회사는 여러 부서를 가질 수 있다. 즉, 회사와 부서는 1:M의 관계를 가진다. 따라서, 부서 테이블에 CompanyId 컬럼을 만들어준 후, 회사 테이블의 기본키인 CompanyId와 부서 테이블의 외래키인 CompanyId 간의 관계를 설정해준다.
c. 컬럼 추가
: 이후 해당 테이블에 필요한 나머지 컬럼들에 대해 하나씩 생성해 나간다.
d. 값 입력 * 부모 테이블과 자식 테이블 중, 부모 테이블부터 값을 입력해야 한다.
- [ 회사 ] 테이블 값 입력
insert into 회사 values('삼성전자');
select * from 회사;
- [ 부서 ] 테이블 값 입력
select * from 부서
insert into 부서 values(1, '총무부', 1)
-- 비즈니스 로직 내에서, 부서만 먼저 만들어놓고, 회사명을 나중에 입력할 수도 있음. 이러한 경우가 필요하다면 이러한 로직으로 사용 가능.
-- 그러나 위의 예시에서는, CompanyId와 DeptId를 함께 기본키로 지정해놨기 때문에, 아래와 같은 코드는 실행 불가능함
insert into 부서(DeptId,DeptName) values(2, '개발부');
insert into 부서 values(3, '영업부', 1)
- [ 회사 ] 테이블, [ 부서 ] 테이블 Join
--inner join : 두 테이블에 값이 모두 존재하는 경우에만 데이터가 조회된다.
select *
from 회사 a inner join 부서 b
on a.CompanyId = b.CompanyId;
-- right outer join : 오른쪽 테이블의 값은 존재하지 않아도, 모두 조회된다.
select *
from 회사 a right outer join 부서 b
on a.CompanyId = b.CompanyId;
cf ) 참고
-- 자식 테이블들이 존재할 경우, 부모 테이블의 해당 레코드를 삭제할 수 없다.
delete 회사 where companyId=1
2. 1:M 재귀적 관계
1) 1:M 재귀적 관계 사례
- 1:M 재귀적 관계의 대표 예시는, 회사의 부서 / 컴퓨터의 디렉토리 / 분류코드가 존재한다. 이와 같이 자기 자신의 테이블 내에서 관계를 맺어 끊임없이 분개해가는 관계를 1:M 재귀적 관계라고 이야기한다.
a. 회사의 부서
: A기업의 부서 중, 총무부에는 총무 1과 / 총무 2과가 있다. 그런데 총무 1과 내에는 총무 1팀 / 총무 2팀으로 구성되어 있으며, 총무 1팀 내에는 1조 / 2조 / 3조로 나눠져 구성된다.
b. 디렉토리 (Directory)
: 상위 디렉토리와 하위 디렉토리간의 1:M 관계를 지속적으로 확장시켜 나가, 무한대로 서브 디랙토리를 만들어나갈 수 있다. 즉 하위 디렉토리는 다른 하위 디렉토리로부터 상위 디렉토리가 되어, 테이블 간의 관계를 맺게 된다.
c. 분류코드
2) 1:M 재귀적 관계 실습
특정 회사의 부서 내, 여러 세부 과 - 팀이 구성되어있다고 생각하고 진행.
양쪽 테이블이 모두 부서1이지만, 하나는 현재 부서 id, 그리고 나머지 하나는 상위부서id로 1:M 재귀적 관계를 맺게 된다.
* 부서 id는 Primary Key, 상위부서id는 Foriegn Key가 된다.
자기 자신에게 돌아오는 아래와 같은 표시는, 하나의 테이블 안에 PK(Primary Key) / FK(Foriegn Key)가 존재한다는 뜻이며, 부모 자식관계를 나타내는 트리구조를 표현 가능하다는 것이다.
3) 1:M 재귀적 관계 데이터 입력
insert into 부서1 values(1, '총무부', Null); -- 최상위 부서
insert into 부서1 values(2, '총무 1과', 1); -- 상위부서 id가 1(총무부)인, 부서명 총무 1과 : 부서 id는 2번
insert into 부서1 values(3, '총무 2과', 1); -- 상위부서 id가 1(총무부)인, 부서명 총무 2과 : 부서 id는 3번
insert into 부서1 values(4, '총무 1팀', 2); -- 상위부서 id가 2(총무부의 총무 1과)인, 부서명 총무 1팀 : 부서 id는 4번
insert into 부서1 values(5, '총무 2팀', 2); -- 상위부서 id가 2(총무부의 총무 1과)인, 부서명 총무 2팀 : 부서 id는 5번select * from 부서1
select * from 부서1 a join 부서1 b on a.부서id = b.상위부서id -- 좌 : 부모, 우 : 자식 테이블
select * from 부서1 a left outer join 부서1 b on a.부서id = b.상위부서id -- 좌 : 부모, 우 : 자식 테이블
select * from 부서1 a join 부서1 b on a.부서id = b.상위부서id where a.부서id = 2; -- 총무 1과에 대한 정보 (하위 부서명 알아보기)
4) 1:M 재귀적 관계 최종
3. N:M 관계
- N:M 관계는 관계를 가진 양쪽 테이블 모두에서, 1:M 관계가 존재할 때 나타나는 모습이다.
ex. 학생 - 과목 : 학생 입장에서는 여러 과목을 수강할 수 있고, 과목 입장에서는 여러 학생이 이 과목을 선택할 수 있다. 어느 방향으로 봐도 모두 관계가 성립한다.
- 이 관계는 선천적으로는 테이블과 테이블 간의 관계가 없다. 각 테이블은 스스로 존재하며, 이들 사이에 어떤 관계를 맺어 줌으로써 관계가 형성된다.
1) 마스터 테이블들 간의 N:M 관계 ( PK-FK 존재 X )
- 선천적으로 [학생] 테이블과 [과목] 테이블은 관계를 가지지 않는다.
- 이들 간 N:M 관계를 임의로 만들어주면 위와 같은 형태가 된다.
- 위 그림에서는, 각자 테이블에서 Primary Key만 가지고 있지, 서로 테이블 간 Foriegn Key로 연결하고 있지 않다.
2) 마스터 테이블들 간의 N:M 관계 ( PK-FK 존재 O )
- [학생] 테이블과 [과목]테이블 사이에 Foriegn Key로 연결할 컬럼을 추가해, N:M 관계를 직접 설정했을 경우를 나타낸다.
- 이 경우 각자 테이블에서 PK에 대한 중복 문제가 발생하게 된다.
ex1. [학생] 테이블의 1번 학생이, 과목을 2개 이상 듣는다고 가정할 경우, 레코드를 추가하게 되면서, PK인 학생 ID가 중복되게 된다.
ex2. [과목]테이블의 국어 과목을 듣는 학생이 2명 이상이라고 가정하면, 레코드를 추가하는 과정에서, PK인 과목 ID가 중복되게 된다.
3) 해결방안
- N:M 관계를 해결하기 위해, 또 하나의 테이블을 세팅한다.
- DB 설계 요구사항 : 학생이 과목을 수강합니다.
(1) DB 설계자는 요구사항으로부터, 명사와 동사를 추출한다.
a. 명사 : 학생 / 과목
b. 동사 : 수강 * ~하다를 붙였을 때, 말이 되는 것은 동사
(2) 명사는 마스터테이블로서, 동사는 관계 테이블로서 역할을 하게 된다.
- 즉 여기서는, 학생과 과목 테이블 간의 관계를 만들기 위한 비즈니스 로직을, 수강 테이블이 형성하고 있는 것이다.
* 위에서 학생과 과목 테이블이 서로 연관성이 없음에도, 관계를 표현하려고 연결하다보니 중복이 발생한 것이다.
- 일반적으로 마스터 테이블은, 크기 변동이 거의 없다.
ex. 대학에서 학생이 5000명이라고 할 때, 학생 수가 변동되는 경우가 그다지 크지 않고, 거의 비슷하게 유지된다.
- 그러나 관계 테이블은, 일반적으로 크기 변동이 큰 경우 사용한다. 또한 관계 테이블은 대부분 추가적으로 날짜정보가 숨겨져 있다.
ex. 수강 테이블 같은 경우, 2022년 봄학기, 여름학기, 가을학기, 겨울학기, 2023년 .. 등 끝도없이 지속적으로 확장해나갈 수 있으며, 이는 하나의 기록으로 역사하게 되며, 이력서가 된다.
(3) [학생]테이블 - [수강]테이블 , [과목]테이블 - [수강]테이블이, 각각 1:M 관계를 형성한다.
- 마스터 테이블(학생/과목)은 Primary Key만 가지며, 해당 PK와 매치되는 FK는 관계 테이블(수강)에 존재한다.
- 마스터 테이블과 관계 테이블은 각각 1:M의 관계를 형성한다.
- 따라서 [수강] 테이블은 중복으로 작성 가능하다. FK는 참조 무결성 제약조건만 지키면 되지, Unique할 필요까지는 없기 때문이다.
- 결국 [수강] 테이블을 블랙박스화 시켜보고, [학생]과 [과목] 테이블의 관계를 살펴보면 N:M의 관계를 가지게 된다. 따라서 이 관계를 N:M 관계라고 이야기하는 것이다.
| cf ) 테이블 분석 방법 1. 해당 테이블이 마스터 테이블인지 확인한다. 2. 테이블 간, 부모-자식 관계인지를 확인한다. 3. 부모-자식 관계를 가지지 않는다면, M:N의 관계를 가지는지 파악하며, 그 둘을 연결하는 관계 테이블이 무엇인지를 확인한다. |
4) 관계 테이블의 독립형 PK를 사용하는 모델
* DBMS에서는 어떤 테이블이든 PK와 FK를 가지도록 구성해야 한다.
해당 [수강신청] 테이블이 PK를 가지게 되면, 다른 테이블과 N:M의 관계 혹은 1:M의 관계를 맺을 수 있게 된다. 이 경우 수강은 동사가 아닌 명사(마스터 테이블)로서 역할을 하게 된다.
이와 같이 PK인 Seq 컬럼을 따로 생성해 구성하는 관계 테이블 형태는, 중복을 허용하는 비즈니스 로직일 경우 사용하는 모델이다.
5) 관계 테이블의 상속형 PK를 사용하는 모델
위의 [수강] 테이블에서 학생 ID, 과목 ID는 PK면서 동시에 FK의 역할을 한다. 이 둘은 묶어서 PK로 선언되었기 때문에, Unqiue해야 한다. 즉, 만약 3번 학생이 E과목을 다시 수강하고자 들어오면, 중복으로 인한 오류가 발생하게 된다.
이와 같이 PK/FK의 역할을 동시에 하도록 구성하는 관계 테이블 형태는, 중복을 막아야 하는 비즈니스 로직일 경우 사용하는 모델이다.
6) 관계 테이블의 독립형 PK를 사용하는 모델 - 대체키 사용
5)의 사례의 경우, [수강]테이블에서 수강신청 목록을 조회하고자 하면, 아래와 같은 문제가 발생한다.
select *
from 수강
where 학생ID = 1 and 과목ID = 'C'
-- PK가 두개이면 내가 하나의 레코드를 끄집어내려 할 때, PK 레코드를 모두 가져와야 unqiue해짐즉, 관계 테이블에서 PK/FK가 1~2개가 아니라, 10개가 넘어갈 경우, 이 것을 하나씩 모두 where절에 입력해 조회를 수행해야 한다.
이러한 번거로움을 해결하기 위해, 관계 테이블에서는 새로운 컬럼(기존의 PK와 같은 역할)을 생성하여, 후보키(AK = 대체키 = 보조키)로 설정한다. 후보키(AK)는 기본키(PK)는 아니지만, Not Null + Unique하는 키를 의미한다. 즉 PK는 아니지만, PK의 역할을 모두 하는 키를 의미한다.
따라서 후보키(AK)의 사용을 통해, 6)의 사례에서 조회를 할 경우에는, 위의 번거로움을 해결할 수 있다.
select *
from 수강
where seq=5
| * 유의사항 - 아직 공부하고 있는 문과생 코린이가, 정리해서 남겨놓은 정리 및 필기노트입니다. - 정확하지 않거나, 틀린 점이 있을 수 있으니, 유의해서 봐주시면 감사하겠습니다. - 혹시 잘못된 점을 발견하셨다면, 댓글로 친절하게 남겨주시면 감사하겠습니다 :) |
