Database 10일차

[ 관계 데이터 모델 ]

 

관계 데이터 모델(relational data model)
- DB를 2차원 테이블(table), 즉 릴레이션(relation)의 집합으로 모델링하는 논리적 데이터 구조
- 1970년 IBM의 코드(E. F. Codd) 박사가 처음 제안
예) 사원 개체를 표현한 사원 릴레이션

 

관계 데이터 모델의 특징
① 개체와 관계가 모두 2차원 테이블, 즉 릴레이션으로 표현됨
② ER 모델을 관계 데이터 모델로 변형하는 것이 매우 쉬움

릴레이션과 테이블의 차이점
① 릴레이션은 다소 추상적인 개념(abstract concept)이고, 테이블은 릴레이션을 표현하는 구체적인 표현 방법
② 하나의 릴레이션은 여러 가지 형태의 테이블로 표현될 수 있음
③ 테이블은 중복되는 행을 허용하지만, 릴레이션은 수학의 집합 개념을 기초로 하므로 중복되는 행을 허용하지 않음

---

[ 릴레이션의 개념과 특징 ]

릴레이션(relation)이란?
- 행(row)과 열(column)로 구성된 2차원 테이블 구조로서,  릴레이션 스키마와 릴레이션 인스턴스가 합쳐진 개념

 

릴레이션 스키마(relation schema)란?

- 릴레이션을 구성하는 속성들의 집합을 의미함
- 시간이 흘러도 변하지 않는 정적인 성질을 가짐 (= 릴레이션 타입)
- 릴레이션 내포(intension) 또는 릴레이션 스킴(scheme)이라고도 부름

 

릴레이션 스키마 표기법

- 릴레이션 R이 n개의 도메인(D1, D2, …, Dn)에 대해 정의될 때, 릴레이션 스키마는
릴레이션 이름 R과 속성들 A1, A2, …, An의 집합으로 구성되며, 
편의상 R(A1, A2, …, An)으로 표기

 

릴레이션 스키마 정의 방법

① 도메인을 정의한 다음 정의된 도메인을 이용해서 정의하는 방법
② 도메인 정의를 생략하고 속성 이름과 함께 도메인을 직접 기술하는 방법
예) 사원(worker) 릴레이션과 도메인

---

[ 릴레이션 인스턴스 ]

 릴레이션 인스턴스(relation instance)란?

- 릴레이션을 구성하는 투플들의 집합으로, 릴레이션의 내용에 해당함
- 즉, 어느 한 시점에 릴레이션 R이 포함하고 있는 투플들의 집합

- 삽입, 삭제, 갱신 등으로 인해 시간이 지나면 변경될 수 있는 동적인 성질을 가짐
- 릴레이션 외연(extension)이라고도 부름
- 그냥 릴레이션이라고 하면, 릴레이션 인스턴스를 의미함

---

[ 릴레이션 R의 정의 ]

릴레이션 R의 수학적 정의

- n개 도메인 D1, D2, ..., Dn(일부 도메인은 같을 수도 있음)에 대한 카티션 프로덕트(cartesian product) 의 부분집합

예) 사원 릴레이션에서 사번과 부서번호이라는 2개 속성만 경우

---

[ 릴레이션의 특징 ]

① 투플의 유일성(uniqueness of tuples)
- 하나의 릴레이션에 포함된 투플들은 모두 서로 달라야 함
- 즉, 중복된 투플이 존재하지 않는 투플들의 집합(set)

   > 릴레이션에는 항상 투플을 유일하게 식별할 수 있는 키(key)가 존재해야 함
② 투플의 무순서성(no ordering of tuples)
- 하나의 릴레이션에 포함된 투플들 간의 순서에는 아무런 의미가 없음
③ 속성의 무순서성(no ordering of attributes)
- 하나의 릴레이션을 구성하는 속성들의 순서에는 아무런 의미가 없음

④ 속성의 유일성(uniqueness of attributes)
- 하나의 릴레이션에서 이름이 같은 속성은 존재할 수 없음
⑤ 속성의 원자성(atomicity of attributes)
- 릴레이션 인스턴스의 모든 속성은 반드시 원자 값(atomic value)만 가져야 함

   > 원자 값은 논리적으로 더 이상 분리될 수 없으며, 여러 타입의 값을 갖는 속성은 직접 표현할 수 없음
- 원시적 데이터 타입(수치형, 문자형, 날짜형, 논리형)의 값만 표현 가능
- 널(null) 값은 원자 값으로 간주함

---

[ 관계 데이터 베이스 ]

관계 DB(relational database)란? 
- 관계 데이터 모델을 기반으로 하는 DB
- 릴레이션들의 집합으로 구성됨
- 관계 DB 스키마에 의해 정의된 릴레이션 인스턴스들의 집합
관계 DB 스키마(relational database schema)
- 하나 이상의 릴레이션 스키마와 무결성 제약조건으로 정의됨
   관계 DB 스키마 = {릴레이션 스키마} + {무결성 제약조건}

   > 무결성 제약조건: 데이터의 정확성과 일관성 유지를 위해 DB가 항상 만족시켜야 하는 조건

 

[ 데이터베이스 키 ]

[ 데이터베이스 키 - 키의 개념과 종류]

키(key)란? 
- 릴레이션을 구성하는 각 투플을 데이터 값에 의해 유일하게 식별할 수 있는 속성 또는 속성들의 집합

 

