실전 데이터모델링 & 데이터베이스 설계와 구축

Presentation on theme: "실전 데이터모델링 & 데이터베이스 설계와 구축"— Presentation transcript:

1 실전 데이터모델링 & 데이터베이스 설계와 구축
(3.실전 데이터모델링 이슈) 李春植

2 3. 실전 데이터모델링 이슈

3 ▶주요이슈 ☞ M:N 관계 해소 방법 ☞ 1: 1 관계 해소 방법 ☞ 엔티티타입 통합 방법 ☞ 코드 엔티티타입 설계 방법
☞ 도미노 속성에 대한 모델링 방법 ☞ 메시지 엔티티타입 설계 방법 ☞ 이력 엔티티타입 설계 방법

4 ▶M:N관계 해소방법 ☞ M:N 관계의 해소방법 – 관계 엔티티타입 생성에 의한 통합

5 ▶M:N관계 생성 M:N 관계의 생성 (M:1) 하나의 강의는 여러 명의 수강생이 있을 수 있다
한 명의 학생은 여러 개의 강의를 수강할 수 있다 (1:M) 강의 강의코드 학생번호 (FK) 학생 학생번호 강의코드 (FK) (1:M)+(M:1) = (M:N) 한 명의 학생은 여러 개의 강의를 수강할 수 있고 하나의 강의는 여러 명의 수강생이 있을 수 있다

6 ▶M:N관계 해소방법 ☞ M:N 관계의 해소방법 - 기본 요금 납부 요금 납부 요금번호 납부번호 요금번호 납부번호 요금납부
요금번호 (FK) 납부번호 (FK) 관계 엔티티타입 생성

7 ▶M:N관계 해소방법 ☞ M:N 관계의 해소방법 – 식별자에 의한 통합 요금 납부 주식별자가 변경됨 요금번호 납부번호 요금
요금납부순차번호 요금번호 (FK)

8 ▶M:N관계 해소방법 ☞ M:N 관계의 해소방법 – 속성에 의한 통합 납부가 최대 2회 발생하는 것을 이용한 속성추가 요금
요금번호 납부번호 요금 납부 요금번호 납부번호 납부번호1 (FK) 납부번호2 (FK)

9 ▶1:1관계 해소방법 ☞ 1:1 관계의 해소방법 – 개별 엔티티타입 유지
☞ 1:1 관계의 해소방법 – 하나의 엔티티타입통합(완전통합) ☞ 1:1 관계의 해소방법 – 하나의 엔티티타입통합(부분통합) ☞ 1:1 관계의 해소방법 – 엔티티타입통합(수퍼타입)

10 ▶1:1관계 해소방법1 청구 출급 청구번호 출급번호 (FK) 청구금액 출금일자
☞ 1:1 관계의 해소방법 – 개별 엔티티타입 유지 청구 출급 청구번호 출급번호 (FK) 청구금액 출금일자

11 ▶ 1:1관계 해소방법2 청구출급 청구 출급 청구출급번호 청구번호 출급번호 (FK) 금액 청구금액 출금일자 출금일자
☞ 1:1 관계의 해소방법 – 하나의 엔티티타입통합(완전통합) 청구출급 청구 출급 청구출급번호 청구번호 출급번호 (FK) 금액 청구금액 출금일자 출금일자

12 ▶ 1:1관계 해소방법3 청구 출급 청구출급 청구번호 출급번호 발생순번 청구출급번호 청구금액 출금금액 청구일자 출금일자
☞ 1:1 관계의 해소방법 – 하나의 엔티티타입통합(부분통합) 청구 출급 청구출급 청구번호 출급번호 발생순번 청구출급번호 청구금액 청구일자 출금금액 출금일자 청구번호(FK) 청구출급구분코드 청구출급금액 청구출금일자

13 ▶ 1:1관계 해소방법4 ☞ 1:1 관계의 해소방법 – 엔티티타입통합(수퍼타입) 청구 청구출급 청구번호 청구출급번호 청구금액
청구일자 청구자명 작업일자 작업담당자 출급 출급번호 (FK) 출금금액 출금일자 출급자명 청구출급번호 (FK) 청구출급 청구출급번호 금액 청구출급구분코드

14 ▶엔티티타입 통합 엔티티타입 엔티티타입

