오라클 정규화

데이터모델링 기초

1. 식별자 정의 (primary key)

주 식별자의 정의는 전체 데이터 모델의 복잡성을 정의하는 중요한 요소

해당 업무에서 자주 이용되는 속성을 주식별자로 지정한다.
속성값의 길이가 가변적인 속성은 주식별자로 적당하지 않다.
부서이름보다 부서코드를 주식별자로 지정한다.
주식별자에는 NULL 데이터를 허용하지 않는다.

2. 정규화

정규화란 데이터 모델을 보다 효율적으로 개선시켜 나가는 과정이며, 고유한 식별자(Primary Key)를 가지는

개체(테이블)에 대하여 더 이상 분리할 수 없는 상태로 나누는 과정이다.

정규화 처리를 통해

데이터의 양이 줄고
데이터 갱신이 빠르며
데이터 일관성 유지가 쉽다.
관계된 여러 속성을 동시에 조회하는 작업을 수행하기 위해 여러 테이블을 JOIN 하는 작업의 어려움이 있다.

일반적으로 정규화는 중복된 데이터를 삭제하는 것이 주 목적이며, 정규화는 1차~5차까지의 단계로 나누어지며,

실무에서는 보통 3차 정규화까지 사용된다.

2.1. 제 1 정규화

반복되는 속성들을 다른 테이블로 나누어 분리하는 작업

다른 개체로 판단할 수 있는 속성들을 분리하고 각 개체의 속성들의 유일한 식별자(Primary Key)를 가지게 된다.

부모테이블의 식별자(Primary Key)는 자식 테이블의 외부키(Foreign Key)와 연결(JOIN) 된다.

문제1) 고객과 계약을 처리하는 테이블을 생성하시오

조건]

1) 동일고객에 대해 1일 계약은 한번 이상 발생하지 않는다.

2) 고객정보

고객번호 "YYYYMMDDNNNN" 으로 "NNNN"은 1번 ~ 9999번까지

연속된 수로 부여, [주 식별자]

고객명 : 20자이내로 한정, aggNOT NULL

주소 : 100자이내로 한정한다.

3) 계약정보

계약일자 :"YYYYMMDD" 로 문자로 입력한다.

계약내용 : 100자이내로 한정한다.

정규화전 테이블 구조

[생성스크립트]

CREATE TABLE KEDU.CONTRACT_CSTM (

CSTMNO VARCHAR2(12 BYTE) NOT NULL,

CSTMNM VARCHAR2(20 BYTE) NULL,

CSTMADD VARCHAR2(100 BYTE) NULL,

AGGDT VARCHAR2(8 BYTE) NULL,

AGGCONT VARCHAR2(100 BYTE) NULL,

AGGDT1 VARCHAR2(8 BYTE) NULL,

AGGCONT1 VARCHAR2(100 BYTE) NULL,

AGGDT2 VARCHAR2(8 BYTE) NULL,

AGGCONT2 VARCHAR2(100 BYTE) NULL

);

ALTER TABLE KEDU.CONTRACT_CSTM ADD

(

CONSTRAINT PK_CONTRACT_CSTM

PRIMARY KEY ( CSTMNO )

);

[입력데이터]

INSERT INTO contract_cstm(cstmno, cstmnm,cstmadd,aggdt, aggcont) VALUES('201501010001','홍길동','서울 구로구','20150101','요술봉');

INSERT INTO contract_cstm(cstmno, cstmnm,cstmadd,aggdt,aggcont) VALUES('201501010002','홍길순','서울 구로구1','20150102','마술봉');

INSERT INTO contract_cstm(cstmno, cstmnm,cstmadd) VALUES('201501020003','고길동','서울 구로구');

INSERT INTO contract_cstm(cstmno, cstmnm,cstmadd) VALUES('201501030004','홍동','서울 구로구');

INSERT INTO contract_cstm(cstmno, cstmnm,cstmadd) VALUES('201501040005','길동','서울 구로구');

INSERT INTO contract_cstm(cstmno, cstmnm,cstmadd) VALUES('201501060007','고동','서울 구로구');