키의 종류

- 후보키(candidate key): 유일성과 최소성을 모두 만족하는 속성 집합이다.
- 슈퍼키(super key): 유일성은 만족하지만 최소성은 만족하지 않는 속성 집합이다.
- 기본키(primary key): 후보키 가운데 업무에 적합하게 선택된 하나의 키로, 

   DB 설계자가 지정한다. 널 값을 가질 수 없고 중복되지 않는 유효한 값을 가져야 한다.

   단일 속성으로 유일성이 만족되지 않을 경우 2개 이상의 속성으로 구성한 기본키를 복합키(composite key)라고 부른다.
- 대체키(alternative key): 기본키를 제외한 나머지 후보키들이다.
- 외래키(foreign key): 릴레이션 간의 관계를 정의하는 키로서, 

   하위 릴레이션(참조 릴레이션)에서 상위 릴레이션(피참조 릴레이션)의 기본키를 외래키로 포함한다.

 

 

---

[ 후보키 ]

후보키(candidate key)란? 
- 릴레이션 R(A1, A2, ..., An)의 전체 속성 집합 A의 부분집합이면서, 
유일성과 최소성을 모두 만족하는 속성 집합
- 만약 릴레이션 R의 한 속성 집합인 K({Ai , Aj , …, Ak})가 전체 속성 집합 A의 부분집합이면서
다음과 같은 두 가지 성질을 만족하면, K는 릴레이션 R의 후보키가 될 수 있음
① 유일성 : K는 각 투플을 유일하게 식별할 수 있어야 함
② 최소성: K는 각 투플을 유일하게 식별하는 데 필요한 최소의 속성만 포함해야 함
(K가 둘 이상의 속성으로 구성된 경우, 그 가운데 어떤 속성을 제거하면 각 투플을 유일하게 식별할 수 없어야 함)

 

---

[ 슈퍼키 ]

슈퍼키(super key)란? 
- 유일성은 만족하지만 최소성이 만족되지 않는 속성 집합

예) 학생 릴레이션의 수퍼키 : {학번, 학과}, {학번, 지도교수}, {휴대폰, 이름}

---

[ 기본키 ]

- 후보키 가운데 DB 설계자가 지정한 하나의 후보키 (나머지는 대체키)
- 릴레이션에 포함된 모든 투플을 유일하게 식별해야 하므로 널 값을 가질 수 없고,  항상 유효한 값을 가져야 함

---

[ 대체키 ]

대체키(alternative key)란? 
- 후보키가 여러 개인 경우 기본키로 선택된 후보키를 제외한 나머지 후보키
- 기본키가 널 값을 가질 수 없는 반면, 대체키는 널 값을 가질 수 있음

---

[ 외래키 ]

외래키(foreign key)란? 
- 참조 릴레이션 R의 어떤 속성 집합 FK가 피참조 릴레이션 S의 기본키일 때, 
이 FK를 참조 릴레이션 R의 외래키라고 함
예) 부서 릴레이션에서 ‘부서장’ 속성

 

외래키의 특징
① 외래키를 포함하는 릴레이션 R을 참조 릴레이션(referencing relation), 외래키가 참조하는
   기본키를 포함하는 릴레이션 S를 피참조 릴레이션(referenced relation)이라고 함
   > 릴레이션 R은 외래키를 통해 릴레이션 S를 참조함
② 외래키의 도메인은 피참조 릴레이션 S의 기본키의 도메인과 같음
③ 외래키의 값은 피참조 릴레이션 S에 존재하는 값 또는 널 값을 가질 수 있음
④ 외래키는 하나 이상의 릴레이션을 서로 연결해서 사용할 때 필요한 키임
⑤ 참조 릴레이션 R과 피참조 릴레이션 S가 동일한 릴레이션인 경우도 있음

 

외래키의 3가지 유형
① 외래키가 다른 릴레이션의 기본키를 참조하는 경우

② 외래키가 동일한 릴레이션의 기본키를 참조하는 경우

③ 외래키가 기본키의 구성 요소가 되는 경우

 

---

[ 무결성 제약조건 ]

무결성 제약조건(integrity constraints)이란? 
- DB에 저장된 데이터의 정확성과 일관성 유지를 위해서 DB가 항상 만족해야 하는 조건을 의미함
- 묵시적 또는 명시적으로 정의할 수 있음

---

[ 무결성 제약 조건 구분 ]

① 개체 무결성(entity integrity) 제약조건
- 기본키는 투플들을 유일하게 식별하기 위해서 널 값을 가질 수 없다는 제약조건
② 참조 무결성(referential integrity) 제약조건
- 외래키는 반드시 피참조 릴레이션의 기본키 값이나 널 값을 가져야 한다는 제약조건
③ 도메인 무결성(domain integrity) 제약조건
- 각 속성은 반드시 미리 정해진 도메인에 존재하는 값을 가져야 한다는 제약조건
(데이터 타입과 크기, 널 값 허용 여부, 고유 값 여부, 디폴트 값, 속성 값이 만족해야 하는 특정 조건 등을 만족하는 값을 가져야 함)

---

[ 무결성 제약조건 정의 방법 ]

① 묵시적 정의 방법
- 릴레이션을 정의할 때 기본키와 외래키를 정의하면, 개체 무결성과 참조 무결성이 묵시적으로 정의됨
 DB가 갱신될 때마다 DBMS가 자동으로 제약조건의 만족 여부 검사
