데이터베이스 : 6장 Database Design Using the E-R Model

1. Overview of the Design Proces

1) Design Phases

1. Initial phase(초기 단계) : prospective(잠재적인) 데이터베이스 사용자의 데이터 요구를 완전히 characterize(특성화)한다.
2. Second phase(두번째 단계) : 데이터 모델을 선택
   • 선택한 데이터 모델의 개념 적용
   • 이러한 요구사항을 데이터베이스의 conceptual schema로 변환
   • 완전히 개발된 conceptual schema는 enterprise(기업)의 기능적 요구사항을 나타냄
     • 데이터에 대해 수행될 작업(또는 transactions)의 종류를 설명
3. Final phase(마지막 단계) : abstract data model에서 데이터베이스 구현으로 이동
   • Logical Design(논리적 설계) : 데이터베이스 스키마를 결정
     1. 데이터베이스 설계를 위해서는 "good" relation schemas의 collection을 찾아야 함
     2. Business decision : 데이터베이스에 기록해야 하는 속성은 무엇인가?
     3. Computer Science decision : 어떤 relation schema를 가져야 하며 다양한 relation schema 사이에서 속성을 어떻게 분배해야 하는가?
   • Physical Design(물리적 설계) : 데이터베이스의 물리적 레이아웃을 결정

2) Design Alternatives

• 데이터베이스 스키마를 설계할 때 다음과 같은 두 가지 주요 pitfall(함정)을 방지해야 한다.
  1. Redundancy(중복성) : 잘못된 설계로 인해 정보가 반복될 수 있다.
     • 정보의 중복 표현은 다양한 정보 복사본 간에 data consistency(데이터 불일치)를 초래할 수 있음
  2. Incompleteness(불완전성) : 잘못된 설계는 기업의 특정 측면을 모델링하기 어렵거나 불가능하게 만들 수 있음
• 나쁜 디자인을 피하는 것만으로는 충분하지 않다. 우리가 선택해야 할 좋은 디자인이 많이 있을지도 모른다.

3) Design Approaches

• Entity Relationship Model(ER model)
  • enterprise(기획)를 Entity 및 Relationship의 집합으로 모델링
    1. Entity : enterprise에서 다른 객체와 구별할 수 있는 "thing" 또는 "object"
       • 속성의 집합에 의해 묘사됨
    2. Relationship : 여러 엔티티 간의 연결
  • entity-relationship diagram(ERD)에 의해 도식화로 표현된다.
• Normalization Theory(정규화 이론)
  • 어떤 디자인이 나쁜지 공식화하고 테스트하는 것 
    → 디자인을 더 좋게 만들어 줌

 

2. The Entity-Relationship Model

1) ER model - Database Modeling

• ER data model은 데이터베이스의 전반적인 논리 구조를 나타내는 enterprise schema를 지정할 수 있도록 하여 데이터베이스 설계를 용이하게 하기 위해 개발됨
• ER data model은 세 가지 기본 개념을 사용
  
  1. entity sets
  2. relationship sets
  3. attributes
• ER model은 데이터베이스의 전반적인 논리 구조를 그래픽으로 표현할 수 있는 연관된 다이어그램 표현인 ER diagram을 가지고 있다.

(1) Entity Sets

• Entity는 존재하는 object(객체)이며, 다른 객체와 구별할 수 있음
  • 예 : 특정 사용자, 회사, 이벤트, 공장
• Entity Set은 동일한 속성을 공유하는 동일한 유형의 엔티티의 집합
  • 예 : 모든 사용자, 모든 회사, 모든 트리, 모든 휴일의 집합
• 엔티티는 속성들의 집합, 즉 엔티티 집합의 모든 구성원이 소유하는 descriptive property(설명 속성)으로 표현된다.
  • 예 : instructor = (ID, name, salary) / course = (course_id, title, credits)

• 속성들의 하위 집합은 엔티티 집합의 primary key를 형성한다. 즉, 집합의 각 구성원을 고유하게 식별

<ER diagram에서의 Entity sets의 표현>

• Entity sets은 다음과 같이 그래픽으로 표현될 수 있다
  • 직사각형은 entity sets을 나타냄
  • 엔티티 직사각형 내부에 나열된 Attributes
  • 밑줄은 primary key 속성들을 나타냄

