[코드잇] SQL 데이터베이스 - 데이터베이스 모델링

---

1. 데이터 모델링이란?

2. 논리적 모델링

3. 정규화

4. 물리적 모델링

---

1. 데이터 모델링이란?

데이터 모델링

개발자 입장에서는 데이터를 추가, 삭제 수정 또는 조회하기 훨씬 전부터 데이터를 어떻게 저장할지 계획해야함.

→ 개체, 속성, 관계, 제약조건을 파악한 후 이 내용을 발전시켜 데이터 모델들을 만드는 과정

• 논리적 모델링 : 테이블과 컬럼을 어떻게 나누고, 컬럼들 사이의 연결관계를 어떻게 되는지 개념적 구조를 정하는 것

• 물리적 모델링 : 개념적 구조를 바탕으로 컴퓨터의 데이터베이스를 구축하기 위해 필요한 세부사항들을 정하는 것

  (컬럼 이름, 데이터타입, 제약조건)

---

데이터 모델

• 데이터 모델 : 다양한 데이터 요소들을 이해하고 사용하기 편한 형태로 정리해놓은 모형

 

가장 익숙한 데이터 모델 : Relational 모델 (릴레이셔널 모델)

출처 : 코드잇 강의

데이터를 row와 column이 있는 테이블로 나누고, 각 테이블 사이의 관계를 foreign key로 나타냄.

 

저장하고 싶은 데이터 값들은 처음부터 주어진 형식이 정해져 있지 않음.

테이블 형태로 정리해 놓은 것이 훨씬 더 이해하고 사용하기 쉬움.

 

• 데이터 모델링 :

- 정리되지 않은 데이터를 인간이 이해할 수 있는 데이터 모델로 정리해내는 작업

  (우리가 데이터를 어떻게 인식해서 저장하고 싶은지를 정확하게 이해해야 함.)

- 개체, 속성, 관계, 제약조건을 파악한 후 이 내용을 발전시켜 데이터 모델들을 만드는 과정

Entity (개체)	저장하고 싶은 데이터의 대상 ex. 데이터베이스에 학생, 수업, 교수에 대한 정보를 저장할 경우, 개체 : 학생, 수업, 교수  · Entitiy : 실제 대상 하나 하나, row 한 개를 의미함. · Entitiy Type : 일반화한 Entity 종류 , 테이블 전체를 의미함. → 대부분 Entitiy Type도 Entity로 표현함.
Attribute (속성)	Entity에 대해서 저장하려는 내용 ex. 학생 (개체) : 학번, 이름, 성별, 입학년도, 전공 (속성)        수업 (개체) : 시간, 이름, 과, 지도 교수 (속성)  →  쉽게 말해 테이블의 컬럼임
Relationship (관계)	Entity들 사이 연결점 ex. 학생 → 수강 → 수업, 수업 ← 가르침 ← 교수
Constraint (제약조건)	여러 데이터 요소들에 있는 규칙 ex. 학생의 고유번호는 서로 겹치면 안 된다, 모든 수업은 적어도 한 명의 교수를 갖는다.

 

• 데이터 모델링의 목적

- 저장하고자 하는 데이터에서 Entity, Attribute, Relationhip, Constraint 파악

- 데이터베이스를 구축할 때 기반이 될 모델 만들기

---

Relational 모델 (릴레이셔널 모델)

 

데이터를 row와 column으로 이루어진 테이블을 가지고 표현한 모델 (관계형 모델)

 

Relation

= 테이블 사이에 맺어지는 '연결 관계' (X)

= '테이블'을 의미하는 수학적인 표현 (O)

→ 하나의 테이블이 하나의 relation으로, Relation model은 데이터를 테이블(relation)로 정리해서 표현한 모델

 

테이블들 사이에는 foreign key를 통해서 관계를 만들어줄 수 있음.

출처 : 코드잇 강의

review 테이블 : foreign key user_id와 product_id를 사용해서 어떤 유저의 어떤 상품인지에 대한 평가인지를 알아낼 수 있음.

이렇게 해서 맺어지는 관계는 relationship이라고 부름.

 

Relational Model의 단점

1) Row가 있다.

row는 개별 데이터 하나하나를 의미하는데, 데이터의 구조를 정할 때는 큰 의미를 지니지 않음.

→ row가 하나도 없어도 테이블 구조에 대한 내용은 쉽게 파악 가능함.

 

2) 테이블 사이에 맺어지는 관계의 특성을 한눈에 알기 힘들다.

· Child table : foreign key를 저장하고 있는 테이블

· Parent table : foreign key를 통해 참조되고 있는 테이블 

어떤 두 테이블이 서로 관계가 있는지 없는지에 대해 알고 싶으면 child table에서 foreign key를 찾고, 이걸 사용해서 연결된 다른 테이블을 확인해야 함.

해당 모델에서 화살표로 관계가 있다는 것은 알 수 있지만 관계의 특성은 파악할 수 없음.

→ 모델링을 할 때에는 구조를 한눈에 파악할 수 있는 다른 모델(ERM)을 같이 사용함.

