8장 데이터베이스 설계

34강. 데이터베이스 설계

1. 데이터베이스 설계

데이터베이스를 설계한다 : 데이터베이스의 스키마 내에 테이블, 인덱스, 뷰 등의 데이터베이스 객체를 정의하는 것

'스키마 설계' 라 불리기도 함

 

- 논리명과 물리명

테이블 설계시 테이블 정의서나 설계도 등의 문서를 작성하는 경우가 많음

보통 문서의 양식은 DESC 명령에 따라 표시되는 결과와 비슷

 

테이블을 설계할 때, 하나의 테이블에 대해 두 개의 이름을 지정할 때가 있음

• 물리명 : 데이터베이스에서 사용될 이름, CREATE TABLE에 지정하는 이름
• 논리명 : 테이블의 '설계상 이름'

물리명은 데이터베이스 시스템 규칙에 따라 제약O

→ 물리명만으로는 의미가 전달되지 않는 경우가 있어 논리명 필요

논리명은 해당 테이블을 실제로 부를 때 사용하는 이름

 

- 자료형

테이블의 열에는 자료형을 지정해야 함

무엇이든 저장할 수 있는 자료형은 없음

 

- 고정길이와 가변길이

문자열의 자료형에는 고정길이와 가변길이가 있음

자리수가 이미 정해져 있는 경우에는 고정길이 문자열로 지정하는 편이 좋음

문자열의 길이의 변동폭이 클 경우에는 가변길이 문자열이 적합

 

VARCHAR 형 < LOB (Large Object) 형

LOB 형의 경우 인덱스를 지정할 수 없다는 제약이 있음

 

- 기본키

테이블의 행에 유일성을 지정

자동증가 열 : INSERT 할 경우 번호를 자동으로 증가시켜 저장해주는 편리한 열

AUTO_INCREMENT로 지정한 열은 PRIMARY KEY or UNIQUE로 유일성 지정해야 함

 

2. ER다이어그램

ER다이어그램 : 테이블을 설계할 때 테이블 간의 관계를 명확히 하기 위해 설계도를 작성하는 경우에 많이 쓰이는 도식

E(Entity)R(Relationship)

개체: 테이블 또는 뷰

개체는 사각형으로 표시, 사각형의 상단에 개체 이름, 사각형 안에는 개체의 속성(테이블의 열→주로 논리명) 표기

ex)

상품

상품코드

상품명 메이커코드 가격

 

개체와 개체가 서로 연계되는 경우 선으로 이어서 표현

연계를 표기할 때는 몇대 몇의 관계를 가지는지 숫자나 기호로 표시

• 일대일(1:1)
• 일대다(1:多)
• 다대다(多:多)

 

 

35강. 정규화

정규화 : 데이터베이스의 테이블을 규정된 올바른 형태로 개선해나가는 것

데이터베이스의 설계 단계에서 행해짐

 

1. 정규화

정규화는 단계적으로 실시

주문번호	날짜	성명	연락처	주문상품
1	1/1	박준용	010-⨉⨉⨉⨉	0001 ◯◯ 1개, 0002 ⨉⨉ 10개
2	2/1	김재진	016-⨉⨉⨉⨉	0001 ◯◯ 2개, 0002 ⨉⨉ 3개
3	2/5	박준용	010-⨉⨉⨉⨉	0001 ◯◯ 3개, 0003 △△ 1개

'0001 ◯◯ 1개' : 상품코드가 0001인 상품◯◯를 1개 주문했다는 뜻

 

위의 데이터를 정규화해 데이터베이스의 테이블로 만들고자 함

첫 번째 단계: 제1 정규화 → 제1 정규형 테이블 만듦

 

2. 제1정규형

관계형 데이터베이스의 테이블에는 하나의 셀에 하나의 값만 지정할 수 있다는 제약이 있음

제약으로 인해 주어진 데이터 중 주문상품의 데이터를 그대로 테이블로 만들 수 없음

 상품코드, 상품명, 개수 데이터를 담는 세 개의 열로 나누어야 함

 

