[Spring][Seminar-hub] 5편. E-R 모델과 릴레이션 변환규칙, 정규화를 이용한 데이터베이스 설계

시작

안녕하세요! PassionFurit200입니다!

이번 글에서는 세미나허브 시스템에서 데이터베이스 설계를 릴레이션 변환규칙을 적용하고, E-R 모델을 통해 표현하는 과정, 그리고 표현한 E-R 모델에서 생길 수 있는 이상현상을 정규화를 이용해 이상현상이 발생하지 않도록 검증하는 작업까지 적용해본 과정을 정리해보려고합니다!

지금까지는 데이터베이스가 어떤 과정을 통해 산출되었는지에 관한 이유와 이력을 남기지 않았었는데, 이번 설계를 통해 어떤 요구사항을 만나도 수월하게 처리할 수 있는 안정적인 능력을 가지게 되면 좋겠습니다.

그럼 시작해보겠습니다~!

목차 

• 문제상황 (릴레이션 변환규칙, 정규화를 적용해야하는 이유)
• 데이터베이스 설계 5과정과 각 단계에서의 산출물 정리.
  • 1단계. 요구사항 분석이 필요한 이유
  • 2단계. 개념적 설계가 필요한 이유
  • 3단계. 논리적 설계가 필요한 이유
  • 4단계. 물리적 설계가 필요한 이유
  • 5단계. 구현이 필요한 이유
•  

문제상황( 왜 릴레이션 변환규칙, 정규화가 반드시 필요한 데이터베이스 설계 이론일까? )

먼저, 문제상황을 제시하기 전에 왜 릴레이션 변환규칙, 정규화가 반드시 필요한 이론일까?에 대한 답을 먼저 하겠습니다.

바로, 엄청나게 복잡한 시스템을 효과적으로 데이터베이스를 설계하기 위함입니다.

예로 들어, 누군가 아래와 같은 요구사항을 주었다고 해봅시다.

물론 직관도 매우 중요하지만, 매우 복잡한 시스템이 주어졌을때는 효율적으로 처리하기 위해서는 이론을 기반으로 접근해야 할 것 입니다. 

이러한 복잡한 시스템을 개발하는 것에도 당연히 필요하고, 이러한 설계 이론을 적용해보면서 관계에 대한 이해도가 늘어날 것 이라 생각합니다!

데이터베이스 설계 5단계와 각 단계별 산출물

이번에 제가 세미나허브를 설계하면서 적용한 설계 5단계와 각 단계별 목표와 결과물입니다. 가장 중요한 부분은 각 단계별 결과물입니다.

먼저, 가볍게 데이터베이스 설계 5단계 과정의 필요성에 대하여 한 단계씩 살펴보겠습니다.

1단계 : 요구사항 분석이란?

어떤 시스템에 대한 요구사항이 들어왔을때, 가장 먼저 시작해야할 부분이 어디일까요? 바로 요구사항 분석입니다.

상단의 문제상황 부분에서 그림의 PM이 요청한 부분을 보듯이, 모든 개발의 시작은 요구사항 분석입니다. 이를 위해 결과물로는 요구사항 명세서를 산출해보려고 합니다.

이러한 요구사항은 이후의 설계 단계에서도 매우 중요하게 사용되고, 결국 구축된 데이터베이스의 품질을 결정합니다.

2단계 : 개념적 설계란?

1단계의 요구사항 분석 단계의 결과물인 요구사항 명세서를 이용해 개념적 데이터 모델을 표현합니다. 이때 개념적 데이터 모델이란 개발에 사용할 DBMS의 종류에 독립적(이 부분이 3단계와 다른점입니다.)이면서, 사용자의 요구사항을 분석한 결과를 기반으로 데이터를 선정하고, 중요한 데이터 요소와 데이터 요소 간의 관계를 표현할 때 사용합니다. 이번 글에서 개념적 데이터 모델은 E-R 모델을 사용할 것 입니다. E-R 모델을 E-R 다이어그램(개념적 구조, 개념적 스키마)으로 표현할 것 입니다.

이번 2단계에서의 산출물은 E-R 다이어그램, 즉 개념적 구조 및 개념적 스키마입니다.

3단계 : 논리적 설계란?

개발에 사용할 DBMS에 적합한 논리적 데이터 모델을 이용해 개념적 설계 단계에서 생성한 개념적 구조를 기반으로 논리적 구조를 설계합니다. 앞의 2단계와 다른점은, 2단계 개념적 설계에서는 DBMS의 종류는 상관없었습니다. 3단계에서는 DBMS에 적합한 논리적 데이터 모델을 설계합니다. 저는 관계 데이터 모델을 사용할 것 입니다. (네트워크 데이터 모델, 계층 데이터 모델, 관계데이터 모델, 객체지향 데이터 모델과 같이 여러가지 논리적 데이터모델이 존재하는데, 관계 데이터 모델을 사용합니다. )

이 단계에서의 결과 산출물은 논리적 스키마(릴레이션 스키마)입니다. 저는 관계 데이터 모델을 사용할 것이므로 릴레이션 스키마가 산출물인 것입니다.

정규화(Normalization)을 이 단계에서 처리하는 이유는?

3단계 논리적 설계까지 완료되었다면, 실제로 4, 5단계에서 구현하기 전에 이상현상이 발생하는지 검증이 필요합니다. 이러한 점검을 위해 해당 단계가 포함되었습니다.

4단계 : 물리적 설계란?

논리적 설계 단계에서 생성된 논리적 구조를 기반으로 물리적 구조(내부 저장 구조 정의 혹은 인덱스)를 설계합니다. 

이 단계에서의 결과 산출물은 내부 스키마 또는 물리적 스키마라고 합니다.

5단계 : 구현이란?

이전 설계 단계의 결과물을 기반으로 DBMS에서 SQL로 작성한 명령문을 실행하여 데이터베이스를 실제로 생성합니다. 이떄 사용되는 SQL 문은 테이블이나 인덱스 등을 생성할 때 사용되는 데이터 정의어(DDL)입니다.

중요한 부분

가장 중요한 단계는

1단계. 요구사항 분석( 전체적인 데이터베이스의 개념적 구조와 논리적 구조를 설계하는데 핵심이기 떄문 ).

2단계. 개념적 설계(각 데이터 모델 간의 관계를 표현).

3단계. 논리적 설계 단계(사용하는 DBMS의 관계 데이터 모델을 선택하여 2단계 개념적 구조를 어떻게 표현할지) 가 핵심입니다. 

