[SQL 개발자] 1)데이터 모델링의 이해 자격증 시험 요약

 

DBA는 아니어도 개발자로서 기본적인 소양을 갖추기 위해서 한번쯤 공부해도 좋은 SQL 개발자 자격증 시험에 대해 조금 정리를 해보았습니다.  시험과목과 점수는 아래와 같습니다.

 

과목	과목별 세부 항목	문항수	배점
데이터 모델링의 이해	데이터 모델링의 이해	10	20(문항당 2점)
	데이터 모델과 성능
SQL 기본 및 활용	SQL 기본	40	80(문항당 2점) 주관식(8)
	SQL 활용
	SQL 최적화 기본 원리

 

- 검정 시간 90분(1시간 30분)

- 합격 기준 : 총점 60점 이상(과목별 40% 미만 과락)

- 50문제 중 30문제 맞추면 합격

- 20문제를 틀리면 합격. 포기할 부분은 포기(?)

 

SQL 활용 부분의 뒤쪽은 일반적인 프로젝트에 잘 사용하지 않는 부분이라 초보자에게는 어려울 수 있을 것 같습니다.  물론 이 책에 모든 쿼리가 다 들어있지 않기 때문에 사용하게 되는 데이터베이스에 맞추어 추가적인 학습을 해야 실전에서는 사용이 가능 하겠지만 기초를 쌓기에는 좋을 것 같습니다. 

 

 

아래 내용은 SQL 개발자 자격증에 대한 서적 중 [이기적 SQL 개발자]를 참고하여 요약을 하였습니다.

 

I. 데이터 모델링

 

1.1 데이터 모델링의 특징

특징	설명
추상화(Abstraction)	현실세계의 특징을 간략하게 표현 한다.
단순화(Simplification)	복잡하지 않도록 누구나 이해할 수 있도록 표현 한다.
명확화(Clarity)	한가지 의미만 가지도록 명확하게 해석 한다.

 

1.2 데이터 모델링 단계

단계	설명
개념적 모델링 (Conceptual Data Modeling)	- 전사적 관점에서 기업의 데이터를 모델링 한다. - 추상화 수준이 가장 높은 수준의 모델링이다. - 업무 측면에서 모델링 한다.
논리적 모델링 (Logical Data Modeling)	- 개념적 모델링을 논리적 모델링으로 변환 - 특정 데이터베이스 모델에 종속 한다. - 식별자를 정의하고 관계, 속성 등을 모두 표현한다. - 정규화를 통해서 재사용성을 높인다.
물리적 모델링 (Physical Modeling)	- 데이터베이스를 실제 구축하고 테이블, 인덱스, 함수등을 생성. - 성능, 보안, 가용성 등을 고려하여 데이터베이스를 구축한다.

 

1.3 데이터 모델링 관점

관점(View)	설명
데이터	- 비즈니스 프로세스에서 사용되는 데이터 - 구조 분석, 정적 분석
프로세스	- 비즈니스 프로세스에서 수행하는 작업을 의미 - 시나리오 분석, 도메인 분석, 동적 분석
데이터와 프로세스	- 프로세스와 데이터 간의 관계를 의미 - CRUD(Create, Read, Update, Delete) 분석

 

1.4 ERD(Entity Relationship Diagram)

ERD 작성 절차

(1) Entity를 도출 하고 그린다.

(2) Entity를 배치 한다.

(3) Entity 간의 관계를 설정 한다.

(4) 관계 명을 서술 한다.

(5) 관계 참여도를 표현한다.

(6) 관계의 필수 여부를 표현한다.

 

 

2. 3층 스키마(3-Level Schema)

 

2.1 3층 스키마란

- 사용자, 설계자, 개발자가 데이터베이스를 보는 관점에 따라 기술한 ANSI 표준

- 데이터베이스의 독립성을 확보하기 위한 방법

- 독립성이 확보되면 대응력 향상, 관리 및 유지보수 비용 절감 등의 장점이 있다.

 

2.2 3층 스키마의 독립성

독립성	설명
논리적 독립성	개념 스키마가 변경 되어도 외부 스키마가 영향을 받지 않는다.
물리적 독립성	내부 스키마가 변경 되어도 개념 스키마가 영향을 받지 않는다.

 

2.3 3층 스키마 구조

 

3층 스키마 구조

