아주대학교 권역응급외상센터 데이터베이스 - 논리적 설계

논리적설계

ER모델을 사용하여 조직체의 데이터베이스 응용을 위한 개념적 스키마를 생성하였다. 논리적 설계 단계에서는 ER 스키마를 관계 데이터 모델의 릴레이션들로 사상해야 한다. 이때 사상할 대상이 어떤 애튜리뷰트이고 어떤 타입을 가지느냐에 따라 사상하는 방법이 달라지기 때문에 7단계 알고리즘을 따라가며 릴레이션으로 사상하였다.

 

단계 1 : 정규 엔티티 타입

환자 엔티티 타입은 기본키인 환자_ID(PAT_ID) 애트리뷰트, 기본키가 아닌 애트리 뷰트 이메일(EMAIL),주소(ADDRESS),이름(PAT_NAME),주민등록번호(REGIST_NUM),성별(SEX),직업(JOB),(PHONE_NUM)과 복합 애트리뷰트인 바이탈사인과, 다이애트리뷰트인 지병을 가지고 있다. 복합 애트리뷰트인 바이탈 사인을 구성하는 단순 릴레이션들인 체온(BODY_HEAT), 맥박(PULSE), 혈압(BLOOD_PRESSURE), 호흡(BREATH)를 PATIENT(환자) 릴레이션에 포함시킨다.

다치애트리뷰트인 지병은 단계 1에서는 고려하지 않으므로 PATIENT(환자) 릴레이션에 PATIENT_DISEASE(지병) 릴레이션은 포함시키지 않는다.

 

PATIENT(환자) (PAT_ID, EMAIL, ADDRESS, PAT_NAME, REGIST_NUM, SEX, JOB, PHONE_NUM, BODY_HEAT, PULSE, BLOOD_PRESSURE, BREATH)

 

의사 엔티티 타입은 아래의 릴레이션으로 사상된다.
DOCTOR(의사) (DOC_ID, SEX, DOC_NAME, PHONE_NUM, EMAIL, TITLE)

 

차트 엔티티 타입은 아래의 릴레이션으로 사상된다.  이때 OPINION은 의사소견 애트리뷰트를 의미한다.
CHART(차트) (CHART_ID, OPINION)

 

간호사 엔티티 타입은 아래의 릴레이션으로 사상된다.

NURSE(간호사) (NUR_ID, NUR_NAME, PHONE_NUM, SEX, EMAIL, TITLE)

 

코디네이터 엔티티 타입은 아래의 릴레이션으로 사상된다.

COORDINATOR(코디네이터) (CODI_ID, CODI_NAME, SEX, PHONE_NUM, EMAIL)

 

병실 엔티티 타입은 아래의 릴레이션으로 사상된다. 이때 MAX_PATIENT_NUM은 최대 수용인원 애트리뷰트 , CUR_PATIENT_NUM은 현재 수용인원 애트리뷰트를 의미한다.

PATIENTS_ROOM (병실)(ROOM_NUM, FLOOR, MAX_PATIENT_NUM, CUR_PATIENT_NUM)

 

수술실 엔티티 타입은 아래의 릴레이션으로 사상된다.

OPERATING_ROOM(수술실) (OPER_NUM, FLOOR)

 

검사실 릴레이션은 아래의 릴레이션으로 사상된다.

LABORATORY(검사실) (LABO_NAME, FLOOR)

 

DRIVER릴레이션은 아래와 같은 릴레이션으로 사상된다. 이때 ROLE은 역할 애트리뷰트를 의마한다.

DRIVER(DRIVER) (DRIVER_ID, DRIVER_NAME, SEX, PHONE_NUM, EMAIL, ROLE)

 

전공 애트리뷰트는 아래와 같은 릴레이션으로 사상된다.

SECTION(전공) (SEC_NUM, SEC_NAME)

 

부서 애트리뷰트는 아래와 같은 릴레이션으로 사상된다.

DEPARTMENT(부서) (DEPT_NUM, DEPT_NAME)

운송수단 애트리뷰트는 아래와 같은 릴레이션으로 사상된다.

TRANSPORT(운송수단) (TRANS_NUM, TYPE)

 

 

단계 2 : 약한 엔티티 타입

소유 엔티티 타입은 PATIENT이므로 PATIENT의 기본키를 PROTECTOR 릴레이션에 외래 키로 포함시킨다. 또한 NURSE와 연락하다라는 약한 관계를 가지고 있기 때문에 NURSE엔티티 타입의 기본키를 NUR_NO 외래 키로 참조 받는다. 기본 키를 참조하는 외래 키 PAT_NOM, NUR_NO와 PROTECTOR의 부분키 PROTECT_NAME이 모여 기본키가 된다.

