[DB] 그룹 함수, 데이터 모델링

그룹 함수

그룹 함수 1

그룹 함수 2

그룹 함수 3

그룹 함수 4

GROUP BY 절

SELECT 문에서 GROUP BY를 사용하면, 데이터베이스는 쿼리된 테이블의 행을 그룹으로 묶는다.
데이터베이스는 선택 목록의 집계 함수를 각 행 그룹에 적용하고, 각 그룹에 대해 단일 결과 행을 반환한다.
GROUP BY 절을 생략하면, 데이터베이스는 선택 목록의 집계 함수를 쿼리된 테이블의 모든 행에 적용한다.
SELECT 절의 모든 요소는 GROUP BY 절의 표현식, 집계 함수를 포함하는 표현식, 또는 상수만 가능하다.

GROUP BY 예시 1

GROUP BY 예시 2

GROUP BY 예시 3 GROUP BY 예시 4 GROUP BY 예시 5

HAVING 절

GROUP BY한 결과에 조건을 추가할 경우 HAVING 절을 사용한다.
쿼리의 실행 순서를 보면 WHERE 절이 GROUP BY 절보다 먼저 실행되기 때문에, 집계(aggregate) 조건은 HAVING 절에 작성한다.
예: 부서별 평균 급여가 7000 이상인 부서 번호와 평균 급여 조회

HAVING 예시 1

HAVING 예시 2

HAVING 예시 3

SET (집합 연산자)

모든 집합 연산자는 동일한 우선순위를 갖는다.
SELECT 절에 있는 컬럼의 개수와 타입이 일치해야 한다.

집합 연산자 1

집합 연산자 2

집합 연산자 3

집합 연산자 4

집합 연산자 5

데이터 모델링 (Data Modeling)

정보화 시스템을 구축하기 위해 어떤 데이터가 존재하는지, 또는 업무에 필요한 정보가 무엇인지 분석하는 방법
관계형 데이터베이스는 이 표(table)의 개념을 사용해 데이터를 구성한다.

데이터 모델링 1

데이터 모델링 2

데이터 모델링 3

데이터 모델링 4

데이터 모델링 5

데이터 모델링 6

개념적 데이터베이스 모델링

업무 분석 단계에서 얻어진 내용을 토대로, 먼저 Entity를 추출하고 엔티티 내에 속성을 구성하며 엔티티 간의 관계를 정의해서 ER 다이어그램을 만드는 과정이다.

사용자 부문의 처리 현상을 분석한다.
중요 실체와 관계를 파악하여 ERD를 작성한다.
실체에 대한 상세 정의를 한다.
식별자를 정의하고, 식별자 업무 규칙을 정한다.
실체별로 속성을 상세화한다.
필요한 속성 및 영역을 상세 정의한다.
속성에 대한 업무 규칙을 정의한다.
각 단계를 마친 후 사용자와 함께 모델을 검토한다.

요약하면, 개체를 뽑아내고 그 개체에서 어떤 속성을 뽑아낼지, 그리고 어떤 관계가 있는지를 파악하는 과정이다.

개념적 모델링 예시

개체 (Entity)

사용자와 관계가 있는 주요 객체로서, 업무 수행을 위해 데이터로 관리되어야 하는 사람, 사물, 장소, 사건 등을 개체라고 한다.
영속적으로 존재하는 것을 말한다. 예) 학생, 직원

Entity를 찾는 법

영속적으로 존재하는 것
새로 식별이 가능한 데이터 요소를 가짐
Entity는 반드시 속성을 가져야 함
명사적 표현

속성 (Attribute)

저장할 필요가 있는 실체에 대한 정보
개체(Entity)의 성질, 분류, 수량, 상태, 특성 등을 나타내는 세부 사항
개체에 포함되는 속성의 숫자는 10개 내외로 하는 것이 바람직함
최종 DB 모델링 단계를 통해 테이블의 컬럼으로 활용

예) 학생: 학번, 이름, 주민번호, 주소

속성의 유형

기초 속성: 원래 갖고 있는 속성으로, 현업에서 기본으로 사용되는 속성
추출 속성: 기초 속성으로부터 계산(가공)에 의해 얻어질 수 있는 속성
설계 속성: 실제로 존재하지 않으나, 시스템의 효율성을 도모하기 위해 설계자가 임의로 부여하는 속성

식별자

한 개체에서 인스턴스를 유일하게 구분할 수 있는 단일 속성 또는 속성 그룹을 말한다.

