3월에 있을 SQLD 자격증 취득을 위해 현재 노랭이 문제집(SQL 자격검정 실전문제)을 풀고 틀린 부분과 이해가 안가는 부분들을 정리해보자!!

데이터 모델링의 이해

데이터 모델의 이해

데이터 모델링이란?

• 정보시스템을 구축하기 위한 데이터 관점의 업무 분석 기법
• 현실 세계의 데이터를 약속된 표기법으로 표현하는 과정
• 데이터베이스를 구축하기 위한 분석 및 설계의 과정

데이터 모델링 특징

1. 현실 세계를 일정한 형식에 맞추어 표현하는 추상화
2. 시스템 구현과 업무분석 및 업무형상화를 하는 것이 목적
3. 복잡한 현실을 제한된 언어나 표기법으로 이해하기 쉽게 하는 단순화
4. 모호함을 배제하고 누구나 이해가 가능하도록 정확하게 현상을 기술하는 정확화

데이터 모델링 유의점

• 중복
  • 여러 장소의 데이터베이스에 같은 정보를 저장하지 않아야 한다.
• 유연성
  • 데이터 혹은 프로세스의 작은 변화가 애플리케이션과 데이터베이스에 중대한 변화를 일으킬 수 있는 가능성을 줄여야한다.
  • 데이터의 정의를 데이터의 사용 프로세스와 분리
• 일관성
  • 데이터 간의 상호 연관관계를 명확하게 정의하여 일관성 있게 데이터가 유지되어야 한다.

데이터 스키마

• 데이터베이스의 구조와 제약조건에 관해 전반적인 명세를 기술한 것

구조

• 외부스키마
  • View 단계
  • 여러 사용자들에게 데이터를 어떻게 보여줄 것인지 표현
• 개념스키마
  • 모든 사용자 관점을 통합한 조직 전체 관점의 통합적 표현
  • DB에 저장되는 데이터와 그들 간의 관계를 표현하는 스키마
  • 데이터 베이스의 전체적인 논리적 구조
• 내부스키마
  • 데이터 베이스의 물리적인 저장구조를 표현

데이터 독립성

• 논리적 독립성
  • 데이터베이스의 논리적 구조가 변경되어도 응용 프로그램이나 사용자의 요구에 영향을 주지 않는다.
• 물리적 독립성
  • 데이터베이스의 파일 구조의 변화가 논리스키마에 영향을 주지 않는다.
  • 데이터베이스의 색인 구조의 변화가 응용 프로그램에 영향을 주지 않음.

엔터티(Entity)

엔터티 특징

1. 반드시 해당 업무에서 필요하고 관리하고자 하는 정보이어야 한다.
2. 유일한 식별자에 의해 식별이 가능해야 한다.
3. 두 개 이상의 인스턴스의 집합이어야 한다.
4. 업무 프로세스에 이용되어야 한다.
5. 반드시 속성이 있어야 한다.
6. 다른 엔터티와 최소 한 개 이상의 관계가 있어야 한다.
   • 단 통계성 엔터티나 코드성 엔터티의 경우 관계를 생략할 수 있다.

발생 시점에 따른 엔터티 분류

• 기본, 키 엔터티
• 행위 엔터티
• 중심 엔터티

엔터티 명명 규칙

1. 현업 업무에서 사용하는 용어이어야 한다.
2. 가능하면 약어를 사용하지 않는다.(약어 지양)
3. 단수명사를 사용
4. 모든 엔터티를 통틀어서 유일한 이름이 부여되어야 한다.

ERD(Entity-Relationship Diagram) 작성 순서

• 엔터티 도출 -> 엔터티 배치 -> 관계 설정 -> 관계명 기술 -> 관계 참여도 기술 -> 관계 필수여부 기술

속성

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

속성 특징

1. 엔터티에 대한 자세하고 구체적인 정보를 나타낸다.
2. 하나의 엔터티는 두 개 이상의 속성을 갖는다.
3. 하나의 인스턴스에서 각각의 속성은 한 개의 속성값을 가진다.
4. 속성도 집합니다.

도메인

• 엔터티 내에서 속성에 대한 데이터 타입과 크기 그리고 제약사항을 지정하는 것

속성 명명 규칙

1. 해당 업무에서 사용하는 이름을 부여한다.
2. 서술식 속성명은 사용하지 않는다.
3. 약어 사용은 가급적 제한한다.
4. 전체 데이터 모델에서 유일성을 확보하는 것이 좋다.

속성의 특성에 따른 분류

• 기본속성
  • 업무분석을 통해 바로 정의한 속성
• 설계속성
  • 원래 업무상 존재하지는 않지만 설계를 하면서 도출해 내는 속성
• 파생속성
  • 다른 속성으로부터 계산이나 변형이 되어 생성되는 속성

관계

관계의 특징

