SQLD 요약

데이터 모델의 이해

데이터 모델링 이란

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

데이터 모델링 특징 

• 추상화 - 일정한 양식(표기법)에 맞게 간략히 표현
• 단순화 - 이해 쉽도록 규약에 의해 제한된 표기법으로 표현
• 명확화 - 명확하게 의미를 해석하기 쉽도록 정확히 기술

관점

• 데이터 -  데이터 관점에 따라 모델링 (구조분석, 정적분석)
• 프로세스 - 수행하는 작업 관점에 따라 모델링 (시나리오 분석, 도메인 분석, 동적 분석)
• 상관 - 프로세스와 데이터 간 영향 관점에 따라 모델링 (crud)

유의사항 : 유연성, 유일성, 일관성

 

데이터 모델링 과정

• 개념적 - 추상화 수준 높고 업무중심적이며 포괄적 수준
• 논리적 - 구축하고자 하는 키, 속성, 관계 등을 정확히 표현 (정규화 완료, 재사용성 높음)
• 물리적 - 데이터 저장을 고려한 설계 , 가장 구체적인 모델링

스키마 구조

• 외부 스키마 - 개별 사용자 관점의 이해와 표현
• 개념 스키마 - 관리자 관점으로 규칙과 구조를 표현한 전체적인 데이터베이스 논리구조 (일반적인 스키마)
• 내부 스키마 - 설계자 관점으로 저장 장치 고려한 이해와 표현 (물리적인 저장)

데이터 독립성 : 논리적, 물리적

데이터 모델링 요소 : 엔티티, 관계, 속성(성격)

 

erd 작성 순서 

엔티티 도출 > 배치 > 관계 설정 > 관계명 기술 > 관계 차수 설정 > 선택사양 기술

 

엔티티

엔티티 - 업무에서 관리해야 하는 데이터의 집합, 단수 명사, 인스턴스 집합

특징 : 유일한 식별자, 2개 이상의 인스턴스 집합 & 2개 이상의 속성, 관계가 있음

종류 

• 유형 - 물리적, 안정적, 지속적 ( 사원, 물품, 강사)
• 개념 - 개념적으로 존재하며 정보로 구분됨 ( 조직, 보함상품)
• 사건 - 비즈니스 프로세스에 따라 발생되는 엔티티 (주문, 청구, 미납)

뷴류 

• 기본 (유형) - 독립적으로 생성되는 업무에 원래 존재하는 정보 == 키
• 중심 (개념) - 기본 엔티티로부터 발생되며 행위 엔티티 생성함
• 행위 (사건) - 2개 이상의 상위 엔티티로부터 발생되는 엔티티 (자주 바뀌거나 많이 발생)

엔티티 이름 규칙 : 약어 금지, 단수 명사, 유일성 지키기, 의미가 명확

 

속성 

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

특징 : 주식별자에 함수적으로 종속 ( 기본키 변경되면 속성도 변경), 속성과 1:1 관계, 속성도 집합

종류

• 기본 - 비즈니스 프로세스에서 도출한 본래 속성 (이름, id, 전화번호)
• 설계 - 데이터 모델링 중 발생되는 속성 (상품코드, 지점코드)
• 파생 - 다른 속성에 의해 만들어지는 속성 (합계, 평균)

도메인 : 속상이 가질 수 있는 값의 범위

 

관계 

엔티티 간 논리적인 연관성, 존재의 형태나 행위로서 연관성이 부여된 상태

관계 차수 - 1:1  1 / 일대다 1:∈  0포함이면 01 or 0∈ 

실선(연관관계, 자식 식별자 구성에 포함, 부모 엔티티에 종속)  점선(의존관계, 자식 일반 속성에 포함)

 

식별자

엔티티를 대표하며 유일성을 만족하는 속성

특징 : 유일하게 구분 가능한 구분자, 다른 엔티티와 관계 연결, 유일성, 최소성, 불변성, 존재성 만족

• 유일성 - 주식별자에 의해 전체에서 유일하게 구분 가능
• 최소성 - 구성하는 속성은 최소의 수 여야 함
• 불변성 - 한번 특정 엔티티에 고정되면 값이 변하지 않으
• 존재성-  반드시 값이 존재(not null)

종류

• 후보키 - 유일성과 최소성 만족
• 기본키 - 후보키 중 대표하는 키
• 슈퍼키 - 유일성 만족, 최소성 불만족
• 대체키 - 후보키 중 대체키를 제외한 나머지 키
• 외래키 - 다른 테이블의 기본키로 참조무결성을 확인하기 위해 사용되는 키

분류

(대표성 여부)

주식별자 - 엔티티 내 구분자 ( 타 엔티티와 참조관계 연결)

보조식별자 - 대표성이 없어 참조 관계 불가능

(스스로 생성 여부)

내부 식별자 - 스스로 생성됨

외부 식별자 - 다른 엔티티와 관계로 생성됨

