티스토리 뷰
반응형
강사 : 김호진 강사님
Oracle SQL Developer (5일차)
20180111_hr.sql
현재 접속한 자신의 계정 확인
--○ 현재 접속한 자신의 계정 확인
SELECT USER
FROM DUAL;
--==>> HR
세 개 이상의 테이블 조인(JOIN)
--○ 세 개 이상의 테이블 조인(JOIN)
-- 형식1. SQL 1992 CODE
SELECT 테이블명1.컬럼명, 테이블명2.컬럼명, 테이블명3.컬럼명
FROM 테이블명1, 테이블명2, 테이블명3
WHERE 테이블명1.컬럼명1 = 테이블명2.컬럼명1
AND 테이블명2.컬럼명2 = 테이블명3.컬럼명2;
-- 형식2. SQL 1999 CODE
SELECT 테이블명1.컬럼명, 테이블명2.컬럼명, 테이블명3.컬럼명
FROM 테이블명1 JOIN 테이블명2
ON 테이블명1.컬럼명1 = 테이블명2.컬럼명1
JOIN 테이블명3
ON 테이블명2.컬럼명2 = 테이블명3.컬럼명2;
HR 계정 소유의 테이블 조회
--○ HR 계정 소유의 테이블 조회
SELECT *
FROM TAB;
--==>>
/*
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
*/
SELECT *
FROM COUNTRIES;
Test30
--○ HR.JOBS, HR.EMPLOYEES, HR.DEPRATMENTS 테이블을 대상으로
-- 직원들의 정보를 조회한다.
-- 단, 조회 항목은 FIRST_NAME, LAST_NAME, JOB_TITLE, DEPARTMENT_NAME
SELECT *
FROM JOBS;
SELECT *
FROM EMPLOYEES;
SELECT *
FROM DEPARTMENTS;
-- 형식1. SQL 1992 CODE
SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.JOB_ID = J.JOB_ID;
-- 형식2. SQL 1999 CODE
SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN JOBS J
ON E.JOB_ID = J.JOB_ID;
Test31
--○ 과제
-- EMPLOYEES, DEPARTMENTS, JOBS, LOCATIONS, COUNTRIES, REGIONS
-- 이 6개의 테이블을 대상으로 직원들의 정보를 다음과 같이 조회한다.
-- FIRST_NAME, LAST_NAME, JOB_TITLE, DEPARTMENT_NAME, CITY, COUNTRY_NAME, REGION_NAME
SELECT *
FROM EMPLOYEES;
SELECT *
FROM DEPARTMENTS;
SELECT *
FROM JOBS;
SELECT *
FROM LOCATIONS;
SELECT *
FROM COUNTRIES;
SELECT *
FROM REGIONS;
-- 형식1. SQL 1992 CODE
SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME
, L.CITY, C.COUNTRY_NAME, R.REGION_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J, LOCATIONS L, COUNTRIES C, REGIONS R
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.JOB_ID = J.JOB_ID
AND L.LOCATION_ID = D.LOCATION_ID
AND C.COUNTRY_ID = L.COUNTRY_ID
AND R.REGION_ID = C.REGION_ID;
-- 형식2. SQL 1999 CODE
SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME
, L.CITY, C.COUNTRY_NAME, R.REGION_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
JOIN LOCATIONS L
ON L.LOCATION_ID = D.LOCATION_ID
JOIN COUNTRIES C
ON C.COUNTRY_ID = L.COUNTRY_ID
JOIN REGIONS R
ON R.REGION_ID = C.REGION_ID;
※ 개별학습 체크
- UNION / UNION ALL
- INTERSECT / MINUS
20180111_scott.sql
SELF JOIN (자기 조인)
--○ EMP 테이블의 정보를 다음과 같이 조회할 수 있도록 한다.
/*
사원번호 사원명 직종명 관리자번호 관리자명 관리자직종명
7369 SMITH CLERK 7902 FORD ANALYST
:
EMPNO ENAME JOB MGR
|---------------------------------------------|
|
EMPNO ENAME JOB
|--------------------------------------------|
*/
SELECT *
FROM EMP;
SELECT E1.EMPNO"사원번호", E1.ENAME"사원명", E1.JOB"직종명", E1.MGR"관리자번호"
, E2.ENAME"관리자명", E2.JOB"관리자직종명"
FROM EMP E1 LEFT JOIN EMP E2
ON E1.MGR = E2.EMPNO;
자연 조인(NATURAL JOIN)
--○ 자연 조인(NATURAL JOIN)
SELECT DEPTNO, DNAME, ENAME, SAL
FROM EMP NATURAL JOIN DEPT;
--> 크게 바람직하지는 않은 방법 (오라클에서만 존재)
-- 간편하게 확인하기 위한 조인 방법으로 사용
※ 참고
1) 관계(relationship)
- 모든 엔트리(entry)는 단일값을 가진다.
- 각 열(column)은 유일한 이름을 가지며 순서는 무의미하다.
- 테이블의 모든 행(row = 튜플 = tuple = 레코드 = record) 은 동일하지 않으며 순서는 무의미하다.
2) 속성(attribute)
- 테이블의 열(column)을 나타낸다.
- 자료의 이름을 가진 최소 논리적 단위이다. → 객체의 성질, 상태 기술
- 일반 파일(file)의 항목(item, field)에 해당한다.
- 엔티티(entity)의 특성과 상태를 기술한다.
- 속성의 이름은 모두 달라야 한다.
3) 튜플(tuple) = 엔티티(entity)
- 테이블의 행(row)
- 연관된 몇 개의 속성으로 구성
- 개념 정보 단위
- 일반 파일(file)의 레코드에 해당한다.
- 튜플 변수(tuple variable) : 튜플(tuple)을 가리키는 변수, 모든 튜플의 집합을 도메인으로 하는 변수
4) 도메인(domain)
- 각 속성이 가질 수 있도록 허용된 값들의 집합
- 속성 명과 도메인 명이 반드시 동일할 필요는 없음
- 모든 릴레이션에서 모든 속성들의 도메인은 원자적(atomic)이어야 함.
- 원자적 도메인(atomin domain) : 도메인의 원소가 더이상 나누어질 수 없는 단일체일 때를 나타냄.
5) 릴레이션(relation)
- 파일 시스템에서 파일과 같은 개념
- 중복된 튜플을 포함하지 않는다. (모두 상이함 : 튜플의 유일성)
- 릴레이션 = 튜플(엔티티:entity)의 집합. 따라서 튜플의 순서는 무의미하다.
- 속성(attribute) 간에는 순서가 없다.
--※ 참고
/*
1) 관계(relationship)
- 모든 엔트리(entry)는 단일값을 가진다.
- 각 열(column)은 유일한 이름을 가지며 순서는 무의미하다.
- 테이블의 모든 행(row = 튜플 = tuple = 레코드 = record) 은 동일하지 않으며 순서는 무의미하다.
2) 속성(attribute)
- 테이블의 열(column)을 나타낸다.
- 자료의 이름을 가진 최소 논리적 단위이다. → 객체의 성질, 상태 기술
- 일반 파일(file)의 항목(item, field)에 해당한다.
- 엔티티(entity)의 특성과 상태를 기술한다.
- 속성의 이름은 모두 달라야 한다.
3) 튜플(tuple) = 엔티티(entity)
- 테이블의 행(row)
- 연관된 몇 개의 속성으로 구성
- 개념 정보 단위
- 일반 파일(file)의 레코드에 해당한다.
- 튜플 변수(tuple variable)
: 튜플(tuple)을 가리키는 변수, 모든 튜플의 집합을 도메인으로 하는 변수
4) 도메인(domain)
- 각 속성이 가질 수 있도록 허용된 값들의 집합
- 속성 명과 도메인 명이 반드시 동일할 필요는 없음
- 모든 릴레이션에서 모든 속성들의 도메인은 원자적(atomic)이어야 함.
- 원자적 도메인(atomin domain)
: 도메인의 원소가 더이상 나누어질 수 없는 단일체일 때를 나타냄.
5) 릴레이션(relation)
- 파일 시스템에서 파일과 같은 개념
- 중복된 튜플을 포함하지 않는다. (모두 상이함 : 튜플의 유일성)
- 릴레이션 = 튜플(엔티티:entity)의 집합. 따라서 튜플의 순서는 무의미하다.
- 속성(attribute) 간에는 순서가 없다.
*/
무결성 (Integrity)
- 무결성에는 개체 무결성(Entity Integrity), 참조 무결성(Relational Integrity), 도메인 무결성(Domain Integrity) 이 있다.
- 개체 무결성 : 개체 무결성은 릴레이션에서 저장되는 튜플(tuple)의 유일성을 보장하기 위한 제약조건이다.
- 참조 무결성 : 참조 무결성은 릴레이션 간의 데이터 일관성을 보장하기 위한 제약조건이다.
- 도메인 무결성 : 도메인 무결성은 허용 가능한 값의 범위를 지정하기 위한 제약조건이다.
- 제약조건의 종류
- primary key(pk:p) : 해당 컬럼의 값은 반드시 존재해야 하며, 유일해야 한다. (not null 과 unique 가 결합된 형태)
- foreign key(fk:f:r) : 해당 컬럼의 값은 참조되는 테이블의 컬럼 데이터 중 하나와 일치하거나 null 을 가진다.
- unique(uk:u) : 테이블 내에서 해당 컬럼의 값은 항상 유일해야 한다.
- not null(nn:ck:c) : 해당 컬럼은 null 을 포함할 수 없다.
- check(ck:c) : 해당 컬럼에 저장 가능한 데이터의 조건이나 값의 범위를 지정한다.
/*
--■■■ 무결성 (Integrity) ■■■--
1. 무결성에는 개체 무결성(Entity Integrity)
참조 무결성(Relational Integrity)
도메인 무결성(Domain Integrity) 이 있다.
2. 개체 무결성
개체 무결성은 릴레이션에서 저장되는 튜플(tuple)의
유일성을 보장하기 위한 제약조건이다.
3. 참조 무결성
참조 무결성은 릴레이션 간의 데이터 일관성을
보장하기 위한 제약조건이다.
4. 도메인 무결성
도메인 무결성은 허용 가능한 값의 범위를
지정하기 위한 제약조건이다.
5. 제약조건의 종류
1) primary key(pk:p)
해당 컬럼의 값은 반드시 존재해야 하며, 유일해야 한다.
(not null 과 unique 가 결합된 형태)
2) foreign key(fk:f:r)
해당 컬럼의 값은 참조되는 테이블의 컬럼 데이터 중 하나와
일치하거나 null 을 가진다.
3) unique(uk:u)
테이블 내에서 해당 컬럼의 값은 항상 유일해야 한다.
4) not null(nn:ck:c)
해당 컬럼은 null 을 포함할 수 없다.
5) check(ck:c)
해당 컬럼에 저장 가능한 데이터의 조건이나 값의 범위를 지정한다.
*/
PRIMARY KEY
--■■■ PRIMARY KEY ■■■--
-- 1. 해당 테이블에 대한 기본 키를 생성한다.
-- 2. 테이블에서 각 행을 유일하게 식별하는 컬럼 또는 컬럼의 집합이다.
-- 기본키는 테이블 당 하나만 존재한다.
-- 그러나 반드시 하나의 컬럼으로만 구성되는 것은 아니다.
-- NULL일 수 없고, 이미 테이블에 존재하고 있는 데이터를 다시 입력할 수도 없다.
-- 내부적으로 UNIQUE INDEX가 자동으로 생성된다.
-- 3. 형식 및 구조
--① 컬럼 레벨의 형식
-- 컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] PRIMARY KEY[(컬럼명, ...)]
--② 테이블 레벨의 형식
-- , 컬럼명 데이터 타입
-- , 컬럼명 데이터 타입
-- , CONSTRAINT CONSTRAINT명 PRIMARY KEY(컬럼명, ...)
-- 4. CONSTRAINT 추가 시 CONSTRAINT명을 생략하면
-- 오라클 서버가 자동적으로 CONSTRAINT명을 부여한다.
-- 일반적으로 CONSTRAINT명은 『테이블명_컬럼명_CONSTRAINT약자』 형식으로 기술한다.
-- (구성 순서는 업무나 상황에 따라 다르다.)
PK 지정실습(① 컬럼 레벨의 형식)
--○ PK 지정실습(① 컬럼 레벨의 형식)
CREATE TABLE TBL_TEST1
( COL1 NUMBER(5) PRIMARY KEY
, COL2 VARCHAR2(30)
);
--==>> Table TBL_TEST1이(가) 생성되었습니다.
-- 데이터 입력
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(1, 'TEST');
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(2, 'ABCD');
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(3, NULL);
INSERT INTO TBL_TEST1(COL1) VALUES(4);
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(2, 'ABCD'); -- X
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(5, 'ABCD'); -- O
INSERT INTO TBL_TEST1(COL2) VALUES('STUDY'); -- X
COMMIT;
SELECT *
FROM TBL_TEST1;
-- 제약조건 확인
DESC TBL_TEST1;
SELECT *
FROM USER_CONSTRAINTS;
--==>>
/*
SCOTT FK_DEPTNO R EMP SCOTT PK_DEPT NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 18/01/05
SCOTT PK_DEPT P DEPT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 18/01/05 SCOTT PK_DEPT
SCOTT PK_EMP P EMP ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 18/01/05 SCOTT PK_EMP
SCOTT SYS_C007000 P TBL_SAWON ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 18/01/09 SCOTT SYS_C007000
SCOTT SYS_C007001 P TBL_TEST1 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 18/01/11 SCOTT SYS_C007001
*/
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='TBL_TEST1';
--==>> SCOTT SYS_C007001 P TBL_TEST1 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 18/01/11 SCOTT SYS_C007001
제약조건이 지정된 컬럼 확인
--○ 제약조건이 지정된 컬럼 확인
SELECT *
FROM USER_CONS_COLUMNS;
SELECT *
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME='TBL_TEST1';
Test32
--○ 제약조건이 설정된 소유주, 제약조건명, 테이블, 제약조건 종류, 컬럼정보 확인
-- OWNER CONSTRAINT_NAME TABLE_NAME CONSTRAINT_TYPE COLUMN_NAME
SELECT UC.OWNER, UC.CONSTRAINT_NAME, UC.TABLE_NAME, UC.CONSTRAINT_TYPE, UCC.COLUMN_NAME
FROM USER_CONSTRAINTS UC JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.TABLE_NAME = 'TBL_TEST1';
--==>> SCOTT SYS_C007001 TBL_TEST1 P COL1
PK 지정 실습(② 테이블 레벨의 형식)
--○ PK 지정 실습(② 테이블 레벨의 형식)
CREATE TABLE TBL_TEST2
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINT TEST2_COL1_PK PRIMARY KEY(COL1)
);
--==>> Table TBL_TEST2이(가) 생성되었습니다.
-- 데이터 입력
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(1, 'TEST');
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(2, 'ABCD');
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(3, NULL);
INSERT INTO TBL_TEST2(COL1) VALUES(4);
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(2, 'ABCD'); -- X
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(5, 'ABCD'); -- O
INSERT INTO TBL_TEST2(COL2) VALUES('STUDY'); -- X
COMMIT;
SELECT *
FROM TBL_TEST2;
-- 제약조건 확인
SELECT UC.OWNER, UC.CONSTRAINT_NAME, UC.TABLE_NAME, UC.CONSTRAINT_TYPE, UCC.COLUMN_NAME
FROM USER_CONSTRAINTS UC JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.TABLE_NAME = 'TBL_TEST2';
--==>> SCOTT TEST2_COL1_PK TBL_TEST2 P COL1
PK 지정 실습(③ 다중 컬럼 PK 지정)
--○ PK 지정 실습(③ 다중 컬럼 PK 지정) → 복합 프라이머리 키 지정
CREATE TABLE TBL_TEST3
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINT TEST3_COL1_COL2_PK PRIMARY KEY(COL1, COL2)
);
--==>> Table TBL_TEST3이(가) 생성되었습니다.
-- 데이터 입력
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(1, 'TEST');
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(2, 'TEST');
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(1, 'ABCD');
COMMIT;
-- 제약조건 확인
SELECT UC.OWNER, UC.CONSTRAINT_NAME, UC.TABLE_NAME, UC.CONSTRAINT_TYPE, UCC.COLUMN_NAME
FROM USER_CONSTRAINTS UC JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.TABLE_NAME = 'TBL_TEST3';
--==>>
/*
SCOTT TEST3_COL1_COL2_PK TBL_TEST3 P COL1
SCOTT TEST3_COL1_COL2_PK TBL_TEST3 P COL2
*/
PK 지정 실습(④ 테이블 생성 이후 제약조건 추가)
--○ PK 지정 실습(④ 테이블 생성 이후 제약조건 추가)
-- 테이블 생성
CREATE TABLE TBL_TEST4
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
);
--==>> Table TBL_TEST4이(가) 생성되었습니다.
-- 제약조건 추가
ALTER TABLE TBL_TEST4
ADD CONSTRAINT TEST4_COL1_PK PRIMARY KEY(COL1);
--==>> Table TBL_TEST4이(가) 변경되었습니다.
-- 제약조건 확인
SELECT UC.OWNER, UC.CONSTRAINT_NAME, UC.TABLE_NAME, UC.CONSTRAINT_TYPE, UCC.COLUMN_NAME
FROM USER_CONSTRAINTS UC JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.TABLE_NAME = 'TBL_TEST4';
--==>> SCOTT TEST4_COL1_PK TBL_TEST4 P COL1
제약조건 확인용 뷰 생성
--○ 제약조건 확인용 뷰 생성
CREATE OR REPLACE VIEW VIEW_CONSTCHECK
AS
SELECT UC.OWNER "OWNER"
, UC.CONSTRAINT_NAME "CONSTRAINT_NAME"
, UC.TABLE_NAME "TABLE_NAME"
, UC.CONSTRAINT_TYPE "CONSTRAINT_TYPE"
, UCC.COLUMN_NAME "COLUMN_NAME"
, UC.SEARCH_CONDITION "SEARCH_CONDITION"
, UC.DELETE_RULE "DELETE_RULE"
FROM USER_CONSTRAINTS UC JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME;
--==>> View VIEW_CONSTCHECK이(가) 생성되었습니다.
--○ 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST4';
--==>> SCOTT TEST4_COL1_PK TBL_TEST4 P COL1
※ 개별학습 체크
나머지 제약조건들에 대한 실습 및 확인
정규화, 정규형 (Normalization)
정규화란?
--○ 정규화란?
--> 한 마디로 DB 서버의 메모리를 낭비하지 않도록 하기 위하여
-- 어떤 테이블을... 식별자를 가지는 여러 개의 테이블로
-- 나누는 과정을 말한다.
-- EX) 개발자로써의 진로를 포기한 김선규 사원이...
-- 정승우 사원의 유혹에 넘어가 옥장판 영업사원으로 취업을 하게 되었다.
-- 이 때 『거래처직원』 정보를 데이터베이스화 하려고 한다.
-- 테이블명 : 거래처직원
/*
10byte 10byte 10byte 10byte 10byte 10byte 10byte
--------------------------------------------------------------------------------
거래처회사명 회사주소 회사전화 거래처직원명 직급 이메일 휴대폰
--------------------------------------------------------------------------------
농심 여의대방로 02-345-6789 명소희 대리 so@nav... 010-1111-...
농심 여의대방로 02-345-6789 임미영 대리 mi@nav... 010-2222-...
농심 여의대방로 02-345-6789 조태희 부장 te@dau... 010-3333-...
삼양 서울소공동 02-555-8888 박기범 주임 ki@nav... 010-5555-...
농심 강원원주로 033-99-9999 서운성 차장 un@gma... 010-4444-...
:
100만 이상
(농심 본사만 100만 명)
가정) 여의대방로 농심 이라는 회사에 근무하는 거래처 직원 명단이
본사 직원만 해도 100 만 명이라고 가정한다. (한 행은 10byte 이다.)
어느 날... 여의대방로에 위치한 농심 본사가
경기 분당으로 사옥을 이전하였다.
-- UPDATE 를 수행해야 할 상황이 발생하게 되었다. (이 때 구성해야 하는 쿼리문)
UPDATE 거래처직원
SET 회사주소='경기분당', 회사전화='031-345-6789'
WHERE 거래처회사명='농심'
AND 회사주소='여의대방로';
-- 그러면... 100만 명의 회사 주소와 회사 전화 정보를 변경해야 하며
100만 개 행을 하드디스크상에서 읽어다가 메모리에 로드시켜 주어야 한다.
즉, 100만 * 70BYTE 를 모두 하드디스크상에서 읽어다가 메모리에 로드시켜 주어야 한다는 말이다.
-- 이는, 테이블의 설계가 잘못되었으므로
DB 서버는 조만간 메모리 고갈로 인해 DOWN 될 것이다.
--==>> 그러므로 정규화 작업에 들어간다.
제 1 정규화
/*
--○ 제 1 정규화
--> 어떤 테이블에 반복되는 컬럼값들이 존재하면
-- 값들이 반복되어 나오는 컬럼을 분리하여
-- 새로운 테이블을 만들어준다.
-- 부모테이블과 자식테이블로 나뉘게 된다.
-- 부모테이블의 PRIMARY KEY는 자식테이블의 FORIGN KEY이다.
-- 테이블명 : 회사
---------------------------------------------
10byte 10byte 10byte 10byte
---------------------------------------------
회사ID 거래처회사명 회사주소 회사전화
---------------------------------------------
10 농심 여의대방로 02-345-6789
20 삼양 서울소공동 02-555-8888
30 농심 강원원주로 033-99-9999
-- 테이블명 : 직원
10byte 10byte 10byte 10byte 10byte
---------------------------------------------------
거래처직원명 직급 이메일 휴대폰 회사ID
---------------------------------------------------
명소희 대리 so@nav... 010-1111-.. 10
임미영 대리 mi@nav... 010-2222-.. 10
조태희 부장 te@dau... 010-3333-.. 10
박기범 주임 ki@nav... 010-4444-.. 20
서운성 차장 un@gma... 010-5555-.. 30
--※ 1 정규화 특징
--> 어떤 테이블에 반복되는 컬럼값들이 존재하면
-- 값들이 반복되어 나오는 컬럼을 분리하여
-- 새로운 테이블을 만들어준다.
-- 거의, 반드시 부모와 자식 테이블로 나뉜다.
-- 부모: 참조받는 식별자 / 자식: 참조하는 식별자
-- 부모 테이블의 PRIMARY KEY 는 자식 테이블의 FOREIGN KEY 이다.
*/
제 2 정규화
/*
--○ 제 2 정규화
--> 식별자가 아닌 컬럼은 식별자 전체 컬럼에 대해 의존적이어야 하는데
-- 식별자 전체 컬럼에 대해 의존적이지 않고 식별자 일부 컬럼에만 의존적이라면
-- 이를 분리하여 새로운 테이블을 생성한다.
--※ 수행에 대한 전제 조건
-- 대상 테이블이 단일 프라이머리키로 구성되어 있을 경우 2 정규화는 수행하지 않는다.
-- 단, 복합 프라이머리키로 구성되어 있을 경우 반드시 2 정규화를 수행해야 한다.
-- 테이블명 : 주문
-------------------------------------------------------------
고객ID 제품코드 주문일자 주문수량
+++++++++++++++++++++++++++++++++++
( PRIMARY KEY )
-------------------------------------------------------------
-- 어떤 테이블에 PRIMARY KEY 제약조건은 최대 1개 까지 설정할 수 있다.
-- 단, 여러 컬럼을 묶어서 설정할 수 있다. → 복합 프라이머리 키
--------------------------------------------------------------------------------
테이블명 : 과목 → 부모 테이블
--------------------------------------------------------------------------------
과목번호 과목명 교수자번호 교수자명 강의실코드 강의실설명
++++++++ ++++++++++
P . K
--------------------------------------------------------------------------------
DB0101 오라클기초 21 에디슨 G309 전산실습관 3층 50석
DB0101 오라클기초 22 장영실 H402 인문과학관 4층 100석
DB0102 오라클고급 22 장영실 H402 인문과학관 4층 100석
JV0103 자바심화 25 우장춘 H402 인문과학관 4층 100석
:
--※ 비식별자인 '과목명'이 식별자 일부인 과목번호에 의존적이기 때문에 2정규화를 수행해야한다.
-- 비식별자인 '강의실설명'이 비식별자인 강의실코드에 의존적이기 때문에 3정규화를 수행해야한다.
--------------------------------------------------------------------------------
테이블명 : 점수 → 자식 테이블
--------------------------------------------------------------------------------
과목번호 교수자번호 학번 학생명 점수
=====================
F.K
--------------------------------------------------------------------------------
DB0101 21 1817110 오승우 98
DB0101 21 1817116 최진규 89
:
-- 복합 PRIMARY KEY일 때 식별자가 아닌 컬럼은 식별자 전체에 대해서 의존적이어야 하는데
-- 식별자 전체 중 일부의 식별자 컬럼에 의존적일 때 테이블을 분리해야한다.(2 정규화를 수행해야된다.)
-- 복합 PRIMARY KEY로 구성하면 2정규화를 수행해야된다.
*/
제 3 정규화
--○ 제 3 정규화
--> 비식별자인 컬럼이 비식별자인 컬럼에 의존적일 때 테이블을 분리한다.
-- 식별자는 신경쓰지 않는다.
제 4 정규화
--○ 제 4 정규화
--> 20개의 컬럼중에서 빈번하게 쿼리되는 5개의 컬럼만 선택해 테이블로 분리하는 경우(1:1)
-- 제1정규화에서 PRIMARY KEY와 FORIGH KEY의 관계(1:N)
-- 논리적인 모델에서는 존재하지만 실제 물리적으로는 존재할 수 없는 관계(M:N)
-- 다대다 관계를 1:N 관계로 바꾸는 것인데 새로운 테이블을 파생시켜 줘야한다.
-- 중간에 연결해주는 테이블을 따로 만들어주어야 한다.
역정규화(비정규화)
/*
--○ 역정규화(비정규화)
--> 정규화가 아니다. 합치는 것이다.
-- 업무를 완벽하게 파악하고 어떤 데이터가 얼마나 들어올 것이다라고 판단할 수 있어야 역정규화를 할지 말지를 결정할 수 있다.
--> 부모 테이블의 크기와 자식 테이블의 크기가 비슷하면 사용하는 것이 좋음
-- 부모 테이블의 크기보다 자식 테이블의 크기가 작으면 사용하지 않는 것이 좋음
--ⓐ 경우 → 정규화를 수행하는 것이 바람직한 상황이다.
부서 테이블(300byte) 사원 테이블(60,000,000byte)
--------------------- ------------------------------------------ + --------
부서번호 부서명 주소 사원번호 사원명 직급 급여 일사일 부서번호 부서명
++++++++ ++++++++ ========
P.K P.K F.K
--------------------- ------------------------------------------ + --------
10 10 10 10 10 10 10 10 10 10
10개 행 1,000,000개 행
>> 조회 결과물
-------------------------
부서명 사원명 직급 급여
-------------------------
부서 테이블과 사원 테이블을 JOIN 시 크기
300 + 60,000,000 = 60,000,300 Byte
-->
SELECT A.부서명, B.사원명, B.직급, B.급여
FROM 부서 A JOIN 사원 B
ON A.부서번호 = B.부서번호;
역정규화를 한 부서별 사원 테이블만 읽어올 경우
(즉, 사원 테이블에 부서명 컬럼을 추가한 경우)
70,000,000 Byte
--ⓑ 경우 → 역정규화를 수행하는 것이 바람직한 상황이다.
부서 테이블 사원 테이블
(15,000,000byte) (60,000,000byte)
--------------------- ------------------------------------------ + --------
부서번호 부서명 주소 사원번호 사원명 직급 급여 일사일 부서번호 부서명
++++++++ ++++++++ ========
P.K P.K F.K
--------------------- ------------------------------------------ + --------
10 10 10 10 10 10 10 10 10 10
500,000개 행 1,000,000개 행
>> 조회 결과물
-------------------------
부서명 사원명 직급 급여
-------------------------
부서 테이블과 사원 테이블을 JOIN 시 크기
15,000,000 + 60,000,000 = 75,000,000 Byte
-->
SELECT A.부서명, B.사원명, B.직급, B.급여
FROM 부서 A JOIN 사원 B
ON A.부서번호 = B.부서번호;
역정규화를 한 부서별 사원 테이블만 읽어올 경우
(즉, 사원 테이블에 부서명 컬럼을 추가한 경우)
70,000,000 Byte
--==>> 실무적으로 일반적으로 부모테이블하고 자식테이블의 레코드의 갯수는 비슷하다.
-- 그래서 역정규화하는 것이 드물다
*/
반응형
'DB > Oracle' 카테고리의 다른 글
| [Oracle] 교육 4일차 (0) | 2018.01.10 |
|---|---|
| [Oracle] 교육 3일차 (0) | 2018.01.09 |
| [Oracle] 교육 2일차 (0) | 2018.01.05 |
| [Oracle] 교육 1일차 (0) | 2018.01.04 |
댓글
반응형
공지사항
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
링크
TAG
- 암브로셔스
- 직딩잇템
- 빅데이터 교육
- 코딜리티
- 빅데이터 기초
- javascript
- ambrosus
- DOCs
- 문제풀이
- 하이퍼레저 패브릭
- Hyperledger Fabric
- 코딩테스트
- 하이퍼레저 페브릭
- 알고리즘
- ubuntu
- codility
- Hyperledger Indy
- docker
- 빅데이터 강의
- 블록체인
- 코테
- Blockchain
- 어서와 데이터는 처음이지
- 빅데이터
- Hyperledger Fabric v1.1
- 하이퍼레저 인디
- Private Data
- 블록 체인
- 기초 of 기초 데이터 개념
- Hyperledger Fabric v1.2
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | |||
| 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 26 | 27 | 28 | 29 | 30 | 31 |
글 보관함