[데이터베이스] MySQL을 통해 데이터베이스 설계를 위한 기초 내용 정리

MySQL로 데이터베이스를 다루는 기초적인 내용에 대해 정리하는 글

 

목차

 

---

데이터베이스(Database)

---

1. 데이터베이스 설계

• 데이터베이스 설계란 데이터베이스 스키마, 테이블, 인텍스, 뷰 등의 데이터베이스 객체를 정의하는 일련의 과정
• 데이터베이스의 설계의 주된 내용은 테이블(컬럼, 자료형, 제약조건 등)과 테이블의 관계성을 연결
• ERD를 활

1.1. 논리명과 물리명

논리명 : 이패도 높은 도메인에서 사용하는 명칭을 적용

물리명 : 사용하는 DBMS 규칙에 맞게 작성해야 하므로 축약된 명칭을 사

 

1.2 자료형

• 테이블의 자료형은 고정된 Type
• ex.
  • 수치 : 수치형
  • 코드 : 문자형
  • 일자 : date형 또는 문자형
• 문자형은 변화가 없을 경우 char로 변화 예상시 varchar
• 1,2,3 또는 'yes' or 'no' 등 정해진 값을 넣어야하는 경우 제약조건을 설정하는 것이 효율적
• 데이터는 향후 사용성을 고려하여 raw data를 입력하고 select 명령어 사용시 필요한 데이터형과 길이로 조정하여 사용하는 것을 권장 (데이터 입력시 품질을 고려)

 

1.3. 고정길이와 가변길이

• 문자형의 자료에는 길이가 고정되는 테이터형과 가변길이 테이터형이 존재(char, varchar 등)
• 코드와 같이 문자의 길이가 변하지 않는 자료는 고정길이형, 그 이외에는 가변길이형
• 고정길이 문자형은 상대적으로 빠른 속도를, 가변길이 문자형은 상대적으로 저장의 효율성을 장점으로 들고 있으나 DBMS에 따라 다를 수 있음
• 고정길이형으로 많이 사용하는 varchar 데이터타입은 최대 길이가 충분하지 않을 경우 LOB(large object)를 사용
• 그러나 LOB는 인텍스가 적용 되지 않음

 

1.4. 기본키 primary 키

• primary 키는 컬럼의 유일성을 확보받고 DBMS에 따라 자동으로 인텍스가 설정되기도 함
• 기본키의 유일성을 DBMS측면에서 확보하기 위해서는 auto_increment 제약조건으로 primary key를 설정
• 기본키의 유일성 확보를 위하여 응용시스템에서 컨트롤 할 수 있으나, 추천하는 방법은 아님
• 기본키는 다른 테이블에 외래키로 사용

1.5. ER 다이어그램

• E : entity 개체
• R : relationship 관계
• ERD : 개체간의 관계를 도식화한 자료
•  RDB는 relation을 기반으로 하는데, 이를 테이블로 구현
• entity는 사각형으로 구성되고 속성, 기본키 구성
• 주로 속성 등은 논리명으로 표기

---

• entity와 entity간에 relationship은 연결선으로 표시
• relationship의 관계성은 카디널리티 또는 다중도를 추가하여 관계성을 명확하게 표시
• 1:1, 1:n, n:m
• relationship으로 생기는 외래키는 필요에 따라 명시

 

---

2. 정규화 (normalization)

• 정규화 normalization은 데이터베이스내 데이터의 일관성과 이상현상을 방지하기 위하여 수행하는 데이터베이스의 설계단계의 과정
• 기존에 운영중이 데이터베이스의 개선을 위해서도 수행

→ 정규화되지않은 테이블은 여러 개의 중복 데이터를 보여서 가독성이 안좋을 수있고

여러 번의 수정을 거쳐야 할 수 있음.

2.1. 1차 정규화

• 하나의 컬럼에 하나의 정보만 저장

 

2.2. 2차 정규화

테이블의 분할을 통해 진행

기본키 중에 특정 컬럼에만 종속된 컬럼이 존재할 경우 이를 제거해주기 위한 과정이라고 생각

ex . 어떤 고객들이 주문한 상품들에 대한 테이블

→ 고객 정보와 주문상품으로 분리

→ 주문 테이블은 기본키의 생성이 가능하고 join을 통하여 필요한 데이터의 조회 가능

 

부분 함수종속성이 제거되어 완전 함수종속성을 확보시켜주는 것으로 생각.

2.3. 3차 정규화

2차 정규화는 primary key에서 중복이 없는지를 판단한것

3차 정규화는 나머지 부분에서의 종속성을 판단하여 테이블을 분할

 

2.4. 정규화의 목적

• 중복되거나 반복되는 부분을 찾아 테이블을 분할하고, 기본키와 외래키를 이용한 결합(Join)
• “하나의 데이터는 한 곳에 있어야 함”을 통하여 데이터의 변경이 있을때 여기저기 변경되는 어려움을 해소
• 이를 통하여 데이터의 integrity를 유지

 

반정규화, 비정규화, 4차 5차 정규화 등등 

 

---

3. Transaction

• 데이터 처리의 단위로써 insert나 update 명령어에서도 자동 커밋이 발생하여 Transaction이 수행되었음

start transaction

commit
rollback

• Commit
  • start transaction 이후의 쿼리문들을 모두 수행하고 하드디스크로 저장
• rollback
  • start transaction 이후의 쿼리문들을 rollback을 만나면 다 취소 시켜버림.

 

set autocommit =0 ;

트랜잭션을 사용하기 위해서는 위 코드블럭처럼 autocommit을 0으로 설정하고 해야함.

기본적으로 1로 되어있음.

---

트랜잭션 사용법

• 하나의 SQL로 하나의 테이블에 영향을 주게되는 경우에는 트랜잭션은 큰 의미가 없으나
• 여러 테이블에 다양한 SQL 명령어로 영향을 줄 경우 트랜잭션으로 묶어 SQL명령어가 모두 적용되거나 또는 모두 적용되지 않게 함으로써 데이터의 integrity를 확보
• MySQL의 start transaction이 오라클 등에서는 begin transaction으로 사용됨
• auto commit은 클라이언트의 기능이므로 트랜잭션을 처리하기 전에 off

마무리

데이터베이스 설계할 때 서로 어떤 관계로 이루어져있는지

이를 정규화를 하는 것을 통해 쉽게 작업하는 것을 중점적으로 체크.

트랜잭션을 여러 처리를 한번에 하는 경우 사용.

---