23-02-09 데이터 베이스

※ 데이터 무결성

 

1. 데이터 무결성 제약조건 개념

 1) 개념

  - 유효성검증 

  - 데이터의 정확성과 일관성을 보장

  - 데이터의 정확성을 유지하여 다양한 종류의 업무규칙 고려한 예

 2) 장점

  - 테이블 생성시 무결성 제약조건을 정의 가능

  - 테이블에 대해 정의, 데이터 딕셔너리에 저장되므로 응용프로그램에서 입력된 모든 데이터에 대해 동일하게 적용

  - 제약조건을 활성화, 비활성화 할 수 있는 융통성

 

 3) 종류

무결성 제약 조건	설명
NOT NULL	열이 NULL을 포함할 수 없음
고유키 (UK)	테이블의 모든 행에서 고유한 값을 갖는 열 또는 열조합을 지정
기본키 (PK)	해당 칼럼값은 반드시 존재해야하며 유일해야함 UNIQUE, NOT NULL 제약조건을 결합한 상태
참조키 (외래키/ FK)	한 열과 참조된 테이블의 열간에 외래 키 관계를 설정하고 시행
CHECK	해당 칼럼에 저장 가능한 데이터 값의 범위나 조건 지정

2. 데이터 무결성 제약조건의 종류

1)  NOT NULL 무결성 제약조건

 - 테이블 레벨이 아닌 열 레벨로만 지정가능

 - NOT NULL 무결성 제약조건이 지정된 칼럼은 반드시 값을 입력해야함 생략시 오류발생

 - 기존 데이터를 NULL로 수정하는 경우에도 오류 발생

 

 2) 고유키 무결성 제약조건

  - NULL 입력 가능

  - 한 테이블 내에서 칼림이 동일한 값을 가질 수 없을음 정의하는 제약조건

  - 열레벨 또는 테이블레벨로 정의 할 수 있음

 

 3) 기본키 무결성 제약조건

  - 테이블내에 저장된 행을 유일하게 식별할 수 있는 식별자로 사용

  - 관계형 데이터 모델에서 테이블은 행을 원소로 가지는 집합으로 정의되어 집합내의 모든 행을 구벼ㅓㄹ할 수 있는 식별자(기본키)가 필요

 

 4) 참조 무결성 제약조건

  - 한 테이블의 칼럼 값이 자신, 다른 테이블의 칼럼 값 중에 하나와 일치 시키기 위한 제약조건

DML 명령문	부모 테이블	자식 테이블
INSERT	참조 키 값이 고유한 경우에만 가능	외래 키 값이 참조 키 값중의 하나와 일치하거나 null인 경우에만 가능
UPDATE	참조 키 값을 참조하는 자식 테이블의 칼럼값이 없는경우에만 가능	수정되는 외래 키 값이 참조 키 값 중의 하나와 일치할 경우에 가능
DELETE RESTRICT	참조 키 값을 참조하는 자식 테이블의 칼럼 값이 없는 경우에만 가능	항상 가능
DELETE CASCADE	항상 가능	항상 가능

 

5) CHECK 무결성 제약조건

  - 칼럼에 허용 가능한 데이터의 범위나 조건을 지정

 

3. 무결성 제약조건 생성방법

 1) 무결성 제약조건 생성

  - 테이블 생성가 동시에 정의

  - 테이블을 생성한 이후에 추가, 삭제 할 수 있음

  - 제약조건명을 지정하지 않으면 SYS_Cn 형태로 자동 생성(기본키에는 제약조건이름을 꼭 붙여야함)

  - 칼럼 레벨 : 칼럼 정의시 해당 칼럼별로 지정

  - 테이블레벨 : 제약조건이 하나 이상의 칼럼을 참조, 지정

2) 생성 예

 * 강좌(subject) 테이블 인스턴스