② 명시적 정의 방법
- 개체 무결성과 참조 무결성 이외에, 데이터의 정확성과 일관성 유지를 위해서 특정 속성이 만족해야 하는 

   도메인 무결성 제약조건은 명시적으로 정의해야 함
- 널 값 허용 여부(NOT NULL 제약조건), 고유 값 여부(UNIQUE 제약조건), 디폴트 값(DEFAULT 제약조건), 

   속성 값이 만족해야 하는 특정 조건(CHECK 제약조건) 등은 릴레이션을 정의할 때
명시적으로 정의해야 함
> DB가 갱신될 때마다 DBMS가 자동으로 제약조건의 만족 여부 검사
> 명시적으로 정의하지 않은 특수한 제약조건은 응용 프로그램을 작성해서 별도로 처리해야 함

 

---

---

---

---

[ 논리적 설계 ]

논리적 설계(logical design)란? 
- 개념적 데이터 모델을 DBMS가 지원하는 논리적 데이터 모델로 변환하는 것

논리적 설계의 주요 업무
① 논리적 스키마 모델링
② 트랜잭션 인터페이스 설계

---

[ 논리적 스키마 모델링 ]

논리적 스키마 모델링(logical schema modeling)이란? 
- 데이터 중심 DB 설계 관점에서, 개념적 스키마(ER 다이어그램)를 논리적 스키마로 변환한 다음, 

   정규화(normalization) 과정을 통해 논리적 스키마를 더욱 바람직한 형태로 변환하고, 무결성 제약조건을 정의하는 과정
- 오라클과 같은 관계형 DBMS를 선정했다면, 논리적 스키마를 관계 데이터 모델, 즉 릴레이션 스키마로 표현해야 함

---

[ 트랜잭션 인터페이스 설계 ]

트랜잭션 인터페이스(transaction interface) 설계란? 
- 처리 중심 DB 설계 관점으로, 개념적 설계 단계에서 작성한 업무 단위의 유형별 트랜잭션 명세서를 기초로 하여, 

   사용자 그룹별 접근 가능한 데이터 범위와 DB 서버에 접근하는 방법 등을

   결정해서 트랜잭션의 전체적인 골격과 인터페이스를 정의하는 과정

 

---

[ 릴레이션 스키마 변환 ]

릴레이션 스키마(relation schema)란? 
- 관계 데이터 모델의 기본이 되는 릴레이션을 구성하는 속성들의 집합으로, 릴레이션 이름과 속성 이름들로 표현함

릴레이션 스키마 변환 과정

1 .ERD의 개체와 관계를 릴레이션 스키마로 변환한다.
     릴레이션은 릴레이션명(속성1, 속성2, …) 형태로 표현된다.

• 개체 릴레이션: 개체명(속성1, 속성2, …)
• 관계 릴레이션: 관계명(속성1, 속성2, …)

2. 릴레이션 스키마에 기본키를 표시한다.
     기본키는 속성명 아래에 밑줄을 그어 표시한다.

3. 릴레이션 스키마를 단순화한다.
     일대일(1:1), 일대다(1:N) 관계 유형을 단순화하여 표현한다.

4. 정규화(normalization)를 수행한다.
     더 바람직한 릴레이션 스키마로 변환하기 위해 정규화 과정을 거친다.

---

[ 개체 타입의 변환 ]

[ 단순 속성을 갖는 개체 타입 변환 ]

변환 방법 : 개체를 구성하는 모든 속성이 릴레이션의 속성이 됨
기본키 : 후보키 가운데 업무의 특성에 적합한 하나를 선택함
변환 사례: 

• 단순 속성을 갖는 개체 타입의 예로 "사원"이라는 개체가 있다.
  이 개체는 사원번호, 이름, 급여라는 세 가지 속성을 가진다.
• 이를 개체 타입 변환 과정을 거쳐 릴레이션 스키마로 바꿀 수 있다.
  변환된 릴레이션 스키마는 두 가지 방식으로 표현할 수 있다.

1. 리스트 형태로 표현
   • 사원(사원번호, 이름, 급여)
   • 또는 기본키를 명시하여 사원(사원번호(PK), 이름, 급여)
2. 테이블 형태로 표현

          사원

         ────────────────

         사원번호 | 이름 | 급여

---

[ 복합 속성을 갖는 개체 타입 변환 ]

변환 방법 : 복합 속성을 구성하는 모든 속성이 릴레이션의 속성이 됨

기본키 : 복합 속성이 일반 속성인 경우와 후보키에 대응하는 속성인 경우를 구분해서 판단해야 함

 

1. 복합 속성이 일반 속성인 경우

- 기본키  : 기존 기본키 유지

- 변환 사례 : 복합 속성을 갖는 개체 타입의 예로 "사원"이라는 개체가 있다.
   이 개체는 사원번호, 이름, 급여라는 속성을 가지는데, 여기서 급여는 복합 속성으로 구성되어 있다.
   급여는 다시 기본급과 상여금으로 나뉜다.
- 이를 개체 타입 변환 과정을 거쳐 릴레이션 스키마로 바꾸면, 복합 속성은 분해되어 개별 속성으로 표현된다.
- 변환된 릴레이션 스키마는 다음과 같다:
   사원(사원번호, 이름, 기본급, 상여금)

 

