Entityrelationship Diagrams

1
Entity-relationship Diagrams

• Evangelos Milios

2
DB design process

• Requirements collection and analysis
• Database requirements
• Create conceptual schema for DB
• Functional requirements
• Specify high-level transactions
• Transform conceptual schema into a DBMS-specific
  data model
• Physical design and transaction implementation

3
Database design phases
High-level Transaction specification
Transaction Implementation
Functional Analysis
Requirements Collection Analysis
Physical Design
Conceptual Design
Data Model Mapping
Schema in the model of the DBMS
Conceptual Schema
4
Entity-relationship model

• Data described as
• Entities things in the real world
• Relationships
• Attributes
• properties of entities
• have values

5
Types of attributes

• Simple vs. composite
• Address is composite attribute
• Postal code is simple attribute
• Single- vs. multi-valued
• Age is single-valued
• College degrees is multi-valued
• Stored vs. derived
• If Date of birth is a stored attribute
• Then Age is derived
• Null value when we do not know the value

6
Entity types

• Entity type set of entities that have the same
  attributes
• The set of employees
• Entity type schema or intension for a set of
  entities sharing the same structure
• Individual entities grouped into collection or
  entity set, caled the extension of the entity type

7
Key attributes

• Key attribute is unique distinct values for each
  individual entity
• Employee SIN is unique
• Employee name is not unique
• Entities can have more than one key attribute
• For a car VIN and license plate number

8
Value sets (domains) of attributes

• Set of values that may be assigned to an
  attribute of an individual entity
• Age integer from 18 to 65
• Name a string of letters, incl. blanks

9
An Example

• Company database employees, departments and
  projects
• A department controls a number of projects
• Employee records
• Keep track of hours per project
• Dependents for insurance purposes

10
ER notation

• Entity type (rectangle)
• Weak entity type owned by another entity (double
  rectangle)

11
Entities
DEPARTMENT
EMPLOYEE
PROJECT
DEPENDENT
12
ER notation

• Entity type (rectangle)
• Weak entity type owned by another entity (double
  rectangle)
• Relationship (diamond)
• Cardinality e.g. MN
• Total participation double line (all employees
  are associated with a department)
• Partial participation single line (only some
  employees manage departments)
• Identifying relationship of weak entity double
  diamond

13
Relationships
WORKS_FOR
?
1
DEPARTMENT
EMPLOYEE
MANAGES
1
1
supervisor
1
supervisee
CONTROLS
SUPERVISION
1
1
N
WORKS_ON
M
DEPENDENT_OF
?
?
PROJECT
?
DEPENDENT
14
ER notation

• Entity type (rectangle)
• Weak entity type owned by another entity (double
  rectangle)
• Relationship (diamond)
• Cardinality e.g. MN
• Total participation double line (all employees
  are associated with a department)
• Partial participation single line (only some
  employees manage departments)
• Identifying relationship of weak entity double
  diamond
• Attribute names oval
• Multivalued attributes double oval
• Key attributes underlined
• Partial key for weak entity type dotted
  underlined

15
Attributes
Name
Dob
Sin
Num
Addr
Loc
WORKS_FOR
Name
?
1
Slry
DEPARTMENT
EMPLOYEE
MANAGES
1
1
supervisor
1
supervisee
CONTROLS
SUPERVISION
1
1
N
WORKS_ON
M
DEPENDENT_OF
?
?
PROJECT
Rln
?
Dob
Name
Loc
DEPENDENT
Num
Name
16
ER notation

• Entity type (rectangle)
• Weak entity type owned by another entity (double
  rectangle)
• Relationship (diamond)
• Cardinality e.g. MN
• Total participation double line (all employees
  are associated with a department)
• Partial participation single line (only some
  employees manage departments)
• Identifying relationship of weak entity double
  diamond
• Attribute names oval
• Multivalued attributes double oval
• Key attributes underlined
• Partial key for weak entity type dotted
  underlined
• Derived attribute dotted oval, dotted line
• Attributes can also be associated with
  relationships (hrs)

17
Full Example
Name
Dob
Sin
Num
Addr
Loc
WORKS_FOR
Name
?
1
Slry
DEPARTMENT
EMPLOYEE
No of employees
StartDate
MANAGES
1
1
supervisor
1
supervisee
CONTROLS
SUPERVISION
1
1
N
WORKS_ON
M
DEPENDENT_OF
?
?
PROJECT
Rln
?
Dob
Hrs
Name
Loc
DEPENDENT
Num
Name
18
Naming of schema constructs

• Choose meaningful names
• Entity and relationship types uppercase
• Attribute names capitalized
• Role names lowercase (e.g. supervisor,
  supervisee)
• Given a narrative description of the database
  requirements
• Nouns entity types
• Verbs relationship types
• Attributes additional nouns describing entity
  types