8. 데이터베이스 설계

34. 데이터베이스 설계

 

데이터베이스 설계

터이터베이스의 스키마 내에 테이블, 인덱스, 뷰 등의 데이터베이스 객체를 정의, 테이블 간의 관계를 고려해 테이블 이름, 열, 자료형을 결정

스키마 내에 정의한다는 의미에서 '스키마 설계'라고 부르기도 함

 

| 물리명: CREATE TABLE에 지정하는 테이블 이름아니 열 이름

 

| 논리명: 설계상의 이름

 

| 수치 자료형: 금액이나 개수와 같은 수치 데이터만 다룰 수 있는 열

 

| 문자열형: 제조번호와 같이 알파벳을 다루는 데이터

 

LOB(Large Object)

문자열형에서 주로 사용하는 VARCHAR형보다 담을 수 있는 용량이 큰 문자열

단, 인덱스를 지정할 수 없다는 제약이 있음

 

AUTO_INCREMENT

기본키로 지정할 열이 생각나지 않는 경우 자동증가 열을 사용해 기본키 지정 가능

자동증가 열을 사용하는 경우 데이터를 INSERT할 때 자동으로 번호를 증가

단, 해당 열은 PRIMARY KEY 혹은 UNIQUE로 유일성을 지정해야 함

 

ER다이어그램

테이블 간의 연계를 나타낼 때 사용하는 도식으로 E는 'Entity(개체)', 'Relationship(관계)'를 의

그림1) ERD 표기법

 

| 사각형: Entity, 테이블 또는 뷰를 의미

* 사각형 상단에는 개체이름을 적고 사각형 내부에는 테이블 열인 개체의 속성을 표시

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

 

| 선: 개체와 개체가 서로 연계되는 경우 선으로 이어서 표현하며 서로 몇 개의 데이터 행과 연관되는지 그 관계를 숫자나 기호로 표기

* 대표적으로 One-to-One, One-to-Many, Many-to_Many

 

35. 정규화

 

정규화

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

데이터베이스 설계 단계에서 행해지며 경우에 따라 기존 시스템을 재검토할 때 정규화하는 경우도 있으며 관계형 데이터베이스가 효육적으로 동작하도록 도움

 

제1정규형

제1정규형 테이블을 만드는 방법

: 하나의 셀에 하나의 값만 저장할 수 있도록하고, 반복되는 부분은 세로방향으로 증가

* 테이블을 분할, 반복 부분 제거, 기본키 지정이 이루어짐

 

ex) 이용자별로 주문을 하나로 정리하는 예제

주문번호	날짜	이름	연락처	주문상품
1012	2024-11-02	Daniel	82+) 1000-0000	XX도서

<주문 데이터>

 

주문 열에 있는 값들을 상품코드와 상품명, 개수 데이터를 담는 3개의 열로 나누어 표현

주문번호	날짜	이름	연락처	상품코드	상품명	개수
1012	2024-11-02	Daniel	82+) 1000-0000	00112	XX도서	2

<주문상품을 세 개의 열로 분할>

 

한번의 주문으로 여러 개의 상품을 주문할 수 있어 주문번호, 날짜, 성명, 연락처가 동일한 값을 가지는 여러 행을 하나로 정리

이때, 주문 테이블을 주문상품테이블과 주문 테이블로 나누어 정리하며 주문 상품 테이블에는 주문번호 열을 추가

 

| 수정 용이: 기존 데이터가 변경되더라도 한 군데만 수정하면 변경 가능

 

| 기본키 지정: 기존 테이블에서 주문 번호와 상품코드를 묶어 기본키로 지정 가능

 

제2정규형

데이터가 중복하는 부분을 찾아내어 테이블을 분할해 나가는 방법, 부분 함수종속성을 찾아내 테이블을 분할하는 것

이때, 기본키에 의해 특정되는 열과 그렇지 않은 열로 나눈는 것으로 정규화

