08. 관계와 테이블 설계 동아대학교 산업경영공학과 김 준 우

08. 관계와 테이블 설계 동아대학교 산업경영공학과 김 준 우

테이블 관계형 DB의 자료 저장 기본 구조 레코드 : 1개 사례 (행) 필드 : 1개 특성 (열) 필요한 경우 복수의 테이블 생성하여 사용 1개 테이블은 1종류 대상만 관리 서로 다른 대상은 서로 다른 테이블에 저장 테이블 간 관계(relation) 서로다른 테이블 레코드 간 대응 관계 연관된 테이블 사이에 존재 가능 공통 속성 이용하여 표현 1. 복수의 테이블

복수의 테이블 예) 프로야구 관련 자료 관리하고자 하는 경우 관리 대상 1 : 선수 관리 대상 2 : 팀 스키마 기본 설계 레코드 간 대응 관계 존재 1명의 선수 : 1개 팀에 소속 1개 팀 : 여러 명의 선수를 포함 참조) 1대 다 관계 1. 복수의 테이블 별개 테이블 필요 기본 키 기본 키 선수 테이블 팀 테이블

테이블 간 관계 표현 한 쪽 테이블 기본 키를 다른 쪽 테이블 필드로 삽입 외래키(foreign key) : 상대 테이블 기본 키 가져와 만든 필드 주의) 상대방 1개 레코드와 대응되는 테이블에 외래 키 삽입 1. 복수의 테이블 기본 키 기본 키 외래 키 선수 테이블 팀 테이블 ( O ) 기본 키 기본 키 외래 키 선수 테이블 팀 테이블 ( X )

외래 키 포함하는 테이블 관찰 레코드 간 대응관계 파악 가능 예) 올바른 외래 키 생성한 경우 예) 외래 키 잘못 된 경우 1. 복수의 테이블 선수 테이블 팀 테이블 ( O ) 선수 테이블 팀 테이블 ( X ) 값 목록 발생하는 테이블 : 잘못된 설계

복수 테이블 생성 기본적인 생성 절차 반복 외래 키는 원래 필드와 데이터 형식 일치해야 함 외래 키는 디자인 단계에서 설정하지 않음 생성 예) 2. 복수의 테이블 생성 선수 테이블 설계 내역 팀 테이블 설계 내역

관계 설정 복수 테이블 생성 탐색 창에서 테이블 여러 개 조회됨 외래키의 설정 [관계] 생성하여 등록 예) 2. 복수의 테이블 생성 메뉴 [데이터베이스 도구]  [관계] 버튼 관계 설정 대상 테이블 명시 선수, 팀 테이블 모두 필요, Shift 누르고 클릭하여 선택 [추가]  [닫기]

관계 설정 관계 창 선택된 테이블들의 필드 목록 및 기본 키 표현 외래 키 지정 해당 기본 키 필드를 상대 외래키에 드래그하여 설정 예) 선수 테이블 [팀ID] : 팀 테이블 [팀ID] 가져온 외래키 2. 복수의 테이블 생성 드래그

관계 설정 관계 편집 창 생성할 관계 세부 사항 지정 참조 무결성 유지 : 외래키 관계 위배 시 데이터 작업 불가 관련 필드 업데이트 : 기본 키 변경 시, 상대 외래키 함께 변경 관련 레코드 모두 삭제 : 기본 키 삭제 시, 상대 외래키 모두 삭제 관계 종류 일대다 관계 : 기본키 테이블(팀) 레코드 1개에 외래키 테이블(선수) 레코드 여러 개 대응될 수 있음을 의미 2. 복수의 테이블 생성 일대일 관계 : 외래키 필드에 중복 불가 인덱스 설정할 경우 표시됨

설정된 관계 확인 관계 창에서 조회 가능 기본 키-외래 키 선으로 연결됨 닫기 버튼 클릭하여 작업 내용 저장 2. 복수의 테이블 생성 일대다 관계 의미 : 팀 1개에 선수 여러 명이 대응됨을 의미

