8/12 Foreign Key, View, 계층적 질의문, TableSpace

-- 1. Foreign Key (중요 : 별3개)
-- 1) Restrict : 삭제할 때 작업 없음.(기본설정) 외래키 연관관계 때문에 자식이 존재하면 삭제 안됨 
-- 선언 : Emp Table에서 REFERENCES DEPT(DEPTNO)
-- 예시 : 오류 integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
    DELETE dept WHERE deptno = 10;
-- 2) Cascading Delete : 같이 삭제됨. 종속삭제.
-- 선언 : Emp Table에서 REFERENCES DEPT (DEPTNO) ON DELETE CASCADE
-- 예시 : 부모 DEPT의 deptno76을 삭제하면 이걸 참조하는 자식 EMP의 데이터 전체가 삭제된다.
    DELETE dept WHERE deptno = 76;
-- 3) SET NULL : 종속 NULL
-- 선언 : Emp Table에서 REFERENCES DEPT (DEPTNO) ON DELETE SET NULL
-- 예시 : 부모 DEPT의 deptno75를 삭제하면 이걸 참조하는 EMP의 deptno만 null이 되고 나머지는 데이터는 그대로이다.
    DELETE dept WHERE deptno = 75;

 

-- 2. View
-- View : 하나 이상의 기본 테이블이나 다른 뷰를 이용하여 생성되는 가상 테이블
--        뷰는 데이터딕셔너리 테이블에 뷰에 대한 정의만 저장(물리적인 것이 아니고 정보만 가지고 있음)
--        장점 : 보안 
--        단점 : Performance(성능)은 더 저하

--공개해도 괜찮은 정보들만 넣어놓고 뷰(가상의 테이블)을 만들어서 공개하는 것임.
--그래서 보안을 강화시킬 수 있음.
CREATE OR REPLACE VIEW VIEW_PROFESSOR AS
SELECT profno, name, userid, position, hiredate, deptno
FROM professor;

SELECT * FROM VIEW_PROFESSOR;

CREATE OR REPLACE VIEW VIEW_PROFESSOR2 AS
SELECT name, userid, position, hiredate, deptno
FROM professor;

-- 특성1. 원래 테이블 professor에 입력가능. 하지만 제약조건은 그대로 따름.
-- 기본 View : INSERT 가능
INSERT INTO view_professor VALUES(2000, 'view', 'userid', 'position', sysdate, 101);
-- 입력 실패, 기본키 지정 안해줬기 때문
INSERT INTO view_professor(profno, userid, position, hiredate, deptno)
    VALUES(2001, 'userid', 'position', sysdate, 101);
-- 원래 테이블의 기본키가 없는 뷰이므로 데이터를 전혀 입력할 수 없음.
INSERT INTO view_professor2(name, userid, position, hiredate, deptno)
    VALUES(2001, 'userid', 'position', sysdate, 101);
    
-- View 이름 v_emp_sample : emp(empno, ename, job, mgr, deptno)
CREATE OR REPLACE VIEW v_emp_sample AS
SELECT empno, ename, job, mgr, deptno
FROM emp;

-- 복합 View, 통계 View : INSERT 안됨.
CREATE OR REPLACE VIEW v_emp_complex
AS SELECT * FROM emp NATURAL JOIN dept;

-- 실행 안됨
INSERT INTO v_emp_complex (empno, ename, deptno)
    VALUES(1500, '홍길동', '20');
INSERT INTO v_emp_complex (deptno, dname, loc)
    VALUES(77, '공무팀', '낙성대');
--join 컬럼은 입력할 수 없다.
INSERT INTO v_emp_complex (empno, ename, deptno, dname, loc)
    VALUES(1500, '홍길동', 77, '공무팀', '낙성대');

-- 과제
-- 문1) 학생 테이블에서 101번 학과 학생들의 학번, 이름, 학과 번호로 정의되는 단순 뷰를 생성
-- 뷰 명 :  v_stud_dept101
create or replace view v_stud_dept101
as select studno, name, deptno FROM student WHERE deptno = '101';
-- 문2) 학생 테이블과 부서 테이블을 조인하여 102번 학과 학생들의 학번, 이름, 학년, 학과 이름으로 정의되는 복합 뷰를 생성
-- 뷰 명 : v_stud_dept102
--WHERE절에서 처음에 있는 S로 통일하는 것이 좋음
CREATE OR REPLACE VIEW v_stud_dept102
AS SELECT s.studno, s.name, s.grade, d.dname
FROM student s, department d
WHERE s.deptno=d.deptno
AND s.deptno=102;
-- 문3) 교수 테이블에서 학과별 평균 급여와 총계로 정의되는 뷰를 생성
-- 뷰 명 : v_prof_avg_sal, Column 명 : avg_sal sum_sal
CREATE OR REPLACE VIEW v_prof_avg_sal
AS
SELECT deptno, sum(sal) sum_sal, avg(sal) avg_sal
FROM professor
GROUP BY deptno;

-- GROUP 함수 Column 등록 안됨 
INSERT INTO V_PROF_AVG_SAL
VALUES(203,600,300);

-- View 삭제 
DROP VIEW v_stud_dept102;