*함수 종속성: 키 값을 이용해 데이터를 특정지을 수 있는 것

주문번호	상품코드	상품명	개수
1012	00112	XX도서	2
2029	01364	OO향수	1

<주문상품 테이블>

 

상품명을 기본키의 일부로 특정해 분할

주문번호	상품코드	개수
1012	00112	2
2029	01364	1

<주문상품 테이블>

상품코드	상품명
00112	XX도서
01364	OO향수

<상품 테이블>

 

상품코드만으로 기본키 지정

 

제3정규형

중복하는 부분을 찾아내 테이블을 분할하는 방법으로 기본키에 중복되는 데이터 여부를 조사한 제2정규화와 다르게 기본키 이외의 부분에서 중복 데이터의 여부를 조사

 

정규화의 목적

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

하나의 데이터는 한 곳에 있어야 한다는 규칙에 근거하며 정규화를 통해 테이블을 분할할 대는 서로 결합할 수 있도록 기본키를 추가해 분할

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

 

- 하나의 데이터가 반드시 한 곳에만 저장되는 경우 데이터를 변경하더라도 한 곳만 변경

: 중복된 데이터를 검색해 일일이 변경하는 과정 간소화, 인덱스가 지정된 열의 경우 인덱스 재구축 간소화

 

- 기본키는 분할한 테이블끼리 연계하기 위해 작성한 내부적인 데이터이므로 변경될 일이 거의 없음

: 정규화를 통해 인덱스의 재구측 억제 가능

 

36. 트랜잭션

 

트랜잭션

 

ex) 발주처리

mysql> INSERT INTO 주문 VALUES(4, '2024-10-31', 1);
mysql> INSERT INTO 주문상품 VALUES(4, '01120', 2);
mysql> INSERT INTO 주문상품 VALUES(4, '01123', 3);

* 세 번째 INSERT 명령에서 에러가 발생할 때, 트랜잭션을 사용하지 않는 경우 앞서 시행한 INSERT 명령에 의해 추가된 데이터를 DELETE로 삭제 필요

 

롤백(Rollback)

몇 단계로 처리를 나누어 SQL명령을 실행하는 경우 트랜잭션을 자주 사용하지만 에러가 발생하는 경우 롤백으로 종료 가능

: 행해진 모든 변경사항을 없었던 것으로 되돌리는 역할

 

커밋(Commit)

에러가 발생하지 않는 경우 변경사항을 적용하고 트랜잭션을 종료

 

START TRANSACTION

트랜잭션 선언: MySQL 클라이언트 명령 실행에는 자동커밋이 켜져 있지만 이를 끄기 위해서는 명시적으로 트랜잭션 시작 선언 필요

트랜잭션을 종료할 때, 변경 내용을 적용 후 종료: commit, 적용하지 않고 종료: rollback 사용

mysql> START TRANSACTION
mysql> INSERT INTO 주문 VALUES(4, '2024-11-02', 1);
mysql> INSERT INTO 주문상품 VALUES(4, '1102', 2);
mysql> INSERT INTO 주문상품 VALUES(3, '1223', 3);
mysql> COMMIT;

* 트랜잭션을 걸어서/내어서 실행한다: 트랜잭션을 시작해 SQL 명령 실행 후 COMMIT, ROLLBACK명령으로 트랜잭션 종료

 

| MySQL: START TRANSACTION, BEGIN

 

| SQL Server, PostgreSQL: BEGIN TRANSACTION

 

| Oracle, DB2: 트랜잭션 시작 명령이 따로 없으며 표준화 진행 미비

 

자동커밋은 클라이언트 툴의 기능으로 미들웨어, 데이터베이스 접속 시 대체로 자동커밋

단, 데이터베이스 서버에서는 언제타 트랜잭션을 걸 수 있는 상태로 SQL 명령이 실행되고 DELETE 명령 역시 트랜잭션 내에서 실행하는 경우 ROLLBACK으로 삭제 취소 가능