---

Entity-Relationship 모델 (ERM)

entity(개체)와 relationship(관계)를 중심으로 모델링하는 모델로, relation model과 더불어 사용함.

이 모델은 relation model과 달리 데이터를 테이블 형태로 표현하지는 않음.

 

Entity 하나하나에 대한 정보(row)를 표현하지는 않는다.

기호들은 각각 관계의 특징을 설명하므로, Entity 사이 관계의 속성을 한눈에 파악하기 쉽다.
ERM에 있는 내용을 릴레이셔널 모델로 바꿔서 표현하는 건 (또는 그 반대로 표현하는 건) 간단하다.
Entity(테이블)는 네모로, attribute(컬럼)은 네모 안 글씨로, relationship은 실선으로 표현한다.

관계의 특징에 따라 모델링하는 방법이 다르므로 관계의 속성을 파악하는 것이 중요함.

---

데이터 모델 스펙트럼

얼마나 자세하게 표현됐는지에 따라 세 종류로 구별함.

개념 모델(Conceptual Model)	논리 모델(Logical Model)	물리 모델(Physical Model)
가장 추상적인 내용을 담고 있는 모델	테이블의 구조(어떤 테이블, 컬럼, foreign key를 사용할지)를 정해나가는 모델링	데이터베이스를 실제로 구축하는 데 필요한 내용들을 정해나가는 모델
· 큼지막한 entity와 간단한 연결관계만을 나타냄. · attribute 내용은 구체화하지 않고, 그냥 대략적인 구조를 파악할 때 주로 사용함.	· entitiy 뿐만 아니라 attribute까지 표현 · Primary key(PK), foreign key(FK) 표현	· 가장 구체적인 모델 · 실제 이 모델로 데이터베이스를 구축할 수 있을 정도로 자세한 정보가 담긴 모델 · 데이터 타입, 변수 이름, 인덱스 추가
경영진 또는 기획자	개발자 구체화 단계	데이터베이스 구축 단계

모델링을 제대로 하지 않으면, 데이터베이스에 다양한 문제들(데이터 정확성이 떨어지거나, 성능 저하가 되는)이 생길 수 있음.

---

좋은 데이터베이스

데이터 베이스 모델링에 따라 데이터베이스의 품질이 달라짐.

나쁜 데이터베이스	좋은 데이터베이스
- 데이터 중복 저장, NULL이 많음 - 연산 실행이 너무 오래 걸림 - 원하는 정보를 찾을 수 없음 - 틀린 데이터를 저장하고 있음	- 중복, Null이 생기지 않음 - 빠르고 정확하게 데이터를 다룰 수 있음 - 데이터가 늘어날 때 테이블 구조가 변하지 않음. - 틀린 데이터 없음

---

2. 논리적 모델링

비즈니스 룰(Business Rule)

모델링의 시작은 Entity, attribute, relationship 파악으로부터 시작함.

이때 해당 서비스의 비즈니스 룰 또는 사업 규칙을 이용하면 됨.

 

비즈니스 룰(Business Rule)

- 기업 뿐만 아니라 어떤 조직이 운영되기 위해 따르는 규칙
- 특정 조직이 운영되기 위해 따라야 하는 정책, 절차, 원칙들에 대한 간단명료한 설명 (Coronel)
- 웹사이트면 페이지에서 제공하는 모든 기능에 관한 규칙

ex. "온라인 쇼핑몰 코팡"의 비즈니스 룰
- 유저는 상품을 주문할 수 있다.
- 동일한 주문 내역은 한 번의 배달로, 3일 안에 유저가 지정한 배송지에 전달돼야 한다.
   만약 그렇지 못할 시, 유저에게 최대한 빨리 알려줘야 한다.
- 유저는 상품에 대한 평가를 줄 수 있다.

   평가는 두 종류의 데이터: 1~5 사이 자연수의 별점, 그리고 200자 이내 줄 글을 통해 할 수 있다.

개발자가 직접 정하기 보다는 서비스를 제공하는 회사나 조직의 구성원들이 회의를 통해 결정하거나 기획 담당자 등이 정함.
비즈니스룰은 데이터 모델링의 핵심이 되기 떄문에 개발자가 비즈니스 룰을 정확히 이해하는 것이 중요함.
→ 비즈니스 룰의 내용을 바탕으로 사용할 Attribute, Enttity, relationship을 정해야함.

---

ERM 초안 만드는 방법

비즈니스 룰에서 entity, attribute, relationship 후보 찾기

 

• 모든 명사는 entity 후보다
• 모든 동사는 relationship 후보다

  단, 다대다 관계에서는 동사도 entity 후보가 될 수 있다
• 하나의 값으로 표현(수치화)할 수 있는 명사는 attribute 후보다

   단, 여러 값을 가질 수 있다면 entity 후보다

모든 학생은 고유의 학생 번호가 있다. 학생은 고유 이메일과 비밀 번호를 사용해서 포털에 로그인할 수 있다. 한 학생은 최대 4개의 수업을 수강할 수 있다. 한 학생은 시간이 겹치는 수업을 수강할 수 없다. 모든 수업은 가르치는 교수 한 명이 있다. 각 수업은 강의실에서 진행됩니다.

