[데이터베이스시스템] Chapter 6: Database Design Using the E-RModel

이번 챕터에서는 데이터 모델링을 하는 방법을 배운다. 데이터 모델링은 건축에서 지반 설계를 하는 작업과 유사하다. 즉, 현실 세계의 복잡한 개념을 단순화하고 추상화시켜 데이터베이스화하는 과정을 의미한다. 

 

Design Alternatives

database schema를 설계할 때, 두가지 오류를 피해야 한다. 

1. Redundancy(중복성) : 좋지 못한 설계는 정보의 반복을 야기할 수 있다. 

       데이터 중복은 정보의 업데이트가 있을 때 data inconsistency를 초래할 수 있다. 

2. Incompleteness (불완전성) : 좋지 못한 설계는 기업의 특정 측면을 수행하기 어렵거나 불가능하게 만들 수 있다. 

 

ER model -- Database Modeling

ER model은 다음 세가지 개념을 가지고 있다. 

   - entity sets

   - relationship sets

   - attributes

그리고 이 모델을 다이어그램으로 표현하면 데이터베이스의 전반적인 논리적 구조를 표현할 수 있으며, 이를 ER diagram이라고 한다. 

 

entity : 다른 opject와 구분이 가능한 opject를 의미한다. 또한 하나의 entity는 여러 속성들의 집합이다. 

entity set: 같은 타입 (변수와 같은)의 entity들의 집합을 의미한다. ex. set of all persons, trees, companies ..

relationship : 여러 entity 간의 관계를 나타낸다. 

 

ralationship set와 관련된 속성이 있을 수도 있다. 예를 들어, instructor entity와 student entity 사이 advisor이라는 관계가 있다고 가정했을 때, 이 advisor이 data라는 속성을 가질 수가 있다. 이 속성을 통해 student가 advisor와 연관되기 시작한 시점을 알 수 있다. 

밑이랑 같은 의미이다. 

Roles : 관계에서의 entity가 행하는 기능을 role이라고 한다. 보통은 모든 role가 구분되므로 굳이 적지 않지만, 아래 예시처럼 한 관계에서 여러 role을 수행할 경우, 서로 구분하기 위해서 적는다. 아래의 경우, 모든 수업의 정보가 담긴 entity를 course라고 할 때, 하나의 entity에서 특정한 과목의 선행 과목을 표현해야 할 때, 두가지의 역할을 수행하므로 다음과 같이 표시해준다. 

Degree of a Relationship Set

degree는 해당 relationship set에 참여하는 entity set의 개수를 의미한다. 대부분 2개의 entity가 관계를 맺는 Binary relationship 이 대다수이다. non-binary relationship set 예시는 다음과 같다. 

Complex Attributes

Attribute type에는 다양한 유형이 있다.

simple attribute: 더 이상 분해가 안되는 속성

composit attribute: 또 다른 속성으로 분해할 수 있는 속성

    예를 들어 주소와 같은 것은 다시 시와 동으로 나뉠 수 있으므로 composit attribute이다.

 

또한, 속성 값의 개수에 따라 single value와 multi value로 나뉜다. (관계형 DB에서는 무조건 속성들이 atomic해야하나, ER에서는 그렇지 않아도 된다.) multi value의 경우 보통 {}로 묶어서 표시한다. 

ex. 전화번호: simple하면서 multivalue한 속성. (더이상 분해되지는 않지만, 한 사람이 여러 전화번호를 가질 수 있으므로)

Derived attribute: 다른 속성으로부터 유도된 속성

    한 사람의 속성에 생년월일이 있고, 나이가 있다면, 나이는 생년월일을 통해 계산될 수 있으므로 유도속성이다. 

Mapping Cardinality and Participaition Constraint

E-R 스키마에서는 어떤 제약 조건을 어느 정도로 준수해야할지를 정의할 수 있는데, 크게 Mappint Cardinality와 Participation Constraint가 있다. 

Mapping Cardinality Constraints

mapping cardinality : relationship set을 통해 매핑되는  개별 entity들의 개수를 의미한다. binary relationshep set에서 가장 유용하다. 다음 4 가지의 유형으로 나뉜다. 

1. one to one

관계가 없는 경우도 one to one에 해당된다. 즉, 관계가 있을 경우 unique하다는 의미이다. 

화살표는 one, 화살표가 없는 선은 many를 의미한다. 

 

2. one to many

