9. 데이터베이스 설계 - 관계 추출, 논리적 설계 규칙, 테이블 명세서

 

💡 1분 핵심 요약 (TL;DR)

• ✅ 관계 추출 — 요구사항 문장의 동사에서 개체 간 관계를 뽑고, 매핑 카디널리티(1:1·1:n·n:m)와 참여 특성을 결정
• ✅ 논리적 설계 5가지 규칙 — 개체→릴레이션, N:M관계→릴레이션, 1:N관계→외래키, 1:1관계→외래키, 다중값 속성→릴레이션
• ✅ 테이블 명세서 — 릴레이션의 데이터 타입·제약조건까지 세부 문서화한 뒤 CREATE TABLE로 구현

 

지난 시간 데이터베이스 설계 5단계(요구사항 분석 → 개념적 설계 → 논리적 설계 → 물리적 설계 → 구현)의 큰 흐름을 짚어봤다면, 이번엔 그중 실무에서 가장 손이 많이 가는 개념적 설계의 관계 추출과 논리적 설계의 5가지 변환 규칙을 한빛 마트 예제로 끝까지 따라가 보겠습니다. E-R 다이어그램이 실제 테이블(릴레이션)로 바뀌는 전체 과정을 한 번에 정리했습니다.

 

 

 

01 관계 추출 — 개체는 동사로 연결된다

요구사항 문장 속 동사에서 관계를 찾아내는 법

 

📖 관계(Relationship)란?
개체 간의 의미 있는 연관성입니다. 요구사항 문장에서 개체와 개체를 의미 있게 이어주는 동사를 찾아 관계로 추출하며, 같은 의미의 동사가 여러 번 등장하면 대표 동사 하나만 선택합니다.

 

관계를 찾아냈다면 두 가지를 추가로 결정해야 합니다.

 

매핑 카디널리티 · 일대일 (1:1) · 일대다 (1:n) · 다대다 (n:m)

참여 특성 · 필수적 참여 · 선택적 참여

 

 

한빛 마트 요구사항 명세서를 예로 직접 관계를 추출해보겠습니다. "입력해야 한다", "부여된다", "식별한다"는 개체와 개체의 관계를 표현하는 동사가 아니므로 제외됩니다. 반면 아래 문장들은 관계를 담고 있습니다.

 

✓ 예시 1) "회원은 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있다."
→ 관계명 : 주문 ("회원" 개체와 "상품" 개체가 맺는 관계)
→ 관계 유형 : 다대다(n:m)
→ 참여 : 회원 선택적 참여 / 상품 선택적 참여
→ 관계 속성 : 주문번호, 주문수량, 배송지, 주문일자

 

✓ 예시 2) "각 상품은 한 제조업체가 공급하고, 제조업체 하나는 여러 상품을 공급할 수 있다."
→ 관계명 : 공급 ("상품" 개체와 "제조업체" 개체가 맺는 관계)
→ 관계 유형 : 일대다(1:n)
→ 참여 : 상품 필수적 참여 / 제조업체 선택적 참여
→ 관계 속성 : 공급일자, 공급량

 

✓ 예시 3) "회원은 게시글을 여러 개 작성할 수 있고, 게시글 하나는 한 명의 회원만 작성할 수 있다."
→ 관계명 : 작성 ("회원" 개체와 "게시글" 개체가 맺는 관계)
→ 관계 유형 : 일대다(1:n)
→ 참여 : 회원 선택적 참여 / 게시글 필수적 참여
→ 관계 속성 : 없음

 

이렇게 요구사항 명세서 14개 문장을 모두 분석하면, 한빛 마트의 관계는 다음과 같이 정리됩니다.

 

관계	관계에 참여하는 개체	관계 유형	속성
주문	회원(선택), 상품(선택)	다대다	주문번호, 주문수량, 배송지, 주문일자
공급	상품(필수), 제조업체(선택)	일대다	공급일자, 공급량
작성	회원(선택), 게시글(필수)	일대다	-

※ 이 표가 완성되면 개체·속성·관계를 모두 모아 E-R 다이어그램으로 표현하는 개념적 설계가 마무리됩니다.

 

 

 

02 논리적 설계 규칙 ① · ② — 개체와 N:M관계는 릴레이션으로

E-R 다이어그램을 실제 테이블로 바꾸는 첫걸음

 

개념적 설계에서 만든 E-R 다이어그램은 아직 DBMS가 이해할 수 있는 형태가 아닙니다. 논리적 설계는 정해진 변환 규칙에 따라 이 다이어그램을 릴레이션(테이블) 구조로 바꾸는 과정입니다.

 