2. 복합 속성이 기본키인 경우

- 변환 방법 : 복합 속성을 구성하는 모든 속성이 기본키(복합키)가 됨

- 변환 사례 : 기본키가 복합 속성인 개체의 예로 "사원"이라는 개체가 있다.
   이 개체는 부서번호, 순서, 사원번호, 이름, 급여라는 속성을 가진다.
   여기서 사원번호는 단일 속성이 아니라 부서번호와 순서라는 두 속성이 합쳐져서 만들어진 복합키이다.
- 이를 개체 타입 변환 과정을 거쳐 릴레이션 스키마로 바꾸면, 복합키를 구성하는 속성들을 그대로 포함시켜 표현한다.
- 변환된 릴레이션 스키마는 다음과 같다:
   사원(부서번호, 순서, 이름, 급여)

 

[비교] 다음 두 릴레이션 스키마는 다른 의미를 가짐
사원(부서번호, Num, 이름, 기본급, 상여금) => 부서번호와 Num이라는 2개 속성으로 구성된 기본키(복합키)
사원(부서번호, Num, 이름, 기본급, 상여금) => 부서번호나 Num 가운데 하나가 기본키가 됨(띄어쓰기 해서 밑줄)

 

---

[ 약한 개체 타입의 변환 ]

약한 개체 타입 변환 방법 및 주의 사항

- 릴레이션 스키마 변환
   소유 개체 타입 E를 갖는 약한 개체 타입 W에 대한 릴레이션 R을 생성한다.
   W에 속한 모든 단순 속성을 릴레이션 R에 포함시키고, 소유 개체 타입 E의 기본키를

   약한 개체 타입 W의 외래키 (Foreign Key, FK)로 포함시킨다.
- 기본키 결정
   약한 개체 타입에 해당하는 릴레이션 R에서 소유 개체 타입에 해당하는 릴레이션을 참조하는 외래키와

   약한 개체 타입의 부분키(partial key)를 조합한 속성 집합이 기본키(복합키)가 된다.
- 주의 사항
   약한 개체 타입에 소유 개체 타입의 기본키가 외래키로 포함될 때 식별 관계 타입이 간접적으로 표현된다.
   따라서 별도로 식별 관계 타입을 릴레이션 스키마로 변환할 필요가 없다.

 

변환 사례:  소유 개체 타입과 약한 개체 타입의 예시로 "사원"과 "부양가족"이 있다.

• 소유 개체 타입(사원)
  • 번호
  • 이름
  • 급여
• 식별 관계 타입
  • "부양하다"라는 관계로 사원과 부양가족을 연결한다.
• 약한 개체 타입(부양가족)
  • 관계
  • 이름
• 이를 변환하면 다음과 같은 릴레이션 스키마가 된다:
  • 사원(사원번호, 이름, 급여)
  • 부양가족(사원번호(FK), 관계, 이름)

---

[ 관계 타입의 변환 ]

일대일 유형 관계 타입 변환 방법 및 주의 사항

- 릴레이션
   관계에 속하는 개체들의 기본키 속성과 관계를 설명하는 속성(있는 경우)을 모두 관계 릴레이션의 속성으로 표현한다.
   이때 관계에 포함된 개체들의 기본키는 모두 관계 릴레이션의 외래키가 된다.
- 스키마 변환
   관계에 포함된 양쪽 개체의 기본키 가운데 하나가 관계 타입의 기본키가 된다.
- 기본키 결정
   업무의 특성을 고려해서 기본키를 결정해야 한다.

---

[ 일대일(1:1) 관계 타입의 변환 방법 ]

일대일 관계 타입 변환 사례

- 개체 타입
- 사원: 사원번호, 이름, 급여
- 부서: 부서번호, 부서명
- 관계 타입 (관리하다)
- 속성: 임명일자
- 관계: 사원과 부서 사이의 일대일 관계
- 릴레이션 스키마 변환
일대일 관계를 변환할 때는 두 가지 방식으로 표현할 수 있다.
- 관리하다(사원번호(FK), 부서번호(FK), 임명일자)
- 관리하다(부서번호(FK), 사원번호(FK), 임명일자)

=> 주의사항 : 기본키를 결정할 때는 업무 특성을 고려해야 함 (검색할 때 자주 사용하는 속성, 널 값을 갖지 않는 속성 등)

---

[ 일대다(1:n) 관계 타입의 변환 방법 ]

일대다 유형 관계 타입 변환 방법 및 주의 사항

- 릴레이션
   관계에 속하는 개체들의 기본키 속성과 관계를 설명하는 속성(있는 경우)을 모두 관계 릴레이션의 속성으로 표현한다.
   이때 관계에 포함된 개체들의 기본키는 모두 관계 릴레이션의 외래키가 된다.
- 스키마 변환
   (내용 없음, 관계 릴레이션으로 변환되는 과정이 암묵적으로 포함됨)
- 기본키 결정
   관계에 포함된 개체 가운데 n측 개체 타입의 기본키가 관계 릴레이션의 기본키가 된다.
- 주의 사항
   1측 개체의 기본키는 관계 릴레이션에 중복해서 나타나므로, 변환된 관계 릴레이션의 기본키가 될 수 없다.

 

일대답 관계 타입 변환 사례

