모각코 4회차 - SQLD 자격증 공부

모각코 네 번째 회의 때는 각자 모여서 공부를 진행했습니다!

저는 제 47회 SQLD 자격증 공부에 대해서 정리하는 시간을 가졌습니다.

SQLD 자격증을 준비하시는 분이라면 오늘 제가 정리한 것을 쭉 훑고 공부를 진행하시면 좀 빠르게 학습하실 수 있을 것 같습니다. 그냥 어떤 문제가 나오고, 어떤 것을 꼭 봐야 하는지 간단히 정리해봤으니 SQLD 자격증이 코앞으로 다가왔을 때 한번 보시고 기출 문제 하나정도 풀면 그래도 도움이 될 것 같습니다!

시작해볼까요~?

 

✏️ PART4. SQLD 자격증 60점을 넘기 위한 필수내용

 

1. SQL 명령문 개괄

- 우선, 연산순서 정렬하는 문제가 나올 수 있습니다.

from - where - group by - having - select - order by 등이 있어요!

 

- 종류에는 무엇이 있을까요?

DML(data manipulation language) : select, insert, delete, update, merge...

DDL(data definition language) : alter, create, modify, drop, rename...

TCL(transaction control language) : roll back, commit...

DCL(data control language) : grant, revoke

2. SELECT

- distinct 는 "집약"해주는 기능을 한다고 보시면 됩니다!

(ex) 10, 10, 20, 20, 30 ,...=> 10, 20, 30 으로 원하는 정보를 집약시켜주는 것이 DISTINCT

 

- distinct deptno, mgr 는 distinct (deptno, mgr)과 같이 괄호를 친 것과 같은 효과입니다.

= group by deptno, mgr 이랑 비슷하다고 보시면 돼요!

3. AS

- SELECT 절에 쓰는 것 : 1) AS 생략 가능, 2) 칼럼명에 띄어쓰기 있는 경우 (ex) "직원 번호"

- FROM 절에 쓰는 것 : 1) AS 사용 불가!

4. concat

- SQL server에서는 + 라는 기호를 쓰고

- Oracle에서는 || 라는 기호를 씁니다!

- concat(_,_) : 인수(parameter)는 반드시 "2개"여야 합니다! (3개, 4개 불가!)

5. 논리 연산자 

- and, or, not

- 연산순위 : NAO(나오) 라고 외워봅시다! (Not And Or 순)

(ex) NOT <조건1> AND <조건2> AND NOT <조건3> OR <조건4>

     (not <조건1> 이 새로운 <조건5>, not <조건3>이 새로운 <조건6>)

= <조건5> AND <조건2> AND <조건6> OR <조건4>

= (<조건5> AND <조건2> AND <조건6>) OR <조건4> 

     (and 먼저)

6. SQL 연산자

- BETWEEN

 (ex) A between 1 and 2 는 1 <= A <= 2 랑 의미가 같다.

 

- IN

(ex) A in (1, 2, 3) 는 A=1 or A=2 or A=3 랑 의미가 같다.

 

- 와일드카드 쓰는법이 안나올리가 없겠죠!?

LIKE : _  : 미지의 한 글자

         : % : 0 이상의 글자

         : escape : (_%)를 문자로 취급해주는 함수

 

- 예시를 통해서 무슨 뜻인지 자세히 알아봅시다!

(ex) '_L%' 는 이름에 두번째 글자가 L인 사원들을 다 고르는 것~ 등의 의미로 쓸 수 있습니다.

(ex) 'a%' : a로 시작하는 데이터

       '%a' : a로 끝나는 데이터 

       '%a%' : a가 포함되어 있는 데이터 

(ex) 문자열에서 '_'가 포함되어 있는 row 검색은 어떻게 할까요?

      = SELECT * FROM A

         WHERE col2 LIKE '%@_%' ESCAPE '@'

      = SELECT * FROM A

         WHERE col2 LIKE '%[_]%' 

