3주차. Entity-Relationship Model

1. 데이터베이스의 설계

1) 개요

Real World의 Entity에는 Feature와 이에 해당하는 Value가 존재한다. 이들은 Comceptual World에서 각각 Entity Type과 Attribute, Instance로 매핑되고, Mini-World에서는 (MySQL을 기준으로) Table(Relation), Field, Instance로 구성된 데이터로 매핑된다.

최종적으로 구조화된 데이터가 DB에 저장되는데 이 때, "저장된 내용과 현실세계의 개체가 얼마나 일치하는가"가 데이터베이스의 완성도를 결정짓는다.

2) 순서

① Mini-World의 requirement를 collect & analysis 한다.

② Conceptual Design

• ERD를 구성한다.

③ Logical Design

• 상용 DBMS를 사용하여 Conceptual Design에서 구축한 ERD를, 구체적인 데이터베이스 구조로 변환한다.

④ Physical Design

• 성능, 보안, 가용성 및 확장성과 같은 다양한 측면을 고려하여 데이터베이스 시스템을 최적화하는 것을 목표로 한다.

⑤ Transaction Implementation

• 트랜잭션 관리 및 격리, Concurrency Control 등의 작업이 포함된다.

⑥ Application Program Design

⑦ Functional Analysis

• DFD(Data Flow Diagram) 등의 순서 다이어그램을 사용한다.

2. Company Mini-World

1) ER 다이어그램 표기법

① Entity는 사각형으로 표시한다.

• 부모 Entity 없이 존재할 수 없는 Entity를 Weak Entity라 하는데, Weak Entity(Derived Entity)는 두 개의 사각형으로 표시한다.

② Relationship은 마름모로 표시한다.

• 식별 관계인 경우 두 개의 마름모로 표시한다.
• 일반적으로 Weak Entity는 부모 Entity와 식별 관계를 맺는다. 예를 들면, 강의가 부모 Entity이고 분반이 Weak Entity이면, 분반 Number는 강의의 식별자와 함께 쓰일 때에만 제대로 식별될 수 있다.
• 이러한 뜻에서 weak entity의 기본키는 일부만 식별할 수 있다는 의미로, primary key라 하지 않고, partial key라 한다.

③ Attribute는 원으로 표시한다.

• 해당 속성이 PK(기본키)인 경우, 밑줄을 긋는다.
• Multivalued Attribute(다치 속성)인 경우, 두 개의 원으로 표시한다. 여기서 다치 속성이란, 한 사람이 여러 취미를 가질 수 있음과 같이 하나의 Entity가 여러 개의 Attribute를 가질 수 있는 경우를 말한다.
• Compoiste Attribute(복합 속성)인 경우, 다른 Attribute를 해당 Attribute의 곁가지로 포함시킨다. 여기서 복합 속성이란, 시, 구, 동으로 구분되는 Address와 같이 하나의 Attribute가 여러 개의 Attribute로 구성될 수 있는 경우를 말한다.
• Drived Attribute(파생 속성)인 경우, 점선으로 된 원으로 표시한다. 여기서 파생 속성이란, 사람의 나이를 주민등록번호로 결정할 수 있는 것과 같이 다른 속성으로부터 값이 결정될 수 있는 속성을 말한다.

④ Participation의 경우 Total Participation이면 두 줄로, Partial Participation이면 한 줄로 나타낸다.

⑤ Cardinality의 경우, 일대일인 경우 1:1로, 일대다인 경우 1:n으로, 다대다인 경우 m:n으로 나타낸다.

• 자주 쓰이지는 않지만, 한 명의 학생이 1개 이상 6개 이하의 과목에 등록할 수 있는 경우, (1, 6)과 같이 카디널리티에 표시할 수 있다.

2) Requirement

Entity는 사각형을 의미하는 []로, Relationship은 마름모를 의미하는 <>로, Attribute는 원을 의미하는 ()로 나타내기로 하자.

① 회사는 여러 [부서]들로 구성된다.

• 각 부서마다 (고유한 이름), (고유한 번호), (부서를 관리하는 사원)이 있다.
• [사원]이 (부서를 관리하기 시작한 날짜)도 저장한다.
• 한 [부서]는 여러 (위치)에 있을 수 있다.

② 한 [부서]는 여러 [프로젝트]들을 <관리>한다.

• 각 프로젝트는 (고유한 이름), (고유한 번호), (한 개의 위치)를 갖는다.

③ 각 [사원]에 대해서 (이름), (사회보장번호), (주소), (급여), (성별), (생년월일)을 저장한다.

• 한 [사원]은 한 [부서]에 <속하지만>, 여러 [프로젝트]에 <관여>할 수 있다.
• 한 [사원]이 관여하는 [프로젝트]는 그 [사원]이 소속된 [부서]가 <관리>하는 프로젝트가 아니어도 무방하다.
• 반드시 한 [부서]의 각 사원이 각 [프로젝트]를 위해 일하는 (주당 근무 시간)을 기록해야 한다.
• 각 [사원]에 대한 <직속>[상사]도 저장한다.