학생 → entity 후보 학생 번호 → attribute 후보 이메일 → attribute 후보 비밀 번호 → attribute 후보 수업 → entity 후보 수업 시간 → attribute 후보 교수→ entity 후보 강의실 → entity 후보

• 값으로 표현할 수 있는 명사라도 attribute가 아니라 entity로 만드는 경우

→ entity 안에 똑같은 종류의 여러 attribute를 저장해야 될 때

출처 : 코드잇 강의

유저는 여러 개의 주소를 가질 수 있음.
어떤 유저는 address가 1개, 어떤 유저는 address가 3개임.

 

• entitiy 안에 똑같은 종류의 attribute를 많이 만들면 발생하는 문제점

Null이 너무 많이 생김.	NULL은 공간을 낭비하고 해석이 애매함. 구조적으로 NULL이 많이 생길 수 있는 경우는 최대한 피해야 함.
컬럼을 몇 개  만들어야 하는지가 애매해짐.	유저가 무수히 많은 주소를 가질 수 있다면 주소를 가장 많이 저장한 유저의 주소 개수만큼 컬럼이 필요함. 공간이 엄청 낭비되고, 같은 종류의 데이터가 늘어날 때마다 테이블 구조가 변해야 되기 때문에 모델링이 자연스럽지 않음.
조회가 복잡함.	특정 주소를 찾고자 할 때 컬럼이 엄청 많이 생겼을 경우 각각의 컬럼에서 그 주소를 다 조회해야됨.

출처 : 코드잇 강의

주소 address는 하나의 값을 표현되는 값이라고 해도 attribute로 만들지 않음.
→ 분리해서 하나의 테이블(entity)로 만들어주면 깔끔하게 정리할 수 있음.
user_id인 foreign key를 만들어서 각 row마다 어떤 유저의 주소인지 알려줌.
→ 특정 주소가 어떤 유저의 것인지 쉽게 알 수 있음.

 

• Business Rule은 항상 간단명료하면서도 필요한 내용을 모두 담고 있어야 함.

• 비즈니스 룰에 없어도 개발자가 알아서 고려해서 추가해야 하는 내용도 있음.

- id : 특정 entity를 식별할 수 있게 해주는 attribute

- foreign key : 연결관계를 나타내는 attribute

• attribute와 relationship들의 특성에 따라서 모델링이 바뀔 수 있으니 ERM은 초안임.

---

카디널리티 (Cardinality)

두 entity type 사이 관계에서 한 종류의 entity가 다른 종류의 entity 몇 개에 대해서 관계를 맺을 수 있는지를 나타내는 개념

일대일, 일대다, 다대다 : 어떤 관계에 있는지에 따라 모델링이 바뀐다

 

1:1 (일대일)		하나의 A에 대해 B가 1개, 하나의 B에 대해 1가 1개가 있음. · 법적 부부 관계 : 한 명의 아내는 한 명의 남편만 가질 수 있고, 그 반대도 마찬가지임 · 주민등록증 : 시민 한 명은 하나의 주민등록증을 부여받음. 하나의 주민등록증은 한 시민만에 대한 내용임. 일반적으로 최소 카디널리티가 0인 테이블에 foreign key를 만들어서 모델링
1:N (일대다)		하나의 A에 대해서 여러 개의 B가 있을 수 있으나, 하나의 B에 대해서는 하나의 A만 있음. · 유저의 평가 : 한 유저는 여러 개 평가 가능, 하나의 평가는 한 명의 유저에만 속함. · 학교 선생님과 수업 : 한 명의 선생님은 동시에 여러 수업 가르 칠 수 있음, 한 수업은 오직 한 명의 선생님에 의해 가르쳐짐. 항상 "다"에 해당하는 테이블에 "일"에 해당하는 테이블 로우를 특정 지을 수 있는 foreign key를 만들어서 모델링
N:N (다대다)		하나의 A에 대해서 여러 개의 B가 있을 수 있고, 하나의 B에 대해서도 여러 개의 A가 있을 수 있음. · 코팡의 찜하기 기능 : 한 명의 유저가 여러 개 상품 찜하기 가능, 한 개의 상품도 여러 개의 유저에 의해 찜 당하기 가능함. · 학생과 수업 : 하나의 학생은 여러 개 수업 듣기 가능, 하나의 수업도 여러 개의 학생들이 들을 수 있음. 연결 테이블에 각각 두 entity의 로우를 특정지을 수 있는 foreign key 두 개를 만들어줘서 연결 관계 데이터를 저장함.

카디널리티는 entity를 어떻게 사용하고 싶은지에 따라 달라지므로 비즈니스룰을 정확히 파악해야함.

ex. 유저와 프로필

- 페이스북 : 유저는 프로필 1개만 가짐. → 유저와 프로필 사이는 1대1 관계

- 넷플릭스 : 1개의 유저는 여러 개의 프로필 가질 수 있음. → 유저와 프로필 사이는 1대다 관계

