Transcript 선수 팀ID(PK)
산업공학전산실무 09. 테이블 설계 검증 동아대학교 산업경영공학과 김준우 Kim Jun Woo Industrial Management System ‹#› 데이터베이스 설계 개요 일반적인 데이터베이스 설계 절차 절차 내용 산출물 데이터베이스를 적용할 업무에 대한 기본 분석 업무기능분해도 • 요구사항 분석 • 사용자, 주요 기능 등을 식별 개념 모델링 • 관리 대상 개체 및 개체 간 관계 식별 논리 모델링 • 물리 모델링 • Kim Jun Woo 식별된 개체 및 관계를 데이터베이스 관리 시스템에 표현하는 방법 결정 데이터베이스 시스템에 설계 내역 등록 Industrial Management System 개체관계도 (ERD) 데이터베이스 스키마 데이터베이스 ‹#› 정규화 개요 논리 모델링 – 개념 DBMS에 등록할 테이블 구조 (스키마) 도출 – 주요 수행 작업 1) Mapping rule : 초기 스키마 도출 2) 정규화 : 스키마 오류 검증 및 수정 정규화 (normalization) – 개념 스키마의 이상 현상 제거 올바른 스키마 도출 – 주요 특징 Kim Jun Woo 기존 테이블 분리하여 작은 테이블 생성 한 개 테이블에 한 개 주제 관련 자료만 기록되도록 설계 Industrial Management System ‹#› 정규화 개요 이상 현상 – 데이터 작업 시 발생하는 오류 1) 삭제 2) 삽입 3) 갱신 여러 개 (delete) 이상 (insert) 이상 (update) 이상 주제 한 테이블에서 관리하여 발생 – 예) 프로야구 선수 데이터 ID(PK) 1 2 3 4 5 Kim Jun Woo 세 가지 이상 현상 모두 발생 이름 홍성흔 박용택 김현수 정근우 김동주 포지션 1루수 좌익수 좌익수 2루수 3루수 팀명 롯데 LG 두산 SK 두산 연고 부산 서울 서울 인천 서울 홈 사직 잠실 잠실 문학 잠실 출장경기 117 109 129 123 101 Industrial Management System 타율 0.375 0.374 0.350 0.353 0.349 출루율 0.435 0.419 0.445 0.487 0.450 장타율 0.539 0.586 0.586 0.437 0.595 ‹#› 정규화 개요 이상 현상 – 삭제 이상 ID 1 2 3 4 5 레코드 삭제 시, 남겨야 할 자료까지 함께 소실 예) ‘정근우’ 선수 레코드 삭제 SK구단의 연고, 홈구장 정보도 소실 이름 홍성흔 박용택 김현수 정근우 김동주 포지션 1루수 좌익수 좌익수 2루수 3루수 팀명 롯데 LG 두산 SK 두산 연고 부산 서울 서울 인천 서울 홈 사직 잠실 잠실 문학 잠실 출장경기 117 109 129 123 101 타율 0.375 0.374 0.350 0.353 0.349 출루율 0.435 0.419 0.445 0.487 0.450 장타율 0.539 0.586 0.586 0.437 0.595 – 삽입 이상 ID 1 2 3 4 5 필요한 내용 기록이 곤란한 현상 예) ‘한화’ 구단 ‘대전’ 연고, ‘한밭’ 홈 등록 선수 없이 등록 불가 이름 홍성흔 박용택 김현수 정근우 김동주 Kim Jun Woo 포지션 1루수 좌익수 좌익수 2루수 3루수 팀명 롯데 LG 두산 SK 두산 연고 부산 서울 서울 인천 서울 홈 사직 잠실 잠실 문학 잠실 출장경기 117 109 129 123 101 Industrial Management System 타율 0.375 0.374 0.350 0.353 0.349 출루율 0.435 0.419 0.445 0.487 0.450 장타율 0.539 0.586 0.586 0.437 0.595 값 목록 발생도 일종의 삽입 이상 ‹#› 정규화 개요 이상 현상 – 갱신 이상 ID 1 2 3 4 5 이름 홍성흔 박용택 김현수 정근우 김동주 ID 1 2 3 4 5 한 가지 정보 변경 시, 이를 여러 번에 걸쳐 반영해야 하는 현상 예) ‘홍성흔’ 선수 소속 ‘두산’으로 변경 연고, 홈까지 수정 필요 팀명 롯데 LG 두산 SK 두산 연고 부산 서울 서울 인천 서울 홈 사직 잠실 잠실 문학 잠실 출장경기 117 109 129 123 101 타율 0.375 0.374 0.350 0.353 0.349 출루율 0.435 0.419 0.445 0.487 0.450 장타율 0.539 0.586 0.586 0.437 0.595 예) ‘두산’ 구단이 연고를 수원으로 이전 여러 선수 정보 모두 수정 이름 홍성흔 박용택 김현수 정근우 김동주 Kim Jun Woo 포지션 1루수 좌익수 좌익수 2루수 3루수 포지션 1루수 좌익수 좌익수 2루수 3루수 팀명 롯데 LG 두산 SK 두산 연고 부산 서울 서울 인천 서울 홈 사직 잠실 잠실 문학 잠실 출장경기 117 109 129 123 101 Industrial Management System 타율 0.375 0.374 0.350 0.353 0.349 출루율 0.435 0.419 0.445 0.487 0.450 장타율 0.539 0.586 0.586 0.437 0.595 ‹#› 종속 관계 종속 관계 – 두 개 필드 값들 사이 연관성 현재 스키마 이상 현상 발생 가능성 평가에 활용 – 올바른 종속 관계 기본 키 값 결정 나머지 필드 값 한 개씩 결정되어야 함 올바른 종속 관계 갖는 스키마는 이상 현상 없음 학생 예) 학번(PK) 2010002 2010018 … 이름 김철수 박영희 … 학과 산업경영 기계공학 … 평점 3.24 3.56 … 성별 남 여 … – 올바르지 못한 종속 관계 Kim Jun Woo 1) 값 목록 : 기본 키 값 하나에 대해 나머지 필드 값 여러 개인 경우 2) 이행 종속 : 기본 키 아닌 필드끼리 종속관계 존재 3) (참조) 부분 종속 : 복합키 중 일부에만 종속 Industrial Management System ‹#› 종속 관계 잘못된 종속 관계 – 1) 값 목록의 발생 ID(PK) 1 2 3 4 5 ID(PK) 1 2 3 4 5 Kim Jun Woo 예) 프로야구 선수 최근 (2012, 2013) 연도별 성적 데이터 이름 홍성흔 박용택 김현수 정근우 김동주 포지션 1루수 좌익수 좌익수 2루수 3루수 출장경기 111,117 122,109 120,129 124,123 95,101 타율 0.315,0.375 0.324,0.374 0.362,0.350 0.311,0.353 0.299,0.349 연도 2012,2013 2012,2013 2012,2013 2012,2013 2012,2013 주의) 필요 항목을 개별 필드로 모두 나누는 것 곤란 이름 홍성흔 박용택 김현수 정근우 김동주 포지션 2012 2013 2012 2013 1루수 좌익수 좌익수 2루수 3루수 출장경기 111 122 120 124 95 출장경기 117 109 129 123 101 타율 0.315 0.324 0.362 0.311 0.299 타율 0.375 0.374 0.350 0.353 0.349 Industrial Management System 현재 스키마로 이후 시즌 데이터 기록 불가 ‹#› 종속 관계 잘못된 종속 관계 – 2) 이행 종속의 발생 ID(PK) 1 2 3 4 5 Kim Jun Woo 예) 프로야구 선수 데이터 (한해 성적) 소속, 연고, 홈 필드 사이 종속 관계 존재 이름 홍성흔 박용택 김현수 정근우 김동주 포지션 1루수 좌익수 좌익수 2루수 3루수 팀명 롯데 LG 두산 SK 두산 연고 부산 서울 서울 인천 서울 홈 사직 잠실 잠실 문학 잠실 출장경기 117 109 129 123 101 Industrial Management System 타율 0.375 0.374 0.350 0.353 0.349 출루율 0.435 0.419 0.445 0.487 0.450 장타율 0.539 0.586 0.586 0.437 0.595 ‹#› 정규형 정규형 (normal form) – 바람직한 스키마 형태 올바른 종속 관계, 이상 현상 없음 – 정규형 종류 1종 정규형 : 값 목록 발생하지 않는 경우 2종 정규형 : 1종 정규형 + 부분 종속 발생하지 않는 경우 3종 정규형 : 1, 2종 정규형 + 이행 종속 발생하지 않는 경우 기타 : 4, 5종, 보이스코드 정규형 등 – 정규화 Kim Jun Woo 테이블 스키마 정규형으로 만드는 작업 1종 정규형부터 순서대로 진행 일반적으로 3종까지 고려 (본 과목에서는 1, 3종 두 가지만 고려) Industrial Management System ‹#› 제 1 정규화 제 1 정규화 – 값 목록 발생하는 경우, 이를 제거하기 위해 스키마 수정 1종 정규형 스키마 얻어짐 – 제 1 정규화 방법 ID(PK) 1 2 3 4 5 Kim Jun Woo 목록 발생 필드 또는 필드 그룹을 별도 테이블로 분해 원래 테이블과 1 대 다 관계 형성 (원래 테이블이 1) 예) 프로야구 선수 연도별 성적 관리 이름 홍성흔 박용택 김현수 정근우 김동주 포지션 1루수 좌익수 좌익수 2루수 3루수 선수 출장경기 111,117 122,109 120,129 124,123 95,101 타율 0.315,0.375 0.324,0.374 0.362,0.350 0.311,0.353 0.299,0.349 연도 2011,2012 2011,2012 2011,2012 2011,2012 2011,2012 Industrial Management System 값 목록 발생하는 필드 그룹 ‹#› 제 1 정규화 제 1 정규화 – 제 1 정규화 방법 예) 프로야구 선수 연도별 성적 관리 선수 분해 필요 시, 신규 테이블에 적절한 기본 키 부여 원래 테이블을 1, 신규 테이블 N으로 1 대 다 관계 형성 ID(PK) 이름 포지션 출장경기 선수 ID(PK) 이름 ID(PK) 이름 포지션 연도 성적 성적ID(PK) 출장경기 타율 포지션 선수 성적 타율 성적 성적ID(PK) 선수ID(FK) 출장경기 연도 타율 연도 외래 키 부여 Kim Jun Woo Industrial Management System ‹#› 제 1 정규화 제 1 정규화 – 제 1 정규화 결과 관찰 ID(PK) 1 2 3 4 5 ID(PK) 1 2 3 4 5 Kim Jun Woo 예) 프로야구 선수 연도별 성적 관리 이름 홍성흔 박용택 김현수 정근우 김동주 선수 이름 홍성흔 박용택 김현수 정근우 김동주 포지션 1루수 좌익수 좌익수 2루수 3루수 포지션 1루수 좌익수 좌익수 2루수 3루수 선수 출장경기 111,117 122,109 120,129 124,123 95,101 타율 0.315,0.375 0.324,0.374 0.362,0.350 0.311,0.353 0.299,0.349 연도 2011,2012 2011,2012 2011,2012 2011,2012 2011,2012 성적 성적ID(PK) 선수ID(FK) 출장경기 1 1 111 2 1 117 3 2 122 4 2 109 5 3 120 6 3 129 7 4 124 … … … Industrial Management System 타율 0.315 0.375 0.324 0.374 0.362 0.350 0.311 … 연도 2011 2012 2011 2012 2011 2012 2011 … ‹#› 제 1 정규화 제 1 정규화 – 기타) 개념 모델링에서 올바른 개체 및 관계 도출 시, 1종 정규형 얻어짐 – 예) 프로야구 선수의 연도별 성적 ‘선수’는 ‘성적’을 기록 (별도 개체 추출) 적절한 관계 설정 Mapping 규칙에 의해 올바른 스키마 얻어짐 선수ID 이름 선수 포지션 성적ID 기록 출장경기 성적 타율 이름 포지션 선수 연도 올바른 ERD (별도 개체 서로 분리됨) Kim Jun Woo 선수ID Industrial Management System 출장경기 타율 연도 잘못된 ERD (여러 개체 한 곳에 뭉쳐짐) ‹#› 제 3 정규화 제 3 정규화 – 이행 종속을 제거하기 위한 스키마 수정 이행 종속 : 기본 키 아닌 필드 간 종속 관계 제 1, 제 2 정규화 작업 이후 수행 본 과목에서는 제 2정규화 생략 – 제 3 정규화 방법 ID(PK) 1 2 3 4 5 이행 종속 발생 필드 그룹을 별도 테이블로 분해 원래 테이블과 적절한 관계 형성 예) 프로야구 선수 데이터 (한 해 성적만 관리) 이름 홍성흔 박용택 김현수 정근우 김동주 Kim Jun Woo 포지션 1루수 좌익수 좌익수 2루수 3루수 팀명 롯데 LG 두산 SK 두산 선수 연고 부산 서울 서울 인천 서울 홈 사직 잠실 잠실 문학 잠실 출장경기 117 109 129 123 101 Industrial Management System 타율 0.375 0.374 0.350 0.353 0.349 출루율 0.435 0.419 0.445 0.487 0.450 장타율 0.539 0.586 0.586 0.437 0.595 이행종속 발생하는 필드 그룹 ‹#› 제 3 정규화 제 3 정규화 – 제 3 정규화 방법 예) 프로야구 선수 데이터 (한 해 성적만 관리하는 것으로 가정) 분해 ID(PK) 필요 시 신규 테이블에 적절한 기본 키 부여 적절한 관계 표현 (선수 N, 팀 1이 적절) Kim Jun Woo 이름 선수 포지션 출장경기 ID(PK) 타율 이름 이름 팀 팀명 연고 선수 포지션 출장경기 팀ID(PK) 팀 팀명 연고 Industrial Management System 팀명 장타율 선수 포지션 출장경기 팀ID(PK) ID(PK) 출루율 타율 팀 연고 홈 출루율 장타율 출루율 장타율 팀ID(FK) 홈 타율 홈 외래 키 부여 ‹#› 제 3 정규화 제 3 정규화 – 제 3 정규화 결과 관찰 예) 프로야구 선수 데이터 (한 해 성적만 관리) 제 3정규형 스키마 얻어짐, 이상 현상 방지됨 선수 ID(PK) 1 2 3 4 5 팀ID(PK) 1 2 3 4 5 Kim Jun Woo 이름 홍성흔 박용택 김현수 정근우 김동주 포지션 출장경기 타율 1루수 117 0.375 좌익수 109 0.374 좌익수 129 0.350 2루수 123 0.353 3루수 101 0.349 팀 팀명 연고 롯데 부산 LG 서울 두산 서울 SK 인천 두산 서울 출루율 0.435 0.419 0.445 0.487 0.450 장타율 팀ID(FK) 0.539 1 0.586 2 0.586 3 0.437 4 0.595 3 홈 사직 잠실 잠실 문학 잠실 Industrial Management System ‹#› 제 3 정규화 제 3 정규화 – 기타 개념 모델링에서 올바른 개체 및 관계 도출 시, 3종 정규형 얻어짐 – 예) 프로야구 선수 데이터 (한 해 성적만 관리) 선수ID 선수 개체와 팀 개체 별도 추출 필요 적절한 관계 설정 Mapping 규칙에 의해 올바른 스키마 도출됨 이름 선수 출장경기 타율 팀ID 포지션 소속 장타율 팀명 팀 연고 홈 선수, 팀을 한 개체 표현 시 잘못된 스키마 얻어짐 출루율 올바른 ERD (별도 개체 서로 분리됨) Kim Jun Woo Industrial Management System ‹#› 정규화 정리 정규화 수행 단계 – 1) Mapping 규칙으로 얻은 스키마 검증 레코드 몇 개 입력해보고 값 목록, 이행 종속 발생 여부 관찰 – 2) 이상 현상 발생 시 정규화 수행 값 목록 발생 시 : 제 1정규화 이행 종속 발생 시 : 제 3정규화 – 3) 최종 테이블 스키마 도출 원래 스키마가 분해되어 작은 테이블로 쪼개짐 정규형 테이블 스키마 – 올바른 테이블 스키마 Kim Jun Woo 서로 다른 개체 서로 다른 테이블로 표현 여러 대상 정보 한 테이블에 포함 시 이상 발생 Industrial Management System ‹#›