데이터베이스 개론과 실습

[머릿말]



본 교재는 한성대학교 산업경영공학과 2학년 학생들을 대상으로 데이터베이스 교과목을 강의하면서 준비한 자료를 바탕으로 저술되었다. 데이터베이스를 처음 접하는 학생을 대상으로 데이터베이스 기본 개념의 이해와 SQL Server 2014의 실습을 통한 데이터베이스 관리 시스템의 기본적인 활용 능력의 배양을 학습 목표로 하였다.



데이터베이스는 정보시스템의 한 구성 요소이므로 PART 1에서 다음과 같이 정보시스템에 대한 개론적인 내용을 다루고 있다.

ㆍ 정보시스템의 정의

ㆍ 데이터, 정보, 지식의 정의와 차이점

ㆍ 일괄 처리, 온라인 거래 처리, 온라인 분석 처리

ㆍ 평사원, 하위 관리자, 중간 관리자, 임원 등 조직의 피라미드 구조에 따른 정보처리 업무와 정보시스템의 유형

ㆍ ERP, SCM, CRM, 비즈니스 인텔리전스 등 기업정보시스템의 기능

ㆍ 데이터베이스 관리 시스템의 역할

ㆍ 관계형 데이터베이스 언어인 SQL



PART 1의 마지막 절은 SQL Server 2014의 실습을 준비하는 단계로서 SQL Server 2014 Express Edition의 설치 및 실행, 그리고 Northwind 예제 데이터베이스의 연결 과정을 설명하고 있다.



데이터베이스에 대한 작업은 크게 정의, 조작, 제어로 나누어진다. 데이터베이스 정의는 데이터베이스를 구성하는 테이블, 인텍스, 뷰, 저장 프로시저, 트리거 등의 데이터베이스 개체를 정의하는 것을 말한다. 테이블의 정의는 PART 2, 인테스의 정의는 PART 5, 저장 프로시저, 트리거의 정의는 PART 6에서 다룬다.



테이블의 정의는 제약조건과 열 속성의 정의로 이루어진다. PART 2의 데이터 정의에서는 다음과 같은 내용을 설명한다.

ㆍ 기본키, 외래키, UNIQUE, CHECK 제약조건

ㆍ 데이터 형식, NULL 허용, ID 사양, 스파스 여부 등의 열 속성

ㆍ Northwind 예제 데이터베이스의 테이블 데이터 내용



데이터베이스 조작은 데이터를 검색하고 수정하는 것을 말한다. PART 3에서는 데이터의 검색, 추가, 삭제, 업데이트를 위한 SELECT, INSERT, DELETE, UPDATE 등의 SQL 명령문을 구사하고 활용하는 능력을 갖추게 될 것이다.



데이터베이스를 구현하기 위해서는 먼저 설계가 이루어져야 한다. PART 4에서는 데이터베이스 설계와 관련한여 다음과 같은 내용을 다루고 있다.

ㆍ 데이터베이스 설계과정

ㆍ 전사적 데이터 통합의 개념

ㆍ 개체-관계 모델

ㆍ 계층형, 네트워크형, 관계형, 객체관계형, 객체지향형, 데이터베이스 모델

ㆍ 관계대수와 관계해석

ㆍ 정규화와 역정규화



데이터베이스의 성능 측면을 고려하기 위해서는 데이터베이스의 가상 물리적 구조인 페이지 구조를 이해하여야 한다. PART 5에서는 SQL Server의 데이터 페이지와 인덱스 페이지의 구조, 그리고 이진 탐색 트리, B 트리, Bitmap, Reverse 등의 인덱스 종류에 대하여 설명한다.



Part 6에서는 뷰, 저장 프로시저, 트리거의 용도와 정의, 그리고 암호화와 사용 권한 제어에 관한 내용을 다룬다.



데이터베이스 제어는 보안 및 권한 제어, 데이터 무결성, 데이터 복구, 병행 제어 등을 말한다. PART 7은 트랜잭션의 병행 제어 그리고 데이터 잠금과 관련한 다음과 같은 내용을 설명한다.

ㆍ SQL Server의 병행 제어 아키택처와 데이터베이스 복구

ㆍ Two-phase locking, 낙관적 잠금, 타임스탬프 순서 등의 병행 제어 기법

ㆍ 공유, 배타, 업데이트, 의도 잠금

ㆍ 교착 상태

ㆍ UNCOMMITED, COMMITED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE 등의 트랜잭션 격리 수준



정보처리기사 자격시험의 데이터베이스 과목에서 다루어지는 주제는 거의 모두 다루고 있기 때문에 본 교재의 내용을 숙지한다면 데이터베이스 과목에서 좋은 점수를 얻을 수 있을 것이다.