④ 보험 가입을 위해 각 [사원]의 [[부양가족]]을 기록한다.

• 각 [[부양가족]]에 대해서 (이름), (성별), (생년월일), (사원과의 관계)를 기록한다.

3) ERD 설계

ERD의 특성상 Requirements에 기재되지 않았더라도, 상식 선 내에서 적절한 내용이 추가될 수 있기 때문에 DB 설계자의 디자인 방식에 따라 같은 요구 사항에 대해서도 다른 ERD가 만들어질 수 있다.

위에서 주어진 Requirement ①, ②, ③, ④를 각각 빨간색, 노란색, 민트색, 보라색으로 나타내었다.

① Department - Employee

• Name, Number를 복합 기본키로 갖고(Number만 기본키로 가져도 상관 없다), Location을 다치 속성으로 갖는다.
• 또한 Number_of_employees는 요구 사항에 명시되지는 않았지만, 상식적인 선에서 필요한 값이므로 포함한다. 이 때, 사원의 수는 간단한 쿼리문으로 얻을 수 있으므로 파생 속성으로 정의한다.
• 하나의 부서를 관리하는 사원은 (상식적으로) 한 명이므로, Employee 테이블과 1:1 Manages(관리) 관계를 맺으며, 이 관계로 인해 파생되는 Start_date는 관계 속성(Relationsip Attribute)이라 한다.
• 특정 Employee만 Department를 관리하므로 Employee는 Manages에 Partial Participate한다. 반대로 모든 Department는 빠짐없이 Employee에 의해 Manage되므로, Total Particiapte한다.

② Department - Project

• Name, Number를 복합 기본키로 갖고(Number만 기본키로 가져도 상관 없다), Location 속성을 갖는다.
• 부서와 프로젝트의 관계는 1:N이며, 프로젝트는 항상 부서에 의해 Control 되므로 Total Particiapte이지만, 모든 부서가 항상 프로젝트를 수행하지는 않을 수도 있으므로 부서는 Partial Participate으로 표시한다. (물론 프로젝트가 끊임이 없다는 가정이 있다면, Total Particiapte일 것이다.)

③ Employee - Department & Project

• Employee는 SSN을 기본 키로 갖고, Name, Bdate, Address, Salary, Sex를 속성으로 갖는다.
• 이 때 Name은 (일반 속성으로 정의해도 상관 없다) FirstName, MiddleInitial, LastName으로 구성된 복합속성으로 정의하였다. (ex. John F Kennedy)
• 사원은 하나의 부서에 속하고, 부서는 여러 명의 사원이 속하기 때문에 사원과 부서의 관계는 N:1이다. 이 때, 모든 사원이 소속 부서가 있고, 사원이 없는 부서라는 것은 없기 때문에 양쪽 다 Total Particiapte으로 정의한다.
• 사원은 여러 개의 프로젝트에 관여하고, 프로젝트 또한 여러 사원에 의해 관리되므로, 사원과 프로젝트의 관계는 M:N이다.
• 사원이 진행하는 프로젝트는 소속 부서에서 관리하는 프로젝트가 아니어도 무방하므로, 부서에서 진행할 프로젝트가 없더라도, 다른 부서의 프로젝트를 함께 진행할 수 있다. 따라서 사원은 항상 진행해야 할 프로젝트가 있다는 가정 하에 사원은 프로젝트 진행에 Total Participate한다고 정의하였다. 당연히 모든 프로젝트는 진행하는 사원이 있어야 하므로 Total Participate 한다.
• 사원과 프로젝트의 관계로 인해 파생되는 Hours는 관계 속성으로 정의한다.
• 여러 명의 사원에 대해 직속 상사가 같을 수 있다는 가정 하에 사원과 직속 상사의 관계는 N:1이다. 이 때, 모든 사원이 직속 상사인 것은 아니며, 직속 상사가 없는 사원도 있을 수 있으므로 양쪽 다 Partial Participate 한다.
• 이와 같이 자신의 PK를 FK로 사용하는 경우(recursive 관계), 관계에 대한 역할 이름(Supervisor와 Supervisee)을 명시해야 한다.

④ Employee - Dependent

• Dependent는 Employee에 의해 파생되는 Weak Entity이므로 두 개의 사각형으로 표시한다.
• 일반적으로, 부모 Entity와 Weak Entity는 식별 관계로 놓기 때문에 두 개의 마름모로 표현하였다. 이 때, Weak Entity의 partial key는 점선으로 된 밑줄로 표시한다. Name을 partial key로 사용하기 위해선 한 사원의 부양 가족 중에는 같은 이름이 없어야 한다.
• 부양 가족은 Name을 partial key로 갖고, Sex, Birth_date, Relationship 속성을 갖는다.
• 한 명의 사원에 대해 여러 명의 부양 가족이 있으므로, Employee와 Dependent의 관계는 1:N이다.
• 모든 Employee가 부양 가족을 갖지 않는다는 가정이 있다면, Employee는 부양에 Partial Participate한다. 반면, 부양 가족은 항상 부양하는 사원이 있어야 하므로, Total Participate 해야 한다.