테이블 관계 이용 예) 1) 팀 테이블 레코드 입력 2) 선수 테이블 레코드 입력 3. 관계 설정된 테이블 이용

테이블 관계 이용 예) 3) 팀 테이블 관찰 1대다 관계의 ‘1’에 해당하는 테이블 좌측에 [+] 버튼 : 클릭 시 대응되는 상대 테이블 레코드 조회 예) 3. 관계 설정된 테이블 이용

관계 생성 시의 옵션 1) 참조 무결성 소속 관계 위배하는 레코드 기록 불가 예) 선수 테이블에 소속 팀ID=5인 레코드 입력 : 해당 ID의 팀 없음 3. 관계 설정된 테이블 이용 확인  ESC 눌러 취소

관계 생성 시의 옵션 2) 관련 필드 모두 업데이트 기본 키 변경시 상대방 외래키 값 함께 갱신 예) 팀 테이블 ‘롯데’팀 ID를 4번으로 수정 선수 테이블 대응 레코드 함께 갱신됨 3. 관계 설정된 테이블 이용

관계 생성 시의 옵션 3) 관련 레코드 모두 삭제 1쪽 테이블 레코드 삭제 : 다쪽 테이블 대응되는 레코드 함께 삭제 예) 팀 테이블 ‘롯데’팀 레코드 삭제 선수 테이블 대응 레코드 함께 삭제됨 3. 관계 설정된 테이블 이용

일반적인 데이터베이스 설계 절차 4. 데이터베이스 설계 개요

요구사항 분석 개념 시스템(데이터베이스)이 가져야 할 기능 정의 수행 방법 고객(사용자) 인터뷰, 타 시스템 벤치마킹, 브레인스토밍 등 산출물 요구사항 명세서 업무기능분해도 유즈케이스 (usecase) 다이어그램 등 5. 요구사항 분석

업무기능분해도 시스템(데이터베이스) 기능을 계층적으로 정리한 그림 또는 표 수준별로 정리, 트리 형태로 정리 도표의 전개 포괄적인 기능하위에 세부적인 기능추가 가급적 상세한 수준까지 전개 예) 프로야구 정보 관리 시스템 5. 요구사항 분석 Level 1 Level 2 Level 3 프로야구 정보 관리 시스템 선수 정보 관리 등록 선수 목록 조회 선수 개인 정보 편집 선수 상세 정보 조회 팀 정보 관리 구단 목록 조회 구단 정보 편집 개인 성적 순위 조회 타율 순위 조회 장타율 순위 조회 출루율 순위 조회

개념 모델링 데이터관리 대상을 개념적으로 표현 일반적으로 도식화 ( ERD : 개체 관계도 ) 1) 개체 추출 개체 : 저장 및 관리 대상, 직사각형으로 표현 일반적개념, 다양한 구체적 사례를 포함 업무기능분해도 주요 명사에 집중하여 추출 예) 프로야구 관련 데이터 6. 개념 모델링 개체 (일반적 개념) 선수 팀 박석민 손아섭 김현수 윤석민 … 삼성 라이온즈 롯데 자이언츠 두산 베어즈 기아 타이거즈 … 설계 단계에서는 개체에 집중 사례 (구체적 대상)

개체 기타 개념적 개체도 존재 물리적 실체가 없는 개체 물리적 개체 : 고객, 학생, 선수, 물품 등 개념적 개체 : 교과목, 구매내역 등 개체의 활용 개체 : 향후 테이블 1개로 표현될 가능성 존재 예) 현재 추출한 개체 6. 개념 모델링 선수 팀