(2) Relationship Sets

• Relationship은 몇몇 엔티티 사이의 연결이다.
  • 예: 44533 (Peltier)            advisor            22222(Einstein)
          student entity        relationship set     instructor entity
• Relationship set은 n ≥ 2개의 엔티티들 사이에서 수학적 관계이고, 각각 entity set {(e1, e2, .... en) | e1 ∈ E1, e2 ∈ E2, ..., en ∈ En}에서 가져온 것이다. → (e1, e2, .... en)은 relatiionship
  
  • 예시 : (44553, 22222) ∈ advisor
• 예시 : student과 그들의 advisor 역할을 하는 instructor 사이의 연관성을 나타내기 위해 advisor이라는 relationship set을 정의한다.
  • 그림적으로 우리는 관련된 엔티티 사이에 선을 그린다.

<ER diagram을 통한 relationship sets을 표현>

• Diamond는 relationship sets을 표현

• relationship sets와 관련된 속성이 있을 수 있다.
  • 예시 : entity set인 instructor와 student 사이의 relationship set인 advisor는 student가 advisor와 연관되기 시작한 시점을 추적하는 속성 날짜를 가질 수 잇음

<ER diagram을 통한 표현>

① Roles

• relationshp의 entity set은 distinct(구별)될 필요가 없다
  • entity set의 각 발생은 relationship에서 "role"을 수행
• "course_id"와  "prereq_id" 레이블을 role이라고 함

② Degree of Relationship Set

• Binary relationship
  • 두 개의 엔티티 집합(또는 degree two)를 포함
  • 데이터베이스 시스템의 대부분의 relationship set은 binary이다.
• 둘 이상의 엔티티 집합 간의 relationship은 드물다. 대부분은 binary이다.
  • 예시 : 학생들은 강사의 지도 아래 연구 프로젝트를 수행
    • relationship proj_guide는 instructor, student, project 간의 ternary relationship이다.

 

3. Complex Attributes

1) Complex Attributes

• Attribute type
  • Simple(atomic) 속성과 Composite(복합) 속성
  • Single-valued 속성과 multivalued 속성
    • 예시 : multivalued 속성 - phone_numbers
  • Derived 속성
    • 다른 속성에서 계산할 수 있음
    • 예시 : age, given date_of_birth → 출생일을 통해 나이를 계산 가능
• Domain : 각 속성에 대해 허용되는 값 집합

(1) Composite Attributes

• 복합 속성을 사용하면 속성을 하위 부분(다른 속성)으로 나눌 수 있음

<ER diagram으로 복합속성 표현>

age의 경우 출생일로 계산 가능

4. Mapping Cardinalities

1) Mapping Cardinality Constraints

• relationship set을 통해 다른 엔티티를 연결할 수 있는 엔티티 수를 나타냄
• binary relationship sets을 설명하는 데 가장 유용
• binary relationship sets의 경우 mapping cardinality(차수)는 다음 유형 중 하나여야 한다.
  1. One to one(1 : 1)
  2. One to many(1 : N)
  3. Many to One(N : 1)
  4. Many to many(N : M)

참고 : A 및 B의 일부 요소는 다른 집합의 어떤 요소에도 매핑되지 않을 수 있음

<ER diagram으로 cardinality constraints 표현>

• 관계 집합과 엔티티 집합 사이에 "one"를 나타내는 방향선(→)을 그리거나 "many"를 나타내는 방향선(—)을 그려 cardinality constraint를 표현
• Husband와 Wife 사이의 one-to-one 관계
  • 한 husband는 spouse를 통해 최대 한 명의 wife와 연관됨
  • 그리고 한 wife도 spouse를 통해 최대 한 명의 husband과 관계가 있음

(1) One-to-Many Relationship

• instructor와 student 사이의 one-to-Many 관계
• instructor는 advisor를 통해 여러 명(0명 포함)의 student와 연결
• student는 advisor를 통해 최대 한 명의 instructor와 연결

(2) Many-to-Many Relationship

• student는 여러 club의 회원이 될 수 있음
• club은 여러 명의 student를 회원으로 둘 수 있음

2) Total and Partial Participation

