Database Design Using the E-R Model

Design Phase

Initial phase : 데이터 베이스 사용자가 필요로 하는 데이터를 파악합니다.

Second phase : 데이터 모델을 선택합니다.

• 선택된 데이터 모델의 개념을 적용합니다.
• 이러한 requirenent들을 conceptual schema로 변환합니다. (ex : E-R 모델)
• 개발이 완료된 conceptual schema는 기업의 functional requirement을 나타냅니다. (데이터에 대해 수행되는 작업 또는 transaction의 종류를 설명합니다.

Final Phase : 추상적인 data model에서 데이터베이스 구현으로 이동합니다.

• Logical Design : 데이터베이스의 schema 결정, 데이터베이스 설계에서는 relation schema의 경우 "좋은" collection을 찾을 필요가 있습니다.

예를들어 Businesee decision 은 어떤 attribute을 우리가 데이터베이스에 넣을지 

Computer Science decision은 다양한 relation schema간에 어떤 relation을 가져야 하며 attribute들을 어떻게

분배 해야 하는지

• Physical Desing :  데이터베이스의 물리적 구조를 결정합니다.

 

Design Alternatives

data schema를 설계할때 다음 2가지는 꼭 피해야합니다.

• Redundancy : 중복성입니다.

이로 인해 정보를 update할때 다양한 information 복사본 간에 데이터 불일치를 초래할 수 있습니다.

ex)

takes(ID, course_id, sec_id, semester, year, grade)

course(course_id, title, dept_name, credit)

TakesCourses(ID, cource_id, title, dept_name, credit, set_id, semester, year, grade)

 

takes와 course를 합칠때 title, dept_name, credit은 중복으로 인해 데이터 중복입니다.

 

• Imcompleteness : 불완전성

설계가 잘못되면 기업의 특정 부분을 모델링하기 어렵거나 불가능 할 수 있습니다. 

보통 attribute에 대한 값이 없을경우가 해당합니다.

 

이러한 설계이외에 여러가지 dad design이 있습니다. 대신 우리가 선택해야할 good design이 엄청 많습니다.

 

Design Approaches

Entity Relationship Model

entity와 relation을 집합으로 enterprise를 모델링합니다.

• Entity(개체) : 현재 존재하며 다른 객체와 구별되는 객체(object)를 말합니다. 

예를들어 어떤사람, 어떤 회사 등과 같은 것이 entity입니다.

entity는 attribute를 갖습니다. 예를들어 사람은 이름과 주소라는 attribute를 갖습니다.

• Relationship (관계) : 여러 entity들이 어떤 식으로 관련되는것을 말합니다.

entity-relationship diagram으로 도식화되어 표현됩니다.

Normalization Theory : 어떤 design이 나쁜지 공식화하고 테스트 합니다.

 

ER model -- Database Modeling

E-R data model는 데이터베이스의 설계를 용이하게 하기 위해 개발되었습니다.

데이터베이스의 logical structure를 나타내는 enterprise schema의 specification을 허용합니다.

E-R data model는 entity sets, relationship sets, attributes 총 3가지로 구성됩니다.

E-R model은 ER diagram이라는 다이어그램 표현과도 연관되어있습니다. 

이는 데이터베이스의 전체 logical structure를 graphically하게 표현할 수 있습니다.

 

Entity Sets

entitiy는 다른 객체와 구분되는 하나의 object(객체)입니다.

ex) 어떤 사람, 어떤 기업, 어떤 이벤트, 어떤 식물

entity set은 같은 type이나 properties를 가진 entity들의 집합입니다.

ex) 모든 사람, 모든 회사, 모든 나무, 모든 휴일들의 집합 등등

하나의 entity는 attribute의 집합으로 표현할 수 있습니다.

ex) instructor = (ID, name, salary)

    course = (course_id, title, credits)

attribute의 부분집합은 entity set의 primary key를 구성할 수 있습니다. 

entity set은 직사각형으로 나타냅니다.

attribute는 직사각형 안에서 나열해서 작성합니다.

primary key는 attribute에는 밑줄을 긋습니다.

 

Relationship Sets

relationship은 entity들의 관계입니다.

하나의 relationship set은 2개 이상의 entity set사이의 수학적인 관계이며, 수학적으로 나타내면 다음과 같습니다.

{(e1, e2, ......, en) | e1∈E1, e2∈E2, …, en∈En}

이때 (e1, e2, …, en)이 하나의 relationship입니다. 

예를 들면

위와 같은 Relationship에서

(44553, 22222) ∈ advisor 