A의 entity가 B의 여러 엔터티와 관계를 맺고 있을 경우를 의미한다. 관계가 없는 경우도 one to one에 해당된다.

 ex instructor와 student의 관계. 지도 교수는 여러 사람을 가르칠 수 있지만 학생의 지도 교수는 딱 한사람이다. 

**many to one은 one to many와 반대로 생각하면 된다. 

4. many to many

 

Total and Partial Participation

위에서 살펴본 것과 같이 모든 entity가 관계를 맺을 필요는 없다. 하지만 반드시 모든 entity가 관계를 맺어야 하며 그것을 표시하고 싶을 수 있다. 위 그림과 같이 line이 2개인 경우에는 모든 학생이 하나 이상의 instructor과 관계를 맺고 있다는 것이다. partial participation은 우리가 기존에 배운 관계들이다. 

 

Notation for Expressing More Complex Constraints

더 많은 관계를 보이고 싶을 때, cardinality (관계와 대응되는 entity의 수)의 max와 min을 표시할 수 있다. 

보통 l(min)..h(max)로 표시한다. 

l의 값이 1일 경우는, 모든 entity가 최소 1개의 관계를 가지고 있어야 한다는 것이므로 total participation을 의미한다. 

*은 limit이 없는 것을 의미한다. 

위 예시의 경우 student는 total participation이며 one의 관계이다. 반면 instructor은 0명부터 더 많은 student를 가르칠 수 있다는 것을 의미한다. (many to one 관계) 

 

Cardinality Constraints on Ternary Relationship

ternary relationship은 세개의 관계를 가지고 있을 때를 의미한다. 

ternary relationship에서 cardinality를 표시하고 싶을 때에는 최대 하나의 화살표만 표시할 수 있도록 하고 있다. (해석이 여러 개 발생하는 혼란을 방지하기 위해) 

Primary Key

1. Primary key for entity sets

 entity를 unique하게 식별가능하게 해주는 attribute의 집합 (이전 챕터에서 자세하게 설명함) 

2. Primary key for relationship sets

 ER 모델의 특징 중 하나. ER 모델에서는 여러 관계가 있으므로 이러한 관계를 식별할 수 있어야 한다.

 E1 .. En의 entity set과 관계를 맺는 R relationship set이 있다고 가정해보자. 관계를 식별하기 위해서는, R은 각 entity의 primary key의 합집합을 superkey로 가져온다. 

 primary key를 가지면서, advisor의 자체적인, 추가 속성이 있을 수 있을 것이다. (student와 instructor가 관계를 맺은 날짜 등) 이러한 날짜도 추가적인 속성으로 들어올 수 있을 것이다. 

 

Binary relationship의 경우 집합이 결정된 다음, 관계의 primary key를 구하기 위해서는 

해당 관계의 cardinality를 고려해야 한다. (일단 연결되어있는 entity의 primary key가 관계의  super key중에서 없어도 될 것들을 제거하고 minimum 상태로 만들면 candidate key. 여기서 하나 고르면 primary key)

• Many to Many relationships: relation을 unique하게 식별하기 위해서는, entity의 primary key의 집합에서 더 제거할 수가 없다. 따라서 집합의 조합 자체가 primary key이다. 
• One-to-Many relationships: 교수와 학생으로 예시를 들 때, many에 속하는 학생의 primary key가 relationship의 primary key가 된다. (생각해보자. 만약 교수의 primary key를 relationship의 primarykey로 한다면, 한 교수는 여러 학생을 가르칠 수 있기 때문에, 어떠한 관계를 나타내는 건지 식별하기가 어려울 것이다.)
• Many-to-one relationships: 마찬가지로 many에 속하는 entity의 primary key가 relationship의 primary key가 된다.
• One-to-one relationships: 서로의 관계가 unique하기 때문에, 둘 중 하나의 entity의 primary key를 relationship의 primary key로 사용할 수 있을 것이다. (어떤 것을 사용할지는 자유)

3. Primary key for weak entity sets

Weak Entity Sets

*중복성을 제거하면서도 두 관계를 명시적으로 표현할 수 있는 방법? 

  예를 들어 현재 section과 course 사이에는 관계가 있는데, 관계를 표현하기 위해 sec_course 집합을 생성했다고 가정해보자. 그런데 그 정보는 이미 section에 course_id가 있기 때문에 중복이 된다. 하지만, 중복을 제거하기 위해 sec_course를 제거해버리면 두 관계가 명확히 표현되지 않는다. 

 

