[Day13] Oracle 13 - 정규화, 이상현상, 뷰, 시퀀스

Kim_dev 2022. 10. 19. 02:40

[Day13] Oracle13 [10/14]

릴레이션 = 테이블
튜플 = 레코드(행)
속성 = 컬럼

정규화

- 이상현상(삽입,갱신,삭제), 함수 종속성 (  X->Y ), 1NF~ 5NF
- ERD->관계스키마 생성 후  종속성 이론을 이용해서 잘못된 설계된 관계 스키마를 더 작은 속성의 바람직한 스키마로 만들어 가는 과정을 정규화 라고 한다.
- DB 설계 문제점(  데이터 중복 )이 있을 경우 삽입,갱신,삭제할 때 문제가 발생하는 것을 "이상현상"이라고 한다. 이런 이상현상을 제거하는 것을 정규화라고 한다.

데이터 중복 제거 과정(작업) == 정규화

함수적 종속성(FD;Functional Dependency)

- 정규화 테이블의 속성들간의 관련성

DEPT(부서테이블)
PK
결정자 종속자
  X Y
[deptno]    dname loc
10      ACCOUNTING NEW YORK
20       RESEARCH DALLAS
30       SALES CHICAGO
40         OPERATIONS BOSTON

* Y 속성은 X 속성에 함수적 종속적이다.
* X -> Y

1) 완전 함수적 종속
- 어떤 릴레이션 R에서 속성A가 속성 집합일 때, 속성 B가 속성A에 함수적 종속하면서, 속성 A의 어떤 진부분 집합에도 함수적으로 종속하지 않으며, 속성 B가 속성 A에 완전하게 함수적으로 종속한다.
- 여러 개의 속성이 모여서 하나의 기본키를 이룰 경우 ( 복합키 ) 복합키 전체에 어떤 속성이 종속적일 때 "완전 함수적 종속"이라 한다.

복합키
[고객ID] [이벤트ID] A B C
kenik E001
[ X + Y ] -> A
[ X + Y ] -> B
[ X + Y ] -> C

2) 부분 함수적 종속
- 완전 함수적 종속이 아니면 부분 함수적 종속이다.

복합키
   [고객ID] [이벤트ID] 합격여부    등급
   kenik E001 Y gold
복합키 -> 합격여부 O
복합키 -> 등급 X
   고객ID  -> 등급   O

3) 이행 함수적 종속
X->Y, Y->Z
결정자

    dept 테이블
    결정자
    PK
       X      ->                   Y
Y     -> Z
    [deptno] dname       부서장    부서장성별
    부서번호 부서명 홍길동    남자

-- 정규화 --
원부이결다조

1 : 원자값이 아닌 도메인 분해
2 : 부분함수 종속성 제거
3 : 이행함수 종속성 제거
4 : 결정자이면서 후보키 아닌 것 제고
5 : 다치종속 제거
6 : 조인 종속성 이용

1) 제1정규화( NOrmal Form ) 1NF
- 릴레이션에 속한 모든 속성의 도메인이 원자 값(atomic value)으로만 구성되어 있으면 제1정규형에 속한다.

deptno dname loc
10 총무부 l_id(FK) 1
20 영업부 2
30 생산부 1
40 자재부 2
:

[부서지역 테이블]
PK
l_id
1    서울   ... ...
2    부산
3    대구

2) 제2정규화 (2NF)
- 릴레이션이 제1정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제2정규형에 속한다.
- 부분 함수적 종속 -> 제거

복합키
[고객ID + 이벤트ID ] 당첨여부 고객명 고객등급

이벤트 테이블
  E001           노트북 30%     시작일    종료일 ...
  E002           노트북 20%
  E003           폰 50%
  E001           노트북 30%

  이벤트 <신청>
  신청번호(PK) 고객ID 이벤트ID 신청일      당첨여부
  1 고객1      E001 22.1.1        Y

고객테이블
고객ID 고객명 고객등급
고객1

3)제3정규화( 3NF )
- 릴레이션이 제2정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 [이행적 함수 종속]이 되지 않으면 제3정규형에 속한다.
- 이행적 함수 종속 -> 제거

    PK
    X -> Y
    X -> Z
Y -> T
  deptno     부서장      부서명       부서장연락처

   X->Y,    Y->Z

부서테이블
부서번호 부서장사원코드(FK) 부서명

사원테이블
사원코드(PK) 사원명 직급명 연락처

  4) 보이스/코드 정규화( BCNF )
    릴레이션의 함수 종속 관계에서 모든 결정자(복합키)가 후보키이면 보이스/코드 정규형에 속한다.
          복합키

[고객아이디 인터넷강좌] 담당강사번호
[ X Y ] -> Z 완전 함수 종속
Y <- Z

           테이블    [ X  Z ]
           테이블    [Z]    Y


