E-R model

Designing a database

→ 전체적인 디자인 과정을 이해해보자.

(교과서 읽으세염...)

• Initial phase
  • characterize fully the data needs of the prospective database users
• Second phase
  • choose a data model
    • Apply the concepts of the chosen data model
    • Translate the requirements into a conceptual schema of the database
    • A fully developed conceptual schema indicated the functional requirements of the enterprise
      • Describe the kinds of operations (or transactions) that will be performed on the data
    c.f NoSQL
    • Not Only SQL
• Final Phase
  • Move from an abstract data model to the implementation of the database
    • Logical Design
      • Deciding on the database schema
        • Database design requires that we find a "good" collection of relation schemas
        • Business decision
          • What attributes should we record in the database?
        • Computer Science decision
          • What relation schemas should we have and how should the attributes be distributes among the various relation schemas?
    • Physical Design
      • Deciding on the physical layout of the database

Design Phases

• In designing a database schema, we must ensure that we avoid two major pitfalls(위험)
  • a bad design may result in repeated information
  Incompleteness
  • a bad design may make certain aspects of the enterprise difficult or impossible to model
  • 실행하는 데 불가능한 모델도 존재함
• Redundancy
• Avoiding bad designs is not enough → good design!!!!

Design Approaches

Entity Relationship Model

• Models an enterprise as a collection of entities and relationships
  • Entity; a "thing" or "object" in the enterprise that is distinguishable from other objects
    • Described by a set of attributes
• Represented diagrammatically by an entity-relationship diagram (E-R diagram)

Normalization Theory

• Formalize what designs are bad, and test for them

E-R diagrams

• Mapping cardinalites
• Primary keys in E-R models
• Weak Entity sets
• Reduction to relation schemass

E-R Models for Database Modeling

• The E-R data model was developed to facilitate database design by allowing specification of a database schema
  • Database Schema represents the overall logical structure of a database
• The E-R data model employs three basic concepts
  • Entity sets
  • Relationship sets
  • Attributes
• The E-R model has an associated diagrammatic representation
  • E-R diagram can express the overall logical structure of a database graphically

Entity Sets

• Entity
  • an objects
  • exits and is distinguishable from other objects
    • e.g., specific person. company, event, plant
• Entity set
  • a set of entities of the same type
  • share the same properties
    • e.g, set of all persons, companies, trees, holidays
• A subset of the attributes form a PK of the ES
  • i.e, uniquely identifying each member of the set

Representing Entity Sets in E-R Diagrams

• Entity sets can be represented graphically as follows
  • Rectangles represent entity sets
  • Attributes listed inside entity rectangle
  • Underline indicated PK attributes

Relationship Sets

• Relationship
  • an association among several entities

• Relationship Set
  • a mathematical relation among n≥2 entities
  • each taken from entity setsrelationship set

Example: Entity and Relationship Sets

Diamonds represent relationship sets

Attribute can also be associated with relationship set

An attribute can also be associated with a relationship set

Cardinality VS. relationship

• 완전 다름

• entity set 3개가 relationship에 참여함.

Roles

• Entity sets of a relationship need not be distinct
  • Each occurrence of an entity set plays a role in the relationship
  • E.g, The label "course_id" and "prereq_id" are called roles

Degree of a Relationship Set

→ # entity sets involved in a relationship set

• Binary relationship
  • Involves two entity sets (or degree two)
  • Most relationship sets in a database systems are binary
• Relationships between more than two entity sets are rare but possible
  • E.g, students work on research projects under the guidance of an instructor
  • Relationship proj_guide is a ternary(삼항연산자) relationship btw instructor, student, and project

Non-binary Relationship Sets

• Most relationship sets are binary
• There are occasions when it is more convenient to represent relationships as non-binary
• E-R diagram with a ternary relationship

Complex Attributes

→ hierarchy, functionality of entity

• Attribute types
  • Simple and composite attributes
  • Single-valued and multivalued attributes
    • E.g., multivalued attributes: phone_numbers → 한 사람이 여러 개의 번호가 있을 수 있으니까
  • Derived attributes
    • attributes that can be computed from other attributes
      • E.g., age, given date_of_birth
• Domain: the set of permitted values for each attribute

Composite Attributes

→ a way to represent the hierarchy among attributes

• composite attributes allow us to divided attributes into subparts(other attributes)

Representing Complex Attributes in E-R Diagrams

→ entity set을 이용해서 표현해보쟝

• SQL DDL
• { phone_number }
  • set을 포함시킬 수 있다 → multivalued attribute
• age()
  • 계산하는 함수도 포함할 수 있다.
  • takes the date of birth and returns the current age

c.f) in R-DB tables

• Not able to represent hierarchy among attributes
• No lists or sets
• No functions

Mapping Cardinalities