(속성의 수)

단일식별자 - 하나의 속성

복합식별자 - 다수의 속성

(대체 여부)

본식별자 - 비즈니스 프로세스에서 만들어짐

인조식별자 - 본식별자가 복잡한 구성을 가질 때 생성됨 (중복 막기 어려움, 추가적인 인덱스 필요)

 

정규화

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

이상현상(error) 줄이며 데이터 모델의 독립성 확보

엔티티 상세화 하는 과정으로 논리 데이터 모델링 수행 시점에 고려함

• 제1 정규화 - 모든 속성은 반드시 하나의 값만 가짐 (원자성) > 기본키 설정 
• 제2 졍규화 - 엔티티의 일반 속성은 주식별자 전체에 종속 (부분함수종속성) >기본키가 2개일 경우 분리
• 제3 정규화 - 엔티티 일반속성 간에는 서로 종속성이면 안됨 (이행함수종속성)
• 보이스코드(bcnf) - 후보키가 기본키 속성 중 일부에 함수적 종속일 때 다수의 주식별자 분리

 이상현상 

• 삽입 이상 - 행 삽입 시 지정되지 않은 속성 값이 null 을 가지는 경우
• 갱신 이상 - 데이터 갱신시 일부만 갱신되어 일관성 깨짐
• 삭제 이상 - 행 삭제 시 의도하지 않은 연쇄적 삭제 발생

반정규화

시스템 성능 향상 및 개발 운영 단순화 위해 정규화된 데이터모들을 중복, 통합, 분리하는 데이터 모델링 기법

조회 성능 향상되지만 모델의 유연성 떨어짐