후보키 (Candidate Key): 개체 내에서 각각의 인스턴스를 구분할 수 있는 속성으로, 기본키가 될 수 있는 후보 속성
기본키 (Primary Key): 개체에서 각 인스턴스를 유일하게 식별하는 데 가장 적합한 키. 설정 시 고려할 사항으로는 해당 실체를 대표할 수 있을 것, 업무적으로 활용도가 높을 것, 길이가 짧을 것 등이 있다.
대체키 (Alternate Key): 후보키 중 기본키로 선정되지 않은 속성
복합키 (Composite Key): 하나의 속성으로 기본키가 될 수 없는 경우, 둘 이상의 컬럼을 묶어서 식별자로 정의하는 경우
대리키 (Surrogate Key): 식별자가 너무 길거나 여러 개의 속성으로 구성되어 있는 경우에 인위적으로 추가하는 식별자

관계 (Relationship)

정의: 두 개체 간의 업무적인 연관성 또는 관련 사실
관계 분석
1. 각 개체 간에 특정한 존재 여부를 결정
2. 현재의 관계뿐 아니라 장래에 사용될 경우도 고려

관계 1

관계 2

관계 3

관계 4

관계 5

관계 6

관계 7

논리적 데이터베이스 모델링

개념적 데이터베이스 모델링 단계에서 정의된 ER-Diagram에 Mapping Rule을 적용하여, 관계형 데이터베이스 이론에 입각한 스키마를 설계하는 단계와, 이를 이용해 필요하면 정규화하는 단계로 구성된다.

키(Key) 정리

기본키 (Primary Key)

후보키 중 선택한 주 키
널(NULL) 값을 가질 수 없다 (NOT NULL)
동일한 값이 중복해서 저장될 수 없다 (UNIQUE)

참조키 / 외래키 (Foreign Key)

관계를 맺는 두 엔티티에서 서로 참조하는 릴레이션의 속성(attribute)으로 지정되는 키

논리적 모델링 1

논리적 모델링 2

처음에는 사원 엔티티를 만들고, 사원번호라는 PK와 번호 등을 넣는다.

논리적 모델링 3

테이블 2개를 만들었다. 부서가 없는 사원도 있으니 0도 포함한다.

논리적 모델링 4

논리적 모델링 5

N:M — 학생도 여러 명, 과목도 여러 개가 가능하다.

논리적 모델링 6

논리적 모델링 7

정규화

속성(Attribute) 간에 존재하는 함수적 종속성을 분석해서, 관계형 스키마를 더 좋은 구조로 정제해 나가는 일련의 과정
데이터의 중복을 제거하고 속성들을 본래의 제자리에 위치시키는 것

정규화 개요

제1 정규화

반복되는 그룹 속성을 제거한 뒤, 기본 테이블의 기본키를 추가해 새로운 테이블을 생성하고, 기존 테이블과 1:N의 관계를 형성하는 과정
반복되는 그룹 속성이란, 같은 성격과 내용의 컬럼이 연속해서 나타나는 컬럼을 말한다.

제1 정규화 1

제1 정규화 2

제2 정규화

복합키(Composite Primary Key)에 전체적으로 의존하지 않는 속성을 제거한다.
제2 정규화의 대상이 되는 테이블은 키가 여러 컬럼으로 구성된 경우다.
복합키의 일부분에 의해 종속되는 것을 부분적 함수 종속 관계라 하며, 이를 제거하는 작업이다.

제2 정규화 1

제2 정규화 2

제3 정규화

기본키에 의존하지 않고 일반 컬럼에 의존하는 컬럼을 제거한다.
이행적 함수 종속 관계를 갖는 컬럼을 제거하는 과정이다.

제3 정규화 1

제3 정규화 2

이 부분은 회원 안에서 주문이라는 게 또 갈라진다.

제3 정규화 3

이것이 최종 테이블이다. 회원, 상품이 주 테이블이고, 주문을 하게 되면 (포스기처럼) 주문 테이블이 생긴다.

엔티티 → 테이블 변환 규칙

첫 번째 규칙은 엔티티가 테이블로 바뀐다는 것이다.

맨 처음에는 카디널리티를 표현한다. (3종류: 1:1, 1:N, N:M)

1:1 은 어느 쪽에 몰아도 된다.
1:N 은 N(다) 쪽으로 몰아라.
M:N 은 중간에 끼어 있는 관계라는 것도 테이블이 된다.

