SQLD 1과목 : 데이터 모델과 SQL 개념정리

 

 

데이터 모델과 SQL

 

1. 정규화

 

정규화

 

• 하나의 엔터티에 많은 속성을 넣음→ 엔터티를 조회할 때 마다 많은 양의 데이터가 조회 됨

• 최소한의 데이터만을 하나의 엔터티에 넣는식으로 데이터를 분해하는 과정을 정규화라고 함

• 데이터의 중복을 제거하여 용량을 최소화시킴

• 데이터 일관성, 최대한 데이터 유연성을 위한 과정

• 데이터의 이상현상을 줄이기 위한 데이터베이스 설계 기법

• 논리 데이터 모델링 수행 시점에서 고려됨

 

 

이상현상

정규화를 하지 않아 발생하는 현상

1. 삽입이상
2. 갱신이상
3. 삭제이상

정규화단계 (중요)

 

1) 제 1정규화

• 모든 속성은 반드시 하나의 값을 가져야 함

• 테이블 컬럼이 원자성을 갖도록 테이블을 분해하는 단계

2) 제 2 정규화

• 제 1정규화를 진행한 테이블에 대해 완전 함수 종속을 만들도록 테이블을 분해

• 엔터티의 일반속성은 주식별자 전체에 종속이어야 한다

3) 제 3 정규화

• 엔터티의 일반 속성 간에는 서로 종속적이지 않다

• 이행적 속성을 제거하도록 테이블을 분해

 

 

4) BNCF 정규화

• 모든 결정자가 후보키가 되도록 테이블을 분해하는 것(결정자가 후보키가 아닌 다른 컬럼에 종속되면 안됨)

5) 제 4 정규화

• 여러 컬럼들이 하나의 컬럼을 종속시키는 경우 분해하여 다중값 종속성을 제거

6) 제 5 정규화

• 조인에 의해서 종속성이 발생되는 경우 분해

 

 

 

 

반정규화

 

• 역정규화(De-Normalization)의 개념

• 성능을 위해 데이터 중복을 허용

• 성능의 향상을 항상 보정하는 것은 아님

• 조회(SELECT) 속도를 향상시키지만, 데이터 모델의 유연성은 낮아짐

*비정규화는 정규화를 수행하지 않음을 의미

 

 

반정규화 수행 케이스 

 

•  정규화에 충실하여 종속성, 활용성은 향상되지만 수행 속도가 느려지는 경우

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

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

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

 

---

 

 

2. 관계와 조인의 이해 

 

 

관계의 개념

 

• 엔터티 인스턴스 사이의 논리적인 연관성

• 관계를 맺는 다는 것은 식별자를 상속 시키고 해당 식별자를 매핑키로 활용해 데이터를 결합해보겠다는 것을 의미한다

 

조인의 개념

 

식별자를 상속하고 상속된 속성을 매핑키로 활용하여 데이터를 결합하는 것을 의미

 

 

 

---

 

 

3. 모델이 표현하는 트랜잭션의 이해

 

 

트랜잭션이란?

• 하나의 연속적인 업무 단위를 말함

• 트랜잭션에 의한 관계는 필수적인 관계 형태를 가짐

• 하나의 트랜잭션에는 여러 select,insert,delete,update 등이 포함될 수 있음

 

 

💡즉 DBMS에서 데이터를 다루는 논리적인 작업 단위를 말함

 

 

 

데이터베이스에서 트랜잭션을 정의하는 이유

 

• 데이터베이스에서 데이터를 다룰 때 장애가 일어날 때 데이터를 복구하는 작업의 단위가 됨

• 데이터베이스에서 여러 작업이 동시에 같은 데이터를 다룰 때가 이 작업을 서로 분리하는 단위가 됨

 

 

💡 트랜잭션은 전체가 수행되거나 또는 전혀 수행되지 않아야함 (all or nothing)

 

 

트랜잭션의 성질

 

☑️원자성 : 트랜잭션에 포함된 작업은 전부 수행되거나 아니면 전부 수행되지 않아야함

START TRANSACTION	트랜잭션의 시작
COMMIT	트랜잭션의 종료
ROLLBACK	트랜잭션을 전체 혹은 <savepoint>까지 무효화 시킴
SAVEPOINT	<savepoint>를 만듦

 

☑️일관성: 트랜잭션을 수행하기 전이나 수행한 후나 데이터베이스는 항상 일관된 상태를 유지해야 함

 