4) Schema

ERD 설계(Conceptual Design)가 완료되었으니 Logical Design을 수행해야 한다. 위에 주어진 요구 사항에 대한 Schema를 구성해보자. ERD와 마찬가지로 데이터베이스도 설계자의 Design Choice에 따라 조금씩 다르게 구성될 수 있다.

① Employee

• ERD에서 복합 속성으로 표시하였던 Fname, Minit, Lname이 실제 DB로 구축될 때에는 각각의 Field로 구성된 것을 볼 수 있다.
• 사원에 대한 직속 상사는 Employee 테이블에 같이 표현하였으며, 기본키 SSN을 참조하고 있다. 만약 직속 상사가 없다면, NULL을 넣어야 할 것이다.
• Department와 일대다 관계이므로 Employee 테이블에서 Department의 기본키인 Dnumber(Dno)를 관리해야 한다. 이 때, Dno를 외래키(FK)라 한다.

② Department

• Department는 Location을 다치 속성으로 갖기 때문에 Location을 표시하기 위해 별도의 Table을 생성하였다.
• Mgr_start_date는 Employee와 Department의 manage 관계가 1:1이므로, Employee나 Department 아무데에나 넣어도 무방하다. 그저 Department 테이블에 넣는게 더 자연스러울 것이라는 Design Choice일 뿐이다.

③ Dept_Locations

• Department가 여러 개의 위치를 가질 수 있기 때문에 Department의 식별자와 위치를 매핑하는 별도의 테이블을 생성하였다.
• 사실, 다치 속성은 별도의 Table을 생성하는 대신, 하나의 Table 안에 여러 개의 Row로 표현하는 방법도 가능하다. 다만, 데이터의 중복이 심해지기 때문에 좋은 방법은 아니다.
• 이 때, Dlocation이나 Dnumber만으로는 아이템을 식별하기 어렵기 때문에 복합 기본 키를 사용하고 있다. (모든 테이블에는 기본 키를 정의하는 것이 좋기 때문)

④ Project

• Project와 Department는 다대일 관계이므로 Project에서 Department의 기본키를 관리해야 한다.

⑤ Works_On

• 다대다 관계인 Employee와 Project를 연결하기 위한 연결 테이블이다.
• 각 테이블의 기본 키는 연결 테이블에서 관리해야 한다.
• Relationship을 테이블로 만들 수 있다는 내용은 매우 중요하다.

⑥ Dependent

• Employee와 일대다 관계이기 때문에 Dependent 테이블에서 Employee의 기본키를 관리해야 한다.
• Dependent_name만으로는 제대로 식별이 안 되기 때문에 Essn을 포함한 복합 기본 키를 사용하고 있다.

3. 보충 설명

1) NULL의 의미

① Unknown

• 값이 존재하긴 하나 알지 못함.
• 휴대폰 번호가 있으나 알지 못하는 상황 등

② Unavailable

• 값이 존재하지만 의도적으로 보류함.
• 판매가 일시적으로 중단된 상품의 수량을 NULL로 표시하는 등

③ Inapplicable

• 해당 아이템에 대해서는 적용되지 않는 속성임.
• 출신 대학 필드는 대학 미졸업자에게 적용되지 않는 등

2) Relationship Attribute

위에서 이미 다룬 내용이지만, 중요한 내용이므로 한번 더 정리하도록 하겠다. Relationship도 Attribute를 가질 수 있다. 이 때 Relationship의 타입에 따라 Attribute를 포함하는 방법이 달라진다.

① 일대일 관계

• 두 Entity 중 아무데나 포함시켜도 무방하다.
  

② 일대다 관계

• 반드시 N에 해당하는 Entity에 포함시켜야 한다.
  

③ 다대다 관계

• 반드시 새로운 연결 테이블을 생성해야 한다.

3) Weak Entity

위에서는 Weak Entity를 별도의 Entity로 정의하는 방법에 대해서만 소개하였다. 그러나 사실, Weak Entity를 별도의 Entity가 아닌 부모 Entity에 Composite-Multivalued Attribute로 포함시키는 방법도 있다.

하지만 Dependents가 많은 Attribute를 가지고 있기 때문에 복합 다치 속성으로 나타내는 것은 좋은 방법이 아니다. 뿐만 아니라, 위의 예시에서는 등장하지 않았지만, Dependents가 어떤 Entity와 독립적인 Relationship에 참여하게 되더라도, 이를 표현할 수 없을 것이다.

따라서, Weak Entity가 많은 Attribute를 갖거나, 다른 Entity와 독립적인 관계를 맺어야 할 경우 Weak Entity로 정의하는 것이 좋다.