[oracle] VIEW, 시퀀스, 인덱스, 데이터모델링

oin이 하는 과정들

서브쿼리

 

반복, 망각의동물

 

VIEW- 하나의 창 역할
1. 조인 - 복잡한 쿼리문을 단순화 시키기 위해
2. 보안 - 특수한 내용들을 제외한 것을 보여주기 위해

SCOTT계정에 VIEW에 대한 권한 부여
->GRANT CREATE VIEW TO SCOTT;

뷰 생성
->CREATE VIEW VIEW_EMP10
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO=10;

 

SELECT * FROM VIEW_EMP10;

 

뷰 데이터 딕셔너리
DESC USER_VIEWS;

TEXT->VIEW의 생성과정을 출력

SELECT VIEW_NAME, TEXT
FROM USER_VIEWS;

 

단순 뷰
- 하나의 테이블로 생성
->INSERT INTO VIEW_EMP10
VALUES (9000, 'ANGEL', 10);
//20번 부서이기 때문에 10번 뷰만 보여지는 곳에선
보여지지가 않는다.
->INSERT INTO VIEW_EMP10
VALUES (8000, 'ANGELS', 20);

해당 VIEW에 보여질 수 있는 것만 입력 가능
->CREATE VIEW VIEW_EMP20
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO=20 WITH CHECK OPTION;

 

INSERT INTO VIEW_EMP20
VALUES (9000, 'ANGEL', 10);

 

그룹 함수를 사용한 단순 뷰
- 반드시 별칭 사용
->CREATE VIEW VIEW_SAL
AS
SELECT DEPTNO SUM(SAL) AS 'SALSUM', AVG(SAL) AS 'SALAVG'
FROM EMP
GROUP BY DEPTNO;

 

복합 뷰
- 여러 개의 데이터들을 한 번에 보여주기 위해서
->CREATE VIEW VIEW_EMP_DEPT
AS
SELECT E.EMPNO, E.ENAME, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
ORDER BY EMPNO DESC;

 

SELECT * FROM VIEW_EMP_DEPT;

 

뷰 삭제

->DROP VIEW VIEW_SAL;

 

뷰 수정

->CREATE OR REPLACE VIEW VIEW_EMP10
AS
SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP
WHERE DEPTNO=10;

 

기존 테이블 없이 뷰 생성

-> CREATE OR REPLACE FORCE VIEW VIEW_EMP10
AS
SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP
WHERE DEPTNO=10;

 

ROWNUM(뷰 활용)
- INSERT문에 의해 입력 순서에 따라 1씩 증가하면서 값이 지정

- 부분 검색하면서 많이 쓴다.

//입사일 기준으로 ROWNUM
->SELECT ROWNUM, EMPNO, ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE;

//입사일 기준으로 ROWNUM 테이블 생성
->CREATE OR REPLACE VIEW VIEW_HIRE
AS
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE;
//입사일 기준으로 ROWNUM 테이블을 생성해서
입사일이 빠른 사람 5명만 출력
SELECT ROWNUM, EMPNO, ENAME, HIREDATE
FR0M VIEW_HIRE
WHERE ROWNUM <= 5;

 

인라인 뷰로 TON-N 구하기
SELECT ...
FROM ALIAS ... (SELECT ...
... ) ALIAS //SUB 쿼리=인라인 뷰
...); //MAIN 쿼리

->SELECT EMPNO, ENAME, HIREDATE
FROM (SELECT EMPNO, ENAME, HIREDATE
 FROM EMP
 ORDER BY HIREDATE)
WHERE ROWNUM<=5;

 

시퀀스

- 유일한 값을 생성
- 기본 키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성하는 오라클 객체
->CREATE SEQUENCE sequence_name
 STATR WITH n      1. 시퀀스의 시작 값 지정
 INCREMENT BY    2. 시퀀스의 증가 값

 

EMP01 테이블 생성
->CREATE TABLE EMP01
AS
SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE 1=0;

 

사원번호를 자동으로 할당받아 데이터 추가
->INSERT INTO EMP01
VALUES(EMP_SEQ.NEXTVAL, 'JULIA', SYSDATE);

 

시퀀스의 현재 값 알아보기
SELECT EMP_SEQ.CURRVAL FROM DUAL;

 

시퀀스의 삭제
DROP SEQUENCE sequence_name;

 

인덱스

