[ DB ] 06. E-R Model

🖥️ Designing a database

• Initial phase: characterize fully the data needs of the prospective database users(관점에서)
• Second phase: choose a data model ( R-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 indicates the functional requirements(SQL queries, application) of the enterprise
• Final Phase: Move from an abstract data model to the implementation of the database
  • Logical Design – Deciding on the database schema
    - 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 distributed among the various relation schemas?
  • Physical Design – Deciding on the physical layout of the database ( SQL DDL )
    
    
• In designing a database schema, we must ensure that we avoid two major pitfalls:
  • Redundancy: a bad design may result in repeated information
    - Redundant representation of information may lead to data inconsistency among the various copies of information
  • Incompleteness: a bad design may make certain aspects of the enterprise difficult or impossible to model
    
    
• Entity Relationship Model
  • Models an enterprise as a collection of entities and relationships
  • Represented diagrammatically by an entity-relationship diagram (E-R diagram)
    ➡️ help us to visually design DB
• Normalization Theory

---

🖥️ E-R diagrams

• The E-R data model employs three basic concepts:
  • Entity sets
  • Relationship sets
  • Attributes
• E-R diagram can express the overall logical structure(전체 논리구조) of a database graphically

Entity Sets

• Entity : an object that exists and is distinguishable from other objects
  ➡️ represented by a set of attributes
• Entity set : a set of entities of the same type that share the same properties
• A subset of the attributes form a primary key of the entity set;
  ➡️ uniquely identifying each member of the set

Relationship Sets

• Relationship : an association among several entities
• Relationship set : a mathematical relation among n 3 2 entities, each taken from entity sets
  ➡️ Attribute can also be associated with a relationship set

Roles : Entity가 수행하는 기능

• Binary relationship : Involves two entity sets (or degree two)
• Ternary relationship : more than two entity sets are rare but possible

Complex Attributes

• Attribute types:
  • Simple and composite attributes
  • Single-valued and multivalued attributes
  • Derived attributes

Mapping cardinalities

• 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
• Express cardinality constraints by drawing either a directed line (→), signifying “one”, or an undirected line (—), signifying “many”, between the relationship set and the entity set

Total and Partial Participation

• Total participation (indicated by double line): every entity in an entity set participates 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

• A line may have an associated minimum and maximum cardinality, shown in the form l..h, where l is the minimum and h the maximum cardinality
  • A minimum value of 1 indicates total participation
  • A maximum value of 1 indicates that the entity participates in at most one
    relationship
  • A maximum value of * indicates no limit➡️ one : many

Primary keys in E-R models

• Primary keys provide a way to specify how entities and relationships are distinguished

Primary Key for Entity Sets

• By definition, individual entities are distinct
• From database perspective(과정), the differences among entities must be expressed in terms of their attributes
  • The attribute values of an entity must be such that they can uniquely identify the entity
  • No two entities in an entity set are allowed to have exactly the same value for all attributes
• A key for an entity is a set of attributes that suffice(과정) to distinguish entities from each other

Primary Key for Relationship Sets

• To distinguish among the various relationships of a relationship set, use the individual primary keys of the entities in the relationship set
  • The primary key for R is consists of the union of the primary keys of entity sets $E_1$, $E_2$, ..., $E_n$
• Example: relationship set “advisor”
  • The primary key consists of inrsructor.ID and student.ID(combination) ➡️ General rule

Choice of Primary Key for Binary Relationship

• The choice of the primary key for a relationship set depends on the mapping cardinality of the relationship set
  • Many-to-Many relationships: The preceding union(Combination) of the primary keys is a minimal super key and is chosen as the primary key
  • One-to-Many relationships: The primary key of the “Many” side is a minimal super key and is used as the primary key
    - Many-to-one relationships: The primary key of the “Many” side is a minimal super key and is used as the primary key
  • One-to-one relationships: The primary key of either one of the participating entity sets forms a minimal super key, and either one can be chosen as the primary key

Weak Entity Sets

• A weak entity set is one whose existence is dependent on another entity, called its identifying entity
• Instead of associating a primary key with a weak entity, use the identifying entity, along with extra attributes called discriminator to uniquely identify a weak entity➡️ Primary key for section – (course_id, sec_id, semester, year)
• A weak entity set does not have a primary key
• We still need a means of distinguishing among an entity set
  • Discriminator of a weak entity: a set of attributes allowing such distinction
  • Primary key of a weak entity set
    = primary key of a strong entity set (which its existence depends)
    + its discriminator
    
• The identifying entity set is said to own the weak entity set that it identifies
  • Identifying entity set: an entity set that has a primary key
  • Identifying entity set = strong entity set
• Identifying relationship
  • Identifying relationship: The relationship associating the weak entity set with the identifying entity set

Reduction to relation schemas

• Entity sets and relationship sets can be expressed uniformly as relation schemas
  • ⭐️ For each entity set and relationship set, there is a unique schema

Representing Extity Sets

• Strong entity set reduces to a schema with the same attributes
  • E.g., student(ID, name, tot_cred)
• Weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set
  • E.g., section ( course_id, sec_id, sem, year )
    
    
• Composite attributes are flattened out by creating a separate attribute for each component attribute
  • E.g., first_name → name_first_name, last_name → name_last_name
  • Prefixes can be omitted if there is no ambiguity(모호함)
    
    
• A multivalued attribute M of an entity E is represented by a separate schema EM
  • Schema EM has attributes corresponding to the primary key of E and an attribute corresponding to multivalued attribute M
  • E.g., Multivalued attribute phone_number of instructor:
    ➡️ inst_phone(ID, phone_number)

Representing Entity Sets

• $\infin$ : $\infin$ ➡️ separate mapping table
• $\infin$ : 1, 1 : $\infin$ ➡️ no separate mapping, copy PK(1) to many side
  ➡️ But many side가 total participation이 아닐 경우 could result in NULL
• 1 : 1 ➡️ no separate mapping, copy one PK to another ( 둘 중 하나의 PK를 가지면 된다 )

HGU 전산전자공학부 홍참길 교수님의 23-1 Database System 수업을 듣고 작성한 포스트이며, 첨부한 모든 사진은 교수님 수업 PPT의 사진 원본에 필기를 한 수정본입니다.