SQLD 38회 기출 문제 풀이

# 두 엔터티 간의 관계차수

 

# 성능 데이터 모델링 고려사항

• 정규화를 수행하여 데이터베이스 모델의 유연성 확보
• 데이터베이스의 전체 용량, 월간, 연간 증감율을 예측
• 애플리케이션의 트랜잭션 유형(CRUD) 파악
• 합계 및 정산 등을 수행하는 반정규화 수행(성능향상을 위한 튜닝)
• 기본키, 외래키, 수퍼타입과 서브타입 등을 조정
• 성능관점에서 데이터 모델을 검증 및 확인

 

# 3가지 모델링

• 개념적 모델링 : 개체들 사이의 관계 ER다이어그램 만드는 과정
• 논리적 모델링 : ER다이어그램을 사용하려 관계 스키마 모델을 만드는 과정
• 물리적 모델링 : 관계 스키마 모델의 물리적 구조를 정의하고 구현하는 과정

 

# 데이터 모델링 과정 (2~4 설계 단계)

  1. 요구사항 수집 및 분석 : 현실 세계의 대상 및 사용자 요구 등을 정리&분석 

        - 사용자 식별

        - 데이터 베이스 용도 식별

       - 사용자 요구 사항 수집 및 명세

  2. 개념적 모델링 : 중요 개념을 구분 (핵심 엔터티 도출 , ERD 작성)

  3. 논리적 모델링 : 각 개념을 구체화

      - 정규화, 반정규화 등

      - 상세 속성 정의

      - ERD-RDB 모델 사상

  4. 물리적 모델링 : 데이터베이스 생성 계획에 따라 개체, 인덱스 등을 생성. 물리적 단계

      - DB 개체 정의

      - 테이블 및 인덱스 설계

  5. 데이터베이스 구현

 

# 테이블 정규화

• 정보의 갱신 이상이 생기지 않도록 한다.
• 함수적 종속성에 따라서 테이블을 분해하는 과정이다.
• 정보의 손실과 중복을 막고 독립성을 향상시킨다.

*** 데이터베이스 보안과 관련있는 것은 뷰!!!

 

# 기본키(PK)와 외래키(FK)

• 기본키 : 엔터티를 식별할 수 있는 대표키. 최소성(NOT NULL)과 유일성(중복 없음)을 만족
• 외래키 : 두 개의 테이블 간에 연결을 설정하는 키. 다른 엔터티의 관계에 포함되는 속성
• 일반속성 : 다른 엔터티의 관계에 포함되지 않는 속성

 

# 엔터티의 특징

• 식별자 : 엔터티는 유일한 식별자가 있어야 한다.
• 인스턴스 집합 : 2개 이상의 인스턴스가 있어야 한다.
• 속성 : 엔터티는 반드시 속성을 가지고 있다.
• 관계 : 엔터티는 다른 엔터티와 최소 한 개 이상 관계가 있어야 한다.
• 업무 : 엔터티는 업무에서 관리되어야 하는 집합이다.

 

# 유형과 무형에 따른 엔터티 종류

• 유형 엔터티 : 업무에서 도출되며 지속적으로 사용되는 엔터티(물리적 형태 O)
• 개념 엔터티 : 개념적으로 사용되는 엔터티(물리적 형태 X)
• 사건 엔터티 : 비지니스 프로세스를 실행하면서 생성되는 엔터티

 

# 발생시점에 따른 엔터티 종류

• 기본 엔터티 : 다른 엔터티로부터 영향X 독립적으로 생성되는 엔터티(=키 엔터티)
• 중심 엔터티 : 기본엔터티로 부터 발생되고 행위 엔터티를 생성하는 엔터티
• 행위 엔터티 : 2개 이상의 엔터티로부터 발생하는 엔터티 . 지속적으로 정보 추가 및 변경되는 엔터티

 

# SQL문 문제

START WITH : 시작 포인트 지정.  루트 데이터 지정

CONNECT BY 자식데이터 지정 (PRIOR와 함께 사용 부모데이터 지정) 

 

WHERE COL3<>3

START WITH COL3 = 4

CONNECT BY COL1 = PRIOR COL2;

 

  : COL3이 3이랑 같지 않은 행중에 계층형쿼리를 시작할거다. 처음 시작은 COL3=4인 행부터 시작한다. 그다음 테이블을 보며 COL1이 부모, COL2가 자식인걸 알수 있다. (COL1이 COL2를 다 포함하고 있다.) 즉, CONNECT BY 부모 = PRIOR 자식;이므로 자식 -> 부모 순방향으로 흐른다. COL2(자식 칼럼)의 값이 다음 COL1(부모 칼럼)의 값이 되는 애를 다음 행에 써준다.

 

# 조인의 종류

   1. NESTED LOOP JOIN

      - 좁은 범위에서 사용

      - 유리순차적으로 처리하며 RANDON ACCESS 위주

      - 후행 테이블에는 조인을 위한 인덱스가 생성되어 있어야한다.

      - 실행속도 = 선행 테이블 사이즈 * 후행테이블 접근 횟수

   2. SINGLE LOOP JOIN

      - 후행 테이블의 조인 속성에 인덱스가 존재할 경우 사용

   3. SORT MERGE JOIN

      - 해당 테이블에 인덱스가 없을때 수행

      - 테이블을 정렬한 후에 정렬된 테이블을 병합하면서 조인을 실행

      - 조인 연결고리의 비교연산자가 범위 연산인 경우 NESTED LOOP 조인보다 유리

      - 두 결과집합의 크기 차이가 많이 나는 경우에는 비효율적

   4. HASH JOIN

      - 해시 함수를 사용하여 두 테이블의 자료를 결합하는 조인방식

      - NESTED LOOP JOIN과 SORT MERGE JOIN의 문제점을 해결

      - 정렬작업이 없어 정렬이 부담되는 대량 배치 작업에 유리

      - 대용량 데이터 처리는 상당히 큰 HASH AREA를 필요해 메모리의 지나친 사용으로 오버헤드 발생 가능성 O

 