구조	설명
외부 스키마 (External Schema)	- 응용 프로그램이 접근하는 데이터베이스를 정의 한다. - 사용자 관점. 업무상 관련이 있는 데이터에 대한 접근 - 관련 데이터베이스의 뷰(View)를 표시
개념 스키마 (Conceptual Schema)	- 설계자 관점. 사용자 전체 집단의 데이터베이스 구조이다. - 전체 데이터베이스 내의 규칙과 구조를 표현 - 통합 데이터베이스 구조
내부 스키마 (Internal Schema)	- 개발자 관점, 데이터베이스의 물리적 저장구조 - 데이터 저장 구조, 레코드 구조, 필드 정의, 인덱스 등을 의미한다.

 

 

3. 엔터티(Entity)

실체, 객체. 실세계에서 개별적으로 인식될 수 있는 것. Table을 의미 한다.

 

3.1 Entity에 대한 정의

변별할수 있는 사물 - Peter Chen (1976) -

데이터베이스 내에서 변별 가능한 객체 - C.J Date (1986) -

정보를 저장할 수 있는 어떤 것 - James Martin (1989) -

정보가 저장될 수 있는 사람, 장소, 물건, 사건 그리고 개념 등 - Thomas Bruce (1992) -

 

3.2 Entity 도출

 

엔터티는 사람, 장소, 물건, 사건, 개념 등의 명사에 해당한다.

엔터티는 업무상 관리가 필요한 관심사에 해당한다.

엔터티는 저장이 되기 위한 어떤 것(Thing)이다.

엔터티는 인스턴스의 집합

 

3.3 Entity 특징

특징	설명
식별자	- Entity는 유일한 식별자가 있어야 한다. (회원 ID, 계좌번호)
인스턴스 집합	- 2개 이상의 인스턴스가 있어야 한다. (고객정보는 2명 이상 있어야 한다.)
속성	- Entity는 반드시 속성을 가지고 있다. (고객 엔터티에는 회원ID, 패스워드, 이름, 주소 등이 있다)
관계	- Entity는 다른 Entity와 최소한 한 개 이상의 관계가 있어야 한다. (고객은 계좌를 개설 한다)
업무	- Entity는 업무에서 관리되어야 하는 집합이다. (고객, 계좌등)

 

3.4 Entity 종류

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

종류	설명
유형 엔터티	- 업무에서 도출되며 지속적으로 사용되는 엔터티, 물리적으로 존재. (고객, 강사, 사원 등)
개념 엔터티	- 물리적 형태가 없이 개념적으로 사용되는 엔터티 (거래소 종목, 코스닥 종목, 생명보험 상품)
사건 엔터티	- 비즈니스 프로세스를 실행하면서 생성되는 엔터티 (주문, 체결, 주문취소, 수수료 청구 등)

 

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

종류	설명
기본 엔터티 (Basic Entity)	- 키 엔터티라고도 한다. - 다른 엔터티로부터 영향을 받지 않고 독립적으로 생성된다. (고객, 상품, 부서 등)
중심 엔터티 (Main Entity)	- 기본 엔터티와 행위 엔터티의 중간에 있다. - 기본 엔터티로 부터 발생되고 행위 엔터티를 생성한다. (계좌, 주문, 취소, 체결 등)
행위 엔터티 (Active Entity)	- 2개 이상의 엔터티로부터 발생된다 (주문 이력, 체결 이력 등)

 

 

 

 

 

4. 속성(Attribute)

 

4.1 속성

1) 사전적 의미

- 사물(事物)의 성질, 특징 또는 본질적인 성질, 그것이 없다면 실체를 생각할 수 없는 것

- 본질적 속성이란 어떤 사물 또는 개념에 없어서는 안 될 징표(徵表)의 전부

- 이 징표는 사물이나 개념이 어떤 것인지를 나타내고 그것을 다른 것과 구별하는 성질

- Table의 column을 의미 한다.

 

2) 데이터 모델링 관점

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

- 업무상 관리하기 위한 최소의 의미 단위

- 의미상 더 이상 분리되지 않는다.

- 엔터티를 설명하고 인스턴스의 구성요소가 된다.

 

4.2 속성의 종류

1) 분해 여부에 따른 속성의 종류

종류	설명
단일 속성	- 하나의 의미로 구성된 것으로 회원ID, 이름 등
복합 속성	- 여러개의 의미가 있는 것으로 주소 등이 있다. - 주소는 시, 군, 동 등으로 분해될 수 있다.
다중값 속성	- 속성에 여러개의 값을 가질 수 있는것으로 상품 리스트 등이 있다. - 다중값 속성은 Entity로 분해 된다.

 

2) 특성에 따른 속성의 종류

