제1강 데이터베이스의 기본개념(3)

제2장 데이터베이스 설계를 위한 데이터 모델

데이터베이스 설계 단계 개념적 설계 (Conceptual) 논리적 설계(Logical) 물리적 설계(Physical) 특정 DBMS와 독립적인 스키마 설계 실세계 환경으로부터 분석한 요구 사항을 기반으로 설계 개념적 설계 (Conceptual) 특정 데이터 모델을 적용한 스키마 설계 관계/계층/네트워크/객체지향 모델 논리적 설계(Logical) DB를 하드디스크에 저장하기 위한 상세 사항 기술 특정 DBMS에 적합한 저장구조/접근방법 설계 물리적 설계(Physical)

데이터베이스 구조 상위 데이터 모델 구축 실행 데이터 데이터베이스 구축 데이터베이스의 논리적 구조 현실 세계 물리적 구조 가상 세계 데이터베이스의 논리적 구조 물리적 구조

참고: 3-Level Database Architecture (ANSI/SPARC) 외부 스키마 1 (External Schema) (부스키마 : Subschema) 외부 스키마 2 외부 스키마 n 외부 계층 (DB 이용자 관점) 외부/개념 매핑 (응용 인터페이스) 개념 계층 (기관의 관점) 개념 스키마 (Conceptual Schema) 개념/내부 매핑 (저장 인터페이스) 내부 계층 (저장장치 관점) 내부 스키마 (Internal Schema) 저장 DB

데이터베이스의 논리적 구조 상위 데이터 모델 실행 데이터 모델 

상위 데이터 모델 상위 데이터 모델은 현실 세계의 데이터 모델로서 사용자가 데이터를 어떻게 인식하는가에 대한 모델이며 어떠한 데이터를 표현할 것인가에 대한 모델 이를 위해 먼저 현실 세계를 단순화 또는 추상화하여 표현 필요 Entity-Relationship 모델 (ERD) 상위 데이터 모델의 표준화된 구현 방법으로는 최초로 제안되었으며 현재도 가장 많이 사용

실행 데이터 모델 상용 DBMS가 사용하는 데이터 모델 이 단계에서 데이터는 테이블 등으로 표현 상위 데이터 모델의 구축이 완료되면 어떤 DBMS를 사용하는가에 따라 실행 데이터 모델은 달라지게 되며 사용할 DBMS에 맞게 상위 데이터 모델을 다시 실행 데이터 모델로 전환 즉, 실제적으로 ER 다이어그램이 테이블 형태로 전환 DBMS 종류와 같이 대략 4가지로 분류: 계층형; 망형; 관계형; 객체지향형 모델

데이터베이스의 물리적 구조 데이터베이스의 물리적 구조는 하위 데이터 모델로 불리는 것으로 상위 데이터 모델을 거쳐 실행 데이터 모델 구축 후 데이터가 실제로 어떻게 하드웨어 즉, 저장매체(하드 디스크 드라이브)에 저장되어 향후에 조회 갱신 추가 되는가에 관한 모델 실제적으로 테이블 형식으로 표현된 데이터가 SQL 등을 통해서 저장매체에 저장되는 단계임 사실상 DBMS의 성능은 데이터베이스의 물리적 구조에 의해 결정될 수 있음 요즘 출시되는 상용 DBMS는 사용자가 데이터베이스의 물리적 구조에 직접적으로 접근하지 못하도록 하고 있으며 거의 모든 사항을 상황에 따라 DBMS가 자동으로 최적화하므로 사용자가 알고 있어야 될 부분은 극히 적음

E-R 모델 E-R 모델은 가장 흔히 사용되는 상위 데이터 모델의 하나로 데이터베이스에 대한 지식이 전혀 없이도 아주 쉽게 이해할 수 있는 모델임 E-R 모델을 표현하기 위해서 ERD(Entity Relationship Diagram)를 사용(Chen, 1976) 직사각형, 마름모, 타원, 직선 등을 사용하여 현실 세계를 표현 요즘은 Chen의 방식을 모방하여 많이 발전된 다양한 ERD가 더 많이 사용

E-R 모델의 구성요소

개체 (Entity) 개체(Entity)란 어떠한 데이터가 저장되어야 하는가에 대한 주요한 데이터 객체로서 유형 또는 무형의 사물이나 사건 등으로 표현 ERD(Entity Relationship Diagram)에서 직사각형으로 표현되며 데이터베이스에서 개체 이름이 테이블 이름 개체(Entity)는 자신이 가지고 있는 속성들에 의해 표현되며 이 속성들이 가지는 값에 의해 실제로 데이터베이스에서 데이터로 표현

