[SQL] 17. 모델링과 정규화

모델링 순서

• 요구사항 수집 → 요구사항 분석 → 개념 모델링 → 논리 모델링 → 물리 모델링 → 구현

 

개념 모델링

ERD(Entity Relationship Diagram)

• 개체들의 관계를 도식화하여 표현하는 방법
• entity(개체) : 데이터베이스화 될 대상. 타원형의 도형으로 표현한다.
• 관계 : 개체와 개체간의 연관성을 나타내는 요소
• 속성 : 개체, 관계에 대한 주요 속성을 나타내는 요소

 

 

논리 모델링

• 개념 모델링의 구조에서 디테일한 속성을 추가한다.
• 사용할 데이터베이스의 특성에 맞는 요소를 추가한다. 관계형 데이터베이스 → (PK, FK)
• 정규화를 거쳐 데이터베이스의 무결성을 유지할 수 있도록 테이블의 구조를 잘 나누어 설계하게 된다.

 

정규화(NF - Normal Form)

• 테이블을 문제가 생기지 않도록 잘 나누는 규칙들을 말한다.
• 정규화를 통해서 데이터베이스 구조의 무결성을 얻을 수 있으나, 성능 부분에서는 좋은 않은 점도 있음.

1NF → 2NF → 3NF → BCNF → … 6NF → 

 

입력 이상(insertion anomalies)

• 데이터 입력 시 수강하지 않는 학생의 정보가 있거나 수강 학생이 없는 과목의 정보를 입력할 때 제한이 되거나 또는 NULL의 값과 입력이 발생하게 된다.

 

학번	이름	전공	과목코드	과목명	성적
1000	김철수	컴퓨터공학	CS101	프로그래밍	A
1000	김철수	컴퓨터공학	CS102	자료구조	B
1001	박영희	컴퓨터공학	CS101	프로그래밍	C
1002	이진수	물리학	PHY101	고전물리학	A
			CS103	데이터베이스

학번	이름	전공	과목코드	과목명	성적
1000	김철수	컴퓨터공학	CS101	프로그래밍	A
1000	김철수	컴퓨터공학	CS102	자료구조	B
1001	박영희	컴퓨터공학	CS101	프로그래밍	C
1002	이진수	물리학	PHY101	고전물리학	A
1003	삼진수

 

삭제 이상(deletion anomalies)

• 데이터 삭제 시, 수강 학생이 하나 있는 고전물리학이나 자료구조의 경우 행을 삭제하게 되면 해당 과목의 정보가 없어지게 된다.

 

학번	이름	전공	과목코드	과목명	성적
1000	김철수	컴퓨터공학	CS101	프로그래밍	A
1000	김철수	컴퓨터공학	CS102	자료구조	B
1001	박영희	컴퓨터공학	CS101	프로그래밍	C
1002	이진수	물리학	PHY101	고전물리학	A

 

갱신 이상(update anomalies)

• 과목의 정보를 갱신 할 때 동일 과목의 정보를 같이 갱신해야 하며, 작업에 문제가 발생한 경우 갱신 한 데이터의 불일치 이상이 발생할 수 있다.

학번	이름	전공	과목코드	과목명	성적
1000	김철수	컴퓨터공학	CS101	응용프로그래밍	A
1000	김철수	컴퓨터공학	CS102	자료구조	B
1001	박영희	컴퓨터공학	CS101	프로그래밍	C
1002	이진수	물리학	PHY101	고전물리학	A
			CS103	데이터베이스

제1정규형(1NF)

• 모든 속성의 데이터들은 단일값(원자값)을 가져야 한다.
• 고객_주문테이블

주문ID	고객ID	고객이름	상품ID	상품이름	가격	대분류	소분류	주문날짜
1	C001	홍길동	P001 / P003	상품A / 상품C	10000 / 15000	의류 / 가전	상의 / TV	2021-01-01 / 2021-03-01
2	C002	이순신	P002	상품B	20000	의류	하의	2021-02-01

 

• 위의 데이터에는 하나의 필드에 여러 항목의 데이터값들이 들어가 있다.
• 이런 형태는 원자값의 형태가 아니기 때문에 제1정규형을 만족하지 않는다.

 

• 아래와 같이 하나의 필드에 하나의 데이터만 들어가도록 정리해준다.

