2020.11.13 일자 수업 : DB 모델링

 DB 모델링 

 

git/eomcs-docs/db/dbmodeling.md

DB 모델링

모델링(modelling)이란 생각하는 바(개념)를 글과 그림으로 표현하는 것을 말한다. (렌더링 : 명령어를 해석하여 화면에 출력하는 것)

DB 모델링이란 데이터 베이스를 관리할 때, 데이터가 중복되지 않도록 테이블을 구조화하기 위해 데이터를 분석하고 구조화하여 데이터 속성과 관계를 글과 그림으로 표현한 것이다. 이 과정을 통해 중복 데이터를 제거하여 데이터의 안정성, 신뢰성을 높이고, 무결성을 유지할 수 있다. 

 

DB 모델링을 할 때 사용되는 주요 용어는 다음과 같다.

• table = relation = entity = file : 테이블
• intension = schema = header : 데이터 구조 설계도
• extension = instance = data : 데이터
• row = tuple = record : 레코드 (여러 컬럼으로 이뤄진 정보 한 개)
• column = attribute = field : 컬럼 (데이터의 한 항목)

키(key)

데이터를 구분할 때 사용하는 식별자로, 수퍼 키(super key)라고 부르기도 한다. 

후보키(candidate key)

수퍼 키들 중에서 선별되어 최소한의 컬럼 값으로 식별이 가능한 최소키가 될 수 있는 키를 말한다. 

기본 키 / 주 키 (primary key : PK)

후보키들 중에서 데이터 식별자로 사용하기 위해 선정된 키이다. 주 키의 값은 변경할 수 없다.

대리 키 (surrogate key) / 인공 키(artificial key)

주 키 컬럼이 두 개 이상이거나 주 키로 적절한 컬럼이 없는 경우, 일련번호와 같이 임의의 값을 갖는 컬럼을 추가하여 PK로 삼을 수가 있다. 이것을 대리 키 혹은 인공 키라고 부른다. 

대체 키(alternate key)

주 키가 되지 못한 나머지 후보키는 대안키(alternate key)라고 부른다. 비록 PK는 아니지만 PK와 마찬가지로 데이터 식별자로 대체하여 사용할 수 있기 때문이다. 주 키와 대리키를 포함한 후보키는 두 개 이상의 레코드에서 값이 중복되어서는 안되며, 주 키는 값을 변경할 수 없으나 대리키는 값을 변경할 수 있다. 대체 키는 테이블을 정의할 때 Unique 컬럼으로 지정된다. 즉. 값을 변경할 수는 있으나 여러 레코드에서 중복이 되지 못하도록 방지하는 것이다.

 

외래 키(foreign key)

다른 릴레이션(테이블)의 PK 값을 저장하는 컬럼으로, FK가 있는 테이블을 자식 테이블이라고 하고, FK가 가리키는 PK 컬럼이 있는 테이블을 부모 테이블이라고 한다. 보통 부모-자식 관계를 맺는 테이블이 있을 때, 자식 테이블쪽에 부모 테이블의 데이터를 가리키기 위해 외부키 컬럼을 둔다. 

---

 DB 모델링 - 논리 모델 

우리는 수강신청 관리 프로그램(Lecture Management System)을 만든다고 가정하여 DB모델링을 해보면서 논리모델과 물리모델에 대한 개념을 배우고, eXERD 프로그램을 통해서 데이터 구조를 모델링한대로 구현해볼 것이다.

 

논리 모델은 특정 DBMS를 고려하지 않고 수행하는 개념적인 모델링을 말한다. 이제 논리 모델을 만드는 방법을 단계적으로 설명할 것이다.

---

엔티티 및 속성 식별

시스템에서 다루는 데이터들의 묶음에서 같은 특성을 갖는 데이터(자바에서는 같은 객체로 표현될 수 있는 데이터)를 먼저 식별하여 엔티티(테이블)로 분류하고, 이 엔티티를 구성하는 각각의 값들을 식별해야 한다.  이 과정을 통해 식별되는 각각의 값은 속성(attribute) 혹은 컬럼(column)이라고 한다.

 

수강 신청 프로그램에서 다뤄지는 엔티티는 학생, 강사, 매니저, 교육과정, 교실, 수강신청으로 분류될 수 있으며, 각각의 엔티티를 구성하는 컬럼값들은 다음 그림과 같다.

---

주키 선정