유재건 교수의 홈페이지(www.hasung.ac.kr/~jgyoo)의 교재 게시판에서 본 교재와 관련한 다음과 같은 참고 자료를 다운로드할 수 있다.

ㆍ Northwind 예제 데이터베이스 파일

ㆍ 본 교재에 포함된 실습 과정을 화면 캡쳐한 동영상

ㆍ 본 교재에 포함된 SQL 명령문



본 교재가 출간될 수 있도록 배려해 주신 한성대학교와 출판부에 감사드린다. 끊임없는 기도로 힘이 되어 주시는 부모님과 동생들, 그리고 사랑하는 아내와 두 아들에게 고마운 마음을 전한다.



2015년 8월

유 재 건

PART|1 |정보시스템 개요



1. 정보시스템의 정의와 구성 4

2. 데이터, 정보, 지식 5

3. 정보처리의 유형 6

3.1. 일괄 처리와 온라인 처리 6

3.2. 실시간 처리 7

3.3. 분산 처리 7

4. 정보시스템의 분류 8

4.1. 조직의 계층 구조에 따른 업무의 분류 8

4.2. 조직의 계층 구조에 따른 정보시스템의 분류 9

4.3. 온라인 거래 처리와 온라인 분석 처리 12

4.4. 조직의 계층 구조에서 기업정보화 솔루션의 위치 14

4.5. 비즈니스 인텔리전스 16

5. 정보 시스템의 구성 요소 19

5.1. 데이터베이스 20

5.2. 데이터베이스 관리 시스템 22

5.3. 데이터베이스 언어 24

5.4. 데이터베이스 사용자 26

6. 실습 | MS SQL Server 2014 설치 및 실행 27

6.1. MS SQL Server 2014 Express Edition의 설치 27

6.2. MS SQL Server 2014의 실행 34

6.3. SQL Server(데이터베이스 엔진)의 실행 34

6.4. Management studio의 실행 36

6.5. Northwind 예제 데이터베이스 연결하기 40

6.6. 운영체제 오류(오류번호 5120) 문제 해결 방법 43



PART|2 |데이터 정의



1. 관계형 데이터의 논리적 구조 47

2. 테이블과 개체 유형 49

3. Northwind 예제 데이터베이스 소개 50

4. 테이블 정의 56

4.1. 기본키 제약조건 57

4.2. 외래키 제약조건 76

4.3. UNIQUE 제약조건 125

4.4. CHECK 제약조건 126

4.5. 데이터 형식 132

4.6. 기타 열 속성 136

4.7. 테이블 정의를 위한 SQL 명령문의 문법 137



PART| 3 |데이터 조작



1. 개체의 속성 검색과 예 141

2. 실습 - 쿼리 및 뷰 디자이너를 이용한 개체의 속성 검색 143

3. 개체군의 속성 검색과 예 146

4. 실습 | 쿼리 및 뷰 디자이너를 이용한 개체군의 속성 검색 147

5. 데이터 검색을 위한 SELECT 문 152

5.1. SELECT 절과 FROM 절 152

5.2. 실습 | 쿼리 창을 이용한 SELECT문 작성과 실행 153

5.3. WHERE 절 155

5.4. ORDER BY 절과 SELECT 절의 TOP, WITH TIES, DISTINCT 157

5.5. GROUP BY 절 160

5.6. HAVING 절 164

5.7. INTO 절 167

5.8. UNION 연산자 168

5.9. JOIN 연산자 169

5.10. 부모별 자식 개체군의 속성 검색 201

5.11. 하위 쿼리와 조인을 이용한 개체와 개체군의 속성 동시 검색 209

5.12. 실습 문제 | SELECT 문 작성 213

5.13. 과제 | SELECT 문 작성 217

6. 데이터 수정을 위한 INSERT, DELETE, UPDATE 문 221

6.1. INSERT 문 221

6.2. DELETE 문 224

6.3. UPDATE 문 225

7. 응용 프로그램에서 데이터를 액세스하기 위한 기술 226

7.1. 내장 SQL(Embedded SQL) 226

7.2. Microsoft의 ODBC(Open Database Connectivity) 227

7.3. Microsoft의 OLE DB(Object Linking and Embedding DB) 227

7.4. Microsoft의 ADO(ActiveX Data Objects)와 ADO.NET 227

7.5. Oracle의 JDBC(Java Database Connectivity) 227

8. 데이터베이스 커서 228



PART| 4 |데이터베이스 설계



1. 데이터베이스 설계 과정 235

