데이터베이스는 데이터를 중앙에 저장하고 관리하여 여러 사용자가 공유할 수 있게 하려고 사용하는 '데이터 저장소'다.
공유가 필요하지 않다면 데이터를 워드나 엑셀과 같은 개별 파일로 관리해도 된다.
하지만 그 데이터에 대한 수정 사항이 생긴다면 각각 개별 파일을 따로따로 수정해줘야 한다.
# 데이터베이스를 사용하는 2가지 분야
OLTP(Online Transaction Processing) 데이터베이스
주로 트랜잭션 처리(우선 데이터 변경 처리로 이해하자)를 위한 용도로 사용하는 데이터베이스이다.
데이터베이스의 테이블은 되도록 중복된 데이터를 제거하고, 빠르게 데이터를 변경할 수 있는 구조로 디자인
이를 위해 SQL Server는 다수의 사용자가 데이터를 동시에 수정할 수 있도록 데이터베이스를 관리한다.
우리가 일반적으로 데이터베이스라고 부르는 것은 대부분 OLTP 데이터베이스다.
OLAP(Online Analytical Processing) 데이터베이스
대량의 데이터를 체계화하고 요약하여, 데이터에 대한 빠른 평가와 분석을 목적으로 사용하는 데이터베이스
이를 위해 SQL Server는 Analysis Services를 사용하여 빠른 의사 결정을 위한 데이터 분석을 가능하게 한다.
Reporting Services를 사용하여 손쉽게 보고서를 작성하고 조회할 수 있게 해준다.
# 시스템 데이터베이스
SQL Server를 설치하는 과정에서 SQL Server가 자체적으로 사용할 목적으로 자동으로 만드는 몇몇 테이터베이스를 '시스템 데이터베이스'라 한다.
master 데이터베이스
SQL Server가 수 많은 데이터베이스를 관리하기 위해서는 이들 데이터베이스에 대한 정보를 저장할 특변한 데이터베이스가 필요하다. 즉, 데이터베이스의 이름은 무엇이며, 데이터베이스와 관련된 데이터 파일과 로그 파일은 어느 폴더에 있으며, 데이터베이스에 접근하기 위해 서버에 로그인할 수 있는 계정은 무엇인지 등의 정보를 어딘가에 저장해야 한다.
이때 사용하는 데이터베이스가 'master' 데이터베이스다.
model 데이터베이스
'model' 이라는 단어에서 그 역할을 추측할 수 있듯이, 새로 만들어지는 데이터베이스의 원형으로 사용된다.
즉, model 데이터베이스를 복사해서 새로운 데이터베이스를 만든다.
model 데이터베이스 설정을 변경하면 이후에 만들어지는 새로운 데이터베이스에 그대로 반영된다.
CREATE DATABASE 문을 실행하면model데이터베이스의 내용을 복사하여 데이터베이스의 첫 번째 부분이 생성
tempdb 데이터베이스
SQL Server가 운영될 때 자동으로, 또는 사용자에 의해 만들어지는 임시 테이블들이 저장되는 곳이다.
물론 임시 테이블을 저장하는 것 외에도 여러 가지 역할을 담당한다.
tempdb 데이터베이스는 그 이름에 '임시' 의 뜻이 있어 중요한 것 같지 않지만,
절대 무실할 수 없는 중요한 시스템 데이터베이스다.
SQL Server 인스턴스에 연결된 모든 사용자가 사용할 수 있는 전역 리소스 이고,
사용자 개체, 내부 개체, 버전 저장소를 저장하는 데 사용한다.
tempdb 데이터베이스
주로 자동화와 관련된 정보를 저장할 때 msdb 데이터베이스를 사용한다.
SQL Server 에이전트 서비스가 제공하는 '작업(Jobs)', '경고(Alert)', '운영자(Operators)' 기능으로 유지관리 업무를 자동화 할 수 있는데, 이들과 관련된 정보를 msdb 데이터베이스에 저장한다.
이런 이유로 msdb 데이터베이스를 'SQL Server 에이전트 서비스가 사용하는 데이터베이스'라고 말하기도 한다.
resource 데이터베이스
SQL Server 운영과 관련된 시스템 개체를 모두 포함하고 있는 resource 데이터베이스가 있다.
하지만 읽기 전용의 숨겨진 데이터베이스여서 개체 탐색기에서는 보이지 않는다.
# 예제 데이터베이스
예제 데이터베이스는 SQL Server 기능을 익힐 때 사용하도록 제공되는 학습용 데이터베이스다.
번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러 개의 테이블로서 저장된다.
DDL의 명령어 종류: CREATE(정의), ALTER(변경), DROP(삭제)
시스템 카탈로그 (System Catalog)
데이터베이스에 포함된 다양한 데이터 객체(기본 테이블, 뷰, 인덱스, 데이터베이스, 패키지, 접근 권한 등)에 대한 정보들을 유지, 관리하기 위한 시스템 데이터베이스이다.
데이터베이스에 포함되는 모든 데이터 객체에 대한 정의나 명세에 관한 정보를 유지 관리한다.
DBMS가 스스로 생성하고, 유지하는 데이터베이스 내의 특별한 테이블의 집합체이다.
데이터 사전(Data Dictionary)이라고도 한다.
시스템 카탈로그에 저장된 정보를 메타 데이터(Meta Data)라고도 한다.
구분
역할
CREATE
DOMAIN
도메인을 생성
TABLE
테이블(릴레이션) 생성
VIEW
뷰를 생성
INDEX
인덱스를 생성
ALTER
TABLE
테이블 구조를 변경
DROP
DOMAIN
도메인을 제거
TABLE
테이블을 제거
VIEW
뷰를 제거
INDEX
인덱스를 제거
Char(n)
character
고정길이 문자열
열 항목들이 크기가 일관적인 경우
varChar(n)
Characyer varying
가변길이 문자열
열 항목들이 크기가 일관적이지 않은 경우
CREATE TABLE
테이블을 정의하는 명령문이다.
표기 형식
CREATE TABLE 학생{
학번 CHAR(15) NOT NULL,
이름 VARCHAR(15) NOT NULL,
학과 VARCHAR(20) NOT NULL,
학년 INT NOT NULL,
전화번호 CHAR(15) NOT NULL,
성별 GENDER,
생년월일 DATE,
PRIMARY KEY(학번),
UNIQUE(전화번호),
FOREIGN KEY(학과) REFERENCES 학과(학과코드),
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT 학년제약 CHECK(학년 >= 1 AND 학년 <= 4));
* NO ACTION(=RESTRICT)
- 피참조 테이블에서 행(들)을 삭제 또는 수정한 후, 참조 테이블에서 무결성 제약조건을 위배하지 않으면 O.K.,
무결성 제약조건을 위배하면 오류로 처리한다.
* CASCADE
- 피참조 테이블에서 행(들)을 삭제 또는 수정한 후, 참조 테이블에서 무결성 제약조건에 따라 삭제 또는 수정을 행한다.
* SET NULL
- 피참조 테이블에서 행(들)을 삭제 또는 수정한 후, 참조 테이블에서 무결성 제약조건에 따라 NULL로 바꾼다.
단, NOT NULL 제약이 참조하는 칼럼에 선언되어 있다면 오류로 처리한다.
* SET DEFAULT
- 피참조 테이블에서 행(들)을 삭제 또는 수정한 후, 참조 테이블에서 무결성 제약조건에 따라 default)으로 바꾼다.
만약, 참조 테이블의 외래 키가 default 값을 찾지 않으면 NULL 값을 default 값으로 한다. 단, default 값이 선언되어
있지 않으며 NOT NULL 제약이 참조하는 칼럼에 선언되어 있다면 오류로 처리한다.
ALTER
- 스키마, 도메인, 테이블, 뷰, 인덱스를 수정
* ALTER TABEL
- 테이블의 정의를 변경하는 명령문
DROP
- 스키마, 도메인, 테이블, 뷰, 인덱스를 삭제
* CASCADE: 삭제할 개체를 참조하는 다른 모든 개체를 함께 삭제
* RETRICT: 다른 개체가 삭제할 개체를 참조 중일 경우 삭제를 취소
# 데이터 제어어 [DCL, data control language]
다수의 사용자가 데이터베이스를 공용하고 정확성을 유지하기위한 데이터 제어를 정의하고 기술하는 언어이다.
데이터의 보안, 무결성, 회복과 밀접한 관련이 있다.
불법적인 사용자로부터 데이터를 보호하기 위한 데이터 보안(Security)을 명세한다.
데이터 정확성을 위한 무결성(Integrity)을 명세한다.
시스템 장애에 대비한 데이터 회복(Recovery)과 병행 수행(Concurrency) 제어를 명세한다.
CREATE VIEW 주문목록(주문번호, 신청자명, 주문일자, 제품번호, 단가) AS SELECT A.주문번호, A.신청자명, A.주문일자, B.제품번호, B.단가 FROM 주문 A, 주문목록 B WHERE A.주문번호 = B.주문번호
주문 테이블
주문번호
신청자명
주문일자
A101
전지현
2005-9-28
A102
김태희
2005-10-30
주문 목록 테이블
주문번호
제품번호
단가
A101
B900
25,000원
A101
A100
50,000원
A102
C300
10,000원
A102
D400
30,000원
사용자에게 보여지는 뷰
주문번호
신청자명
주문일자
제품번호
단가
A101
전지현
2005-9-28
B900
25,000원
A101
전지현
2005-9-28
A100
50,000원
A102
김태희
2005-10-30
C300
10,000원
A102
김태희
2005-10-30
D400
30,000원
실제로 이렇게 저장된 테이블은 없다.
가상 테이블 뷰라고 부른다
사용자 입장에서 보여지는 데이터베이스의 구조
# 인덱스(INDEX) 설계
인덱스(INDEX)란?
데이터베이스에서 원하는 데이터를 좀더 빨리 찾아줄 수 있도록 데이터의 위치정보를 모아놓은 개체
항상 정렬되어 있는 상태로 유지
시스템의 성능 향상을 가져올 수 있음
인덱스에 사용 할 컬럼은 수정이 자주 발생되는 컬럼을 선정
데이터의 입력, 수정, 삭제가 많이 발생하면 데이터 영역뿐만 아니라 인덱스에 있는 정보도 DBMS에서 수정해야 하므로 시간과 자원이 더 소모
데이터검색
FTS(Full Table Scan) - 전체 데이터를 쭉 읽어서 찾는 방법 - 모든 테이블을 읽어서 찾는 방법 - 많은 데이터를 찾을 때 효율적이다
INDEX SCAN - 전체 데이터 중 하나를 찾는 다면 인덱스 스캔이 효율적이다
클러스터드 인덱스(Index)
클러스터드 인덱스가 생성되는 컬럼을 기준으로 데이터 페이지에 저장된 데이터를 물리적으로 정렬
기본적으로 넌 클러스터드 인덱스보다 검색 속도가 빠르며 특히 범위 조회(Range Query)를 할 경우 속도가 빠름
한 테이블에 하나의 클러스터드 인덱스만 생성 가능
기본키를 만들면 기본적으로 기본키에 클러스터드 인덱스가 만들어짐
넌 클러스터드 인덱스(Index)
데이터 페이지의 데이터 그대로의 위치 정보를 인덱스로 구성
리프 레벨은 정렬된 상태로 관리되며 하위 데이터 페이지에 대한 포인터 정보를 갖는다.
상위 레벨들은 하위 인덱스 페이지들의 첫번째 레코드에 대한 정보를 관리
데이터 페이지와는 별도로 인덱스 페이지가 생성
클러스터드 인덱스보다 검색 속도가 느리며 범위 조회(Range Query)를 할 경우 거의 인덱스의 도움을 받을 수 없음
데이터를 조회할 때
클러스터드 인덱스: 루트 => 리프
넌 클러스터드 인덱스: 루트 => 리프 => 데이터 페이지 - 클러스터드 인덱스보다는 넌 클러스터드 인덱스가 더 많은 공간을 차지한다. - 클러스터드 인덱스의 추가 필요 공간은 테이블 크기의 5 ~ 10% 정도 넌 클러스터드 인덱스의 추가 필요 공간은 테이블 크기의 10 ~ 20% 정도이다.
# 선택성(Selectivity)
Data의 종류의 수와 실제 입력된 데이터 수의 비율, 즉 선택될 수 있는 빈도
예를 들어서 1000건의 데이터가 들어 있는 컬럼에서 유일한 값을 반환하는 경우에는 선택성이 0.1%이며, 이러한 경우에는 선택성이 너무 낮기 때문에, 인덱스를 지정하지 않는 것이 좋다.
반대로 선택성이 너무 높은 경웅에도 인덱스를 지정하지 않는 것이 좋다. (만약 성별에 대해서 인덱스를 지정하는 것 역시, 성능향상에는 도움이 되지 않음)
"중복되는 항목이 없다"에 대한 정의는 학자마다 주장이 조금씩 달라서, 1NF를 만족하는 테이블에 대해서의 정확한 정의는 없다. 대부분의 학자들(에드거 F. 커드, Ramez Elmasri, Shamkant B. Navathe[3])은 1NF를 만족하는 테이블은 관계 값을 가지는 항목(테이블 내 테이블)을 허용하지 않으나, 일부 학자들(크리스토퍼 J. 데이트등)은 이를 허용하기도 한다.
# 크리스토퍼 J. 데이트의 1정규형의 정의
열에는 위 - 아래의 순서가 없다.
행에는 좌 - 우의 순서가 없다.
중복되는 열이 없다.
모든 열과 행의 중복지점에는 (열과 행의) 해당되는 분야에서 한 개의 값을 가진다.
모든 행은 규칙적이다.
# 제 2 정규형(2NF)
- 키가 아닌 모든 속성들이 기본키에 완전 함수 종속
# 제 3 정규형(3NF)
- 키가 아닌 모든 속성들이 기본키에 이행적으로 함수 종속 되지 않은 릴레이션
# 무손실 분해(Lossless Decomposition)
분해된 두 릴레이션을 조인하면 원래의 릴레이션에 들어 있는 정보를 완전하게 얻을 수 있다.
여기서 손실이란 정보의 손실을 뜻한다.
정보의 손실을 원래의 릴레이션을 분해한 후에 생성된 릴레이션들을 조인한 결과에 들어 있는 정보가 원래의 릴레이션에 들어 있는 정보보다 적거나 많은 것을 포함한다.
