SQL 자격 검정 실전 문제 풀이 2 (Feat. 주황이)

데이터 모델링의 이해 (11~20)

엔터티 분류

발생시점에 따른 분류

• 기본엔터티 - 그 업무에 원래 존재하는 정보로서 다른 엔터티와 관계에 의해 생성되지 않고 독립적으로 생성 가능하고 자신은 타 엔터티의 부모의 역할을 하게 됩니다. 자신의 고유한 주식별자를 가지게 됩니다. (사원, 부서, 고객, 상품, 자재)
• 중심엔터티 - 기본엔터티로부터 발생되고 그 업무에 있어서 중심적인 역할을 합니다. 데이터의 양이 많이 발생되고 다른 엔터티와의 관계를 통해 많은 행위 엔터티를 생성합니다. (계약, 사고, 예금원장, 청구, 주문, 매출)
• 행위엔터티 - 두 개 이상의 부모엔터티로부터 발생되고 자주 내용이 바뀌거나 데이터량이 증가됩니다. 분석 초기 단계에서는 잘 드러나지 않으며 상세 설계 단계나 프로세스와 상관 모델링을 진행하면서 도출될 수 있습니다.

 

행위에 따른 분류

• 유형 엔터티 - 물리적인 형태가 있고 안정적이며 지속적으로 활용되는 엔터티로 업무로부터 엔터티를 구분하기가 가장 용이합니다. (사원, 물품, 강사)
• 개념 엔터티 - 물리적인 형태는 존재하지 않고 관리해야 할 개념적 정보로 구문이 되는 엔터티 (조직, 보험 상품)
• 사건 엔터티 - 업무를 수행함에 따라 발생되는 엔터티로서 비교적 발생량이 많으며 각종 통계자료에 이용될 수 있습니다. (주문, 청구 )

 

엔터티 명명 기준

• 가능하면 현업업무에서 사용하는 용어를 사용
• 가능하면 약어를 사용하지 않는다
• 단수명사 사용
• 모든 엔터티를 통틀어서 유일한 이름 부여
• 엔터티 생성 의미대로 이름 부여

 

속성

인스턴스에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위

 

분류

• 기본 속성 - 업무로부터 추출한 모든 속성
• 설계 속성 - 업무상 필요한 데이터 이외에 데이터 모델링을 위해, 업무를 규칙화하기 위해 속성을 새로 만들거나 변형하여 정의하는 속성
• 파생 속성 - 다른 속성에 영향을 받아 발생하는 속성으로서 보통 계산된 값들이 이에 해당됩니다.

 

명명 기준

• 약어의 사용은 가급적 제한
• 서술식 용어를 사용하지 않는다
• 복합 명사를 사용하여 구체적으로 명명하여 전체 데이터모델에서 유일성을 확보 (반정규화, 통합 등의 작업을 할 때 혼란을 방지)
• 해당 업무에서 자주 사용하는 이름을 이용

 

기타

• 도메인 - 각 속성이 가질 수 있는 데이터 타입과 크기 제약사항을 말합니다.
• 데이터모델링에서는 존재적 관계와 행위에 의한 관계를 구분하는 표기법이 없으며, UML에서는 연관 관계와 의존 관계에 대해 다른 표기법을 가지고 표현하게 되어 있습니다.

 

 

 

데이터 모델과 성능 (41~50)

반정규화 기법

테이블의 반정규화

기법 분류	기법	내용
테이블 병합	1:1 관계 테이블 병합	1:1 관계를 통합하여 성능 향상
	1:M 관계 테이블 병합	1:M 관계를 통합하여 성능 향상
	슈퍼/서브타입 테이블 병합	슈퍼/서브 관계를 통합하여 성능 향상
테이블 분할	수직분할	칼럼 단위의 테이블을 디스크 I/O를 분산처리 하기 위해 테이블을 1:1로 분리하여 성능향상(트랜잭션의 처리되는 유형 파악이 선행되어야함)
	수평분할	로우 단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O 및 데이터 접근의 효율성을 높여 성능을 향상하기 위해 로우 단위로 테이블을 쪼갬 (관계가 없음)
테이블 추가	중복테이블 추가	다른 업무이거나 서버가 다른 경우 동일한 테이블 구조를 중복하여 원격 조인을 제거하여 성능을 향상
	통계테이블 추가	SUM, AVG 등을 미리 수행하여 계산해 둠으로써 조회 시 성능을 향상
	이력테이블 추가	이력테이블 중에서 마스터 테이블에 존재하는 레코드를 중복하여 이력테이블에 존재하는 방법
	부분테이블 추가	하나의 테이블의 전체 컬럼 중 자주 이용하는 집중화된 컬럼들이 있을 때 디스크 I/O를 줄이기 위해 해당 컬럼들을 모아놓은 별도의 테이블

 

 

컬럼 반정규화

