[SQL] ER 다이어그램을 통한 관계 데이터베이스 설계 (RDBMS)
ER 다이어그램을 작성하고 나면,
이를 통해 관계 데이터베이스를 설계해야 한다.
그 방법을 알아볼 것이다.
1. 정규 엔터티 타입의 사상
- ER 다이어그램의 엔티티 타입은 릴레이션으로 매핑한다.
- 모든 단순 애트리뷰트를 포함시킨다.
- 엔티티 타입의 키 중에서 하나를 릴레이션의 기본 키로 설정한다.
우선, ER 다이어그램 상의 엔티티를 확인해보자.
ER 다이어그램의 표기법을 잘 모르는 사람도 있을 수 있기 때문에 사진을 하나 첨부하겠다.
위 사진에서 Entitiy는 테두리가 한 줄인 직사각형 하나로 표현될 수 있다.
여기서 테두리가 한 줄인 직사각형에 속하는 Entitiy는
- EMPLOYEE
- DEPARTMENT
- PROJECT
이 세 가지이다.
위의 세 Entitiy를 릴레이션으로 매핑시킨 결과는 다음과 같다.
- EMPLOYEE
| FNAME | MINIT | LNAME | SSN | BDATE | ADDRESS | SEX | SALARY |
- DEPARTMENT
| DNAME | DNUMBER |
- PROJECT
| PNAME | PNUMBER | PLOCATION |
EMPLOYEE의 복합 애트리뷰트인 Name은
세 단순 애트리뷰트 FNAME, MINIT, LNAME으로 릴레이션에 매핑시켰다.
위의 세 릴레이션의 기본키(Primary Key)는
- EMPLOYEE = SSN
- DEPARTMENT = DNUMBER
- PROJECT = PNUMBER
이다.
2. 약한 엔터티 타입의 사상
- 약한 엔티티 타입을 릴레이션으로 매핑시킨다.
- 모든 단순 애트리뷰틀 포함시킨다.
- 소유 릴레이션의 키 속성을 포함시킨다.
- 생성된 릴레이션의 기본 키는 소유 릴레이션의 키와 약한 엔티티 타입의 부분키를 합쳐서 만든다.
위의 COMPANY database에서 약한 엔티티 타입은 DEPENDENT이다.
약한 엔티티 타입을 릴레이션으로 매핑시키는 규칙에 따라서 릴레이션을 만들면 아래와 같다.
- DEPENDENT
| ESSN | DEPENDENT_ NAME |
SEX | BDATE | RELATIONSHIP |
DEPENDENT Entitiy의 애트리뷰트인
Name, Sex, Birthdate, Relationship을 애트리뷰트로 넣고
소유 릴레이션인 EMPLOYEE의 기본키인 SSN을 애트리뷰트로 넣는다.
위에선 ESSN과 DEPENDENT_NAME에 각각 밑줄이 쳐져있지만
{ESSN, DEPENDENT_NAME}이 한 쌍의 기본키가 된다.
3. 이진 1:1 관계 타입의 사상
1:1 관계 타입을 매핑시키는 방법에 있어서
세 가지 방식이 있다.
- 외래키 접근방식 : 한 릴레이션(S)을 선택하여 다른 릴레이션(T)의 기본키를 S에 외래키로 포함, S는 완전참여 릴레이션을 선택하는 것이 좋다. 관계타입의 모든 단순 애트리뷰트를 S에 포함시킨다.
- 합병된 릴레이션 접근방식 : 두 릴레이션을 하나의 릴레이션으로 통합, 두 릴레이션이 모두 완전참여일 때 좋은 방법
- 교차참조 / 관계 릴레이션 접근방식 : S와 T를 교차 참조하는 제3의 릴레이션 R 생성
세 가지 방식이 있지만 1번 방식이 가장 유용하다고 한다.
따라서 1번 방식을 알아보겠다.
COMPANY database에서 1:1 관계는
EMPLOYEE - DEPARTMENT 관계가 있다.
가운데 마름모인 MANAGES를 확인해보면
양 옆에 1이 써있기 때문에 1:1 관계이며
자세히보면, EMPLOYEE에는 선이 한 개 DEPARTMENT에는 선이 두 개가 있다.
위의 ER 다이어그램 표기법을 보면 알겠지만
선이 두 개인 DEPARTMENT는 모든 튜플이 참여한다는 의미이고
선이 한 개인 EMPLOYEE는 부분적으로 참여한다는 의미이다.
위 규칙에 따라
완전참여하는 DEPARTMENT의 애트리뷰트에
부분참여하는 EMPLOYEE의 기본키인 SSN을 넣는다.
- DEPARTMENT
| DNAME | DNUMBER | MGRSSN | MGRSTARTDATE |
MGRSTARTDATE는 관계인 MANAGES의 애트리뷰트이며
기본키는 DNUMBER
MGRSSN은 EMPLOYEE의 기본키인 SSN의 이름을 변형시켜준 것이며, EMPLOYEE를 참조하는 외래키로 쓰인다.
4. 이진 1:N 관계 타입의 사상
이진 1:N 관계 타입의 릴레이션 매핑도 더 많은 방식이 있지만 권장하는 방식은 이 방식이다.
- 외래키 접근방식 : (관계)N측의 릴레이션(S)을 선택하여 (관계)1측의 릴레이션(T)의 기본키를 S에 외래키로 포함, 관계타입의 모든 단순 애트리뷰트를 S에 포함시킨다.
관계가 N인 릴레이션 S를 선택하여
관계가 1인 릴레이션 T의 기본키를
S에 외래키로 포함시킨다.
COMPANY database에서 1:N 관계는
- EMPLOYEE - <WORKS_FOR> - DEPARTMENT
- EMPLOYEE - <<DEPENDENT_OF>> - DEPENDENT
- DEPARTMENT - <CONTROLS> - PROJECT
- EMPLOYEE - <SUPERVISION> - EMPLOYEE
총 4가지가 있으며
위 규칙대로 릴레이션을 매핑시키면
- EMPLOYEE
| FNAME | MINIT | LNAME | SSN | BDATE | ADDRESS | SEX | SALARY | SUPERSSN | DNO |
- PROJECT
| PNAME | PNUMBER | PLOCATION | DNUM |
릴레이션 EMPLOYEE에
자기 자신과의 관계인 SUPERVISION이 있기 때문에 EMPLOYEE의 기본키인 SSN이 SUPERSSN으로
DEPARTMENT와의 관계인 WORKS_FOR이 있기 때문에 DEPARTMENT의 기본키인 NUMBER가 DNO로
애트리튜브에 추가되었고
PROJECT에
DEPARTMENT와의 관계인 CONTROLS가 있기 때문에 기본키인 NUMBER가 DNUM으로 들어간다.
EMPLOYEE와 DEPENDET와의 관계인 DEPENDENTS_OF가 있지만
소유 관계라 별도로 추가하지 않는다.
5. 이진 M:N 관계 타입의 사상
M:N 이진 관계는 별도의 릴레이션(이를 관계 릴레이션이라고 부름)으로 생성하고,
관계에 참여하는 두 릴레이션의 기본키를 각각 참조하는 외래키로 애트리뷰트를 구성한다.
이 때 두 외래키가 관계 릴레이션의 기본키르 형성한다.
EMPLOYEE - <WORKS_ON> - PROJECT
의 관계는 M:N 관계이다.
두 릴레이션 EMPLOYEE와 PROJECT의 기본키인 SSN과 NAME
그리고 WORKS_ON의 애트리뷰트인 HOURS를 가지고 릴레이션을 구성한다.
- WORKS_ON
| ESSN | PNO | HOURS |
ESSN과 PNO는 각각 외래키로
ESSN은 EMPLOYEE를
PNO는 PROJECT를 참조한다.
그리고 외래키의 쌍 {ESSN, PNO}는 기본키가 된다.
6. 다치 애트리뷰트의 사상
- 릴레이션 R의 다치 애트리뷰트는 R의 기본키를 포함하는 새로운 릴레이션으로 매핑된다.
- 새로운 릴레이션의 키는 R의 기본키와 다치 애트리뷰트의 조합이다.
릴레이션 DEPARTMENT에는 다치 애트리뷰트인 Locations 가 존재한다.
RDBMS의 제1 정규형을 만족시키기 위해서는
모든 값이 원자값이 되어야 하는데 다치 애트리뷰트는 원자값을 만족시키지 못하므로
이를 릴레이션으로 분해하여 준다.
- DEPT_LOACTION
| DNUMBER | DLOCATION |
DEPARTMENT의 기본키인 NUMBER와 Locations가 릴레이션을 이룬다.
7. N차 관계 타입의 사상
- n차 관계는 관계에 참여하는 n개 릴레이션의 키들로 구성되는 관계 릴레이션으로 매핑된다. 관계 릴레이션의 애트리뷰트들은 참여 릴레이션의 기본키를 참조하는 외래키들과 관계 속성(들)으로 구성된다.