15 장 점 단 점 ▶엔티티타입 통합의 장단점 종합적으로 정보를 조회하기에 용이함 불필요한 조인을 제거하여 성능을 향상시킨다.
장 점 단 점 종합적으로 정보를 조회하기에 용이함 불필요한 조인을 제거하여 성능을 향상시킨다. 비슷한 속성이 합해지므로 엔티티간 중복성이 제거됨 동일한 규칙이 적용되는 경우에 한번의 규칙만 적용하면 됨. ERD가 간결해진다. 물리적인 테이블의 관리 개수가 줄어든다. 업무확장에 따른 데이터모델의 확정성이 유연하지 않다. 업무흐름을 데이터모델로만 가지고 정확하게 이해하기 어렵다. 많은 양의 데이터가 한군데 집약되므로 성능 저하현상도 있을 수 있다.(디스크 배치, 파티셔닝을 잘못할 경우) 속성에 제약조건을 걸지 못할 수 있다. SQL에 체크 해야 할 조건이 증가한다.

16 ▶엔티티타입 통합의 이해 ☞ 엔티티타입이 통합되면 업무처리 프로세스가 변형되거나 화면 사용자 인터페이스가 바뀌는가? ?

17 ▶엔티티타입 통합 진행절차 엔티티타입, PRIMARY KEY, PK업무규칙 관계, FOREIGN KEY, FK업무규칙 속성,
속성업무규칙

18 ▶엔티티타입 통합 ☞ PRIMARY KEY가 동일한 엔티티타입의 통합-완전통합 등록자명 등록자 등록자주민등록번호 이름 우편번호
시도 시군구 읍면동 번지 휴대폰번호 전화번호 팩스번호 주소 접수일자 접수자 접수자주민등록번호 접수자명 등록자명

19 ▶엔티티타입 통합 ☞ PRIMARY KEY가 동일한 엔티티타입의 통합 - 추가속성 부동산소유자 소유자명 속성이 추가됨
부동산전세자 주민등록번호 전세자명 우편번호 시도 시군구 읍면동 번지 전자메일 전화번호 부동산관계자 관계자명 부동산소유자 소유자명 관계자구분 속성이 추가됨

20 ▶엔티티타입 통합 ☞ 엔티티타입 통합 수퍼서브타입으로 통합 부동산관계자 주민등록번호 관계자구분 관계자명 우편번호 시도 시군구
부동산전세자 주민등록번호 (FK) 전세금 부동산관계자 주민등록번호 관계자구분 관계자명 우편번호 시도 시군구 읍면동 번지 전자메일 전화번호 부동산소유자 부동산등록일 관계자구분에 의해 서브타입이 분리된다.

21 ▶엔티티타입 통합 ☞ PRIMARY KEY가 비슷한 엔티티타입 통합 할인대상고객 고객 고객번호 주민등록번호(AK) 우편번호
시도 시군구 읍면동 번지 휴대폰번호 전화번호 팩스번호 주소 등록일자 특별고객계약내용 활동마일리지 특별고객 주민등록번호 이름 계약내용 할인대상고객

22 ▶엔티티타입 통합 ☞ PRIMARY KEY, 도메인, 속성이 비슷한 엔티티타입 통합 작업관리 작업번호 내용 일자 장소
작업담당자 관련작업번호(FK) 작업완료 작업완료번호 작업완료내용 작업완료일자 작업장소 작업요청 작업요청번호 작업요청내용 작업요청일자

23 ▶엔티티타입 통합 ☞ 복합 PRIMARY KEY 구성 중 동일 속성에 의한 엔티티타입의 통합 주문 통합대상 엔티티타입 주문진행
선적단위 주문번호 선적단위번호 제품 제품번호 주문목록 주문번호 (FK) 제품번호 (FK) 선적단위번호 (FK) 주문 주문목록진행 주문진행번호 주문진행 통합대상 엔티티타입

24 ▶엔티티타입 통합 ☞ 복합 PRIMARY KEY 구성 중 동일 속성에 의한 엔티티타입의 통합 주문 통합된 엔티티타입 주문목록
선적단위 주문번호 선적단위번호 제품 제품번호 주문목록 주문번호 (FK) 제품번호 (FK) 선적단위번호 (FK) 주문 진행 주문진행번호 통합된 엔티티타입