---

카디널리티와 ERM

Crow's foot 표현법

최대 카디널리티	최소 카디널리
한 개의 Entitiy가 다른 entity와 최대 몇 개까지 연결	한 개의 Entitiy가 다른 entity와 최소 몇 개까지 연결
일 Entity : 수직선 1개 다 Entity : 선 3개	하나도 없어도 되는 Entitiy : 동그라미 적어도 하나는 있어야 하는 Entity : 수직선

---

관계 모델링

 

일대일 관계 모델링

두 entity 어디에 다른 테이블 로우를 특정 지을 수 있는 foreign key를 넣어도 큰 상관은 없음.

하지만 구조적으로 NULL이 생기는 문제를 최소화하기 위해서 일반적으로 최소 카디널리티가 0인 테이블에 foreign key를 만들어서 모델링함.

user가 항상 card가 있어야만 할 때는 user 테이블에 foreign key를 넣어도 됨.

만약 card가 있어도 되고 없어도 되면 card 테이블(최소 카디널리티가 0인 테이블)에 넣음.
(user에 card_id를 foreign key로 넣으면 NULL이 생기기 때문에)
위 사례는 card는 무조건 use가 있어야 하기 때문에, card에 foreign key를 넣으면 NULL이 생기는 걸 방지할 수 있음.

 

일대다 관계 모델링

항상 "다"에 해당하는 테이블에 "일"에 해당하는 테이블 로우를 특정 지을 수 있는 foreign key를 만들어서 모델링함.

만약 위 사례에서 "일"에 해당하는 user에 foreign key를 만들게 되면 review를 컬럼으로 계속 만들어야 해서 NULL이 많이 생기고, 불필요하게 user 테이블의 구조를 계속 변경해야함.

 

M:N 관계 모델링

관계를 저장하는 새로운 테이블, 연결 테이블(Junction Table)을 사용함.

이 테이블에 각각 두 entity의 로우를 특정지을 수 있는 foreign key 두 개를 만들어줘서 연결 관계 데이터를 저장함.

다대다 관계에 있는 두 Entity는 테이블 두 개만으로 자연스럽게 표현할 수 없음.

(한 테이블에 foreign key를 만들면 컬럼을 계속 만들어야 함.)

연결테이블로 사용할 favorite라는 entity를 만들어서 user와 product의 '찜하기 관계'를 저장 , foreign key가 2개임.

다대다 관계에서는 동사도 entity 후보가 될 수 있음.

---

3. 정규화

이상 현상 (anomaly)

데이터베이스에서 삽입, 업데이트, 삭제를 제대로 할 수 없게 되는 경우

데이터 모델링을 제대로 하지 않았기 때문에 발생함.(테이블과 컬럼이 제대로 나눠지지 않아서)

삽입 이상	업데이트 이상	삭제 이상
새로운 데이터를 자연스럽게 저장할 수 없는 경우	데이터를 업데이트했을 때, 정확성을 지키기 어려워지는 경우	원하는 데이터만 자연스럽게 삭제할 수 없는 경우
(넥손, 09-333-333)이라는 새로운 row를 추가할 경우, name과 hobby가 NULL값으로 채워져야함.	코팡의 전화번호를 수정하고 싶을 경우, 코팡에 다니는 사람들의 row를 찾아서 전화번호를 각각 업데이트해야함.	김영희 유저에 대한 row가 사라지면 삼송과 삼송의 전화번호도 모두 날아감.

---

정규화

정규화(Normalization)
- 데이터베이스의 테이블이 잘 만들어졌는지 평가하고, 잘 만들지 못한 테이블을 고쳐나가는 과정
- 테이블을 정규형(normal form)이라고 불리는 형태에 부합하게 만들어 나감.
(문제가 있는 컬럼을 다른 테이블로 옮기기 등)

정규형(Normal Form)
- 1NF, 2NF, 3NF 등
- 순서에 따라 규칙이 누적됨.

1NF	테이블 안 모든 로우의 모든 컬럼 값들은 나눌 수 없는 단일 값이어야 한다.
2NF	1NF에 부합해야 한다. 테이블에 candidate key의 일부분에 대해서만 함수 종속성이 있는 non-prime attribute가 없어야 한다.
3NF	2NF에 부합해야 한다. 테이블 안에 있는 모든 attribute들은 오직 primary key에 대해서만 함수 종속성이 있어야 한다.

EKNF, BCNF, 4NF, ETNF, 5NF, DKNF, 6NF은 데이터베이스 연구자 아니면 볼 일이 거의 없음.
대부분 3NF에 부합하면 잘 정규화된 데이터베이스라고 함.

데이터베이스에서 삽입, 업데이트, 삭제 이상을 없앨 수 있음.
새로운 종류의 데이터를 추가할 때 테이블 구조 수정을 많이 하지 않아도 됨.
데이터베이스 구조를 단순화해서 사용자가 더 쉽게 이해할 수 있음.
데이터 모델을 만들고, 실제 데이터베이스를 구현하기 전에 적용하면 좋음.