# SQL 명령어

 

# NULLIF(A,B)

  : A랑 B의 값이 같으면 NULL출력 아니면 A값 출력!

 

# COUNT()

  1) COUNT(3), COUNT(1) 등등.. : NULL값도 포함해서 갯수 카운트

  2) COUNT(*) : NULL값도 포함해서 갯수 카운트, 만약 조건이 거짓이라 만족하는 행이 하나도 없을 때 COUNT(*)를 쓰면

       NULL이 아니라 0을 반환한다! 만족하는 행이 0개라는 뜻!

  3) COUNT(칼럼명) : NULL값은 제외하고 갯수 카운트

 

# GROUPING SETS 

  1) GROUPING SETS (칼럼1,칼럼2)

      - NULL값도 포함해서 갯수 카운트 각 칼럼을  UNION ALL한 것과 같음

      - 칼럼1 그룹핑하고 그밑에 칼럼2 그룹핑한 결과 합집합 하는 것

 

2) GROUPING SETS ((칼럼1,칼럼2))

      - 합쳐서 세는거 GROUP BY 칼럼1, 칼럼2와 같은 결과

      - 칼럼1과 칼럼2 모두 합쳐서 그룹핑한 것

 

3) GROUPING SETS((칼럼1,칼럼2),(칼럼1,칼럼3))

      - 위의 두개 합친거. 칼럼1과 칼럼2 합쳐서 그룹핑한 결과와 칼럼1과 칼럼3 합쳐서 그룹핑한 결과를 UNION ALL하는 결과와 같다.

 

* 빈괄호를 포함해주면 합계가 나온다!!!!

 

4) GROUPING SETS((칼럼1,칼럼2),())  

      - 그룹핑 한 결과와 그걸 다 더한 총계가 표시된다.

 

5) GROUPING SETS((칼럼1,칼럼2),(칼럼1,칼럼3),())

      - 다더한 총계가 나오는데 이 총계가 모든 갯수를 다 더한게 아니라 그냥 총 갯수일뿐임. 

 

6) GROUPING SETS(칼럼1,(칼럼1,칼럼2),())

      - 이러면 칼럼1 기준으로 나눴을 때 개수와 칼럼1,2로 나눴을때 각각 세부적인 개수 정보와 빈괄호를 통해 총 개수까지 모두 알 수 있다.

 

# PL/SQL

• PL/SQL은 절차형 언어이다.
• 테이블을 생성할 수 있다.(보통 임시테이블로 사용된다.)
• 조건문은 IF~THEN~ELSE IF~ END IF와 CASE ~ WHEN을 사용한다.
• NAME이라는 변수에 'aaa'를 대입할 때 ":="을 이용한다.

 

# INDEX 구문

• INDEX 생성 : CREATE INDEX [인덱스 명] ON [테이블 명][칼럼 명]
• INDEX 삭제 : DROP INDEX [인덱스 명] ON [테이블 명]
• INDEX 수정 : 인덱스 삭제 후 다시 만들어 줘야 한다.
• INDEX 조회 : SELECT 테이블명, 인덱스명, 컬럼명 FROM ALL_IND_COLUMNS WHERE TABLE_NAME=테이블명

 

# INDEX

• INDEX는 연산을 하게 되면 변형이 일어나기 때문에 연산은 하지 않는 것이 좋다.
• 글로벌 인덱스 : 파티션에서의 인덱스는 파티션 키에 대해서 인덱스를 생성한 것을 말한다.

 

# 분산 데이터베이스 장단점

  1. 장점

      - 데이터베이스 신뢰성과 가용성이 높다.

      - 분산 데이터베이스가 병렬 처리를 수행하기 때문에 빠른 응답이 가능하다.

      - 분산 데이터베이스를 추가하여 시스템 용량 확장이 쉽다.

  2. 단점

      - 데이터베이스가 여러 네트워크를 통해서 분리되어 있기 때문에 관리와 통제가 어렵다

      - 보안관리가 어렵다.

      - 데이터 무결성 관리가 어렵다.

      - 데이터베이스 설계가 복잡하다.

 

# ROWNUM

  : ROWNUM은 논리적인 숫자이므로 ROWNUM=2와 같은 조회가 불가능하다. 1번과의 같다 다르다라던가 1번부터 크기비교인 경우만 가능하다. 1번을 뛰어넘고 2번부터! 이런게 안됨!!

 

# 연산자 우선순위

 

# 순수 관계 연산자

 : 관계형 데이터베이스에서 적용할 수 있도록 개발한 관계 연산자 

• SELECT
• PROJECT
• JOIN
• DIVISION

*** DELETE는 포함되지 않는다!!!

 

# 순위 연산자

• RANK() : 중복값은 중복등수 등수 안건너뜀 (1위,1위,2위,3위) 
• DENSE_RANK() : 중복값은 중복등수, 등수 안건너뜀 (1위,1위,3위,4위)
• ROW_NUMBER() : 중복값이 있어도 고유 등수 부여(1위,2위,3위,4위)