25 ▶엔티티타입 통합 가이드 논리설계 단계에서는 가능한 모든 엔티티타입을 상세하게 표현
데이터베이스에 테이블로 설계하기 위한 물리설계 단계에서는 가능한 통합을 유도하여 표현 통합의 첫번째 기준은 트랜잭션이 통합하여 발생하는지 분리되어 발생하는지에 대한 사전조사 필요 데이터량/트랜잭션양이 많지 않은 경우 통합을 유도 데이터량/트랜잭션양이 많을 경우 트랜잭션의 유형에 따라 결정

26 ▶주요 사항에 대한 데이터모델링 코드 데이터모델링 도미노 속성 데이터모델링 메시지 데이터모델링 이력 데이터모델링

27 ▶코드 데이터모델링 ☞ 정보화시스템을 구현할 때 코드란 업무적으로 또는 정보화시스템에서
구분하기 용이하도록 간단하게 구분해놓은 단위를 말한다.

28 ▶코드표현 방법 2가지 첫 번째로 코드에 대해 한 가지 코드의 값이 반복적으로 나타나는 경우가 있다
예) 지불방법코드 {‘현금’,‘계좌이체’,‘신용카드결재’,‘휴대폰결재’} [A001] {‘01’,‘02’,‘03’,‘40’) 두 번째는 한 가지 코드에 대해 여러 개의 속성이 반복되어 나타나는 경향이 있는 코드값이 있을 수 있다. 예) 업부를 “01”로 인사부를 “02”로 총무부를 “03”으로 자재부 “04”, 기술연구부를 “05”로 정의 부서코드  {‘01’, ‘02’, ‘03’, ‘04’, ‘05}            부서장 {‘이춘식’, ‘김경재’, ‘황경빈’, ‘이현철’, ‘이동철’}            위치   {‘서울 여의도’,‘서울 서초구’,‘인천’, ‘인천’, ‘부산’}            역할   {‘영업’, ‘인사및급여’, ‘사무환경’,‘자재구매’, ‘기술연구’}

29 ▶코드 데이터모델링 부서 부서코드 부서명 부서역할 부서위치 부서장 신청자구분 코드 코드명 접수구분 접수방법 접수 접수번호
☞ 코드에 대해 한 가지 코드의 값이 반복적으로 나타나는 경우 데이터모델링 방법 - 초기모델 부서 부서코드 부서명 부서역할 부서위치 부서장 신청자구분 코드 코드명 접수구분 접수방법 접수 접수번호 접수구분코드 (FK) 접수자명 접수방법코드 (FK) 접수일자 신청자명 신청자구분코드 (FK) 부서코드 (FK)

30 ▶코드 데이터모델링 코드구분 코드구분명 상세코드 코드 코드명 CD001 접수방법코드 01 방문접수 02 인터넷접수 03
☞ 코드에 대해 한 가지 코드의 값이 반복적으로 나타나는 경우 데이터모델링 방법 – 코드 값 코드구분 코드구분명 상세코드 코드 코드명 CD001 접수방법코드 01 방문접수 02 인터넷접수 03 전화접수 CD002 접수구분코드 일반 특별 긴급 신청자구분코드 자연인 법인

31 ▶코드 데이터모델링 접수방법 코드구분 코드구분명 접수구분 상세코드 코드구분 (FK) 코드 신청자구분 코드명
☞ 코드에 대해 한 가지 코드의 값이 반복적으로 나타나는 경우 데이터모델링 방법 – 코드 값 모델링 상세코드 코드구분 (FK) 코드 코드명 코드구분 코드구분명 신청자구분 접수구분 접수방법 코드구분 코드구분명 상세코드 코드 코드명 CD001 접수방법코드 01 방문접수 02 인터넷접수 03 전화접수 CD002 접수구분코드 일반 특별 긴급 신청자구분코드 자연인 법인

32 코드 엔티티타입 설계방법 ▶코드 데이터모델링 코드구분 제약사항이 발생하는 구분 명을 ‘접수요금’을 등록한다. 코드구분명
☞ 코드에 대해 한 가지 코드의 값이 반복적으로 나타나는 경우 데이터모델링 방법 – 제약사항 추가 코드 엔티티타입 설계방법 상세코드 코드구분 (FK) 코드 코드명 코드규칙1 코드구분 코드구분명 코드규칙명1 제약사항이 발생하는 구분 명을 ‘접수요금’을 등록한다. 발생하는 상세내용을 등록한다. 일반 -1,000 특별 - 7,000 긴급 – 10,000