CREATE TABLE SUBJECT (
    SUBNO NUMBER(5) CONSTRAINT PK_SUBJECT PRIMARY KEY,
    SUBNAME VARCHAR2(20) NOT NULL,
    TERM VARCHAR2(1) CONSTRAINT CK_TERM CHECK (TERM IN (1,2)) ,
    TYPE VARCHAR2(5)
);

 * 수강(sugang) 테이블 인스턴스

CREATE TABLE SUGANG (
    STUDNO NUMBER(5) CONSTRAINT FK_SUGANG_STUDNO REFERENCES STUDENT, -- PK, FK
    SUBNO NUMBER(5), -- PK, FK
    REGDATE DATE,
    RESULT NUMBER(3),
    CONSTRAINT PK_SUGANG PRIMARY KEY(STUDNO, SUBNO), -- 테이블레벨
    CONSTRAINT FK_SUGANG_SUBNO FOREIGN KEY(SUBNO) REFERENCES SUBJECT
);

5. 무결성 제약조건에 의한 DML 명령문의 영향

6. 무결성 제약조건 삭제

 

※ 인덱스

1. 개념

 - SQL명령문의 처리속도를 향상시키기 위한 컬럼을 대상으로 생성하는 객체

 - 인덱스는 포인터를 이용하여 테이블에 저장된 데이터를 랜덤 액세스 하기 위한 목적으로 사용

 

 2. 종류

  고유, 비고유, 단일, 결합, DECENDING INDEX, 함수기반

 

3. 구조

 - 인덱스의 내부 구조는 B*-트리 형식으로 구성

※ 흑적트리

 

4. 인덱스의 효율적인 사용방법

 4-1) 인덱스가 효율적인 경우

  - WHERE절이나 조인 조건절에서 자주 사용되는 컬럼(검색 대상이 되는 컬럼이였을 때)

  - 전체 데이터중에서 일부를 검색하는경우 (페이징)

  - 두개이상의 컬럼이 where절이나 조인 조건에서 자주 사용되는 경우

  - 테이블에 저장된 데이터의 변경이 드문 경우

  - 열에 널 값이 많이 포함된 경우, 열에 광범위한 값이 포함된 경우

 

 4-2) 생성

5. 고유인덱스(unique index)

 - 유일한 값을 가지는 칼럼에 대해 생성하는 인덱스로 모든 인덱스 키는 테이블의 하나의 행과 연결

 

예제) 부서 테이블에서 name 칼럼을 고유 인덱스로 생성, 고유인덱스의 이름을 idx_dept_name으로 정의

CREATE UNIQUE INDEX IDX_DEPT_NAME ON DEPARTMENT(DNAME);

6. 비고유인덱스(non unique index)

 - 고유인덱스보다 조금 느림(cost가 높음)

 - 비고유인덱스는 중복된 값을 가지는 칼럼에 대해 생성하는 인덱스로 하나의 인덱스 키는 테이블의 여러행과 연결될 수 있다

예제) 학생테이블의birthdate 칼럼을비고유인덱스로생성하여라. 비고유인덱스의이름은idx_stud_birthdate로정의한다

CREATE INDEX IDX_STUD_BIRTHDATE ON STUDENT(BIRTHDATE);

7. 단일인덱스, 결합인덱스

8. DECENDING INDEX

 

9. 함수 기반 인덱스  (function based index)

10. 인덱스 정보 조회

 user_indexes - 인덱스 이름과 유일성 여부 등을 확인

 

11. 인덱스 재구성

  - 인덱스 재구성은 인덱스를 정의한 테이블의 칼럼 값에 대해 변경 작업이 자주 발생하여, 불필요하게 생성된 인덱스 내부 노드를 정리하는 작업

 

 

※ 사용자 권한 제어