속성 (Attribute) 속성(Attribute)은 한 개체(Entity)를 잘 설명해 줄 수 있는 특성 또는 특징으로 이 속성들의 값(value)이 데이터베이스에 저장 ERD(Entity Relationship Diagram)에서 타원으로 표현되며 데이터베이스에서 속성 이름이 필드 이름 한 개체(Entity) 내에서 자신을 유일하게 식별할 수 있는 속성 또는 속성들을 식별자(identifier 또는 key)라고 하며 한 개체(Entity)는 반드시 식별자를 가져야만 함 그리고 하나의 속성은 반드시 값을 가지는 mandatory 특성과 어떠한 값을 가질 수도 또는 가지지 않을 수도 있는 optional 특성을 가짐 또한 속성은 다중값을 가질 수도 있으며 다른 속성들에 의해 유도(또는 계산)되어질 수 있음

관계 (Relationship) 관계(Relationship)는 두 개체(Entity)들 또는 한 개체(Entity)와 그 개체 자체 사이에서 자연적으로 발생하는 사실을 표현한 것 데이터베이스에선 외래키(Foreign Key)로 표현되며 관계는 ERD에서 마름모로 표현

일대일 관계

일대다 관계

다대다 관계

ERD (Gordon Everest) A, B 개체는 일대다 관계를 가지고 있으며 필수(mandatory) 관계 C, D 개체는 일대다 관계를 가지고 있으며 선택(optional) 관계 E, F 개체는 일대일 관계를 가지고 있으며 선택(optional) 관계 필수(mandatory) 관계란, 예를 들어 퇴원요약 개체와 진단분류 개체에서 퇴원한 한 환자는 ‘반드시’ 진단명을 받는다는 것을 의미하며 선택(optional) 관계란 퇴원한 한 환자는 수술을 ‘받을 수도 있고 받지 않을 수도’ 있다는 것을 의미

퇴원요약 데이터베이스

Cleveland VAMC Diabetes Registry

요약 데이터베이스 구조 E-R 모델 개체, 속성, 관계

제3장 관계형 데이터베이스의 이해

테이블, 레코드, 필드 테이블 이름 필드들 필드 한 환자의 레코드

필드, 속성

용어비교 E-R 모델 (RDB 모델) Access 용어 비고 Entity 개체 Table 테이블 Attribute 속성 Field 필드 Column Tuple 투플 Record 레코드 Row Relation 관계

인덱스 Index 책의 인덱스(색인)가 있어 책에서 중요하고 의미 있는 부분들을 찾으려고 할 때 빨리 찾을 수 있도록 해줌 데이터베이스에서도 인덱스는 같은 목적으로 사용되며 테이블에서 하나 이상의 필드에 인덱스가 부여 예를 들면 입원환자 관리 테이블에서 가장 많이 검색되는 필드는 ‘환자번호 또는 환자이름’ 이럴 땐 환자번호 필드와 환자이름 필드에 인덱스를 부여하면 빠르게 검색 너무 많은 필드에 인덱스가 부여되면 오히려 역효과 대부분의 서버용 RDBMS에서는 인덱스가 부여된 필드를 인덱스가 부여된 순서 그대로 물리적으로 저장하는 옵션이 있어 좀 더 빠른 데이터 검색이 가능 Microsoft SQL Server의 경우 이런 인덱스를 ‘clustered index’라고 함

키 Key 키란 한 레코드를 유일하게 식별할 수 있는 하나 이상의 필드들을 말하며 테이블들 사이에서 관계를 정의하기 위해 대부분 사용 키의 종류로는 Primary Key(기본 키), Foreign Key(외래 키), 그리고 Candidate Key(후보 키) 등이 있음

기본키, Primary Key 테이블에서 다른 레코드들로부터 한 레코드를 유일하게 식별할 수 있는 하나 이상의 필드를 말하며 최소한의 필드로 구성되어야 함 그러므로 Primary Key는 절대 중복될 수 없으며 null값을 절대 가질 수 없음

후보키, Candidate Key 기본키가 될 수 있는 필드를 말하며 기본키는 후보키 중에 식별자로 선정된 것 주민등록번호가 환자번호(기본키)를 대신할 수 있는 후보키

외래키, Foreign Key 어떠한 테이블의 기본키 값과 일치하는 값을 가진 다른(간혹 같은) 테이블의 하나 이상의 필드 물론 키 값 뿐만 아니라 타입도 일치하여야만 함 외래키 값은 기본키와는 다르게 반드시 유일할 필요도 없으며 null값도 경우에 따라 가질 수가 있음 하지만 외래키 값은 반드시 기본키 값에 있는 것들 중에 하나이어야만 함  참조무결성 성립

외래키의 예 학생(STUDENT) 학생 릴레이션의 지도교수 속성은 교수 릴레이션에서 기본키로 사용된다. 김수철 이기문 박동진 이름 P_NAME 정찬우 김정길 강재우 이수자 이길녀 S_NAME 서울… 경기도… 인천… 대전… 주소 S_ADDRESS 19800505 19801215 19801119 19791205 19791130 생년월일 S_BIRTH P0001 P0002 P0003 : 교 수 부교수 T102 T103 T302 4444 4445 4555 교수번호 P_NO 직급 GRADE 연구실 ROOM_NBR 전화 P_TEL P_ADDRESS 19520401 19540605 19610105 P_BIRTH 00880301 00890901 00910301 입사일 JOIN_DATE 9911001 9912002 9911003 9813009 9811015 컴퓨터공학과 정보과학과 정보통신학과 2 3 023355519 03463355411 0325491115 0428601144 023386634 학번 S_NO 학과 DEPT 학년 YEAR S_TEL P0015 P0022 지도교수 ADVISOR 교수(PROFESSOR) 학생(STUDENT) 30 29 15 호봉 PAY_STEP 학생 릴레이션의 기본키 교수 외래키 학생 릴레이션의 지도교수 속성은 교수 릴레이션에서 기본키로 사용된다.