(여기서 '@'는 아무글자나 다 됩니다! (# 으로도 많이 쓰곤 해요))

 

- SQL server에서는 TOP

(select 절에서 top (n) 컬러명 은 컬럼을 출력할 때 상위 n개를 가져오겠다는 말입니다)

- Oracle에서는 ROWNUM

(ex) SELECT empno, sal

       FROM emp

       WHERE rownum <= 3

       ORDERBY sal DESC

명령문에서, ORDERBY 절은 가장 마지막에 실행됩니다.

예를 들어

sal 정렬 전 테이블이             sal 정렬 후 테이블이 

.......     1500                                      .......     5000

.......     1700                                      .......     4500

.......     1800                                      .......     3000

              .....                                                     .....

형태라면, ORDERBY 가 가장 마지막에 실행되므로, 정렬전의 테이블에 ROWNUM의 조건절이 실행이 됩니다.

그런 후에, ORDERBY 가 실행되어서 

.......     1800 

.......     1700 

.......     1500

이렇게만 출력이 될 것입니다.

 

7. NULL

무조건 나오는 문제, 바로 NULL 입니다.

1. NULL의 정의는 부재, 모르는 값을 의미합니다.

2. NULL+2, NULL-4, NULL x NULL ... 등의 NULL의 '산술연산'은 모조리 다 NULL 입니다.

3. NULL의 '비교연산'은 무조건 IS, IS NOT으로만 비교할 수 있습니다.

(ex) NULL = NULL 이나 NULL = 2 등의 등호를 사용하는 등의 형식은 불가입니다!

알수없음의 논리로 해석되어서 사실상 False입니다!

 

4. 정렬에서 NULL의 의미

: Oracle에서는 양의 무한대(∞)를 의미하고, SQL SERVER에서는 음의 무한대(-∞) 를 의미합니다.

즉, 오름차순 기준으로 Oracle에서는 null이 있는 값을 정렬하면 null 이 맨 마지막에 나오고, 

sql server에서는 null이 있는 값이 맨 처음에 나옵니다.

 

- NVL(값1, 값2) : 값1이 Null 이라면, 값2, 아니라면 값1  - (*널뛰기 라고 외워봐요!) 

- NVL2(값1, 값2, 값3) : 값1이 Null이라면, 값3, 아니라면 값2

- isNull(값1, 값2) : NVL과 동일한 함수

- Null if(값1, 값2) : 값이 같으면 null, 다르면 값1             - (*같이놀자! 라고 외워봐요!) 같으면 (null) 아니면 값1

- coalesce(값1, 값2, 값3,...) :  null 이 아닌 첫 번째 값    - (coalesce 인자는 무제한)

(ex) coalesce(null, null, 2, ....) = 2 

       coalesce(2,1,3,...) = 2

       coalesce(null,3,...) = 3

8. 정렬

- 정렬의 특성  1) 가장 마지막에 실행!

                       2) 성능이 느려질 가능성이 존재!

                       3) null 값과의 관계 => Oracle에서는 양의 무한대(∞) 로 취급!

- 컬럼번호로 정렬  : 출력되는 컬럼의 수 보다 큰 값이 불허!

- 인수 2개 정렬

     (ex) sal desc, ename asc   : sal이 같으면, ename 오름차순

- SELECT 절에 칼럼명이 없어도 정렬 가능

     (ex) SELECT ename 

             ORDERBY sal

       =  sal 이 없는데도 sal로 정렬하는 것이 가능!

9. 숫자 함수

- round 자릿수 

(ex) SELECT ROUND(138.947, _ , _) : _ 인수가 무엇인지에 따라 다른 결과를 나타냅니다!

(세번째 인수가 생략되어 있거나 0이면, 반올림을 의미합니다!, 음수면 버림을 뜻합니다.)

(ex) SELECT ROUND(138.947, 1) : = 138.9 (소수 1번째 자리까지 반올림) 

(ex) SELECT ROUND(138.947, 2, 0) : = 138.950 (소수 2번째 자리까지 반올림)

(ex) SELECT ROUND(138.947, 2, -1) : = 138.940 (소수 2번째 자리까지 버림)

 

- ceil 함수(oracle에서는 ceil, sql server에서는 ceiling) : 정수값으로 출력

(ex) SELECT CEIL(21.35) : = 22 

 

- floor 함수 : 정수값으로 출력

(ex) SELECT ROUND(21.95) : = 21

 

10. 문자열 함수

- upper : 대문자로 바꾸는 함수

- lower : 소문자로 바꾸는 함수

 

- LPAD : 문자길이 왼쪽부터 총길이만큼 지정한 문자를 채우는 함수

  (ex) LPAD(컬럼명, 5, ' ')  =>      30

- RPAD : 문자길이 오른쪽부터 총길이만큼 지정한 문자를 채우는 함수

  (ex) RPAD(컬럼명, 5, ' 0') =>  30000

 

- TRIM : 양쪽 공백이나 특정문자를 제거하는 함수

  (ex) TRIM('문자열') :      NEWYORK      => NEWYORK