1. 데이터베이스 보안

 1) 다중 사용자 환경

  - 불법적인 접근 및 유출 방지를 위해 보안대책 필요

  - 다중 사용자 환경에서는 데이터베이스 관리자의 암호를 철저하게 관리

 

 2) 중앙 집중적인 데이터 관리

 - 파일시스템보다 보안이 취약할 수 있으므로 철저한 보안 대책 필요

 

 3) 시스템 보안

  - 시스템 관리 차원에서 데이터베이스 자체에 대한 접근 권한을 관리

  - 데이터베이스 관리자는 사용자 계정, 암호관리, 사용자별 허용가능한 디스크공간 할당

 

 4) 데이터 보안

  - 사용자별로 객체를 조작하기 위한 동작 관리

  - 데이터베이스 객체에 대한 접근 권한을 관리

 

2. 권한

 2-1) 권한이란

  - 권한은 사용자가 데이터베이스 시스템을 관리하거나 객체를 이용할 수 있는 권리

 

 2-2) 종류

 2-3) 데이터베이스 관리자가 가지는 시스템 권한

  - 사용자 생성, 삭제, 사용자계정에서 객체의 생성 또는 수정, 데이터베이스 백업 관리

2-4) 일반 사용자가 가질 수 있는 시스템 권한

  - 사용자가 생성한 객체를 관리, 내장 프로시저를 관리

2-5) 시스템 권한 부여

 - 시스템 권한은 특정 사용자나 모든 사용자에게 부여 가능

 - 롤에도 시스템권한을 부여 가능

 - grant 명령문 사용

-- 권한부여
GRANT SELECT ON HR.DEPARTMENTS TO SAMPLE;

-- 권한철회
REVOKE SELECT ON HR.DEPARTMENTS FROM SAMPLE;

-- 권한 부여시 SAMPLE에서 조회가능
SELECT * FROM HR.EMPLOYEES;
SELECT * FROM HR.DEPARTMENTS;

-- 권한부여시 SAMPLE에서 JOIN 가능
SELECT * 
FROM HR.EMPLOYEES
JOIN HR.DEPARTMENTS USING(DEPARTMENT_ID);

-- SAMPLE에서 HR에 직접 권한부여
GRANT SELECT ON STUDENT TO HR;

3. 롤(role)

 3-1) 개념

  - 다수 사용자와 다양한 권한을 효과적으로 관리하기 위하여 서로 관련된 권한을 그룹화한 개념

 

 3-2) 롤 종류

 3-3) 롤 생성

 3-4) 롤에 대한 부여

 

4. 동의어

 4-1) 개념

  - 모든 객체를 대상으로 이름을 새로 붙이는것?

  - 다른사용자가 객체를 조회할 때에는 소유자의 아이디를 객체 이름 앞에 첨부해야함

  - 동의어는 하나의 객체에 대해 다른 이름을 정의하는 방법

 

* DUAL은 공용 동의어이다.

 

※ 계층적 질의문

 1.개념

  - 관계형 데이터 베이스 모델은 평면적인 2차원 테이블 구조

  - 관계형 데이터 베이스에서 데이터간의 부모관계를 표현할 수 있는 칼럼을 지정하여 계층적인 관계를 표현

  - 

 

2. 사용법

  - SELECT 명령문에서 START WITH와 CONNECT BY절을 이용

  - 계층적 질의문에서는 계층적인 출력 형식과 시작위치 제어

  - 출력 형식은 TOP-DOWN 또는 BOTTOM-UP

 

3. 출력형태 

예제) 사용예계층적질의문을사용하여부서테이블에서학과,학부,단과대학을검색하여단대,학부학과순으로top-down 형식의계층구조로출력하여라. 단, 시작데이터는10번부서이다.

SELECT LEVEL, DEPTNO, DNAME, COLLEGE
FROM DEPARTMENT
START WITH DEPTNO = 10
CONNECT BY PRIOR DEPTNO = COLLEGE;

예제2)

SELECT LEVEL, DEPTNO, DNAME, COLLEGE
FROM DEPARTMENT
START WITH DEPTNO = 101
CONNECT BY PRIOR COLLEGE = DEPTNO ;