뷰 (View), 시퀀스 (Sequence), 모델링

목차

1. 뷰 (View)

1-1 뷰

1-2 View 생성 및 옵션

1-3 Inline View

 

2. 시퀀스 (Sequence)

2-1 시퀀스

2-2 시퀀스 실습

 

3. 모델링

3-1 모델링 개요 

3-2 관계

3-3 erWIN을 이용한 테이블 생성

 

 

1. 뷰 (View)

1-1 뷰

실제로 데이터는 테이블에서 검색하지만 어플리케이션 개발자같은 사람들은 테이블에 액세스해 복잡한 작업을 수행할 수 없기 때문에 뷰를 테이블처럼 이용해 데이터를 검색한다.

뷰는 테이블을 보게 해주는 창문과 같은 역할로, 메모리를 차지하지 않는다. 보통 select 할 때만 사용한다. 

보안에 도움이 될 수 있으나 실제로 보안 용도로 쓰는 경우는 없다.

 

● 뷰의 종류

- 단순 뷰

 : DML 문장 수행이 가능하다.

 

- 복합 뷰

 : 다중 테이블이나 수식 등이 포함된 뷰이다.

 

● 뷰 생성

CREATE [OR REPLACE][FORCE | NOFORCE] VIEW 뷰 (컬럼... )
AS (SELECT 문장: sub query)
[WITH CHECK OPTION [CONSTRAINT constrant_name]]
[WITH READ ONLY [CONSTRAINT constrant_name]];

 

뷰를 실행시키면 실제로는 내부의 서브 쿼리가 동작한다.

WITH 옵션들은 insert, update, delete 가 수행 가능한 뷰일 때만 사용되어 실제로 쓸 일은 거의 없다. 

 

- OR REPLACE

 : ALTER VIEW를 대체한다. 뷰는 ALTER 개념이 없어 수정하고 싶을 때 create or replace로 다시 생성한다.

 : 세그먼트가 아니다.

 

- FORCE

 : 기반 테이블 없이 뷰를 생성한다.

 

- WITH CHECK OPTION

 : 뷰에 의해서 검색 가능한 행만 DML 작업이 가능하도록 제약한다.

 

- WITH READ ONLY

 : 뷰를 통해서는 SELECT만 가능하도록 제한한다.

 

● 뷰 삭제와 확인

DROP VIEW 뷰;
SELECT view_name, text FROM user_views;

 

- text

 : 뷰 생성 서브 쿼리이다.

 : LONG 타입으로 where에 검색 용도로 쓸 수 없다.

 

※ 뷰 생성 주의 사항

뷰를 생성할 수 있는 'CREATE VIEW' 권한이 보통은 RESOURCE, CONNECT 롤에 포함되어 있지 않으므로 사용자에게 반드시 해당 권한을 할당해야 한다. 

 

1-2 View 생성 및 옵션

● 뷰 생성

ex) 일반화학 과목의 학과별 기말고사 평균을 뷰로 생성한다.

SQL> CREATE VIEW ma_result(과목번호, 과목명, 학과, 기말고사평균)
  2  AS SELECT c.cno, cname, major, ROUND(AVG(result))
  3     FROM student s, course c, score r
  4     WHERE s.sno = r.sno AND r.cno  =c.cno
  5     AND cname ='일반화학'
  6     GROUP BY c.cno, cname, major;

SQL> SET LONG 1000		// 1000 byte까지 출력
SQL> SELECT view_name, text FROM user_views;

SQL> DESC ma_result;

SQL> SELECT * FROM ma_result;

 

● WITH CHECK 옵션

select 외의 dml 문장 수행이 가능한 뷰에 사용 가능하다.

SQL> CREATE VIEW st_ch
  2  AS SELECT sno, sname, syear, avr
  3     FROM student
  4     WHERE syear = 1;

SQL> INSERT INTO st_chVALUES ('000001', '시현', 2, 4.0);

SQL> SELECT * FROM student WHERE sname = '시현';

SQL> SELECT * FROM st_chWHERE sname = '시현';	// 검색되지않는다.

 

뷰에 보이지 않는 행도 뷰를 통해 입력될 수 있다.

 

1-3 Inline View

● 인라인 뷰 (Inline View)

데이터베이스 관리자들이 현장에서 즉시 검색해야 할 때 자주 사용한다.

검색보단 데이터베이스 관리 작업에 많이 사용한다.

SELECT ...                               
FROM  (SELECT 문장 : sub query)  [별명]

 

- 인라인 뷰는 FROM절에 쓰인 서브쿼리다.

- 인라인 뷰는 문장이 실행되는 동안만 존재하는 뷰이다.

- 인라인 뷰는 일반 뷰와 동일하게 조인이나 검색에 이용된다.

 

ex) 각 부서별 최소 급여자를 검색한다.

SQL> SELECT eno, ename, d.dno, sal, msal
  2  FROM emp e,
  3       (SELECT dno, MIN(sal) msal FROM emp
  4        GROUP BY dno) d
  5  WHERE e.dno = d.dno
  6  AND sal = msal;

 

 

2. 시퀀스 (Sequence)

2-1 시퀀스

● 시퀀스 생성과 수정

CREATE [| ALTER] SEQUENCE 시퀀스이름
[ START WITH 시작_값			// 기본값: 1
  INCREMENT BY 증가_값			// 기본값: 1
  MAXVALUE [상한값| NOMAXVALUE]		// 기본값: NOMAXVALUE
  MINVALUE [하한값| NOMINVALUE]		// 기본값: NOMINVALUE
  CYCLE | NOCYCLE			// 기본값: NOCYCLE
  CACHE [cache_개수| NOCACHE] ]; 	// 기본값: 20

 