- LTRIM : 왼쪽 공백이나 특정문자를 제거하는 함수

  (ex) LTRIM('문자열', '0') : 0010 => 10

- RTRIM : 오른쪽 공백이나 특정문자를 제거하는 함수

  (ex) RTRIM('문자열', '0') : 0010 => 001

 

- substr : 문자열 자르는 함수 

  (ex) substr('문자열', 1, 4) : 1번째 글자부터 4개 자르기

  (ex) substr('문자열', 10) : 10번째 글자부터 끝까지 자르기

  (ex) substr('문자열', -3) : 뒤에서 3번째 번째 글자부터 끝까지 자르기

- Instr : 시작위치 인덱스 출력하는 함수

11. 날짜 함수

* 형변환을 일으키는 함수

=> to_char , to_date -- 실습을 해보세요!

 

- oracle에서는 sysdate

- sql server에서는 getdate

  (ex) 날짜데이터 + 100 : 100일 이후라는 의미 (days로 인식합니다!)

12. DECODE/case

- case만 살펴보자면, 

case WHEN then ...1

         WHEN then ...2

else ...(else가 없는 경우, 1,2 만족하지 않는다면) = null 이 출력됩니다!

end

13. 집계함수

- null 과의 관계!!

(ex)

  A      B      C

null   null    1

  3      2      2

null    2      3

의 테이블이라면, 

sum()은 자동으로 null 값을 제외시키기 때문에,

 

sum(A) = 3

sum(B) = 4

sum(C) = 6

로 나타낼 수 있습니다.

count(A) = 1  (count(컬럼)이면 null 값을 제외한 3뿐이니까!)

count(*) = 3   (count * 은 row개수를 세는데, null 값이 포함된 row도 다 포함합니다!)

 

그렇다면, sum(A+B+C) 는 무엇일까요?

sum(A) + sum(B) + sum(C) 와는 값이 다른데요, 우선, sum(A) + sum(B) + sum(C) = 3 + 4 + 6 = 13 입니다. 

sum(A+B+C)로 하면, 테이블을 우선 합쳐주고 sum 하기 때문에,

 A+B+C

   null

    7

   null     

형식의 테이블이 나와서, sum(A+B+C)는 7입니다!

이 부분 주의해주세요!

14. GROUP BY

개략적인 특징

- 집약기능이 있습니다

- where 다음에 실행이 됩니다. (HAVING)

- 그룹수준의 정보를 바꿉니다.

15. JOIN

- natural join에서는 alias 사용 불가입니다!

- using

- 중복된 컬럼 "하나"로 출력합니다. (중복된 컬럼은 제일 앞에 등장)

 

- left outer join

(ex) A left outer join B 가 무엇이랑 동일한가?

 = A col1 = B col1 (+)

= left면 반대로 오른쪽에 붙여서 

= 왼쪽이 뚱뚱해지는데, (+) 기호를 join key column 뒤쪽에다 붙이기 때문입니다!

(ex) FROM A , B, C

   조인순서는 join은 A,B 먼저 그 다음에 C 순으로~

16. 서브쿼리

- select : scalar

- from : inline view --> 메인쿼리의 컬럼 사용 가능합니다!

- where : 거의 모든 서브쿼리

- group by : 사용못함! (X)

- having : 거의 모든 서브쿼리

- order by : scalar

 

(ex)

SELECT

FROM

WHERE SELECT

               FROM B col

               A col 1

바깥 테이블이 A테이블 안쪽 테이블이 B테이블 이라고 하면, 

col 1 = A를 찾고 한 번 돌아갈 때마다 다 돌립니다. (for문이랑 비슷하다고 생각하시면 됩니다)

 

- 함수 : in , any/some, all, exist(해당 문자가 존재하면 True, 0 rows 면 False)

 

17. 집합 연산자

- union 

- intersect

- minus(except)

- union all

union, intersect, minus(except)는 정렬작업이 있기 때문에 다소 느린편입니다.

반면, 정렬작업이 없는 union all은 빠르다는 특징이 있습니다!

union all이 중복데이터가 존재하고 빠르다는 것을 기억하시면 될 듯 합니다!

 

18. DDL

- "TCL"과 연관지어서 생각할 필요가 있습니다.

- 항상 TCL인 roll back과 commit과 연관되어 문제가 출제되기 마련입니다.

 

1. TRUNCATE VS DROP

TRUNCATE 와 DROP 은 둘다 DDL인데,

TRUNCATE는 입주민 퇴거 개념으로 이해하시면 됩니다. 즉, 구조가 남는다는 의미죠!