INSERT INTO contract_cstm(cstmno, cstmnm,cstmadd) VALUES('201501070008','고길','서울 구로구');

INSERT INTO contract_cstm(cstmno, cstmnm,cstmadd) VALUES('201501080009','길순','서울 구로구');

[결과]

정규화 처리후 테이블 구조

[생성스크립트]

CREATE TABLE KEDU.CSTM (

CSTMNO VARCHAR2(12 BYTE) NOT NULL,

CSTMNM VARCHAR2(20 BYTE) NULL,

CSTMADD VARCHAR2(100 BYTE) NULL

);

ALTER TABLE KEDU.CSTM ADD

(

CONSTRAINT PK_CSTM

PRIMARY KEY ( CSTMNO )

);

CREATE TABLE KEDU.CONTRACT (

CSTMNO VARCHAR2(12 BYTE) NULL,

AGGDT VARCHAR2(8 BYTE) NULL,

AGGCONT VARCHAR2(100 BYTE) NULL

);

ALTER TABLE KEDU.CONTRACT ADD

(

CONSTRAINT PK_CONTRACT

PRIMARY KEY ( CSTMNO, AGGDT )

);

ALTER TABLE KEDU.CONTRACT ADD

(

CONSTRAINT FK_CONTRACT

FOREIGN KEY ( CSTMNO )

REFERENCES KEDU.CSTM ( CSTMNO )

);

[입력 데이터]

고객정보 입력

INSERT INTO cstm(cstmno, cstmnm,cstmadd) VALUES('201501010001','홍길동','서울 구로구');

INSERT INTO cstm(cstmno, cstmnm,cstmadd) VALUES('201501010002','홍길순','서울 구로구1');

INSERT INTO cstm(cstmno, cstmnm,cstmadd) VALUES('201501020003','고길동','서울 구로구');

INSERT INTO cstm(cstmno, cstmnm,cstmadd) VALUES('201501030004','홍동','서울 구로구');

INSERT INTO cstm(cstmno, cstmnm,cstmadd) VALUES('201501040005','길동','서울 구로구');

INSERT INTO cstm(cstmno, cstmnm,cstmadd) VALUES('201501060007','고동','서울 구로구');

INSERT INTO cstm(cstmno, cstmnm,cstmadd) VALUES('201501070008','고길','서울 구로구');

INSERT INTO cstm(cstmno, cstmnm,cstmadd) VALUES('201501080009','길순','서울 구로구');

[결과]

[계약정보]

INSERT INTO contract(cstmno,aggdt, aggcont) VALUES('201501010001','20150101','요술봉');

INSERT INTO contract(cstmno,aggdt,aggcont) VALUES('201501010002','20150102','마술봉');

[결과]

데이터 결과 확인

중요-연구해보기

"동일고객에 대해 1일 계약은 한번 이상 발생하지 않는다" 조건이 변경되어 동일고객이 하루에도 여러번 계약 처리할 수 있도록 되었음, 이에 따라 테이블 구조 변경이 필요함

답) 순번 컬럼을 생성하여 PK로 하고 계약일자를 일반 컬럼으로 내려야 함

제 2 정규화

모든 속성(컬럼)은 식별자에 직접적으로 의존적이어야 하며, 이에 해당되지 않는 속성을 분리한다.

(PRIMARY KEY)

모든 속성(컬럼)은 반드시 UID(UNIQUE IDENTIFIER) 전부에 종속되어야 한다.

즉, 일부만 종속되면 안된다.

정규화가 이루어지고 나면 부모 엔티티로 분리된다. (부모테이블이 만들어 진다.)

문제2) 학번, 과목코드, 과목명, 수강시작일자, 수강종료일자, 등록일자, 평가코드, 평가내역을 관리하는 테이블을 생성하시오

[정규화전 테이블 구조]