• Express the # of entities to which another entity can be associated via a relationship set
  • Most useful in describing binary relationship 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
  → Some elements in A and B may not be mapped to any elements in the other sets

Representing Cardinalities in E-R diagrams

• Express cardinality constraints by drawing either
  • a directed line (→)
    • signifying "one"
  • an undirected line (—)
    • signifying "many"
    • btw the relationship set and the entity set

One-to-one

 

• 1:1
• 한 명씩만 연결
• instructor가 한 명의 학생
• 학생은 한 명의 advisor

One-to-many

• 교수는 여러 명(0~n)의 학생
• 학생은 무조건 한 명

Many-to-one

• 교수는 한 명의 학생
• 학생은 여러명의 advisor

Many-to-many

• Cardinality is an important factor that determines the final DB schema

Total and Partial Participation

→ final DB를 위해서 중요함.

• Total participation
  • double line
  • every entity in an entity set participate in at least one relationship in the relationship set
  • 전부 다 참여
• Partial participation
  • some entities may not participate in any relationship in the relationship set
  • 일반적인 경우 (초기조건)

Notation for Expressing More Complex Constraints

• line; associated min and max cardinality
• min value of 1; total participation
• max value of 1; at most one relationship
• max value of *; no limit

• 헷갈릴 수도 있으니까 잘보자.
• 1:many이다.
  • 0..*; min: 0, max: *(unlimited)
  • 1..1; min: 1, max: 1
    • 모든 student는 한 명의 advisor를 갖는다.
    • min 1; total participation

Primary keys in E-R models

• PK; identifier처럼 동작.
• entity와 relation구분.
  • Entity sets
  • Relationship sets
  • Weak entity sets

Primary Key for Entity Sets

• 각각의 entity는 구별.
  • in terms of their attributes
    • uniquely identify the entity
    • No two entities in an entity sets are allowed to have exactly the same value for all attributes
• key for an entity is a set of attributes
  • suffice to distinguish entities from each other

Primary Key for Relationship Sets

• individual PKs of the entities를 사용한다.
• union of the pk도 허용한다.
• If the relationship set R has attributes a1, a2, ..., am associated with it, then the pk of R also includes the attributes a1, a2, ..., am

Choice of PK for Binary Relationship

• depends on the mapping cardinality of the relationship set
  • Many-to-Many
    • union of the PKs; min super key
  • One-to-Many
  • Many-to-One
    • PK of Many side
  • One-to-one
    • Pk of either one of the participating entity sets; min super key
      • either one can be chosen as the pk

Weak Entity Sets

→ final DB를 위해서 중요함.

자기 정보를 알려주기 위해 추가적인 정보가 필요한 경우

• dependent on another entity
  • identifying entity
  • not enough information to identify
• use the identifying entity
  • discriminator to uniquely identify a weak entity
• does not have PK
• Own
  • identifying entity set: an entity set that has a pk
  • identifying entity set = strong entity set
• Identifying relationship
  • the relationship associating the weak entity set with the identifying entity set
  • DDL

distinguishing among an entity set

• Discriminator of a weak entity
  • a set of attributes allowing such distinction
• PK of a weak entity set
  • = pk of a strong entity set + discriminator

• 자연스럽게 total participation이 된다.
• pk가 없고 discriminator가 있다.
• 쌍마름모로 표현

E-R Diagram for a University Database

 

Ex1. a Record Shop

Ex2. Flight Database

 

 

Reduction to relation schemas

연속된 테이블을 그리자

• Entity sets and relationship sets can be expressed uniformly as relation schemas(DB schemas)
  • assigned the name of the corresponding entity set or relationship set
  • each schema has a number of columns
    • unique names

Representing Entity Sets

• Table로 만드는 방법
  • entity set → schema, attribute → column
• strong entity set
  • schema with the same attributes
• weak entity set
  • a columns for the PK of the identifying strong entity set

Representing of Entity Sets with Composite Attributes

• flattened out
  • first_name → name_first_name
  • last_name → name_last_name
• multivalued attributes
  • { phone_number }
    • 테이블 하나 더 만들어서 집합 없애라
    • multiple records in the separate table
    • multivalue에 들어가야 할 내용들을 여러 개의 records로 만들어줌.
  • age()
    • SELECT sysdate - date_of_birth
    • age를 계산, 함수에 해당하는 column X, 그냥 구현만 함.

Representing Relationship Sets

• many-to-many relationship set
  • the PKs of the two participating entity sets
  • 새로운 테이블을 만들어 준다.
• many-to-one
  • one-to-many
  • total인 경우; 따로 만들어주지 않고 column 4개의 table을 만든다.
  • total이 아닌 경우; null이 될 수도 있다.
• one-to-one
  • 둘 중 하나를 골라서 상대방의 PK를 가지고 있으면 됨.
  • relationship을 위한 별도의 테이블은 필요 없다.