하지만, DROP은 철거로 이해하면 편합니다. 구조도 삭제하기 때문이죠!

 

2. TRUNCATE VS DELETE

TRUNCATE는 DDL 인데, 비슷한 기능인 DML로서 DELETE가 있다는 것도 알아두면 좋습니다!

19. DML

- insert, update, delete 에서는 insert의 오류상황을 살펴보고 시험보러 들어가세요!

역시 TCL인 roll back과 commit과 연관되어 문제가 출제되기 마련입니다.

- merge도 DML 인것을 잊지마세요!

20. 제약조건

- PK = unique + not null

- PK는 null 이 아니어야 하고 하나만 존재해야 합니다!

21. DCL 제약조건

- grant, revoke 정의를 살펴보세요! grant는 권한을 부여하는 것, revoke는 권환을 다시 회수하는 명령어입니다!

- role의 특징은 객체이지, 명령어가 아니라는 점입니다. table, index, view,.. 처럼 role도 하나의 객체입니다!

1) role은 role에게 부여가 가능

2) role은 사람에게도 부여가능

3) 사람들은 같은 role을 가질 수 있음

4) rol을 부여하려면 권한이 필요

등으로 role의 특징을 정리할 수 있겠군요!

 

- grant에서의 on to 문법과 revoke에서의 on from 문법도 기출로 살펴보고 가세요!

22. VIEW

- 장점부터 외워봅시다. 독 편 부 !

• 독립성 : 기존 테이블의 구조가 변경되어도, VIEW를 따로 update할 필요는 없습니다.
• 편리성 : 편리하다. 계속 테이블을 조작할 필요가 없고, 쓸 때 마다 나옵니다. 
• 보관성 : 원하는 정보만 줄 수 있습니다.

- 단점

• 뷰에 인덱스를 구성할 수 없다
• 뷰를 포함하여 뷰를 만든 경우 연관 뷰를 삭제하면 생성된 뷰도 삭제된다
• 한번 정의된 뷰는 수정이 불가하다

23. 그룹함수

(ex) 결과값을 주고 무엇을 썼는지 물어보는 문제가 나옵니다.

- roll up

- cube

- grouping sets

 

roll up에 인수가 2개인 경우에

roll up(A,B)

roll up(B,A)

는 다른 결과가 나옵니다. (계층구조이기 때문에!) 즉, ROLLUP함수는 인수의 순서에도 영향을 받게 됩니다.

 

cube에 인수가 2개인 경우에

cube(A,B)

cube(B,A)

는 같은 결과가 나옵니다. 즉, CUBE함수는 인수의 순서에 무관하게 결과가 같습니다.

 

(ex) 표를 주고, ROLLUP 인지 CUBE인지, GROUPINGSETS 인지 판단하는 문제가 나옵니다.

1) 먼저 NULL 값을 다 찾으세요!

2) 총합행이 있는지 없는지 찾으세요!

2-1) 만약 총합행이 없다(X)면, GROUPINGSETS 입니다.

2-2) 만약 총합행이 있다(O)면, 한쪽만 결과가 나와서 계층구조로 나오면 ROLL UP입니다. (행의 수가 적습니다.)

                                                 양쪽으로 둘 다 결과가 나오면 CUBE 입니다.(행의 수가 많습니다.)

24. TCL

- commit, roll back ..> 데이터 무결성을 보장합니다.

- auto commit off and begin transaction ..-> DDL에 commit 기능을 없애는 것입니다.

25. 윈도우 함수

- 반드시 나오는 문제 중 하나입니다.

1) ROWS vs RANGE

   : 결과값의 "차이점"을 숙지하세요! ( 같은 값 있는지 없는지의 유무!!)

   : ROWS는 조회된 ROW 하나하나를 대상으로 연산하며,
   : RANGE는 ORDER BY 를 통해 정렬된 컬럼에 같은 값이 존재하는 ROW가 여러 개일 경우, 동일한 컬럼값을 가지는 모      든 ROW를 묶어서 연산을 합니다.

  

2) RANK vs DENSE_RANK

   : RANK는 중복을 건너뜁니다. (ex) 1, 1, 3, 4, ...

   : DENSE_RANK는 중복을 건너뛰지 않습니다. (ex) 1, 1, 2, 3, ...

 

3) PARTITION BY vs GROUP BY

결론은 데이터 다 보고 싶으면 PARTITION BY, 요약해서 하나씩 한줄씩만 보려면 GROUP BY

 

26. 계층형 질의

- 프자부 부자순! 으로 외워봅시다.