- START WITH : 할당을 시작하는 값을 정의한다.

- INCREMENT BY : 생성되는 값의 증가치를 결정한다.

- MAXVALUE : 생성될 상한값을 정의한다.

- MINVALUE : 하한값을 정의한다.

- CYCLE : 상한값까지 생성한 이후 다시 하한값부터 재생성할 것인지 여부이다.

- CACHE : 메모리에 미리 생성될 값의 개수를 정의한다.

 

● 시퀀스 삭제와 확인, 사용

DROP SEQUENCE 시퀀스;
SELECT sequence_name, max_value, min_value, increment_by,
cache_size, last_number, cycle_flag
FROM user_sequences;

......시퀀스.NEXTVAL ......
......시퀀스.CURRVAL ......

 

- NEXTVAL : 시퀀스로부터 유일한 값을 할당받는다.

- CURRVAL : 시퀀스로부터 마지막으로 할당받은 값을 다시 할당받는다.

- 주로 INSERT문의 VALUES절이나 UPDATE의 SET절에서 많이 사용된다.

 

2-2 시퀀스 실습 

● 시퀀스 생성

시퀀스를 생성하고 설정을 검색한다.

SQL> CREATE SEQUENCE emp_eno_seq
  2  START WITH 1    
  3  INCREMENT BY 1
  4  NOMAXVALUE
  5  NOMINVALUE
  6  NOCYCLE
  7  CACHE 20;
 
SQL> CREATE SEQUENCE dept_dno_seq;

SQL> SELECT sequence_name, max_value, min_value, increment_by,
  2         cache_size, last_number, cycle_flag
  3  FROM user_sequences;

 

emp_eno_seq와 같이 모두 기본값으로 설정한다면 dept_dno_seq와 같이 생성하면 된다. 

 

● 시퀀스 이용

emp table에 시퀀스를 이용해서 행을 입력한다.

SQL> INSERT INTO emp (eno, ename) 
 2  VALUES (emp_eno_seq.nextval, '첫번째');

SQL> SELECT emp_eno_seq.currval FROM dual;

SQL> ROLLBACK;

SQL> SELECT sequence_name, max_value, min_value, increment_by,
  2         cache_size, last_number, cycle_flag
  3  FROM user_sequences;

 

- 롤백 작업 수행 시 시퀀스에 갭이 생긴다. 

- 롤백 작업을 수행하지 않아도 오라클은 알아서 갭을 만들기도 한다.

 

3. 모델링

3-1 모델링 개요 

모델링이란 기업이나 조직의 정보 구조를 개체와 관계를 중심으로 체계적으로 표현하고 문서화하는 기법이다.

정보 시스템의 중심을 데이터 관점에서 접근하는 분석 방법으로, 다음 4가지 구성 요소를 가진다.

 

- 개체 (Entity)             : DB에서는 테이블을 나타낸다.

- 관계 (Relationship)  : DB에서는 foreign key로 관계를 설정한다.

- 속성 (Attribute)         : DB에서는 컬럼을 나타낸다.

- 식별자 (Identifier)    : 보통 주식별자를 의미한다.

 

위의 구성요소로 모든 정보를 파악할 수 있도록 한다.

 

모델링 구성 요소

 

논리 모델링 과정: 모델링 시 개체, 속성 등으로 표현하여 만든다.

물리 모델링 과정: 모델링 시 테이블 명, 컬럼 명 등으로 표현하여 만든다.

 

좌: 논리 모델링 / 우: 물리 모델링

 

3-2 관계

● 관계 표현 요소

관계 표현 요소

 

먼저 양측에 위의 모든 관계 요소를 그려놓고 지워나가며 최종 결과로 부모 자식 관계를 확립한다.

 

관계 표현

 

관계 표현 그림의 첫 번째 표현과 세 번째 표현은 테스트의 용이함을 위해 'o'를 추가하여 표현하기도 한다.

 

3-3 ERwin을 이용한 테이블 생성

ERwin data modeler에서 새 파일을 생성 시 logical/Physical 타입으로 설정하고 파일을 생성한다.

새 파일 생성

 

모델링 실습 시 사용할 항목은 다음의 6가지 항목으로 모델러 화면의 상단에 위치해 있다.

실습 시 사용할 항목

- 왼쪽에서 2번째 항목으로 테이블을 생성한다.

- 우측 3가지 항목으로 테이블 간의 관계를 설정한다. 

- 실선은 *식별관계, 점선은 *비식별관계를 설정한다.

 

*식별관계     : 외부 식별자가 주식별자에 있는 관계
*비식별관계  :  외부 식별자가 일반 속성에 있는 관계

 

 

테이블 간 관계를 설정했을 때, 비식별관계의 경우 다음과 같이 'Nulls Allowed' 설정이 되어있어 부모와 자식 양 측에 'o' 표시가 되어있음을 확인할 수 있다.

관계선을 더블클릭하여 'Nulls Allowed' 설정 확인

 

자식이 받을 데이터가 부모에게 없을 수 없으므로 다음과 같이 'No Nulls' 설정을 하여 부모 측의 'o' 표시를 지운다.

'No Nulls' 설정

 

이처럼 비식별관계는. 'Nulls Allowed'에서 'NO Nulls'로 반드시 설정해 주어야 한다.