Database - Discussions

Data Modeling Using the

Entity-Relationship (ER) Model

Dr. Buleje

Slide 1- 1

Slide 3- 2

Outline

 Overview of Database Design Process

 Example Database Application (COMPANY)

 ER Model Concepts

 Entities and Attributes

 Entity Types, Value Sets, and Key Attributes

 Relationships and Relationship Types

 ER Diagrams - Notation

 ER Diagram for COMPANY Schema

 Alternative Notations – UML class diagrams, others

Slide 3- 3

Overview of Database Design Process

 Two main activities:

 Database design

 Applications design

Slide 3- 4

Overview of Database Design Process

Methodologies for Conceptual Design

 Entity Relationship (ER) Diagrams (This

Presentation)

 Enhanced Entity Relationship (EER) Diagrams

 Use of Design Tools

 The UML (Unified Modeling Language) Class

Diagrams

Slide 3- 5

Slide 3- 6

Example COMPANY Database

 Database Schema Design based on the following (simplified) requirements of the COMPANY Database:

 The company is organized into DEPARTMENTs.

 Each department controls a number of PROJECTs.

Slide 3- 7

Example COMPANY Database

(Continued)

 The database will store each EMPLOYEE’s social security number, address, salary, sex, and birthdate.

 Each employee may have a number of DEPENDENTs.

Slide 3- 8

Entity Relationship (ER) Model

Concepts

 Entities and Attributes  Entity is a basic concept for the ER model.

 Attributes are properties used to describe an entity.

 A specific entity will have a value for each of its attributes.

 Each attribute has a value set (or data type) associated.

Slide 3- 9

Types of Attributes

 Simple

 For example, SSN or Sex.

 Composite

• For example:

• Address (Apt#, House#, Street, City, State, ZipCode, Country), or

• Name (FirstName, MiddleName, LastName).

• Composition may form a hierarchy where some components are themselves composite.

 Multi-valued

• For example, Color of a CAR or PreviousDegrees of a STUDENT.

• Denoted as {Color} or {PreviousDegrees}.

Slide 3- 10

Example of a composite attribute

Slide 3- 11

Entity Types and Key Attributes (1)

 Entities with the same basic attributes are

grouped or typed into an entity type.

 For example, the entity type EMPLOYEE

and PROJECT.

 Key Attribute of the entity type.

 For example, SSN of EMPLOYEE.

Slide 3- 12

Entity Types and Key Attributes (2)

 Any key attribute may be composite.

 VehicleTagNumber is a key of the CAR entity

type with components (Number, State).

 An entity type may have more than one key.

 The CAR entity type may have two keys:

 VehicleIdentificationNumber (popularly called VIN)

 VehicleTagNumber (Number, State), aka license

plate number.

Slide 3- 13

Entity Set

 Each entity type will have a collection of entities

stored in the database

 Called the entity set or sometimes entity collection

Value Sets (Domains) of Attributes

 Each simple attribute is associated with a value

set

 A value set specifies the set of values associated

with an attribute

Slide 3- 14

Attributes and Value Sets

 Value sets are similar to data types in most

programming languages

 Mathematically, an attribute A for an entity type E

whose value set is V is defined as a function

A : E -> P(V)

Slide 3- 15

Slide 3- 16

Displaying an Entity type

 In ER diagrams, an entity type is displayed in a rectangular box

 Attributes are displayed in ovals

 See the full ER notation in advance on the next slide

Slide 3- 17

NOTATION for ER diagrams

Slide 3- 18

Initial Conceptual Design of Entity Types

for the COMPANY Database Schema

 Based on the requirements, we can identify four

initial entity types in the COMPANY database:

 DEPARTMENT

 PROJECT

 EMPLOYEE

 DEPENDENT

Slide 3- 19

Initial Design of Entity Types: EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT

Slide 3- 20

Refining the initial design by introducing

relationships

 The initial design is typically not complete

 Some aspects in the requirements will be

represented as relationships

 ER model has three main concepts:

 Entities

 Attributes

 Relationships

Slide 3- 21

Relationships and Relationship Types (1)

 A relationship relates two or more distinct entities with a specific meaning.

 Relationships of the same type are grouped or typed into a relationship type.

 The degree of a relationship type is the number of participating entity types.  Both MANAGES and WORKS_ON are binary relationships.

Slide 3- 22

Relationship instances of the WORKS_FOR N:1

relationship between EMPLOYEE and DEPARTMENT

Slide 3- 23

Relationship instances of the M:N WORKS_ON

relationship between EMPLOYEE and PROJECT

Slide 3- 24

Relationship type vs. relationship set

 Relationship Type

 Relationship Set

Slide 3- 25

Refining the COMPANY database

schema by introducing relationships

 By examining the requirements, six relationship types are

identified

 All are binary relationships ( degree 2)

 Listed below with their participating entity types:

1. WORKS_FOR (between EMPLOYEE, DEPARTMENT)

2. MANAGES (also between EMPLOYEE, DEPARTMENT)

3. CONTROLS (between DEPARTMENT, PROJECT)

4. WORKS_ON (between EMPLOYEE, PROJECT)

5. SUPERVISION (between EMPLOYEE (as subordinate),

EMPLOYEE (as supervisor))

6. DEPENDENTS_OF (between EMPLOYEE, DEPENDENT)

Slide 3- 26

ER DIAGRAM – Relationship Types are: WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF

Slide 3- 27

Discussion on Relationship Types

 In the refined design, some attributes from the initial entity

types are refined into relationships:

 Manager of DEPARTMENT -> MANAGES

 Works_on of EMPLOYEE -> WORKS_ON

 Department of EMPLOYEE -> WORKS_FOR

 etc

 In general, more than one relationship type can exist

between the same participating entity types

 MANAGES and WORKS_FOR are distinct relationship

types between EMPLOYEE and DEPARTMENT

 Different meanings and different relationship instances.

Slide 3- 28

Constraints on Relationships

 Constraints on Relationship Types

 Cardinality Ratio

 One-to-one (1:1)

 One-to-many (1:N) or Many-to-one (N:1)

 Many-to-many (M:N)

 Existence Dependency Constraint (specifies minimum

participation) (also called participation constraint)

 zero

 one or more

Slide 3- 29

Many-to-one (N:1) Relationship

Slide 3- 30

Many-to-many (M:N) Relationship

Slide 3- 31

UML class diagrams

 Represent classes (similar to entity types) as large rounded boxes with three sections:  Top section includes entity type (class) name

 Second section includes attributes

 Third section includes class operations (operations are not in basic ER model)

 Relationships (called associations) represented as lines connecting the classes  Other UML terminology also differs from ER terminology

 Used in database design and object-oriented software design

 UML has many other types of diagrams for software design

Slide 3- 32

UML class diagram for COMPANY

database schema

Slide 3- 33

Other alternative diagrammatic notations

Slide 3- 34

Summary

 ER Model Concepts: Entities, attributes,

relationships

 Constraints in the ER model

 Using ER in step-by-step mode conceptual

schema design for the COMPANY database

 ER Diagrams - Notation

 Alternative Notations – UML class diagrams,

others