관계 데이터 제약(constraints) 개체 무결성 제약 (entity integrity) constraints 관계의 기본키 속성은 절대로 널 값(null value)를 가질 수 없음 참조 무결성 제약 (referential integrity) constraints 관계의 외래키 속성은 참조할 수 없는 값을 가질 수 없음

정규화 Normalization 데이터의 중복을 막고 무결성을 강화하기 위해 하나의 테이블을 둘 이상으로 “분리”하는 작업 정규화란 필드들을 재정렬하여 원시 테이블들을 구조화된 테이블들로 바꾸어 주는 하나의 가이드라인이며 이렇게 정규화 된 테이블들은 “최소한”의 데이터 중복을 가지며 데이터 프로세싱 과정(데이터 추가 삭제 변경 등)을 효율적으로 진행할 수 있도록 해줌

정규화의 발전 정규화는 관계형 모델의 창시자인 E.F. Codd가 수학적 함수관계를 적용하여 최초로 제안되었으며 그 내용은 1차 정규형, 2차 정규형, 그리고 3차 정규형 이후 3차 정규형의 문제점을 해결하기 위해 제안한 사람의 이름을 딴 보이스-코드(Boyce-Codd) 정규형이 제안되었으며 4차 정규형, 5차 정규형, 그리고 이론적으로 완벽한 도메인 키(Domain Key) 정규형으로 발전 위와 같이 많은 정규형들이 있지만 대부분 3차 정규형을 넘어가는 경우는 드물며 이는 테이블 관리의 어려움과 고차원 정규형이 대부분의 데이터 프로세싱 작업에서 실질적인 도움을 주지 못하기 때문

정규화의 장점 데이터 중복의 최소화 테이블 관리의 수월성 증대 빠른 데이터 프로세싱

정규화의 단계 ➀ 제 1 정규화(First Normalization) ➁ 제 2 정규화(Second Normalization) ➂ 제 3 정규화(Third Normalization) ➃ 보이스-코드 정규화(Boyce-Codd Normalization) ➄ 제 4 정규화(Fourth Normalization) ➅ 제 5 정규화(Fifth Normalization) ➆ 비정규화(De-normalization)

제1정규화: 기본키 만들기(기본키는 중복되면 안됨) 제2정규화: 기본키에 종속되면서 중복되는 것을 분리 제3정규화: 기본키 외의 필드에 종속되면서 중복되는 것 분리

정규화 예제

정규화 전: 주문내역 테이블

제1정규화 테이블에 있는 각 필드의 값들은 단일 값을 가짐 테이블의 각 행과 열에는 오직 하나의 값만 들어 있어야 함

수정된 주문내역 테이블은 각 필드의 값들이 단일 값으로 구성되어 있지만, 주문번호가 중복되어 나타나므로 기본 키로 설정할 수 없음

기본 키 설정을 위해 일련번호 필드를 먼저 추가하여 테이블 수정

제2정규화 (키가 아닌) 모든 필드의 값이 기본 키에 종속되어야 함 기본 키 중 일부 필드에만 종속적인 관계가 있는 필드들이 있을 경우에는 종속적 관계에 있는 필드들을 기존 테이블에서 분리해 새로운 테이블을 생성

주문번호에 종속적인 필드(회원ID, 이름, 주문일)를 분리해 새로운 테이블 생성 : 주문 기본 정보 테이블

제3정규화 특정 필드의 값이 (키가 아닌) 다른 필드에 종속적일 경우 이를 분리해야 함

종속적인 필드를 분리하여 새 테이블 생성 : 도서 테이블

정규화 과정 요약 기본키 만들기

기본키에 하나에만 종속되면 안됨  기본키를 중심으로 중복이 있으면 안됨

다른 필드에 종속되면 안됨  다른 필드를 중심으로 중복되면 안됨

다른필드에 종속(중복)

제4-5정규화 제4정규화 한 테이블 내의 필드들 사이에서 다대다 관계가 존재할 경우 그들은 같은 테이블에 저장되어서는 안됨 제5정규화 정규화에 의해 분해된 많은 테이블들은 다시 조인할 때 손실되는 데이터 없이 이전의 테이블 상태로 되어야 함

요약 테이블, 레코드, 필드 인덱스, 키 정규화

참고문헌 Freed-Hardeman Univ., Database Systems, iTunes U. 김지연, 정종진. 데이터베이스 기초와 실습: 액세스 2007. 한빛미디어; 2010. Freed-Hardeman Univ., Database Systems, iTunes U.