• Total participation(double line으로 표시) : 엔티티 세트의 모든 엔티티가 관계 집합에서 하나 이상의 관계에 참여
  • 예시) advisor에서 student의 participation은 total이다.
    • 모든 학생은 반드시 한 명의 강사와 연관되어야 한다.

• Partial participation : 일부 엔티티는 관계 집합의 어떤 관계에도 참여할 수 없다
  • 예시) 강사의 advisor 참여는 partial이다.

3) Notation for Expressing More Complex Constraints

• line은 l...h 형식으로 표시된 최소 및 최대 cardinality를 가짐 → 여기서 l은 최소값이고 h는 최댓값
  • 최소값 1은 total participation을 나타냄
  • 최댓값 1은 엔티티가 최대 하나의 관계에 참여함을 나타냄
  • 최대값 *은 제한이 없음을 나타냄
• 예시) 강사는 0명 이상의 학생에게 조언할 수 있음. 학생은 단 한 명의 advisor가 있어야 함

4) Cardinality Constraints on Ternary Relationship

• 우리는 ternary(또는 그 이상의 degree) 관계에서 최대 하나의 화살표가 cardinarlity constraint를 나타낼 수 잇도록 허용
• 예시 : proj_guide에서 instructor로 가는 화살표는 각 학생이 프로젝트에 대해 최대 하나의 guide를 가지고 있음을 나타냄
• 화살표가 둘 이상인 경우 의미를 정의하는 두 가지 방법이 있음
  • 예시) A, B, C의 3원 관계 R과 B, C의 화살표는 다음을 의미할 수 있음
    1. A의 각 엔티티는 B와 C의 특정 엔티티와 연관되어 있거나
    2. (A, B)의 각 엔티티 쌍은 고유한 C의 엔티티와 연관되어 있고, (A, C)의 각 엔티티 쌍은 고유한 B의 엔티티와 연관되어 있다.
  • 각 대안은 서로 다른 형식으로 사용됨
  • 혼란을 피하기 위해 두 개 이상의 화살을 금지

5. Primary Key

1) Primary Key

• Primary key는 엔티티와 관계를 구분하는 방법을 지정하는 방법을 제공한다.
  • 고려사항
    1. Entity Sets
    2. Relationship sets
    3. Weak entity sets

(1) Primary key for Entity Sets

• 정의에 따르면, 개별 엔티티는 distinct하다.
• 데이터베이스 관점에서, 그들 사이의 차이는 그들의 속성의 관점에서 나타나야한다.
• 엔티티의 속성값은 엔티티를 고유하게 식별할 수 있어야 한다.
  • entity set의 두 엔티티는 모든 속성에 대해 정확히 동일한 값을 가질 수 없다.
• 엔티티의 키는 서로 구별하기에 충분한 속성 집합이다.

(2) Primary key for Relationship Sets

• Relationship set의 다양한 relationship를 구별하기 위해 relationship set에 있는 엔티티의 개별 기본키를 사용
  • R : 엔티티 집합 {E1, E2, ... En}을 포함하는 relationship set
  • R의 Superkey : 엔티티 집합 {E1, E2, ... En}의 기본키 조합으로 구성된다.
  • 만약 relationship set R이 속성 {a1, a2, ... am}을 포함한다면, R의 슈퍼키는 속성 {a1, a2, ... am}을 포함한다.
• 예시) relationship set "advisor"
  • 슈퍼키는 instructor.ID와 student.ID로 구성된다.
• relationship set에 대한 기본키의 선택은 relationship set의 mapping cardinality에 따라 달라짐

<Choice of Primary key for Binary Relationship>

• Many-to-Many relationship : 기본키의 preceding union(이전 조합)은 minimal superkey이고 primary key로 선택됨
• One-to-Many relationship : "Many" 측의 기본키는 minimal superkey이며 primary key로 사용됨
• Many-to-One relationship : "Many"측의 기본키는 minimal superkey이며 primary key로 사용됨
• One-to-One relationship : 참여 엔티티 세트 중 하나의 기본키는 minimal superkey를 형성하며 둘 중 하나를 primary key로 선택할 수 있음

(3) Weak Entity Sets