라고 표현할 수 있습니다.

 

ex)

relationship set advisor를 정의하여 student와 advisor 역할을 하는 instructor간의 연관성을 나타냅니다.

그림으로 나타내면 우리는 관계된 entity끼리 선을 그어줄 수 있습니다.

이러한 relationship set을 ER Diagram을 통해 나타낼 수 있습니다.

다이아몬드 형태는 relationship set입니다.

또한 attribute는 relationship set과 연결될 수도 있습니다.

예를들어 instructrutor 와 student사이에 advisor라는 relationship이 있다고 하면, 이들이 서로 

연결되기 시작한 시점을 date라는 attribute로 가질 수 있습니다.

위를

다음과 같이 표현합니다.

 

Roles

같은 entity 안에서의 relationship을 말합니다. (꼭 서로 다른 entity일 필요는 없습니다.)

entity set의 각 occurrence는 relationship에서 "Role(역할)"을 수행합니다.

위의 그림에서 course_id와 prereq_id는 roles라고 불립니다.

 

Degree of a Relationship Set

• Binary relationship

두개의 entity를 가지고 있습니다. 

대부분의 데이터베이스 시스템의 relationship set은 binary입니다.

• Non-binary relationship sets (ternary relationship)

3개 이상의 entity set을 갖는 경우도 드물지만 있습니다.

예를들어 student는 instructor 아래에서 project를 진행한다고 가정하면

 

Complex Attributes

Attribute types :

• Simple and composite attributes

ex) 주소 : composite attributes (시, 동, 상세주소.....)

• Single-valued and multivalued attributes

ex) 폰 번호 : multivalued attribute

• Derived attributes

다른 attribute로 부터 얻어지는것입니다.

예를 들어 age라는 attribute는 date_of_birth로 부터 구할 수 있습니다.

이때 date_of_birth는 base attribute 혹은 stored attribute라고 불립니다. 

age는 derived attribute입니다. 이는 저장되지는 않지만 필요할때 계산가능하게 합니다.

 

Domain : 각 attribute가 가질 수 있는 값의 집합입니다.

 

Composite attribute는 다른 attribute(sub)로 나뉘어질 수 있습니다.

 

Mapping Cardinality Constraints

relationship set을 통해 다른 entity를 연결할 수 있는 entity의 수를 나타냅니다.

binary relationship를 설명하는 데 가장 유용합니다.

binary relationship set의 경우 mapping cardinality는 다음 유형중 하나여야 합니다.

• One to one
• One to many
• Many to one
• Many to many

→(화살표) 는 one을 의미하고 -(직선)은 many를 의미합니다. 

One-to-one Relationships

A와 B의 어떤 element들은 아예 연결이 되있지 않을 수 있습니다.

student는 최대 1명의 instructor와 relation인 advisor을 통해 연결되있습니다.

student는 stud_dept를 통해 최대 1개의 학과와 연결됩니다.

instructor의 ID와 student의 ID 둘중 하나만 primary key가 될 수 있습니다.

 

One-to-Many Relationships

A와 B의 어떤 element들은 아예 연결이 되있지 않을 수 있습니다.

instrctor는 여러명(0명도 가능)의 student와 advisor라는 relation을 통해 연결될 수 있습니다.

1명의 student는 최대 1명의 instructor와 advisor에 의해 연결됩니다.

instructor의 ID와 student의 ID중에 primary key는 student의 ID입니다.

왜냐하면 student는 오직 1명의 instructor와 연결이 되기 때문입니다. (고유적으로 구별이가능)

 

Many-to-One Relationships

A와 B의 어떤 element들은 아예 연결이 되있지 않을 수 있습니다.

instructor는 최대 1명의 student와 advisor에의해 연결됩니다.

1명의 student는 여러명(0명도 포함)의 instrutor와 advisor에 의해 연결됩니다.

instructor의 ID와 student의 ID중 primary key는 instuctior ID입니다.

왜냐하면 instructor는 오직 1명의 student와 연결이 되기 때문입니다. (고유적으로 구별이 가능)

 

Many-to-Many Relationship

A와 B의 어떤 element들은 아예 연결이 되있지 않을 수 있습니다.

instructor는 여러명(0명도 포함)의 student와 advisor에의해 연결됩니다.

1명의 student는 여러명(0명도 포함)의 instructor와 advisor에 의해 연결됩니다.

instructor ID와 student ID 모두 super key이면서 primary key입니다.

따라서 2개 모두 있어야 합니다.

 

Total and Partial Participation