---

1NF (제 1정규형)

• 테이블 안 모든 로우의 모든 컬럼 값들은 나눌 수 없는 단일 값이어야 한다.

 

한 컬럼에 같은 종류의 값을 여러 개 저장하고 있을 때
→ 해당 컬럼을 하나의 테이블로 분리해서 모델링

컬럼 수를 늘려서 하나의 컬럼에 하나의 전화번호만 저장하면 NULL이 생기기 쉬운 구조가 되고, 추가해야 하는 컬럼 수 불명확하다는 문제가 있음.

해당 attribute를 분리해서 새로운 테이블로 만들고 관계를 foreign키로 저장하면 됨.

 

한 컬럼에 서로 다른 종류의 값을 여러 개 저장하고 있을 때
→ 한 컬럼을 여러 개로 분리해서 모델링

사람 이름은 first, middle, last로 나눌 수 있으니 왼쪽은 1정규형을 만족하지 않음.

그렇다고 해서 테이블을 각각 따로 만들면 관리하기 불편하므로 컬럼을 분리해서 모델링하는 것이 더 나음.

---

함수 종속성 (Functional Dependency)

테이블 안 attribute 사이에서 생기는 관계로, x의 값에 따라 y의 값이 결정될 때 y는 x에 함수 종속성이 있다고 함. (x → y)

출처 : 코드잇 강의

user 테이블에서 email은 중복될 수 없어서 하나의 유저를 특정지을 때 사용할 수 있음.
→ 이걸 이용해서 유저의 name, age, gender 값을 결정할 수 있음.

 

user 테이블에서

• name, age, gender는 이메일에 함수 종속성이 있다
• email → {name, age, gender}

name, age, gender에 따라 나머지 값이 결정되지 않는다

출처 : 코드잇 강의

• size와 price는 product에 대한 함수 종속성이 있다
• product → {size, price}

함수 종속성은 반드시 하나의 컬럼에만 있어야 되는 건 아님.

• score은 {user, product}에 함수 종속성이 있다.
• (score, 즉 별점은 user와 product 두 attribute 쌍에 대한 함수 종속성이 있다.)
• {user, product} → score

 

출처 : 코드잇 강의

이행적 종속성(Transitive Dependency)

데이터베이스 모델링에서 속성 간의 종속 관계를 나타내는 개념으로, 하나 이상의 attribute를 건너서 함수 종속성이 있는 경우

A가 B에 종속되고, B가 C에 종속되는 경우, A는 C에 대해서도 이행적으로 종속됨.

brand는 product에 따라 결정되는게 맞지만, brand_country는 어떤 브랜드인지에 따라 결정되는 값임.

brand는 product에 대한 함수 종속성이 있음.
{product} → brand

brand_country는 brand에 대한 함수 종속성이 있음.

{brand} → brand_country

 

브랜드 국적(brand_country)은 상품(product)에 대한 이행적 함수 종속성이 있다고 함.
product → brand → brand_country

---

Candidate Key

하나의 row를 특정 지을 수 있는 attribute들의 최소 집합

 

review 테이블에서 한 개의 row는 한 명의 유저가 하나의 상품에 주는 평가, 리뷰임.

· id 한 개만 알고 있으면 한 개의 리뷰 row를 특정지을 수 있음.

· 어떤 유저와 어떤 상품이지 알고 있으면 한 개의 리뷰 row를 특정지을 수 있음.

  = user_id와 product_id 두 개를 알고 있으면 한 개의 리뷰 row를 특정지을 수 있음.

 

candidate key : {id}, {user_id, product_id}

row를 특정 짓는데 사용되지 않는 attribute는 candidate key에 포함될 수 없음.

prime attribute	cadidate key에 포함된 모든 attribute id, user_id, product_id
non-prime attribute	candidate key가 아닌 모든 attribute를 의미함. score, description

---

2NF (제 2정규형)

• 제 1정규형에 부합해야 한다

(테이블 안 모든 값이 나눌 수 없는 단일 값이어야 한다)

• 테이블에 candidate key의 일부분에 대해서만 함수 종속성이 있는 non-prime attribute가 없어야 한다.

(candidate key 자체에 대해서는 함수 종속성이 허용)

 

출처 : 코드잇 강의

 

review 테이블에 유저 나이를 나타내는 age, 상품 가격을 나타내는 price attribute가 있음.

review는 하나의 유저가 하나의 product에 주는 평가임.

 

id 한 개 혹은 user_id와 product_id 두 개를 합쳐서 하나의 review row를 찾을 수 있음.

candidate key : {id}, {user_id, product_id}

 

• age는 user_id에 대한 함수 종속성이 있음. (user_id로 user을 특정지으면 age 값이 결정됨)
• price는 product_id에 대한 함수 종속성이 있음. (product_id로 product를 특정지으면 price 값이 결정됨)
• user_id → age 
• product_id → price

age와 price는 cadidate key의 전체 {user_id, product_id}가 아닌 일부에만 의존하고 있음.