종류	설명
기본 속성	- 비즈니스 프로세스에서 도출되는 본래의 속성 (회원ID, 이름, 계좌번호, 주문 일자 등)
설계 속성	- 데이터 모델링 과정에서 발생되는 속성 - 유일한 값을 부여 한다. (상품코드, 지점코드 등)
파생 속성	- 다른 속성에 의해서 만들어지는 속성 (합계, 평균 등)

 

4.3 도메인

 

속성이 가질 수 있는 값의 범위

Attribute	Domain
학점	0.0~4.0 실수
주소	20자리 문자열

 

 

5. 관계(Relationship)

 

5.1 관계

Entity 간의 관련성을 의미하며 존재 관계와 행위 관계로 분류 된다.

 

5.2 관계의 분류

1) 존재 관계

- Entity간의 관계가 존재 상태로 연결 되는 것. (고객이 은행지점에서 회원가입 하면 관리지점이 할당되고 관리 된다.)

 

2) 행위 관계

- Entity간의 관계가 행위로서 연결 되는 것. (계좌를 이용해서 주문을 하면 주문이력이 발생)

 

5.3 관계 차수

 

 

 

1) 1:1(One To One)

종류	설명
완전 1대1	하나의 엔터티에 관계되는 엔터티의 관계가 하나인 경우. 반드시 존재
선택적 1대1	하나의 엔터티에 관계되는 엔터티가 하나 이거나 없을 수도 있다.

 

2) 1:M (One To Many)

- 하나의 엔터티에 관계된 엔터티에 값이 여러개 있는 관계.

- 고객 엔터티와 구매내역 엔터티의 경우 고객이 많은 구매내역을 가질 수 있다.

 

3) M:N (Many to Many)

- 두개의 엔터티가 서로 여러개의 관계를 가지고 있다.

- 학생과 과목 엔터티의 경우 한 명의 학생이 여러 개의 과목을 수강하거나, 하나의 과목을 여러 학생이 수강하는 경우 M대N 관계가 발생 한다.

- M대N의 관계 조인은 카데시안 곱이 발생 하며 1대N, N대1로 분리 하여야 한다.

 

4) 선택성

구분	설명
필수적 관계	Entity의 속성 하나에 대해 상대 Entity의 속성에 관계가 존재 해야 한다.
선택적 관계	Entity의 속성 하나에 대해 상대 Entity에 관계가 없을 수도 있다.

 

 

6. 엔터티 식별자(Entity Identifier)

 

식별자의 개념

Entity 내에서 Instance 들을 구분할 수 있는 구분자

 

6.1. 주식별자의 특징(기본키, Primary key)

특징	내용
대표성	주식별자는 Entity를 대표할 수 있어야 한다. (예: EMP 테이블 사원번호)
최소성	주식별자를 구성하는 속성의 수는 최소의 수가 되어야 한다.
유일성	주식별자는 Entity에 있는 Instance 들을 유일하게 구분한다.
불변성	주식별자로 지정되면 그 값은 변하지 않아야 한다.
존재성	주식별자는 반드시 데이터 값이 존재해야 한다.

 

 

6.2 데이터베이스 키의 종류

종류	설명
후보키(Candidate Key)	유일성과 최소성을 만족하는 키
기본키(Primary Key)	후보키 중에서 Entity를 대표할 수 있는 키
대체키(Alternate Key)	여러개의 후보키 중에서 기본키를 선정하고 남은 키
슈퍼키(Super Key)	유일성은 만족하지만 최소성을 만족하지 않는 키
외래키(Foregin Key)	관계를 맺고 있는 다른 Entity의 기본 키를 가리킨다.

 

6.3 식별자의 종류

 

1) 식별자의 대표성

종류	설명
주 식별자	- Entity를 대표하는 주 식별자 - 다른 Entity와 참조 관계로 연결될 수 있다.(다른 Entity에 외래 키)
보조 식별자	- 유일성과 최소성을 만족 하지만 대표성을 만족 못하는 식별자.

 

2) 생성 여부

종류	설명
내부 식별자	Entity 내부에서 스스로 만들어 지는 식별자
외부 식별자	다른 Entity와의 관계를 통해서 다른 Entity로부터 받아오는 식별자

 

3) 속성의 수

종류	설명
단일 식별자	하나의 속성으로 구성된 식별자
복합 식별자	둘 이상의 속성으로 구성된 식별자

 

4) 대체 여부

종류	설명
본질 식별자	비즈니스 프로세스에서 생성되는 식별자
인조 식별자	유일성을 만족하는 속성이 없는 경우 인위적으로 생성하는 식별자

 

 

 