-- 사용자가 가지고 있는 모든 view 보여주기
SELECT view_name, text FROM USER_VIEWS;

 

뷰의 종류

 

계층적 질의문 사용법

 

-- 계층적 질의문
--관계형 데이터 베이스 모델은 평면적인 2차원 테이블 구조
--관계형 데이터 베이스에서 데이터간의 부모 관계를 표현할 수 있는 칼럼을 지정하여 계층적인 관계를 표현
--하나의 테이블에서 계층적인 구조를 표현하는 관계를 순환관계(recursive relationship)
--계층적인 데이터를 저장한 칼럼으로부터 데이터를 검색하여 계층적으로 출력 기능 제공

--사용법
--SELECT 명령문에서 START WITH와 CONNECT BY 절을 이용
--계층적 질의문에서는 계층적인 출력 형식과 시작 위치 제어
--출력 형식은  top-down 또는 bottom-up
--참고) CONNECT BY PRIOR 및 START WITH절은 ANSI SQL 표준이 아님. 
--그래서 다른 DB에선 사용이 안 될 수도 있다. oracle에서는 사용 가능함.

--이 3가지 알고있어야 함.(중요 : 외우기)
-- 문1) 계층적 질의문을 사용하여 부서 테이블에서 학과, 학부, 단과대학을 검색하여 단대, 학부
-- 학과순으로 top-down 형식의 계층 구조로 출력하여라. 단, 시작 데이터는 10번 부서
-- top-down만 컬럼이 쓰여진 순서대로.
SELECT deptno, dname, college
FROM department
START WITH deptno= 10
CONNECT BY PRIOR deptno = college;

-- 문2) 계층적 질의문을 사용하여 부서 테이블에서 학과,학부,단과대학을 검색하여 학과,학부
-- 단대 순으로 bottom-up 형식의 계층 구조로 출력하여라. 단, 시작 데이터는 102번 부서이다
-- bottom-up은 원래 컬럼을 뒤집음
SELECT  deptno, dname, college
FROM department
START WITH deptno = 102
CONNECT BY PRIOR college = deptno;

-- 문3) 계층적 질의문을 사용하여 부서 테이블에서 부서 이름을 검색하여 단대, 학부, 학과순의
-- top-down 형식으로 출력하여라. 단, 시작 데이터는 ‘공과대학’이고,
-- 각 레벨(LEVEL)별로 우측으로 2칸 이동하여 출력
-- 조부모 1계층, 부모 2계층, 자식 3계층임. (시스템 내부에서 sudo column이 각각 level을 1, 2, 3으로 주어줌)
SELECT LPAD(' ', (LEVEL - 1) *2) || dname 조직도
FROM department
START WITH dname = '공과대학'
CONNECT BY PRIOR deptno = college;

문3) 출력 결과

 

--Table space (중요 : 면접에서 물어볼 수 있음)
-- 정의 : 데이터베이스 오브젝트 내 실제 데이터를 저장하는 공간이다. 
-- 데이터베이스의 물리적인 부분이며, 세그먼트로 관리되는 모든 DBMS에 대해 저장소(세그먼트)를 할당
-- 테이블스페이스를 생성하지 않으면 병목현상이 생길 수 있다. 

--1. TableSpace 생성
CREATE TABLESPACE user5 DATAFILE 'C:\oraclexe\tableSpace\user1.ora' SIZE 100M;
CREATE TABLESPACE user6 DATAFILE 'C:\oraclexe\tableSpace\user2.ora' SIZE 100M;
CREATE TABLESPACE user3 DATAFILE 'C:\oraclexe\tableSpace\user3.ora' SIZE 100M;
CREATE TABLESPACE user4 DATAFILE 'C:\oraclexe\tableSpace\user4.ora' SIZE 100M;

-- 2. user 생성
-- system 계정에서 실행!
-- scott3 / tiger
CREATE USER scott3 IDENTIFIED BY tiger
DEFAULT TABLESPACE user1;   --테이블 스페이스 할당
GRANT DBA TO scott3;

-- 생성한 scott3 계정에서 실행!
-- 이 계정에서 저장하는 dept3 테이블과 같은 데이터들은 아까 할당한 테이블스페이스에 저장됨.
CREATE TABLE DEPT3
        (DEPTNO number(2)   PRIMARY KEY,
         DNAME VARCHAR2(14),
	     LOC   VARCHAR2(13) ) ;

--3. 테이블의 테이블스페이스 변경
-- 1)테이블의 index와 테이블의 테이블스페이스 조회
SELECT index_name, table_name, tablespace_name
FROM user_indexes;
SELECT table_name, tablespace_name
FROM user_tables;

--2) 각 테이블별로 테이블스페이스 변경
-- 해당 INDEX 먼저 변경 후 TABLE의 TABLESPACE 변경
ALTER INDEX SYS_C007006 REBUILD TABLESPACE user1;
ALTER TABLE JOB3 MOVE TABLESPACE user1;

-- 4. TableSpace Size 변경
-- 메모리가 꽉 차면 테이블스페이스를 늘려달라는 메시지가 나옴. 그럴 때 하는 
ALTER DATABASE DATAFILE 'C:\oraclexe\tableSpace\user1.ora' Resize 200M;