22.12.09 / CS특강 / DataBase설계

데이터베이스 설계 순서

 

튜터자료

1. 개념적 모델링 - https://app.diagrams.net/#G1fpKnZw_HbMybaCXmYvPOBPLD5MrJEdZl
2. 논리적 모델링 - https://docs.google.com/spreadsheets/d/1-WOEukosLCAJneqYGwfllPgHFagsRYDkWb0lOZXKClI/edit#gid=13739035

1. 요구조건 분석

 

2. 개념적 모델링

• 개념적 모델링: 개체와 개체들 간의 관계에서 ER다이어그램을 만드는 과정

 

ER모델

• ER모델: 세상의 모든 사물을 개체(Entity)와 개체 간의 관계(Relationship)으로 표현
• 개체: 독립적인 의미를 지니고 있는 유무형의 사람 또는 사물로 개체의 특성을 나타내는 속성(Attribute)에 의해 식별된다.
• 개체끼리 서로 관계를 지닌다.

간단히 말해서 ER모델(Entity Relationship Model)은 현실 세계에서 개체들을 도출하고 개체들 사이의 관계를 기술하는 것으로, 아래의 그림을 참고하면 쉽게 이해할 수 있습니다.

우리는 ER모델으로 ER다이어그램(ER Diagram)을 만들 수 있습니다. ER다이어그램에서 개체는 ㅁ, 개체의 속성은 ㅇ 그리고 개체 사이의 관계는 ◇로 표현됩니다.

• ER 다이어그램 연습 - https://itwiki.kr/w/ER_다이어그램
• ERD 툴 추천 - https://gitmind.com/kr/er-diagram-tool.html

 

ERD(Entity-Relation Diagram: 개체 관계도)

• ER 모델(개체-관계 모델, Entity-Relationship Model)
• 현실 세계에 존재하는 데이터와 그들 간의 관계를 사람이 이해할 수 있는 형태로명확하게 표현하기 위해서 가장 널리 사용되고 있는 모델 개념적 모델링의 대표적인 도구

ERD표준 기호

ERD예시

 

관계타입의 유형

(1) 1 : 1 관계(일 대 일)

• 개체 집합 A의 각 원소가 개체 집합 B의 원소 1개와 대응

(한명의 교수는 한과목만 강의 하고, 한개의 과목은 한명의 교수에 의해 강의되어진다.)

 

(2) 1 : N 관계(일 대 다)

- 개체 집합 A의 각 원소는 개체 집합 B의 원소 여러 개와 대응할 수 있고, 개체 집합 B의 각 원소는 개체 집합 A의 원소 1개와 대응할 수 있음

(한명의 학생은 여러과목을 수강할 수 있고, 한 과목은 여러명의 학생에 의해 수강되어 질 수 있다.)

 

새발(Crow-feet) 표기법

관계의 의미나 제약조건 등을 개체 타입들을 연결하는 실선 위에 몇 가지 기호를 사용하여 표현하는 표기법.

 

새발표기법의 예

3. 논리적 모델링

논리적 모델링: ER다이어그램을 사용하여 관계 스키마 모델을 만드는 과정

 

4. 물리적 모델링

물리적 모델링: 관계 스키마 모델의 물리적 구조를 정의하고 구현하는 과정

5. 데이터베이스 구현

---

정규화(Normalization)

정규화의 목적, 목표

 

중복 데이터를 없애고 관계를 단순하게 가져 간다!

 

제1정규화: 원자성

• 테이블의 컬럼이 원자값(Atomic Value, 하나의 값)을 갖도록 테이블을 분해하는 것

 

제2정규화: 완전 함수적 종속 (부분 종속 제거)

• 제2 정규화란 제1 정규화를 진행한 테이블에 대해 완전 함수 종속을 만족하도록 테이블을 분해하는 것이다.
• 여기서 완전 함수 종속이라는 것은 기본키의 부분집합이 결정자가 되어선 안된다는 것을 의미한다.

이 테이블에서 기본키는 (학생번호, 강좌이름)으로 복합키이다. 그리고 (학생번호, 강좌이름)인 기본키는 성적을 결정하고 있다. (학생번호, 강좌이름) --> (성적)

그런데 여기서 강의실이라는 컬럼은 기본키의 부분집합인 강좌이름에 의해 결정될 수 있다. (강좌이름) --> (강의실)

즉, 기본키(학생번호, 강좌이름)의 부분키인 강좌이름이 결정자이기 때문에 위의 테이블의 경우 다음과 같이 기존의 테이블에서 강의실을 분해하여 별도의 테이블로 관리하여 제2 정규형을 만족시킬 수 있다.

제3 정규화: 이행 종속 제거

• 제3 정규화란 제2 정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분해하는 것이다. 여기서 이행적 종속이라는 것은 A -> B, B -> C가 성립할 때 A -> C가 성립되는 것을 의미한다.