* 제3정규화를 만족하면 BCNF 정규화 거의 없다.  X

* 물리적 dB 모델링
  - 역정규화 - 성능
* EXERD  DB 모델링 -> 오라클 DB 서버 ( 스키마 생성 )
* 엑셀, 메모장 ->  INSERT 자동

뷰( View )

1. 테이블을 보기 위한 창문 정도로 생각.
2. 가상테이블 =  1개 이상의 테이블 또는 뷰를 이용해서 생성된다.
3. 뷰는 "데이터 사전"에 정의 부분만 저장되고 , 실제 데이터는 저장 X
4. 뷰를 사용하는 목적(이유 )
   1) 보안
   2) 편리성
5. 뷰 - SELECT, INSERT, UPDATE, DELETE 가능
6. 제약 조건 설정 가능
7.  user_views :   뷰 조회.
    user_tables : 테이블 정보 조회

SELECT *
FROM user_views;

8. 뷰 종류
1) 단순뷰 ( simple view ) - 1 개
2) 복합뷰 ( complex view ) - 2개 이상.~
9. 뷰 생성 형식
【형식】
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름
[(alias[,alias]...]
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY];

OR REPLACE  : 같은 이름의 뷰가 있을 경우 무시하고 다시 생성
FORCE : 기본 테이블의 유무에 상관없이 뷰를 생성
NOFORCE : 기본 테이블이 있을 때만      뷰를 생성

ALIAS : 기본 테이블의 컬럼이름과 다르게 지정한 뷰의 컬럼명 부여

WITH CHECK OPTION : 뷰에 의해 access될 수 있는 행(row)만이 삽입, 수정 가능
WITH READ ONLY :  DML 작업을 제한(단지 읽는 것만 가능)

예) 판매량 확인 - 쿼리

SELECT b.b_id, title, price, g.g_id, g_name, p_date, p_su  
FROM book b JOIN danga  d ON b.b_id = d.b_id
            JOIN panmai p ON  b.b_id = p.b_id
            JOIN gogaek g ON  p.g_id = g.g_id;

-- 뷰 생성

GRANT CREATE VIEW TO scott;

CREATE OR REPLACE FORCE VIEW panView
( bookid, booktitle, bookprice, gogaekid, gogaekname, pdate, psu )
AS (

SELECT b.b_id , title, price, g.g_id, g_name, p_date, p_su  
FROM book b JOIN danga  d ON b.b_id = d.b_id
            JOIN panmai p ON  b.b_id = p.b_id
            JOIN gogaek g ON  p.g_id = g.g_id
);

-- 뷰를 사용해서 전체 판매 금액

SELECT SUM( psu * BOOKPRICE ) 전체판매금액
FROM panview;

-- 뷰 생성 : gogaekview
-- 년도, 월, 고객코드, 고객명, 판매금액총합 (  년도별 월별 )  조회

-- panmai :  p_date (  year, month ) , p_su
-- gogaek :  g_id, gname
-- danga  :                             price

-- ORA-00979: not a GROUP BY expression
-- ORA-00907: missing right parenthesis

CREATE OR REPLACE FORCE VIEW gogaekview 
  -- [alias,,,,]
AS 
        SELECT
              -- p_date
              TO_CHAR( p_date, 'YYYY')   year
              , TO_CHAR( p_date, 'MM')   month
              , g.g_id, g_name
              ,  SUM(  p_su * price ) amt
        FROM panmai p  JOIN gogaek g ON p.g_id = g.g_id
                       JOIN danga d  ON d.b_id = p.b_id
        GROUP BY    TO_CHAR( p_date, 'YYYY') 
              , TO_CHAR( p_date, 'MM'), g.g_id, g_name    
        ORDER BY   year
              , month

-- 뷰를 사용해서 DML 작업 --

CREATE TABLE tbl_view1(
  aid   number primary key
  , name varchar2(20) not null
  , tel varchar2(20) not null
  , memo varchar2(100)
)
-- Table TBL_VIEW1이(가) 생성되었습니다.
CREATE TABLE tbl_view2(
  bid number primary key
  , aid number CONSTRAINT FK_tblview2_aid
         REFERENCES tbl_view1(aid) ON DELETE CASCADE -- FK
  , score number(3)
)

INSERT INTO tbl_view1 (aid, NAME, tel) VALUES (1, 'a', '1');
INSERT INTO tbl_view1 (aid, name, tel) VALUES (2, 'b', '2');
INSERT INTO tbl_view1 (aid, name, tel) VALUES (3, 'c', '3');
INSERT INTO tbl_view1 (aid, name, tel) VALUES (4, 'd', '4');