33 ▶코드 데이터모델링 ☞ 코드에 대해 여러 개의 코드의 값이 반복적으로 나타나는 경우 데이터모델링 방법
부서 부서코드 부서명 부서역할 부서위치 부서장 접수 접수번호 접수자명 접수일자 신청자명 부서코드 (FK) 속성이 여러개인 부서코드는 별도의 통합코드 엔티티타입이 필요하지 않다.

34 ▶코드값 처리 팁 코드에 대한 어플리케이션 설계 TIP ☞ 코드의 특징 ① 코드값은 잘 변하지 않음
② 데이터의 크기가 크지 않음 ③ 많은 엔티티타입에서 참조하고 있음 ④ 많은 테이블에서는 코드테이블을 코드 개수 만큼 다중 조인이 발생 ☞ 코드값 변환을 잘못하는 대표적으로 세 가지 유형 첫 번째 모든 코드변환을 데이터베이스가 있는 위치에서 조인에 의해 변환 SQL구문이 복잡해짐, 가독성 저하 두 번째는 SQL문장내에서 DECODE를 사용하여 코드와 코드값을 매핑(MAPPING) 코드값변환에 대한 별도의 작업(로직에서)은 필요하지 않지만 만약 코드가 변경되거나 추가라도 된다면 모든 SQL을 수정해야 하므로 코드가 변경될 때마다 관련 소스를 찾아 재 컴파일이 발생해야 함 세 번째는 응용어플리케이션에서 코드와 코드값을 변환 소스에 코드값을 기술하는 방법으로 역시 코드가 변경될 때마다. 모든 소스에 대해 수정과 재 컴파일 발생해야 하는 치명적인 결함을 가지고 있음

35 ▶코드값 처리 팁 코드에 대한 어플리케이션 설계 TIP ☞ 코드값 처리를 위한 효율적인 방법
첫 번째는 DBMS Function을 이용한 코드변환 두 번째는 응용 어플리케이션의 메모리에 공통코드 캐쉬 후 변환 을 사용하는 것이 효율적임

36 ▶도미노 속성 ☞ 앞의 값에 규칙적인 제약이 연쇄적으로 발생하는 경우를 도미노속성이라 한다.

37 ▶도미노 속성구성 도미노 구분 순번 도미노1 도미노2 도미노3 도미노4 코드 DOM01 1 CD001 A CD013 1001
OA 2 3 02 CV 4 1002 FS 5 B DOM02 CD002 CD783 AA CD227 9 CD107 A33 B22 BB C55

38 ▶도미노 속성 데이터모델링 ☞ 도미노속성 데이터모델 - 속성이용 코드구분 코드구분명 코드규칙명 도미노 도미노구분 도미노순번
코드1 (FK) 코드구분1 (FK) 코드2 (FK) 코드구분2 (FK) 코드3 (FK) 코드구분3 (FK) 코드4 (FK) 코드구분4 (FK) 상세코드 코드구분 (FK) 코드 코드명 코드규칙1 코드구분 코드구분명 코드규칙명

39 ▶도미노 속성 데이터모델링 ☞ 도미노속성 데이터모델 - BOM(BILL OF MATERIAL)이용 코드구분명 코드규칙명
상위코드구분 상위코드 상세코드 코드구분 (FK) 코드명 코드규칙1 코드구분명 코드규칙명

40 ▶도미노 속성 활용 ☞ 도미노속성과 USER INTERFACE CD001-A CD001-B A CD013-101
CD723-OA OA 제약

41 ▶메시지 ☞ 메시지는 사용자가 시스템을 조작하면서 받는 정보시스템의 반응에 해당된다.

42 ▶메시지처리 방법 세가지 첫 번째 방법은 모든 개발소스에 메시지를 코딩하여 개발하는 방법
두 번째 방법으로는 헤더파일이나 클라이언트 테스트파일에 메시지를 정의하고 어플리케이션에서 임포트하여 사용하는 방법 세 번째는 데이터베이스에 저장하고 모든 개발자가 데이터베이스를 통해서 메시지의 내용을 가져와 보여주는 방법이 있을 수 있다.

43 ▶메시지종류 저장되었습니다.

44 ▶메시지 조사