- 개체 타입
- 사원: 사원번호, 이름, 급여
- 부서: 부서번호, 부서명
- 관계 타입 (근무하다)
- 속성: 발령일
- 관계: 사원과 부서 사이의 일대다 관계
- 한 사원은 여러 부서에서 근무할 수 있다(n측)
- 한 부서는 한 사원만을 가진다(1측)
- 릴레이션 스키마 변환
- 근무하다(사원번호(FK), 부서번호(FK), 발령일)

---

[ 다대다(m:n) 관계 타입의 변환 방법 ]
다대다 유형 관계 변환 방법 및 주의 사항

- 릴레이션 스키마 변환
   관계에 속하는 개체들의 기본키 속성과 관계를 설명하는 속성(있는 경우)을 모두 관계 릴레이션의 속성으로 표현한다.
   이때 관계에 포함된 개체들의 기본키는 모두 관계 릴레이션의 외래키가 된다.
- 기본키 결정
   기본적으로 관계에 포함된 양쪽 개체 타입의 기본키를 구성하는 모든 속성으로 이루어진 속성 집합이 기본키(복합키)가 된다.
- 주의 사항
   양쪽 개체 타입의 기본키들만으로 구성된 속성 집합이 유일성을 만족하지 않으면, 유일성을 만족시킬 수 있도록

   다른 속성을 포함시키거나 기본키 역할을 할 설계 속성을 추가할 수 있다.

 

다대다 관계 타입 변환 사례(1)

- 개체 타입
- 사원: 사원번호, 이름, 급여
- 교육과정: 과정번호, 과정명, 교육시간
- 관계 타입 (수강하다)
- 속성: 수강일자, 성적
- 관계: 사원과 교육과정 사이의 다대다 관계
- 한 사원은 여러 교육과정을 수강할 수 있다.
- 한 교육과정도 여러 사원이 수강할 수 있다.
- 릴레이션 스키마 변환
- 수강하다(사원번호(FK), 과정번호(FK), 수강일자, 성적)

다대다 관계 타입 변환 사례(2)

- 개체 타입
- 강사: 강사번호, 이름, 강사료
- 교육과정: 과정번호, 과정명, 교육시간
- 관계 타입 (강의하다)
- 속성: 강의일자
- 관계: 강사와 교육과정 사이의 다대다 관계
- 한 강사는 여러 교육과정을 강의할 수 있다.
- 한 교육과정도 여러 강사가 강의할 수 있다.
- 릴레이션 스키마 변환
- 강의하다(강사번호(FK), 과정번호(FK), 강의일자)

같은 강사가 같은 강의를 다른 날짜에 여러 번 강의할 수 있는데, 만약 (강사번호,과정번호)를 기본키로 설정하면

두 번째 강의한 데이터는 삽입할 수 없게 됨

---

[ 순환 관계 타입의 변환 ]

일대다(1:n) 순환 관계 타입 변환 방법

- 릴레이션 스키마 변환
① 관계 릴레이션의 속성으로 관계에 속하는 개체의 기본키를 외래키로 2개 포함시킨 후, 이름이 중복되지 않게 변경하고, 

   관계를 설명하는 속성(있는 경우)도 관계 릴레이션에 포함시킨다.
② 관계를 설명하는 속성의 수가 많지 않은 경우, 관계 릴레이션을 별도로 생성하지 않고 개체 릴레이션에 개체의 기본키를 

   외래키로 포함시켜서 순환 관계를 표현한다. 이때 관계를 설명하는 속성(있는 경우)도 개체 릴레이션에 포함시킨다.
- 기본키 결정
   n측 개체 타입의 기본키가 관계 릴레이션의 기본키가 된다.
- 주의 사항
   개체의 기본키를 외래키로 2개 포함시킬 때 속성 이름이 중복되지 않도록 반드시 1개는 이름을 변경해야 한다.

 

 일대다 순환 관계 타입 변환 사례

 

- 개체 타입 (사원)
- 사원번호
- 이름
- 업무
- 급여
- 관계 타입 (관리하다)
- 사원과 사원 사이의 순환 관계
- 관리자와 피관리자가 같은 개체(사원) 안에서 연결됨
- 속성: 관리시작일자
- 릴레이션 스키마 변환
- 초기 변환:
- 사원(사원번호, 이름, 업무, 급여)
- 관리하다(사원번호(FK), 관리자사원번호(FK), 관리시작일자)
- 단순화된 변환:
- 사원(사원번호, 이름, 업무, 급여, 관리자사원번호(FK), 관리시작일자)

 다대다 (m:n) 순환 관계 타입 변환 방법

- 릴레이션 스키마 변환
   관계 릴레이션을 별도로 생성한 후, 개체의 기본키를 외래키로 두 번 포함시킨다.
   또한 관계를 설명하는 속성(있는 경우)도 관계 릴레이션에 포함시킨다.
- 기본키 결정
   두 개의 외래키로 구성된 속성 집합이 관계 릴레이션의 기본키가 된다.
- 주의 사항
 동일한 기본키가 두 번 추가되므로 반드시 서로 다른 이름을 사용해야 한다.

 

다대다 순환 관계 타입 변환 사례

 

- 개체 타입 (교과목)
- 교과목번호
- 교과명
- 학점
- 구분
- 관계 타입 (선수하다)
- 관계: 교과목과 교과목 사이의 다대다 순환 관계
- 선수과목과 후수과목이 같은 개체(교과목) 안에서 연결됨
- 속성: 없음 (단순히 선수-후수 관계를 표현)
- 릴레이션 스키마 변환
- 교과목(교과목번호, 교과명, 학점, 구분)
- 선수하다(선수교과목번호(FK), 후수교과목번호(FK))