Total participation (두 줄로 표현) : 모든 entity가 반대편에 다른 entity와 무조건 하나씩은 연결이 되어야합니다.

ex) 모든 student는 무조건 최소 1명의 instuctor과 연결이 되어야합니다.

Partial participation : 몇몇 entity들은 연결될 수도 있고 아닐 수도 있습니다.

ex) instructor는 student와 연결될 수 있고 안될 수도 있습니다.

 

Notation for Expressing More Complex Constraints

선에 표시된 l...h의 의미는 연결 가능 개수입니다. l은 최소, h는 최대를 의미합니다.

최소가 1이면 total participation입니다.

최대가 1이면 최대 연결이 1입니다.

최대가 *이면 연결 개수의 제한이 없다는 의미입니다.

0..* : Partial participation

1..1 : 최대 연결개수가 1인 Total participation

 

위의 relationship set은 ont-to-many와 같은 형태를 보여줍니다.

한명의 instructor가 여러명의 student와 연결가능합니다.

 

Cardinality Constraints on Ternary Relationship

Ternary relationship에서 최대 1개의 화살표가 cardinality constraints를 나타낼 수 있습니다.

예를들어 proj_guide에서 instructor의 화살표는 각 student이 project에 대해 최대 1개의 guide를

가지고 있음을 나타냅니다.

만약 화살표가 여러개일 경우 의미를 정의하는 방법에는 두 가지가 있습니다.

1. 각 A entity는 B 및 C의 unique한 entity에 연결됩니다.

2. (A, B)로 부터의 각 entity는 unique한 C entity에 연결되면, 각 pair(A, C)는 unique한 B에 연결됩니다.

 

이러한 해석의 중의성 때문에 우리는 ternary 에서 화살을 1개보다 많이 사용하는것을 금지합니다.

 

Primary Key

primary key는 entitiy와 realtion를 구분하는 방법을 제시합니다.

 

Primary key for Entity Sets

정의에 따르면 개별 entitiy는 구별됩니다.

데이터베이스의 관점에서 이들 간의 차이는 attribute의 관점에서 표현되어야합니다.

entitiy의 attribute값은 entitiy를 고유하게 식별할 수 있어야 하는 값이어야 합니다.

entity set내의 2개의 entity에는 모든 attribute에 대해 동일한 값을 지정할 수 없습니다.

즉, entity의 key는 entity를 서로 구별할 수 있는 attribute의 집합입니다.

 

Primary Key for Relationship Sets

relationship set의 여러 relation을 구분하기 위해서는 relation set에 있는 entity들의 primary key를 사용합니다.

R을 entitiy set E1, E2, ...En를 포함하는 relationship set이라고 가정합니다.

R의 attributes의 집합은 entity set E1, E2, ..En의 primary key union(조합)으로 구성됩니다.

이는 R안에서 개별 relationship을 가리킵니다.

만약 relationship set R이 attribute a1, a2, ..am이 관련지어져 있는 경우, R의 attribute 집합에는 

attribute a1, a2, ..am도 포함됩니다.

 

mapping cardinality에 따라 primary key 선택이 달라집니다.

entity set E1, E2, ..En의 primary key union는 R의 super key를 형성합니다.

예를들어 위의 relationship set인 "advisor"의 super key는

instructor ID ∪ student ID 로 표현가능합니다.

 

Binay Relationship에서 Primary key고르기

1. Many-to-Many : 양 쪽의 primary key둘다 사용합니다.

2. One-to-Many : Many 쪽에 있는 primary key를 사용합니다.

3. Many-to-One : Many 쪽에 있는 primary key를 사용합니다.

4. One-to-One : 둘 중 하나 고르면 됩니다.

 

Weak Entitiy Sets

위의 schema diagram에서 section entity와 course entity가 있고 ,

이 둘을 잇는 sec_course relation을 생각해보자

이미 section에는 course_id가 있기 때문에 sec_course를 만들 경우, 정보가 중복되게 됩니다.

그러면 relation인 sec_course를 없애면 section과 course간의 관계가 attribute에만 명시되게 됩니다.

 

대안책은 course_id attribute를 section entity에 추가하지 않고 나머지 attribute(section_id, year, semester)만

추가하는것입니다.

그러나 setction entity를 다른 entity와 구별할 수 없습니다. 왜냐하면 attribute가 부족하기 때문입니다.

 

이를 해결하는게 weak entity set입니다.

sec_course를 section entity를 식별하기 위해 필요한 course_id를 제공하는 특수 관계로 만드는 것입니다.