• course_id, semester, year 그리고 sec_id로 고유하게 식별되는 section 엔티티를 고려해보자
  • Section 엔티티들은 Course 엔티티와 관련이 있음
    → 엔티티 집합 section과 course 사이에 sec_course 관계를 생성한다고 가정
  • Section에는 Section과 관련된 Course를 식별하는 course_id라는 속성이 이미 존재하기 때문에 sec_course의 정보는 중복!!!
  • 해결방법 1 :  sec_course 관계를 제거하는 것이지만, 그렇게 함으로써 section과 course 사이의 관계가 암시적이 되므로 바람직하지 않음!!
  • 해결방법 2 :  course_id 속성을 section 엔티티에 저장하지 않고 나머지 속성 section_id, year 그리고 sememster만 저장하는 것!!
    → 하지만, 엔티티 집합 section은 특정 section 엔티티를 고유하게 식별하기에 충분한 속성을 가지지 않게 됨
•  이 문제를 처리하기 위해, 우리는 sec_course 관계를 추가 정보, 이 경우 section 엔티티를 고유하게 식별하는데 필요한 course_id를 제공하는 special  relationship으로 취급하여 해결!!!
• weak entity set : 엔티티 존재가 identifying entity(식별 엔티티)라고 불리는 다른 엔티티에 의존하는 것이다.
  • 기본키를 weak entity와 연결하는 대신 식별 엔티티를 사용하여 약한 엔티티를 고유하게 식별하기 위해 discriminator(판별기)라는 추가 속성을 사용
• Weak entity set이 아닌 엔티티 집합을 strong entity set이라고 함
• 모든 weak 엔티티는 식별 엔티티와 연관되어야 한다. 즉, weak 엔티티 집합은 식별 엔티티 집합에 existence dependent(따라 존재)
  • 식별 엔티티 집합은 weak 엔티티 집합을 own(소유)한다고 함
• identifying relationship : weak 엔티티 집합과 식별 엔티티 집합을 연관시키는 관계

Note!!
엔티티 집합 Section에서 course_id 속성을 삭제했음에도 불구하고 나중에 명확해질 이유로, 우리가 엔티티 집합 Section에서 생성한 관계형 스키마는 course_id 속성을 가지고 있음

<ER diagram으로 weak entity set 표현>

• E-R diagram에서 weak entity set은 이중 직사각형을 통해 표시됨
• 우리는 점선으로 설정된 weak 엔티티의 discriminator를 강조
• weak entity set와 identifying strong entity set를 연결하는 relationship set은 이중 다이아몬드로 표시됨
• section에 대한 기본키 - (course_id, sec_id, semester, year)

 

6. Removing Redundant Attributes in Entity Sets

1) Redundant Attributes(중복 속성)

• 엔티티 집합이 있다고 가정
  • student 엔티티의 속성 : ID, name, tot_cred, dept_name
  • department 엔티티의 속성 : dept_name, building, budget
• 관계 집합 stud_dept을 사용하여 각 학생이 연관된 학과를 가지고 있다는 사실을 모델링한다.
• 아래 student 엔티티의 dept_name 속성은 관계에 존재하는 정보를 복제하므로 중복됨 → 제거해야 함
• BUT : 테이블로 다시 변환할 때 나중에 보게 될 것처럼 경우에 따라 속성이 다시 도입됨

<University Enterprise에 대한 ER diagram>

 

7. Reducing ER Diagrams to Relational Schemas

1) Reduction(전환) to Relation Schemas

• 엔티티 집함 및 관계 집합은 데이터베이스의 내용을 나타내는 relation schemas로 균일하게 표현될 수 있음
• E-R diagram을 준수하는 데이터베이스는 스키마 모음으로 표시될 수 있음
• 각 엔티티 집합과 관계 집합에 대해 해당 엔티티 집합과 관계 집합의 이름이 할당된 고유한 스키마가 있음
• 각 스키마에는 고유한 이름을 가진 여러 개의 column(일반적으로 속성에 해당)이 있음

2) Representing Entity Sets

• Strong entity set은 동일한 속성을 가진 스키마로 전환된다
  → course(course_id, title, credits)
• Weak entity set은 identifying strong entity set의 기본키에 대한 열을 포함한 테이블이 된다.
  → section(course_id, sec_id, sem, year)