---

[ 3진 관계 타입 변환 ]

3진 관계 타입 변환 방법 및 주의 사항

• 릴레이션 스키마 변환
  ① 3진 관계 타입에 대한 릴레이션을 생성한다.
  ② 관계 타입에 참여하는 모든 개체 타입의 기본키를 관계 릴레이션에 외래키로 포함시킨다.
  ③ 관계를 설명하는 속성(있는 경우)도 관계 릴레이션에 포함시킨다.
• 기본키 결정
  기본적으로 외래키들로 구성된 속성 집합이 관계 릴레이션의 기본키가 된다.
  하지만 3진 관계 타입에서는 기본키가 복잡해지므로, 기본키 역할을 대체할 수 있는 설계 속성을 추가하는 것이 바람직하다.
• 주의 사항
  외래키들로 구성된 복합키가 유일성을 만족하지 않으면, 유일성을 만족시킬 수 있도록 다른 속성을 포함시키거나 기본키 역할을 할 설계 속성을 추가해야 한다.

 

3진 관계 타입 변환 사례

• 개체 타입
  • 사원: 사원번호, 이름, 급여
  • 부서: 부서번호, 부서명
  • 장비: 장비번호, 장비명
• 관계 타입 (과제수행하다)
  • 속성: 시작일자, 종료일자
  • 관계: 사원, 부서, 장비가 함께 참여하는 3진 관계
• 릴레이션 스키마 변환
  ① 과제수행하다(사원번호(FK), 부서번호(FK), 장비번호(FK), 시작일자, 종료일자)
  ② 또는 설계 속성을 추가하여
  과제수행하다(과제번호, 사원번호(FK), 부서번호(FK), 장비번호(FK), 시작일자, 종료일자)

 

---

[ 다중치 속성 변환 ]

[ 다중치 속성의 변환 방법 킻 주의 사항 ]

• 릴레이션
  다중치 속성을 표현하기 위해 별도의 릴레이션을 생성한다.
• 스키마 변환
  다중치 속성을 갖는 개체 타입의 기본키를 외래키로 포함시킨다.
• 기본키 결정
  외래키와 다중치 속성으로 구성된 속성 집합이 기본키가 된다.
• 주의 사항
  다중치 속성을 표현하기 위한 릴레이션 이름과 다중치 속성 이름은 반드시 서로 달라야 한다.

 다중치 속성의 변환 사례

• 개체 타입 (회원)
  • 회원번호
  • 이름
  • 주소
  • 휴대폰
  • 취미 (다중치 속성)
• 관계 및 변환
  • "취미"는 한 회원이 여러 개를 가질 수 있는 다중치 속성
  • 따라서 별도의 릴레이션으로 분리하여 표현한다.
• 릴레이션 스키마 변환
  1. 회원(회원번호, 이름, 주소, 휴대폰)
  2. 취미(회원번호(FK), 취미종류)

---

[ 슈퍼-서브 타입의 변환 ]

서브타입 개체 기준으로 정제한 경우
- 각각의 서브타입 개체로 통합되어 서브타입 개체 수만큼 별개의 개체 타입으로 변환

• 슈퍼 타입 (직원)
  • 공통 속성: 직원번호, 이름, 급여, 담당업무
• 서브 타입
  • 정규직: 직급, 호봉, 입사일
  • 계약직: 계약일, 계약기간
• 릴레이션 스키마 변환
  • 정규직(직원번호, 이름, 급여, 담당업무, 직급, 호봉, 입사일)
  • 계약직(직원번호, 이름, 급여, 담당업무, 계약일, 계약기간)

슈퍼타입과 서브타입 개체 모두를 기준으로 정제한 경우
- 슈퍼타입 개체의 기본키를 서브타입 개체의 기본키로 추가해야 하므로 상호 참조할 수 있도록 반드시 외래키로 지정해야 함

• 슈퍼 타입 (직원)
  • 공통 속성: 직원번호, 이름, 급여, 담당업무
• 서브 타입
  • 정규직: 직급, 호봉, 입사일
  • 계약직: 계약일, 계약기간
• 릴레이션 스키마 변환
  • 직원(직원번호, 이름, 급여, 담당업무)
  • 정규직(직원번호(FK), 직급, 호봉, 입사일)
  • 계약직(직원번호(FK), 계약일, 계약기간)

 

---

[ 릴레이션 단순화 ]

[ 일대일(1:1) 관계 단순화 ]

일대일 관계 단순화 방법

①  관계에 전체 참여인 개체 릴레이션에 다음을 추가한다.
• 관계에 포함된 다른 개체의 기본키에 대응하는 속성
• 관계의 설명 속성(있는 경우)
② 기존의 관계 릴레이션은 삭제한다.

 

일대일 관계의 단순화 사례(1) 

ER 다이어그램 → 릴레이션 변환

• 사원(사원번호, 이름, 급여)
• 부서(부서번호, 부서명)
• 관리하다(부서번호(FK), 사원번호(FK), 임명일자)
  혹은
• 관리하다(사원번호(FK), 부서번호(FK), 임명일자)

릴레이션 단순화

①

• 부서(부서번호, 부서명, 부서장번호(FK), 임명일자)
• 사원(사원번호, 이름, 급여)