weak entity set은 어떤 entity에 종속된 entity를 말합니다.

weak entity와 primary key를 associating하는 대신, weak entity를 식별 가능하게 해주는

discriminator라고 불리는 추가 attribute와 identifying entity(weak entity의 owner)를 사용합니다. 

 

Strong entity set : weak entity set이 아닌 모든 entity set입니다.

모든 weak entity는 식별 가능한 다른 entity set과 연관되어 있어야 합니다.

existence dependent on the identifying entity set으로도 불립니다.

identifying entity set은 weak entity set을 own(소유) 한다고 표현합니다.

이러한 weak entity set과 identifying entity를 연결하는 relation을 identifying relationship이라고합니다.

 

entity set인 section에서 attribute인 course_id를 삭제한 경우에도 entity set인 section에서 최종적으로 작성하는

relational schema에는 attribute인 course_id이 포함됩니다.

 

ER Diagram에서 weak entity는 이중 사각형으로 표현됩니다.

weak entity의 discriminator는 점선을 그어줍니다.

weak entity set과 strong entity를 이어주는 relation은 이중 다이아몬드로 표시해줍니다.

weak entity는 반드시 total participation이어야 합니다.

weak entity set의 primary key = strong entity의 primary key ∪ weak entity의 discriminator

 

Redundant Attributes

위의 diagram을 보면 student entity의 dept_name 과 department의 dept_name이 중복되는것을 볼 수 있습니다.

따라서 제거를 해줘야 합니다.

그러나 table로 변환할 때 나중에 설명하겠지만 attribute가 재도입되는 경우가 있습니다.

이는 뒤에서 살펴보겠습니다.

 

Reduction to Relation Schemas

entity set와 relationship set는 데이터베이스의 내용을 나타내는 relation schema로 균일하게 표현될 수 있습니다.

E-R diagram을 준수하는 데이터베이스는 schema 집합으로 나타낼 수 있습니다.

각 entity set과 relationship set에 대한 해당 entity set 또는 relation set의 이름이 할당된 고유한 schema가 

있습니다.

각 schema에는 고유한 이름을 가진 여러개의 열(attribute)이 있습니다.

 

Representing Entity Sets

Entity set을 표현하는 여러가지 방법입니다.

 

Strong entity set : 있는 그대로 만듭니다.

ex) student(ID, name, tot_cred)

Weak entity set : 원래 그 attribute들에 자신이 속한 entity의 primary key를 가져와 table을 만듭니다.

Renpresentation of Entity Sets with Composite Attributes

Composite attributes : 여러개의 attribute로 이루어진 attribute

 왼쪽 instructor entity set을 보면

name이란 attribute에 또 다른 2개의 attribute가 있습니다.

바로 first_name과 last_name입니다.

이는 entity set으로 표현할 때 name_first_name, name_last_name 으로 표현가능합니다.

만약 모호성이 없는 경우 접도사가 생략됩니다.

즉, name_first_name = first_name 입니다.

 

 

왼쪽의 instructor schema의 multivalued 와 derived 제외하고

확장형 instructor schema를 만들 수 있습니다.

 

 

 

 

 

 

 

 

 

 

 

 

Representatoin of Entity Sets with Multivalued Attributes

어떤 entity E의 multivalued attribute인 M이 있을 경우, E의 Primary key와 M의 attribute를 가지고 

새 table을 만듭니다.

instructor의 Multivalued인 phone_number는 

inst_phone = (ID, phone_number)

같이 표현할 수 있습니다.

 

또한 multivalued의 값들은 각각 primary key와 엮일 수 있습니다.

예를 들어 ID = 22222이고 phone_number = {456-7890, 123-4567} 이라 할떄

(22222, 456-7890) 과 (22222, 123-4567) 로 나눌 수 있습니다.

 

 

 

 

 

 

 

Representing Relationship Sets

Many-to-many relationship set의 경우, 두 entity sets의 primary key로 table을 구성합니다.

One-to-many relationship set은 many쪽에서 primary key만 있으면 됩니다.

One-to-one 은 둘 중 아무거나 선택해도 상관없습니다.

 

Redundancy of Schemas

Many-to-one과 One-to-many relationship에서 many쪽이 total인 경우, one쪽의 primary key를 many쪽에

추가해주는 것 만으로도 둘의 관계를 나타낼 수 있습니다. 

즉, 새로운 table을 안만들고 attribute만 추가해주면 됩니다. primary key도 바꿔줄 필요없습니다.