→ cadidate key 의 일부분에만 의존하는 non-prime attribute들을 따로 분리해주면 됨.

출처 : 코드잇 강의

age와 price를 각각 다른 테이블로 옮겨주어서 제2정규형에 만족하도록 함.

---

3NF (제 3정규형)

• 2NF에 부합해야 한다

- 테이블 안 모든 값들은 나눌 수 없는 단일값이어야 함. (1NF)

- candidate key의 일부분에 대해서만 함수 종속성이 있는 non-prime attribute가 있으면 안 됨.

• 테이블 안에 있는 모든 attribute들은 오직 primary key에 대해서만 함수 종속성이 있어야 함.

  (테이블의 모든 attribute는 직접적으로 테이블 entitiy에 대한 내용이여야만 함.)

 

id 한 개 혹은 이벤트 정보와 몇 회차 이벤트인지 알면 하나의 당첨 정보를 특정 지을 수 있음.

winner, age attribute는 당첨 정보를 특정 짓는 데 사용할 수 없음.

 

candidate key : {id}, {event, event_num}

non-prime attribute : winner, age

 

age는 당첨자에 대한 정보로, winner에 따라서 그 값이 결정됨.

winner는 candidate key의 일부분이 아닌 non-prime attribute임.

• winner → age

→ 제 2 정규형에 부합암.

 

제 3 정규형을 만족하기 위해서는

• 모든 attribute들이 직접적으로 primary key에 대한 함수 종속성이 있어야 한다는 말임.

= 모든 attribute들이 직접적으로 당첨 정보에 대한 내용이여야 함.

= primary key인 id에 대한 직접적인 함수 종속성이 있어야 함.

 

하지만 몇몇 개는 이행적인 종속성이 있음.

 

• age는 winner에 대한 함수 종속성이 있음.
• winner은 id에 대한 함수 종속성이 있음.
• age는 id(primary key)에 대한 이행적 함수 종속성이 있음.
• id → winner → age

 

제3정규형을 만족시키기 위해서는 다른 attribute를 건너서 종속(이행적 종속성을 만족)하고 있으면 안 됨.

age attribue 때문에 테이블은 제3 정규형을 지키고 있지 않음.

 

출처 : 코드잇 강의

age 컬럼을 분리하면 됨.

user 테이블을 만들어서 id, name, age 컬럼을 만들고, 당첨자 테이블에서 user를 찾을 수 있는 foreign key 설정함.

---

비정규화

정규형에 부합하는 테이블을 정규형을 지키지 않게 바꾸는 것
· 데이터가 너무 퍼져있어서 조회 연산의 성능 문제가 심각한 수준으로 일어나는 게 확실할 때
· 테이블을 삽입, 업데이트, 삭제하는 것보다 조회하는 용도로만 사용하고 있을 때

---

4. 물리적 모델링

네이밍

물리적 모델링 : 데이터베이스에 실제로 저장하는 내용에 최대한 가깝게 데이터 모델을 만드는 과정
네이밍 (이름짓기)
- 각 요소들의 이름을 정하기
- 데이터베이스의 테이블이나 컬럼에 대한 네이밍 규칙은 정해져 있는 것이 아님.
- 데이터베이스를 사용하는 사람이나 조직의 규칙에만 잘 따르면 됨.

테이블 이름을 사용할 때 고려해야 하는 것

 

1) 단수/복수 정하기
• 단수 : 단수, 복수 변화가 없어서 일관성을 유지하기 쉬움.

ex. user/users, review/reviews, person/people, ox/oxen → 단수형은 변화가 없음.
• 복수 : 실제로 테이블은 한 개의 객체가 아니라 여러 개 개체의 집합이기 때문에 복수형이 더 의미적으로 자연스럽다고 주장함.
ex. SELECT FROM users : 모든 유저"들" 중, 특정 유저를 골라라.

 

컬럼 이름은 대부분 단수를 사용하는 걸 선호함.
모든 컬럼의 값들은 분리 불가능한 단일 값을 사용해야되기 때문임.

2) 대문자/띄어쓰기 정하기
ex. 모든 알파벳 소문자, 띄어쓰기는 언더스코어
user_id, userId, UserId

3) 줄임말 정하기
• 본래 단어 : 일일이 치는 게 비효율적 (ex. social_security_number)
• 줄인 단어 : 무엇을 의미하는지 직관적으로 이해하기 힘들 수 있음 (ex. ssn)

---

데이터 타입

각 컬럼이 어떤 데이터를 저장하는지에 대한 내용

데이터베이스의 가장 기본적인 제약 사항 (데이터의 정확성을 지켜주는 역할)

Numeric types(숫자형 타입)		Date and Time types(날짜 및 시간 타입)	String types(문자열 타입)
정수형	실수	DATE	CHAR
TINYINT	DECIMAL	DATETIME	VARCHAR
SMALLINT	FLOAT	TIMESTAMP	TEXT
MEDIUMINT	DOUBLE		MEDIUMTEXT
INT			LONGTEXT
BIGINT

 

숫자형 타입(Numeric types)

 

