[데이터베이스]관계형 데이터베이스의 ERD

이전 글에서 봤듯이, SQL은 구조화된(Structured) 테이블을 사용하는

 

관계형 데이터베이스(RDB -Relational Database)에서 주로 쓰인다.

 

관련 글: https://gnidinger.tistory.com/444

[데이터베이스]SQL

사전에 정의된, 구조화된 테이블을 Relation이라고도 부르기 때문에

 

테이블을 사용하는 데이터베이스를 관계형 데이터베이스라고 부르는 것이다.

 

혹은, 엔티티끼리 관계를 맺는 형태로 데이터베이스가 구성되고 있기 때문이기도 하다.

 

이전 글에서 살펴봤던, 관계형 데이터베이스에서 알고 있어야 할 키워드에 대해 다시 짚고 넘어가자.

 

이름	내용
데이터(Data)	각 항목에 저장되는 값
테이블(Table, Relation)	자료의 구조를 2차원 표로 나타낸 것
컬럼(Column, Field, Attribute)	테이블의 각 열, 데이터 값. 속성이라고도 불린다.
도메인(Domain)	한 속성이 취할 수 있는 (동일한 유형, 동일한 범위의)모든 값
레코드(Record, Row, Tuple)	테이블의 각 행, 단일 구조 데이터 항목. 모든 레코드는 동일한 구조
릴레이션 인스턴스(Relation Instance)	테이블에 저장된 튜플의 집합
키(Key)	레코드마다 가지는 고유한 값. 기본키(Primary Key)와 외래키(Foreign Key) 등이 있다.
스키마(Schema)	테이블의 구조나 표현 방법, 테이블 간의 관계를 정의해놓은 논리적인 틀
엔티티(Entity)	유, 무형의 대상을 가리키는 데이터의 집합 혹은 개체. 저장, 관리되어야 함 유일한 식별자, 2개 이상의 인스턴스, 속성이 반드시 있어야 함 다른 엔티티와 최소 한 개 이상의 관계(Relationship)을 가져야 함 유일한 이름을 가져야 하며, 가능하면 약어가 아닌 단수 명사를 사용해야 함

 

엔티티 간의 관계를 나타낸 다이어그램을 ERD(Entity Relationship Diagram)라고 부르며,

 

크게 세 종류의 관계가 있다.

 

• 1:1 관계
• 1:N 관계
• N:M 관계

엔티티 간의 연결은 기본키(Primary Key)와 외래키(Foreign Key) 사이의 연결로 이루어지는데,

 

기본키와 외래키 설정은 데이터의 무결성을 보장할 수 있는 일종의 제약조건이다.

 

여기서 먼저 기본키와 외래키에 대해 알아보고 가자.

 

• 기본키(Primary Key)
  
  
  • 테이블 안에서 특정 레코드를 찾는데 이용
  • 설계자가 정의한 식별자(유일성, 최소성을 만족하는 조합) - 데이터의 중복 방지
  • 기본키가 정의되면 데이터베이스 엔진은 인덱스 생성 - 검색 속도 향상
  • 기본키의 모든 필드의 값은 Null이 될 수 없음
  • 주민등록번호를 예로 들 수 있음

위에 설명한 것처럼 기본키는 하나의 필드일 수도, 여러 필드의 조합일 수도 있다.

 

예를 들어 아래와 같은 테이블이 있을 때,

 

CustomerID	FirstName	LastName	OrderNumber
001	Coding	Kim	6238240
002	Hacker	Park	5974262
003	Latte	Cafe	6642878

 

각 필드의 속성(CustomerID, FirstName, LastName, OrderNumber) 각각은 기본키가 될 수 있으며,

 

그 조합도 기본키가 될 수 있다.

 

하지만 유일성과 최소성을 만족하는 조합을 찾을 때 CustomerID 필드가 가장 직관적이므로

 

보통 CustomerID 필드를 기본 키로 지정한다.

 

하지만 테이블이 아래와 같이 정의된다면,

 

ProductID	ClientID	AverageOrder	Price	Cost
1	1	100	53.00	32.00
3	29	127	67.00	46.00
56	6	100	44.00	30.00
174	8	100	38.00	25.00
174	29	127	131.00	89.00

 

ProductID나 ClientID 하나로는 기본키를 만들 수가 없다.

 

이럴 땐 ProductID와 ClientID를 조합해 기본키로 설정하게 된다.

 

하지만 하나의 테이블에 여러 기본키가 존재할 수는 없는데,

 

기본 키는 여러 필드로 구성될 수 있으나, 기본키가 여러 개일수는 없다는 뜻이다.

 

• 외래키(Foreign Key)
  
  
  • 다른 테이블의 기본키, 고유키를 그대로 참조하는 역할, 테이블 사이의 관계(JOIN) 결정
  • 외래키의 모든 필드는 참조할 기본키와 동일한 도메인을 가져야 한다.
  • 중복을 허용하지 않지만 (고유키를 참조하는 경우) Null 값을 허용한다.

예를 들어 아래와 같은 두 테이블이 있다고 하자.

 