규칙 1 : 모든 개체는 릴레이션으로 변환

 

📖 변환 방법
· 개체의 이름 → 릴레이션 이름
· 개체의 속성 → 릴레이션의 속성
· 개체의 키 속성 → 릴레이션의 기본키
· 복합 속성은 이를 구성하는 단순 속성만 릴레이션 속성으로 변환

 

릴레이션	속성 (밑줄 = 기본키)
상품	상품번호, 상품명, 재고량, 단가

예) 상품 개체(상품번호, 상품명, 재고량, 단가) → 상품 릴레이션으로 그대로 변환, 상품번호가 기본키가 됩니다.

 

규칙 2 : 다대다(N:M) 관계는 릴레이션으로 변환

 

📖 변환 방법
· 관계의 이름 → 릴레이션 이름
· 관계의 속성 → 릴레이션의 속성
· 관계에 참여하는 두 개체의 기본키를 각각 외래키로 포함시키고, 이 외래키들을 조합해 관계 릴레이션의 기본키로 지정

 

예) 고객 ―n:m― 상품 관계인 '주문'을 변환해보면, 고객 릴레이션의 기본키 고객번호와 상품 릴레이션의 기본키 상품번호가 각각 외래키로 주문 릴레이션에 들어가고, 이 둘을 조합한 (고객번호, 상품번호)가 주문 릴레이션의 기본키가 됩니다.

 

릴레이션	속성
고객	고객번호, 이름, 주소, 등급
상품	상품번호, 상품명, 재고량, 단가
주문	고객번호(FK), 상품번호(FK), 주문수량  — 둘을 조합해 기본키로 지정

 

 

 

03 논리적 설계 규칙 ③ — 1:N 관계는 외래키로

일대다 관계는 릴레이션을 새로 만들지 않고 외래키로만 표현

 

📖 규칙 3-1 : 일반적인 1:N 관계
1측 개체 릴레이션의 기본키를 N측 개체 릴레이션에 포함시켜 외래키로 지정합니다. 관계의 속성들도 N측 릴레이션에 함께 포함시킵니다.

 

제조업체 (1) 제조업체명, 전화번호, 위치, 담당자

— 공급 (n) →

상품 (n) 상품번호, 상품명, 재고량, 단가

 

릴레이션	속성
제조업체	제조업체명, 전화번호, 위치, 담당자
상품	상품번호, 상품명, 재고량, 단가, 제조업체명(FK), 공급수량

1측인 제조업체의 기본키(제조업체명)가 n측인 상품 릴레이션에 외래키로 들어갔습니다.

 

 

⚠️ 규칙 3-2 : 약한 개체가 참여하는 1:N 관계
약한 개체(Weak Entity)는 강한 개체가 없으면 존재할 수 없는 개체입니다. 약한 개체는 자체적인 기본키를 가질 수 없고, 자신을 구별하기 위한 부분키(Partial Key, 식별자/Discriminator)만 가집니다. 따라서 N측 릴레이션은 강한 개체의 기본키를 외래키로 가져와 자신의 부분키와 조합하여 기본키로 지정합니다 — 약한 개체는 강한 개체에 의해 존재 여부가 결정되므로, 강한 개체의 기본키로 식별하는 것입니다.

 

비행기 (1, 강한개체) 비행기번호, 총좌석수, 생산년도

— 존재 (n) →

좌석 (n, 약한개체) 좌석번호, 좌석등급, 이용금액

 

릴레이션	속성
비행기	비행기번호, 총좌석수, 생산년도
좌석	비행기번호(FK), 좌석번호, 좌석등급, 이용금액  — 외래키를 포함해 기본키 지정

좌석번호는 비행기 안에서만 의미가 있는 부분키입니다. 비행기번호(FK)와 이 부분키를 함께 조합해야 좌석 하나를 정확히 식별하는 기본키가 됩니다.

 

 

 

04 논리적 설계 규칙 ④ — 1:1 관계는 외래키로

참여 특성에 따라 세 가지 하위 규칙으로 나뉜다

 

일대일 관계는 양쪽이 관계에 필수적으로 참여하는지에 따라 처리 방식이 달라집니다. '남자'와 '여자'가 '혼인' 관계를 맺는 예시로 세 가지 규칙을 비교해보겠습니다.

 