PROTECTOR(보호자) (PAT_NO, NUR_NO, PROTECT_NAME, SEX, PHONE_NUM)

 

단계 3 : 정규 2진 1:1 엔티티 타입

차트 엔티티 타입은 기록받다라는 정규 2진 1:1 관계를 관계 타입을 가진다. 기록하다는 관계타입은 의사 엔티티 타입과 정규 2진 1:N 관계타입을 가지므로 다음 단계에서 다룬다.

차트 엔티티 타입과 환자 엔티티 타입은 기록받다 관계 타입에 전체참여한다. 이때 기록받다 관계 타입에서 의사가 환자에 대한 의사 소견을 적어야되기 때문에 차트 릴레이션에 환자 엔티티 타입의 기본키를 참조하는 외래키를 차트 릴레이션에 추가해준다.

CHART(차트) (CHART_ID, OPINION, PATIENT_ID)

 

수술실 엔티티 타입은 기록받다라는 정규 2진 1:1 관계를 관계 타입을 가진다. 수술하다는 관계타입은 의사 엔티티 타입과 정규 2진 1:N 관계타입을 가지므로 다음 단계에서 다룬다.

수술실 릴레이션은 수술실 번호를 기본키로 가지고 관계를 맺는 환자 엔티티 타입의 기본키에 대응되는 값을 참조하는 외래키 인 OPER_PAT 가져야 한다. OPER_DATA(수술날짜), OPER_TIME(수술일정)은 관계 타입이 갖고 있는 애트리뷰트이기 때문에 수술실 릴레이션에 추가해준다.

OPERATING_ROOM(수술실) (OPER_NUM, FLOOR, OPER_PAT, OPER_DATE, OPER_TIME)

 

 

단계 4 : 정규 2진 1:N 엔티티 타입

의사 엔티티 타입은 수술실과 수술하다 2진 1:N 관계 타입을 가진다. 1측에 대응 되는 수술실 엔티티 타입의 기본키가 외래키(OPER_NO)로 의사 릴레이션에 추가된다.

전공 엔티티 타입과 의사 엔티티 타입은 가지다란 관계타입에 대해 모두 전체 참여를 하고 있는 1:N 관계를 가지고 있다.  이때 1 측의 전공 엔티티 타입의 기본키를 의사 릴레이션에 외래키 SEC_NO로 추가해준다. OPER_DATA와 OPER_DATE와 OPER_TIME 은 수술하다 관계타입에서 나온 관계 애트리뷰트이다.
DOCTOR(의사) (DOC_ID, SEX, DOC_NAME, PHONE_NUM, EMAIL, TITLE, OPER_NO, SEC_NO, OPER_DATE, OPER_TIME)

 

간호사 엔티티 타입은 부서 엔티티 타입과 가지다 라는 정규 2진 1:N 관계 타입을 가지고 있다. 이때 1측의 부서 엔티티 타입의 기본키를 외래키 DNO 라고 지정하여 NURSE 릴레이션에 추가해준다.

NURSE(간호사) (NUR_ID, NUR_NAME, PHONE_NUM, SEX, EMAIL, TITLE, DNO)

 

드라이버 엔티티 타입은 코디네이터 엔티티 타입과 가지다 라는 정규 2진 1:N 관계 타입을 가지고 있다. 이때 기본키를 외래키 CODI_NUM 라고 지정하여 드라이버 릴레이션에 추가해준다.

DRIVER(드라이버) (DRIVER_ID, DRIVER_NAME, SEX, PHONE_NUM, EMAIL, ROLE, CODI_NUM)

 

차트 엔티티 타입은 의사 엔티티 타입과 기록되다 정규 2진 1:N 관계 타입이 존재한다. 그렇기 때문에 1측의 의사 엔티티 타입의 기본키를 차트 릴레이션의 외래키RECORDER로 추가해준다.