- Prior 자식데이터 = 부모데이터 (부모데이터 = Prior 자식데이터)

- 부모에서 자식으로 가면 순방향

(ex)  level 1    King ... empno

        level 2    James ... mgr

        level 3    Scott

현재 level2라면, James의 mgr이 이전의 King의 empno다. 

즉, Prior empno = mgr 로 표현할 수 있습니다.

실습은 이렇게 풀어야 합니다. 이론과는 다른 느낌입니다. 꼭 실습을 해보세요!

 

27. 절차형 PL/SQL

- Exception은 생략이 가능합니다!

- Procedure와 Trigger와 user_defined_function의 차이점을 기억하세요!

- Procedure : 반드시 값이 안나옵니다.

- Trigger : roll back, commit이 불가능합니다. 보통 DML 많이 씁니다.

- user_defined_function : 반드시 값이 나옵니다.

28. 데이터 모델링

- 현실에서의 업무를 데이터 모델화 할 수 있습니다.

1. 데이터 구조화는 "업무"에만 집중해서 정보시스템을 만듭니다. Process 중심이라고 볼 수 있죠!

(ex) 서점을 예로 든다면

  [책] - [돈 받는다] - [매출이 오른다] 처럼

"절차"에 따라서 프로그래밍이 이루어집니다.

 

2. 관계형 데이터베이스 

- 데이터 "자체"의 "관계"를 중심으로 모델링 하는 방법이 바로 관계형 데이터베이스 입니다.

(ex) 서점에서도 [배송 process]가 있고 [광고 process]가 있고 [매출 process]가 있듯이, 각각의 process가 존재합니다.

       각자 process가 다르기 때문에, 업무에만 집중해서 정보시스템을 만든다면, 정보의 중복이 발생하고, 데이터 quality가 떨어지는 비효율적인 현상이 발생합니다.

그래서, 1970년대에, 이런 데이터 자체의 관계를 중심으로 모델링하는 관계형 데이터베이스가 나왔습니다. 우리가 배우는 DB가 바로 이 관계형 데이터베이스입니다.

 

3.객체지향 데이터베이스

- object 중심으로 모델링 하는 방법입니다. 

- 하지만, 너무 복잡한 탓에, 잘 쓰이지 않게 됐습니다.

29. 엔터티(entity)

- 관계형 데이터베이스의 데이터 자체는 엔터티를 뜻합니다.

- 관계형 데이터 베이스 지도를 그리기 위한 기초 개체인 엔터티에 대해서 알아봅시다!

- 먼저, 엔터티란 관리하고자 하는 "대상"을 뜻합니다.

(ex) 병원에서 환자를 관리할 때, entity가 무엇이 될까요? => "환자"가 엔터티가 됩니다.

 

- 특징을 살펴봅시다

1. 인스턴스는 2개 이상이어야 합니다.

2. 관계는 하나 이상 가져야 합니다.

3. "업무 프로세스"에 이용되어야 합니다.

 

- 엔터티 분류도 살펴보고 가시죠!

유 개 사 / 기 중 행 으로 외워봅시다!

"유형 엔터티, 개념 엔터티, 사건 엔터티"

"기본 엔터티, 중심 엔터티, 행위 엔터티"

로 분류 할 수 있습니다.

30. 속성(attribute)

- 관리하고자 하는 대상의 "인스턴스"의 특성, 속성을 뜻합니다.

- 속성은 인스턴스들의 집합이라고 할 수 있죠!

(ex) 사람 중에서 Scott이라는 사람의 얼굴을 구성하는 눈,코,입 등이 다 속성이 될 수 있습니다.

 

- 속성의 분류도 알아봐야겠죠?

기 설 파 로 외워봅시다.

- 기본 속성: 업무로부터 추출한 모든 속성이 여기에 해당하며 엔터티에 가장 일반적이고 많은 속성을 차지한다

- 설계 속성 : 업무상 필요한 데이터 이외에 데이터 모델링을 위해, 업무를 규칙화하기 위해 속성을 새로 만들거나 변형하여 정의하는 속성

- 파생 속성: 다른 속성에 영향을 받아 발생하는 속성으로서 보통 계산된 값들로 다른 속성에 영향을 받기 때문에 될 수 있으면 적게 정의하는 것이 좋습니다.

 

31. 도메인

- 도메인은 (데이터 유형, 크기, 제약조건, check, primary key) 등 "값의 범위"를 의미합니다!

 

32. 관계

- IE표기법 과 Barker표기법의 차이를 숙지해야 합니다.

 