주문번호	날짜	성명	연락처	상품코드	상품명	개수
1	1/1	박준용	010-⨉⨉⨉⨉	0001	◯◯	1
1	1/1	박준용	010-⨉⨉⨉⨉	0002	⨉⨉	10
2	2/1	김재진	016-⨉⨉⨉⨉	0001	◯◯	2
2	2/1	김재진	016-⨉⨉⨉⨉	0002	⨉⨉	3
3	2/5	박준용	010-⨉⨉⨉⨉	0001	◯◯	3
3	2/5	박준용	010-⨉⨉⨉⨉	0003	△△	1

 

제1정규화의 제1단계 :하나의 셀에 하나의 값만 저장할 수 있도록 하고, 반복되는 부분을 세로(행) 방향으로 늘려나감

 

제1정규화에서는 중복을 제거하는 테이블의 분할도 이루어짐

 

위의 테이블을 주문상품 테이블 / 주문 테이블로 나눔

 

주문)

주문번호	날짜	성명	연락처
1	1/1	박준용	010-⨉⨉⨉⨉
2	2/1	김재진	016-⨉⨉⨉⨉
3	2/5	박준용	010-⨉⨉⨉⨉

 

주문상품)

주문번호	상품코드	상품명	개수
1	0001	◯◯	1
1	0002	⨉⨉	10
2	0001	◯◯	2
2	0002	⨉⨉	3
3	0001	◯◯	3
3	0003	△△	1

두 테이블(주문, 주문상품)을 주문번호로 연계함

 

두 개로 분할하여 반복되는 부분(주문번호~성명)이 하나로 정리됨

 

주문 테이블의 경우 주문번호에 중복된 값이 존재하지 않기 때문에 기본키로 지정 가능

주문상품 테이블의 경우는 주문번호와 상품코드를 묶어 기본키로 지정 가능

 

제1정규화 : 반복되는 부분을 찾아내서 테이블을 분할하고 기본키가 될 열을 작성할 수 있음

 

3. 제2정규형

데이터가 중복하는 부분을 찾아내어 테이블로 분할해 나감

기본키에 의해 특정되는 열과 그렇지 않은 열로 나누는 것으로 정규화가 이루어짐

주문번호	상품코드	상품명	개수
1	0001	◯◯	1
1	0002	⨉⨉	10
2	0001	◯◯	2
2	0002	⨉⨉	3
3	0001	◯◯	3
3	0003	△△	1

이 테이블에서는 주문번호-상품코드가 기본키

 

개수는 기본키를 바탕으로 특정되는 데이터임

상품명은 주문번호와 관계없이 상품코드만으로 특정됨

 

이에 따라 두 가지 테이블로 분할

 

주문상품)

주문번호	상품코드	개수
1	0001	1
1	0002	10
2	0001	2
2	0002	3
3	0001	3
3	0003	1

 

상품)

상품코드	상품명
0001	◯◯
0002	⨉⨉
0003	△△

 

제2정규화 : 함수종속성(키 값을 이용해 데이터를 특정지을 수 있는 것)을 찾아내서 테이블을 분할

 

4. 제3정규형

중복하는 부분을 찾아내어 테이블을 분할하는 수법

기본키 이외의 부분에서 중복이 없는지 조사

 

주문)

주문번호	날짜	성명	연락처
1	1/1	박준용	010-⨉⨉⨉⨉
2	2/1	김재진	016-⨉⨉⨉⨉
3	2/5	박준용	010-⨉⨉⨉⨉

기본키 : 주문번호

 

같은 사람이 여러 번 주문하는 경우가 있음

이름을 기준으로 연락처를 특정지을 수 있음

 

위 테이블을 분할

주문)

주문번호	날짜	고객번호
1	1/1	1
2	2/1	2
3	2/5	1

 

고객)

고객번호	성명	연락처
1	박준용	010-⨉⨉⨉⨉
2	김재진	016-⨉⨉⨉⨉

고객번호를 기본키로 지정하여 고객 테이블 작성함

 

실제로는 제5규형까지 있으나 대부분의 시스템에서는 제3정규형까지의 정규화 채택

 

 