주문ID	고객ID	고객이름	상품ID	상품이름	가격	대분류	소분류	주문날짜
1	C001	홍길동	P001	상품A	10000	의류	상의	2021-01-01
2	C002	이순신	P002	상품B	20000	의류	하의	2021-02-01
3	C001	홍길동	P003	상품C	15000	가전	TV	2021-03-01

 

함수적 종속성(functional dependancy)

• 속성 집합 X에 의해 집합 Y의 내용이 결정된다.
• X이면, Y이다.
• X(결정자) → Y(종속자)

 

학번	학생이름	전공	전공학과
1	영희	컴퓨터공학	공과대학
2	철수	수학	이과대학
3	지민	물리학	이과대학
4	호석	컴퓨터공학	공과대학
5	수지	생물학	이과대학

 

• 전공(결정자) → 전공학과(종속자) 의 함수적 종속성이 있다.
• 현재 데이터만 볼 때는 학생이름 → 전공 의 함수적 종속성이 있는 것처럼 보이겠지만 의미상으로 학생의 이름은 고유한 값이 아니라 동명이인으로 같은 값이 발생 할 수 있기 때문에 학생이름에 의해서 전공의 값이 정해지는 관계는 성립하지 않는다.

학번	학생이름	전공	전공학과
1	영희	컴퓨터공학	공과대학
2	철수	수학	이과대학
3	지민	물리학	이과대학
4	호석	컴퓨터공학	공과대학
5	수지	생물학	이과대학
6	호석	물리학	이과대학

 

• X → Y의 종속성이 성립을 한다고 해서 Y → X가 성립하지는 않는다.

 

부분적 함수 종속성

• 복합키의 일부에 의해서 값이 결정되는 상태

학번	과목코드	과목명	담당교수
1	CS101	컴퓨터공학기초	김철수
2	MT101	기초수학	이영희
3	PH101	기초물리학	박지민
4	CS101	컴퓨터공학기초	김철수
5	BL101	기초생물학	최호석

 

• 학번, 과목코드가 복합키로 구성되어 있을 때 과목명과 담당교수는 해당 복합키에 종속적으로 볼 수 있다.
• {학번, 과목코드} → {과목명, 담당교수}

 

학번	과목코드	과목명	담당교수
1	CS101	컴퓨터공학기초	김철수
2	MT101	기초수학	이영희
3	PH101	기초물리학	박지민
4	CS101	컴퓨터공학기초	김철수
5	BL101	기초생물학	최호석

 

• 다만, 복합키의 일부인 과목코드로도 과목명과 담당교수를 부분적 함수 종속하여 결정하게 된다.
• 과목코드 → {과목명, 담당교수}

완전 함수 종속성

• 기본키에 의해서 나머지 모든 비키 속성의 값들이 결정되는 상태

 

키

• 테이블에서 고유한 값을 개별 행을 구별할 수 있는 값
• 슈퍼키 > 후보키 > 기본키

슈퍼키(super key)

• 테이블에서 행들을 고유하게 식별할 수 있게 해주는 속성 집합들. 키라고 부르기도 한다.

기본키(primary key)

• 슈퍼키 중 최소성을 만족하는 키 중 선택된 키이다.
• 테이블에서 기본키는 하나만 존재한다.

후보키

• 기본키가 될 수 있는 키들을 후보키라 한다.

 

제2정규형(2NF)

• 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속이어야 한다.
• 아래에서는 {주문ID, 고객ID, 상품ID}가 기본키이다.

 

주문ID	고객ID	고객이름	상품ID	상품이름	가격	대분류	소분류	주문날짜
1	C001	홍길동	P001	상품A	10000	의류	상의	2021-01-01
2	C002	이순신	P002	상품B	20000	의류	하의	2021-02-01
3	C001	홍길동	P003	상품C	15000	가전	TV	2021-03-01

• 하지만, 위의 구조에서는 다음 항목들이 부분 종속되어 있는 상태이다.
  고객ID → 고객이름
  상품ID → 상품이름, 가격, 대분류, 소분류
• 따라서 이를 분리해서 기본키에 대한 완전 종속되도록 만들어줘야 한다.

 

고객 테이블

고객ID	고객이름
C001	홍길동
C002	이순신
C001	홍길동

 