-> 이러한 상황에 대한 또 다른 대안은, section에서 course_id를 저장하지 않는 것이다. 그렇게 되면 section은 독립적이게 식별할 수 있는 속성이 부족해진다. 이러한 문제를 해결하기 위해, 우리는 sec_course relationship을 추가하여 section entity를 식별하기 위한 정보 (ex. course_id)를 제공하도록 한다. 

section(분반)은 course entity가 없으면 존재할 수 없는 개념이다. 따라서 여기서 course는 strong entity이고, identifying entity이다. section은 weak  entity이다. 이 section은 course_id를 제거하는 대신 sec_course라는 관계(identifying relationship)를 두어, course에 의존적인 상황임을 보인다. 점선으로 표시한 속성들은 descriminator(식별자)라고 한다. 이는 약한 개체 타입에서 개별 개체를 구분하는 속성이다. 또한, course가 있는 이상 secstion도 모두 존재하므로 total participation이다. (그래서 선 두개) 여기서 section의 primary key는 (course_id, sec_id, semester, year)이다. 

Redundant Attributes

 

stud_dept가 있으므로 student에 있는 dept_name은 정보의 중복이므로 제거될 필요가 있다. (테이블로 변환할 때는 이야기가 또 달라지는데, 이것은 나중에 살펴보기로 하자.) 

 

Reduction to Relation Schemas

(ER diagram → Schemas Diagram)

 

데이터베이스 설계를 완료하기 위해서는, ER 모델링을 관계형 데이터베이스로 변환할 수 있어야 한다.

Entity와 Relationship은 각각 하나의 테이블로 표시하고, Attribute는 테이블 column으로 표현될 수 있다. 

 

Representing Entity Sets

weak entity set의 경우, relation schema로 만들 때 strong entity set의 primary key를 추가해야 한다. 예시는 다음과 같다. 

Representation of Entity Sets with Composite Attributes

schema로 변환하는 과정에서, 기존 entity 속성이 single 타입인지, multi value 타입인지, simple 타입인지, composed 타입인지 구분할 필요가 있다. 

single/multi : 값을 하나만 가진다면 single, 여러개 가질 수 있다면 multi value. 위 예시에서 phone number의 경우, 한 사람이 여러 전화번호를 가질 수 있기 때문에 multi value이다. 

simple/composed: 속성이 더 작은 단위로 분해될 수 있으면 composed, 단일한 속성이면 simple이다. 위 예시에서 ID의 경우 simple이지만, name의 경우 first_name, middle_initial, last_name으로 나뉠 수 있으므로 compoased 속성이다. 

 

**age()의 경우, date_of_birth에 따라 결정되는 속성이다. 

schema로 변환할 때,

1. composite 속성의 경우

