본문 바로가기
교육 및 세미나/신입사원 교육

7일차(2018.01.11)

hihelllo 2018. 1. 12. 15:37

HR계정으로 이동

SELECT USER
FROM DUAL;

--==>> HR


세 개 이상의 테이블 조인(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;

SELECT *
FROM TAB;
--==>>
/*
COUNTRIES	    TABLE
DEPARTMENTS	    TABLE
EMPLOYEES	    TABLE
EMP_DETAILS_VIEW    VIEW
JOBS	            TABLE
JOB_HISTORY	    TABLE
LOCATIONS	    TABLE
REGIONS	            TABLE
*/

SELECT *
FROM LOCATIONS;
--==>> LOCATION_ID   STREET_ADDRESS  POSTAL_CODE   CITY    STATE_PROVINCE   COUNTRY_ID

SELECT *
FROM REGIONS;
--==>> REGION_ID  REGION_NAME

SELECT *
FROM COUNTRIES;
--==>> COUNTRY_ID  COUNTRY_NAME   REGION_ID

SELECT *
FROM DEPARTMENTS;
--==> DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

SELECT *
FROM EMPLOYEES;
--==> EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

SELECT *
FROM JOBS;
--==>> JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
--    :


과제

--○ HR.JOBS, HR.EMPLOYEES, HR.DEPARTMENTS 테이블을 대상으로 직원들의 정보를 조회한다. 단, 조회 항목은 FIRST_NAME, LAST_NAME, JOB_TITLE, DEPARTMANET_NAME

형식1. SQL 1992 CODE

SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, JOBS J,DEPARTMENTS D
WHERE E.JOB_ID = J.JOB_ID
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;

형식2. SQL 1999 CODE

SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;


과제

--   EMPLOYEES, DEPARTMENTS, JOBS, LOCATIONS, COUNTRIES, REGIONS

--   이 6개의 테이블을 대상으로 직원들의 정보를 다음과 같이 조회한다.

--   FIRST_NAME, LAST_NAME, JOB_TITLE, DEPARTMENT_NAME, CITY, COUNTRY_NAME, REGION_NAME

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 JOBS J
ON E.JOB_ID = J.JOB_ID
LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
LEFT JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
LEFT JOIN COUNTRIES C
ON L.COUNTRY_ID = C.COUNTRY_ID
LEFT JOIN REGIONS R
ON C.REGION_ID = R.REGION_ID;

/*

Alexander	Hunold	Programmer	IT	Southlake	United States of America	Americas
Bruce	    Ernst	Programmer	IT	Southlake	United States of America	Americas
David	    Austin	Programmer	IT	Southlake	United States of America	Americas
Valli	    Pataballa	Programmer	IT	Southlake	United States of America	Americas
Diana	    Lorentz	Programmer	IT	Southlake	United States of America	Americas
Matthew	  Weiss	Stock Manager	Shipping	South San Francisco	United States of America	Americas
Adam	    Fripp	Stock Manager	Shipping	South San Francisco	United States of America	Americas
Payam	    Kaufling	Stock Manager	Shipping	South San Francisco	United States of America	Americas
Shanta	  Vollman	Stock Manager	Shipping	South San Francisco	United States of America	Americas
Kevin	    Mourgos	Stock Manager	Shipping	South San Francisco	United States of America	Americas
Julia	    Nayer	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Irene	    Mikkilineni	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
James	    Landry	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Steven	  Markle	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Laura	    Bissot	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Mozhe	    Atkinson	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
James	    Marlow	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
TJ	      Olson	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Jason	    Mallin	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Michael	  Rogers	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Ki	      Gee	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Hazel	    Philtanker	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Renske	  Ladwig	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Stephen	  Stiles	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
John	    Seo	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Joshua	  Patel	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Trenna	  Rajs	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Curtis	  Davies	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Randall	  Matos	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Peter	    Vargas	Stock Clerk	Shipping	South San Francisco	United States of America	Americas
Winston	  Taylor	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Jean	    Fleaur	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Martha	  Sullivan	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Girard	  Geoni	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Nandita	  Sarchand	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Alexis	  Bull	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Julia	    Dellinger	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Anthony	  Cabrio	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Kelly	    Chung	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Jennifer	Dilly	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Timothy	  Gates	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Randall	  Perkins	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Sarah	    Bell	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Britney	  Everett	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Samuel	  McCain	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Vance	    Jones	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Alana	    Walsh	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Kevin	    Feeney	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Donald	  OConnell	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Douglas	  Grant	Shipping Clerk	Shipping	South San Francisco	United States of America	Americas
Shelley	  Higgins	Accounting Manager	Accounting	Seattle	United States of America	Americas
William	  Gietz	Public Accountant	Accounting	Seattle	United States of America	Americas
Nancy	    Greenberg	Finance Manager	Finance	Seattle	United States of America	Americas
Daniel	  Faviet	Accountant	Finance	Seattle	United States of America	Americas
John	    Chen	Accountant	Finance	Seattle	United States of America	Americas
Ismael	  Sciarra	Accountant	Finance	Seattle	United States of America	Americas
Jose      Manuel	Urman	Accountant	Finance	Seattle	United States of America	Americas
Luis	    Popp	Accountant	Finance	Seattle	United States of America	Americas
Steven	  King	President	Executive	Seattle	United States of America	Americas
Neena	    Kochhar	Administration Vice President	Executive	Seattle	United States of America	Americas
Lex	      De Haan	Administration Vice President	Executive	Seattle	United States of America	Americas
Den	      Raphaely	Purchasing Manager	Purchasing	Seattle	United States of America	Americas
Alexander	Khoo	Purchasing Clerk	Purchasing	Seattle	United States of America	Americas
Shelli	Baida	Purchasing Clerk	Purchasing	Seattle	United States of America	Americas
Sigal	Tobias	Purchasing Clerk	Purchasing	Seattle	United States of America	Americas
Guy	Himuro	Purchasing Clerk	Purchasing	Seattle	United States of America	Americas
Karen	Colmenares	Purchasing Clerk	Purchasing	Seattle	United States of America	Americas
Jennifer	Whalen	Administration Assistant	Administration	Seattle	United States of America	Americas
Michael	Hartstein	Marketing Manager	Marketing	Toronto	Canada	Americas
Pat	Fay	Marketing Representative	Marketing	Toronto	Canada	Americas
Susan	Mavris	Human Resources Representative	Human Resources	London	United Kingdom	Europe
John	Russell	Sales Manager	Sales	Oxford	United Kingdom	Europe
Karen	Partners	Sales Manager	Sales	Oxford	United Kingdom	Europe
Alberto	Errazuriz	Sales Manager	Sales	Oxford	United Kingdom	Europe
Gerald	Cambrault	Sales Manager	Sales	Oxford	United Kingdom	Europe
Eleni	Zlotkey	Sales Manager	Sales	Oxford	United Kingdom	Europe
Peter	Tucker	Sales Representative	Sales	Oxford	United Kingdom	Europe
David	Bernstein	Sales Representative	Sales	Oxford	United Kingdom	Europe
Peter	Hall	Sales Representative	Sales	Oxford	United Kingdom	Europe
Christopher	Olsen	Sales Representative	Sales	Oxford	United Kingdom	Europe
Nanette	Cambrault	Sales Representative	Sales	Oxford	United Kingdom	Europe
Oliver	Tuvault	Sales Representative	Sales	Oxford	United Kingdom	Europe
Janette	King	Sales Representative	Sales	Oxford	United Kingdom	Europe
Patrick	Sully	Sales Representative	Sales	Oxford	United Kingdom	Europe
Allan	McEwen	Sales Representative	Sales	Oxford	United Kingdom	Europe
Lindsey	Smith	Sales Representative	Sales	Oxford	United Kingdom	Europe
Louise	Doran	Sales Representative	Sales	Oxford	United Kingdom	Europe
Sarath	Sewall	Sales Representative	Sales	Oxford	United Kingdom	Europe
Clara	Vishney	Sales Representative	Sales	Oxford	United Kingdom	Europe
Danielle	Greene	Sales Representative	Sales	Oxford	United Kingdom	Europe
Mattea	Marvins	Sales Representative	Sales	Oxford	United Kingdom	Europe
David	Lee	Sales Representative	Sales	Oxford	United Kingdom	Europe
Sundar	Ande	Sales Representative	Sales	Oxford	United Kingdom	Europe
Amit	Banda	Sales Representative	Sales	Oxford	United Kingdom	Europe
Lisa	Ozer	Sales Representative	Sales	Oxford	United Kingdom	Europe
Harrison	Bloom	Sales Representative	Sales	Oxford	United Kingdom	Europe
Tayler	Fox	Sales Representative	Sales	Oxford	United Kingdom	Europe
William	Smith	Sales Representative	Sales	Oxford	United Kingdom	Europe
Elizabeth	Bates	Sales Representative	Sales	Oxford	United Kingdom	Europe
Sundita	Kumar	Sales Representative	Sales	Oxford	United Kingdom	Europe
Ellen	Abel	Sales Representative	Sales	Oxford	United Kingdom	Europe
Alyssa	Hutton	Sales Representative	Sales	Oxford	United Kingdom	Europe
Jonathon	Taylor	Sales Representative	Sales	Oxford	United Kingdom	Europe
Jack	Livingston	Sales Representative	Sales	Oxford	United Kingdom	Europe
Charles	Johnson	Sales Representative	Sales	Oxford	United Kingdom	Europe
Hermann	Baer	Public Relations Representative	Public Relations	Munich	Germany	Europe
Kimberely	Grant	Sales Representative
*/

--※ 개별학습 체크

--   UNION/ UNION ALL

--   INTERSECT / MINUS

SCOTT계정으로 이동

SELECT USER
FROM DUAL;
--==>>SCOTT


SELF JOIN (자기 조인)

--○ EMP 테이블의 정보를 다음과 같이 조회할 수 있도록 한다.

/*
사원번호    사원명     직종      관리자번호     관리자명      관리자직종명

7369     SMITH      CLERK       7902         FORD          ANALYST

:

:

EMPNO       ENAME      JOB        MGR

|------------------------------------------------| |

EMPNO           ENAME         JOB |------------------------------------------| */

-- 소희 풀이

SELECT *
FROM EMP;

SELECT *
FROM DEPT;

SELECT F.EMPNO"사원번호", F.ENAME"사원명", F.JOB"직종", F.MGR"관리자번호", E.ENAME"관리자명", E.JOB"관리자직종명"
FROM EMP E JOIN EMP F
ON E.EMPNO = F.MGR;

-- 강사님 풀이

SELECT E1.EMPNO"사원번호", E1.ENAME"사원명", E1.JOB"직종명", E2.EMPNO"관리자번호", E2.ENAME"관리자명", E2.JOB"관리자직종명"
FROM EMP E1 LEFT JOIN EMP E2
ON E1.MGR = E2.EMPNO;

SELECT *
FROM EMP;

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)이어야 함
  • 원자적 도메인(atomic domain)