INSERT INTO tbl_view2 (bid, aid, score) VALUES (1, 1, 80);
INSERT INTO tbl_view2 (bid, aid, score) VALUES (2, 2, 70);
INSERT INTO tbl_view2 (bid, aid, score) VALUES (3, 3, 90);
INSERT INTO tbl_view2 (bid, aid, score) VALUES (4, 4, 100);

 COMMIT;

-- 단순뷰 : 1개 테이블

CREATE OR REPLACE VIEW aView
AS
  -- SELECT aid, name, memo  -- , tel varchar2(20) not null  X
  SELECT aid, name,tel
  FROM tbl_view1;

-- 뷰를 사용해서 DML 작업

INSERT INTO AVIEW (aid, name, memo ) VALUES ( 5, 'f', null );

-- ORA-01400: cannot insert NULL into ("SCOTT"."TBL_VIEW1"."TEL")
-- 왜? -> tbl_view1의 NOT NULL 조건이 적용되기 때문에

INSERT INTO AVIEW (aid, name, tel ) VALUES ( 5, 'f', '5' );

--뷰 : 1 행 이(가) 삽입되었습니다.

-- 단순뷰
-- 복합뷰

CREATE OR REplace VIEW abview
AS
SELECT a.aid, name, tel, bid, score
FROM tbl_view1 a JOIN tbl_view2 b ON a.aid = b.aid
WITH READ ONLY; -- SELECT 조회만 하겠다,         I/U/D X  읽기 전용
-- View ABVIEW이(가) 생성되었습니다.
SELECT *
FROM abview;

-- 뷰 삭제
DROP TABLE

DROP VIEW aview;
DROP VIEW abview;
-- View ABVIEW이(가) 삭제되었습니다.

[ WITH CHECK OPTION ]
- 뷰에 의해 access될 수 있는 행(row)만이 삽입, 수정 가능

SELECT *
FROM tbl_view2;

bid aid score
1 1 80
2 2 70
3 3 90
4 4 100

CREATE OR REPLACE VIEW bview
AS
  SELECT bid, aid, score
  FROM tbl_view2
  WHERE score >= 90;

UPDATE bview
SET   score = 70
WHErE bid = 3;

CREATE OR REPLACE VIEW bview
AS
  SELECT bid, aid, score
  FROM tbl_view2
  WHERE score >= 90
  WITH CHECK OPTION  CONSTRAINT CK_BVIEW;

UPDATE bview
--SET   score = 70  -- UPDATE X
SET   score = 170  -- UPDATE O
WHERE bid = 3; -- 90

-- ORA-01402: view WITH CHECK OPTION where-clause violation

INSERT INTO bview ( bid, aid, score) VALUES ( 5, 4, 10);

-- ORA-01402: view WITH CHECK OPTION where-clause violation

INSERT INTO bview ( bid, aid, score) VALUES ( 5, 4, 110);

--1 행 이(가) 삽입되었습니다.

뷰 사용이유 ?
-- 자주 사용되는 긴 쿼리( SELECT ) -> 뷰 생성 , 보안, 편리성

[시퀀스 SEQUENCE ]

SELECT *
FROM dept;
                        deptno 부서번호 자동 설정..
INSERT INTO dept VALUES ( 50,  OPERATIONS BOSTON );
INSERT INTO dept VALUES ( 60,  OPERATIONS BOSTON );
INSERT INTO dept VALUES ( 70,  OPERATIONS BOSTON );

ex) 은행 업무 처리
제일 먼저 해야 될 일 ? 순서 - 번호표 뽑아요.
시퀀스 == [은행 번호 장치] 청원경찰 10갯수
-> chache / 캐시기능 (성능 높일 수 있음)

【형식】
CREATE SEQUENCE 시퀀스명
[ INCREMENT BY 정수]      1
[ START WITH 정수]        1
[ MAXVALUE n ¦ NOMAXVALUE]  100
[ MINVALUE n ¦ NOMINVALUE]  5
[ CYCLE ¦ NOCYCLE]
[ CACHE n ¦ NOCACHE];

-- 시퀀스
CREATE SEQUENCE seq_dept
INCREMENT BY 10
START WITH 50
MAXVALUE 90
NOCYCLE
NOCACHE;
-- Sequence SEQ_DEPT이(가) 생성되었습니다.

INSERT INTO dept VALUES ( seq_dept.nextval ,  'QC',  'SEOUL' );

SELECT seq_dept.currval
From dual;

INSERT INTO dept VALUES ( seq_dept.nextval ,  'XX',  'SEOUL' );
COMMIT;

CREATE SEQUENCE
ALTER SEQUENCE
DROP  SEQUENCE seq_dept;

저작자표시 (새창열림)