4단계와 5단계는 사실상 같은 단계라고 봐도 무방합니다.

 

이제 위의 각 5단계를 직접 세미나 허브 시스템에 적용하여 설계해보도록 하겠습니다.

1단계 : 요구사항 분석

데이터베이스에 대한 사용자들의 요구사항을 수집하고 분석하여, 개발할 데이터베이스의 용도를 명확히 파악하는게 목적입니다. 그리고 분석한 사용자 요구사항의 내용을 요구사항 명세서로 작성하여 이후 설계 단계에서 기초 자료로 활용합니다.

세미나 허브는 회원들에게 세미나를 전문적으로 판매하는 세미나의 허브가 되는 것이 목표입니다.

1단계 결과물 : 요구사항 명세서

요구사항 명세서 작성이 완료되었습니다.

2단계 개념적 설계( 2.1 개체와 속성 추출, 2.2 관계추출, 2.3 E-R 다이어그램 작성 )

1단계의 요구 사항 명세서를 바탕으로 데이터 요소를 추출하고 데이터 요소 간의 관계를 파악하여 개념적 스키마(E-R 다이어그램)으로 산출할 것 입니다. 따라서 개발에 사용할 DBMS의 종류는 중요하지 않습니다.

개념적 데이터 모델은 E-R 모델을 사용하겠습니다.(일반적으로 가장 많이 사용됩니다.)

 

위의 그림을 보듯이 개념적 설계는 세가지 단계로 이루어져있습니다.

[2-1] 개체와 속성 추출 : E-R 모델의 E를 의미하는 Entity(개체)를 추출할 것 입니다. 이떄 Entity의 주요 속성과 키 속성 또한 선별합니다. 

[2-2] 관계 추출 : E-R 모델의 R(Relation, 관계) 를 추출할 것 입니다. 각 개체간의 관계를 의미합니다.

[2-3] E-R 다이어그램 작성 : 각 개체와 관계 추출이 완료되면, 하나의 E-R 다이어그램(개념적 스키마)로 합치는 과정입니다.

 

각 단계별로 작업을 시작해보겠습니다.

[2-1] 개체와 속성 추출

개체와 속성을 추출하기 위해, 요구사항 명세서를 활용합니다. 먼저, 개체부터 결정해야 속성과 관계도 결정할 수 있습니다. 

 

개체를 추출하기 위해, 요구사항의 '명사'들을 모두 찾습니다. (중복된 명사는 제거하고, 조직의 업무 처리와 관련이 적은 일반적이고 광범위한 의미의 명사는 제외하며, 의미가 같은 명사가 여러개면 대표 명사 한개만 선택).

이 과정을 통해 업무 처리와 관련이 깊은 의미있는 명사만을 찾습니다만, 그 명사들을 모두 개체로 추출하지 말아야 합니다. 이유는, 개체가 아닌 속성으로 분류되는 단어도 존재하기 때문입니다. (아래의 그림에서는 '주문' 명사)

 

명사들을 모두 체크하고, 개체와 속성으로 분류했습니다.

결론적으로 위의 그림을 통해 업무처리와 관련이 깊은 의미있는 명사들만 선별한 결과입니다. 

이를 표로 나타내면, 아래와 같습니다.

 

개체와 속성을 모두 추출했으니, 최종 결과물은 E-R 다이어그램으로 작성해보겠습니다. E-R 다이어그램에서 개체는 사각형으로, 속성은 타원형으로 표현합니다.

회원 개체 E-R 다이어그램

세미나 개체 E-R 다이어그램

업체 개체 E-R 다이어그램

게시글 개체 E-R 다이어그램

 

이로써 [2-1]단계. 개체와 속성 추출을 끝내고, E-R 다이어그램으로 표현까지 마무리했습니다.

[2-2] 관계 추출

개체와 속성을 추출하였으니, 개체 간의 관계를 결정해보겠습니다. 관계란 무엇일까요? 개체 간의 의미있는 연관성입니다. 일반적으로 관계는 요구사항을 표현한 문장에서 동사로 표현됩니다. 그러므로 개체 간의 관계를 결정할 때는 요구 사항 문장에서 동사부터 찾아야 합니다. 단, 조직의 업무처리와 연관하여 개체 간의 연관성을 의미있게 표현한 동사만 선택하고, 의미가 같은 동사가 여러개이면 대표동사 하나만 선택합니다.

 

관계를 추출한 후에는 관계에 대한 매핑 카디널리티와 참여특성을 결정해야 합니다. 즉 관계가 있는 것을 알았으니 그 관계가 어떻게 이루어져 있는지에 대하여입니다.

 

매핑 카디널리티와 참여특성이란 무엇일까요?

매핑 카디널리티란? 매핑 카디널리티는 관계를 맺고 있는 두 개체에서, 각 개체 인스턴스가 관계를 맺고 있는 상대 개체의 개체 인스턴스 개수를 의미합니다. 매핑 카디널리티를 기준으로 추출한 관계를 일대일 1:1, 일대다 1:n, 다대다 n:m 중 하나로 분류합니다. 

참여 특성이란?  개체가 관계에 필수적으로 참여하고 있는지 선택적으로 참여하고 있는지를 의미합니다. 분류는 '선택적 참여특성' '필수적 참여특성' 2가지로 분류합니다.

 

관계에 대한 매핑 카디널리티와 참여 특성은 이후 논리적 설계 단계에서 중요하게 활용되는 정보이므로 정확히 판단해야 합니다.

관계추출을 위한 의미있는 동사 추출하기