II. 데이터 모델과 성능

 

1. 정규화

 

- 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 위한 데이터 분해 과정

- 함수적 종속성을 이용하여 잘못 설계된 스키마를 쪼개어 분해하는 과정으로 하나의 릴레이션에 하나의 함수적 종속성만 존재하도록 정규화 한다.

- 정규화를 통해 추상화가 되면 비즈니스가 변화해도 수정사항이 최소화된다.

- 정규화는 1정규화에서 5정규화까지 존재. 실제 3정규화까지 수행

 

1.1 정규화 절차

정규화 절차	설명
제 1 정규화	- 속성(Attribute)의 원자성 확보 - 기본키(Primary key) 설정
제 2 정규화	- 기본키가 2개 이상의 속성으로 이루어진 경우, 부분 함수 종속성 제거
제 3 정규화	- 기본키를 제외한 컬럼 간 종속성 제거. 즉, 이행 함수 종속성 제거
BCNF	- 기본키를 제외하고 후보키가 있는 경우, 후보키가 기본키를 종속시키면 분해 한다.
제 4 정규화	여러 컬럼이 하나의 컬럼을 종속시킬 때 분해를 통해 다중값 종속성 제거
제 5 정규화	조인에 의해 종속성이 발생하는 경우 분해한다.

 

 

1.2 함수적 종속성(Functional Dependency)

- 데이터나 데이터베이스 구성요소가 다른 구성요소에 영향을 끼치는 현상

- 릴레이션의 어떤 속성 값에 의하여 다른 속성 값이 결정되는 경우 함수적 종속이라한다.

- 함수적 종속 관계는 X -> Y 로 표시하고 “Y는 X에 함수적으로 종속된다”라고 한다.

 

1) 제1정규화

- 기본키를 잡는것

- 하나의 속성이 단일 값을 갖도록 한다.

 

2) 제2정규화

- 부분 함수 종속성을 제거하기 위해 분해하는 과정

- 부분 함수 종속성 : 기본키가 2개 이상의 column으로 이루어진 경우에 발생.

- 종속 관계에 데이터의 중복 또는 불일치를 해결.

 

 

3) 제3정규화

- 이행 함수 종속성을 제거하기 위해 분해하는 과정.

- 이행 함수 종속성 : 기본키를 제외하고 column간에 종속성이 발생하는것.

- 주식별자가 아닌 속성들 중에 종속관계가 발생하면 분해하는 작업.

- 제3정규화는 제1정규화, 제2정규화를 수행하고 작업 한다.

 

4) BCNF(Boyce-Codd Normal Form)

복수의 후ㅗㅂ키가 있고, 후보키들이 복합 속성이어야 하고 서로 중첩되는 경우.

 

2. 정규화와 성능

 

2.1 정규화의 문제점

- 정규화는 데이터 조회(SELECT) 시 조인(JOIN)을 유발하여 자원(CUP와 메모리)을 많이 사용한다.

- 조회를 프로그램으로 구현하는 경우 중첩된 루프(Nested Loop)를 사용하게 된다.

- 정규화의 문제점 해결을 위해서 반정규화를 통해 테이블 하나에 값을 저장 하여 해결한다.

- 반정규화는 데이터를 중복시키기 때문에 또다른 문제점 발생.

- 정규화는 테이블을 분해하여 입출력 데이터의 양을 줄여서 성능을 향상 시켜야 한다.

 

3. 반정규화(De-Normalization)

- 데이터베이스의 성능 향상을 위해 데이터 중복을 허용하여 조인(Join)을 줄이는 방법.

- 반정규화는 조회(SELECT)의 속도를 향상 하지만 데이터 모델의 유연성은 낮아진다.

 

3.1 반정규화를 하는 경우

- 정규화를 통하여 수행 속도가 느려진 경우

- 다량의 범위를 자주 처리해야 하는 경우

- 특정 범위의 데이터만 자주 처리하는 경우

- 요약/집계 정보가 자주 요구되는 경우

 

3.2 반정규화 절차

반정규화 절차	설명
대상 조사 및 검토	데이터 처리 범위, 통계성 등을 확인해서 반정규화 대상을 조사한다.
다른 방법 검토	- 반정규화를 수행하기 전에 다른 방법이 있는지 검토한다. - 예를 들어 클러스터링, 뷰, 인덱스 튜닝, 응용 프로그램, 파티션 등을 검토한다.
반정규화 수행	테이블, 속성, 관계 등을 반정규화한다.

 

