[55] 230317 SQL Ch. 8 데이터베이스 설계: 34강 데이터베이스 설계, 35강 정규화, 36강 트랜잭션 [K-디지털 트레이닝 55일]

230317 Thu 55th class

Ch. 8 데이터베이스 설계
진도: p.  312 ~ (교재: SQL 첫걸음, 저자: 아사이 아츠시)
MySQL Workbench Manual: https://dev.mysql.com/doc/workbench/en/
MySQL 튜토리얼: https://www.tutorialspoint.com/mysql/
SQLD 요약 pdf: https://yurimac.tistory.com/40
책 요약 (9p) pdf: https://brunch.co.kr/@ydy702/10

SQL 첫걸음 | 아사이 아츠시 - 교보문고

오늘 배운 것 중 기억할 것을 정리했다.

---

다시 기억할 것

데이터 유형

- CHAR(s): 고정 길이 문자열 정보, 최대 길이만큼 공간 채움 ‘AA’ = ‘AA ’
- VARCHAR(s) : 가변 길이 문자열 정보, 할당된 변수 값의 바이트만 적용 ‘AA’ != ‘AA’
- NUMBER: 정수, 실수 등 숫자 정보
- DATE: 날짜와 시각 정보

no	a	b	newcol
1	10ABC	2014-01-01	NULL
2	20ABC	2014-01-02	NULL

2023.03.10 - [🌳K-Digital Revision 2023✨/SQL, MariaDB] - [50] 230310 [K-디지털 트레이닝 50일] SQL Ch. 6 데이터베이스와 SQL: 25강 데이터베이스 객체, 26강 테이블 작성, 삭제, 변경, 27강 제약, 28강 인덱스 구조, 29강 인덱스 작성과 삭제, 30강 뷰 작성과 삭제

---

Ch. 8 데이터베이스 설계

책의 목차
__34강__데이터베이스 설계
1. 데이터베이스 설계
2. ER다이어그램

__35강__정규화
1. 정규화
2. 제1정규형
3. 제2정규형
4. 제3정규형
5. 정규화의 목적

__36강__트랜잭션
1. 트랜잭션
2. 롤백과 커밋
3. 트랜잭션 사용법

34강 데이터베이스 설계

테이블 ID				테이블명
데이터베이스명
한글 칼럼명 (논리명)	영문 칼럼명 (물리명)	타입	길이	PK 구분	Not Null 구분	연관테이블 (외래키)

 An Entity Relationship Diagram(ERD)
: 1:1관계, 1:다 관계인지 이미지화해서 보여준다.

ERD툴
참고: https://gitmind.com/kr/er-diagram-tool.html

ERD 분석을 하면 업무가 보인다. 

예시)

- 1명의 회원(member)이 여러 개의 게시글(board)을 쓸 수 있다.

- 하나의 게시글(board)에 여러 개의 댓글(reply)을 쓸 수 있다. 

참고:

2023.03.17 - [🌳K-Digital Revision 2023✨/SQL, MariaDB] - [54~55] 230316, 230317 [K-디지털 트레이닝 54~55일] SQL 문제 풀이 (member, board 테이블)

---

34.1 데이터베이스 설계

데이터베이스를 설계한다: 데이터베이스의 스키마 내에 테이블, 인덱스, 뷰 등의 데이터베이스 객체를 정의하는 것을 말한다.
데이터베이스 설계의 주된 내용: 테이블의 이름이나 열, 자료형을 결정하는 것이다.

1) 논리명과 물리명 

Point: 물리명은 CREATE TABLE에 지정하는 테이블 이름이나 열 이름이다. ex) 'item_master'
논리명은 설계상의 이름이다. ex) '상품 마스터'

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

열명	논리명	자료형	NULL	기본값	비교
item_code	상품코드	CHAR(4) (고정길이)	No
item_name	상품명	VARCHAR(30) (가변길이)	Yes
price	가격	INTEGER	Yes

---

2) 자료형

---

3) 고정길이와 가변길이

Point: 큰 데이터는 LOB(Large Object) 형으로 저장할 수 있다.

---

4) 기본키

---

34.2 ER다이어그램

erd(개체 관계도) 관계선 종류

실선: 부모테이블pk가 자식 테이블 fk가 되는 경우
점선: 부모테이블pk가 자식테이블의 일반속성이 되는 경우

erd 기호의 종류
|: 1개
<: 여러개
o: 0개

기호 설명

참고: https://sum0725.tistory.com/12