CHART(차트)( (CHART_ID, OPINION, PATIENT_ID, RECORDER)

 

환자 엔티티 타입은 의사 엔티티 타입, 간호사 엔티티 타입과 각각 담당하다 정규 2진 1:N 관계 타입이 존재한다. 그렇기 때문에 1측의 의사 엔티티 타입의 기본키를 차트 릴레이션의 외래키 CHARGE_DOC으로 추가해주고 1측의 간호사 엔티티 타입의 기본키를 차트 릴레이션의 외래키 CHARGE_NUR로 추가해준다.

PATIENT(환자) (PAT_ID, EMAIL, ADDRESS, PAT_NAME, REGIST_NUM, SEX, JOB, PHONE_NUM, BODY_HEAT, PULSE, BLOOD_PRESSURE, BREATH, CHARGE_DOC, CHARGE_NUR)

 

병실 엔티티 타입과 환자  엔티티 타입은 입원하다 정규 2진 1:N 관계 타입을 가진다. 이때 1 측의 환자 엔티티 타입의 기본키를 병실 릴레이션에 외래키 STAY_PAT로 추가해준다. IN_DATE(입원날짜)와 OUT_DATE(퇴원날짜)는 입원하다 관계타입에서 나온 관계 애트리뷰트이다.

PATIENTS_ROOM(병실) (ROOM_NUM, FLOOR, MAX_PATIENTS_NUM, CUR_PATIENTS_NUM, IN_DATE, OUT_DATE)

 

수술실 엔티티 타입과 환자 엔티티 타입은 수술받다 정규 2진 1:N 관계 타입을 가진다. 이때 1 측의 환자 엔티티 타입의 기본키를 수술실 릴레이션에 외래키 CHECK_PAT로 추가해준다. CHECK_DATE(입원날짜)와 CHECK_TIME(퇴원날짜)는 입원하다 관계타입에서 나온 관계 애트리뷰트이다.

LABORATORY(수술실) (LABO_NAME, FLOOR, CHECK_PAT, CHECK_DATE, CHECK_TIME)

 

코디네이터 엔티티 타입은 부서 엔티티 타입과 가지다 라는 정규 2진 1:N 관계 타입을 가지고 있다. 이때 1측의 부서 엔티티 타입의 기본키를 외래키 DEPT_NUM 라고 지정하여 코디네이터 릴레이션에 추가해준다.

COORDINATOR(코디네이터)(CODI_ID, CODI_NAME, SEX, PHONE_NUM, DEPT_NAME, EMAIL, DEPT_NUM)

 

단계 5 : 2진 M:N 관계 타입

드라이버 엔티티 타입과 운송수단 엔티티 타입은 배정받다 2진 M:N 관계 타입을 가진다. 운송수단 엔티티 타입에 대응되는 기본키와 드라이브 엔티티 타입에 대응되는 기본키를 외래키로 포함시켜 ASSIGN배정받다 릴레이션에 추가시킨다. ASSIGN 릴레이션은 두 외래키를가 모여 기본키가 된다.

ASSIGN(배정받다) (TRANS_NUM, DRIVER_ID)

 

간호사 엔티티 타입과 환자 엔티티 타입은 배정받다 2진 M:N 관계 타입을 가진다. 간호사 엔티티 타입에 대응되는 기본키와 드라이브 엔티티 타입에 대응되는 기본키를 외래키로 포함시켜 TAKE_CHARGE_OF(담당하다) 릴레이션에 추가시킨다. ASSIGN 릴레이션은 두 외래키를가 모여 기본키가 된다.

TAKE_CHARGE_OF(담당하다) (NUR_ID, ROOM_NUM)

 

코디네이터 엔티티 타입과 수술실 엔티티 타입은 조회하다 2진 M:N 관계 타입을 가진다. 간호사 엔티티 타입에 대응되는 기본키와 드라이브 엔티티 타입에 대응되는 기본키를 외래키로 포함시켜 TAKE_CHARGE_OF(담당하다) 릴레이션에 추가시킨다. ASSIGN 릴레이션은 두 외래키를가 모여 기본키가 된다.

CHECK_OPER(조회하다) (CODI_ID, OPER_NUM)

 

코디네이터 엔티티 타입과 병실 엔티티 타입은 조회하다 2진 M:N 관계 타입을 가진다. 코디네이터 엔티티 타입에 대응되는 기본키와 병실 엔티티 타입에 대응되는 기본키를 외래키로 포함시켜 CEHCK_PATIENT_ROOM(조회하다) 릴레이션에 추가시킨다. CHECK_PATIENT_ROOM 릴레이션은 두 외래키를가 모여 기본키가 된다.

CHECK_PATIENT_ROOM(조회하다) (CODI_ID, ROOM_NUM)

코디네이터 엔티티 타입과 수술실 엔티티 타입은 조회하다 2진 M:N 관계 타입을 가진다. 코디네이터 엔티티 타입에 대응되는 기본키와 수술실 엔티티 타입에 대응되는 기본키를 외래키로 포함시켜 CEHCK_OPER(조회하다) 릴레이션에 추가시킨다. CHECK_OPER 릴레이션은 두 외래키를가 모여 기본키가 된다.

CHECK_OPER(조회하다) (CODI_ID, OPER_NUM)

 

코디네이터 엔티티 타입과 검사실 엔티티 타입은 조회하다 2진 M:N 관계 타입을 가진다. 코디네이터 엔티티 타입에 대응되는 기본키와 검사실 엔티티 타입에 대응되는 기본키를 외래키로 포함시켜 CEHCK_LAB(조회하다) 릴레이션에 추가시킨다. CHECK_LAB 릴레이션은 두 외래키를가 모여 기본키가 된다.

CHECK_LAB(조회하다) (CODI_ID, OPER_NUM)

 

의사 엔티티 타입과 병실 엔티티 타입은 조회하다 2진 M:N 관계 타입을 가진다. 의사 엔티티 타입에 대응되는 기본키와 병실 엔티티 타입에 대응되는 기본키를 외래키로 포함시켜 VISIT(회진하다) 릴레이션에 추가시킨다. VISIT(회진하다) 릴레이션은 두 외래키를가 모여 기본키가 된다.

VISIT(회진하다) (ROOM_NUM, DOC_ID)

 

단계 6 : 3진 관계 타입

ER 스키마에서 의사 엔티티 타입, 간호사 엔티티 타입, 코디네이터 엔티티 타입을 연결하는 CONTACT(연락하다) 엔티티 관계 타입이 유일한 3진 관계 타입이다. 3진 관계 타입에 대해서는 새로운 릴레이션을 생성한다. 이 릴레이션의 이름을 CONTACT라고 부르기로 하자. 의사 엔티티 타입에 대응되는 릴레이션의 기본 키, 간호사 엔티티 타입에 대응되는 릴레이션의 기본 키, 코디네이터 엔티티 타입에 대응되는 릴레이션의 기본키를 CONTACT(연락하다) 릴레이션의 외래 키로 포함시킨다.  세 외래 키의 조합이 CONTACT(연락하다) 릴레이션의 기본 키가 된다.

CONTACT(연락하다) (NUR_ID, DOC_ID, CODI_ID)

 

단계 7 :  다치 애트리뷰트

ER 스키마에서 PATIENT(환자) 엔티티 타입의 지병 애트리뷰트가 다치 애트리뷰트이다. 다치 애트리뷰트에 대해서 새로운 릴레이션을 생성한다. 이 릴레이션의 이름을 PATIENT_DISEASE라고 부르기로 하자. 다치 애트리뷰트에 해당하는 애트리뷰트CHRONIC_DSEASE를 PATIENT_DISEASE 릴레이션에 포함시키고, 다치 애트리뷰트를 갖고 있는 PATIENT 엔티티 타입에 대응되는 릴레이션의 기본키 PAT_ID를 외래 키로 포함시킨다. 두 애트리뷰트의 조합이 PATIENT_DISEASE 릴레이션의 기본키가 된다.

PATIENT_DISEASE(지병) (PAT_ID, CHRONIC_DISEASE)

 

ER 스키마에 대해 7단계의 알고리즘을 적용하면 최종적으로 아래와 같은 릴레이션들의 집합이 생성된다.

 

n  PATIENT(환자) (PAT_ID, EMAIL, ADDRESS, PAT_NAME, REGIST_NUM, SEX, JOB, PHONE_NUM, BODY_HEAT, PULSE, BLOOD_PRESSURE, BREATH)

n  DOCTOR(의사) (DOC_ID, SEX, DOC_NAME, PHONE_NUM, EMAIL, TITLE)

n  CHART(차트) (CHART_ID, OPINION)

n  NURSE(간호사) (NUR_ID, NUR_NAME, PHONE_NUM, SEX, EMAIL, TITLE,DNO)

n  COORDINATOR(코디네이터) (CODI_ID, CODI_NAME, SEX, PHONE_NUM, EMAIL)

n  PATIENTS_ROOM (병실)(ROOM_NUM, FLOOR, MAX_PATIENT_NUM, CUR_PATIENT_NUM)

n  OPERATING_ROOM(수술실) (OPER_NUM, FLOOR)

n  LABORATORY(검사실) (LABO_NAME, FLOOR)

n  DRIVER(DRIVER) (DRIVER_ID, DRIVER_NAME, SEX, PHONE_NUM, EMAIL, ROLE)

n  SECTION(전공) (SEC_NUM, SEC_NAME)

 

n  DEPARTMENT(부서) (DEPT_NUM, DEPT_NAME)

n  TRANSPORT(운송수단) (TRANS_NUM, TYPE)

n  PROTECTOR(보호자) (PAT_NO, NUR_NO, PROTECT_NAME, SEX, PHONE_NUM)

n  CHART(차트) (CHART_ID, OPINION, PATIENT_ID)

n  OPERATING_ROOM(수술실) (OPER_NUM, FLOOR, OPER_PAT, OPER_DATE, OPER_TIME)

n  DOCTOR(의사) (DOC_ID, SEX, DOC_NAME, PHONE_NUM, EMAIL, TITLE, OPER_NO, SEC_NO, OPER_DATE, OPER_TIME)

n  NURSE(간호사) (NUR_ID, NUR_NAME, PHONE_NUM, SEX, EMAIL, TITLE, DNO)

n  DRIVER(드라이버) (DRIVER_ID, DRIVER_NAME, SEX, PHONE_NUM, EMAIL, ROLE, CODI_NUM)

n  CHART(차트)( (CHART_ID, OPINION, PATIENT_ID, RECORDER)

n  PATIENT(환자) (PAT_ID, EMAIL, ADDRESS, PAT_NAME, REGIST_NUM, SEX, JOB, PHONE_NUM, BODY_HEAT, PULSE, BLOOD_PRESSURE, BREATH, CHARGE_DOC, CHARGE_NUR)

n  PATIENTS_ROOM(병실) (ROOM_NUM, FLOOR, MAX_PATIENTS_NUM, CUR_PATIENTS_NUM, STAY_PAT, IN_DATE, OUT_DATE)

n  LABORATORY(수술실) (LABO_NAME, FLOOR, CHECK_PAT, CHECK_DATE, CHECK_TIME)

n  COORDINATOR(코디네이터)(CODI_ID, CODI_NAME, SEX, PHONE_NUM, DEPT_NAME, EMAIL, DEPT_NUM)

n  ASSIGN(배정받다) (TRANS_NUM, DRIVER_ID)

n  TAKE_CHARGE_OF(담당하다) (NUR_ID, ROOM_NUM)

n  CHECK_OPER(조회하다) (CODI_ID, OPER_NUM)

n  CHECK_PATIENT_ROOM(조회하다) (CODI_ID, ROOM_NUM)

n  CHECK_OPER(조회하다) (CODI_ID, OPER_NUM)

n  CHECK_LAB(조회하다) (CODI_ID, OPER_NUM)

n  VISIT(회진하다) (ROOM_NUM, DOC_ID)

n  CONTACT(연락하다) (NUR_ID, DOC_ID, CODI_ID)

n  PATIENT_DISEASE(지병) (PAT_ID, CHRONIC_DISEASE)

 

 

정규화

원래의 릴레이션을 무손실 분해함으로써 중복과 갱신 이상을 최소화하여 일관성과 정확성을 유지하는 과정이다. 부주의한 데이터베이스 설계는 제어할 수 없는 데이터 중복을 야기하여 여러 가지 갱신 이상을 유발하는데, 이러한 문제들을 가지고 있는 릴레이션 스키마를 함수적 종속성과 기본 키를 기반으로 분석하여 좋은 관계 데이터베이스 스키마를 생성한다.

 

제1정규형

n  DOCTOR(DOC_ID, SEX, DOC_NAME, PHONE_NUM, EMAIL, TITLE, OPER_NUM, SEC_NUM, OPER_DATE, OPER_TIME)

DOCTOR 릴레이션에서는OPER_DATE와 OPER_TIME 애트리뷰트가 각 의사들이 진행하는 수술 시간들의 집합 값을 가지므로 제1정규형을 만족하지 못한다. 따라서 DOCTOR(DOC_ID, SEX, DOC_NAME, PHONE_NUM, EMAIL, TITLE, SEC_NUM) 릴레이션과 OEPRATION(DOC_ID, OPER_NUM, OPER_DATE, OPER_TIME)릴레이션으로 나눈다.

DOCTOR(DOC_ID, SEX, DOC_NAME, PHONE_NUM, EMAIL, TITLE, SEC_NUM)

OEPRATION(DOC_ID, OPER_NUM, OPER_DATE, OPER_TIME)

 

n  LABORATORY(LABO_NAME, FLOOR, CHECK_PAT, CHECK_DATE, CHECK_TIME)

LABORATORY릴레이션에서는 CHECK_DATE와 CHECK_TIME 애트리뷰트가 각 검사실에서 진행하는 검사 시간들의 집합 값을 가지므로 제1정규형을 만족하지 못한다. 따라서LABORATORY(LABO_NAME,CHECK_PAT,CHECK_DATE,CHECK_TIME, FLOOR)릴레이션과CHECK_LAB(LABO_NAME, CHECK_PAT, CHECK_DATE, CHECK_TIME) 릴레이션으로 나눈다.

LABORATORY(LABO_NAME,CHECK_PAT,CHECK_DATE,CHECK_TIME, FLOOR)

CHECK_LAB(LABO_NAME, CHECK_PAT, CHECK_DATE, CHECK_TIME)

 

n  OPERATING_ROOM(OPER_NUM, FLOOR, OPER_PAT, OPER_DATE, OPER_TIME)

OPERATING_ROOM릴레이션에서는 OPER_DATE와 OPER_TIME 애트리뷰트가 각 수술실에서 진행하는 수술 시간들의 집합 값을 가지므로 제1정규형을 만족하지 못한다.따라서OPERATING_ROOM(OPER_NUM, FLOOR)릴레이션과 GET_OPER(OPER_NUM, OPER_PAT, OPER_DATE, OPER_TIME)릴레이션으로 나눈다.

OPERATING_ROOM(OPER_NUM, FLOOR)
GET_OPER(OPER_NUM, OPER_PAT, OPER_DATE, OPER_TIME)

 

제2정규형

n  DOCTOR(DOC_ID, SEX, DOC_NAME, PHONE_NUM, EMAIL, TITLE, SEC_NUM)

DOCTOR 릴레이션에서 SEX, DOC_NAME, PHONE_NUM, EMAIL 은 DOC_ID에 부분적으로 종속하며, TITLE은  DOC_ID와 SEC_NUM 키에 완전하게 함수적으로 종속된다. 따라서 TITLE애트리뷰트를 새로운 릴레이션에 포함시키고, DOC_ID를 외래키로 이 릴레이션에 포함시키며, 기본키를 (DOC_ID,SEC_NUM)으로 복합 애트리뷰트로 만든다. 따라서DOCTOR(DOC_ID, SEX, DOC_NAME, PHONE_NUM, EMAIL) 릴레이션과DOC_TITLE(DOC_ID, SEC_NUM, TITLE)릴레이션으로 나눈다.

DOCTOR(DOC_ID, SEX, DOC_NAME, PHONE_NUM, EMAIL)
DOC_TITLE(DOC_ID, SEC_NUM, TITLE)

 

n  NURSE(NUR_ID, NUR_NAME, PHONE_NUM, SEX, EMAIL, TITLE, DNO)

NURSE 릴레이션에서 NUR_NAME, PHONE_NUM, SEX, EMAIL은 NUR_ID에 부분적으로 종속하며, TITLE은 NUR_ID와 DNO키에 완전하게 함수적으로 종속된다. 따라서 TITLE 애트리뷰트를 새로운 릴레이션에 포함시키고, NUR_ID를 외래키로 이 릴레이션에 포함시키며, 기본키를(NUR_ID, DNO)으로 복합 애트리뷰트로 만든다. 따라서NURSE(NUR_ID, NUR_NAME, PHONE_NUM, SEX, EMAIL)릴레이션과 NUR_TITLE(NUR_ID, DNO, TITLE) 릴레이션으로 나눈다.

NURSE(NUR_ID, NUR_NAME, PHONE_NUM, SEX, EMAIL)
NUR_TITLE(NUR_ID, DNO, TITLE)

 

제3정규형

어떠한 키가 아닌 모든 애트리뷰트도 기본 키에 대해서 이행적으로 종속되지 않을때 제3정규형을 만족한다고 하는데, 모든 릴레이션에 대해서 어떤 애트리뷰트도 기본 키에 대해서 이행적으로 종속하거나, 추론되지 않기 때문에 제3정규형을 사용하지 않았다.

각 릴레이션별 정규화가 진행되지 않은 이유는 아래 따로 서술하였다.

BCNF

BCNF의 조건인 제3정규형을 모든 릴레이션이 만족을 했고, 모든 결정자가 후보 키였기 때문에 BCNF를 사용하지 않았다.

각 릴레이션별 정규화가 진행되지 않은 이유는 아래 따로 서술하였다.

 

역정규화

데이터들이 이온화 되어있어서 검색하는데 복잡하다. 따라서 검색 복잡도를 줄이고, 검색 질의들의 수행 속도를 높이기 위해서 역정규화를 실행하였다.

PATIENT(PAT_ID, CHARGE_DOC, CHARGE_NUR, PAT_ROOM, IN_DATE, OUT_DATE, REG_NUM) 릴레이션과PAT_INFO(REGIST_NUM, EMAIL, ADDRESS, PAT_NAME,  SEX, PHONE_NUM, BODY_HEAT, PULSE, BLOOD_PRESSURE, BREATH) 릴레이션으로 나눈 PATIENT와 PAT_INFO를 PATIENT로 다시 통합한다. 따라서 PATIENT(PAT_ID, EMAIL, ADDRESS, PAT_NAME, REGIST_NUM, SEX, PHONE_NUM, BODY_HEAT, PULSE, BLOOD_PRESSURSE, BERATH,CHARGE_DOC, CHARGE_NUR, PAT_ROOM, IN_DATE, OUT_DATE)

n  PATIENT(PAT_ID, EMAIL, ADDRESS, PAT_NAME, REGIST_NUM, SEX, PHONE_NUM, BODY_HEAT, PULSE, BLOOD_PRESSURSE, BERATH,CHARGE_DOC, CHARGE_NUR, PAT_ROOM, IN_DATE, OUT_DATE)

 

정규화한 릴레이션을 제외한 다른 릴레이션들을 정규화 하지 않은 이유

n  DRIVER(DRIVER_ID, DRIVER_NAME, SEX, PHONE_NUM, EMAIL, ROLE,CODI_NUM)

DRIVER릴레이션의 모든 애트리뷰트가 원잣값만을 갖고, 어떤 후보 키에도 속하지 않는 모든 애트리뷰트들이 DRIVER의 기본 키에 완전하게 함수적으로 종속되며된다.
키가 아닌 모든 애트리뷰트가 DRIVER 릴레이션의 기본 키에 이행적으로 종속되지 않기 때문이다.

 

n  CHART(CHART_ID, OPINION, PATIENT_ID, RECORDER)

CHART릴레이션에서 모든 애트리뷰트가 원잣값만을 갖고, 어떤 후보 키에 속하지 않는 모든 애트리뷰트들이 CHART의 기본 키에 완전하게 함수적으로 종속되며, 키를 제외한 모든 애트리뷰트가 CHART 릴레이션의 기본키에 이행적으로 종속되지 않기 때문이다.

 

 

n  ASSIGN(TRANS_NUM, DRIVER_ID)

ASSIGN 관계 릴레이션에서는 TRANS_NUM외래키를 TRANSPORT 릴레이션에서 가져오고, DRIVER_ID 외래키를 DRIVER릴레이션 에서 가져온다.
외래키로 가져온 TRANS_NUM 와 DRIVER_ID는 원잣값만을 갖고, 복합키를 제외한 애트리뷰트가 없기때문에 제2정규형, 제3정규형을 만족하기 때문에 정규화를 하지 않았다.

 

n  TAKE_CHARGE_OF(NUR_ID, ROOM_NUM)

TAKE_CHARGE_OF 관계 릴레이션에서는 NUR_ID 외래키를 NURSE 릴레이션에서 가져오고, ROOM_NUM 외래키를PATIENTS_ROOM릴레이션에서 가져온다.
복합키인 NUR_ID와 ROOM_NUM 은 원잣값만을 가지고, 복합키면서 키를 제외한 애트리뷰트가 없기때문에 정규화를 하지 않았다.

 

n  CHECK(CODI_ID, OPER_NUM)

CHECK 관계 릴레이션에서는 CODI_ID 외래키를 COORDINATOR 릴레이션에서 가져오고, OPER_NUM 외래키를 OPERATING_ROOM 릴레이션에서 가져온다. 복합키인 CODI_ID와 OPER_NUM은 원잣값만을 가지고, 복합키면서 키를 제외한 애트리뷰트가 없기때문에 정규화를 하지 않았다.

 

n  VISIT(ROOM_NUM, DOC_ID)

VISIT 관계 릴레이션에서는   PATIENTS_ROOM릴레이션에 포함된 ROOM_NUM 를 외래키로 가져오고, DOCTOR 릴레이션에 포함된 DOC_ID를 외래키로 가져온다. ROOM_NUM과 DOC_ID는 원잣값만을 가지고, 복합키면서 키를 제외한 애트리뷰트가 없기때문에 정규화를 하지 않았다.

 

n  CONTACT(NUR_ID, DOC_ID, CODI_ID)

CONTACT 관계 릴레이션에서는 NURSE 릴레이션에 포함된 NUR_ID를 외래키로 가져오고, DOCTOR 릴레이션에 포함된 DOC_ID를 외래키로 가져오며, COORDINATOR릴레이션에 포함된 CODI_ID를 외래키로 가져온다.
NUR_ID, DOC_ID와 CODI_ID 3개의 외래키가 복합키이며, 원잣값만을 갖는다. 그리고 3개의 키들을 제외한 애트리뷰트가 없기때문에 정규화를 하지 않았다.

 

 

n  PATIENT_DISEASE(PAT_ID, CHRONIC_DISEASE)

PATIENT_DISEASE릴레이션에서 PAT_ID는 PATIENT에 포함된 PAT_ID값을 가져온 외래키이고, PATIENT_DISEASE 릴레이션에서 기본키는 PAT_ID와 CRHONIC_DISEASE 애트리뷰트의 조합이다. 키를 제외한 애트리뷰트가 없기 때문에 정규화를 하지 않았다.

 

n  PROTECTOR(PAT_ID, NUR_ID, PROTECT_NAME, SEX, PHONE_NUM)

PATIENT 릴레이션에서 포함된 PAT_ID 를 외래키로 가져왔고, NURSE 릴레이션에서 포함된 NUR_ID 를 외래키로 가져왔다. PROTECTOR 릴레이션에 모든 애트리뷰트들은 원잣값만을 가지며, 어떤 후보 키에 속하지 않는 모든 애트리뷰트들이 PROTECTOR의 기본 키에 완전하게 함수적으로 종속되며, 키를 제외한 모든 애트리뷰트가 PROTECTOR릴레이션의 기본 키에 이행적으로 종속되지 않기 때문이다.

n  TRANSPORT(TRANS_NUM, TYPE)

TRANS_NUM과 TYPE은 원잣값만을 갖는다. TRANS_NUM이 기본키이고 TYPE은 TRANS_NUM에 완전하게 함수적으로 종속된다. 키를 제외한 TYPE 애트리뷰트는 TRANSPORT릴레이션의 기본 키에 이행적으로 종속되지 않기 때문에 정규화 하지 않았다.

 

n  SECTION(SEC_NAME, SEC_NUM)

SEC_NAME과 SEC_NUM은 원잣값만을 갖는다. SEC_NAME은 키본키이고, SEC_NUM은 SEC_NAME에 완전하게 함수적으로 종속된다. 키를 제외한 SEC_NUM 애트리뷰트는 SECTION릴레이션의 기본 키에 이행적으로 종속되지 않기 때문에 정규화 하지 않았다.

 

n  DEPARTMENT(DEPT_NUM, DEPT_NAME)

DEPARTMENT 릴레이션의 모든 애트리뷰트들은 원잣값만을 가지고, DEPT_NAME은 기본키인 DEPT_NUM에 완전하게 함수적으로 종속된다. 키를 제외한 DEPT_NAME 애트리뷰트는 DEPARTMENT릴레이션의 기본 키에 이행적으로 종속되지 않기 때문에 정규화 하지 않았다.

 

n  COORDINATOR(CODI_ID, CODI_NAME, SEX, PHONE_NUM, DEPT_NUM, EMAIL)

COORDINATOR릴레이션의 모든 애트리뷰트는 원잣값만을 가진다. DEPARTMENT 릴레이션에 포함된 DEPT_NUM을 외래키로 가져오고, 어떤 후보 키에도 속하지 않는 모든 애트리뷰트들은 COORDINATOR의 기본키인 CODI_ID에 완전하게 함수적으로 종속된다. 키가 아닌 모든 애트뷰트들은 COORDINATOR 릴레이션의 기본키에 이행적으로 종속되지 않기때문에 정규화를 하지 않았다.

 

n  PATIENTS_ROOM(ROOM_NUM, FLOOR, MAX_PATIENTS_NUM, CUR_PATIENTS_NUM, IN_DATE, OUT_DATE)

PATIENTS_ROOM 릴레이션의 모든 애트리뷰트들은 원잣값만을 가진다.어떤 후보 키에도 속하지 않은 모든 애트리뷰트들은 PATIENTS_ROOM릴레이션의 기본키인 ROOM_NUM에 완전하게 함수적으로 종속된다. 키가 아닌 모든 애트리뷰트들은 PATIENTS_ROOM 릴레이션의 기본키에 이행적으로 종속되지 않기 때문에 정규화를 하지 않았다.

 

n  BE_HOSPITALIZED(ROOM_NUM, STAY_PAT, IN_DATE, OUT_DATE)

SPATEINT릴레이션에서 포함된 PATIENT_ID를 외래키로 가져오고, 어떤 후보 키에도 속하지 않은 모든 애트리뷰트들은 BE_HOSPITALIZED 릴레이션의 기본 키인  ROOM_NUM에 완전하게 함수적으로 종속된다. 키가 아닌 모든 애트리뷰트들은 BE_HOSPITALIZED 릴레이션의 기본 키에 이행적으로 종속되지 않기 때문에 정규화를 하지 않았다.

 

n  CHECK_PATIENT_ROOM(CODI_ID, ROOM_NUM)

CHECK_PATIENT_ROOM 릴레이션에 포함된 CODI_ID와 ROOM_NUM 애트리뷰트는 각각 COORDINATOR릴레이션과, ROOM 릴레이션에 포함된 애트리뷰트값을 가져온 외래키 값이며, 두 애트리뷰트를 묶어 복합키로 사용한다. 두 애트리뷰트는 원잣값만을 가지며, 복합키인 두 애트리뷰트밖에 없기 때문에 정규화를 하지 않았다.