개념 모델링 2) 개체 속성 및 식별자 추출 속성 : 개체의 사례가 갖는 여러 가지 특성, 타원으로 표기 식별자 : 유일한 사례 식별 가능한 속성, 타원 내 밑줄 표기 해당되는 개체에 선으로 연결 참조) 식별자 없을 시, 적절히 추가하여 사용 가능 예) 프로야구 관련 데이터 각 개체에 대하여 기록 필요한 특성들 추출 6. 개념 모델링 포지션 타율 팀명 이름 홈런 모기업 연고지 선수 팀 선수ID 팀ID

속성 기타 속성의 활용 속성 : 향후 소속 테이블의 필드 1개로 표현될 가능성 존재 식별자 : 향후 소속 테이블의 기본 키역할 수행 가능성 존재 식별자 사용가능 속성 없는 경우, 순차적으로 정수 할당할 필드 새로 부여 예) 선수ID : 1, 2, 3, … 각 속성의 대략적인 도메인 함께 결정 바람직 6. 개념 모델링 포지션 (문자열) 타율 (실수) 팀명 (문자열) 이름 (문자열) 홈런 (정수) 모기업 (문자열) 연고지 (문자열) 선수 팀 선수ID (정수) 팀ID (정수)

개념 모델링 3) 관계 표현 관계 : 2개 개체 간 연관성, 개체 사이 마름모로 표현 복수의 개체 추출된 경우 반드시 분석 예) 프로야구 관련 데이터 정리 선수, 팀 간 “소속”관계 존재 6. 개념 모델링 선수는 팀에 “소속” 되어짐 팀은 선수를 “소속” 시킴 포지션 (문자열) 타율 (실수) 팀명 (문자열) 이름 (문자열) 홈런 (정수) 모기업 (문자열) 연고지 (문자열) 선수 소속 팀 선수ID (정수) 팀ID (정수)

개념 모델링 관계 유무의 판별 각 개체 사례 간 대응 관계 관찰 예) 프로야구 관련 데이터 정리 선수, 팀 개체 사례 간 대응 관계 존재 6. 개념 모델링 선수 팀 박석민 삼성 손아섭 롯데 황재균 두산 김현수 기아 … …

개념 모델링 관계의 세부 사항 차수, 선택성 : 개념 모델링 시 함께 고려 관계의 차수 대응 관계에 참여하는 개체들의 개수 정보 1 대 1 (1 : 1)관계 1 대 다 (1 : N) 관계 다 대 다 (N : N) 관계 관계의 선택성 한 쪽 사례에 대해 반드시 대응 상대 있어야 하는지 여부 6. 개념 모델링

개념 모델링 관계의 차수 종류 및 표현 1) 1 대 1 관계 양쪽 개체 사례가 한 개의 대응 상대만 가질 수 있는 경우 예) 강의실 내 좌석(번호 표시됨)에 학생 한 명 배정하는 관계 6. 개념 모델링 학생 좌석 표현 김철수 A12 학생 배정 좌석 이영훈 B25 박정한 B30 학생 한 명에 대해 좌석 1개 대응됨 (2개 이상 대응되지 않음) 좌석 1개에 대해 학생 한 명 대응됨 (2명 이상 대응되지 않음)

개념 모델링 관계의 차수 종류 및 표현 2) 1 대 다관계 한 쪽 개체 사례는 여러 개의 대응 상대가능 반대 쪽 개체 사례는 한 개의 대응 상대만 가능한 경우 예) 학생 학과 간 전공 관계 (부전공, 복수 전공 없는 것으로 가정) 6. 개념 모델링 학생 학과 표현 김철수 경영정보 학생 전공 학과 이영훈 산업경영 박정한 김종환 기계공학 정윤호 학생 한 명에 대해 학과 1개 대응됨 (2개 이상 대응되지 않음) 학과 1개에 대해 학생 여러 명 대응 가능

개념 모델링 관계의 차수 종류 및 표현 3) 다 대 다관계 양쪽 개체 사례가 여러 대응 상대가질 수 있는 경우 예) 학생과 교과목 간 수강 관계 6. 개념 모델링 학생 교과목 표현 김철수 산업경영 시스템 학생 수강 교과목 이영훈 물리 및 실험 박정한 김종환 대학수학 정윤호 학생 한 명에 대해 여러 교과목 대응 가능 교과목 1개에 대해 여러 명의 학생 대응 가능