1.1. 정보 요구사항 분석 236

1.2. 개념적 데이터 설계 236

1.3. 논리적 데이터 설계 237

1.4. 물리적 데이터 설계 238

2. Three schema architecture 238

3. 개체|관계 모델 240

3.1. 개체 유형 242

3.2. 관계 250

3.3. 속성 258

4. 데이터베이스 모델 260

4.1. 계층형 데이터베이스 모델 260

4.2. 네트워크형 데이터베이스 모델 262

4.3. 관계형 데이터베이스 모델 262

4.4. 객체관계형 데이터베이스 모델 263

4.5. 객체지향형 데이터베이스 모델 263

5. 관계형 데이터베이스 모델 264

5.1. 관계대수 264

5.2. 관계해석 268

5.3. 정규화 269

5.4. 역정규화 277



PART| 5 |SQL Server DB의 데이터와 인덱스 구조



1. 사용자 데이터베이스와 시스템 데이터베이스 285

2. 시스템 카탈로그 286

3. 사용자 데이터베이스 파일 및 파일 그룹 287

3.1. 주 데이터 파일, 보조 데이터 파일, 트랜잭션 로그 파일 287

3.2. 파일 그룹 288

3.3. 분할된 테이블 289

3.4. 데이터베이스 파일의 속성 291

3.5. 데이터베이스 분리 및 연결, 그리고 삭제 298

3.6. Filestream 302

4. 데이터 페이지 구조 303

4.1. 데이터 구조의 계층적 분류 303

4.2. 페이지 구조 304

4.3. 데이터 페이지의 구조 306

5. 인덱스 페이지 구조 307

5.1. 클러스터형 인덱스 구조 308

5.2. 클러스터형 인덱스가 있는 경우의 비클러스터형 인덱스의 구조 312

5.3. 클러스터형 인덱스가 없는 경우의 비클러스터형 인덱스의 구조 316

5.4. 채우기 비율 322

5.5. 인덱스 설계 323

5.6. 실습 - 인덱스 생성 및 속성 지정 325

6. 인덱스의 종류 329

6.1. m-원 탐색 트리 330

6.2. B, B*, B+ 트리 344

6.3. Bitmap 인덱스 346

6.4. Reverse 인덱스 348



PART| 6 |뷰, 저장 프로시저, 트리거, 보안



1. 뷰 353

1.1. 뷰의 용도 354

1.2. 뷰의 유형 354

2. 저장 프로시저 360

2.1. 실습 | 출력 매개변수 값을 반환하는 사용자 저장 프로시저의 생성 및 호출 361

2.2. 실습 | 결과 집합을 반환하는 사용자 저장 프로시저의 생성 및 호출 363

2.3. 실습 | 반환 코드를 반환하는 사용자 저장 프로시저의 생성 및 호출 364

3. 트리거 366

3.1. DML 트리거의 용도 367

3.2. AFTER 트리거와 INSTEAD OF 트리거 368

3.3. 실습 | 외래키 테이블에 대한 INSTEAD OF 트리거 생성 369

3.4. 실습 - DELETED와 INSERTED 테이블의 데이터 확인 372

3.5. 실습 - UNIQUE 제약조건을 트리거로 설정 374

3.6. 실습 - 복잡한 제약조건의 설정 376

3.7. 실습 - 파생 필드의 자동 업데이트 설정 380

3.8. 실습 - 복합 뷰를 통한 기본 테이블 데이터의 수정 385

4. 보안 387

4.1. 암호화 388

4.2. 사용 권한 제어 391



PART| 7 |트랜잭션의 병행 제어와 잠금



1. 트랜잭션 397

1.1. 트랜잭션의 개념 397

1.2. SQL Server의 트랜잭션 로그 아키텍처 399

1.3. SQL Server의 트랜잭션 관리 모드 413

1.4. 트랜잭션 프로그래밍 414

1.5. 데이터베이스 복구 430

2. 병행 제어 432

2.1. 병행 제어 부재 시 발생하는 문제 432

2.2. 병행 제어 기법 436

3. 잠금 442

3.1. 잠금의 세분성 442

3.2. 잠금 모드 443

3.3. 실습 - 잠금 정보 조회 446

3.4. 교착 상태 448

3.5. 트랜잭션 격리 수준 452

유재건 저자 유재건은 서울대학교 산업공학과 및 동대학원에서 졸업, University of Houston 산업공학과 대학원에서 박사학위를 취득하였다. 한국통신개발연구원 주임연구원, 한국전자통신연구원 선임연구원을 역임하였으며, 현재 한성대학교 산업경영공학과 교수이다.