②

• 사원(사원번호, 이름, 급여, 관리부서번호(FK), 임명일자)
• 부서(부서번호, 부서명)

 

 일대일 관계의 단순화 사례(2) 

ER 다이어그램 → 릴레이션 변환

• 멘토(멘토번호, 이름, 직장, 직책)
• 멘티(멘티번호, 이름, 학교, 학년)
• 멘토링하다(멘토번호(FK), 멘티번호(FK), 시작일자)
  혹은
• 멘토링하다(멘티번호(FK), 멘토번호(FK), 시작일자)

릴레이션 단순화

①

• 멘토(멘토번호, 이름, 직장, 직책, 멘티번호(FK), 시작일자)
• 멘티(멘티번호, 이름, 학교, 학년)

②

• 멘토(멘토번호, 이름, 직장, 직책)
• 멘티(멘티번호, 이름, 학교, 학년, 멘토번호(FK), 시작일자)

---

[ 일대다(1:n) 관계 단순화 ]

일대다 관계 단순화 방법
① 관계의 전체 참여인 n측 개체 릴레이션에 다음을 추가한다.
• 관계의 1측에 있는 개체의 기본키에 대응하는 속성
• 관계의 설명 속성(있는 경우)
② 기존의 관계 릴레이션은 삭제한다.

 

일대다 관계의 단순화 사례(1)

ER 다이어그램 → 릴레이션 변환

• 사원(사원번호, 이름, 급여)
• 부서(부서번호, 부서명, 사무실)
• 근무하다(사원번호(FK), 부서번호(FK))

릴레이션 단순화

• 사원(사원번호, 이름, 급여, 근무부서번호(FK))
• 부서(부서번호, 부서명, 사무실)

단순화가 불필요한 일대다 관계 사례(1)

ER 다이어그램 → 릴레이션 변환

• 사원(사원번호, 이름, 입사일)
• 동아리(동아리번호, 동아리명, 주요활동, 개설일자)
• 활동하다(사원번호(FK), 동아리번호(FK))

릴레이션 단순화 => 의미가 없다.(할 필요 없다.)

• 사원(사원번호, 이름, 입사일, 동아리번호(FK))
• 동아리(동아리번호, 동아리명, 주요활동, 개설일자)

 

단순화가 불필요한 일대다 관계 사례(2)

ER 다이어그램 → 릴레이션 변환

• 사원(사원번호, 이름, 입사일)
• 동아리(동아리번호, 동아리명, 주요활동, 개설일자)
• 부서(부서번호, 부서명, 사무실)
• 활동하다(사원번호(FK), 동아리번호(FK), 가입일자)
• 근무하다(사원번호(FK), 부서번호(FK), 발령일자)

릴레이션 단순화

• 사원(사원번호, 이름, 입사일, 동아리번호(FK), 동아리가입일자, 근무부서번호(FK), 부서발령일자)
• 동아리(동아리번호, 동아리명, 주요활동, 개설일자)
• 부서(부서번호, 부서명, 사무실)

 

---

[ 릴레이션 스키마 변환 사례 ]

1. 환자 검사 내역 관리 사례

문제
병원에서 입원 환자의 검사 내역을 기록하기 위한 요구사항을 만족하는 ER 
다이어그램을 작성한 다음, 릴레이션 스키마로 변환하시오. 
요구사항
① 환자는 환자번호와 이름, 주민등록번호, 휴대폰 정보가 필요하다. 
② 환자가 검사한 검사내역을 기록한다.
③ 검사내역은 순서와 검사명, 일시, 결과 정보가 필요하다. 
④ 검사내역의 순서는 각 환자마다 1번부터 시작한다.

(1) 최종 개체 타입

(2) 최종 관계 타입

(3) ER 다이어그램

(4) 릴레이션 스키마 변환

---

2. 요리 레시피 관리 사례

문제
요리 레시피를 관리하기 위해 작성한 요구사항을 만족하는 ER 다이어그램을 작성한 다음, 릴레이션 스키마로 변환하시오. 
요구사항
① 요리 레시피에는 번호, 요리명, 요리법, 요리시간, 칼로리, 식재료 정보가 포함된다. 
② 요리마다 필요한 식재료 수는 크게 다르다.

(1) 최종 개체 타입

(2) ER 다이어그램

(3) 릴레이션 스키마 변환

식재료코드 개체를 활용한 릴레이션 스키마 변환

---

3. 과제 수행 내역 관리 사례

문제
과제를 수행할 때 하나의 관리 부서와 참여하는 여러 명의 연구원 및 인턴들의
정보를 관리하기 위해 작성한 요구사항을 만족하는 ER 다이어그램을 작성한
다음, 릴레이션 스키마로 변환하시오. 
요구사항
① 부서는 부서번호와 부서명 정보가 필요하다. 
② 연구원은 사번과 이름, 전공 정보가 필요하다.
③ 인턴은 id와 이름, 담당업무 정보가 필요하다.
④ 수행한 과제명과 과제를 시작한 일자, 종료한 일자 정보가 필요하다.
⑤ 한 과제의 관리 부서는 하나이지만, 한 부서는 여러 과제를 관리할 수 있다. 
⑥ 한 부서가 관리하는 여러 과제의 시작일자는 서로 다르다. 
⑦ 연구원은 시작일자가 다른 여러 과제를 동시에 수행할 수 있다. 
⑧ 연구원과 인턴은 과제 진행 중간에 참여할 수 있다. 
⑨ 인턴은 한 번에 한 과제에만 참여할 수 있다. 
⑩ 인턴은 과제가 종료되기 전에 다른 과제에 배정될 수 있다.