개념 모델링 관계의 선택성 대응 상대 반드시 필요한지 명시 필수 : 반드시 필요 (짧은 수직선 표시) 선택 : 없는 경우 가능 (원으로 표시) 관계의 선택성 예) 6. 개념 모델링 학생에 대해 반드시 대응되는 좌석 필요 좌석에 대해 학생 대응되지 않을 수 있음 학생 배정 좌석 교과목은 반드시 수강 학생 필요 학생 중 수강 교과목 없는 경우 가능 (휴학생) 학생 수강 교과목

개체 관계도 ERD (Entity-Relationship Diagram) 개념 모델링 최종 산출물 개체, 속성, 관계 등을 상세 표기 예) 프로야구 관련 데이터 관계 표현 추가 6. 개념 모델링 포지션 (문자열) 타율 (실수) 팀명 (문자열) 이름 (문자열) 홈런 (정수) 모기업 (문자열) 연고지 (문자열) 선수 소속 팀 선수ID (정수) 팀ID (정수)

관계의 활용 데이터베이스에서의 관계 관계, 테이블 등으로 표현 가능성 존재 관계도 속성 포함 가능 개체관계도의 작성 표 형태로도 표현 가능 포함 속성을 목록으로 기입 예) 프로야구 관련 데이터 정리 : ERD 6. 개념 모델링 개체 소속 속성 및 도메인

내용 데이터 관리를 위한 테이블을 Access 로 설계 및 구현 주제 : 자유 선정 팀원 : 3인 (조 편성 필요) 1차 진행 (다음 수업 시간) PT 작성 : 주제 선정 배경, DB 도입 기대 효과, 설계 내역 Access 작성 : 테이블, 관계, 간단한 데이터 등록 등 팀 프로젝트 안내

논리 모델링 개념 실제 DBMS에 등록가능한 테이블 구조 (스키마) 도출 개체관계도를 설계 내역으로 변환 설계 내용에 대한 오류 검증수행 등 주요 수행 작업 Mapping rule : 테이블 스키마 산출 정규화 : 오류 검증 기타 산출물 최종 테이블 구조 7. 논리 모델링

Mapping rule 정의 개체관계도내용을 DB스키마로 변환하는 규칙 주요 mapping rule 1) 각 개체는 테이블로 표현 2) 각 개체의 속성은 해당 테이블 필드로 표현 3) 각 개체 식별자는 해당 테이블 기본 키로 설정 기본 키 : Primary key (PK) 4) 개체 간 관계는 외래키로 표현 외래 키 : Foreign key (FK) 7. 논리 모델링

Mapping rule 기본 활용 규칙 1), 2), 3) 직관적으로 적용 가능 예) 프로야구 관련 데이터 정리 7. 논리 모델링 포지션 (문자열) 타율 (실수) 팀명 (문자열) 이름 (문자열) 홈런 (정수) 모기업 (문자열) 연고지 (문자열) 선수 팀 선수ID (정수) 팀ID (정수) 테이블 필드

관계의 표현 관계 서로 다른 개체들의 사례 간 대응 관계 서로 다른 테이블의 레코드] 간 대응 관계 외래 키(foreign key)로 표현 다른 테이블 기본 키 가져온 필드 관계의 종류에 따라 사용 방법 조금씩 차이 예) 프로야구 관련 데이터 정리 선수 개체 –팀 개체 간 관계 존재 (소속) 관계의 종류 : 일 대 다관계 7. 논리 모델링 선수 소속 팀