CREATE TABLE KEDU.학과등록 (

학번 VARCHAR2(12 BYTE) NOT NULL,

과목코드 VARCHAR2(6 BYTE) NOT NULL,

과목명 VARCHAR2(10 BYTE) NULL,

수강시작일자 VARCHAR2(8 BYTE) NULL,

수강종료일자 VARCHAR2(8 BYTE) NULL,

등록일자 VARCHAR2(8 BYTE) NULL,

평가코드 VARCHAR2(10 BYTE) NULL,

평가내역 VARCHAR2(1000 BYTE) NULL

);

COMMENT ON TABLE KEDU.학과등록 IS '학과등록테이블';

COMMENT ON COLUMN KEDU.학과등록.학번 IS '학번';

COMMENT ON COLUMN KEDU.학과등록.과목코드 IS '과목코드';

COMMENT ON COLUMN KEDU.학과등록.과목명 IS '과목명';

COMMENT ON COLUMN KEDU.학과등록.수강시작일자 IS '수강시작일자';

COMMENT ON COLUMN KEDU.학과등록.수강종료일자 IS '수강종료일자';

COMMENT ON COLUMN KEDU.학과등록.등록일자 IS '등록일자';

COMMENT ON COLUMN KEDU.학과등록.평가코드 IS '평가코드';

COMMENT ON COLUMN KEDU.학과등록.평가내역 IS '평가내역';

ALTER TABLE KEDU.학과등록 ADD

(

CONSTRAINT PK_학과등록

PRIMARY KEY ( 학번, 과목코드 )

);

[입력 데이터]

INSERT INTO 학과등록 VALUES ('20141201','101101','전산','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141202','101101','전산','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141203','101101','전산','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141204','101101','전산','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141205','101101','전산','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141206','101101','전산','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141207','101101','전산','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141208','101102','영어','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141209','101102','영어','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141210','101102','영어','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141211','101103','수학','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141212','101103','수학','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141213','101103','수학','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141214','101103','수학','20150301','20150531','20150102','','');

INSERT INTO 학과등록 VALUES ('20141215','101103','수학','20150301','20150531','20150102','','');

[결과]

[정규화후 테이블 구조]

[생성스크립트]

DROP TABLE KEDU.등록 CASCADE CONSTRAINTS;

CREATE TABLE KEDU.등록 (
학번 VARCHAR2(12 BYTE) NOT NULL,
과목코드 VARCHAR2(6 BYTE) NOT NULL,
등록일자 VARCHAR2(8 BYTE) NULL,
평가코드 VARCHAR2(10 BYTE) NULL,
평가내역 VARCHAR2(1000 BYTE) NULL
);

COMMENT ON TABLE KEDU.등록 IS '과정등록';

COMMENT ON COLUMN KEDU.등록.학번 IS '학번';

COMMENT ON COLUMN KEDU.등록.과목코드 IS '과목코드';

COMMENT ON COLUMN KEDU.등록.등록일자 IS '등록일자';

COMMENT ON COLUMN KEDU.등록.평가코드 IS '평가코드';

COMMENT ON COLUMN KEDU.등록.평가내역 IS '평가내역';

ALTER TABLE KEDU.등록 ADD
(
CONSTRAINT PK_등록
PRIMARY KEY ( 학번, 과목코드 )
);

ALTER TABLE KEDU.등록 ADD
(
CONSTRAINT FK_등록
FOREIGN KEY ( 과목코드 )
REFERENCES KEDU.과목 ( 과목코드 )
);

DROP TABLE KEDU.과목 CASCADE CONSTRAINTS;

CREATE TABLE KEDU.과목 (

과목코드 VARCHAR2(6 BYTE) NOT NULL,

과목명 VARCHAR2(10 BYTE) NULL,

수강시작일자 VARCHAR2(8 BYTE) NULL,

수강종료일자 VARCHAR2(8 BYTE) NULL

);

COMMENT ON TABLE KEDU.과목 IS '과목데이터';

COMMENT ON COLUMN KEDU.과목.과목코드 IS '주키';

COMMENT ON COLUMN KEDU.과목.과목명 IS '과목명';

