Database 11일차

다음은 국내 관광을 전문으로 하는 여행사의 DB 구축을 위한 릴레이션 스키마다.

함 수적 종속 관계를 분석하여 정규화한 다음, 최종 릴레이션 스키마를 작성하시오.

 

고객(id, 이름, 휴대폰, 주소, 등급코드)

직원(사번, 이름, 생년월일, 연락처, 급여, 담당업무, 입사일)

여행상품(여행번호, 출발지, 도착지, 프로그램, 시작일시, 종료일시, 최소출발인원,

최대인원, 여행경비, 예약금, 출발여부, 담당직원사번(FK))

관광버스(차량번호, 좌석수, 출고년도)

운전기사(기사_id, 이름, 생년월일, 휴대폰, 운전경력, 시급, 계약일, 계약기간)

예약하다(고객_id(FK), 여행번호(FK), 예약일자, 예약금결제여부, 여행경비결제여부)

기사배정하다(여행번호(FK), 기사_id(FK), 근무시간)

차량배정하다(여행번호(FK), 차량번호(FK))

고객등급코드(코드. 등급, 기준)

업무코드(코드, 업무)

 

1. 함수적 종속 관계 분석

• 고객: id → 이름, 휴대폰, 주소, 등급코드
• 직원: 사번 → 이름, 생년월일, 연락처, 급여, 담당업무, 입사일
• 여행상품: 여행번호 → 출발지, 도착지, 프로그램, 시작일시, 종료일시, 최소출발인원, 최대인원, 여행경비, 예약금, 출발여부, 담당직원사번
• 관광버스: 차량번호 → 좌석수, 출고년도
• 운전기사: 기사_id → 이름, 생년월일, 휴대폰, 운전경력, 시급, 계약일, 계약기간
• 예약하다: (고객_id, 여행번호) → 예약일자, 예약금결제여부, 여행경비결제여부
• 기사배정하다: (여행번호, 기사_id) → 근무시간
• 차량배정하다: (여행번호, 차량번호)
• 고객등급코드: 코드 → 등급, 기준
• 업무코드: 코드 → 업무

---

2. 정규화 과정

• 1NF: 모든 속성은 원자값을 가짐 → 이미 만족.
• 2NF: 부분 종속 제거 → 예약하다, 기사배정하다, 차량배정하다는 복합키에 종속된 속성만 존재하므로 만족.
• 3NF: 이행 종속 제거 → 직원의 담당업무는 업무코드 테이블로 분리, 고객의 등급코드는 고객등급코드 테이블로 분리.

---

3. 최종 릴레이션 스키마

정규화 후 최종 스키마는 다음과 같습니다:

고객

• 고객(id, 이름, 휴대폰, 주소, 등급코드(FK))

고객등급코드

• 고객등급코드(코드, 등급, 기준)

직원

• 직원(사번, 이름, 생년월일, 연락처, 급여, 업무코드(FK), 입사일)

업무코드

• 업무코드(코드, 업무)

여행상품

• 여행상품(여행번호, 출발지, 도착지, 프로그램, 시작일시, 종료일시, 최소출발인원, 최대인원, 여행경비, 예약금, 출발여부, 담당직원사번(FK))

관광버스

• 관광버스(차량번호, 좌석수, 출고년도)

운전기사

• 운전기사(기사_id, 이름, 생년월일, 휴대폰, 운전경력, 시급, 계약일, 계약기간)

예약하다

• 예약하다(고객_id(FK), 여행번호(FK), 예약일자, 예약금결제여부, 여행경비결제여부)

기사배정하다

• 기사배정하다(여행번호(FK), 기사_id(FK), 근무시간)

차량배정하다

• 차량배정하다(여행번호(FK), 차량번호(FK))