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

[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;

VS

 

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;