엔티티에 저장되는 데이터들을 서로 구분할 때 사용할 식별자인, 주 키를 지정해야 한다. 만약 PK로 지정한 적절한 컬럼이 없거나 있더라도 여러 개의 컬럼을 묶어서 사용해야 하는 경우에는 인공 키를 사용하는 편이 좋다.

 

각각의 엔티티에 대하여 주 키를 정할 때, 편하게 주 키를 관리할 수 있도록 모든 엔티티에 대하여 일련번호 형태의 인공 키를 생성하고, 이를 주 키로 설정한다.

 

---

포함 관계 및 배타적 관계 추가

여러 테이블에 공통으로 포함되는 컬럼이 있는 경우엔 별도의 테이블로 정의한다. 이 때 둘 중 하나의 관계로 설정이 가능하다. 

• 포함관계 : 여러 테이블에서 한 테이블을 동시에 포함할 수 있는 관계
• 배타적 관계 : 여러 테이블 중 한 개의 테이블만이 어떤 테이블을 포함할 수 있는 관계

강사와 매니저, 학생이 서로 같은 속성을 몇개씩 공유하고 있다. 이때 같은 속성을 갖는 사용자 테이블을 따로 만들어 각각의 강사, 매니저, 학생 테이블이 사용자 테이블을 포함하는 관계를 만들 수가 있다. 

 

이때, 한 사용자가 강사, 매니저, 학생 중 두 개 이상의 역할을 가질 수 있게 하려면 이 관계를 포함관계로 정의하고, 사용자가 강사와 매니저, 학생 셋 중에 하나만 될 수 게 하려면, 배타적 관계로 정의한다. 이렇게 테이블의 관계를 짓는 이유는 사용자의 기본정보만 필요한 작업(예: 로그인)에는 사용자 테이블만 조회해도 되기 때문이다.

 

만약 다음과 같이 외부키가 그 테이블의 pk가 되면 다른 테이블의 정보가 해당 테이블의 정보를 식별하는 구조가 된다. 따라서 이런 상황에서 두 테이블의 관계를 식별(identifying)관계라고 부른다.

---

제 1정규화

정규화란 데이터 중복을 찾아내어 별도의 테이블로 데이터를 분리시키는 과정을 말한다. 중복 데이터 또는 중복 컬럼을 별도의 테이블로 분리하여 부모-자식 관계를 맺는다.

• 중복 데이터의 예시 : 수강생 데이터, 강의 데이터, 강사 데이터... (중복 데이터가 있으면 정해진 데이터 이외에 잘못된 데이터가 들어갈 위험성이 발생한다.)
• 중복 컬럼의 예시 : 사진1, 사진2, 사진3... (중복 컬럼이 있으면 같은 값이라도 다른 컬럼에 있을 경우 다른 데이터로 인식되며, 각각 정해진 컬럼의 개수에 맞춰 값을 넣어야 한다.)

데이터를 참조하는 테이블이 자식테이블이고, 데이터를 갖고 있는 테이블이 부모 테이블이다. 자식 테이블에서는 부모 테이블의 데이터를 가리키기 위해 그 데이터의 pk값을 보관해야 한다. 이렇게 부모 테이블의 데이터에 대해 PK값을 저장하는 컬럼을 외부키(FK)라 부른다.

 

우리의 데이터 중에서도 중복 데이터가 존재한다. 일단 학생과 강사 테이블에 존재하는 학력, 학교, 전공 컬럼이 그러하다. 이것을 별도의 테이블로 뽑아 관리하고, 강사와 학생 테이블에서 이 테이블을 참조하는 편이 좋다. 이 밖에도 은행과 지점 테이블도 중복 데이터이므로 테이블을 뽑고 원래 있던 테이블에서 이를 참조하도록 한다.

 

아직 위의 그림에서는 교육과정 테이블에 강사1, 강사2, 강사3이라는 중복 컬럼이 있고, 교실 테이블에는 사진1, 사진2, 사진3, 사진4, 사진5라는 중복 컬럼이 존재한다. 따라서 이것을 별도의 테이블로 분리할 것이다.

 

그런데 일단 강사는 이미 있는 테이블이고, 강사는 여러 개의 교육과정 정보를 가질 수 있으며, 교육과정은 여러 개의 강사 정보를 가질 수 있으므로 다대다 관계이다. DBMS는 다대다 관계를 표현할 수 없기 때문에 이를 위한 관계 테이블을 만들어야한다. 관계 테이블에는 강사 번호와 교육과정 번호를 외부키, 그리고 이 테이블의 주키로 갖는다. 즉 이 테이블은 교육과정 테이블과 강사테이블의 자식 테이블이자, 식별 관계에 있는 테이블이다.

 

 