관계의 표현 1) 일 대 일 관계 양쪽 개체 모두 상대방 1개에만 대응 가능 한 쪽 개체 기본 키를 다른 쪽 개체에 외래키로 삽입 어느 쪽으로 삽입해도 무관 예) 학생 한 명에게 좌석 한 명 할당하는 경우 7. 논리 모델링 좌석 Id 학번 이 름 학 생 좌 석 배정 성별 나이 줄 번호 Mapping rule 1, 2, 3 Mapping rule 4

관계의 표현 1) 일 대 일 관계 예) 학생 한 명에게 좌석 한 명 할당하는 경우 아래 양쪽 모두 가능 7. 논리 모델링 학생 기본 키를 좌석의 외래키로 좌석 기본 키를 학생의 외래키로

관계의 표현 1) 일 대 일 관계 예) 학생 한 명에게 좌석 한 명 할당하는 경우 생성된 스키마 활용 예시 7. 논리 모델링 학 생 좌 석 어느 학생이 어느 좌석에 배정되었는지 파악 가능 학 생 좌 석 외래키 미사용 시, 배정 관계 파악 불가

관계의 표현 2) 일 대 다 관계 한 쪽 개체 사례만 상대 개체 사례 여러 개에 대응 가능 ‘일’쪽 개체 기본 키를 ‘다’쪽 개체에 외래키로 추가 반대 방향으로 외래 키 삽입 불가 예) 학생 학과 간 전공 관계 (부전공, 복수 전공 없는 것으로 가정) 7. 논리 모델링 학번 이 름 학과ID 학과명 학생 전공 학과 성별 나이 정원 Mapping rule 1, 2, 3 Mapping rule 4

관계의 표현 2) 일 대 다 관계 예) 학생 학과 간 전공 관계 (부전공, 복수 전공 없는 것으로 가정) 반드시 학생 테이블에 학과 기본 키 추가 반대 경우 불가 7. 논리 모델링 학과 기본 키를 학생의 외래키로 ( O ) 학생 기본 키를 학과의 외래키로 ( X )

관계의 표현 2) 일 대 다 관계 예) 학생 학과 간 전공 관계 (부전공, 복수 전공 없는 것으로 가정) 7. 논리 모델링 학 생 학과 올바른 테이블 설계 학 생 학과 잘못된 설계 (값의 목록 발생)

관계의 표현 3) 다 대 다 관계 양쪽 개체 모두상대방 여러 개에 대응 가능한 관계 관계 자체를 새로운 테이블로 설계 관계 테이블에 양쪽 개체 테이블 기본 키 모두 외래키로 추가 예) 학생-교과목 간 수강 관계 7. 논리 모델링 학번 이 름 교과목ID 교과목명 학생 수강 교과목 성별 나이 정원 Mapping rule 1, 2, 3 Mapping rule 4

관계의 표현 3) 다 대 다 관계 예) 학생-교과목 간 수강 관계 “수강”테이블 : 학생들의 교과목 수강 내역 정보 저장 7. 논리 모델링 관계를 의미하는 새로운 테이블 생성 양쪽 개체에서 기본키 가져옴

관계의 표현 3) 다 대 다 관계 예) 학생-교과목 간 수강 관계 7. 논리 모델링 학 생 교과목 수강

참조) 다 대 다 관계의 설계 추가된 테이블에 의해 일 대 다 관계 2개로 쪼개어짐 7. 논리 모델링 학번 이 름 교과목ID 교과목명 학생 수강 교과목 성별 나이 정원 학번 이 름 교과목ID 교과목명 학생 수강 교과목 성별 나이 정원

논리 모델링의 완료 상세 테이블 구조 도출 Access 내용 작성 가능 7. 논리 모델링 포지션 (문자열) 타율 (실수) 팀명 (문자열) 이름 (문자열) 홈런 (정수) 모기업 (문자열) 연고지 (문자열) 선수 소속 팀 선수ID (정수) 팀ID (정수)

08. 관계와 테이블 설계 동아대학교 산업경영공학과 김 준 우

08. 관계와 테이블 설계 동아대학교 산업경영공학과 김 준 우

Presentation Transcript