[SQL] DB 설계(정규화,ROLLBACK, COMMIT) 1편

안녕하세요.

공부하는 웅이입니다.

 

SQLD 공부를 제대로 시작한지 2일차이다. 

 

나는 본래 책을 받자마자 그냥 한번 풀어보는 스타일이다. 개념이고 뭐고 나의 내공(?)을 믿는다는 마음이다. 그러다 항상 후회한다. 이번에도 바로 문제집부터 풀려고 하니, 확실히 개념들이 생소해서 오답률이 상당했다. 그래도 그럴 것 같아서 찍은 것들은 왠만하면 맞추긴 했는데, 속이 답답했다. 이런식으로 공부하면 망할 것 같다는 느낌이 들었다. 그래서 본격적으로 개념 공부를 시작하려고 한다.

 

이번 포스팅에서 알아볼 큰 개념은 데이터베이스 설계이다.

 

그럼 시작해보겠다!

 

---

데이터베이스 설계

데이터베이스를 설계한다는 것?

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

스키마 내에 정의한다는 뜻에서 '스키마 설계'라고도 한다.

​

데이터베이스 설계의 주된 내용은 테이블의 이름이나 열, 자료형을 결정하는 것이다.

'이 테이블은 이 열을 이용해 저쪽 테이블과 연결한다'와 같이 테이블 간의 관계를 생각하면서 여러 테이블을 정의하고 작성한다.

​

논리명과 물리명

테이블을 설계할 대는 테이블 정의서나 설계도 등의 문서를 작성하는 경우가 많다.

 

열명	자료형	NULL	기본값	비고
상품코드	CHAR(6)	No
상품명	VARCHAR(20)	Yes
가격	INTEGER	Yes
상품코드	INT(10)	No

 

하나의 테이블에 두 개의 이름을 지정할 때도 있다.

하나는 데이터베이스에서 사용되며, 실제로 CREATE TABLE에 지정하는 이름으로 '물리명'이라 한다.

또 하나는 테이블의 '설계상 이름'에 해당하는 '논리명'이다.

물리명은 길이 제한이나 공백문자 등의 제약이 있거나 전통적으로 알파벳을 사용하고,

때론 생략하거나 약자로 이름을 붙이는 경우도 많아 의미가 전달되지 않는 경우가 생겨 논리명이 필요하다.

여기서 차이점은, 물리명의 경우 CREATE TABLE 명령으로 테이블을 작성할 때 사용하는 이름이라면,

 

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

물리명은 잘못 정하면 변경하기 힘들지만 논리명은 언제나 바꿀 수 있다.

 

EX) 물리명 'item_buyer', 논리명 '상품 구매자'

 

 

기본키

테이블의 행에 유일성을 지정하는 것은 대단히 중요한 일이다.

하지만 기본키로 지정할 열이 않날 경우 자동 증가 열을 사용해 기본키로 지정하면 해결할 수 있다.

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

MySQL의 경우 열을 정의할 때 AUTO_INCREMENT를 지정하면 자동 증가 열이 되며,

AUTO_INCREMENT로 지정한 열은 PRIMARY KEY 또는 UNIQUE로 유일성을 지정해야 한다.

테이블 생성

쿼리 결과

 

ER 다이어그램

ER 다이어그램은 테이블을 설계할 때 테이블 간 관계를 명확히 하기 위해 설계도를 작성하는 경우 사용된다.

ER 다이어그램은 개체 간의 관계를 표현한 것이다.

개체란 엔티티, 즉 테이블 또는 뷰를 말하는 것으로 ER 다이어그램은 개체를 사각형으로 표현하는데 사각형의 상단에는 개체 이름을 적고 사각형 안에는 개체의 속성(테이블의 열)을 표기한다.

기본키가 되는 열부터 차례로 기술하며, 열 이름은 주로 논리명으로 표기한다.

​

엔티티끼리 관계를 맺는 모습으로 1:1 관계

