[Database] Entity Relationship(ER) Model

Database Design : 3 Steps

• Conceptual Design : ER Model
  • Describe data requirements of users
  • Both DBMS and H/W independent
  • High Level Conceptual Schema (= ER Diagram)
• Logical Design
  • Actual implementation using specific DBMS
  • DBMS dependent, but H/W independent
  • Logical (Low Level Conceptual) Schema
• Physical Design
  • Specify access paths, indexes, and file organizations
  • Both DBMS and H/W dependent
  • Physical(Internal) Schema

Example: COMPANY Database

• Database Analysis Requirements : Company
  • Our company is organized into departments. Each department has a name, number and an only one employee who manages the department. (. . . . .)
  • Each department controls many projects. Each project is controlled by only one department. Each project has a name, number, location. (. . . . .)
  • We store each employee’s ssn, address, salary, sex, and birthdate. Each employee works for one department but may work on several projects. We keep track of the direct supervisor of each employee. (. . . . .)
  • Each employee has a number of dependents. For each dependent, we keep track of their name, sex, birth-date, and supported relationship to employee. (. . . . .)
  • . . . . . (etc.)

Entity and Attribute

• Entity
  • Entity is a thing (or object) existing in a world.
• Attribute
  • Attributes are properties used to describe an entity.

Types of Attributes

• Simple Attribute
  • An entity has a single value for the attribute.
• Multi-Valued Attribute
  • An entity has several (> 1) values for the attribute.
• Composite Attribute
  • An attribute is composed of other attributes.
• Complex Attribute
  • Composite + Multi-valued
• Derived Attribute
  • Value of an attribute is computed from another attribute.

Entity Type and Key

• Entity Type
  • A collection of similar entities (= sharing the same attributes)
• Key
  • A set of attributes to identify each entity uniquely.
  • Every values in a key attribute must be distinct.
  • An entity type may have more than one key attributes.
• Examples of Key
  • EMPLOYEE의 key는?
  • STUDENT의 key는?
  • VEHICLE의 key는?

Example: EMPLOYEE Entity Type

• EMPLOYEE
  • (SSN, Name, BirthDate, Age, Phone)

(1234567, Bob, 6-1-1977, 36, 290-7218),
(2345678, Abe, 6-1-1966, 47, {290-7118, 390-7118}),
(3456789, Eve, 5-1-1957, 57, 290-7230),
.
.
.

ER Diagram : EMPLOYEE Entity Type

Relationship (Type)

• Relationship
  • It connects (or relates) many entities.
• Relationship Type
  • A collection of similar relationships .
• Degree (of Relationship Type)
  • Unary (= Recursive) Relationship : Degree 1
  • Binary Relationship : Degree 2
  • Ternary Relationship : Degree 3

WORK_FOR Relationship Type

WORK_ON Relationship Type

ER Diagram : WORK_FOR and WORK_ON

Constraints on Relationship(1)

• Mapping Constraints
  • : Express the number of entities to which another entity can be related via a relationship type.

• One to One (1 : 1)
• Many to One (M : 1)
• One to Many (1 : M)
• Many to Many (M : N)

WORK_FOR Relationship Type (M : 1)

• “Each employee works for at one department, but each department can have many employees for it.”

MANAGE Relationship Type (1 : 1)

• “Each employee works for at one department, and each department has one employee for it.”

WORK_ON Relationship Type (M : N)

• “Each employee can work on many projects, and a project can have many working employees for it.

ER Diagram : Mapping Constraints

Constraints on Relationship(2)

• Participation Constraints
  • Total (= Mandatory)
  • Every entity must participate (in a relationship).
  • It is not allowed that there are any entities not participating in relationship.
• Partial (= Optional)
  • Some entities may not participate.

WORK_FOR Relationship Type (Total : Total)

• Every employee must work for some department.
• Every department must have employee working for it.

MANAGE Relationship Type (Partial : Total)

• Not every employee needs to manage a department.
• But every department must have some manager for it.

ER Diagram : Total/Partial Constraints

Exercises : Relationship

• Show examples of each of the following relationships;

Constraints on Relationship(3)

• (Min, Max) Constraints
  • Assign (min, max) to each entity type.
  • min means “at least”; max means “at most”
  • min = 0 means partial
  • min > 0 means total
  • min  max; min  0; n  max  1

ER Diagram : (Min, Max) Constraints

• Each department must have exactly one manager and each employee can manage at most one department
• Each employee must work for exactly one department, but each department must have maximum 30 employees

Recursive Relationship

• Relationship with degree 1 is called recursive(unary).
• Both participating entity types are the same.
• Examples of Recursive Relationships
  • 사람과 사람들 간의 관계
  • 과목과 과목들 간의 관계
  • 부품과 부품들 간의 관계
• 참여하는 양쪽의 동일한 entity type들을 구분하기 위해 서로의 고유한 다른 role (역할)들이 필요함.
• ER diagram 그릴 때 각 role 들을 모두 표기함

Recursive Relationship : SUPERVISE

Recursive Relationship 1 : M

Recursive Relationship 1 : 1

Recursive Relationship M : N

Weak Entity Types

• In real world, some entity type may not have its key.
• An entity type that does not have a key, is called a weak entity type.
• To identify weak entities uniquely, we must find its owner (= strong) entity type.
• Owner entity type has a weak relationship with weak entity type;
• Owner has always its own key

Weak Entity Types : Example