1. 관계는 존재적(연관) 관계와 행위에(의존) 의한 관계로 나누어 볼 수 있다.
   • ERD에서 존재관계와 행위 관계를 따로 구분하여 표기하지 않는다.
   • UML에서는 연관관계는 실선으로 나타내고 소스코드에서 멤버변수로 선언하여 사용하고, 의존관계는 점선으로 나타내며 오퍼레이션에서 파라미터 등으로 이용할 수 있도록 되어 있다.
2. 관계의 표기법은 관계명(관계의 이름), 관계차수(1:1, 1:M, M:N), 선택사양(필수 또는 선택)의 3가지 개념을 사용한다.
   • 선택사양에서 필수는I, 선택은 O로 표시

관계 정의시 확인할 사항

• 두 개의 엔터티 사이에 관심 있는 연관 규칙이 존재하는가?
• 두 개의 엔터티 사이에 정보의 조합이 발생되는가?
• 두 개의 엔터티 사이에 관계연결을 가능하게 하는 동사가 있는가?
• 두 개의 엔터티 사이에 관계연결에 대한 규칙이 서술되어 있는가?

식별자

주식별자 특징

• 유일성
  • 주식별자에 의해 엔터티 내에 모든 인스턴스들을 유일하게 구분
• 최소성
  • 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 한다.
• 불변성
  • 주식별자가 한번 특정 엔터티에 지정되면 그 식별자의 값은 변하지 않아야 한다.
• 존재성
  • 주식별자가 지정되면 반드시 데이터 값이 존재해야 한다.

식별자 vs 비식별자 관계 비교

항목	식별자 관계	비식별자 관계
목적	강한 연결관계 표현	약한 연결관계 표현
자식 주식별자 영향	자식 주식별자의 구성에 포함	자식 일반 속성에 포함
표기법	실선	점선
연결 고려사항	- 반드시 부모 엔터티에 종속 - 자식 주식별자 구성에 부모 주식별자 포함 필요 - 상속받은 주식별자 속성을 타엔터티에 이전 필요	- 약한 종속관계 - 자식 주식별자 구성을 독립적으로 구성 - 부모쪽의 관계참여가 선택관계

식별자 분류 체계

식별자	설명
주식별자	엔터티 내에서 각 인스턴스를 구분할 수 있는 식별자
보조식별자	각 인스턴스 구분이 가능하나 대표성을 가지지 못하는 식별자
내부식별자	엔터티 내부에서 스스로 만들어지는 식별자
외부식별자	타 엔터티와의 관계를 통해 타 엔터티로부터 받아오는 식별자
단일식별자	하나의 속성으로 구성된 식별자
복합식별자	둘 이상의 속성으로 구성된 식별자
본질식별자	업무에 의해 만들어지는 식별자
인조식별자	원조식별자가 복잡한 구성을 가지고있어 인위적으로 만들어진 식별자

데이터 모델과 SQL

정규화, 비정규화

정규화

• 관계형 데이터베이스에서 중복을 최소화하기 위해 데이터를 구조화하는 작업
• 논리 데이터 모델 상세화 과정의 대표적인 활동
• 논리 데이터 모델의 일관성을 확보하고 중복을 제거하여 속성들이 가장 적절한 엔터티에 배치되도록 한다.

장점

1. 데이터베이스 변경 시 이상 현상 제거
2. 저장 공간의 최소화 가능
3. 효과적인 검색 알고리즘 생성 가능
4. 데이터 삽입 시 릴레이션 재구성의 필요성 감소
5. 데이터 구조의 안정성 및 무결성 유지

단점

1. 릴레이션 간의 JOIN 연산 증가
   • 이로 인해 질의에 대한 응답 시간 지연

정규화의 원칙

• 정보의 무손실 표현
  • 하나의 스키마를 다른 스키마로 변환할 때 정보의 손실이 있어서는 안 된다.
• 분리의 원칙
  • 하나의 독립된 관계성은 하나의 독립된 릴레이션으로 분리시켜 표현해야 한다.
• 데이터의 중복성이 감소되어야 한다.

함수적 종속

• 완전 함수적 종속
  • 어떤 속성이 기본키에 대해 완전히 종속적일 때를 말한다.
• 부분 함수적 종속
  • 어떤 속성이 기본키를 구성하는 일부 속성에 족송되거나 기본키가 아닌 다른 속성에 종속적일 때를 말한다.

함수적 종속 예시

예를 들어<수강> 릴레이션이 (학번, 과목명, 성적, 학년)으로 되어있고 (학번, 과목명)이 기본키라고 한다면.

• '성적'은 '학번'과 '과목명'이 같을 경우에는 항상 같은 '성적'이 온다.
  • 즉, '성적'은 '학번'과 '과목명'에 의해서만 결정되므로 '성적'은 기본키에 완전 함수적 종속이 되는 것이다.
• 반면에 '학년'은 '과목명'에 관계없이 '학번'이 같으면 항상 같은 '학년'이 온다.
  • 즉, 기본키의 일부인 '학번'에 의해서 '학년'이 결정되므로 '학년'은 부분 함수적 종속이다.