: 도메인의 원소가 더이상 나누어질 수 없는 단일체일 때를 나타냄.

5) 릴레이션(relation)

  • 파일 시스템에서 파일과 같은 개념
  • 중복된 튜플을 포함하지 않는다.(모두 상이함 : 튜플의 유일성)
  • 릴레이션 = 튜플(엔티티:entity)의 집합. 따라서 튜플의 순서는 무의미하다.
  • 속성(attribute)간에는 순서가 없다.


무결성 (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

1. 해당 테이블에 대한 기본 키를 생성한다.

2. 테이블에서 각 행을 유일하게 식별하는 컬럼 또는 컬럼의 집합이다.

  • 기본키는 테이블 당 하나만 존재한다.
  • 그러나 반드시 하나의 컬럼으로만 구성되는 것은 아니다.
  • NULL일 수 없고, 이미 테이블에 존재하고 있는 데이터를 다시 입력할 수도 없다.
  • 내부적으로 UNIQUE INDEX가 자동으로 생성된다.

3. 형식 및 구조

① 컬럼 레벨의 형식

  • 컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] PRIMARY KEY[(컬럼명, ...)]

② 테이블 레벨의 형식

-- , 컬럼명 데이터타입

-- , 컬럼명 데이터타입

-- , CONSTRAINT CONSTRAINT명 PRIMARY KEY(컬럼명, ...)