3) Representing Relationship Sets 

(1) One-to-Many relationship

• Many측에서 total인 One-to-Many 관계 집합과 Many-to-One 관계 집합은 "Many"측의 기본키를 포함하는 추가 속성을 추가함으로써 표현될 수 있음
• 예시) 관계 집합 inst_dep에 대한 스키마를 만드는 대신 instructor 엔티티 집합에서 발생하는 스키마에 dept_name 속성을 추가한다.
  → department(dept_name, building, budget)
       instructor(ID, name, salary, dept_name)
       student(ID, name, tot_cred, advisor, dept)
  
  • 교재에서 advisor이라는 table을 따로 만듦
    → 기본적인 변환규칙에는 맞지 않음
    → 없는 경우, 즉 partial한 경우 null일 수 있으므로 다음과 같이 처리
    
• parcitipation이 "many"측에서 partial인 경우, 스키마를 "Many"측에 해당하는 스키마의 추가 속성으로 대체하면 null 값이 발생할 수 있음

(2) Many-to-Many relationship

• Many-to-Many 관계 집합은 스키마로 표현되며, 두 참여 엔티티 집합의 기본키 속성과 관계 집합의 모든 descriptive attribute(설명 속성)이 포함됨
• 예시) 관계 집합 member에 대한 스키마

(3) One-to-One relationship

• One-to-One 관계 집합의 경우 두 엔티티 집합에 해당하는 테이블 중 하나에 추가 속성을 추가할 수 있다.
• Husband(ID, address, phone, wife) 또는 Wife(ID, address, phone, husband)

4) Representing of Entity Sets with Composite Attributes

• 복합 속성은 각 구성 요소 속성에 대해 별도의 속성을 만들어 평평하게 만듦
  • 예시) 구소 요소 속성 first_name 및 last_name을 가진 복합 속성 name을 가진 엔티티 집합 instructor의 경우 엔티티 집합에 해당하는 스키마는 name_first_name 및 name_last_name 두 가지 속성을 가짐
    • ambiguity(모호성)이 없는 경우 prefix(접두사)는 생략
      → name_first_name을 first_name으로 할 수 있음
• multivalued attribute(다중값 속성)은 무시하고, 확장된 instructor 스키마는
  • instructor(ID, first_name, middle_initial, last_name, street_number, street_name, apt_number, city, state, zip_code, date_of_birth)

5) Representing of Entity Sets with Composite Attributes

• 엔티티 E의 다중값 속성 M은 별도의 스키마 EM으로 표현된다.
• 스키마 EM은 E의 기본 키에 해당하는 속성과 다중값 속성 M에 해당하는 속성이 존재
• 예시) instructor의 다중값 속성 phone_number를 스키마로 표현
  → inst_phone=(ID, phone_number)
• 다중값 속성의 각 값은 스키마 EM에 있는 관계의 개별 튜플에 매핑된다.
  • 예시) 기본키 22222와 전화번호 456-7890 및 123-4567을 가진 instructor 엔티티는 두 개의 튜플에 매핑됨
    → (222, 456-7890) 및 (222, 123-4567)
• Special case : 엔티티 time_slot에는 기본키 속성 외에 하나의 속성만 있으며 해당 속성은 다중값이다.
  • Optimization(최적화) : 엔티티에 해당하는 관계를 만들지 말고 다중값 속성에 해당하는 관계만 작성
  • time_slot(time_slot_id, day, start_time, end_time)
  • Caveat(주의) : 이 최적화로 인해 section의 time_slot 속성(from sec_time_slot)은 외래키가 될 수 없다.

8. Extended E-R Features

1) Specialization(<=> Generalization)

• Top-down design process(하향식 설계 프로세스) :집합의 다른 엔티티와 구별되는 엔티티 내의 sub-grouping(하위 그룹)을 지정
• sub-grouping : 속성을 가지거나 상위 엔티티 집합에 적용되지 않는 관계에 참여하는 하위 엔티티 집합이 됨
  • ISA로 표시된 triangle component에 의해 표시됨(예시 : instructor "is a" person)
• Attribute inheritance(속성 상속) : 하위 수준 엔티티 세트는 연결된 상위 수준 엔티티 세트의 모든 속성 및 관계 참여를 상속함

