데이터 무결성과 제약조건
데이터 무결성
- 데이터베이스 시스템은 데이터에 접근하거나, 데이터를 처리할 때마다, 부적절한 데이터가 입력되는지 검사하여 데이터의 결점 없음, 즉 무결성을 유지한다.
- 이를 지키기 위한 기본 규칙을
데이터 무결성이라고 한다.
1. 개체 무결성(entity integrity)
- 기본 키(primary key)로 선택된 열은 고유해야 하며, null 값을 가질 수 없다.
2. 참조 무결성(reference integrity)
- 기본키와 외래키의 관계. 외래키가 있는 테이블은, 기본키와 외래 키 간의 관계가 항상 유지됨을 보장한다. 참조하는 외래 키가 존재하면 행은 삭제될 수 없고, 기본 키도 변경될 수 없다.
3. 영역 무결성(domain integrity)
- 데이터 형태, 범위, 기본값, 유일성에 관한 제한
- 주어진 속성 값은 그 속성이 정의된 도메인에 속한 값이어야 한다.
ex) 값이 0 이상, YN 값 준수 여부, 기본값은 1 등
4. 비즈니스 무결성(business integrity)
- 사용자의 업무 규칙에 따른 비즈니스적인 제약 조건
ex) 제약 조건, DEFAULT, TRIGGER 등의 사용자 정의
제약 조건
- 정해 놓은 규칙에 맞는 데이터만 입력받고, 규칙에 어긋나는 데이터는 거부하여 데이터 무결성을 지키는 방법
① 기본 키 제약 조건
UNIQUE + NOT NULL을 만족해야 한다. 또한, 테이블 대표하여 각 행을 유일하게 식별하는
값이어야 한다.
② 외래 키 제약 조건
- 열 값이 부모 테이블의 참조 열을 반드시 참조해야 한다. 참조되는 열은 UNIQUE하거나, 기본키이다.
③ 유일 키: 중복된 값을 허용하지 않는다. 유일한 값으로 존재해야 한다.(NULL 가능)
④ NOT NULL
⑤ CHECK : 범위나 조건 등 지정된 값만 허용한다.
트랜잭션
-
DB의 데이터 무결성이 보장되는 상태에서, DML 작업을 완수하기 위한 기본 작업 단위이다.
-
일반적으로 DML의 실행과 실행에 대한 커밋/롤백 단계까지를
트랜잭션이라고 부르지만,
실무에서는 데이터베이스에서 SELECT문으로 데이터를 조회하고,
DML을 실행하여 종료하는 과정까지를 트랜잭션이라고 부릅니다. -
조회: SELECT -> 실행: DML(INSERT, UPDATE, DELETE) -> 종료: COMMIT -> 결과: 성공 또는 철회
ACID
원자성(Atomicity)
- 트랜잭션 처리가 완전히 끝나지 않았을 경우에는, 전혀 이루어지지 않은 것과 같아야 한다. (all or nothing)
일관성(Consistency)
- 트랜잭션의 실행이 성공적으로 완료되면, 데이터베이스는 모순 없이 일관성이 보존된 상태여야 한다.
고립성(Isolation)
- 어떤 트랜잭션도 다른 트랜잭션의 부분적 실행 결과를 볼 수 없다.
지속성(Durability)
- 트랜잭션이 성공하면 트랜잭션의 결과를 영구적으로 보존해야 한다.
커밋(commit)
- 트랜잭션의 모든 미결정 데이터를 영구적으로 반영함으로써 트랜잭션을 종료한다. (저장과 유사)
롤백(rollback)
- 트랜잭션의 모든 미결정 데이터 변경을 포기함으로써 트랜잭션을 종료한다. (되돌리기와 유사)
동시성 제어
- 동시에 실행되는 여러 개의 트랜잭션이 작업을 성공적으로 마칠 수 있도록 지원한다.
- 여러 명의 사용자가 존재하므로, 데이터에 대한 접근 제어를 정확하게 하지 않으면 부정확한 데이터가 되고 만다.
동시성 제어 실패로 인한 오류 현상:
갱신 손실, 불일치 현상, 연쇄 복귀 등
동시성 제어 기법
락킹(locking)
- 트랜잭션이 데이터에 잠금(lock)을 설정하면, 다른 트랜잭션은 해당 데이터에 대해 잠금이 해제(unlock)될 때까지 접근/수정/삭제가 불가능하다.
타임스탬프
- 시스템에서 생성하는 고유 번호인 타임스탬프를 트랜잭션에 부여함으로써 트랜잭션의 접근 순서를 미리 정한다.
적합성(validation)검증
- 먼저 트랜잭션을 수행하고, 트랜잭션을 종료할 때 적합성을 검증하여 데이터베이스에 최종 반영한다.
뷰: 가상의 테이블
- 뷰: 직접 테이블에 접근하는 게 아니라, 테이블에서 사용자가 필요로 하는 부분만 선택하여 만들어 놓은 데이터의 집합이다. 즉, 가상의 테이블이다.
- 보안을 이유로. (접근 가능한 사람을 지정할 수 있다.)
- 뷰 구성을 통해 자주 사용되는 복잡한 SQL문을 매번 작성하지 않아도 된다.
뷰의 종류
| 종류 | 설명 | 비고 |
|---|---|---|
| 심플 뷰(simple view) | 하나의 테이블에서 데이터를 생성한다. | CREATE VIEW 명령어로 생성 |
| 컴플렉스 뷰(complex view) | 여러 개의 테이블을 조인하여 데이터를 생성한다. | CREATE VIEW 명령어로 생성한다. |
| 인라인 뷰(inline view) | SELECT 문의 FROM 절에 기술한 SELECT문 | 1회용 뷰로 권한을 제어할 수 없다. |
인덱스
실무 활용팁
- 분석시스템(OLAP)와 운영 시스템(OLTP)에 따라 인덱스 유형이 달라진다.
- 인덱스가 지나치게 많으면, 과부하가 발생한다.
- 조인할 때 옵티마이저가 인덱스를 사용하도록 유도해야 한다.
- DBMS 운영 상황에 따라 별도의 저장 공간으로 지정이 필요하거나, 재생성이 필요할 수 있다.
- DML 문을 자주 사용하는 경우에는 데이터베이스 시스템 성능에 악영향을 끼칠 수 있다.
언제 사용하는 것을 추천?
- 열이 WHERE절의 조인 조건으로 자주 사용된다.
- 열이 다양한 값을 포함한다. 또한 많은 수의 null 값을 포함한다.
- 테이블 크기가 대형이고, 대부분의 질의가 행의 2~4% 이하보다 적게 읽어 들일 것으로 예상된다.
인덱스 비추천
- 위의 1~3의 반대 내용
- 테이블이 자주 갱신된다. DML 문을 자주 사용하면 인덱스의 유지 작업을 위해 상대적으로 더 많은 시간이 걸린다.
NCS 기반의 ORACLE SQL&PL/SQL - 허태성 지음
테이블 생성 쿼리문 (복붙해서 사용)
학사관리
-- 학생 테이블을 생성한다.
create table student(
stu_no char(9),
stu_name varchar2(12),
stu_dept varchar2(20),
stu_grade number(1),
stu_class char(1),
stu_gender char(1),
stu_height number(5,2),
stu_weight number(5,2),
constraint p_stu_no primary key(stu_no)
);
-- 학생(student) 테이블에 데이터를 삽입한다.
insert into student values(20153075, '옥한빛','기계',1,'C','M',177,80);
insert into student values(20153088, '이태연','기계',1,'C','F',162,50);
insert into student values(20143054, '유가인','기계',2,'C','F',154,47);
insert into student values(20152088, '조민우','전기전자',1,'C','M',188,90);
insert into student values(20142021, '심수정','전기전자', 2,'A','F', 168,45);
insert into student values(20132003, '박희철','전기전자',3,'B','M',null,63);
insert into student values(20151062, '김인중','컴퓨터정보',1,'B','M',166,67);
insert into student values(20141007, '진현무','컴퓨터정보',2,'A','M',174,64);
insert into student values(20131001, '김종헌','컴퓨터정보',3,'C','M',null,72);
insert into student values(20131025, '옥성우','컴퓨터정보',3,'A','F',172,63);
-- 과목 테이블을 생성한다.
create table subject(
sub_no char(3),
sub_name varchar2(40),
sub_prof varchar2(12),
sub_grade number(1),
sub_dept varchar2(40),
constraint p_sub_no primary key(sub_no)
);
-- 과목(subject) 테이블에 데이터를 삽입한다.
insert into subject values('111', '데이터베이스','이재영',2,'컴퓨터정보');
insert into subject values('110', '자동제어','정순정', 2, '전기전자');
insert into subject values('109', '자동화설계','박민영',3, '기계');
insert into subject values('101', '컴퓨터개론','강종영', 3, '컴퓨터정보');
insert into subject values('102', '기계공작법','김태영',1,'기계');
insert into subject values('103', '기초전자실','김유석', 1,'전기전자');
insert into subject values('104', '시스템분석설계','강석현', 3,'컴퓨터정보');
insert into subject values('105', '기계요소설계', '김명성', 1,'기계');
insert into subject values('106', '전자회로실험','최영민',3,'전기전자');
insert into subject values('107', 'CAD용실습','구봉규',2,'기계');
insert into subject values('108', '소프트웨어공학','권민성', 1,'컴퓨터정보');
-- 수강 테이블
create table enrol(
sub_no char(3),
stu_no char(9),
enr_grade number(3),
constraint p_course primary key(sub_no, stu_no)
);
-- 수강(enrol) 테이블에 다음과 같이 데이터를 입력한다.
insert into enrol values('101', '20131001',80);
insert into enrol values('104', '20131001',56);
insert into enrol values('106', '20132003',72);
insert into enrol values('103', '20152088',45);
insert into enrol values('101', '20131025',65);
insert into enrol values('104', '20131025',65);
insert into enrol values('108', '20151062',81);
insert into enrol values('107', '20143054',41);
insert into enrol values('102', '20153075',66);
insert into enrol values('105', '20153075',56);
insert into enrol values('102', '20153088',61);
insert into enrol values('105', '20153088',78);
인사관리
-- 부서 테이블 생성
create table dept(
deptno number(2),
contraint pk_dept primary key,
dname varchar2(14),
loc varchar2(13)
);
-- 부서 데이터 삽입한다
insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30,'SALES', 'CHICAGO');
insert into dept values (40,'OPERATIONS', 'BOSTON');
-- 사원(emp) 테이블을 생성한다.
create table emp(
empno number(4)
constraint pk_emp primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
);
alter table emp
add constraint fk_mgr
foreign key(mgr)
references emp(empno);
alter table emp
add constraint fk_deptno
foreign key(deptno)
references emp(empno);
--alter table emp drop constraint fk_deptno;
--사원(emp) 테이블에 데이터를 입력한다.
insert into emp values (7839,'KING', 'PRESIDENT',NULL, to_date ('17-11-1981', 'dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, to_date ('2-4-1981', 'dd-mm-yyyy'), 2975,NULL,20);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, to_date ('1-5-1981', 'dd-mm-yyyy'), 2850,NULL,30);
insert into emp values (7782,'CLARK', 'MANAGER', 7839, to_date ('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788, 'SCOTT','ANALYST', 7566, to_date ('13-07-1987', 'dd-mm-yyyy'),3000,NULL, 20);
insert into emp values (7902, 'FORD', 'ANALYST',7566, to_date ('3-12-1981','dd-mm-yyyy'), 3000,NULL, 20);
insert into emp values (7499,'ALLEN', 'SALESMAN', 7698, to_date ('20-2-1981', 'dd-mm-yyyy'),1600,300,30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, to_date ('22-2-1981', 'dd-mm-yyyy'), 1250,500,30);
insert into emp values (7654, 'MARTIN', 'SALESMAN',7698, to_date ('28-9-1981', 'dd-mm-yyyy'),1250,1400,30);
insert into emp values (7844, 'TURNER','SALESMAN', 7698, to_date ('8-9-1981', 'dd-mm-yyyy'), 1500,0,30);
insert into emp values (7900,'JAMES', 'CLERK', 7698, to_date ('3-12-1981', 'dd-mm-yyyy'), 950,NULL,30);
insert into emp values (7934, 'MILLER', 'CLERK',7782, to_date ('23-1-1982', 'dd-mm-yyyy'),1300,NULL,10);
insert into emp values (7369, 'SMITH','CLERK', 7902, to_date ('17-12-1980', 'dd-mm-yyyy'), 800,NULL,20);
insert into emp values (7876,'ADAMS', 'CLERK', 7788, to_date ('13-07-1987', 'dd-mm-yyyy'),1100,NULL, 20);
-- 급여 테이블 생성
create table salgrade(
grade number(7,2),
losal number(7,2), -- 최저급여
hisal number(7,2) -- 최고급여
);
-- 급여 테이블에 데이터 생성
insert into salgrade values(1, 700,1200);
insert into salgrade values(2, 1201,1400);
insert into salgrade values(3, 1401,2000);
insert into salgrade values(4, 2001,3000);
insert into salgrade values(5, 3001,9999);