• ‘pname’ is almost a key for players, but there may be two with the same name.
• ‘pnumber’ is certainly a key within a same team. But players on two different teams could have the same number.
• How to identify players uniquely?
  • Player들과 relationship을 갖는 Team들을 찾아 냄.
  • 이들 team들은 key가 존재하고 이를 owner라 함.

• “Players” (by double box) is a weak entity type.
• “Teams” is an owner (= identifying) entity type.
  • : It has its own key “(team) name”
• “Plays-On” (by double diamond) is a weak relationship.

Weak Entity Type : Properties

• Weak entity type은 Owner와 M : 1 (1 : 1) 관계
• Weak entity type은 항상 total participation
• Weak entity type의 Key는?
  • Key of Owner + Partial Key of Weak entity type
  • (Partial key는 owner key의 도움을 받아 weak entity들을 식별할 수 있는 일종의 부분 key를 의미)
• Existence Dependency
  • (Weak entity의 존재는 owner에 종속됨. 만약 어떤 owner entity가 DB에서 삭제되면, 이와 relationship 을 갖는 weak entity들 모두 역시 삭제되어야 함)

• ⚫ “Teams” 의 key는?
• “Players” 의 partial key는?
• “Players” 의 key는?
• 만약 어떤 team이 해체 (즉 DB에서 삭제)된다면?

Weak Entity Types : Exercise

• 어느 회사 사원들의 가족(DEPENDENT)들임.
• 이들의 key를 찾을 수 없음.
• 위의 ER Diagram을 완성시켜라. (Owner Entity type? 즉, 이 가족들을 부양하는 사원(EMPLOYEE))

Attributes on Relationship : Example

• We want add “grade” attribute; Where to attach?
• Thus, a relationship can also have its own attributes;
• Sometimes, it is useful to attach an attribute to relationship;

Subclass and Superclass

• 일반적으로 한 entity type은 여러 개의 의미있는 이에 속한 sub-entity type 들로 group화 할 수 있음.
• 예 : EMPLOYEE를 다음과 같이 group화 함.
  • {SECRETARY, ENGINEER, TECHNICIAN}
  • Each of these groupings is a subset of EMPLOYEE entities
  • Each of these grouping is called a subclass of EMPLOYEE
  • EMPLOYEE is the superclass for each of these subclasses

• Subclass와 superclass 관계를 IS-A (혹은 Inclusion) relationship 라고 함. IS-A는 로, Inclusion은 로 표기함
• Subclass에 속한 한 entity는 superclass에 속한 어떤 entity와 실제로는 같은 entity임.
• Subclass에 속한 모든 entity들은 superclass에 속해야 함. (즉, subclass에 속했지만, superclass에는 속하지 않은 entity는 존재할 수 없음)
• Superclass에 속한 모든 entity들이 반드시 어떤 subclass에 속할 필요는 없음.

• Relationship between subclass and superclass : IS_A
  • SECRETARY “eve” IS-A EMPLOYEE “eve”;
  • ENGINEER “joe” IS-A EMPLOYEE “joe”;
  • TECHNICIAN “bob” IS-A EMPLOYEE “bob”;

• 각 subclass는 superclass 보다 더 적은 entity들을 가짐.
• 각 Subclass는 superclass 보다 더 많은 attribute들을 가짐
  • (즉 superclass의 attribute들 외에 자신만의 고유한 attribute들을 추가로 가질 수 있음.)
• 예 : 각 class는 다음의 attribute들을 가짐.
  • EMPLOYEE : {SSN, Name, Age, Phone}
  • SECREATRY : {SSN, Name, Age, Phone} ∪ {Type-Speed}
  • ENGINEER : {SSN, Name, Age, Phone} ∪ {Eng-Type}
  • TECHINICIAN : {SSN, Name, Age, Phone} ∪ {T-Grade}
• 위의 요구사항을 어떻게 ER modeling 할까?

Inheritance

• An entity that is member of a subclass inherits all attributes of the entity as a member of the superclass
• It also inherits all relationships
• It also inherits all functions (= programs)
• It also has its own relationship with other classes
  • Example: BELONG-TO_UNION of SALARY_EMP
• By inheritance, we can reuse existing ER schema for building new ER schema;
  • (Thus, we can avoid unnecessary database redesign.)

• Each subclass SECRETARY, ENGINEER, TECHINICAIN inherits all attributes from its superclass EMPLOYEE

Multiple Inheritance

• Single Inheritance
  • Every subclass has only one superclass.
  • It forms a class hierarchy (= like tree).
• Multiple Inheritance
  • A subclass can have more than one superclasses.
  • A subclass can inherit attributes of each of its superclasses.
• 즉, 각 superclass는 자기 자신 고유의 이질적인 특성들을 가질 수 있음.

• A subclass STUD-ASSISTANT can inherit all attributes from both EMPLOYEE and STUDENT superclasses.

Specialization

• Process of defining a set of subclasses from superclass
• Top-Down (= Refinement) Modeling
• Based on IS_A relationship
• A subclass inherits attributes of its all direct or indirect superclasses
• Example: {SECRETARY, ENGINEER, TECHNICIAN} is a specialization of EMPLOYEE based upon job type.
• Example: {SALARY_EMP, HOURLY_EMP} is a specialization of EMPLOYEE based in payment type

Specialization : Company

Specialization : University

Example

• ER Diagram : COMPANY Databases

• ER Diagram : BANK Databases