데이터 베이스(Database) - Entity-Relationship Model

데이터 베이스(Database) - Entity-Relationship Model

1. Overview of Design Process

- Specification of user requirements

- Conceptual design

- Specification of functional reqirements

- Logical design

- Physical design

 

2. Entity Sets

- A database can be modeled as : a collection of entities
                                                   relationship among entities

- An entity is an object that exists and is distinguishable from other objects.
   ex) specific person, company, event, plant

- Entities have attributes. ex) people have names and addresses

- An entity set is a set of entities of the same type that share the same properties. ex) set of all persons, companies

※ entity와 object의 차이 : entity는 identity(attribute)만 가지고, object는 identiy와 behavior까지
    갖는다.

 

Crick, Katz, Srinivansan, … -> entity
instructor, student  -> entity set

 

3. Relationship Sets

- A relationship is an association among several entities.
  ex) 44553(Peltier)         advisor        22222(Einstein)
          student entity   relationship set   instructor entity

- A relationship set is a mathematical relation among n>= 2 entities, each taken from entity sets

{(e1,e2,…,en)|e1∈E1, e2∈E2, …, en∈En}
where (e1,e2,…,en) is a relationship
ex) (44553,22222) ∈ advisor

* entity set간에 연결된 선이 relationship

- An attribute can also be property of a relationship set.

위 다이어그램의 타원 -> descriptive attribute : 관계를 설명하는 속성

 

4. Degree of a Relationship Set

- Binary relationship(이진 관계)

* involve two entity sets (or degree two)

* most relationship sets in a database system are binary

- Relationships between more than two entity sets are rare. Most relationships are binary.
   3진 이상의 관계를 SQL DDL로 표현하기 힘들기 때문.

 

5. Attributes

- An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set. ex) instructor = (ID, name, street, city, salary)

- Domain

the set of permitted values for each attribute

- Attribute types

* Simple and composite attributes

* Single-valued and multivalued attributes
    example of  multivalued attribute : phon_numbers (집전화, 직장전화, 휴대폰, …)

* Derived attributes
    Can be computed from other attributes
    Example : age, given date_of_birth

 

6. Composite Attributes

first_name, middle_initial, …, street_name, apartment_number -> simple attributes

 

7. Constraints - Mapping Cardinalities

- Express the number of entities to which another entity can be associated via a relationship set.

- Most useful in describing binary relaitonship sets.

- For a binary relationship set the mapping cardinality must be one of the following types :

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

8. Keys

- A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity.

- A candidate key of an entity set is a minimal super key.

* ID is candidate key of instructor

* course_id is candidate key of course

- Although several candidate keys may exist, one of the candidate keys is selected to be the primary key.

 

9. Keys for Relationship Sets

- The combination of primary keys of the participating entity sets forms a super key of a relationship set.

ex) (s_id, i_id) is the super key of advisor

- Must consider the mapping cardinality of the relationship set when deciding what are the candidate keys

ex) if the relationship of student-to-instructor is
      * many-to-one, the primary key : (s_id)
      * one-to-many, the primary key : (i_ id)
      * one-to-one, the primary key : (s_id) or (i_ id)
      * many-to-many, the primary key : (s_id, i_ id)

 

10. Entity-Relationship Diagrams

- Rectangle : represent entity sets

- Diamonds : represent relationship sets

- Attributes : listed inside entity rectangle

- Underline : indicates primary key attributes

- Lines : link entity sets to relationship sets

- Dashed lines : link attributes of a relationship set to the relationship set

- Double lines : indicate total participation of an entity in a relationship set

- Double diamonds : represent identifying relationship sets linked to weak entity sets

* weak entity set : primary key를 가지고 있지 않는 entity (partial key,완전하지 않은 key만 갖음)

 

11. Complex Attributes

 

  name, address -> coposite attributes
  phone_number -> multivalued attributes
  age() -> derived attribute

 

12. Roles

- Entity sets of a relationship need not be distinct

Each occurrence of an entity set plays a 'role' in the relationship.

- The labels "course_id" and "prereq_id" are called roles.

  선 위의 label -> role을 표시

 

13. Cardinality Constraints

- one : directed line(->)

- many : undirected line(-)

 

14. One-to-One Relationship

- one-to-one relationship between an instructor and a student

* an instructor is associated with at most one sturdent via advisor

* and a student is associated with at most one instructor via advisor

 

15. One-to-Many Relationship

- one-to-many relationship between an instructor and a student

* an instructor is associated with several (including 0) students via advisor

* a student is associated with at most one instructor via advisor

 

16. Many-to-One Relationships

- In a many-to-one relationship between an instructor and a student

* and instructor is associated with at most one student via advisor

* and a student is associated with several (includeing 0) instructors via advisor

 

17. Many-to-Many Relationship

- In a many-to-many relationship between an instructor and a student

* and instructor is associated with several (possibly 0) student via advisor

* and a student is associated with several (possibly 0) instructors via advisor

18. Alternative Notation for Cardinality Limits

- Cardinality limits can also express participation constraints

  * 최소 대응수..최대 대응수
     0..* ->각 교수님은 지도학생이 한명도 없을 수도 있고, 여러명 있을 수도 있다.
     1..1 -> 학생은 반드시 한명의 지도교수님이 있어야 한다.

19. Participation of an Entity Set in a Relationship Set

- Total participation

double line으로 표시, entity set의 모든 entity가 relationship set의 하나 이상의 relationship에 
나타나는 것.

ex) every section must have an associated course (분반 정보가 있으면 과목이 반드시 존재)

- Partial participation

relationship set의 relationship중에 나타나지 않는 entity가 존재하는 것.

ex) participation of instructor in advisor is partial (지도 학생이 없는 교수가 있는 경우)

 

20. Weak Entity Sets

- primary key를 갖지 않는 entity set을 weak entity set이라 한다.

- discriminator (or partial key)는 weak entity set의 모든 entity를 구분할 수 있는 attribute들의 집합

- weak entity set의 primary key는 (weak entity set의 partial keys) + (strong entity set의
   primary keys) 이다.

- partial key는 dashed line으로 표시한다.

- Identifying relationship은 duble diamond로 표시한다.

    section의 primary key = (course_id, sec_id, semester, year)
    * course_id는 strong entity set의 primary key
    * sec_id, semester, year은 weak entity set의 partial keys
    * sec_course를 구성하는 attribute는 primary key 전부 이다.

21. Reduction to Relation Schemas

- E.R 다이어그램은 schema들의 모음으로 표현할 수 있다.

- E.R 다이어그램으로부터 relation schema를 만드는 과정

1) Representing Strong Entity Sets With Simple Attributes

attribute를 그대로 사용한다.

2) Representing Strong Entity Sets With Complex Attributes

composite attributes -> flatten시킨다.

multivaluted attributes -> primary key와 합쳐서 새로운 table을 생성한다.

special case -> attribute가 하나이고, primry key일 때 flatten 시킨다.

3) Representing Weak Entity Sets

identifying strong entity set의 primary key를 갖는 table을 생성한다.

4) Representing Relationship Sets

many-to-many relationship set -> descriptive attributes와 참여하는 두 entity sets의 primary key로 나타낸다.

Many-to-one and one-to-many relationship set -> many측이 total participation일 때 one측의 primary key를 many측에 추가한다.

one-to-one -> 한쪽의 attribte를 다른쪽에 모두 넣는다.