기법	내용
중복컬럼 추가	조인에 의해 처리할 성능저하를 예방하기 위해 즉, 조인을 감소시키기 위해 중복된 컬럼을 위치
파생컬럼 추가	트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해 미리 값을 계산하여 컬럼에 보관. (Derived Column)
이력테이블 컬럼 추가	대량의 이력데이터를 처리할 때 불특정 날 조회나 최근 값을 조회할 때 나타날 수 있는 성능 저하를 예방하기 위해 이력 테이블에 기능성 컬럼(최근값 여부, 시작과 종료일자 등)을 추가
PK에 의한 컬럼 추가	복합의미를 갖는 PK를 단일 속성으로 구성할 경우 발생. 단일 PK안에서 특정 값을 별도로 조회하는 경우 성능저하가 발생할 수 있습니다. 이 때 이미 PK 안에 데이터가 존재하지만 성능향상을 위해 일반 속성으로 포함하는 방법이 PK에 의한 칼럼 추가 입니다.
응용시스템 오작동을 위한 컬럼 추가	업무적으로는 의미가 없지만 사용자가 데이터처리를 하다가 잘못 처리하여 원래 값으로 복구하기를 원하는 경우 이전 데이터를 임시적으로 중복하여 보관하는 기법. 컬럼으로 이것을 보관하는 방법은 오작동 처리를 위한 임시적인 기법이지만 이것을 이력 데이터 모델로 풀어내면 정상적인 데이터 모델의 기법이 될 수 있습니다.

 

 

관계 반정규화

기법	내용
중복 관계 추가	데이터를 처리하기 위한 여러 경로를 거쳐 조인이 가능하지만 이 때 발생할 수 있는 성능 저하를 예방하기 위해 추가적인 관계를 맺는 방법이 관계의 반정규화입니다.

 

 

반정규화 대상을 처리하는 다른 방법

• 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 뷰(VIEW)를 사용하면 해결할 수 있습니다.
• 대량의 데이터 처리나 부분 처리에 의해 성능이 저하되는 경우에 클러스터링을 적용하거나 인덱스를 조정함으로써 성능을 향상시킬 수 있습니다.
• 대량의 데이터는 PK의 성격에 따라 부분적인 테이블로 분리할 수 있습니다. 파티셔닝 기법이 적용되어 성능저하를 방지할 수 있습니다.
• 응용 어플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킬 수 있습니다.

 

슈퍼/서브 타입 데이터 모델

Extended ER 모델이라고 부릅니다.

공통의 부분을 슈퍼타입으로 모델링, 공통으로부터 상속받아 다른 엔터티와 차이가 있는 속성에 대해서는 별도의 서브엔터티로 구분하여 업무의 모습을 정확하게 표현하면서, 물리적인 모델로 변환을 할 때 선택의 폭을 넓힐 수 있습니다.

 

슈퍼/서브 타입 데이터 모델의 변환 기술

• 개별로 발생되는 트랜잭션에 대해서는 개별 테이블로 구성합니다.
• 슈퍼타입+서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입 테이블로 구성합니다.
• 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성합니다.

 

기타

• 인덱스는 값의 범위에 따라 일정하게 정렬이 되어 있으므로 상수값으로 EQUAL 조건으로 조회되는 컬럼이 가장 앞으로 나오고 범위 조회하는 유형의 컬럼이 그 다음에 오도록 하는 것이 인덱스 엑세스 범위를 좁힐 수 있는 가장 좋은 방법이 됩니다.

 

 

 

SQL 기본(11~20)

Delete/Modify Action

• Cascade - Master 삭제 시 Child 같이 삭제
• Set Null - Master 삭제 시 Child 해당 필드 Null
• Set Default - Master 삭제 시 Child 해당 필드 Default 값으로 설정
• Restrict - Chlid 테이블에 PK 값이 없는 경우만 Master 삭제 허용
• No Action - 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

 

Insert Action

• Automatic - Master 테이블에 PK가 없는 경우 Master PK를 생성한 후 Child 입력
• Set Null - Master 테이블에 PK가 없는 경우 Child 외부키를 Null 값으로 처리
• Set Default - Master 테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 처리
• Dependent - Master 테이블에 PK가 존재할 때만 Child 입력 허용
• No Action - 참조무결성을 위반하는 입력 액션을 취하지 않음

 

기타

• 테이블명과 컬럼 명은 반드시 문자로 시작해야 합니다. 사용되는 글자는 A-Z, a-z, 0-9, _, $, #만 허용
• 외래키 값은 NULL 값을 가질 수 있고, 한 테이블당 여러 개가 존재할 수 있습니다.

 

 

 

SQL 활용 (75~84)

집합 연산자

집합 연산자	연산자의 의미
UNION	여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다
UNION ALL	여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시됩니다. (단순히 결과만 합쳐놓은 것) 컬럼 ALIAS는 첫 번째 SQL 모듈 기준으로 표시, 정렬 기준은 마지막 SQL 모듈에 표시하면 됩니다
INTERSECT	여러 개의 SQL문의 결과에 대한 교집합
EXCEPT	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합

 

 

기타

• ORACLE에서는 OUTER JOIN 구문을 (+) 기호를 사용하여 처리할 수 있으며, 이를 ANSI 문장으로 변경하기 위해서는 Inner쪽 테이블에 조건절을 ON절에 함께 위치시켜야 합니다.

 

 

 

레퍼런스

https://dataonair.or.kr/db-tech-reference/d-guide/sql

https://anodos.tistory.com/entry/4-%EC%86%8D%EC%84%B1%EC%9D%98-%EB%B6%84%EB%A5%98

https://eehoeskrap.tistory.com/57