<IE 표기법>

- 관계는 둘 다 까마귀 발로 표현 합니다. (1:1 이든 1:N이든)

- IE 표기법에서는 엔터티가 각진 사각형입니다.

- 식별자를 맨 위에다가 표시합니다.(PK)

- 1번이 2번을 어떻게 취급하는지에 따라 그림이 달라지니 유의하세요! (상속에 따라 다름)

 

<Barker 표기법>

- 역시 관계 표현은 까마귀 발로 표현합니다.

- Barker표기법에서는 엔터티의 모서리가 둥근형태의 사각형입니다.

- 식별자를 #으로 표기합니다.

- 1번이 2번을 어떻게 취급하는지에 따라 그림이 달라지니 유의하세요! (상속에 따라 다름)

 

33. 식별자

- 식별자와 비식별자 관계는 위 ERD에서처럼 알 수 있습니다.

(ex)

- 주식별자의 특징

: 유 최 불 존 으로 외워봅시다!

1) 유일성 : 인스턴스를 유일하게 구분할 수 있는 속성

2) 최소성 : 여러가지 속성을 묶어서도 식별자가 가능한데, 그것이 최소여야 하는 것

3) 불변성 : 한번 만들어놓으면 바뀌지 않는 것

4) 존재성 : not null ( null 이 허용되지 않음)

=> 이 4가지 특성을 다 만족하면 후보키(Candidate key)가 될 수 있고, 그 주에서 대표로 선정된 것이 기본키(Primary key)가 되는 것입니다! 나머지는 대체키(Alternative key)가 되겠죠!

 

34. 식별자 관계/ 비식별자 관계

- 식별자 vs 비식별자 로 살펴본다면,

 

식별자는 강한관계입니다. 단점은 SQL 구문이 복잡해집니다.(PK 속성수가 증가하기 때문이죠)

ERD에서 표기법은 식별자는 실선

VS

비식별자는 약한관계입니다. 단점은 '조인'이 많아져서 느려집니다.

이고, 비식별자는 점선입니다.

 

(※ ERD 서술 규칙을 살펴본다면,

1) 시선이 좌상단 => 우하단 으로 움직여야 합니다.

2) 관계명을 반드시 표시 안해도 됩니다!

3) UML은 객체지향에서만 쓰입니다!)

 

35. 성능 데이터 모델링

- 백종원의 골목식당 컨설팅이라고 생각해봅시다!

1) 아키텍처

(데이터 베이스 "구조"를  계속 바꾸는 방법입니다)

테이블, 파티션,..

 

(ex) 테이블을 자르거나 분할하거나, 파티션을 나누거나 등등의 방법으로

말 그대로, 아키텍처를 뜯어 고치면서 성능을 개선하는 방법입니다. 

백종원의 골목식당에서 주방구조부터 싹 다 바꾸는 것과 비슷하다고 할까요?

 

=> 가장 효과적인 방법이죠.(구조부터 바꾸는 것이니까요!) (

 

VS

 

2) SQL 쿼리문(명령문)

조인수행원리, optimizer, 실행계획,..

등을 통해 성능을 개선시키는 방법입니다.

조인수행원리는 꼭 나오는 문제이니까 반드시 숙지하시길 바랍니다!

36. 정규화

- 매우 중요한 파트입니다. 데이터 모델링보다 먼저 해야 하는 것이 정규화 과정입니다.

- 1차 정규화 : 원자성 확보 (속성 값 2개인 것 자르기)

제1 정규형은 다음과 같은 규칙들을 만족해야 한다.

1. 각 컬럼이 하나의 속성만을 가져야 한다.
2. 하나의 컬럼은 같은 종류나 타입(type)의 값을 가져야 한다.
3. 각 컬럼이 유일한(unique) 이름을 가져야 한다.
4. 칼럼의 순서가 상관없어야 한다.

제1정규화가 필요한 테이블

제 1정규화를 한 테이블

- 2차 정규화 : 부분함수 종속 제거 -> 예시를 보고 시험장 들어가세요!

제2 정규형은 다음과 같은 규칙을 만족해야 합니다.

1. 1정규형을 만족해야 한다.
2. 모든 컬럼이 부분적 종속(Partial Dependency)이 없어야 합니다. == 모든 칼럼이 완전 함수 종속을 만족해야 한다.

(부분적 종속이란 기본키 중에 특정 컬럼에만 종속되는 것이다.

완전 함수 종속이란 기본키의 부분집합이 결정자가 되어선 안된다는 것이다.) => 비슷한 말입니다.