📖 규칙 4-1 : 둘 다 선택적 참여 → 외래키를 서로 주고받음
관계에 참여하는 두 개체 릴레이션이 서로의 기본키를 주고받아 외래키로 지정합니다. 단, 양쪽 모두에 값을 넣어야 해서 불필요한 데이터 중복이 발생할 수 있습니다.

 

릴레이션	속성
남자	남자번호, 이름, 나이, 여자번호(FK), 결혼날짜
여자	여자번호, 이름, 나이, 남자번호(FK), 결혼날짜

 

⚠️ 규칙 4-2 : 한쪽만 필수적 참여 → 필수 참여 개체만 외래키를 받음
관계에 필수적으로 참여하는 개체 릴레이션에만 외래키를 포함시킵니다. (남자는 혼인에 필수 참여, 여자는 선택적 참여인 경우)

 

릴레이션	속성
남자 (필수 참여)	남자번호, 이름, 나이, 여자번호(FK), 결혼날짜
여자 (선택 참여)	여자번호, 이름, 나이  (외래키 없음)

 

✓ 규칙 4-3 : 둘 다 필수적 참여 → 릴레이션 하나로 합침
두 개체가 관계에 모두 필수적으로 참여한다면 따로 나눌 필요가 없어 하나의 릴레이션으로 합칩니다. 관계 이름을 릴레이션 이름으로 쓰고, 두 개체의 키 속성을 조합해 기본키로 지정합니다.

 

릴레이션	속성
혼인 (통합)	남자번호(FK), 여자번호(FK), 남자이름, 남자나이, 여자이름, 여자나이, 결혼날짜

 

 

 

05 논리적 설계 규칙 ⑤ & 기타 고려사항

다중 값 속성 처리, 그리고 순환 관계

 

📖 규칙 5 : 다중 값 속성은 독립적인 릴레이션으로 변환
하나의 속성이 여러 값을 가질 수 있는 다중 값 속성은 별도의 릴레이션으로 분리해야 합니다. 릴레이션은 하나의 칸에 여러 값을 저장할 수 없기 때문입니다.

 

예) 사원 개체에 '부하직원'이라는 다중 값 속성이 있다고 해봅니다. 부장 홍정화의 부하직원이 김정수, 이수연 두 명이라면 한 칸에 두 값을 넣을 수 없어 아래처럼 별도 릴레이션으로 분리합니다.

 

릴레이션	속성
사원	사원번호, 사원명, 직위
사원-부하직원	사원번호(FK), 부하직원

이렇게 나누면 사원번호·사원명·직위가 불필요하게 중복 저장되는 문제도 함께 해결됩니다. 새로 만들어진 '사원-부하직원' 릴레이션의 기본키는 가져온 외래키(사원번호) + 다중 값 속성(부하직원)을 조합한 복합키로 지정해야 각 튜플을 고유하게 식별할 수 있습니다.

 

📖 기타 고려사항
· 모든 관계는 독립적인 릴레이션으로 변환할 수 있습니다. 속성이 많은 관계라면 유형(1:1, 1:N, N:M)에 상관없이 별도 릴레이션으로 분리하는 것을 고려할 수 있습니다.
· 순환 관계(개체가 자기 자신과 관계를 맺는 경우)도 기본 규칙을 그대로 적용합니다. 예를 들어 '사원'이 '사원'을 관리하는 1:N 관계라면, 사원 릴레이션에 상사번호(FK)를 자기 참조 외래키로 추가합니다.

💡 실무 팁 : 이때 하나의 릴레이션 안에 '사원번호'라는 속성이 중복되면 안 되므로, 외래키로 추가되는 속성의 이름은 역할에 맞게 '상사번호' 또는 '매니저번호' 등으로 변경하여 의미를 명확히 해야 합니다.

 

 

 

06 종합 예제 — 한빛 마트 전체 스키마 변환

규칙 1~5를 순서대로 적용해 완성한 최종 릴레이션

 

앞서 추출한 회원·상품·제조업체·게시글 개체와 주문·공급·작성 관계에 규칙을 차례로 적용하면 아래와 같은 5개의 최종 릴레이션이 완성됩니다. (일대일 관계와 다중 값 속성이 없어 규칙 4·5는 적용하지 않았습니다.)

 

릴레이션	속성 (밑줄 = 기본키, FK = 외래키)
회원	회원아이디, 비밀번호, 이름, 나이, 직업, 등급, 적립금
상품	상품번호, 상품명, 재고량, 단가, 제조업체명(FK), 공급일자, 공급량
제조업체	제조업체명, 전화번호, 위치, 담당자
게시글	글번호, 글제목, 글내용, 작성일자, 회원아이디(FK)
주문	회원아이디(FK), 상품번호(FK), 주문번호, 주문수량, 배송지, 주문일자