3.3 반정규화 기법

1) 계산된 칼럼 추가

배치 프로그램으로 총판매액, 평균잔고, 계좌평가 등을 미리 계산하고, 그 결과를 특정 칼럼에 추가한다.

 

2) 테이블 수직 분할

하나의 테이블의 두 개 이상의 테이블로 분할한다. 즉, 칼럼을 분할하여 새로운 테이블을 만드는 것이다.

 

3) 테이블 수평 분할

하나의 테이블에 있는 값을 기준으로 테이블을 분할하는 방법이다.

 

4) 테이블 병합 : 조인(Join) 발생 차단

- 1:1 관계의 테이블을 하나의 테이블로 병합해서 성능을 향상시킨다.

- 1:N 관계의 테이블을 병합하여 성능을 향상시킨다. 하지만 많은 양의 데이터 중복이 발생한다.

- 슈퍼 타입과 서브 타입 관계가 발생하면 테이블을 통합하여 성능을 향상시킨다.

 

5) 슈퍼 타입 및 서브 타입 변환 방법

변환 방법	설명
OneToOne Type	- 슈퍼 타입과 서브 타입을 개별 테이블로 도출한다. - 테이블의 수가 많아서 조인이 많이 발생하고 관리하기 어렵다.
Plus Type	- 슈퍼 타입과 서브 타입 테이블로 도출한다. - 조인이 발생하고 관리가 어렵다.
Single Type	- 슈퍼 타입과 서브 타입을 하나의 테이블로 도출한다. - 조인 성능이 좋고 관리가 편하지만, 입출력 성능이 나쁘다.

 

 

4. 분산 데이터베이스

 

4.1 분산 데이터베이스

- 중앙 집중형 데이터베이스 : 한 대의 물리적 시스템에 데이터베이스 관리 시스템을 설치하고 여러 명의 사용자가 데이터베이스 관리 시스템에 접속하여 데이터베이스를 사용하는 구조.

- 분산 데이터 베이스 : 물리적으로 떨어진 데이터베이스에 네트워크로 연결하여 단일 데이터베이스 이미지를 보여주고 분산된 작업 처리를 수행하는 데이터베이스.

- 투명성 제공 : 분산 데이터베이스를 사용하는 고객이, 시스템이 분산되어있는지 인식하지 못하면서, 자신만의 데이터베이스를 사용하는 것 처럼 사용

 

4.2 투명성 종류

투명성	설명
분할 투명성	고객은 하나의 논리적 릴레이션이 여러 단편으로 분할되어 각 단편의 사본이 여러 시스템에 저장되어있음을 인식할 필요가 없다.
위치 투명성	- 고객이 사용하려는 데이터의 저장 장소를 명시할 필요가 없다. - 고객은 데이터가 어느 위치에 있더라도 동일한 명령을 사용하여 데이터에 접근할 수 있어야 한다.
지역 사상 투명성	지역 DBMS와 물적 데이터베이스 사이의 사상이 보장됨에 따라 각 지역 시스템 이름과 무관한 이름이 사용 가능하다.
중복 투명성	데이터베이스 객체가 여러 시스템에 중복되어 존재함에도 고객과는 무관하게 데이터의 일관성이 유지된다.
장애 투명성	데이터베이스가 분산되어 있는 각 지역의 시스템이나 통신망에 이상이 발생해도, 데이터의 무결성은 보장된다.
병행 투명성	여러 고객의 응용 프로그램이 동시에 분산 데이터베이스에 대한 트랜잭션을 수행하는 경우에도 결과에 이상이 없다.

 

4.3 분산 데이터베이스의 설계 방식

1) 상향식 설계 방식

지역 스키마 작성 후 향후 전역 스키마를 작성하여 분산 데이터베이스를 구축한다.

 

2) 하향식 설계 방식

전역 스키마 작성 후 해당 지역 사상 스키마를 작성하여 분산 데이터베이스를 구축한다.

 

3) 분산 데이터베이스 장점과 단점

장점	단점
- 데이터베이스 신뢰성과 가용성이 높다 - 분산 데이터베이스가 병렬처리를 수행하기 때문에 빠른 응답이 가능하다. - 분산 데이터베이스를 추가하여 시스템 용량 확장이 쉽다.	- 데이터베이스가 여러 네트워크를 통해서 분리되어 있기 때문에 관리와 통제가 어렵다. - 보안관리가 어렵다. - 데이터 무결성 관리가 어렵다. - 데이터베이스 설계가 복잡하다.

 

 

 - copy coding -