[데이터베이스 설계] E-R 모델(관계 표현과 스키마 변환)

1. 카디널리티 제약조건의 표현

－ E-R 다이어그램에서 엔티티 간의 관계에 참여하는 엔티티의 수를 표현하는 것을 '카디널리티'라고 함

 

표기법:

• 'One' (1): 관계 다이아몬드에서 엔티티 쪽으로 **화살표가 있는 선(→)**으로 표현합니다.
• 'Many' (N): **화살표가 없는 일반 실선(-)**으로 표현합니다.
• one인 방향 쪽에 화살표를 그리고, many에서는 화살표를 그리지 않음

관계 종류별 표현:

 

• 일대일 (One-to-One):
  - 한 학생이 최대 한 명의 지도교수를, 한 교수도 최대 한 명의 학생을 담당하는 관계
  - 양쪽 엔티티 방향으로 모두 화살표가 있음

• 일대다 (One-to-Many):
  - 한 명의 교수가 여러 명(0명 포함)의 학생을 지도할 수 있지만, 학생은 최대 한 명의 교수에게만 지도받는 관계
  - 여러 명의 student가 하나의 instructor와 연결되어 있음
  - 아무 학생과도 연결되지 않은 교수가 있을 수 있음

• 다대일 (Many-to-One):
  - 여러 명의 학생이 한 명의 교수에게 지도를 받는 관계
  - 'One'에 해당하는 student 엔티티 쪽으로 화살표가 그려짐
   
• 다대다 (Many-to-Many):
  - 한 명의 교수가 여러 학생을 지도하고, 한 명의 학생도 여러 교수에게 지도받을 수 있는 관계
  - 양쪽 모두 'Many'이므로 화살표가 없음
  - instructor와 student 모두 0이 가능

---

2. 관계 참여도: 전체 참여와 부분 참여

전체 참여 (Total Participation):

• 해당 엔티티 셋의 모든 엔티티가 관계에 반드시 참여해야 함을 의미
• E-R 다이어그램에서 **이중선(double line)**으로 표현
• 예: 모든 학생은 반드시 지도교수가 있어야 한다면(advisor 관계에 전체 참여), student와 advisor 사이를 이중선으로 연결

부분 참여 (Partial Participation):

• 엔티티 셋의 일부 엔티티만 관계에 참여할 수 있음을 의미
• 예: 교수가 학생을 한 명도 지도하지 않을 수 있다면(advisor 관계에 부분 참여), instructor와 advisor 사이는 일반 실선으로 연결

최소/최대 참여 표현 (l..h):

• 관계선 위에 *는 제한 없음을 의미
• 최소값..최대값 형태로 참여하는 엔티티 수를 더 명확하게 표현할 수 있음
• 예: instructor (0..*) -- advisor -- (1..1) student교수는 0명 이상의 학생을 지도할 수 있음(부분 참여).
• 학생은 반드시 1명의 지도교수를 가져야 합니다 (전체 참여).
• student 입장에서는 instructor와 1:1로 연결되어야 하며, 1..1은 최소 1명, 최대 1명을 의미

---

3. 관계의 기본 키(Primary Key) 설정 

- 관계 셋의 기본 키는 관계에 참여하는 엔티티들의 기본 키 조합으로 결정

 

- 두 엔티티와 관계에서의 PK를 정하는 방법: 두 엔티티의 PK를 UNION 한 후 관계에 해당하는 속성을 포함

 

• 다대다 (Many-to-Many) 관계:
  • 참여하는 양쪽 엔티티의 기본 키를 합친(UNION) 것이 관계 셋의 기본 키가 됨
  • 양 엔티티의 PK를 UNION 한 것이 그 관계의 PK이자 슈퍼키
• 다대일 (Many-to-One) / 일대다 (One-to-Many) 관계:
  
  • 'Many' 쪽 엔티티의 기본 키만으로 관계 셋의 기본 키가 됩니다
  • 'Many' 쪽의 PK가 관계의 최소 슈퍼키이자 PK입니다. 왜냐하면 'Many' 쪽의 엔티티는 오직 하나의 'One' 쪽 엔티티와만 관계를 맺음
• 일대일 (One-to-One) 관계:
  • 참여하는 두 엔티티 중 어느 쪽의 기본 키를 사용해도 관계 셋의 기본 키가 될 수 있음

---

4. 약한 엔티티 셋 (Weak Entity Set)