ex) inst_dept relation set에 대한 schema를 생성하는 대신 entity set인 instructor에서 발생하는 schema에 

dept_name attribute를 추가해라

 

One-to-one 에서는 둘 중 하나의 primary key를 다른 한 쪽에 추가해 주면됩니다.

만약 many쪽에서 total이 아닌 partial일 경우에 many쪽에서attribute를 추가할 경우 null 값이 채워질 수 있습니다.

 

weak entity set과 strong entity set을 연결하는 relationship에 해당하는 schema는 redundant(중복)됩니다.

ex) sec_course schema에 나타날 attribute가 이미 section schema에 이미 포함됩니다.

 

Specialization

Top-down process : sub-grouping을 해서 tree형태와 비슷하게 grouping을 하는것입니다.

sub-grouping은 상위 레벨 entity에는 적용되진 않는 attribute나 praticipate를 가진 

lower level entity set이 됩니다.

ISA 라는 triangle component로 표현됩니다.

Attribute inheritance : lower level entity들은 high level entity의 모든 attribute와 relationship participation을

상속 받습니다.

 

Overlapping : 두 개의 속성이 동시에 가능한 경우 (겸하기 가능)

Disjoint : 무조건 둘 중 하나 (동시 불가)

 

Representing Specialization via Schemas

Method 1

higher level entity를 schema로 구성합니다.

각각의 lower level entity를 schema로 구성합니다. 이때 higher level의 primary key를 포함합니다.

하지만 만약에 이러한 정보를 얻기 위해서는 employee schema는 high level와 lower level 둘 다 접근해

join을 해야만 얻을 수 있습니다.

그렇기 때문에 level이 깊어질 수록 비효율 적입니다.

 

Method 2

lower level 에서 high level 까지 싹다 저장합니다.

하지만 정보의 중복이 일어나게 됩니다. 대신 join할 필요는 없어집니다.

 

Generalization

A bottom-up design process : lower entity set을 이용해 공통 분모를 찾아 high entity를 구성합니다.

구성된 결과는 specialization과 같습니다.

 

Completeness constraint

high level entity set이 무조건 lower level entity set에 속해야 하는지를 지정합니다.

total : 무조건 lower level entity set들 중 하나에는 속해야합니다.

partial : 안 속해도 됩니다. (generalization의 기본값입니다.)

화살표에 total 이라고 적음으로서 ER Diagram상에 표시할 수 있습니다.

모든 student 는 graduate 또는 undergraduate 입니다. 

generalization은 low entity set의 공통점으로 만들어지므로 거의 대부분은 total 입니다.

 

Aggregation

우리가 이전에 봤던 ternary relationship 인 proj_guide입니다.

여기서 student의 evaluation을 저장하고 싶다고 가정해보자

 

relationship set인 proj_guide와 eval_for은 overlapping 입니다.

모든 eval_for relationship은 proj_guide와 동일합니다.

하지만 어떤 proj_guid relationship은 eval_for와 다를 수 있습니다.

따라서 우리는 proj_guide를 버리면 안됩니다.

 

proj_guide와 eval_for의 중복을 버릴 수 있습니다.

한명의 student는 특정한 instruction와 특정한 project를 진행합니다.

student, instructor, project는 한 묶음으로 묶습니다.

 

Common Mistake in ER Diagram

redundancy가 문제가 됩니다.

multivalue가 문제가 됩니다.

 

Entities vs Attributes

entity set 사용 : entity 쪼개기 (한 entity가 여러 entity를 가질 수 있습니다.)

attribute 추가 : sigle value

 

Entities vs Relationship sets

relation 으로 명시적으로 이을지, 아니면 바로 이을지 결정합니다.

entity간에 일어나는 action은 relationship으로 지정하라는게 권장됩니다.

 

Binary vs Non-binary relationships

non binary relationship은 여러 구분된 binary relationship sets로 만들 수 있지만

n-ary relationship이 각 entity들이 하나의 relation에 참여한다는 것을 잘 보여줍니다.

어떤 relationship은 non-binary 보다 binary 쓰는 것이 좋을 수 있습니다.

parent라는 termary relationship : child, father, mother

두개의 binary relation으로 쪼갭니다. child-father / child-mother

 

Converting Non-Binary Relationship to Binary Form

보통 non binary 관계는 binary관계를 이용해 나타낼 수 있습니다.

A, B, C 사이의 Relation R을 entity set E로 바꾸고, relation을 A-E, B-E, C-E로 쪼갭니다.

E에 identifying attribute를 추가하고, R의 attribute를 E에 추가합니다.