어느 엔티티 쪽에서 상대 엔티티와 반드시 단 하나의 관계를 가지는 것을 말한다

관계를 맺은 엔티티가 여러 객체를 갖을 수 있다 1:N 관계

부모 테이블(1)에서는 내 자식들이 누구인지 정보를 넣을 필요가 없고, 
자식 테이블(N)에서만 각각의 자식들이 자신의 부모 정보(FK)를 넣음 으로써 관계를 표현할 수 있다.

 

N : M 관계

양쪽 엔티티 모두에서 1:N 관계를 가지는 것을 말함 => 서로가 서로를 1:N 관계로 보고 있는 것이다.

예를들어, 학원과 학생의 관계를 생각해보면, 한 학원에는 여러명의 학생이 수강할 수 있으므로 1:N 관계를 가진다.

반대의 경우인 학생도 여러개의 학원을 수강할 수 있어, 이 사이에서도 1:M 관계를 가진다.

개체끼리 서로 연계되는 경우에는 선으로 이어서 표현한다.

연계를 표현할 때는 서로 몇 개의 데이터 행과 연관되는지, 즉 몇 대 몇의 관계를 가지는지를 숫자나 기호로 나타낸다.

 

N:M 관계는 서로가 서로를 1:N 관계, 1:M 관계로 갖고 있기 때문에, 서로의 PK를 자신의 외래키 컬럼으로 갖고 있으면 된다.

 

직관적인 관계를 파악할 수 있는 MySQL ; employee 테이블

여기에서 Identifying(실선) 과 Non-identifying(점선) 관계 두가지로 나뉜다.

위의 경우로 예를 들자면, 직원 테이블 없이 부서 사원 테이블, 부서 매니저 테이블, 연봉 테이블등이 존재할 수 없으므로 실선으로 연결한다.

이와 반대로 각각의 테이블이 독립적으로 존재할 수 있다면 점선으로 연결하면 된다.

 

연결부분

테이블 제목 혹은 테이블 연결선에 마우스를 위에 가져다대면,  관계를 맺고 있는 컬럼에 대해서 하이라이트 처리가 됩니다.

 

 

 

정규화

 

정규화란, 데이터베이스(DB)의 테이블을 규정된 올바른 형태로 개선해나가는 것으로 정의할 수 있다.

 

쿼리 결과

 

주문번호	날짜	이름	연락처	주문상품
1	10/1	홍길동	010-1234-5678	0001 A 2개, 0002 B 3개
2	11/1	김준	010-5344-2313	0001 A 3개, 0002 B 5개
3	12/1	홍길동	010-1234-5678	0001 A 9개, 0003 C 7개

 

 

이 테이블을 보면 주문상품 부분이 특별한 형식 없이 대충 만들어진 것처럼 보인다.

​

제1정규형

 

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

주문번호	날짜	이름	연락처	상품코드	상품명	개수
1	10/1	홍길동	010-1234-5678	0001	A	2개
1	10/1	홍길동	010-1234-5678	0002	B	3개
2	11/1	김준	010-5344-2313	0001	A	3개
2	11/1	김준	010-5344-2313	0002	B	5개
3	12/1	홍길동	010-1234-5678	0001	A	9개
3	12/1	홍길동	010-1234-5678	0003	C	7개

 

이렇게 하나의 셀에 하나의 값만 저장할 수 있도록 하고,

반복되는 부분을 세로(행) 방향으로 늘려나가는 것이 제1정규형의 제1단계이다.

이 테이블의 이름을 '주문' 테이블이라 하겠다.

​

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

주문 테이블에선 주문번호, 날짜, 성명, 연락처가 중복되는 경우가 생긴다.

 

 

[주문테이블]

주문번호	날짜	이름	연락처
1	10/1	홍길동	010-1234-5678
2	11/1	김준	010-5344-2313
3	12/1	홍길동	010-4324-0032

 

 