비정규화(반정규화)

• 하나 이상의 테이블에 데이터를 중복해 배치하는 최적화 기법
• 시스템의 성능 향상, 개발 및 운영의 편의성 등을 위해 정규화된 데이터 모델을 통합, 중복 분리하는 과정으로, 의도적으로 정규화 원칙을 위배하는 행위

비정규화 예시

예를 들어, Courses와 Teachers라는 테이블로 이루어진 정규화된 데이터베이스를 생각해 보자. Courses 테이블에는 teacherlD를 둘 수는 있어도, teacherName이라는 필드를 두지는 않을 것이다. 따라서 강좌 정보와 교사 이름을 함께 나열하고 싶은 경우에는 두 테이블을 조인해야 한다.

이 방법의 단점은 테이블이 아주 클 경우 조인을 하느라 불필요할 정도로 많은 시간을 낭비하게 된다는 것이다.

장점

1. 조인 비용이 줄어들어 빠른 데이터 조회
2. 쿼리가 단순해져서 버그 발생 가능성도 줄어든다.

단점

1. 데이터 갱신이나 삽입 비용이 높음
2. 데이터 간의 일관성이 깨어질 수 있다.
3. 데이터를 중복하여 저장하므로 더 많은 저장 공간이 필요

비정규화 대상

• 자주 사용되는 테이블에 액세스하는 프로세스의 수가 가장 많고, 항상 일정한 범위만을 조회하는 경우
• 테이블에 대량 데이터가 있고 대량의 범위를 자주 처리하는 경우
• 테이블에 지나치게 조인을 많이 사용하게 되어 데이터를 조회하는 것이 기술적으로 어려울 경우

정규형

• 주어진 관계형 데이터베이스가 어느 정도의 정규화가 이루어졌는지를 판단하는 기준
• 정규화는 과정, 정규형은 그 결과(단계)이다.

제1 정규형(1NF)

• 테이블의 컬럼이 원자값(하나의 값)을 갖도록 테이블을 분해하는 것

  

제2 정규형(2NF)

• 제 1 정규형을 만족하는 테이블에 대해 완전 함수 종속을 만족하도록 테이블을 분해하는 것

  

• 위 이미지처럼 '성적' 컬럼은 기본키에 완전 함수 종속을 이루고 있지만 '과목' 컬럼은 부분 함수 종속을 이루고 있다.

• 제 2 정규형을 만족하는 테이블은 아래와 같다.

  

제3 정규형(3NF)-(수정 중)

• 제 2 정규형을 만족하는 테이블에 대해 이행적 함수 종속을 없애도록 테이블을 분해하는 것이다.

• 이행적 함수 종속이란, 특정 B 컬럼이 A 컬럼에 종속인 상태에서 특정 C 컬럼이 B 컬럼에 종속인 경우, C 컬럼이 A 컬럼에 종속적인 상태를 이행적 함수 종속이라 한다. (A->B, B->C = A->C)

  

• 위 이미지처럼 '과목코드'는 '학번'에 의해 결정되고 '담당교수'는 '과목코드'에 의해 결정되어서 '담당교수'는 '학번'에 의해 결정된다. 이를 이행적 함수 종속이라하며, 제3 정규형에 의해 아래와 같이 분해가 되어야 한다.

  

BCNF

• 제3 정규형을 만족하는 테이블에 대해 모든 결정자가 후보키가 되도록 테이블을 분해하는 것이다.

  

• 위 이미지처럼 기본키에 의해 '교수이름'이 정해진다.

• 하지만, '과목명'이 결정되면 '교수이름'도 정해지는데(과목명 -> 교수이름) 과목명은 후보 키가 아니다.

• 따라서 BCNF를 위반해서 BCNF 가 만족하도록 아래와 같이 분해하면 된다.

  

관계와 조인의 이해

• 조인이란 식별자를 상속하고, 상속된 속성을 매핑키로 활용하여 데이터를 결합하는 것
• 부모의 식별자를 자식의 식별자에 포함하면 식별관계, 부모의 식별자를 자식의 일반속성으로 상속하면 비식별관계(비식별관계에서 조인이 많이 발생)
• 관계를 맺는다는 것은 식별자를 상속시키고 해당 식별자를 매핑하여 데이터를 결합하는 것

Null

특징

1. 아직 정의되지 않은 값
2. 0이나 공백문자('')이 아니다.
3. NOT NULL 또는 PRIMARY KEY 외 모든 데이터 유형에 포함 가능
4. NUL, ISNULL로 다른 결과값을 얻음
5. 집계 함수에서는 제외

Null 과의 연산

• Null 값과의 연산은 Null을 리턴
• 모든 비교는 알 수 없음(Unknown) 리턴
• 집계함수는 Null을 제외하고 계산

참고

• 정규화, 비정규화
  • https://owlyr.tistory.com/20#85ff9bd6-2b8f-4f30-8dd4-df570d3cba58
  • https://mangkyu.tistory.com/110