(반졍규화 == 역정규화  // 반정규화 != 비정규화)

 

적용하는 경우

정규화로 인해 수행 속도가 느려진 경우

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

특정 범위의 데이터만 자주 사용하는 경우

요약/집계 정보를 자주 사용하는 경우

 

수행 절차

대상 조사 및 검토 > 문제 파악 

• 대량의 범위 처리 및 빈도수  - 접근하는 프로세스 많고 일정한 범위만 조회 및 처리하는 경우
• 통계성  프로세스 - 통계 정보를 필요로 할 때 별도의 통계테이블 생성
• 테이블 조인 수 - 조인을 위해 테이블에 많은 조인 연산 필요한 경우

다른방법 검토

• 클러스터링, 인덱스 - 대량 혹은 부분 처리에 의해 성능 저하된 경우 클러스터링 , 인덱스 조정
• 뷰 - 많은 조인 연산으로 조회 성능 저하된 경우 사용
• 파티셔닝 - 키 성격에 따라 테이블 분리해 물리적으로 저장공간 분리
• 캐시 - 응용 프로그램에서 로직 구사하는 바업 변경해 성능 향상

 

슈퍼타입 - 공통부분

서브타입 - 공통부분을 상속받고 다른 엔티티와 차이가 있는 속성

 

변환기술

OneToOne type -  개별로 발생되는 트랜잭션에 대해 개별 테이블로 구성

plus type - 슈퍼 + 서브에 대해 발생하는 트랜잭션은 슈퍼+서브 테이블로 구성

single type / all in one type - 전체가 하나의 트랜잭션으로 발생할 경우 하나의 테이블로 구성

 

조인

식별자를 상속하고 상속된 속성을 매핑키로 활용해 데이터 결합함

부모 식별자 -> 자식 식별자 에 포함 : 식별관계

부모 식별자 -> 자식 일반 속성으로 상삭 : 비식별관계 (조인 많이 발생)

관계를 맺음 : 식별자 상속시키고 해당 식별자를 매핑해 데이터를 결합하는 것

 

null

아직 정의 되지 않은 값 (0이나 빈값이 아님)

not null 또는 primary key 외 모든 데이터 유형에 가능

nvl, isnull로 결과값 얻음

집계함수에서 제외됨

 

연산

null과의 연산 결과는 null 리턴

비교 연산자와의 결과는 unknown

집계함수는 null 을 제외하고 계산

 

 

DBMS

효율적인 데이터 관리와 데이터 손상을 피하고 데이터 복구를 위한 시스템 

구조

• 중앙 집중형 - 하나의 물리적인 시스템에 여러명의 사용자 접속 (트리 구조 1:n)
• 분산형 - 물리적으로 떨어져 있지만 네트워크로 연결된 데이터베이스로 작업 (n:m)

분산형 특징

• 분할 투명성 - 하나의 논리적 관계가 분할되어 각 사본이 여러 시스템에 저장되어 있음을 알 필요 없음
• 위치 투명성 - db위치를 알 필요 없으며(접속 주소), 어느곳에서 접속 해도 동일한 명령으로 접근 가능해야함
• 지역 사상 투명성 - 지역 db와 물리적 db 간 사상이 보장되어 각 지역 시스템과 무관하게 사용 가능
• 중복 투명성 - 객체가 여러 시스템에 중복되어도 데이터의 일관성 유지해야 함
• 장애 투명성 - 지역 시스템이나 통신망 이상 발생해도 데이터의 무결성 유지해야 함
• 병행 투명성 - 응용 프로그램이 동시에 트랜잭션을 수행해도 결과에 이상이 없어야 함

관계형 데이터베이스

관계에 데이터 저장 및 관리하며 집합 연산 및 관계 연산 할 수 있는 데이터베이스

종류 - oracle, mssql, mysql  (mongodb는 아님)

 

관계 연산

• 선택연산 - 관계에서 특정 조건에 해당하는 행만 조회
• 투영연산 - 관계에서 특정 조건에 맞는 속성만 조회
• 결합연산 - 여러 관계에서 공통된 속성을 통해 새로운 관계 생성
• 나누기연산 - 기준 관계에서 나누는 관계가 가지는 속성과 동일한 행만 추출해 중복 행 제거

SQL

 

명령어 종류 

• DML - select, insert, update, delete  (조회 및 데이터 변형)
• DDL - create, alter, drop, rename (데이터 구조 정의)
• DCL - grant, revoke (권한 부여 및 회수)
• TCL - commit, rollback (트랜잭션 제어)

트랜잭션 특징

• 원자성 - 연산 전부가 실행되거나 실행되지 않아야 함
• 일관성 - 트랜잭션 결과로 데이터베이스 상태가 모순되지 않고 일관성 유지 해야 함
• 고립성 - 트랜잭션 실행 중 중간결과는 다른 트랜잭션이 접근 불가능
• 지속성(영속성) - 트랜잭션 완료 후 결과는 영구적으로 보장되어야 함

sql문 실행 순서

1. 파싱 - sql 문법 확인 및 구문 분석 후 library cache에 저장
2. 실행 - 옵티마이저가 생성한 실행 계획에 따라 sql문 실행
3. 인출 - 데이터 읽어 전송함

select

데이터 조회하는 명령어 > 특정 행 또는 칼럼 조회

select 칼럼명 from 테이블명;

 

distinct - 중복 제외

alias(as) - 테이블 또는 컬렴명을 별칭으로 일시적 저장 및 호출 가능

select count(*) as "책 수" from book;

 

order by  - 데이터 조회 시 오름/내림 차순으로 정렬

 

where - 원하는 행 또는 칼럼만 조회하기 위한 조건문  (in list - list 안에 있는 경우)

+오라클에선 ' ' 입력 시 null로 인식 > is null 로 조회  // sql에서는 ' ' 로 조회 가능

+검색 case 표현식 - 개별 조건 확인하고 반환

  단순 case 표현식 - 표현식 값 기준, 여러 조건 확인

   decode - 여러 조건 비교하고 일치하는 조건의 결과 반환

 

group by - 집합 내 하나 혹은 여러 행을 그룹화한 후 집계함수를 이용해 계산하는 명령어

having - group by의 조건  

특성

• group by 절을 통해 소그룹별 기준 정한후 select 절에 집계함수 사용
• 집계함수의 통계 정보는 null 제외 후 수행
• select 와 달리 as 사용 불가
• having 절은 group by 절의 기준이나 소그룹의 집계함수를 이용한 조건 표시
• group by 절에 의한 집계 데이터 중 having절에서 조건을 둬 만족하는 내용 출력
• having절은 group by 뒤에 오지만 group by절이 없어도 가능

*실행 순서 - from > where > group by > having > select > order by

 

함수

null - 데이터 정의가 없는 알 수 없는 값 >  연산 시 null

 

문자

• lower / upper - 소문자 / 대문자 화
• length - 길이 반환
• substr - 전체 문자열 중 start ~ end까지의 일부 문자열 반환 substr(컬럼명,s,e)
• instr - 전체 문자열 중 특정 문자 "a"의 위치 반환 instr(컬럼명, "a")
• lpad / rpad - 문자열을 l 길이가 되도록 왼/오른쪽에 특정 문자 "a"를 채운 문자열 반환
• lpad/rpad(컬럼명, l, "a")
• trip/ltrim/rtrim - 문자열에서 공백 제거한 문자열 반환
• replace - 문자열에서 "old" 문자를 'new' 문자로 변환한 문자열 반환
• replace( 컬럼명, old, new)

숫자 

• round - 숫자를 n번째 소수점 자리에서 반올림한 숫자 반환 round(컬럼명, n)
• truncate - 숫자를 n번째 소수점 자리에서 버린 숫자를 반환
• ceil/floor - 숫자를 n번째 소수점 자리에서 올림/내림한 숫자 반환
• power - 숫자를 n번 제곱한 숫자 반환
•  sign - 숫자가 양수/음수/0인지 구별
• trunc(숫자[m]) - 숫자를 m자리에서 반올림해 리턴 (디폴트 0)