정수형

	SIGNED (양수, 0, 음수)	UNSIGNED (0, 양수)
TINYINT	-128 ~ 127	0 ~ 255
SMALLINT	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	-8388608 ~ 8388607	0 ~ 16777215
INT	-2147483648 ~ 2147483647	0 ~ 4294967295
BIGINT	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

TINYINT라고만 쓰면 SINGED로 자동해석됨.

 

실수형

DECIMAL DEC NUMERIC FIXED	일반적으로 자주 쓰이는 실수형 타입 중 하나 보통 DECIMAL(M, D)의 형식 · M : 최대로 쓸 수 있는 전체 숫자의 자리수 (최대 65) · D : 최대로 쓸 수 있는 소수점 뒤에 있는 자리의 수 (최대 30) DECIMAL (5, 2)라면 -999.99 부터 999.99 까지의 실수
FLOAT	-3.402823466E+38 ~ -1.175494351E-38, 0,  1.175494351E-38 ~ 3.402823466E+38
DOUBLE	-1.7976931348623157E+308 ~ -2.2250738585072014E-308,  0,  2.2250738585072014E-308 ~ 1.7976931348623157E+308 FLOAT에 비해 더 넓은 범위의 수를 나타낼 수 있을 뿐만 아니라, 그 정밀도 또한 더 높은 타입

 

날짜 및 시간 타입(Date and Time types)

DATE	날짜를 저장하는 데이터 타입	’2020-03-26’ / 연, 월, 일
DATETIME	날짜와 시간을 저장하는 데이터 타입 타임 존(time_zone) 정보도 함께 저장 X	’2020-03-26 09:30:27’ / 연, 월, 일, 시, 분, 초
TIMESTAMP	날짜와 시간을 저장하는 데이터 타입 타임 존(time_zone) 정보도 함께 저장 O	’2020-03-26 09:30:27’ / 연, 월, 일, 시, 분, 초

 

문자열 타입(String types)

CHAR (Character)	고정 길이 타입 → 값의 길이가 크게 변하지 않을 컬럼에 사용 문자열을 나타내는 기본 타입 CHAR(숫자) : 숫자는 문자열의 최대 길이 (0~255) ex. CHAR(10) : 최대 10자의 문자열 저장 어떤 길이의 문자열이 저장되더라도 항상 그 값이 10만큼의 저장 용량을 차지
VARCHAR (Character Varing)	가변 길이 타입 → 길이가 들쑥날쑥할 컬럼에 사용 VARCHAR(숫자) : 숫자는 문자열의 최대 길이 (0~65,535) ex. VARCHAR(10) : ‘Hello’와 같이 5자라면 저장 용량도 5만큼 차지 - 저장 용량이 설정된 최대 길이에 맞게 고정되는 게 아니라 실제 저장된 값에 맞게 최적화됨. - 해당 값의 사이즈를 나타내는 부분(1byte 또는 2byte)이 저장 용량에 추가됨.
TEXT	문자열을 저장하는 데이터 타입, 최대 65535 자까지 저장
MEDIUMTEXT	16,777,215 (224 − 1) 자까지 저장
LONGTEXT	4,294,967,295(232 − 1) 자까지 저장

 

데이터베이스에서 데이터 타입을 잘 정해야 되는 이유
- 데이터 정확성을 지키기 위해서
- 데이터베이스 연산/함수들을 제대로 활용하기 위해서
- 데이터베이스 용량을 최적화하기 위해서

 

ex. 각 user의 나이를 age 컬럼에 저장하려고 함.

 

age 컬럼의 타입 : INTEGER (정수)

INTEGER

· -2,147,483,648 ~ 2,147,483,648 정수만 저장할 수 있음.
· 모든 숫자는 동일하게 4바이트 단위를 저장함.

→ 나이는 0부터 아무리 많아도 200 이하의 자연수이므로 INTEGER보다 TINYINT를 사용하여 1/4 용량으로 저장함.

출처 : 코드잇 강의록

 

---

선형  vs 이진 탐색

선형 탐색(Linear Search)	이진 탐색(Binary Search)
규칙 없이 섞여 있는 데이터에서 특정 조건을 만족하는 데이터를 찾을 때 가장 앞에서부터 뒤까지 모든 원소를 하나씩 확인하는 방법	데이터가 특정 순서대로 정렬돼 있을 때, 계속 남은 범위의 중간 원소를 확인해봄으로써, 탐색 범위를 매번 반씩 줄여나가는 방법
O(n) 1024개의 데이터: 최대 1024번 만에 데이터를 찾아낼 수 있음	O(lg(n)) 최대 11번 만에 원하는 데이터를 찾아낼 수 있음
	데이터가 정렬됐을 때만 사용가능함. 데이터가 정렬만 되어있으면 훨씬 빠르게 찾을 수 있음.

---

인덱스 개념

원하는 조건의 row를 더 빠르게 찾기 위해서 특정 column 값들을 정렬해서 저장해 놓은 것

인덱스는 테이블의 각 컬럼 값에 해당하는 row의 주소와 함께 특정 컬럼들을 정렬된 상태로 저장함.