아래 이미지에선 선이 설명과 반대로 됨.

아래 이미지에선 선이 설명과 반대로 됨.

 

MySQL에서 EER Diagram을 만든다.
참고: https://www.webucator.com/article/how-to-create-an-er-diagram-for-a-mysql-database-w/ 

참고

---

35강 정규화

35.1 정규화
DB 설계단계에서 테이블을 분할 → 데이터가 한 곳에만 저장되도록

---

35.2 제1정규형

Point: 반복되는 데이터를 가로(열 방향)가 아닌 세로(행 방향)로 늘리는 것이 제1정규화의 제1단계이다. 
Point: 제1정규화에서는 테이블 분할과 기본키 지정이 이루어진다.

---

35.3 제2정규형
제 2 정규화 : 부분 함수 종속성을 찾아 테이블분할 (PK에서 중복제거 )

---

35.4 제3정규형
제 3 정규화 : PK가 아닌 일반 칼럼에서 중복 제거

---

35.5 정규화의 목적

Point: 정규화로 데이터 구조를 개선하는 것은 하나의 데이터가 한 곳에 저장되도록 하기 위함이다.

---

36강 트랜잭션

트랜잭션은 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적인 단위로 사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정의 작업 단위이다. 하나의 트랜잭션은 커밋(commit) 되거나 롤백이 된다.

In short, a database transaction is a sequence of multiple operations performed on a database, and all served as a single logical unit of work — taking place wholly or not at all. In other words, there’s never a case that only half of the operations are performed and the results saved. 

트랜잭션의 제어

START TRANSACTION
COMMIT
ROLLBACK

ex) 게시글 클릭했을 때 클릭과 업데이트가 동시에 진행된다.
아래로 묶는다.
둘 다 실행 또는 둘 다 실행X 

COMMIT: 업데이트하라
ROLLBACK: 원래대로 돌아가라 

트랜잭션
두 개 이상의 sql을 동시다발로 실행하고자 할 때 사용

게시판 제목을 클릭
select * from board where no = 1125;
update board set count=count+1 where no = 1125

start transaction
select * from board where no = 1125;
update board set count = count+1 where no = 1125

select와 update가 둘 다 성공했으면 commit
select와 update가 둘 중 하나라도 실패했으면 rollback 실행 (원래 데이터로 돌아간다.)

---

36.1 트랜잭션
- 발주 처리
예제 8-1) 발주처리

INSERT INTO 주문 VALUES(4, '2014-03-01', 1);
INSERT INTO 주문상품 VALUES(4, '0003', 1);
INSERT INTO 주문상품 VALUES(4, '0004', 2);

---

36.2 롤백과 커밋

Point: 트랜잭션을 롤백(rollback)하면 변경한 내용이 적용되지 않는다. 

- 자동커밋 

트랜잭션 시작

START TRANSACTION

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

COMMIT

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

ROLLBACK

예제 8-1) 트랜잭션 내에서의 발주처리

START TRANSACTION;
INSERT INTO 주문 VALUES(4, '2014-03-01', 1);
INSERT INTO 주문상품 VALUES(4, '0003', 1);
INSERT INTO 주문상품 VALUES(4, '0004', 2);
COMMIT;

 

Point: 트랜잭션을 사용해서 처리하는 것으로 간단히 데이터를 관리할 수 있다.

use example;
alter table board modify count int default 0;
desc board;
select * from board where no = 5; -- title 클릭하면,
-- update board set count=0;
update board set count=count+1 where no=5;

start transaction;
select * from board where no = 5; -- title 클릭하면,
update board set count = count+1 where no=5;
-- 정상적으로 select랑 update가 됨: 정상처리 해줘야 함.(commit) 
commit;
-- update,는 정상적으로 수행되었으나, select가 제대로 작동하지 않았음.: count값을 원래대로 되돌려 줘야 함.
rollback;

처음에 no = 3으로 했었는데 ROLLBACK 코드 실행하면서 지워졌다. 

---

36.3 트랜잭션 사용법 p. 333

반드시 세트로 실행하고 싶은 SQL 명령을 트랜잭션에서 하나로 묶어 실행한다.
에러가 발생하지 않아도 ROLLBACK을 하면 변경한 내용은 파기된다.
에러가 발생하더라도 COMMIT을 하면 문제없이 실행된 SQL 명령의 변경사항은 데이터베이스에 그대로 반영된다.

Point: 세트로 실행하고 싶은 SQL 명령은 트랜잭션 내에서 실행한다.

---