CustomerID	FirstName	LastName	OrderNumber
001	Coding	Kim	6238240
002	Hacker	Park	5974262
003	Latte	Cafe	6642878

OrderID	CustomerID	Amount	Price	Cost
001	001	47	17.00	12.00
002	002	65	39.00	22.50
003	002	33	27.00	20.00
004	003	29	53.00	37.50

 

첫 번째 테이블의 기본키(CustomerID)를 두 번째 테이블의 외래키가 가지고 있음을 확인할 수 있다.

 

이때 두 번째 테이블의 외래키로 첫 번째 테이블의 기본키를 참조하면 관계가 생긴다고 한다.

 

추가로 이후의 ERD는 새발 표기법, 혹은 까마귀 발 표기법(Crow's Foot Notation)으로 나타내며

 

그 간략한 설명은 아래와 같다.

 

구체적인 의미는 아래 예시를 보면 직관적으로 파악이 가능할 것이다.

 

계속해서 엔티티 간의 세 가지 관계를 살펴보자.

 

• 1:1 관계(One-to-One Relationship)

개체 집합 A의 원소가 개체 집합 B의 원소 하나와 대응하는 관계를 말한다.

 

위에서 본 테이블을 다시 가져와 Customer 테이블이라 부르자.

 

CustomerID	FirstName	LastName	OrderNumber
001	Coding	Kim	6238240
002	Hacker	Park	5974262
003	Latte	Cafe	6642878

                                            <Customer Table>

 

이어서 회원의 주민등록번호가 저장된 테이블 Social을 만들면 아래와 같다.

 

SocialNumber	CustomerID
111111-1111111	001
222222-2222222	002
333333-3333333	003

                                                <Social Table>

 

Customer 테이블의 기본키인 CustomerID를 Social 테이블의 외래키가 가지고 있으며,

 

그 반대도 성립함을 알 수 있다.

 

 

[Customer Table] : [Social Table] = 1 : 1

 

1:1 관계는 잘 사용되지 않는데, 1:1로 나타낼 수 있는 관계라면 Customer 테이블에

 

CustomerID를 대신해 SocialNumber를 직접 입력하는 게 나을 수도 있기 때문이다.

 

1:1 관계는 대형 테이블 분할 및 보안 목적으로 두 테이블을 분리한 경우에 사용된다.

 

• 1:N 관계(One-to-Many Relationship)

개체 집합 A의 원소가 개체 집합 B의 원소 여러 개와 대응하는 관계를 말하며, 가장 많이 사용되는 관계이다.

 

다음과 같은 Rank 테이블과 Employee 테이블이 있다고 하자. 

 

RankID	RankName
01	Senior Director
02	Manager
03	Engineer
04	Staff

                             <Rank Table>

 

EmployeeID	Name	RankID
01	Kim	01
02	Lee	02
03	Park	03
04	Choi	03
05	Jung	04
06	Kang	04
07	Cho	04

                         <Employee Table>

 

하나의 RankID에 해당하는 EmployeeID는 여러 개지만, 한 명의 EmployeeID는 하나의 RankID만 가질 수 있다.

 

[Rank Table] : [Employee Table] = 1 : N

 

• N:M 관계(Many-to-Many Relationship)

개체 집합 A의 원소가 B의 원소 여러 개와 대응할 수 있고, B의 원소가 A의 원소 여러 개와 대응할 수 있는 관계를 말한다.

 

N:M 관계는 두 개의 1:N 관계로 나타내는데, 이때 사용되는 테이블을 JOIN 테이블이라고 한다.

 

다음과 같은 Employee 테이블과 Hobby 테이블이 있다고 하자.

 

EmployeeID	Name
01	Kim
02	Lee
03	Park
04	Choi
05	Jung
06	Kang
07	Cho

                         <Employee Table>

 

HobbyID	Hobby
01	독서
02	농구
03	그림
04	요리

                             <Hobby Table>

 

보통 한 명의 사람이 여러 개의 취미를 가질 수 있으며, 여러 사람이 같은 취미를 가질 수도 있다.

 

위 두 테이블을 바탕으로 만든 JOIN 테이블은 아래와 같이 될 수 있다.

 

JOINID	HobbyID	EmployeeID	Hobby	Name
A	01	01	독서	Kim
B	01	02	독서	Lee
C	01	05	독서	Jung
D	02	06	농구	Kang
E	02	04	농구	Choi
F	02	03	농구	Park
G	03	02	그림	Lee
H	03	06	그림	Kang
I	04	03	요리	Park
J	04	04	요리	Choi

                                                        <JOIN Table>

 

이를 바탕으로 ERD를 작성하면 아래와 같이 된다.

 

[Employee Table] : [Hobby Table] = N : M

 

 

• 추가 - 자기 참조 관계(Self Referencing Relationship)

같은 테이블 내에서도 관계가 필요할 때가 있다. 예를 들면 회원가입 시 추천인을 파악할 때 쓰인다.

 

한 명의 유저(user_id)는 한 명의 추천인(recommend_id)을 가질 수 있으며,

 

여러 명이 한 명의 유저를 추천인으로 등록할 수 있다.