상품 테이블

상품ID	상품이름	가격	대분류	소분류
P001	상품A	10000	의류	상의
P002	상품B	20000	의류	하의
P003	상품C	15000	가전	TV

 

주문 테이블

주문ID	고객ID	상품ID	주문날짜
1	C001	P001	2021-01-01
2	C002	P002	2021-02-01
3	C001	P003	2021-03-01

 

제3정규형(3NF)

• 모든 키(결정자)가 아닌 속성이 다른 속성의 값을 결정하는 이행적 함수 종속을 제거한다. 

 

이행적 함수 종속

• A 이면 B이다. B 이면 C이다. 따라서 A 이면 C이다. 의 관계를 이행적 함수 종속이라 한다.
  A → B, B → C, A → C

상품ID	상품이름	가격	대분류	소분류
P001	상품A	10000	의류	상의
P002	상품B	20000	의류	하의
P003	상품C	15000	가전	TV

 

• 상품 테이블에서 본다면 상품ID → {상품이름,가격}, {상품이름,가격} → {대분류, 소분류}의 함수적 종속이 있다. 결론적으로 상품ID → {대분류, 소분류} 형태가 될 수 있다.
• 이를 없애기 위해서 상품 테이블과 분류 테이블을 분리한다.

 

• 상품 테이블

상품ID	상품이름	가격	분류ID
P001	상품A	10000	C001
P002	상품B	20000	C002
P003	상품C	15000	C003

• 분류 테이블

분류ID	대분류	소분류
C001	의류	상의
C002	의류	하의
C003	가전	TV

 

BCNF

• 모든 결정자는 후보키의 부분집합이 되어야 한다.
• 후보키는 기본키가 될 수 있는 키들을 말한다.

상품ID	상품이름	리뷰ID	리뷰내용	작성자ID	작성자이름
P001	상품A	R001	좋아요	U001	홍길동
P002	상품B	R002	별로에요	U002	이순신
P001	상품A	R003	괜찮아요	U001	홍길동

• 위의 테이블에서는 작성자ID → 작성자, 상품ID → 상품이름의 종속성이 있다.
• 하지만, 상품ID, 작성자ID 단독으로는 후보키에 속하지 않기 때문에, 테이블을 다음 형태로 분리해준다.

• 상품 테이블

상품ID	상품이름
P001	상품A
P002	상품B

 

• 리뷰 테이블

리뷰ID	상품ID	작성자ID	리뷰내용
R001	P001	U001	좋아요
R002	P002	U002	별로에요
R003	P001	U001	괜찮아요

 

• 사용자 테이블

사용자ID	사용자이름
U001	홍길동
U002	이순신

게시판 테이블 예시

게시글 테이블

게시글ID	제목	작성자	내용	부모게시글ID
1	안녕하세요	홍길동	처음 뵙겠습니다	NULL
2	답변입니다	이순신	부모글에 대한 답변	1

 

댓글 테이블

게시글ID	댓글ID	댓글내용	댓글작성자	부모댓글ID
1	1	좋은 글이네요	이순신	NULL
1	2	동의합니다	홍길동	1

 

 

테이블 관계대수

• 관계 수 : 개체, 관계들이 서로 대응하는 수를 나타낸다.
  1:1 → 부모(기본키)와 자식(외래키)간 고유한 값으로 서로 연결된다.
  1:N → 부모(기본키)의 값을 다수의 자식(외래키)이 받아갈 수 있다.
  N:M → 다수의 부모의 값과 다수의 자식의 관계가 만들어진다. 이어주는 중간 테이블이 필요하다.

 

 

 

 

 

• 사용할 데이터베이스 이름을 작성해준다.

 

 

 

 

DB 설계용 도구

 

draw.io (diagrams.net)

 

AQueryTool (유료)

 

test (erdcloud.com)

 

workbench

 

모델링 결과물 데이터베이스에 적용하기

• workbench → Database → Forward Engineer 선택

 

• Stored Connection 항목에 작업에 필요한 DB 접속이 선택되었는지 확인한다.

 

• 필요한 항목을 선택 후 진행

 

• 입력할 오브젝트별 수를 확인하고 진행

 

• 자동 생성 된 오브젝트 생성구문 확인 후 진행

• 작업 완료