일관성은 테이블 생성시 create문과 alter 문의 무결성 제약조건을 통해 명시됨

 

☑️고립성: 수행 중인 트랜잭션에 다른 트랜잭션이 끼어들어 변경 중인 데이터 값을 훼손하는 일이 없어야 함

 

고립성이 낮은 경우 발생되는 문제점

 

1. Dirty Read (오손읽기): 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않는 데이터를 읽는 것
2. Non-Repeatable Read(반복읽기 불가능) : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행 시, 그 사이에 다른 트랜잭션이 값을 수정/삭제하여 두 쿼리의 결과가 다르게 제공되는 현상
3. Phantom Read(유령데이터읽기) : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행 시, 첫 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서 나타나는 현상

 

 

☑️ 지속성: 수행을 성공적으로 완료한 트랜잭션은 변경한 데이터를 영구히 저장해야 함

 

트랜잭션이 정상적으로 완료 호은 부분완료한 데이터는 DBMS가 책임지고 데이터베이스에 기록하는 성질

 

 

Oracle vs SQLServer에서의 트랜잭션

Oracle에서는 DDL 수행시 자동 Commit 된다. (SQLServer는 자동 X)

즉 oracle 에서 DDL 문장 수행은 내부적으로 트랜잭션을 종료시킨다

 

 

 

 

트랜잭션의 재실행 (REDO)

 

트랜잭션 시작이 있고 종료(COMMIT)가 있는 경우. 변경내용이 버퍼에서 데이터베이스에 기록되지않았을 가능성이 있다. 따라서 트랜잭션이 변경한 내용을 데이터베이스에 다시 기록하는 과정이 필요하다.

 

 

트랜잭션의 취소 (UNDO)

 

트랜잭션의 시작만 있고 종료가 없는 경우. COMMIT 연산이 로그에 보이지 않는 다는 것은 트랜 잭션이 완료되지 못했다는 의미로 트랜잭션이 한 일을 모두 취소해야한다

 

 

즉시 갱신 방법

갱신데이터→ 로그 버퍼→데이터베이스 작업이 COMMIT전에 동시에 진행될 수 있으며 COMMIT이 되면 갱신 데이터는 로그에 기록이 끝난 상태

 

 

지연 갱신 방법

갱신 데이터→로그가 끝나고 COMMIT이 된후 버퍼→데이터베이스 작입이 진행되는 방법

 

 

 

 

 

---

 

4. NULL 속성의 이해

 

 

NULL이란?

DBMS에서 아직 정해지지 않은 값을 의미

0과 빈문자열은 다른 개념

모델 설계 시 각 컬럼별로 NULL을 허용할 지 결정

 

 

NULL 특성

 

• NULL을 포함한 연산결과는 항상 NULL 값이다.

• NULL 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. (0은 숫자이고 공백은 하나의 문자이다)

• 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY 또는 정의되지 않은 모든 데이터 유형은 NULL값을 포함할 수 있다.

• 결괏값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.

• 널 값의 대상이 숫자 유형 데이터인 경우는 주로 0으로 문자 유형 데이터인 경우는 공백보다 X 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.

집계함수는 NULL을 제외한 연산 결과 리턴

(sum,avg,min,max 등의 함수는 항상 NULL을 무시한다)

 

 

NULL의 ERD 표기법

IE 표기법에서는 NULL 허용여부를 알 수 없음

바커 표기법에서는 속성 앞에 동그라미가 NULL 허용속성을 의미

 

 

💡Oracle에서 공백은 NULL로 치환되지만 SQL server에선 공백을 반영한다 

 

💡 또한 Oracle에선 NULL을 가장 큰 값으로 간주하여 오름차순으로 정ㄹ렬했을 경우에는 가장 마지막에 정렬된다. 반면 SQL server 에서는 NULL값을 가장 작은 값으로 간주하여 오름차순으로 정렬했을 경우 가장 먼저 정렬된다 

 

---

 

 

5. 본질식별자 VS 인조식별

 

 

본질 식별자

→업무에 의해 만들어지는 식별자 (꼭 필요한 식별자)

인조 식별자

→ 인위적으로 만들어지는 식별자 (꼭 필요하지 않지만 편이성 등의 이유로 인위적으로 만들어지는 식별자)

 

 

인조식별자 단점 (중요)

1. 중복데이터 발생 가능성→ 데이터 품질 저하
2. 불필요한 인덱스 생성 → 저장공간 낭비 및 DML 성능 저하