(1) 최종 개체 타입

(2) 최종 관계 타입

(3) ER 다이어그램

(4) 릴레이션 스키마 변환

(5) 릴레이션 스키마 단순화

---

연습문제

어느 대학에서 어떤 교수가 어떤 학생을 지도하는지, 학생이 어떤 교과목을 수강하는 지, 교수가 어떤 교과목을 강의하는지를 

관리하려고 한다. 다음 요구사항을 만족하는 ER 다이어그램을 작성한 다음, 릴레이션 스키마로 변환하는 과정을 완성하시오.
■ 요구사항
① 교수는 사번과 직급, 전공, 입사일 정보가 필요하다.
② 학생은 학번과 이름, 주소, 학년 정보가 필요하다.
③ 교과목은 교과목번호와 교과목명, 학점, 구분 정보가 필요하다.
④ 교과목 구분은 전공필수, 전공선택, 교양필수, 교양선택으로 구분한다.
⑤ 교수 1명이 여러 명의 학생을 지도한다.
⑥ 교수 가운데 지도 학생이 없는 교수도 있다.
⑦ 모든 학생은 1명의 지도교수가 배정된다.
⑧ 교수가 교과목을 강의한 년도_학기 정보가 필요하다.
⑨ 동일 교과목의 여러 분반을 여러 교수가 강의하는 경우도 있다.
⑩ 학생이 교과목을 수강한 년도_학기와 성적 정보가 필요하다.
⑪ 학생은 한 학기에 최대 21학점까지 수강할 수 있다.
⑫ 학생의 성적이 ‘F’인 경우에만 동일한 교과목을 재수강할 수 있다.
-> 결과물 예시
■ 최종 개체 타입
■ 최종 관계 타입
■ ER 다이어그램
■ 릴레이션 스키마 변환
■ 릴레이션 스키마 단순화

1. 최종 개체 타입 (Entity Types)

• 교수(Professor)
  • 사번(P_ID, PK)
  • 직급(Position)
  • 전공(Major)
  • 입사일(Hire_Date)
• 학생(Student)
  • 학번(S_ID, PK)
  • 이름(Name)
  • 주소(Address)
  • 학년(Year)
• 교과목(Course)
  • 교과목번호(C_ID, PK)
  • 교과목명(C_Name)
  • 학점(Credit)
  • 구분(Category: 전공필수/전공선택/교양필수/교양선택)

---

2. 최종 관계 타입 (Relationship Types)

• 지도하다(Advises)
  • 교수 1 : 학생 N
  • 모든 학생은 반드시 1명의 지도교수 배정
  • 교수는 지도 학생이 없을 수도 있음
• 강의하다(Teaches)
  • 교수 N : 교과목 N
  • 속성: 년도_학기(Year_Semester)
  • 동일 교과목 여러 분반 가능
• 수강하다(Enrolls)
  • 학생 N : 교과목 N
  • 속성: 년도_학기(Year_Semester), 성적(Grade)
  • 제약조건: 한 학기 최대 21학점, 성적 F일 경우만 재수강 가능

---

3. ER 다이어그램 (텍스트 표현)

교수(P_ID, 직급, 전공, 입사일)
학생(S_ID, 이름, 주소, 학년)
교과목(C_ID, 교과목명, 학점, 구분)

교수 ──< 지도하다 >── 학생
교수 ──< 강의하다(년도_학기) >── 교과목
학생 ──< 수강하다(년도_학기, 성적) >── 교과목

---

4. 릴레이션 스키마 변환

• 교수(사번 PK, 직급, 전공, 입사일)
• 학생(학번 PK, 이름, 주소, 학년, 지도교수번호 FK)
• 교과목(교과목번호 PK, 교과목명, 학점, 구분)
• 강의하다(사번 FK, 교과목번호 FK, 년도_학기, PK=(사번, 교과목번호, 년도_학기))
• 수강하다(학번 FK, 교과목번호 FK, 년도_학기, 성적, PK=(학번, 교과목번호, 년도_학기))

---

5. 릴레이션 스키마 단순화

• 학생 테이블에 지도교수번호(FK)를 포함 → 별도 관계 테이블 불필요
• 강의하다, 수강하다는 관계 속성(년도_학기, 성적)이 존재하므로 별도 테이블 유지 필요

최종 단순화된 스키마:

• 교수(P_ID, 직급, 전공, 입사일)
• 학생(S_ID, 이름, 주소, 학년, 지도교수번호 FK)
• 교과목(C_ID, 교과목명, 학점, 구분)
• 강의하다(P_ID FK, C_ID FK, 년도_학기, PK=(P_ID, C_ID, 년도_학기))
• 수강하다(S_ID FK, C_ID FK, 년도_학기, 성적, PK=(S_ID, C_ID, 년도_학기))

---

요약:

• 일대일 관계는 한쪽 테이블에 FK 포함 → 단순화 가능
• 일대다 관계는 다수 쪽에 FK 포함 → 단순화 가능
• 다대다 관계 + 관계 속성 존재 → 별도 테이블 유지 필요

---