한 학생은 여러 과목을 수강하고(DB도 듣고 OS도 듣고 등등), 한 과목도 한 학생만 듣는 것이 아니다. (1:N, N:1의 관계이므로 다대다 관계이다.) “어떤 학생이 무슨 과목을 듣는가”를 표현한다.

과목번호와 학번을 합해서 PK를 주거나, 수강번호라는 걸 하나 더 만들어서 PK로 만들 수 있다.

다대다 관계 1

다대다 관계 2

다대다 관계 3

다대다 관계 4

다대다 관계 5

엔티티는 테이블로 변환되고, 1:1이나 1:N은 FK(외래키)가 된다.

1:1 에서는 아무나 가져가고,
1:N 에서는 N이 상대방의 PK를 가져간다.
M:N 에서는 두 테이블과, 중간의 관계가 또 테이블로 만들어지므로 외래키가 2개가 된다.

속성(애트리뷰트)은 컬럼이 되고, 키 애트리뷰트(밑줄로 표시하던 것)는 기본키가 된다.

변환 규칙 1

변환 규칙 2

변환 규칙 3

변환 규칙 4

변환 규칙 5

유저 아이디가 유저의 아이디를 가리킨다. 유저 아이디가 외래키인 이 경우처럼, 외래키가 다른 PK와 연결되면 비식별 관계라고 한다.

비식별 관계 1

비식별 관계 2

비식별 관계 3

비식별 관계 4

비식별 관계 5

엄밀히 따지면 DAO는 데이터베이스 관련 일만 하는 것이고, 더하고 빼고 곱하는 등의 처리는 서비스에서 한다. 즉, 모든 일 처리는 서비스에서 하고, 서비스에서 나온 결과를 가지고 DB로 가는 것이 DAO이다.

DAO 구조

뷰란? (VIEW)

기존의 테이블을 바탕으로 생성되는 가상의 테이블이다.
조인 등의 작업을 해서 만든 결과 테이블이 가상으로 저장된 형태다.
데이터를 직접 소유하지 않으며, 뷰를 통해 기존 테이블 내용을 검색한다.
보안상의 이유 혹은 자주 사용되는 복잡한 쿼리를 보다 간단하게 사용하기 위해 만든다.
물리적인 저장 공간을 가지지 않는다.
하나 이상의 테이블로부터 만들어진다.
뷰를 액세스하면 간접적으로 테이블을 액세스하게 된다.

뷰 개념

뷰의 목적은 빠른 검색이다.

1000페이지짜리 책에서 “뷰”라는 주제의 내용을 빨리 찾고 싶다고 하자. 1페이지부터 계속 넘겨 가며 찾는 것이 순차 검색이고, 맨 뒤의 색인(인덱스)에서 가나다순/abc순으로 정리된 위치를 찾아가는 것이 빠른 방법이다. 뷰도 마찬가지로, 테이블을 만들고 INSERT로 데이터를 집어넣으면 데이터가 연속적인 저장 공간에 저장된다고 생각할 수 있다.

인덱스

인덱스 생성 기준

SQL 문의 WHERE 절에서 자주 사용하는 컬럼이 대상이 된다.
빈번하게 변경되지 않는 테이블에 적용한다. (자주 변경되는 테이블에 적용하면 비효율적)
데이터가 많은 테이블에 효과적이다. (책으로 치면 1000페이지가 넘으면 자료 찾기가 힘든데, 인덱스가 있으면 찾기 쉽다.)
테이블 간 Join에 사용되는 컬럼을 선정한다.

데이터가 많은 테이블에 효과적이며, 그렇지 않으면 굳이 인덱스를 만들지 않아도 된다. 변경이 일어나면(예: 500페이지 기준으로 정리해 뒀는데 530페이지로 늘어나면) 인덱스를 다시 조정해야 한다.

인덱스를 사용하지 말아야 할 경우

테이블에 행이 적은 경우
찾는 컬럼이 WHERE 조건에 자주 사용되지 않을 때
테이블이 자주 입력·수정·삭제될 때
검색 결과의 양이 많은 경우 (전체 데이터의 15% 이상)

인덱스 예시

인덱스를 만드는 문제가 나올 수 있는데, 인덱스를 만드는 전치사(예약어)는 ON이다.

인덱스는 떨어져 있어도, 실제 주소 값(물리적인 위치 값)을 하나 주면 그 값이 어디인지를 알고 바로 찾아갈 수 있도록 한다.