스키마란 ? :
하나의 사용자(계정)가 생성한 모든 객체들을 일컫는 단어이다.
다른의미로는 데이터베이스 사용자라고 해석 할 수 있다.
6.1.1 제약조건 (CONSTRAINTS)
제약조건이란 : 데이터가 원치않게 입력/ 변경/ 삭제 되는 것을 방지하기 위해 테이블 생성 시, 혹은 생성 후 컬럼 단위로 설정할 수 있는
제약된 조건을 말한다.
데이터의 무결성(Integrity)를 보장하기 위한 방법 중 하나이다.
6.1.2 Primary Key : 식별키
식별키란 : 해당 행을 다른 행과 구분해 주는 대표 컬럼이다.
식별키는 하나의 테이블에 Only 하나만 존재.
식별키를 정의하면 인덱스는 자동으로 생성 된다.
제약조건을 설정할 떄는 반드시 CONSTRAINTS 절과 함께 제약조건 명을 부여해야 한다.
PK 생성 예)
SQL> create table dept
( deptno number(5) constraints dept_deptno_pk PRIMARY KET ,
name varchar2(15) );
여기서 dept_deptno_pk 는 제약조건명인데 제약조건 명을 부여하지 않아도 자동으로 생성 된다.
제약조건 확인 예문)
SQL> select constraint_name from user_constraints ; : 이렇게 조회하면 제약조건 명이 출력되어 나옴.
이미 생성되어있는 테이블에 제약조건 추가 예문)
SQL> alter table dept add (constraint dept_deptno_pk PRIMARY KEY(deptno));
6.1.3 Foreign Key (참조키)
1. 부모 / 자식 테이블 간에 행들의 무결성을 보장하기 위한 제약조건이다.
2. 부모 테이블 컬럼에 존재하지 않는 값을 입력 또는 변경할 수 없다.
3. 부모 테이블은 참조를 허용하고 자식테이블은 참조하게 된다.
4. 부모 테이블이 먼저 생성 되어 있어야만 걸 수 있는 제약조건이다.
5. 자식 테이블의 FK 컬럼에 대한 부모테이블 컬럼은 반드시 PK제약조건을 가져야 한다.
6.1.4 Unique Key (유일키)
1. PK가 아니더라도 컬럼의 모든 값이 유일해야 하는 경우 사용한다.
2. 중복된 행이 존재할 수 없다. 하지만 NULL값은 허용 가능하다.
3. PK와 같이 Index가 자동 생성된다.
4. 하나의 테이블에 여러개의 유일키를 생성할 수 있다.
6.1.5 Check
1. 컬럼에 입력되는 데이터를 검사해서 조건에 맞는 데이터만 입력 할 수 있다.
2. 조건으로는 데이터 값의 범위/ 특정 패턴의 숫자/ 문자 값을 설정 할 수 있다.
6.1.6 NOT NULL
1. 해당 컬럼에 NULL값이 저장되어서는 안될 경우 사용한다.
2. COLUMN-LEVEL 방법으로만 정의할 수 있다.
column level방식은 칼럼명 옆에 제약조건을 정의하는 방법이다.
table level 방식은 모든 컬럼들을 정의한 후 제약조건은 따로 정의하는 방법이다.
3. PK와 Unique Key는 기본적으로 NOT NULL조건을 가지고 있다.
6.2 테이블 설계서
작성 방법
1. 관련 업무 : 프로젝트 명 또는 개발 업무명을 기록한다.
2. DB 사용자 계정 : 해당 테이블과 인덱스를 생성하게 될 테이터 베이스 사용자명을 기록한다. (scott user 또는 SYS 유저 처럼)
3. 테이블명 (영문) : 영문 테이블 명을 기록한다. 표준화 원칙과 지침에 의해 결정하고 12문자 이내로 작성하여 이해하기 쉽도록 한다.
4. 테이블명 (국문) : 영문 테이블 명만으로는 테이블에 대한 용도를 쉽게 이해할 수 없기 떄문에 한글 테이블 명을 기록한다.
5. 영문 컬럼명 (국문) : 영문 컬럼명에 대한 한글 컬럼명을 기록한다.
6. 제약조건 : 사용한 제약조건을 기록한다. 유형은 되도록 간결하게 작성한다.
7. NULL여부 : 해당 컬럼이 NULL을 허용하면 NULL이라고 작성 / 허용하지 않으면 NOT NULL이라고 기록한다.
8. FK 테이블 : 테이블 간의 관계에서 참조하는 테이블을 의미한다. (부서 테이블은 사원 테이블의 FK테이블에 해당된다.)
9. FK 컬럼 : FK테이블에서 관계되는 컬럼 명을 기록한다.
10. DATA 타입 : 각 테이블의 컬럼에 부여된 데이터의 타입을 기록한다.
11. 데이터길이 : 각 컬럼에 정의된 데이터의 길이를 기록한다.
※ 테이블 설계서 작성시 주의 사항
테이블 설계서를 작성할 때는 식별키(PK)를 가진 테이블을 먼저 작성하고 외부키(FK)를 가진 테이블을 나중에 작성하는 것이 효과적이다.
6.3 테이블 생성 스크립트
테이블 설계서가 작성되고 나면 개발자들에게 전달되고 개발자들은 테이블을 생성하기 위한 '테이블 생성 스크립트'를 작성한다.
* 방법 :
1. SQL * PLUS 툴에서 create table 명령어로 하나하나 다 실행한다.
2. 운영체제 상에서 스크립트 형태로 문장을 미리 작성하여 SQL *PLUS 툴에서 일관 실행 한다.
6.4 시스템 개발 절차에 대한 이해
DB를 구축하는 것보다 더 중요한 것은 '설계' 이다.
만약 기업에서 하나의 시스템을 구축한다고 가정 했을때
1. 프로젝트 기획자 : 예산을 수립하고 , 관련 담당자를 선정한다.
2. 조직을 편성하고 업무에 대한 조사 + 진행 : 개발 범위를 결정하고 데이터에 대한 수집
3. 가장 적합한 하드웨어 + 소프트웨어 선정 : 어떤 회사에서 판매하는 제품이 적정한지 검토한다.
4. 구현
6.5 논리적 구조 알기
논리적으로 데이버 테이스를 구성하는 단위는
Block → Extent → Segment(테이블, 뷰, 인덱스) → Tablespace → Database
데이터 베이스를 생성하면 기본적으로 4개의 테이블 스페이스가 생성된다.
1. SYSTEM : system tablespace는 오라클 데이터베이스의 논리적 구조를 구성하는 필수 공간이다. 모든 Data Dictionary view가 저장되어있다.
2. SYSAUX : sysaux tablespace는 10g 버전부터 등장했으며 오라클 서버의 성능 튜닝을 위한 데이터가 저장되어있는 공간이다.
3. UNDO : user 가 사용한 명령문들이 기록되며, rollback할 수 있는 데이터 정보를 담고 있는 공간이다.
4. TEMP : Sort(정렬)시 필요한 공간 + 임시 테이블이 저장되는 공간
6.6 테이블스페이스 설계
오라클 데이터베이스를 설치하면 기본적으로 4개의 테이블스페이스가 생성되는데 이 기본적인 테이블스페이스에는
유저가 생성한 데이터들을 저장할 수 없기 때문에 추가적으로 테이블 스페이스를 생성해 주어야 한다.
6.6.1 데이터 파일의 설계
1. 데이터 테이블스페이스와 SYSTEM 테이블스페이스는 분리하라.
- 사용자가 생성한 테이블들이 새로운 익스텐트를 할당 / 삭제 될 때 ,
모든 변화에 대한 상태정보를 자료사전 테이블에 저장한다.
SYSTEM 테이블스페이스는 실시간 지속적으로 DISK I/O가 발생하기 떄문에 사용자가 생성한 데이터들까지 함께 저장할 경우
더 많은 부하를 일으키게 됨으로 따로 테이블스페이스를 생성해서 그 곳에 저장해야 한다.
2. 데이터 테이블스페이스와 인덱스 테이블 스페이스는 분리하라.
- 수많은 인덱스와 테이블이 저장되어 있는 디스크에, 두가지 테이블 스페이스가 같이 있을 경우, 인덱스를 읽고 데이터 테이블을 검색을 할때
수많은 사용자들이 동시에 읽기 작업을 수행하게 되면 빠르게 검색을 하지 못하고 순차적인 검색을 하게 될 수 있으므로
성능 저하 현상을 유발하게 된다. 그래서 인덱스 테이블 스페이스와 데이터 테이블 스페이스는 물리적으로 다른 공간에 위치해 있어야 한다.
3. 각 데이터 테이블스페이스는 I/O경합을 줄이기 위해 분리하라.
- 가능하다면 각각의 데이터 파일들을 물리적으로 다른 디스크 공간에 저장하는 것이 I/O를 분산시켜 줄 수 있는 최적의 방법이다.
※ 자주 사용되는 데이터 파일들은 반드시 같은 디스크에 생성하지 않을것!!
4. 하나의 테이블스페이스를 생성할때는 하나의 데이터 파일을 크게 만드는 것보다 여러개의 데이터 파일로 분리해서 생성 시켜 주는 것이
부하를 줄이는 방법이다. 또한 백업 과 복구를 더욱 쉽게 할 수 있는 방법이기도 하다.
5. 데이터 테이블 스페이스와 UNDO 테이블스페이스를 분리하다.
- 유저가 DML문(UPDATE, DELETE, INSERT)문을 실행할 때 변경 전 데이터를 일시적으로 기억해 두기 위해 UNDO segment를 할당한다.
언두 세그먼트는 많은 사용자들이 공유하는 공간이기 떄문에 사용자가 생성한 데이터와 함께 저장하면 디스크 경합 현상이 발생 함으로
반드시 분리 저장해야 한다.
6. 데이터 테이블스페이스와 TEMP 테이블스페이스를 분리하라.
- TEMPORARY 테이블스페이스에는 사용자가 실행한 SQL문장이 저장되어있다. Sort작업을 실행할때 작업의 일부 결과를 잠시 저장하기 위해
사용되는 공간이기 때문에 이 역시 많은 사용자들이 동시에 사용하는 공유영역이다. 그래서 TEMP테이블스페이스도 반드시
데이터 테이블스페이스와 물리적으로 다른 디스크에 저장시켜 주어야 한다.
7. 대용량의 분류작업을 위해 TEMP 테이블스페이스를 각 사용자 별로 할당하라.
- 오라클 서버를 설치하면 기본적으로 하나의 temp tablespace가 생성되는데 모든 사용자들이 하나의 공간을 사용하면 디스크 경합 현상이
발생 하기 떄문에 TEMP 테이블 스페이스를 여러개 생성해서 데이터 베이스 내 사용자 계정을 생성 할 때는
각 사용자마다 다른 temporary tablespace를 할당해 주는 것이 좋다.
6.7 사용자의 설계
데이터베이스에서 다음과 같은 설계 기준에 의해 사용자를 생성한다.
1. 업무별로 사용자 계정을 만드는 방법
2. 전체 업무를 하나의 사용자로 관리하는 방법.
**사용자를 생성할 떄 고려할 사항
1. 성능
2. 데이터베이스 설계 단계에서 고려해야 하는 효과적인 관리 문제
3. 얼마나 많은 양의 데이터들이 존재 하는 업무환경인가에 따라서.
<사용자의 생성 설계 비교하기>
1. 업무별로 사용자를 따로 생성하는 경우
- 사용자 계정은 테이블의 이름을 결정하는 기준이 된다.
- 효과적인 관리가 용이해 진다.
-백업시 특정 사용자를 기준으로 그 사용자가 소유한 모든 객체들을 백업 + 복구하게 된다.
- 주로 대용량의 데이터를 저장 + 관리하며 많은 사용자들이 동시에 데이터베이스에 접속해야 하는 업무환경에서 가장 최적의 방법이다.
2. 전체 업무를 하나의 사용자로 생성하는 경우
- 각각 다른 업무에서 사용되는 테이블과 객체들을 구별하기 위해서 반드시 테이블 명을 별도로 부여 해야만 한다.
예) S_DEPT : S라는 접두어가 붙게되면 급여(salary)의 약자를 줄여서 썼다 라고 하던지..
- 데이터베이스 백업 방법 중에 전체 백업(관련된 모든 테이블을 일괄 백업하는 방법)에만 적용할 수 있다.
- 빠른 성능보다는 효과적인 관리가 더 요구되는 중소 규모의 데이터베이스 환경에 가장 최적의 방법이다.
**기업에서 구축하는 DB환경
1. 하나의 DB에 모든 업무의 데이터를 저장 + 관리하는 방법
2. 단위 업무별로 여러개의 서버에 데이터베이스를 여러개 분산시켜 저장 관리하는 방법 : 분산 DB
6.7.1 사용자 생성
※ Default tablespace절에는 SYSTEM tablespace가 할당되지 않도록 조심한다.
- default tablespace를 지정해주지 않으면 유저가 생성한 데이터들이 기본적으로 SYSTEM tablespace에 저장된다.
Temporary tablespace를 각 사용자마다 할당해 준다.
- 여러개의 temp tablespace를 생성해서 각 사용자 마다 다른 temp tablespace를 할당하는 것이 DISK I/O를 줄이는 방법이다.
tablespace에 QUOTA설정하지 않기.
- quota절은 : 해당 사용자가 사용할 수 있는 데이터 파일의 크기를 제한할 떄 사용된다. 이것은 대용량 데이터를 저장 + 관리하는데
문제점을 일으킬 수 있기때문에 설정하지 않는 것이 좋다.