· '공급'(1:N) → 상품에 제조업체명 FK 추가 (규칙3)  |  '작성'(1:N) → 게시글에 회원아이디 FK 추가 (규칙3)  |  '주문'(N:M) → 별도 릴레이션 + 복합키 (규칙2)

 

 

 

 

07 테이블 명세서 · 물리적 설계 · 구현

릴레이션이 실제 SQL 테이블로 완성되는 마지막 단계

 

📖 테이블 명세서란?
릴레이션 스키마 변환 후 각 속성의 데이터 타입, 길이, 널(NULL) 값 허용 여부, 기본값, 제약조건 등을 세부적으로 결정해 문서화한 것입니다. 이 문서를 기반으로 실제 CREATE TABLE문을 작성합니다.

 

회원 릴레이션의 테이블 명세서는 다음과 같이 작성할 수 있습니다.

 

속성 이름	데이터 타입	널 허용	기본값	기본키	제약조건
회원아이디	VARCHAR(20)	N	-	PK	-
비밀번호	VARCHAR(20)	N	-	-	-
이름	VARCHAR(10)	N	-	-	-
나이	INT	Y	-	-	0 이상
직업	VARCHAR(20)	Y	-	-	-
등급	VARCHAR(10)	N	silver	-	silver, gold, vip만 허용
적립금	INT	N	0	-	-

 

이 명세서를 그대로 SQL로 옮기면 다음과 같은 CREATE TABLE문이 완성됩니다.

 

회원 테이블 생성 (테이블 명세서 → SQL)

CREATE TABLE 회원 (
    회원아이디    VARCHAR(20)   NOT NULL,
    비밀번호      VARCHAR(20)   NOT NULL,
    이름            VARCHAR(10)   NOT NULL,
    나이            INT,
    직업            VARCHAR(20),
    등급            VARCHAR(10)   NOT NULL  DEFAULT 'silver',
    적립금        INT               NOT NULL  DEFAULT 0,
    PRIMARY KEY(회원아이디),
    CHECK (나이 >= 0),
    CHECK (등급 in ('silver', 'gold', 'vip'))
);

 

▶ 실행 결과

Table 회원 생성 완료
— 나이에 음수를 넣거나, 등급에 'silver'·'gold'·'vip' 외의 값을 넣으면 CHECK 제약조건 위반으로 삽입이 거부됩니다.

 

테이블 명세서까지 완성됐다면, 남은 두 단계는 다음과 같습니다.

 

단계	내용
설계 4단계 : 물리적 설계	하드웨어·운영체제 특성을 고려해 필요한 인덱스 구조나 내부 저장 구조 등 물리적 구조를 설계
설계 5단계 : 구현	SQL로 작성한 명령문을 DBMS에서 실행하여 데이터베이스를 실제로 생성

 

 

📌 핵심 정리

· 관계 추출 : 요구사항 문장의 동사에서 관계를 뽑고, 매핑 카디널리티(1:1·1:n·n:m)와 참여 특성(필수/선택)을 결정
· 규칙 1 : 모든 개체 → 릴레이션 (키 속성이 기본키가 됨)
· 규칙 2 : N:M 관계 → 릴레이션 (양쪽 기본키를 외래키로 조합해 기본키 지정)
· 규칙 3 : 1:N 관계 → 1측 기본키를 N측에 외래키로 (약한 개체는 부분키(Partial Key)와 외래키를 조합해 기본키 지정)
· 규칙 4 : 1:1 관계 → 참여 특성에 따라 외래키를 서로 주고받거나, 필수 참여 쪽만 받거나, 하나로 합침
· 규칙 5 : 다중 값 속성 → 독립적인 릴레이션으로 분리, 기본키는 외래키+다중값 속성을 조합한 복합키
· 순환 관계 실무 팁 : 자기 참조 외래키는 '상사번호'처럼 역할에 맞는 이름으로 바꿔 속성명 중복을 피함
· 테이블 명세서 → 물리적 설계 → 구현 : 데이터 타입·제약조건을 문서화한 뒤 CREATE TABLE로 실제 데이터베이스 생성

 

 

태그 : 관계 추출 매핑 카디널리티E-R 다이어그램 릴레이션 변환논리적 설계 규칙 5가지다대다 관계 릴레이션 변환일대다 관계 외래키약한 개체란일대일 관계 외래키 규칙다중 값 속성 처리테이블 명세서 작성법CREATE TABLE 제약조건