COMMENT ON COLUMN KEDU.과목.수강시작일자 IS '수강시작일자 YYYYMMDD 입력';

COMMENT ON COLUMN KEDU.과목.수강종료일자 IS '수강종료일자 YYYYMMDD 입력';

ALTER TABLE KEDU.과목 ADD

(

CONSTRAINT PK_과목

PRIMARY KEY ( 과목코드 )

);

[입력데이터]

INSERT INTO 등록 VALUES ('20141201','101101','20150102','','');

INSERT INTO 등록 VALUES ('20141202','101101','20150102','','');

INSERT INTO 등록 VALUES ('20141203','101101','20150102','','');

INSERT INTO 등록 VALUES ('20141204','101101','20150102','','');

INSERT INTO 등록 VALUES ('20141205','101101','20150102','','');

INSERT INTO 등록 VALUES ('20141206','101102','20150102','','');

INSERT INTO 등록 VALUES ('20141207','101102','20150102','','');

INSERT INTO 등록 VALUES ('20141208','101102','20150102','','');

INSERT INTO 등록 VALUES ('20141209','101102','20150102','','');

INSERT INTO 등록 VALUES ('20141210','101102','20150102','','');

INSERT INTO 등록 VALUES ('20141211','101103','20150102','','');

INSERT INTO 등록 VALUES ('20141212','101103','20150102','','');

INSERT INTO 등록 VALUES ('20141213','101103','20150102','','');

INSERT INTO 등록 VALUES ('20141214','101103','20150102','','');

INSERT INTO 과목 VALUES ('101101','전산','20150301','20150531');

INSERT INTO 과목 VALUES ('101102','영어','20150301','20150531');

INSERT INTO 과목 VALUES ('101103','수학','20150301','20150531');

-> 에러 주의

[결과]

결과비교

[정리]

위 출력결과를 보면, 과목명, 수강일자시작, 수강종료일자가 중복되어 등록되어 있음을 확인 할 수 있다

과목명을 보면, 과목코드에 종속은 되나, 학번에는 종속이 되지 않는다는 것을 확인할 수 있다.

이런 속성(과목명, 수강기간)들은 과목코드를 UID로 하는 부모 엔티티로 분리하여 새로 만들어 준다.

제2정규화를 진행하고 나면, 중보되어 등록되는 데이터 항목들이 따로 분리되어 만들어지며, 중복되지 않는 데이터를 구성해서 데이터 관리의 효율성을 높일 수 있다.

제 3 정규화

식별자 이외의 속성은 식별자가 아닌 다른 속성에 종속적이지 않아야 한다.

UID가 아닌 모든 속성간에는 서로 종속될 수 없다.

위 테이블에서 평가코드와 평가내역을 보면 평가내역이 평가코드에 종속적이다. 이런 경우 평가코드를 UID로 하고 별도의 테이블로 분리하여 만든다.

[테이블 생성]

DROP TABLE KEDU.학과등록 CASCADE CONSTRAINTS;

CREATE TABLE KEDU.학과등록 (

학번 VARCHAR2(12 BYTE) NOT NULL,

과목코드 VARCHAR2(6 BYTE) NOT NULL,

평가코드 CHAR(1 BYTE) NULL,

등록일자 VARCHAR2(8 BYTE) NULL

);

COMMENT ON TABLE KEDU.학과등록 IS '학과등록';

COMMENT ON COLUMN KEDU.학과등록.학번 IS '학번';

COMMENT ON COLUMN KEDU.학과등록.과목코드 IS '과목코드 FK -> 과목테이블 과목코드 참조';

COMMENT ON COLUMN KEDU.학과등록.평가코드 IS '평가코드 A, B, C';

COMMENT ON COLUMN KEDU.학과등록.등록일자 IS '등록일자 8자리 입력';

ALTER TABLE KEDU.학과등록 ADD

(

CONSTRAINT PK_학과등록

PRIMARY KEY ( 학번, 과목코드 )

);

ALTER TABLE KEDU.학과등록 ADD