- 약한 엔티티 셋은 스스로의 속성만으로는 고유하게 식별될 수 없는 개체

• 정의:
  - 자신의 속성만으로는 고유하게 식별될 수 없음
  - 다른 엔티티(강한 엔티티, Identifying Entity)의 존재에 의존하는 엔티티 셋
• 예시:
  - section(분반)은 course(강의) 없이는 고유하게 식별될 수 없음
  - '분반 ID 1'은 'CS101의 분반 1'인지 'EE200의 분반 1'인지
  - course 정보가 있어야만 특정 가능
• 식별자 (Discriminator): 약한 엔티티 셋 내에서 개체를 구분하는 속성들의 집합
• E-R 다이어그램 표기법:
  
  • 약한 엔티티 셋: 이중 사각형.
  • 식별 관계 (Identifying Relationship): 이중 마름모.
  • 식별자 속성: 점선 밑줄.
    - 예: section의 PK = course_id (강한 엔티티 course의 PK) + (sec_id, semester, year) (식별자).
• 기본 키: 약한 엔티티 셋의 기본 키는 강한 엔티티의 기본 키 + 약한 엔티티의 식별자로 구성

---

5. 중복 속성 제거 

- 엔티티 셋의 속성이 관계를 통해 이미 표현될 수 있는 정보를 포함하고 있다면 해당 속성은 중복되므로 제거

• 예시: student 엔티티에 dept_name 속성이 있고, department 엔티티와 stud_dept라는 관계로도 연결되어 있다면, student의 dept_name 속성은 중복
• 이 경우 dept_name이 중복되므로, student 엔티티에서 dept_name은 없어도 됨
• 대신, stud_dept 관계 속성 안에 student의 studentId와 dept_name이 있어야 함
• many(student)에 one(department)의 PK를 추가

---

6. E-R 다이어그램을 관계형 스키마로 변환하기 

- E-R 다이어그램의 각 요소를 데이터베이스 테이블(스키마)로 변환하는 규칙

강한 엔티티 셋 (Strong Entity Set):

• 엔티티 셋의 이름으로 테이블을 만들고, 엔티티의 속성들을 컬럼으로 변환
• 예: student(ID, name, tot_cred).

약한 엔티티 셋 (Weak Entity Set):

• 자신의 속성들과 강한 엔티티 셋의 기본 키를 포함하는 테이블을 생성
• 약한 엔티티 셋은 그것과 연결된 강한 엔티티 셋의 PK를 컬럼으로 포함
• 예: section(course_id, sec_id, semester, year).

복합 속성 (Composite Attributes):

• 각 구성 요소를 별도의 컬럼으로 만듬(Flattening).
• 예: name(first_name, last_name) -> first_name, last_name 두 개의 컬럼으로 변환.

다중값 속성 (Multivalued Attributes):

• 해당 속성을 위해 별도의 테이블을 생성
• 이 테이블은 원본 엔티티의 기본 키와 다중값 속성 컬럼을 가짐
• 다중값 속성은 제거되고, 이를 위한 별도의 릴레이션(테이블)을 만들어야 함
• 예: instructor의 phone_number -> inst_phone(ID, phone_number) 테이블 생성

 

관계 셋 (Relationship Sets):

- Many-to-Many의 경우, 2개 엔티티와 1개 관계에 대해 총 3개의 테이블이 만들어짐

• 다대다 관계: 관계에 참여하는 엔티티들의 기본 키들을 포함하는 새로운 테이블을 생성

• 다대일 / 일대다 관계:
  • 별도의 테이블을 만들지 않고, 'Many' 쪽 엔티티 테이블에 'One' 쪽 엔티티의 기본 키를 외부 키(Foreign Key)로 추가
  •  'Many' 쪽이 관계에 전체 참여(Total Participation)일 때 효과적
  • Many-to-One의 경우 2개의 테이블만 만들어짐
  • 'Many' 쪽에 'One'의 PK를 넣어함
  • 만약 'Many' 쪽의 참여가 부분 참여(Partial)라면, 추가된 외부 키 컬럼에 NULL을 허용하지 않는다면 별도의 테이블을 만들어야 함
  • NULL 값이 허용될 수 있음

• 약한 엔티티 식별 관계:
  • 이 관계를 위한 별도의 테이블은 만들지 않음
  • 약한 엔티티의 테이블을 만들 때 이미 강한 엔티티의 기본 키가 포함되었기 때문에 중복이기 때문