기존의 테이블에서 학생 번호는 강좌 이름을 결정하고 있고, 강좌 이름은 수강료를 결정하고 있다. 그렇기 때문에 이를 (학생 번호, 강좌 이름) 테이블과 (강좌 이름, 수강료) 테이블로 분해해야 한다.

이행적 종속을 제거하는 이유는 비교적 간단하다. 예를 들어 501번 학생이 수강하는 강좌가 스포츠경영학으로 변경되었다고 하자. 이행적 종속이 존재한다면 501번의 학생은 스포츠경영학이라는 수업을 20000원이라는 수강료로 듣게 된다. 물론 강좌 이름에 맞게 수강료를 다시 변경할 수 있지만, 이러한 번거로움을 해결하기 위해 제3 정규화를 하는 것이다.즉, 학생 번호를 통해 강좌 이름을 참조하고, 강좌 이름으로 수강료를 참조하도록 테이블을 분해해야 하며 그 결과는 다음의 그림과 같다.

정규화 연습(아래 링크를 보면서 정규화 하는법을 연습해 봅시다!)

https://mjn5027.tistory.com/46

https://yaboong.github.io/database/2018/03/09/database-normalization-1/ https://ko.wikipedia.org/wiki/제2정규형

그외 정규화

• BCNF 정규화
• 반(역) 정규화

---

제약조건

NOT NULL

NOT NULL 제약 조건을 설정하면, 해당 필드는 NULL 값을 저장할 수 없습니다.

즉, 이 제약 조건이 설정된 필드는 무조건 데이터를 가지고 있어야 합니다.

NOT NULL 제약 조건은 CREATE 문으로 테이블을 생성할 때나, 나중에 ALTER 문으로 추가할 수도 있습니다.

 

UNIQUE

UNIQUE 제약 조건을 설정하면, 해당 필드는 서로 다른 값을 가져야 합니다.UNIQUE 제약 조건은 CREATE 문으로 테이블을 생성할 때나, 나중에 ALTER 문으로 추가할 수도 있습니다.

즉, 이 제약 조건이 설정된 필드는 중복된 값을 저장할 수 없습니다.

 

PRIMARY KEY

PRIMARY KEY 제약 조건을 설정하면, 해당 필드는 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가집니다.

따라서 이 제약 조건이 설정된 필드는 NULL 값을 가질 수 없으며, 또한 중복된 값을 가져서도 안 됩니다.

이러한 PRIMARY KEY 제약 조건을 기본 키라고 합니다.

UNIQUE는 한 테이블의 여러 필드에 설정할 수 있지만, PRIMARY KEY는 테이블당 오직 하나의 필드에만 설정할 수 있습니다.

이러한 PRIMARY KEY 제약 조건은 테이블의 데이터를 쉽고 빠르게 찾도록 도와주는 역할을 합니다.

 

FOREIGN KEY

FOREIGN KEY 제약 조건을 설정한 필드는 외래 키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할을 합니다.

외래 키가 설정된 테이블에 레코드를 입력하면, 기준이 되는 테이블의 내용을 참조해서 레코드가 입력됩니다.

즉, FOREIGN KEY 제약 조건은 하나의 테이블을 다른 테이블에 의존하게 만듭니다.

FOREIGN KEY 제약 조건을 설정할 때 참조되는 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY 제약 조건이 설정되어 있어야 합니다.

 

DEFAULT

DEFAULT 제약 조건은 해당 필드의 기본값을 설정할 수 있게 해줍니다.

만약 레코드를 입력할 때 해당 필드 값을 전달하지 않으면, 자동으로 설정된 기본값을 저장합니다.

 

제약조건 참고 - http://www.tcpschool.com/mysql/mysql_constraint_default

위에 소개드린 5개 제약조건 말고도 check, create_index 등의 제약조건도 있습니다. 아래 링크를 통해서 나머지 제약조건도 한번 살펴보면 좋을 거 같습니다.

w3schools - https://www.w3schools.com/mysql/mysql_constraints.asp

mysql 공식문서 - https://dev.mysql.com/doc/search/?d=201&p=1&q=constraints

inpa 티스토리 - https://inpa.tistory.com/entry/MYSQL-📚-제약-조건-정리#ON_DELETE_/_ON_UPDATE

db, mysql 에 대한 전반적인 강의(혼자공부하는SQL) - https://www.youtube.com/watch?v=0cRhit1EJM0&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&ab_channel=한빛미디어

w3schools (mysql tutorial) - https://www.w3schools.com/mysql/default.asp

 

SQL 연습 사이트

해커랭크 - https://www.hackerrank.com/

leetcode - https://leetcode.com/

프로그래머스 (코딩테스트 MySQL) - https://programmers.co.kr/