제 2정규화가 필요한 테이블

위 테이블에서 기본키는 (학생 번호, 과목)으로 복합키입니다.

그런데 이때 지도교수 칼럼은 (학생 번호, 과목)에 종속되지 않고 (과목) 에만 종속되는 부분적 종속입니다. 

 

따라서 제2정규화를 만족하지 않으므로 아래와 같이 분해해야 합니다.

제 2정규화를 한 테이블

 

- 3차 정규화 : 이행함수 종속 제거 -> 예시를 보고 시험장 들어가세요!

제3 정규형은 다음과 같은 규칙을 만족해야 합니다.

1. 2 정규형을 만족해야 한다.
2. 기본키를 제외한 속성들 간의 이행 종속성 (Transitive Dependency)이 없어야 한다.

이행 종속성이란 A->B, B->C 일 때 A->C 가 성립하면 이행 종속이라고 한다. 

위와 같은 테이블을 봅시다. ID를 알면 등급을 알 수 있습니다. 등급을 알면 할인율을 알 수 있습니다. 따라서 ID를 알면 할인율을 알 수 있습니다. 따라서 이행 종속성이 존재하므로 제 3 정규형을 만족하지 않는다.

3정규형을 만족하기 위해서는 아래와 같이 분해해야 합니다.

제 3정규화를 한 테이블

- BCNF(Boyce-Codd Normal Form) : 후보키가 상속하는 것을 제거합니다. 

BCNF는 제 3정규형을 좀 더 강화한 버전으로 다음과 같은 규칙을 만족해야 합니다.

1. 3정규형을 만족해야 한다.
2. 모든 결정자가 후보키 집합에 속해야 한다.

(= 후보키 집합에 없는 칼럼이 결정자가 되어서는 안 된다는 뜻이다.)

BCNF가 필요한 테이블

위와 같은 테이블을 보자. (학생 번호, 과목)이 기본키로 지도교수를 알 수 있습니다. 하지만 같은 과목을 다른 교수가 가르칠 수도 있어서 과목-> 지도교수 종속은 성립하지 않습니다. 하지만 지도교수가 어떤 과목을 가르치는지는 알 수 있으므로 지도교수-> 과목 종속이 성립한다.

 

이처럼 후보키 집합이 아닌 칼럼이 결정자가 되어버린 상황을 BCNF를 만족하지 않는다고 합니다.

(참고로 위 테이블은 제3 정규형까지는 만족하는 테이블입니다. )

 

BCNF를 만족하기 위해서는 아래와 같이 분해하면 됩니다.

BCNF를 만족한 테이블

- 이상현상 보고 시험장 들어갑시다! 삽입 이상 과 삭제 이상 예시 살펴보세요!

- 성능은 어떻게 될까요?

=> SELECT 절에서는 JOIN 때문에 느려질 수 있습니다. (성능 저하)

=> INSERT, UPDATE 는 성능이 향상 합니다. ( table이 작아지기 때문이죠!)

37. 반정규화

- 반정규화는 데이터 무결성 해칩니다!

1) 대상분석 단계

- 먼저 대상을 분석합니다. 대범한 통조림! 을 기억하세요! (대량범위, 범위 처리 빈도수, 통계 처리 여부)

- 를 거치고 반정규화를 들어가는 것이 아니라  2) 응클인뷰 단계를 거칩니다.

응클인뷰! 를 기억하세요! (응용시스템 변경, 클러스트링, 인덱스, 뷰 테이블 처리)

 

이런 것들을 다 해보고 그래도 안되면 이제, 반정규화를 해야 합니다.

 

반정규화의 종류에는 테속관 을 기억하세요! 

 

<테이블 반정규화> 

: 병합( 1:1 , 1: N , 슈퍼/서브) => (병합에 슈퍼/서브 있는 것 기억하세요!)

: 분할 => 수직 분할, 수평 분할

: 추가( 이력테이블, 부분테이블, 통계 테이블, 중복 테이블..) => (추가는 이 부분에 통증있어요~로 외워봅시다!)

 

<속성 반정규화>

: 파생 컬럼, 오류 컬럼(임시컬럼 만들어줌), 이력 컬럼 추가, PK -> 속성 편입, 중복 속성,... => 파오리P중으로 암기하세요!

 

<관계 반정규화>

: 중복 관계 추가

38. 데이터에 따른 성능

- row migration

: 최초로 저장된 블락에 프리스페이스가 없어서 새로운 블락을 할당받아서 그곳으로 옮긴뒤 수정하는 것을 마이그레이션이 일어난다고 합니다.