(1) 예시

• Overlapping(중복) : employee and student
• Disjoint(분리) : instructor and secretary
• Total and partial
  • Total : employee의 경우 instructor와 secretary로만 이루어짐
  • Partial : person의 경우 employee와 student 이외가 존재함

(2) Representing Specialization via Schemas

1. Method 1

• 상위 수준 엔티티에 대한 스키마 형성
• 각 하위 수준 엔티티 집합에 대한 스키마를 구성하고 상위 수준 엔티티 집합의 기본키 및 local attribute들을 포함
• 단점 : 정보를 얻기 위해서는 낮은 수준의 스키마와 높은 수준의 스키마에 해당하는 두 가지 관계에 접근해야 함

2. Method 2

• 모든 local 및 inherited 속성을 사용하여 각 엔티티 집합에 대한 스키마를 구성
• 단점 : 학생과 직원 모두를 위해 name, street 및 city가 중복 저장될 수 있음

탐색 속도 : Method1 < Method2
중복 문제 : Method1 < Method2

 

9. Entity-Relationship Design Issues

1) Common Mistakes in E-R Diagrams

• 오류있는 E-R Diagram 예시

• b : 여러 개의 과제가 존재한다면 문제가 생김

2) Entities vs Attributes

• Entity sets vs attributes

• phone을 엔티티로 사용하면 phone number(및 여러 phone number)에 대한 추가 정보가 허용됨

3) Entities vs Relationship sets

• entity sets vs relationship sets
  • Possible guideline(가능한 지침) : 엔티티 간에 발생하는 행동을 설명하기 위해 relationship set을 지정하는 것

4) Binary vs Non-Binary Relationships

• 다수의 distinct binary relationship set에 의해 설정된 non-binary(n-ary, for n > 2) 관계를 대체할 수 있지만,  n-ary 관계 집합은 여러 엔티티가 단일 관계에 참여한다는 것을 더 명확하게 보여줌
• binary가 아닌 것으로 보이는 일부 관계는 binary 관계를 사용하여 더 잘 나타낼 수 있음
  • 예시) child를 father과 mother와 연관시키는 ternary relationship parent는 father와 mother이라는 두 개의 binary relationship으로 대체하는 것이 가장 좋다.
    • 두 개의 binary 관계를 사용하면 partial 정보(예 : 어머니만 알려져 있는 것)을 허용
  • 하지만 naturally non-binary인 관계들이 있다.
    • 예시) proj_guide

5) E-R Design Decisions

• object를 나타내기 위해 속성 또는 엔티티 집합을 사용
• 실제 개념이 엔티티 집합 또는 관계 집합에 의해 가장 잘 표현되는지 여부
• ternary relationship vs binary relation 쌍의 사용
• strong 또는 weak 엔티티 집합의 사용
• specialization/generalization의 사용 : design의 modularity(모듈화)에 기여

 

10. Alternative Notations for Modeling Data

1) Alternative ER Notations

• Chen, IDE1FX, ...

2) UML

• UML : Unified Modeling Language
  • UML은 전체 소프트웨어 시스템의 다양한 측면을 그래픽으로 모델링하는 많은 구성요소를 가짐
  • UML Class Diagram은 E-R Diagram에 해당하지만 몇 가지 차이점이 있음

(1) ER vs UML Class Diagrams

* cardinality constraint depiction(카디널리티 제약 조건 설명)에서 position 반전 참

* generalization은 disjoint/overlapping과 무관하게 병합되거나 별도의 화살표를 사용할 수 있음

(2) UML Class Diagram

• 이진 관계 집합은 엔티티 집합을 연결하는 선을 그리기만 하면 UML로 표시됨.
  → 관계 집합 이름은 줄 옆에 쓰여있음
• 관계 집합에서 엔티티 집합에 의해 수행되는 role은 엔티티 집합에 인접한 라인에 role 이름을 기록하여 지정 가능
• 관계 집합 이름은 관계 집합의 속성과 함께 상자에 쓸 수 있으며 상자는 점선을 사용하여 관계 집합을 나타내는 선에 연결됨

 

11. Other Aspects of Database Design

• Functional Requirements(기능 요구사항)
• Data Flow, Workflow
• Schema Evolution