개체와 개체의 관계를 추출하기 위해 '동사'를 추출하되, 개체 간의 관계를 나타내는 동사를 추출합니다. (아래의 그림에서 보듯이, '입력해야 한다.', '부여된다', 유지해야한다' 와 같이 동사이지만, 개체 간의 관계를 표현한다고 볼 수 없으므로 추출하지 않습니다.)

위의 요구사항 명세서 6번, 8번, 12번을 통해서

6번 : 회원 개체와 상품 개체가 주문 관계를 맺고 있습니다.

8번 : 세미나 개체와 업체 개체가 공급 관계를 맺고 있습니다.

12번 : 회원 개체와 게시글 개체가 작성 관계를 맺고 있습니다.

관계에서 관계차수와 참여특성 추출

성공적으로 관계를 추출했으니, 각 관계의 관계차수와 참여특성을 추출해보겠습니다.

우리가 추출한 6번 8번 12번 문장에서 한번에 확인할 수 있었습니다.

6번 요구사항에서 추출한 주문관계의 관계차수와 참여특성

- 주문관계의 관계차수 : 회원 개체와 세미나 개체의 주문 관계는 N:M 관계차수입니다.

- 주문관계의 회원 개체의 참여특성 : 회원 개체는 세미나 개체를 반드시 주문해야하는 것은 아니므로 선택적 참여특성을 가집니다.

- 주문관계의 세미나 개체의 참여특성 : 세미나 개체는 회원에게 반드시 주문되어야하는 것은 아니므로, 선택적 참여특성을 가집니다.

 

8번 요구사항에서 추출한 공급 관계의 관계차수와 참여특성

- 공급관계의 관계차수 : 업체 개체와 세미나 객체가 맺는 공급 관계는 일대다(1:N) 관계차수입니다.

- 공급관계의 세미나 개체의 참여특성 : 세미나가 반드시 업체에 의해 공급되어야 하므로, 세미나 개체는 공급 관계에 필수적 참여

- 공급관계의 업체 개체의 참여특성 : 업체가 반드시 세미나를 공급해야 하는 것은 아니므로, 세미나 개체는 공급 관계에 선택적 참여

 

12번 요구사항에서 추출한 작성 관계의 관계차수와 참여특성

- 작성관계의 관계차수 :  회원 개체와 게시글 개체가 맺는 작성관계는 일대다(1:N) 관계차수입니다.

- 작성관계의 회원 개체의 참여특성 : 회원이 반드시 게시글을 작성해야 하는 것은 아니므로, 선택적 참여입니다.

- 작성관계의 게시글 개체의 참여특성 : 게시글은 반드시 회원에 의해 작성되어야 하므로, 필수적 참여입니다.

관계와 관계의 속성 추출 최종결과

아래와 같이 정리할 수 있습니다.

요구사항 명세서에 추출한 주문 관계의 E-R 다이어그램

주문관계를 E-R 다이어그램으로 표현합니다.

공급 관계의 E-R 다이어그램

공급관계를 E-R 다이어그램으로 표현합니다.

작성 관계의 E-R 다이어그램

작성관계를 E-R 다이어그램으로 표현합니다.

산출물 : 개념적 스키마 및 전체 E-R 다이어그램

전체 E-R 다이어그램 및 개념적 스키마입니다.

3단계 논리적 설계(릴레이션 스키마 변환 규칙 1~5 적용)

이번 단계에선, E-R 다이어그램(개념적 스키마)를 릴레이션 스키마(테이블 스키마 및 논리적 스키마)로 변환하는 것이 목표입니다.

E-R 다이어그램을 릴레이션 스키마로 변환하면서 유의해야할점은 무엇일까요?

E-R 모델과 관계 데이터 모델은 개체와 관계를 표현하는 방법이 다릅니다.

E-R 모델에서는 개체와 관계를 구분하지만, 관계 데이터 모델에서는 개체와 관계를 구분하지 않고 모두 릴레이션으로 표현합니다. 즉, 세미나허브에서는 주문 관계, 공급 관계, 작성 관계가 모두 릴레이션으로 변경된다는 의미입니다. 그리고 E-R 모델에서는 다중 값 속성이나 복합 속성의 표현을 허용하지만, 관계 데이터 모델에서는 다중 값 속성과 복합 속성의 표현을 허용하지 않습니다. (저의 E-R 다이어그램에서는 이미 다중값 속성이나 복합 속성이 존재하지는 않습니다. 또한, 애초에 다중 값과 복합속성이 존재하는 경우 릴레이션의 필요조건을 충족하지 못합니다.) 

릴레이션 스키마(릴레이션) 변환 규칙 5가지를 이용하여 E-R 다이어그램을 관계데이터 모델(Relation Data Model)로 변환하는 과정을 담아보았습니다! 

릴레이션 스키마 변환 규칙

규칙 1 : 모든 개체는 릴레이션으로 변환한다.

E-R 다이어그램의 각 개체를 하나의 릴레이션으로 변환합니다. 개체의 이름을 릴레이션의 이름으로 하고, 개체가 가진 속성도 릴레이션의 속성으로 그대로 변환합니다. 단, 개체가 가지고 있는 속성이 복합 속성인 경우에는 복합 속성을 구성하고 있는 단순 속성만 릴레이션의 속성으로 변환합니다. 개체가 가지고 있는 키 속성은 릴레이션의 기본키로 변환한다.

[규칙 1] : 세미나 릴레이션

 

[규칙 1] : 회원 릴레이션

[규칙 1] : 업체 릴레이션

[규칙 1] : 게시글 릴레이션

개체를 릴레이션으로 변환을 완료했습니다. 이제 두번쨰 규칙을 적용해보겠습니다.

규칙 2 : 다대다n:m 관계는 릴레이션으로 변환한다.

E-R 다이어그램에 있는 다대다(N:M) 관계를 하나의 릴레이션으로 변환합니다. 관계의 이름을 릴레이션의 이름으로 하고, 관계의 속성도 릴레이션의 속성으로 그대로 변환합니다. 관계를 맺고 있는 개체의 릴레이션들의 기본키를 관계 릴레이션에 포함시키고 외래키로 지정합니다. 그리고 이 외래키들을 조합하여 관계 릴레이션의 기본키로 지정해도 되고, 기본키를 새로 만들어서 지정해도 됩니다(저는 후자를 택했습니다.).

이떄 유의할점은, 개체를 변환한 릴레이션의 기본키를 외래키로 지정할 때는 가져온 기본키들의 이름이 같을 경우 하나는 이름을 변경해야 합니다. 한 릴레이션에 있는 속성은 이름이 모두 달라야 하기 때문입니다. 하지만 속성의 이름만 달라질뿐 속성의 도메인은 변하지 않으므로 외래키로 사용하는데 문제가 되지 않습니다.

 

저의 E-R 다이어그램에서 다대다 관계는 세미나-회원의 주문 관계입니다. 이 관계에 규칙 2를 적용하겠습니다.

규칙 2를 적용하면, 주문 관계가 주문 릴레이션으로 변환되고(관계의 이름 그대로 변환), 관계의 속성도 릴레이션의 속성인 주문일자, 주문수량, 주문번호가 함께 변환됩니다. 그리고 관계를 맺고 있는 회원과 세미나의 각 기본키를 주문 릴레이션에 포함시키고 외래키로 지정합니다. (이 외래키들을 조합하여 관계 릴레이션의 기본키로 지정해도 됩니다만, 저는 주문번호라는 기본키를 새로 만들었습니다.)

규칙 3 : 일대다1:n 관계는 외래키로 표현한다.

E-R 다이어그램에 있는 일대다 1:n 관계는 릴레이션으로 변환하지 않고 외래키로만 표현합니다. 단, 약한 개체(다른 개체에 의존적인 개체)가 참여하는 일대다 1:n 관계는 일반 개체가 참여하는 경우와 다르게 처리해야 하므로 규칙 3을 다음과 같이 2개의 세부 규칙으로 나누어 적용합니다.

규칙 3-1 : 일반적인 일대다 관계는 외래키로 표현한다.

일반 개체들이 참여하는 일대다1:n 관계는 릴레이션으로 변환하지 않고 외래키로만 표현합니다. 일대다1:n 관계의 1측 개체 릴레이션의 기본키를 가져와 n측 개체 릴레이션에 포함시키고 외래키로 지정합니다. 관계의 속성들도 n측 개체 릴레이션에 포함시킨다. 단, 외래키나 관계의 속성을 포함시킬 때 해당 릴레이션의 원래 속성과 이름이 같으면 이름을 변경해야 합니다.

 

[규칙 3-1] : 공급 관계에 적용

위의 규칙을 세미나 허브 시스템에 그대로 적용해보겠습니다.

업체와 세미나는 일대다 1:n 관계입니다. 이 관계에서는 릴레이션으로 변환하지 않고 외래키로만 표현합니다. 일대다1:n 관계의 1측 개체 릴레이션인 업체 개체의 기본키인 업체번호를 가져와 n측 개체인 세미나 릴레이션에 포함시키고 업체번호를 외래키로 지정합니다. 관계의 속성들인 공급 관계의 (공급일자, 공급량) 속성도 n측 개체 릴레이션인 세미나 릴레이션에 포함시킵니다. 

결과적으로 업체의 업체번호, 공급 관계의 공급일자, 공급량이 세미나 업체의 속성으로 포함됩니다.

[규칙 3-1] : 작성 관계에 적용

위의 규칙을 세미나 허브 시스템에 그대로 적용해보겠습니다.

회원과 게시글은 일대다 1:n관계입니다. 이 관계에서는 릴레이션으로 변환하지 않고 외래키로만 표현합니다. 일대다 1:n 관계의 1측 개체 릴레이션인 회원 개체의 기본키인 회원번호를 가져와 n측 개체인 게시글 릴레이션에 포함시키고 회원번호를 외래키로 지정합니다. 관계의 속성들인 작성 관계의 속성도 n측 개체 릴레이션에 포함시켜야합니다만, 작성 관계에는 속성이 없습니다.

결과적으로 회원의 회원번호가 게시글 업체의 속성으로 포함됩니다.

규칙 3-2 : 약한 개체가 참여하는 일대다 관계는 외래키를 포함해서 기본키로 지정한다.

이 규칙을 보고서 처음에 이해가 가지 않았던 부분들이 있었습니다. 약한 개체가 정확히 무엇일까요? 다른 개체에 의존적인 개체를 의미합니다. 세미나허브 시스템에서 약한 개체라고 의심할만한 개체를 찾아보았습니다. 처음에 업체와 세미나 관계에서 세미나가 약한 개체인것일까? 라는 생각이 들었습니다만, 좀 더 공부해보니 전혀 그렇지 않습니다.

이유는, 세미나허브에서는 세미나는 업체와 반드시 연관지어서 검색되지 않기 떄문입니다. 만약, 각 세미나가 업체에 약한 개체라면, 기본키를 [세미나번호 + 업체번호] 로 설정하는 것이 적성입니다만, 저의 시스템에 일치하는 내용이 아닙니다.

예시로, 어떤 회원이 '테란 21업 7팩타이밍' 세미나를 찾는다고 생각해보면, 이는 반드시 업체를 통해서 검색될 필요가 없으므로 약한 개체가 아니란 것을 알 수 있습니다.

 

그렇다면, 이러한 예제는 무엇이 있을까요?

Seminar와 Seminar_Schedule(세미나 일정)이라는 관계로 표현할 수 있습니다.

• 약한 개체 : Seminar_Schedule
• 강한 개체 : Seminar
• Seminar_Schedule의 기본키 : (seminar_id, schedule_no)
• 이유 : Seminar_Schedule은 Seminar 없이는 독립적으로 존재할 수 없습니다. 규칙 3-2를 적용하여 세미나의 외래키(Seminar_id)를 포함하여 기본키로 구성합니다.

 

또, 다른 예제로는

Seminar_Room(세미나 룸) 과 Room_Section(강의실 구역)이라는 관계로 표현할 수 있습니다.

• 약한 개체: ROOM_SECTION
• 강한 개체: SEMINAR_ROOM
• 기본키: (room_id, section_code)
• 이유: 강의실 구역은 강의실 없이는 존재할 수 없습니다. 강의실의 외래키(room_id)를 포함하여 기본키를 구성합니다.

규칙 4 : 일대일 1:1 관계를 외래키로 표현한다.

E-R 다이어그램에 존재하는 일대일 1:1 관계도 일대다 1:n 관계처러 릴레이션으로 변환하지 않고 외래키로만 표현합니다. 이때, 데이터의 중복을 피하려면 개체가 관계에 참여하는 특성에 따라 약간 다르게 처리해야 하므로, 

규칙 4-1 : 일반적인 일대일 관계는 외래키를 서로 주고 받는다.

규칙 4-2 : 일대일 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받는다.

규칙 4-3 : 모든 개체가 일대일 관계에 필수적으로 참여하면 릴레이션 하나로 합친다.

이 세가지 규칙이 존재합니다.

 

[규칙 4 적용을 위한] 멘토와 멘티 E-R 다이어그램

아쉽게도, 제 세미나 허브 시스템에는 일대일 1:1 관계가 존재하지 않기에, 각 규칙에 대하여 세미나 허브 시스템에서 만들 수 있는 관계를 예시로 만들어 설명해보겠습니다.

저는 멘티와 멘토 관계를 만들었습니다.

필수조건은 관계차수가 1:1이어야만 합니다. 멘티는 1명의 멘토만 가질 수 있으며, 멘토는 1명의 멘티만 가질 수 있어야만 합니다.

참여 특성은 미정인 것을 볼 수 있습니다. 이 참여 특성에 의해 규칙 4-1, 4-2, 4-3 중 어느것을 적용할지 정해지므로 각 케이스마다 다르게 적용하여 테스트하기 위해 미정으로 선언했습니다. (물론, E-R 모델에서 한줄로 관계를 맺을경우 참여특성은 선택적을 의미하지만, 여기서만 잠시 미정이라고 인식합니다.)

 

멘토와 멘티를 생각하기 전에 가장 적합한 1:1 관계를 찾기 위해 몇가지를 더 생각해보았습니다.

다음과 같이 일대일 1:1 관계를 생성해보려고 했습니다만, 적합한 예시는 아닙니다.

• 회원과 회원프로필 : 회원은 하나의 프로필을 가지고, 하나의 프로필은 하나의 회원을 갖는다.
• 세미나와 담당자(회원) : 회원은 하나의 세미나를 담당하고, 하나의 세미나는 하나의 담당자를 가진다.(물론 기획에 따라 하나의 회원이 여러 세미나를 맡을 수 있음. 억지로 1:1 관계를 만들기 위함입니다.)

왜 적합한 예시가 아닐까요? 이러한 관계는 정규화 관점에서 나눈것이라는 것을 생각해보면 이해할 수 있습니다.

1. 회원과 회원 프로필 예시를 보면, 회원은 회원 프로필은 동등한 개념이 아닌 회원의 개념에서 파생된 개념입니다. 그렇기에 동등한 관계가 아니지요. 언제든지 회원 프로필은 회원에 합쳐질 수 있는 관계인 것 입니다.

2. 세미나와 담당자 예시를 보면, 이 또한 세미나의 담당자는 세미나의 개념에서 파생된 개념입니다. 그렇기에, 동등한 관계가 아닙니다. 언제든지 세미나 담당자는 세미나에 합쳐질 수 있는 관계인 것 입니다.

 

반면, 멘티와 멘토는 어떤 관계일까요?

두개 모두 동등한 1:1 관계입니다. 서로 간의 개체의 존재가 어떤 개체에게서 파생된 개념이 아닙니다. 

 

이제 멘티와 멘토 관계를 가지고서 규칙 4 : 일대일 1:1 관계를 외래키로 표현한다를 각각 적용해보겠습니다.

규칙 4-1 : 일반적인 일대일 관계는 외래키를 서로 주고 받는다.

규칙 4-1을 적용해보겠습니다. 멘토와 멘티는 일반적인 1:1 관계이므로, 릴레이션으로 변환하지 않고 외래키로만 표현합니다. 즉, 멘티와 멘토의 릴레이션들이 서로의 기본키를 주고받아 이를 외래키로 지정합니다. 관계가 가지는 속성들은 관계에 참여하는 개체를 변환한 릴레이션에 모두 포함시킵니다. 즉, 협력 관계의 협력시작일자 속성을 멘티와 멘토 릴레이션 스키마에 각각 포함시킵니다.

 

이렇게 서로의 기본키를 주고받아 각각 외래키로 지정하는 이유는, 협력 관계를 맺고 있는 멘티와 멘토가 서로 누구인지를 표현하기 위해서입니다. 그런데 협력 관계를 표현하기 위해 멘티 릴레이션과 멘토 릴레이션이 외래키를 모두 가질 필요없이, 일대다 1:n 관계처럼 한쪽 릴레이션만 외래키를 가져도 관계를 표현하는데 충분합니다. 외래키 뿐만 아니라 관계가 가지는 속성도 마찬가지입니다. 협력 관계가 가지는 협력시작일자 속성은 멘티 릴레이션과 멘토 릴레이션에 모두 포함시킬 필요 없이 한쪽 릴레이션에만 포함시켜도 충분합니다.

 

그렇다면 두 멘토와 멘티 중 어느 릴레이션에 외래키와 관계의 속성을 포함하는 것이 좋을까요? 위의 E-R 다이어그램에서는 어느쪽 관계에 넣어도 상관없습니다. 현재는 참여특성이 멘티와 멘토 모두 선택적 참여특성을 가지기에 상관 없습니다.

만약 필수조건이 등장하면, 조건이 달라지게 되고, 이를 다룬 내용이 규칙 4-2와 규칙 4-3 입니다. 이제 하단에서 규칙4-2와 4-3을 적용해보겠습니다!

규칙 4-2 : 일대일 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받는다.

[규칙 4-2]를 이용해, 관계에 필수적으로 참여하는 개체의 릴레이션이 선택적으로 참여하는 개체의 릴레이션 기본키를 받아 외래키로 지정합니다. 이때, 관계가 가지고 있는 속성들도 관계에 필수적 참여하는 개체 릴레이션에 함께 포함시킵니다. 

멘티와 멘토를 예제로 들면, 위의 그림처럼 멘티와 멘토 개체 중 참여특성이 필수인 멘티 개체의 릴레이션에만 멘토번호를 외래키로 추가하고, 협력 관계의 속성도 멘티 릴레이션에만 포함시킵니다.

 

왜 이렇게 필수적으로 참여하는 개체에 외래키를 추가하는 것일까요? 관계에 선택적으로 참여하는 멘토 개체의 릴레이션이 외래키를 가지면 어떻게 될까요? 관계에 선택적으로 참여하기 때문에 외래키로 지정된 속성에는 널 값이 저장되는 경우가 존재하게 됩니다. 예시로 들면, 멘티는 필수 참여특성을 가지고 있기에 항상 관계를 맺기 위해서는 하나의 멘티는 하나의 멘토가 필요하지만, 선택 참여 특성인 멘토는 하나의 멘토는 멘티가 없어도 가능한 관계입니다. 즉 NULL이 가능하다는 이야기입니다.

만약, 두 개체 모두 참여특성이 선택적이라면, 외래키를 포함시킬 릴레이션을 자유롭게 선택하면 됩니다.(규칙 4-1과의 차이점은 한개의 릴레이션에만 외래키가 포함됩니다.)

규칙 4-3 : 모든 개체가 일대일 관계에 필수적으로 참여하면 릴레이션 하나로 합친다.

멘티와 멘토가 1:1 관계를 맺으면서 참여특성이 모두 필수적인 E-R 다이어그램입니다. 이때는 관계의 이름을 릴레이션의 이름으로 사용하고, 관계에 참여하는 두 개체의 속성들을 모두 관계 릴레이션에 포함시킵니다. 그리고 두 개체 릴레이션의 키 속성을 조합하여 관계 릴레이션의 기본키로 지정합니다.

 

즉, 멘티와 멘토 릴레이션을 협력 릴레이션 한개로 합칩니다. 그 과정에서 멘티의 기본키인 멘티번호, 멘토의 기본키인 멘토번호는 함꼐 조합되어 (멘티번호, 멘토번호)는 기본키가 됩니다. 그리고 멘티의 멘티이름, 멘티나이, 멘토의 멘토이름, 멘토나이, 마지막으로 협력의 속성인 협력시작일자가 협력 릴레이션 스키마에 포함됩니다.

규칙 5 : 다중 값 속성은 릴레이션으로 변환한다.

만약, 업체의 담당자가 1명이 아니라 다중값일 경우 어떻게 할까요? 업체-담당자 릴레이션으로 변환시켜줍니다! (물론, 담당자 이름은 중복될 수 있습니다만, 회원번호라고 생각하시면 됩니다.)

먼저 이론에 대하여 설명하겠습니다. 관계 데이터 모델 릴레이션에서는 기본적으로 다중 값 속성이나 복합속성을 허용하지 않습니다.(3단계 논리적 설계 시작부분에 위치합니다.) 그렇기에, E-R 다이어그램에 있는 다중 값 속성은 그 속성을 가지고 있는 개체 릴레이션이 아닌 별도의 릴레이션을 만들어 포함시킵니다. 새로 만들어진 릴레이션에는 E-R 다이어그램에서 다중 값 속성으로 표현된 속성뿐 아니라 그 속성을 가지고 있는 개체에 해당하는 릴레이션의 기본키를 가져와 포함시키고 이를 외래키로 지정합니다.

릴레이션의 이름은 자유롭게 정하고, 기본키는 다중값 속성과 외래키를 조합하여 지정합니다.

 

이제, 실제로 적용해보겠습니다. 업체 E-R 다이어그램에는 담당자가 다중 값(2중원) 속성으로 표현되어 있습니다. 담당자가 다중값 속성임을 알 수 있습니다. 관계 데이터 모델 릴레이션에서는 다중값 속성 혹은 복합 속성은 허용하지 않습니다. 그렇기에, 업체 E-R 다이어그램에 있는 담당자 속성은 그 속성을 가지고 있는 업체 릴레이션이 아닌 별도의 업체-담당자 릴레이션을 만듭니다. 업체-담당자 릴레이션에는 담당자 다중값 속성 뿐 아니라, 업체의 업체번호 기본키를 가져와 포함시킵니다. 그렇게 되면 (업체번호, 담당자)가 기본키가 됩니다. 릴레이션의 이름은 업체-담당자 릴레이션이 아닌 자유롭게 설정합니다.

기타 규칙 : 반드시 다대다 N:M 관계에서만 릴레이션으로 변환하는 것은 아니다.

규칙 2 : 다대다n:m 관계는 릴레이션으로 변환한다.

규칙 3 : 일대다1:n 관계는 외래키로 표현한다.

규칙 4 : 일대일 1:1 관계를 외래키로 표현한다.

규칙 5 : 다중 값 속성은 릴레이션으로 변환한다.

위의 규칙들을 보면, 그러면 다대다 n:m 관계나 다중값 속성일때만 릴레이션으로 변환하는것인가? 라는 생각이 듭니다.

하지만, 그렇지 않습니다. 일대다 1:n, 일대일 1:1 관계도 릴레이션으로 변환할 수 있습니다.

특히, 속성이 많은 관계는 관계 유형에 상관없이 릴레이션으로 변환하는 것을 고려할 수 있습니다. (멘토와 멘티 예제를 적용해보면, 협력이라는 관계에 많은 속성이 존재하는 경우이겠습니다.)

 

하지만, 시작에 앞서 유의할점은, 릴레이션의 개수가 많아지는 것은 DBMS의 입장에서 부담이 커지게 되므로, 릴레이션의 개수를 최대한 적게 유지하는 것이 좋습니다. 그러므로, 굳이 1:1 관계를 충분히 외래키만으로도 표현가능하다면 그럴 필요는 없겠습니다.

 

1:1 관계를 외래키로 표현하지 않고 릴레이션으로 변환하는 작업을 해보겠습니다.

[기타규칙 : 1:1 (혹은 1:N) 관계에서 릴레이션으로 변환] 1:1 멘티 멘토 E-R 다이어그램

협력 관계의 속성이 8개로, 상당히 많습니다. 만약, 멘티와 멘토 개체의 외래키로 들어가게 되면 불편할 수 있습니다. (이런 부분은 각자 개인마다 다르므로 협의해서 사용해야 합니다.)

그러므로, 1:1 관계를 외래키로 표현하지 않고, 릴레이션으로 변환하겠습니다.

E-R 다이어그램에서 멘티와 멘토가 맺는 관계는 1:1 이지만, 다대다 N:M 관계처럼 릴레이션으로 변환합니다.

관계의 이름은 릴레이션의 이름으로 그대로 사용하고, 협력 관계의 협력종료일자, 협력시작일자, 세션횟수, 소통방식, 목표, 메포, 협력수준, 주제 속성을 협력 릴레이션의 속성으로 변환합니다. 그리고 협력 관계를 맺고 있는 두 개체에 해당하는 릴레이션들의 기본키를 가져와 포함시키고 이를 외래키로 지정합니다. 즉, 멘티 릴레이션의 기본키인 멘티 번호와 멘토 릴레이션의 기본키인 멘토 번호 속성을 가져와 협력 릴레이션에 포함시키고 이를 외래키로 지정하고, 두 외래키를 조합하여 기본키로 지정합니다. (물론, 협력번호라는 고유번호를 사용해도 됩니다.)

 

[기타규칙 : 자기자신과 관계를 맺는 순환관계]

개체 자기 자신과 관계를 맺는 순환관계에 경우 어떻게 릴레이션 변환 규칙이 어떻게 적용될까요?

릴레이션 변환 규칙을 그대로 적용하면 됩니다. 즉, 순환관계가 N:M 관계일경우에는 릴레이션으로 변환하고, 일대일 1:1이나 일대다 1:N 관계일경우에는 외래키로만 표현합니다. (여기서도 물론, 관계의 속성이 많을경우 기타규칙인 1:1 관계나 1:N 관계에서도 상황에 따라 릴레이션으로 변환하는 과정이 있을 수 있습니다.)

 

[자기자신과 관계를 맺는 순환관계 E-R 다이어그램]

회원 개체가 자기 자신과 관리 관계를 1:N 관계로 맺고 있습니다. 회원 개체는 상사와 부하직원이라는 서로 다른 역할로 관계를 맺고 있지만 관리 관계에 참여하는 실제 개체는 회원개체 하나뿐입니다. 

관리 관계를 어떻게 1:N 관계로 맺고 있는지 확인합니다.

회원개체이면서 상사직원역할 : 회원개체이면서 부하직원역할이 1:N 입니다.

하나의 상사직원은 여러명의 부하직원을 가질 수 있고, 각 부하직원은 한개의 상사직원을 가진다. 라고 요구사항명세서를 작성할 수 있습니다.

 

이제 규칙을 적용해보겠습니다.

하나의 개체만 관계아 참여하는 특수한 형태이지만 순환관계도 기본 규칙을 그대로 적용합니다.

규칙 1 : 모든 개체는 릴레이션으로 변환한다. --> 회원 개체를 회원 릴레이션으로 변환합니다.

규칙 3 : 일대다1:n 관계는 외래키로 표현한다. --> 관리 관계는 일대다 1:N 이므로 릴레이션을 생성하지 않고 관리관계의 1측 개체의 기본키 속성을 N측 개체의 외래키로 지정합니다. 회원 릴레이션의 기본키인 회원번호를 회원 릴레이션의 외래키로 지정해야하는데, 회원 릴레이션의 기본키 속성이름과 외래키 속성이름이 같으면 안되므로 관리 관계를 표현하기 위해 상사번호라는 이름으로 변경합니다. 상사번호라고 설정하는 이유는, 관리 관계에서 1측 상사 역할의 회원 릴레이션의 기본키를 N측에 해당하는 부하직원의 회원 개체 릴레이션의 외래키로 지정한다는 의미이므로 외래키 속성의 이름을 상사번호로 변경합니다.(간단하게 1측 (상사직원 역할) 기본키를 N측(부하직원 역할)의 외래키로 설정한다라고 생각합니다.)

 

3단계 : 논리적 스키마 결과물

모든 처리가 끝난뒤 논리적 스키마 결과물입니다.

회원 릴레이션 테이블 명세서

세미나 릴레이션 테이블 명세서

업체 릴레이션 테이블 명세서

주문 릴레이션 테이블 명세서

게시글 릴레이션 테이블 명세서

[정규화 검증 단계] 데이터베이스 정규화 적용 단계

 

1, 2, 3단계를 통해 E-R 모델과 릴레이션 변환 규칙을 활용해 데이터베이스를 설계했습니다. 추가로, 정규화(normalization)을 이용해 세미나 허브 데이터베이스를 검증하겠습니다. 

정규화 적용 순서

각 정규화를 적용하는 방법과 순서입니다.

정규화란 무엇일까요?

정규화란 이상현상이 발생하지 않도록, 릴레이션을 관련이 있는 속성들로만 구성하기 위해 릴레이션을 분해(decomposition)하는 과정입니다. 

이상현상이란?

불필요한 데이터 중복이 발생하여 릴레이션에 삽입, 수정, 삭제 연산시 부작용이 발생하는 것을 의미합니다.

서로 관계 없는 속성들이 하나의 릴레이션에 모여있기에 발생하는 것이기도 합니다.

이상현상에는 3가지가 존재합니다.

1. 삽입 이상(insertion anomaly) : 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야하는 문제입니다.

2. 갱신 이상(update anomaly) : 중복 투플 중 일부만 변경하여 데이터가 불일치하게 되는 문제입니다.

3. 삭제 이상(deletion anomaly) : 투플을 삭제하면 꼭 필요한 데이터까지 삭제되는 데이터 손실의 문제입니다.

 

[정규화 1단계 ] : 제1정규형(1NF)

제1정규형에 속하기 위해선, 릴레이션에 속한 모든 속성의 도메인이 원자값으로만 구성되어 있으면 제 1정규형에 속합니다.

이와 같은 처리는 릴레이션 스키마 변환 규칙의 [규칙 5 : 다중 값 속성은 릴레이션으로 변환한다] 규칙과 동일합니다.

정규화 1단계는 통과했습니다.

[정규화 2단계] : 제2정규형(2NF)

제2정규형에 속하기 위해선, 릴레이션에 제1정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제2정규형에 속합니다.

 

이를 판단하기 전에, 먼저 함수 종속이 무엇일까요?

회원 릴레이션에서 각 회원번호 속성 값에 대응되는 회원이름과 회원등급 속성의 값은 단 하나입니다. 즉, 회원번호가 회원이름과 회원등급을 결정한다고 볼 수 있습니다. 예를 들어, 회원번호가 12320 인 사람의 이름은 passionfrut200, 등급이 Platinum인 한사람 밖에 없습니다. 그러므로, 회원 릴레이션에서 회원이름과 회원등급 속성은 회원번호 속성에 함수적으로 종속되어 있고, 회원번호는 결정자가 되고, 이름과 등급은 종속자가 됩니다.

 

이해되셨나요?

그렇다면, 완전 함수 종속과 부분 함수 종속은 무엇일까요?

한번, 예시를 들어보겠습니다. 주문 릴레이션(회원번호, 가게번호, 회원이름, 회원등급, 주문금액, 주문일자)가 존재한다고 합시다.(세미나허브와는 연관없는 E-R 모델입니다.) 이떄, 주문릴레이션에는 어느 가게에서 주문했는지 알필요가 있으므로 (회원번호, 가게번호)가 기본키이자 각각 외래키입니다.

이떄, 함수 종속을 나타내보면, 

회원등급과 회원이름은 부분 함수 종속된 상태입니다. 이유는 {회원번호}와 {회원번호, 가게번호} 에서 회원번호가 2번 겹친 속성에 종속되었기 때문입니다.

주문금액과 주문일자는 완전 함수 종속된 상태입니다. 

 

X->Y 의 함수종속이 먼저 있다고 가정합니다.

정리해보면, 완전함수 종속(FFD, Full Functional Dependency)은 릴레이션에서 속성 집합 Y가 속성 집합 X에 함수적으로 종속되어 있지만, 속성 집합 X 전체에 종속된 것이지 일부분에 종속된 것이 아님을 의미합니다.

이와 반대로, 부분 함수 종속(PFD, Partial Functional Dependency)은 속성 집합 Y가 속성 집합 X의 전체가 아닌 일부분에도 함수적으로 종속됨을 의미하므로, 부분 함수 종속 관계가 성립하려면 결정자가 여러 개의 속성들로 구성되어 있어야 합니다.

 

회원 릴레이션, 세미나 릴레이션, 업체 릴레이션, 게시글 릴레이션 모두 완전 함수 종속되어 있는 것을 알 수 있습니다.

[정규화 3단계] : 제3정규형(3NF)

제 3정규형에 속하기 위해선, 릴레이션이 제2정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제3정규형에 속합니다. 

 

이행적 함수 종속이란 무엇일까요?

릴레이션을 구성하는 3개의 속성 집합 X, Y, Z에 대해 함수 종속 관계 X->Y와 Y->Z 가 존재하면 논리적으로 X->Z가 성립합니다. 이떄 속성 집합 Z가 속성집합 X에 이행적으로 함수 종속되었다고 합니다.

 

예시와 함께 살펴보면, 회원릴레이션(회원번호, 회원등급, 적립율)이 있다고 가정합니다.

여기서 이행적 함수 종속이 발생합니다. 이유는 적립율은 회원등급에 의해 결정되는데, 회원등급은 회원번호에 의해 결정되기 때문입니다. 그런데, 회원번호 또한 적립율을 결정하니, 이상현상이 발생할 수 있습니다.

이떄, 분리 방법은 X->Y 속성 집합의 릴레이션과 Y와 Z 속성 집합 릴레이션으로 분해합니다.

 

릴레이션에서 이행적 함수 종속을 제거해서, 모든 속성이 기본키에 이행적 함수 종속이 되지 않도록 릴레이션을 분해하는 정규화 과정을 거쳐야 제3정규형을 만족합니다. 하나의 릴레이션에 하나의 관계만 존재하게 되어 이행적 함수 종속으로 인한 이상현상이 발생하지 않습니다.

[정규화 3.2단계] : 보이스/코드 정규형(BCNF)

보이스/코드 정규형을 성립하기 위해선, 릴레이션의 함수 종속 관계에서 모든 결정자가 후보키이면 보이스/코드 정규형에 속합니다.

 

보이스/코드 정규형의 경우, 이후에 더 필요하다고 여겨지면 살펴보고 넘어가겠습니다.

참고로, 세미나 허브 시스템의 릴레이션은 모두 기본키와 후보키를 하나씩 가지고 있었습니다. 즉, 후보키 속성이 하나밖에 없어 이를 기본키로 설정했기에 모두 보이스/코드 정규형 BCNF를 만족합니다.

후보키가 1개라서 기본키로 설정했다면 보이스/코드 정규형을 만족한다고 여겨도 됩니다.

 

4, 5단계 물리적 설계와 구현

이제 위의 테이블 명세서를 기반으로 실제로 데이터베이스를 생성하겠습니다.

아래의 SQL 질의어를 사용합니다.

위의 속성들 외에도 더 많은 속성이 작성되어있습니다.

create database testdb;
use testdb;  
create table member(
	member_no INT UNSIGNED not null auto_increment comment '회원번호',
	id VARCHAR(40) not null comment '아이디',
	name varchar(20) not null comment '이름',
	pw varchar(64) not null comment '패스워드',
	gender char(1) not null comment '성별',
	age int unsigned not null comment '나이',
	address varchar(255) comment '주소',
	job varchar(30) comment '직업',
	grade varchar(10) not null default 'bronze' comment '등급',
	points INT unsigned not null default 0 comment '적립금',
	inst_dt datetime not null default current_timestamp comment '삽입일시',
    updt_dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '수정일시', 
	del_dt datetime null comment '삭제일시',
	primary key (member_no),
	unique(id),
	constraint chk_points check (points >= 0),
	constraint chk_age check (age >= 0),
	constraint chk_grade check (grade in ('bronze', 'silver', 'gold', 'platinum'))
	constraint chk_gender check (gender in ('M', 'F'))
);

create table company(
	company_no int unsigned not null auto_increment comment '회사번호',
	name varchar(255) not null comment '이름',
	phone varchar(20) comment '전화번호',
	contact varchar(255) null comment '담당자',
	address varchar(255) null comment '주소',
	inst_dt datetime not null default current_timestamp comment '삽입일시',
    updt_dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '수정일시', 
	del_dt datetime null comment '삭제일시',
	primary key(company_no)
);


create table seminar(
	seminar_no int unsigned not null auto_increment comment '세미나번호',
	name varchar(255) not null comment '이름',
	description TEXT not null comment '설명',
	price int unsigned not null default 0 comment '가격',
	available_seats int unsigned not null comment '여석',
	reg_start_date datetime not null comment '신청시작일자',
	reg_end_date datetime not null comment '신청종료일자',
	start_date datetime not null comment '시작일자',
	end_date datetime not null comment '종료일자',
	address varchar(255) comment '주소',
	company_no int unsigned not null comment '회사번호',
	max_capacity int unsigned not null comment '최대인원',
	inst_dt datetime not null default current_timestamp comment '삽입일시',
    updt_dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '수정일시', 
	del_dt datetime null comment '삭제일시',
	primary key (seminar_no),
	foreign key (company_no) references company(company_no) on delete cascade on update cascade,
	constraint chk_price check(price >= 0),
	constraint chk_available_seats check(available_seats >= 0),
	constraint chk_max_capacity check(max_capacity >= 0)
);


create table orders(
	order_no int unsigned not null auto_increment comment '주문번호',
	member_no int unsigned not null comment '회원번호',
	seminar_no int unsigned not null comment '세미나번호',
	quantity int unsigned not null comment '주문량',
	inst_dt datetime not null default current_timestamp comment '삽입일시',
    updt_dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '수정일시', 
	del_dt datetime null comment '삭제일시',
	primary key (order_no),
	foreign key(member_no) references member(member_no) on delete cascade on update cascade,
	foreign key(seminar_no) references seminar(seminar_no) on delete cascade on update cascade,
	constraint chk_quantity check(quantity > 0)
);

create table board (
	board_no int unsigned not null auto_increment comment '게시글번호',
	title varchar(255) not null comment '제목',
	content text not null comment '내용',
	member_no int unsigned not null comment '회원번호',
	inst_dt datetime not null default current_timestamp comment '삽입일시',
    updt_dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '수정일시', 
	del_dt datetime null comment '삭제일시',
	primary key(board_no),
	foreign key(member_no) references member(member_no) on delete cascade on update cascade
);

 

추가적으로 작성해볼 내용들

Foreign Key를 사용할경우, 정확한 잠금의 타이밍에 관하여.

그리고 유니크 인덱스 사용을 피해야하는 이유에 대하여 이후에 더 다루어보겠습니다.