- row chaining

: 로우 체이닝은 하나의 값이 여러블락에 거쳐 저장될때를 말합니다.

저장엔 문제가없지만 SELECT 할 때 한블럭만 읽어도 될걸 여러블락을 읽어야하므로 성능이 떨어집니다.

한 번 예시를 찾아보세요!

 

* partitioning 도 보고 갑시다!

- list partitioning(목록 분할): 값 목록에 파티션 할당

- range partitioning(범위 분할): 분할 키 값이 범위 내에 있는지 여부로 구분 => 관리가 쉽습니다, 가장 많이 쓰입니다. 

- hash partioning(해시 분할): 해시 함수의 값에 따라 파티션 포함 여부 결정 => 관리가 정말 어렵습니다.

39. 슈퍼/서브 타입

용량이 작은 경우 => one to one 트랜잭션이 개별로 들어갑니다. (Identity)

용량이 큰 경우

• Plus 타입 : 공통/차이점에 따라서 별개로 트랜잭션이 들어옵니다. (Roll down)

• Single 타입 : 전체 통합적으로 트랜잭션이 들어갑니다. (Roll up)

(ex) 시험문제에서는 다음 중 그림을 보여주고 이것을 언제쓰냐~ 혹은 각 특징을 주고 어떤 Type인지 물어보는 문제가 출제 될 수 있습니다!

40. 분산 데이터 베이스 

- 투명성을 만족해야 합니다. (투명성에 대해서는 잘 안 물어볼 것 같습니다.)

- 분산 데이터 베이스의 정의 정도 알고가시죠

=> 여러 개의 서버를 뜯어놓은 개념으로 (반정규화와 유사합니다.) => 데이터 무결성을 해칩니다!

41. 조인 수행원리

- 정말 중요한 문제이죠! 기출로 이러한 유형에 대해서 다루고 가주세요!

1) Natural Join

: 랜덤 엑세스, 대용량 sort 작업시 유리합니다.

: 중첩된 반복문과 유사한 방식으로 조인을 수행합니다.

2) Sort Merge Join

: join키를 기준으로 정렬합니다. (등가 조인/ 비등가 조인)

3) Hash Join

: 등가 조인만 씁니다. (only 등가!)

: 선행 테이블 크기가 작습니다.

: hash 처리를 해야해서 별도 공간이 필요합니다.(데이터를 별도로 잡아먹습니다)

 

꼭 공부하고 가세요! (https://eehoeskrap.tistory.com/84) 이 블로그에 잘 정리 되어 있었습니다!

42. optimizer

- CBO (cost based optimizting)

   : "경로"를 다 짜봤을 때 제일 경제적인 것

   :  RBO에 비해 사용자의 의도대로 하기 힘듭니다. 업무중에 통계 수집은 절대 하면 안됩니다.

- RBO (rule based optimizing)

   : "규칙"에 기반

   : 단점 => 상황에 따라서는 인덱스를 사용하지 않는것이 성능에 이득이 될 수 있지만 RBO는 그런 상황에 대처하지 못합니다. 인덱스가 존재하면 무조건 사용합니다.

43. 인덱스

Q) 인덱스 언제 사용하지 않나요?

=> 부정형, LIKE 함수, 묵시적 형변환 일 때 안 씁니다!

 

Q) 인덱스 사용시 성능이 안 좋아지는 경우가 있나요? ( 느려지는 경우를 말합니다!)

=> insert, update, delete 와 같은 DML들의 성능이 저하됩니다!

(why? 책갈피(목차)를 만들어놨는데, 계속 페이지가 조작되면, 계속 목차를 새로 바꿔야 하기 때문입니다!)

 

44. 실행계획

- 실행순서 문제는 무조건 1문제 출제 됩니다.

(ex) 다음과 같은 순서일 때, 올바른 실행 순서는 무엇일까요?

blablablabla	# 1
		blablablabla	# 2
    			blablablabla	# 3
   	 	blablablabla	# 4
    			blablablabla	# 5

=> [ 3 -> 2 -> 5 -> 4 -> 1 ]

같은 레벨이면 뭉텅이로 생각하면 편합니다!

가장 바깥쪽 실행문이 가장 뒤입니다!

 

이상으로, SQLD 자격증을 위한 최소한의 정리를 해봤습니다.

위 내용을 보고 기출로 공부하시면서 다듬으신다면, 충분히 합격하실 수 있으실 겁니다!

긴 내용 읽어주셔서 감사합니다 ^ __ ^