- 데이터의 빠르게 접근하는 것을 도와줌
- 데이터베이스 성능 향상을 도와주는 객체
장점 
1. 검색 속도가 빨라진다.
2. 시스템에 걸리는 부하를 줄여 시스템 성능을 향상
단점
1. 인덱스 생성하는 데 시간이 걸린다.
2. 데이터 변경작업이 자주 일어나면 오히려 성능이 저하된다.

//인덱스 생성
->CREATE INDEX INDEX_EMPNO_EMP
ON EMP01(EMPNO);
//인덱스 확인
->SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME IN('EMP', 'EMP01');

//인덱스 삭제
DROP INDEX INDEX_EMPNO_EMP;

주제선정 -> 요구분석 -> 
*화면설계와 DB설계는 서로 조화를 이루어야함
화면설계(내용) = DB설계(TABLE생성, DATA초기화)
-> 개발(JSP, JAVA, H/J, AJAX)

데이터 모델링

- 정보화 시스템을 구축하기 위해 어떤 데이터가 존재하는 지,
업무가 필요로 하는 정보는 무엇인지 분석하는 방법

데이터 모델링의 관점
1. 데이터 관점(DATA, WHAT)
2. 프로세스 관점(PROCESS, HOW)
3. 상관 관점(INTERACTION)
 3_1. 업무에 관여하는 어떤 것
 3_2. 업무가 관여하는 어떤 것 간의 관계
 3_3. 어떤 것이 가지는 성격

개념:타입/클래스:어커런스
어떤것:엔티티타입-테이블:엔티티
어떤것간의관계:관계-관계:패어링

엔티티타입
1.업무에 필요하고 유용한 정보를 지정하고 관리하기 위한 것으로
2.영속적으로 존재하는 단위

엔티티타입의 규칙
1. 업무하고 반드시 필요로해서 찾는 정보
2. 식별자에 의해 식별이 가능해야 한다,
3. 엉속적으로 존재하는 엔티티 집합이어야한다
4. 업무프로세스에 의해 이용
- 업무프로세스에 이용되지 않는 엔티티타입은 엔티티 타입이 아니다.
5. 반드시 속성이 있어야한다.
- 속성이 존재하지 않는 오브젝트 타입은 엔티티타입이 아니다.
6. 다른 데이터 엔티티타입과 최소 한 개이상의 관계가 있어야한다.

대리식별자

관계
- 두 개의 엔티티타입 사이의 논리적인 관계
- 일대일 관계
- 일대다 관계:가장 자연스러운 관계
- 다대다 관계: 하나의 주문으로 여러 개의 제품을 주먼하는 것

주식별자
주식별자를 선정하기 위한 속성의 수는 적게 해야 한다.
복잡한 부모 엔티티타입의 주식별자에 의해 자식 엔티티타입의 주식별자도 복잡해진다

비식별자 관계 생성 이유
1. 부모없는 자식이 생성될 수 있는 경우
2. 부모가 있었지만 자식만 남겨두고 먼저 소멸될 수 있는 경우
3. 여러 개의 엔티티타입이 하나의 엔티티타입으로 통합되어 표현될 경우
4. 자식 엔티티타입에 별도의 주식별자를 가지고 있는 것이 더 유리하다고 판단되는 경우
5. 자식과 관련이 있는 엔티티타입으로의 주식별자 상속을 차단하기 위해서

속성
- 업무에 필요한 엔티티에서 관리하고자 하는 분리되지 않는 최소의 데이터 단위

1. 엔티티타입의 도출
2. 식별자 관계
3. 정규화

정규화
- 다양한 유형의 검사를 통해 데이터 모델을 좀 더 구조화하고 개선시켜 나가는 절차에 관련된 이론
- 테이블에 중복된 데이터가 없도록 하는 것(데이터 무결성)

제1정규화
- 복수의 속성값을 갖는 속성을 분리
제2정규화
- 주식별자에 종속적인지 않은 속성의 분리, 부분종속 속성을 분리
- 주식별자에 종속되지 않는 것은 나가도록..
제3정규화
- 속성에 종속적인 속성의 분리, 이전종속 속성의 분리
- 주식별자에게도 종속되고, 비식별자에게도 종속되는 것은 분리
제4정규화
제5정규화 - 실제 존재 하지 않는..

정규화할 때마다 관계 설정이 되어있는 테이블이 생성된다.