(

CONSTRAINT FK_학과등록

FOREIGN KEY ( 평가코드 )

REFERENCES KEDU.평가 ( 평가코드 )

);

DROP TABLE KEDU.평가 CASCADE CONSTRAINTS;

CREATE TABLE KEDU.평가 (

평가코드 CHAR(1 BYTE) NOT NULL,

평가내역 VARCHAR2(200 BYTE) NULL

);

COMMENT ON TABLE KEDU.평가 IS '평가코드 관리 테이블';

COMMENT ON COLUMN KEDU.평가.평가코드 IS '평가코드 A,B,C';

COMMENT ON COLUMN KEDU.평가.평가내역 IS '평가내';

ALTER TABLE KEDU.평가 ADD

(

CONSTRAINT PK_평가

PRIMARY KEY ( 평가코드 )

);

[입력 데이터]

INSERT INTO 학과등록 VALUES ('20141201','101101','A','20150102');

INSERT INTO 학과등록 VALUES ('20141202','101101','A','20150102');

INSERT INTO 학과등록 VALUES ('20141203','101101','A','20150102');

INSERT INTO 학과등록 VALUES ('20141204','101101','A','20150102');

INSERT INTO 학과등록 VALUES ('20141205','101101','B','20150102');

INSERT INTO 학과등록 VALUES ('20141206','101101','B','20150102');

INSERT INTO 학과등록 VALUES ('20141207','101101','B','20150102');

INSERT INTO 학과등록 VALUES ('20141208','101101','B','20150102');

INSERT INTO 학과등록 VALUES ('20141209','101101','B','20150102');

INSERT INTO 학과등록 VALUES ('20141210','101101','C','20150102');

INSERT INTO 학과등록 VALUES ('20141211','101101','C','20150102');

INSERT INTO 학과등록 VALUES ('20141212','101101','C','20150102');

INSERT INTO 학과등록 VALUES ('20141213','101101','C','20150102');

INSERT INTO 평가 VALUES('A','A 평가내역입니다.');

INSERT INTO 평가 VALUES('B','B 평가내역입니다.');

INSERT INTO 평가 VALUES('C','C 평가내역입니다.');

SELECT * FROM 학과등록;

SELECT * FROM 평가;

CREATE TABLE KEDU.CONTRACT_CSTM(

cstmNo VARCHAR2( 12) NOT NULL

, cstmNm VARCHAR2( 20)

, cstmAdd VARCHAR2(100)

, aggDt VARCHAR2( 8)

, aggCont VARCHAR2(100)

, aggDt1 VARCHAR2( 8)

, aggCont1 VARCHAR2(100)

, aggDt2 VARCHAR2( 8)

, aggCont3 VARCHAR2(100)

);

/*----------------------------------------------

* CSTMNO(고객번호) -> PRIMARY KEY 생성

*----------------------------------------------*/

ALTER TABLE kedu.CONTRACT_CSTM

ADD (

CONSTRAINT PK_CONTRACT_CSTM

PRIMARY KEY(CSTMNO)

);

CREATE TABLE KEDU.CONTRACT_CSTM

(

cstmNo VARCHAR2( 12) NOT NULL

, cstmNm VARCHAR2( 20)

, cstmAdd VARCHAR2(100)

, aggDt VARCHAR2( 8)

, aggCont VARCHAR2(100)

, aggDt1 VARCHAR2( 8)

, aggCont1 VARCHAR2(100)

, aggDt2 VARCHAR2( 8)

, aggCont3 VARCHAR2(100)

);

ALTER TABLE kedu.CONTRACT_CSTM

ADD (

CONSTRAINT PK_CONTRACT_CSTM

PRIMARY KEY(CSTMNO)

);

INSERT INTO contract_cstm(cstmno, cstmnm,cstmadd,aggdt, aggcont)

VALUES('201501010001','홍길동','서울 구로구','20150101','요술봉');

INSERT INTO contract_cstm(cstmno, cstmnm,cstmadd,aggdt,aggcont)

VALUES('201501010002','홍길순','서울 구로구1','20150102','마술봉');