또 교실 사진 테이블을 별도로 뽑아야하는데, 교실은 여러개의 사진 정보를 갖고, 사진 정보는 한 개의 교실 정보를 가지므로 교실 사진 테이블이 자식 테이블이 되고, 교실 테이블이 부모 테이블이 된다.

 

---

마스터 데이터

학교, 전공, 학력 테이블처럼 특별한 경우 아니면 변경되지 않고, 다른 테이블에서 사용하기 위해 만들어진 데이터를 말한다.

---

---

제 2정규화

제 2정규화는 PK가 여러 컬럼으로 이루어진 경우에 수행되며, 모든 일반 컬럼은 반드시 일부 PK가 아니라 모든 PK 컬럼에 종속되어야 하는데 이렇지 않은 일반 컬럼이 있을 경우에는, 일부 PK와 이 PK에 종속되는 일반컬럼들을 묶은 테이블을 만들고, 이 테이블을 참조하게 바꿔야 한다.

 

우리 예제에는 PK가 여러 컬럼으로 이뤄진 경우가 없으므로 이 단계는 넘어간다.

---

제 3정규화

어떤 컬럼이 PK가 아닌 다른 일반 컬럼에 종속되는 경우가 있다면, 별도 테이블로 분리하여 부모-자식 관계를 맺는다.

우편번호에 따라 기본주소가 달라지기 때문에 이것은 별도의 테이블로 빼주었다. 그러나 실무에서는 주소 테이블을 만들지 않는다. 주소체계가 매주 끊임없이 바뀌기 때문에 그냥 직접 문자열의 형태로 입력하는 편이다.

---

다대다 관계 해소

테이블과 테이블 사이에 다 대 다 관계를 형성한다면, DBMS는 물리적으로 다대다 관계를 표현할 수 없기 때문에 일대다의 관계로 변경해야 한다. 이를 위해 두 테이블의 관계를 저장할 테이블인 "관계 테이블"을 생성하고, 이를 통해 연결되는 두 테이블을 일대다의 관계로 참조하므로 각 테이블의 자식테이블이 된다.

 

우리의 예제에서는 이미 제 1정규화 과정에서 강사와 교육과정의 다대다 관계를 관계 테이블로 정의했다.

 

또, 수강신청 테이블은 결국 학생 테이블과 교육과정 테이블의 다대다 관계를 해소하는 관계 테이블로 해석될 수 있으므로 그렇게 바꿔줄 수가 있다.

 

---

테이블 관계 차수 설정

부모테이블과 자식 테이블과의 관계가 0이상인지 1이상인지 혹은 0과1뿐인지 따져본다. 실무에서는 크게 신경쓰지는 않으나, 엄밀히 정하는 것이 원칙이다. 그런데 이 과정에서 개념적인 부분과 데이터 입력에 대한 부분에 차이가 있을 수 있다. 교육 과정에 매니저가 없는 경우는 없으므로 개념적으로는 1:1이 맞으나, 실제로 데이터를 입력할 때는 당장 매니저가 미정된 상태일 수도있으니 이럴 경우에는 0이상으로 해주는 것이 좋다. 그래서 그냥 실무에서는 무조건 0이상으로 하기는 한다. 이것을 다소 애매하게 정해놓아도 큰 문제가 발생하지는 않기 때문이다.

 

우리의 예제에서도 몇가지 수정할 사항이 있다. 예를 들어 지점에서도 부서가 하나도 없는 경우는 없기 때문에 자식 테이블쪽을 1이상으로 바꿔준다.

또한 사용자가 주소를 입력하는 것을 선택 사항으로 두기 위해 주소 테이블(부모)과 사용자 테이블(자식)의 관계를 부모 테이블 쪽에 0또는 1로 바꿔준다.

---

유니크(Unique) 컬럼 지정

UK는 PK는 아니지만 PK처럼 중복되어서는 안되는 컬럼으로, 대체 키(alternate key) 컬럼이 유니크 컬럼이 된다. 즉 PK로 선정되지 않은 나머지 후보 키는 유니크 컬럼으로 지정하여 데이터가 중복되지 않도록 한다. pk/uk는 색인이 자동으로 만들어진다.

 

우리의 예제를 살펴보면 사용자 테이블에 있는 이메일과 주민번호는 PK는 아니지만 서로 다른 데이터와 중복되지 않아야 한다. 따라서 이 컬럼들을 유니크 컬럼으로 지정한다. 

---