컬럼에 중복되는 값들이 있어도 인덱스는 충분히 잘 작동할 수 있음.

출처 : 코드잇 강의

---

Clustered vs Non-Clustered 인덱스

Clustered 인덱스	Non-Clustered 인덱스
테이블 자체를 특정 순서로 저장하는 법	row들이 실제 저장된 순서 자체는 건들지 않고, 별도의 데이터베이스 다른 곳에 컬럼 값들의 순서를 저장하는 방법
테이블 안 row들이 실제 e-mail의 알파벳 순서대로 저장됨. (a부터 z까지)	DB 다른 곳에 데이터가 생겨서 각 이메일에 대해 해당 row의 유저가 저장된 주소를 갖고 있음. 원하는 이메일을 찾았을 떄 이 주소를 사용해서 바로 원하는 유저 row를 찾을 수 있음.
데이터를 특정 순서대로 저장해서 인덱스를 하나만 생성함 ex. 이메일 순서대로 정렬되어 있으면서, 이름 순서대로 정렬할 수는 없음	개수 제한이 없음
조회속도가 굉장히 빠르다	Clustered 인덱스보다는 조금 느리다
따로 인덱스를 만들어 놓는 게 아니라 영한사전처럼 애초에 단어들을 알파벳 순서대로 저장함.	일반 책의 색인, 또는 인덱스와 비슷한 개념

일반적으로 Clustered Index는 선형 탐색을 수행하고 Non-Clustered Index는 이진 탐색을 수행하는 경향이 있음.

Non-Clustered Index를 사용하면 특정 값을 찾기 위해 이진 탐색 또는 트리 기반의 탐색 알고리즘이 일반적으로 사용됨.

---

Composite 인덱스

인덱스는 여러 개의 컬럼을 합쳐서도 만들 수 있음.

• 개별 컬럼에 인덱스를 추가하는 것과, 여러 컬럼들에 대한 인덱스를 추가하는 건 두 개의 다른 인덱스임.

• 여러 컬럼들에 대한 인덱스를 만들 때, 순서가 중요함.

- 브랜드, 종류, 색깔에 대한 인덱스가 있으면 가장 앞순서는 '브랜드' 인덱스가 있음. 

→ 항상 가장 왼쪽에 조건으로 가장 많이 사용하는 컬럼을 사용하고, 오른쪽으로 갈수록 조건으로 덜 사용하는 컬럼

각각의 브랜드와 종류별로 인덱스가 따로 있는 경우	인덱스 조합을 해놓은 경우

---

인덱스 사용과 단점

인덱스 사용의 장점

- 특정 조건의 데이터를 빠르게 조회할 수 있음.

- 인덱스는 데이터베이스 내의 데이터를 논리적으로 정렬하고 검색을 용이하게 하는 구조

인덱스 사용의 단점

· 인덱스는 별도의 저장 공간을 필요로 하므로 데이터베이스의 용량을 증가시킴.
· 인덱스를 사용하면 데이터를 추가, 수정 또는 삭제할 때 인덱스를 업데이트해야 함.

  - 하나의 로우값을 변경하면 해당 값의 컬럼이 포함된 모든 인덱스를 수정해야 함.
· 인덱스의 사용은 주로 조회 작업에 적합합니다.

  - 조회를 자주 수행하는 테이블에 대해 인덱스를 생성하는 것은 성능 향상에 도움이 됨.

  - 데이터를 자주 추가, 수정 또는 삭제해야 하는 테이블의 컬럼에 인덱스를 생성하는 것은 오히려 성능을 저하시킬 수 있음.

인덱스 추가할 때 고려할 원칙

· 모든 primary key에 대해서 인덱스를 만들어준다.
· 모든 foreign key에 대해서 인덱스를 만들어준다.
·특정 조회 쿼리가 너무 느려지거나, 느려질 게 확실한 경우 조회에 사용되는 컬럼들에 대해서 인덱스를 만들어준다.

---

SQL로 인덱스 만들고 사용하기

• 인덱스 만들기

clustered 인덱스	· primary key (주로 id 컬럼)에 대한 clustered 인덱스 생성 : → MySQL에서 자동으로 됨. · primary key가 아닌 다른 컬럼을 clustered 인덱스 생성 :  → 기존 인덱스 삭제 후, CREATE CLUSTERED INDEX index_name ON table_name (column_name)
Non-clustered 인덱스	CREATE INDEX index_name ON table_name (column_name)
Composite 인덱스	· 컬럼 이름들을 괄호 안에 모두 넣어주면 됨. CREATE INDEX index_name ON table_name (column_name_1, column_2, ...)

• 인덱스 확인하기 : SHOW INDEX FROM table_name;

• 인덱스 삭제하기 : DROP INDEX index_name ON table_name;

인덱스를 삭제하기 위해서는 항상 인덱스 이름을 알고 있어야 됨.

---

▼ 코드잇 - SQL로 하는 데이터 분석 (MySQL)

코드잇 - SQL로 하는 데이터 분석 (MySQL)