SQL 첫걸음 8장
34. 데이터베이스 설계
데이터베이스 설계
데이터베이스의 설계 : 데이터베이스의 스키마 내에 테이블 / 인덱스 / 뷰 등의 데이터베이스 객체를 정의하는 것
스키마 내에 정의한다는 의미로 스키마 설계라고도 함
테이블을 설계할 때는 테이블 정의서나 설계도 등의 문서를 작성하는 경우가 많음
테이블 정의서는 DESC명령에 따라 표시되는 결과를 그대로 옮겨적은 것이라 봐도 무방하며, 어느 열이 기본키인지를 지정하는 경우도 있음
하나의 테이블에 두 개의 이름을 지정할 때도 있음
- 물리명 : 데이터베이스에서 실제로 사용되는 이름
CREATE TABLE에 지정- 데이터베이스 시스템 규칙에 따라 길이나 공백 사용금지등의 제약이 따르며 전통적으로 알파벳을 사용해 이름을 지정하며 약자로 붙이는 경우도 많음
- 논리명 : 테이블의 설계상 이름
- 물리명만으로는 의미가 전달되지 않는 경우가 많아 논리명이 필요함
- 해당 테이블을 실제로 부를 때 사용하며, 언제나 바꿀 수 있음
경우에 따라서는 물리명과 논리명을 테이블 설계도나 정의서에 함께 기재하기도 함
테이블의 열에는 자료형을 지정해야함
- 수치 데이터만 저장하는 열은 수치형으로 지정
- 알파벳 등의 문자도 다룰 경우 문자열형으로 지정하며, 수치형을 문자형으로 변환하는 것은 문제가 없지만 그 반대는 에러가 발생하기도 함
- 데이터에 따라 취급하는 값이 한정되어있는 경우 제약을 걸어 데이터 정합성을 확보함
- 일반적으로는 데이터베이스 시스템에서 데이터 정합성을 체크할 수 있는 경우 데이터베이스에 맡기는 편이 가장 확실하고 편리함
문자열의 자료형에는 고정길이와 가변길이가 있으며, 어느 쪽을 선택할 것인지는 저장할 데이터에 따라 결정함
데이터베이스의 열에 저장할 수 있는 크기는 한정적임
따라서 큰 파일을 저장하기 위해서는 LOB(Large Object)형을 사용함
단, LOB형은 인덱스를 지정할 수 없음
테이블 작성시 기본키 제약을 거는 경우에는 주의를 기울여야함
기본키로 지정할 열이 생각나지 않는 경우 자동증가 열을 사용해서 기본키로 지정하여 간단하게 해결할 수 있음
- 자동증가 열 :
INSERT할 경우 번호를 자동으로 증가시켜 저장해주는 열 - MySQL의 경우 열 정의시
AUTO_INCREMENT를 지정하여 자동증가 열으로 만듬 - 자동증가 열으로 지정된 열은
PRIMARY KEY또는UNIQUE로 유일성을 지정해야 함
ER다이어그램
ER다이어그램 : 테이블을 설계할 때 테이블간의 관계를 명확히 하기 위해 작성하는 도식
- E는 개체를 뜻하는 Entity, R은 Relationship의 약자
- 따라서 ER다이어그램은 개체간의 관계를 표현한 다이어그램이며, 이 때의 관계는 관계형 데이터베이스의 릴레이션이 아닌 릴레이션십(연계)임
개체는 테이블 또는 뷰를 말함
ER다이어그램의 개체는 사각형으로 표기하며, 사각형 산단에는 개체 이름 / 사각형 안에는 개체의 속성을 표기함
- 이 때의 속성은 테이블의 열을 의미함
- 기본키가 되는 열부터 차례로 기술하며, 열 이름은 주로 논리명으로 표기함
- 개체끼리 연계되는 경우에는 선으로 이어서 표현
- 특정한 열이 몇 개의 데이터 행과 연관되는지를 숫자나 기호로 나타낼 수 있으며, 이를 카디널리티 또는 다중도라고 함
- 일대일(1:1)
- 일대다(1:多)
- 다대다(多:多)
ER다이어그램의 연계는 데이터베이스에서 외부키 제약으로 지정되는 경우가 있음
외부키 제약을 설정하면 데이터의 정합성이 엄격히 관리되어 번거로워지기 때문에 이를 채용하지 안흔 시스템도 있음
35. 정규화
정규화 : 테이블을 올바른 형태로 변경하고 분할하는 것, 즉 데이터베이스의 테이블을 규정된 올바른 형태로 개선해나가는 것
- 데이터베이스의 설계 단계에서 행해짐
- 경우에 따라 기존 시스템의 재검토시 정규화하는 경우도 있음
정규화
제1정규화 : 반복되는 데이터를 열 방향이 아닌 행 방향으로 늘리는 것이 1단계
중복을 제거하기 위해 테이블을 분할함
중복을 제거했기 때문에 기본키를 지정할 수 있음
제2정규화 : 제1정규화에서 기본키를 작성한 것과 마찬가지로 데이터가 중복하는 부분을 찾아내어 테이블로 분할해나감
즉, 부분 함수종속성을 찾아나여 테이블을 분할함
함수종속성 : 키 값을 이용해 데이터를 특정지을 수 있는 것
제3정규화 : 제2정규화에서 기본키에 중복이 없는지 조사했다면 제3정규화에서는 기본키 이외의 부분에서 중복이 없는지를 조사함
실제로는 제5정규형까지 있으나 대부분의 시스템에서 제3정규형까지의 정규화를 채택함
정규화의 목적
정규화는 중복되거나 반복되는 부분을 찾아내서 테이블을 분할하고 기본키를 작성해 사용하는 것을 기본 개념으로 삼음
즉, 하나의 데이터는 한 곳에 있어야 한다는 규칙에 근거함
하나의 데이터가 한 곳에만 저장되어있을 경우 데이터를 변경하더라도 한곳만 변경하는 것으로 끝낼 수 있기 때문에 편리
정규화되지 않은 경우 데이터 변경시 중복된 데이터들을 일일히 검색하고 변경하여야 하며, 인덱스가 지정된 열의 데이터가 변경되는 경우에는 인덱스도 재구축해야함
그러나 기본키는 분할한 테이블끼리의 연계를 위해 작성한 내부적인 데이터이므로 변경될 일이 거의 없으므로 정규화를 통해 테이블에 대한 인덱스의 재구축을 억제할 수 있음
36. 트랜잭션
트랜잭션
주문 테이블 : 주문번호 / 날짜 / 고객번호 주문상품 테이블 : 주분번호 / 상품코드 / 개수
주문 테이블과 주문상품 테이블 사이에는 의존관계가 존재
새로운 주문이 발생한 경우 주문 테이블에 INSERT 한번, 주문상품 테이블에는 주문된 상품 수만큼 INSERT명령이 실행됨
이 때 복수의 테이블에 INSERT되므로 명령도 여러번 실행되는데, 이 때 명령이 어떠한 원인으로 인해 에러가 발생한 경 실행 전으로 돌아가기 위해 앞서 문제없이 실행된 INSERT명령들을 DELETE명령을 실행해 지워야 함
이런 번거로운 작업을 트랜잭션을 통해 방지할 수 있음
몇 단계로 처리를 나누어 SQL 명령을 실행하는 경우에 트랜잭션을 자주 사용함
롤백과 커밋
트랜잭션을 사용하여 데이터를 추가할시 에러가 발생하면 트랜잭션을 롤백(rollback)하여 종료할 수 있음
롤백시 트랜잭션 내에서 행해진 모든 변경사항을 없었던 것으로 만듬
에러가 발생하지 않으면 변경사항을 적용하고 트랜잭션을 종료하는 커밋(commit)을 사용함
MySQL 클라이언트에서 명령을 실행할 때는 자동커밋이 켜져있어 INSERT / UPDATE / DELETE가 처리될 때마다 트랜잭션은 암묵적으로 자동커밋됨
자동커밋을 끄기 위해서는 START TRANSACTION명령으로 명시적으로 트랜잭션의 시작을 선언해야함
이후 커밋을 원할시에는 COMMIT, 롤백을 원할시엔 ROLLBACK명령을 사용함
트랜잭션을 시작해서 SQL 명령을 실행하고 처리하는 일련의 방법을 트랜잭션을 걸어서 실행 또는 트랜잭션 내에서 실행이라고 말함
트랜잭션 사용법
트랜잭션 내에서 실행하는 복수의 SQL명령은 세트 단위로 유효/무료처리되기 때문에 반드시 세트로 붂어서 실행해야함
따라서 하나의 명령에 트랜잭션을 걸어 실행하는 것은 별로 의미가 없음
MySQL : START TRANSACTION 또는 BEGIN
SQL Server / PostgreSQL : BEGIN TRANSACTION
Oracle / DB2 : 트랜잭션을 시작하는 명령어가 따로 존재하지 않음
자동커밋은 클라이언트 툴의 기능이며, 데이터베이스 서버에서는 언제나 트랜잭션을 걸 수 있는 상태로 SQL명령이 실행됨
트랜잭션을 사용할 경우에는 접속형태나 클라이언트 툴의 자동커밋 사용 여부 등 트랜잭션 관련 기능을 파악해 둘 필요가 있음