45 ▶메시지 모델링 방법1. 하나의 엔티티타입으로 설계 방법2. 응답을 분리하여 설계 메시지 메시지번호 메시지 메시지유형
메시지내용 조치번호 (FK) 메시지유형 메시지내용 조치메시지 조치 조치번호 조치내용

46 ▶이력관리 이력관리란… 발생 업무단위 시작 종료 동일한 관리항목의 반복적인 발생
하나의 업무단위가 시간흐름에 따라 발생하는 과거와 현재 데이터를 지속적으로  유지하는 관리방법을 이력(HISTORY)관리라 한다. 동일한 관리항목의 반복적인 발생

47 ▶이력의 세가지 유형 변경이력 발생이력 진행이력 주문변경 계약변경 급여계산 요금청구 공사진행 접수진행

48 ▶이력의 데이터모델링 ☞ 엔티티타입이 분리된 이력 데이터모델 유형 변경이력 공사 공사번호 공사명 공사개요 착공일자 공사장소
사원 사원번호 우편번호 주소 전화번호 휴대폰번호 입사일자 급여 사원번호 (FK) 급여일자 갑근세 주민세 의료보험료 국민보험료 실수령급여액 주문변경 주문번호 (FK) 변경일자 변경자사번 주문수량 수수료 변경사유 주문 주문번호 고객번호 접수일자 담당부서 담당자사번 발생이력 진행이력 공사진행 공사번호 (FK) 진행상태코드 진행감독자명 진행상태시작일자 진행상태종료일자

49 ▶이력의 데이터모델링 ☞ 엔티티타입이 분리된 이력 데이터모델 유형 공사 공사진행 공사번호 공사번호 (FK) 공사명
공사개요 착공일자 공사장소 공사진행 공사번호 (FK) 진행상태시작일자 진행상태 진행감독자명 진행상태종료일자

50 ▶이력관리-진행이력 ☞ 진행상태 종료일자가 없는 경우 ☞ 진행상태 종료일자가 있는 경우
SELECT 진행시작1.공사번호, 진행시작1.진행상태 FROM 공사진행 진행시작1, (SELECT 공사번호, MAX(진행상태시작일자) 진행상태시작일자                            FROM 공사진행                            WHERE 진행상태시작일자 <= ‘2002년 7월 15일’                            GROUP BY 공사번호) 진행시작2 WHERE 진행시작1.공사번호 = 진행시작2.공사번호   AND 진행시작1.진행상태시작일자 = 진행시작2.진행상태시작일자 ☞ 진행상태 종료일자가 있는 경우 SELECT 공사진행.공사번호, 공사진행.진행상태 FROM 공사진행 WHERE 공사진행.진행상태시작일자 <=‘2002년 7월 15일’   AND 공사진행.진행상태종료일자 > ‘2002년 7월 15일’

51 사업부코드 ‘1001’에 대해서 가장 최근에 변경된 주문번호, 주문수량을 가져오는 SQL문장
▶이력관리-변경이력 변경이력 데이터조회 SELECT 주문변경1. 주문번호, 주문변경1.주문수량 FROM 주문변경 주문변경1, (SELECT 주문번호, MAX(변경일자) 변경일자 FROM 주문변경 WHERE 사업부코드 = '1001' GROUP BY 주문번호) 주문변경2 WHERE 주문변경1.주문번호 = 주문변경2.주문번호 AND 주문변경1.변경일자 = 주문변경2.변경일자 사업부코드 ‘1001’에 대해서 가장 최근에 변경된 주문번호, 주문수량을 가져오는 SQL문장

52 사업부코드 ‘1001’에 대해서 가장 최근에 변경된 주문번호, 주문수량을 가져오는 SQL문장
▶이력관리-변경이력 변경이력 데이터조회 SELECT 주문변경. 주문번호, 주문변경.주문수량 FROM 주문변경 WHERE 주문변경.사업부코드 = '1001' AND 주문변경.최신여부 = 'Y' 사업부코드 ‘1001’에 대해서 가장 최근에 변경된 주문번호, 주문수량을 가져오는 SQL문장

53 시스템의 법칙과 만남의 법칙 엔티티타입의 잘못 선정된 식별자의 구조는 상대방의 엔티티타입과 올바른 관계를 맺을 수 없듯이
사람의 원죄적 속성은 하나님과의 관계를 단절 시켰습니다. 예수그리스로를 통한 죄의 고백과 하나님과의 만남은 각 사람에게 꼭 필요한 사실이 되었습니다.