[주문상품 테이블]

주문번호	상품코드	상품명	개수
1	0001	A	2개
1	0002	B	3개
2	0001	A	3개
2	0002	B	5개
3	0001	A	9개
3	0003	C	7개

 

이처럼 주문 테이블을 분해해 각각 주문 테이블과 주문상품 테이블로 나눈다.

주문 테이블과 주문상품 테이블은 주문번호로 연계되어 있다.

주문 테이블의 경우 주문번호를 기본키로 활용하고,

주문상품 테이블은 주문 번호와 상품코드를 한데 묶어 기본키로 지정할 수 있다.

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

 

 

제2정규형

제2정규화에서는 데이터가 중복하는 부분을 찾아내어 테이블로 분할해 나간다.

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

주문상품 테이블을 다시 보면, 개수의 경우 주문번호와 상품코드를 통해 특정되는 데이터이다.

하지만 상품명의 경우 상품코드로는 특정되지만 주문번호로는 특정되지 않다.

여기서 상품코드 단독으로는 기본키 역할을 할 수는 없기 때문에 분할이 이루어진다.

정리하자면, 개수는 기본키로 특정되며 상품명은 기본키의 일부로 특정되기 때문에 상품명 열을 분할한다.

 

 

[상품 테이블]

상품코드	상품명
0001	A
0002	B
0003	C

 

 

[주문 상품 테이블]

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

 

상품 테이블에서는 상품코드만 기본키로 지정한다.

이처럼 부분 함수종속성을 찾아내서 테이블을 분할하는 것이 제2정규화이다.

여기서 함수종속성이란 키값을 이용해 데이터를 특정 지을 수 있는 것을 가리킨다.

 

 

제3정규형

 

 

제3정규화 또한 중복하는 부분을 찾아내어 테이블을 분할하는 수법이다.

제2정규화의 경우에는 기본키에 중복이 없는지 조사했지만

제3정규화에서는 기본키 이외의 부분에서 중복이 없는지를 조사한다.

​

주문 테이블을 다시 살펴보면, 같은 사람이 여러 번 주문하는 경우가 있어 데이터가 중복되어 있다.

따라서 이름을 기준으로 데이터를 분할하는데, 이름은 기본키와 관계가 없다.

 

[주문 테이블]

주문번호	날짜	고객번호
1	10/1	1
2	11/1	2
3	12/1	1

 

 

[고객 테이블]

고객번호	성명	연락처
1	홍길동	010-1234-5678
2	김준	010-5344-2313

 

고객이라는 새로운 테이블을 만들었는데, 앞서 말했듯이 이름은 주문번호와 관계가 없으며 이름을 기본키로 지정할 경우 중복이 발생하므로 새롭게 고객번호라는 기본키를 만들었다.

 

이렇게까지 테이블을 분할하면 정규화가 마무리된다.

​

마지막으로, 정규화한 후의 테이블을 정리해보면

주문 -> 주문, 주문상품, 상품, 고객 테이블 4개로 분할한 것을 확인할 수 있다.

 

분할할 때에는 서로 결합할 수 있도록 기본키를 추가해 분할한다.

테이블 간의 연계는 ER다이어그램으로 표현하면 알기 쉽다.

실제로는 제5정규형까지 있는데 대부분의 시스템에서는 제3정규형까지의 정규화를 채택한다.

 

 

정규화의 목적

정규화에서는 중복하거나 반복되는 부분을 찾아내서 테이블을 분할하고 기본키를 작성해 사용하는 것을 기본 개념으로 삼는다.

 

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

​

하나의 데이터가 반드시 한 곳에만 저장되어 있다면 데이터를 변경하더라도 한곳만 변경하는 것으로 끝낼 수 있다.

반면 정규화되지 않은 경우에는 여기저기 중복해서 저장된 데이터를 검색하고, 일일이 변경해야 하므로 매우 번거로운 작업이 된다.