정규화 후의 테이블

주문)

주문번호	날짜	고객번호
1	1/1	1
2	2/1	2
3	2/5	1

 

고객)

고객번호	성명	연락처
1	박준용	010-⨉⨉⨉⨉
2	김재진	016-⨉⨉⨉⨉

 

주문상품)

주문번호	상품코드	개수
1	0001	1
1	0002	10
2	0001	2
2	0002	3
3	0001	3
3	0003	1

 

상품)

상품코드	상품명
0001	◯◯
0002	⨉⨉
0003	△△

 

위의 테이블 간의 연계를 ER다이어그램으로 표현하여 테이블 간 관계를 알아보기 쉽게 할 수 있음

FK: 외부키 속성을 가짐을 의미

 

5. 정규화의 목적

하나의 데이터는 한 곳에 있어야 한다는 규칙에 근거

중복하거나 반복되는 부분을 찾아내서 테이블을 분할하고 기본키를 작성해 사용

 

하나의 데이터가 반드시 한 곳에만 저장되어 있다면 데이터를 변경하더라도 한 곳만 변경하면 됨

정규화를 통해 테이블에 대한 인덱스의 재구축 억제 가능

 

36강. 트랜잭션

START TRANSACTION
COMMIT
ROLLBACK

 

1. 트랜잭션

- 발주처리

주문이 발생했을 때의 처리를 생각해보면 주문테이블에 INSERT 한 번, 주문상품 테이블에 주문된 상품 수 만큼 INSERT 명령 실행하여 최소 두 번 INSERT 명령이 실행되어야 함

 

INSERT 명령이 특정 원인으로 인해 에러가 발생한 경우, 트랜잭션을 사용하지 않을 때는 따로 DELETE 명령을 실행

 

2. 롤백과 커밋

몇 단계로 처리를 나누어 SQL 명령을 실행하는 경우에 트랜잭션을 자주 사용

트랜잭션을 사용하여 데이터를 추가하면 에러가 발생한 경우에도 트랜잭션을 롤백하여 종료할 수 있음

아무런 에러가 발생하지 않은 경우 변경사항을 적용하고 트랜잭션을 종료(커밋)

 

- 자동커밋

트랜잭션을 사용하여 데이터를 추가할 때는 자동커밋을 꺼야 함

자동커밋을 끄기 위해 명시적으로 트랜잭션의 시작 선언

START TRANSACTION #트랜잭션 시작

 

< 트랜잭션을 종료하는 두 가지 방식 >

①변경된 내용을 적용한 후에 종료

COMMIT #트랜잭션 내에 실행한 명령을 적용한 후 종료

 

②변경된 내용을 적용하지 않고 종료

ROLLBACK #트랜잭션 내에서 실행한 명령을 파기한 후 종료

 

트랜잭션 내에서 실행된 SQL 명령은 임시 데이터 영역에서 수행되다가,

COMMIT 명령을 내릴 시 정식 데이터 영역으로 변경이 적용되고 ( '트랜잭션을 걸어서 실행한다' )

ROLLBACK 명령을 내리면 임시 데이터 영역에서의 처리는 버려짐 ( ' 트랜잭션 내에서 실행한다' )

 

3. 트랜잭션 사용법

트랜잭션 내에서 실행하는 SQL 명령은 세트 단위로 유효/무효가 됨

에러가 발생하지 않아도 ROLLBACK 하면 변경된 내용 파기됨

에러가 발생하더라도 COMMIT 하면 문제없이 실행된 SQL 명령의 변경사항은 그대로 반영됨

 

MYSQL의 경우만 'START TRANSACTION' / SQL Server or PostgreSQL : 'BEGIN TRANSACTION'

Oracle or DB2 는 따로 없음

 

자동커밋은 클라이언트 툴의 기능

데이터베이스 서버에서는 언제나 트랜잭션을 걸 수 있는 상태로 SQL 명령이 실행됨

트랜잭션을 사용할 경우 접속형태나 클라이언트 툴의 자동커밋 사용 여부 등, 트랜잭션 관련 기능을 파악해 둘 필요가 있음