null 허용 여부 지정

각 테이블의 일반 컬럼들에 대해서 필수 입력 컬럼(Not-Null)인지 선택 입력 컬럼(Null)인지 지정한다. 

 

우리의 예제에서 정의된 모든 컬럼에 대해서 필수 입력 컬럼인지 선택 입력 컬럼인지 모두 결정해야 한다.

---

인덱스 컬럼 지정

데이터를 찾을 때 일반 컬럼 중 검색 조건으로 사용할 컬럼을 인덱스 컬럼으로 지정한다. PK/UK는 이미 색인표에 자동 생성되므로 굳이 지정해줄 필요가 없다. 조회 컬럼으로 지정하면 그 컬럼의 값으로 색인표가 자동 생성되므로 데이터를 찾는 속도가 빨라진다. 단, 데이터가 수정될 때마다 색인표도 같이 수정되기 때문에 insert, update, delete가 느릴 수 밖에 없다.

 

우리는 가장 수정을 할 일이 없는 데이터들 중에서 일부 일반 컬럼을 인덱스로 지정해줄 것이다. 가장 수정이 적을 마스터 데이터(학교, 학력, 전공, 지점, 은행)의 이름 컬럼을 인덱스로 정의한다. 부서의 이름 컬럼은 지점에 종속되어있기 때문에 검색한다고 해도 큰 의미가 없으므로 인덱스 컬럼으로 지정하지 않는다.

 

null 허용여부와 인덱스 컬럼을 지정한 논리모델의 최종 결과는 다음과 같다.

 

---

 DB 모델링 - 물리모델 

 

테이블명과 컬럼명 지정

DBMS에 맞춰서 테이블명과 컬럼명을 설정한다. DBMS에서 테이블명과 컬럼명을 작성할 때 보통 다음의 규칙에 따라 작성한다.

• 단어는 알파벳 3자 또는 4자로 축약해서 표현한다.
• 단어와 단어 사이는 밑 줄( _ )로 표현한다.

예) first name(FST_NM), regist date(REG_DT), teacher assignment(TCH_ASN)

 

우리의 예제는 기본적으로 수강신청 관리 프로그램(Lecture management System)이므로 모든 테이블의 이름을 lms_xxx와 같이 짓는다. 그 밖에 컬럼명도 다음과 같이 지정해준다.

---

도메인(domain) 정의 및 적용

비슷한 종류의 컬럼들을 묶어 새 타입으로 정의하면, 타입을 변경할 때 이미 같은 타입으로 지정해놓은 모든 컬럼의 타입을 한 번에 변경할 수 있어서 유지보수에 좋다.

 

우리 eXERD에서 기본적으로 제공하는 도메인은 다음과 같다. 이처럼 미리 타입들을 정해놓으면 각 컬럼의 타입을 편리하게 지정할 수도 있고 타입을 수정하고 싶을 때에도 이 목록에서 타입을 수정하면 이 타입으로 지정된 모든 컬럼의 데이터 타입이 수정된 타입으로 한번에 수정된다.

우리는 여기서 기본적으로 제공하는 도메인 이외에도, 몇가지 도메인을 추가하여 사용할 것이다. 추가한 도메인들은 다음과 같다.

그리고 이 추가한 도메인과 기본적으로 제공된 도메인들을 사용하여 예제에서 만들어진 모든 컬럼의 데이터 타입을 지정한다.

---

번호가 자동 증가하는 컬럼 지정

테이블의 PK 중에서 자동으로 증가해야 하는 컬럼을 지정한다. 보통은 인공키로 지정된 일련번호 컬럼이 자동 증가로 지정된다.

 

우리의 예제에서는 대부분의 테이블의 PK가 인공키로 지정되어있으므로, 이 PK들을 인공키로 지정해줘야 한다. 다만, student, mgr, teacher 테이블에서는 이미 user에서 자동증가된 외부키를 PK로 사용하기 때문에 굳이 지정할 필요는 없다.

---

기본 값 및 제약 조건 설정

데이터를 입력하지 않을 시 자동 생성될 값이 필요한 컬럼에 대해 기본 값을 설정하고, 일부 컬럼의 값의 범위를 지정한다. 

 

우리의 예제에서는 수강신청 테이블에서 신청일을 입력하지 않으면 데이터가 입력된 날짜와 시간 값이 수강신청일로 들어가도록, 기본 값으로 now() 함수 결과를 설정한다.

 

이 단계를 모두 거쳐 만들어진 물리모델의 모습은 다음 그림과 같다.