"flattened" 시킨다. 즉, 위 속성 중 name의 경우, name_first_name. name_middle_initial .. 등으로 다 분리시킨다. (이 경우는 name이 없어도 고유하므로 first_name라고 써도 된다. 다른 속성에서도 first name이 나오는 경우가 있다면 name_first_name으로 써야 한다. 

2. multivalue 속성의 경우 : 반영하지 않는다. age()와 같이 derived된 속성도 반영하지 않는다. 따라서 이러한 것들을 지켜서 schema로 변환하면 다음과 같이 된다. 

-> multivalue 속성의 경우 일단 제외시켰는데, 이걸 어떻게 반영할 것인가?

추가적인 테이블을 만들면 된다. 

 

Representation of Entity Sets with Multivalued Attributes

multivalue한 속성이 M이고, 해당 속성이 entity E에 속해있는 경우, EM이라는 이름의 속성을 만든다. 

EM 스키마에서 primary key는 E의 primary key이며, multivalue한 값을 속성으로 갖는다. 예를 들면 다음과 같은 스키마이다. 

ex. time_slot entity의 경우는 어떨까? time_slot은 multivalue 중에서도 특별한 케이스이다. 왜냐하면 하나의 primary key + multivalue 조합이기 때문이다. 보통 multivalue attribute의 경우, 위와 같이 기존 entity의 primary key + multivalue 속성 조합으로 relation schema를 새롭게 만드는데, 이러한 경우 만들면 기존 entity와 중복이 발생한다. 따라서 추가적인 릴레이션 스키마를 만들지 않아도 되므로 최적화를 할 수 있다.  

하지만 이럴 경우 sec_time_slot에서 time_slot_id를 FK로 참조할 때 문제가 된다. 왜냐하면 time_slot_id에 해당하는 튜플이 없거나, 여러 튜플이 있을 수도 있기 때문이다. 따라서 FK로 참조하고 싶다면 최적화를 포기하고 릴레이션을 따로 만들어야 한다. 

 

Representing Relationship Sets

 

위에서 relation은 크게 4가지 경우로 나뉜다고 했었다. (one to one, many to one, one to many, many to many) 

그리고 relation 유형에 따라 schema로 바꾸는 방식이 다르다. 

 

1. many to many

이러한 경우에는 advisor이라는 추가적인 table을 만들어야 하고, 양쪽의 primary 속성을 가져와야 한다. many to many의 경우에는 두 primary key의 조합이 있어야 식별되므로 두 key의 조합이 advisor의 primary key이다. 

 

2. many to one, one to many

이러한 경우에는 추가적인 table을 만들 필요가 없다. 

이러한 경우, 추가적인 테이블 없이 instructor, student(N)의 속성에 department(1)의 primary key가 FK로서 추가된다. 한 department에 여러 instructor, student이 있을 수 있으므로, 이쪽에서는 속성으로 추가할 수는 없다. 

그렇다면 instructor와 student의 관계에서는 어떨까? 위 논리에 따르면 여기서도 1 : N 관계이므로 student에 instructor의 ID가 FK로서 추가될 것이다. 하지만 위와 다른 점이 있다면, 여기는 total participation이 아니라는 것이다. 즉 advisor이 없는 student도 있을 경우, 그러한 경우에는 instructor ID를 속성으로 추가한다면 널값이 될 것이다. 따라서 우리는 어느 쪽이 더 좋을 지 선택을 해야 한다. 

(관계에 대한 추가적인 테이블을 만드는 것 vs 널값이 생기더라도 추가적인 테이블을 만들지 않고 student에 속성으로 추가하는 것)

만약 advisor이라는 추가적인 relation table을 만들 경우에는

advisor(i_id, s_id)

일 것이고, 여기서 many 관계 측이 primary key가 될 것이다. 

실제 설계에서는 두 경우에서 상황에 따른 선택을 하지만, 수업에서는 total이 아닌 one to many / many to one 관계에서는 별도의 테이블을 만드는 것만 허용하는 것으로 하기로 한다. 

 

3. one-to-one

두 entity 중 하나를 선택해서 그것의 primary key를 다른 entity의 FK로 넣으면 된다. 즉 다음 두 경우가 가능하다. 

 

instructor(instructor.ID, name, salary, student.ID)

student(student.ID, name, tot_cred)

 

or

 

instructor(instructor.ID, name, salary)

student(student.ID, name, tot_cred, instructor.ID)

 

그런데 이 경우도 total이 아닌 경우 null을 허용하지 않겠다고 하면 advisor이라는 추가적인 table을 만들 것이다. 

그리고 그 table의 primary key는 둘 중에서 자유롭게 선택하면 된다. 

 

Redundancy of Schemas

weak entity의 경우 section에 course_id가 추가될 것이다. 그리고 이러한 관계에서는 별도의 테이블을 따로 만들 필요는 없다. 만들면 결국 section 테이블과 같은 테이블이 나오기 때문이다. 

 

Extended E-R Features

extenden E-R은 기존 E-R model의 단점을 보완하기 위해 Specialization(특수화), generalization(일반화), aggregation(집단화) 등의 기능을 제공한다. 

 

Specialization

Top-Down design process와 관련된 개념인데, 우리는 specialization을 통해 하위집합/상속관계를 나타낼 수 있다. 이를 통해 다양하게 entity를 쪼갤 수 있다. 

 

Attribute inheritance : CPP의 클래스 개념을 생각하면 이해하기 편하다. low-level의 entity set은 higher level entity set의 모든 속성과 관계를 상속받는다.

다음은 Specialization의 예시이다. 

person에서 서브그룹으로 employee와 student로 쪼갠 모습이다. 그리고 각각 추가속성으로 salary와 tot_credits를 넣었다.

이를 통해 다음과 같은 설계 방향이 가능하다.

1. inheritance: instructor을 예로 들었을 때, 이 entity의 속성은 rank, salary, ID, name, street, city일 것이다. 
2. overlapping : 중복이 허용되는 경우, 즉 employee이면서 student인 경우가 허용되는 설계라면, 양 쪽 다 같은 사람이 있을 수 있을 것이다. 
3. disjoint : 2번과 반대로 중복이 허용되지 않는 설계도 가능하다. employee면서 student인 경우를 금지하는 것이다. 
4. total and partial
   1. total: 서브그룹에서 모든 학생은 해당 서브그룹안에 소속되는 것. (ex. 모든 person은 employee or student이다)
   2. partial은 person을 employee와 student로 나누었을 때, 두 그룹 어디에도 속하지 않는 경우가 있는 것을 의미하다.

그리고 이러한 것을 shema로 변환할 때 두 가지 망법이 가능하다.

 

Mathod 1 : parent entity의 primay key만 상속받는 경우

정보의 중복을 최소화시킨다는 장점은 있으나, 정보를 얻을 때 필수적으로 join을 해야하므로 시간이 오래 걸린다는 단점이 있다. 

 

Mathod 2: 모든 속성을 상속받는 경우 

1번 방법과 반대로, 정보를 얻을 때 시간은 오래 안걸리지만(join 연산을 하지 않으므로) 정보의 중복이 많이 생긴다는 단점이 존재한다. 

 

Generalization

Generalization은 Specialization과 반대로 bottom-up design process에서 쓰인다. 상위 집합을 만들어내는 개념으로, 즉 하위 entity로부터 상위 entity를 추출하는 방식이다. 해석이 다를 뿐 결국 나오는 entity set의 결과는 동일하다.

Completeness constraint

상위 집합의 entity가 무조건 하위 집합에 속해야하는지 여부를 결정할 수 있다. total은 무조건 속해야하고, partial은 속하지 않아도 괜찮다. 위에서 다룬 개념과 유사하다. 보통 Partial generalization이 디폴트값이라고 한다. 항상 전체 하위 집합을 대상으로 generalization을 하지는 않기 때문이다. 만약 total generalization을 한다면 다음과 같이 표시한다고 한다.

Aggregation

 

Aggregation이란 관계 자체를 하나의 entity로 묶는 방식을 의미한다. 

project, instructor, student 관계에 대한 관계가 필요한 경우, 밑 그림과 같이 하나하나 연결해줄 수도 있지만, 불필요한 정보의 중복이 발생한다. 또한, proj_guide가 항상 eval_for와 같지 않으므로 둘 중 하나를 제거하기도 어려운 상황이다. 

위 문제를 없애기 위해, 다음과 같이 할 수 있다. 

세 entity의 관계 자체를 하나의 entity로 묶어서 eval_for와 연결시키고 있다. 이렇게 하면 정보의 중복 없이 원하던 의도대로 표현이 가능하다. 

Reduction to Relational Schemas

aggregation을 schema로 표현하기 위해서는,

새로운 schema에 집합으로 이루어진 (proj_guide)의 primary key와 evaluation의 primary key를 포함하면 된다.

eval_for (s_ID, project_id, i_ID, evaluation_id)

위와 같이 표현할 수 있을 것이다. 이러한 경우 proj_guide schema는 불필요하다. 

 

Design Issues

 

E-R 다이어그램을 설계할 때 실수할 수 있는 것들에 대해 알아보자. 

1. 내부 속성으로 정의된 것을 추가 속성으로 넣는 경우: 이미 관계가(stud_dept) 정의되어 있으므로 student 내부에 있는 dept_name을 제거해야 한다.

2.  relationship attributes에서 과제물이 2개 이상인 경우 다음과 같이 저장하는 것은 적절하지가 않다. 

해결 방법은 relationship attributes를 multi value로 설정하거나, 아니면 assignment entity를 추가하는 방법이 있다. 상황에 따라 적절히 판단하자. 

Entities vs. Attributes

phone_number가 1개일 경우에는 속성으로 추가해도 무관하지만, 여러개인 경우 따로 entity화 시키는 것이 나을 것이다. 

 

Entities vs. Relationship sets

보통 entities 사이에서 발생하는 이슈를 묘사할 때 relationship set을 사용한다. 

 

Converting Non-Binary Relationships to Binary Form

 

추가적인 entity set을 통해 비이진 -> 이진 변환이 가능하다. 

하지만, 위에서 Ra, Rb, Rc로 분리시켜서 관계를 맺는 것과 R을 사용하여 관계를 맺는 것이 다른 경우도 있다. 따라서 분리시킬 때 추가적인 제약 조건을 두는 것이 좋다. 혹은, E를 weak entity set으로 두어 Ra, Rb, Rc에 의해 식별되도록 할 수도 있다.