4. CONSTRAINT 추가 시 CONSTRAINT명을 생략하면

  • 오라클 서버가 자동적으로 CONSTRAINT명을 부여한다.
  • 일반적으로 CONSTRAINT명은 『테이블명_컬럼명_CONSTRAINT약자』형식으로 기술한다.(구성 순서는 업무나 상황에 따라 다르다.)


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;
--==>>
/*
이름   널        유형
---- -------- ------------
COL1 NOT NULL NUMBER(5)
COL2          VARCHAR2(30)
*/

SELECT *
FROM USER_CONSTRAINTS;
--==>>
/*
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	FK_DEPTNO	  R	EMP	     SCOTT	PK_DEPT	NO ACTION	ENABLED	NOT  DEFERRABLE	IMMEDIATE	VALIDATED	USER NAME	      18/01/05
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';
--==>> SCOTT	SYS_C007001	TBL_TEST1	COL1	1

--○ 제약조건이 설정된 소유주,  제약조건명,      테이블,      제약조건종류,      컬럼정보 확인

--                    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 지정 실습(② 테이블 레벨의 형식) 

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');

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 지정) → 복합 프라이머리 키 지정 

CREATE TABLE TBL_TEST3
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINT TEST3_COL1_COL2_PK PRIMARY KEY(COL1, COL2)
);
--==>> Table TBL_TEST3이(가) 생성되었습니다.

-- COL1+COL2 합쳐서 PRIMARY KEY임
-- 데이터 입력
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(1, 'TEST');
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(2, 'ABCD');
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(3, 'TEST'); -- O
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(1, 'ABCD');


PK 지정 실습(④ 테이블 생성 이후 제약조건 추가) 

CREATE TABLE TBL_TEST4
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
);
--==>> Table TBL_TEST4이(가) 생성되었습니다.

-- 테이블안에 데이터에 PRIMARY KEY 속성에 맞지 않으면 속성을 바꾸지 못한다.
-- 제약조건 추가

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-4444-..

농심     강원원주로  033-99-9999   서운성     차장   un@gma...  010-5555-..

:

100만 이상

(농심 본사만 100만 명)

가정) 여의대방로 농심 이라는 회사에 근무하는 거래처 직원 명단이 본사 직원만 해도 100만 명이라고 가정한다. (한 행은 10byte이다.)

어느 날... 여의대방로에 위치한 농심 본사가 경기 분당으로 사옥을 이전하였다.

-- UPDATE 를 수행해야 할 상황이 발생하게 되었다. (이 때 구성해야 하는 쿼리문)

UPDATE 거래처직원
SET 회사주소='경기분당', 회사전화='031-345-6789'
WHERE 거래처회사명='농심'
AND 회사주소='여의대방로';

-- 그러면... 100만 명의 회사 주소와 회사 전화 정보를 변경해야 하며 100만 개 행을 하드디스크상에서 읽어다가 메모리에 로드시켜 주어야 한다. 즉, 100만 * 70BYTE를 모두 하드디스크상에서 읽어다가 메모리에 로드시켜 주어야 한다는 말이다. 이는, 테이블의 설계가 잘못되었으므로 DB 서버는 조만간 메모리 고갈로 인해 DOWN 될 것이다.

--==>> 그러므로 정규화 작업에 들어간다.


제 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 정규화 특징

거래처회사명 거래처직원명

--정규화 이전

SELECT 거래처회사명


제 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

--------------------------------------------------------------------------------

DB101         21        1817110   오승우     98

DB101         21        1817116   최진규     89

:

-- 복합 PRIMARY KEY일 때 식별자가 아닌 컬럼은 식별자 전체에 대해서 의존적이어야 하는데 식별자 전체 중 일부의 식별자 컬럼에 의존적일 때 테이블을 분리해야한다.(2 정규화를 수행해야된다.)

-- 복합 PRIMARY KEY로 구성하면 2정규화를 수행해야된다


제 3 정규화

비식별자인 컬럼이 비식별자인 컬럼에 의존적일 때 테이블을 분리한다.


제 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,000 byte)                  (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
--==>> 실무적으로 일반적으로 부모테이블하고 자식테이블의 레코드의 갯수는 비슷하다.
--     그래서 역정규화하는 것이 드물다
*/


'교육 및 세미나 > 신입사원 교육' 카테고리의 다른 글

9일차(2018.01.15)  (0) 2018.01.15
8일차(2018.01.12)  (0) 2018.01.12
6일차(2018.01.10)  (0) 2018.01.12
5일차(2018.01.09)  (0) 2018.01.12
4일차(2018.01.05)  (0) 2018.01.12

'교육 및 세미나/신입사원 교육' Related Articles

티스토리툴바