# Connexions

You are here: Home » Content » Data Modeling Using Entity-Relationship Model

### Lenses

What is a lens?

#### Definition of a lens

##### Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

##### What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

##### Who can create a lens?

Any individual member, a community, or a respected organization.

##### What are tags?

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

#### Affiliated with (What does "Affiliated with" mean?)

This content is either by members of the organizations listed or about topics related to the organizations listed. Click each link to see a list of all content affiliated with the organization.
• VOCW

This module is included inLens: Vietnam OpenCourseWare's Lens
By: Vietnam OpenCourseWare

Click the "VOCW" link to see all content affiliated with them.

### Recently Viewed

This feature requires Javascript to be enabled.

# Data Modeling Using Entity-Relationship Model

Module by: Nguyen Kim Anh. E-mail the author

Summary: Conceptual modeling is an important phase in the database design process. In this lecture, we will discuss brieftly about data modeling and then focus on modeling concepts of the Entity – Relationship Model, which is popular high-level conceptual data model.

## Database Application Development Process

Database Application Development is the process of obtain real-world requirements, analyse requirement, design the data and functions of the system and then implement the operations in the system.

The figure below shows the main phases of the database application development process.

As we can see, the first step is Requirement Collection and Analysis. During this step, the database designers have to interview the customers (database users) to understand the be-built system, obtain and document the data and functional requirements. The results of this step is a document including the detail requirements of the users.

Data Modeling is the next step in the process. This step is sometimes considered as a high-level and abstract design phase (conceptual design). The aims of this phase are:

• Describes what data is contained in the database (E.g entities: students, lecturers, courses, subjects etc.)
• Describes the relationships between data items (E.g Students are supervised by Lecturers; Lecturers teach Courses )
• Describes the constraints on data (E.g Student Number has exact 8 digits; A subject has 4 or 6 unit of credits only)

The data items, the relationships and constraints all are expressed using the concepts provided by the high-level data model. Because these concepts donot include the implementation details so the results of the data modeling process is a (semi) formal representation of the database structure. This result is quite easy to understand so it is used as reference to make sure that all the user’s requirements are met.

The third step is Database Design. During this step, we might have two substeps called Database Logical Design which define a database in a data model of a specific DBMS and Database Physical Design which define the internal database storage structure , file organization or indexing techniques. The last two steps shown are Database Implementation and Operations/Interfaces Building focus on create instance of schema and implementing operations and user interfaces.

In the database design phases, data is represented using a certain data model. Data Model is a collections of conceptual concepts or notations for describing data , data relationships, data semantics and data constraints. Most data models also include a set of basic operations for manipulating data in the database.

As mentioned in the first lecture, data models are either:

1. Conceptual models
• Database is considered as a collection of entities (objects) of various kinds.
• These model provide a flexible data structuring capabilities.
• The typical examples of this model is entity-relationship model, object-oriented model or semantic data model.
2. Record based logical models
• Database is considered as a collection of fixed – size record.
• These models are closer to the physical level or file structure so they are easier to implement.
• The three most wellknown models of this kind are relational data model , network data model or hierachical data model.
3. Physical models
• Provide concepts that describe the details of how data is stored in the computer’s memory

In the next section, we will discuss the Entity-Relationship Data Model in more detail.

## Entity Relationship Model (ER Model)

Entity Relationship Data Model has existed for 30 years (original description: Chen, ACM Transactions on Database Systems, 1(1), 1976).

The ER model is well­suited to data modelling for use with databases because it is fairly abstract and is easy to discuss and explain. ER models are readily translated to relations.

ER modelling is based on two concepts:

• Entities, that is, things. Eg. Prof. Ba, Course Database Sytem
• Relationships, that is, associations or interactions between entities. Eg. Prof. Ba teachs course Database Sytems

ER models (or ER schemas) are represented by ER diagrams.

### The example database

In this lecture, we will use a sample database called the COMPANY database to illustrate the concepts of Entity Relationship Model. This database contains the information about employees, departments and projects:

• There are several departments in the company. Each department has an unique identification, a name, location of the office and a particular employee who manages the department.
• A department controls a number of projects, each of which has unique name, a unique number and the budget.
• Each employee has name, an identification number, address, salary, birthdate. An employee is assigned to one department but can join in several projects. We need to record the start date of the employee in each project. We also need to know the direct supervisor of each employee.
• We want to keep track of the dependents of the employees. Each dependent has name, birthdate and relationship with the employee.

### Entity, Entity Set and Entity Type

Entity is an object in the real world with an independent existence and can be differentiated with other object

An entity might be

• Object with physical existence. Eg a lecturer, a student, a car
• Object with conceptual existence. Eg a course, a job, a position

An Entity Type defines a collection of similar entities

An Entity Set is a collection of entities of an entity type at a point of time. In ER diagram, an entity type is represented by a name in a box

### Attributes

Each entity is described by a set of attributes that model “properties of interest”

E.g. Employee = (Name, Address, Age, Salary)

Each attribute has a name, associate with an entity and is associated with a domain of legal values. However the information about attribute domain is not presented on the ER diagram

In the diagram, each attribute is represented by an oval with a name inside.

Types of Attributes

• SIMPLE attributes are attributes that are drawn from the atomic value domains

E.g. Name = {John} ; Age = {23}

• COMPOSITE attributes: Attributes that consist of a hierarchy of attributes

E.g. Address may consists of “Number”, “Street” and “Suburb” → Address = {59 + ‘Meek Street’ + ‘Kingsford’}

• SINGLE VALUED attributes: Attributes that have only one value for each entity

E.g. Name, Age for EMPLOYEE

• MULTIVALUED attributes: Attributes that have a set of values for each entity

E.g. Degrees of a person: ‘ BSc’ , ‘MIT’, ‘PhD’

• DERIVED attributes: Attributes Contain values that are calculated from other attributes

Eg. Age can be derived from attribute DateOfBirth. In this situation, DateOfBirth might be called Stored Attribute.

### Keys

An important constraint on the entities is the key. Key is an attribute or a group of attributes whose values can be used to uniquely identify individual entity in an entity set.

For example, for the entity EMPLOYEE = {EID, Name, Address, Age, Salary}

• Definite keys are any set that involving EID
• Possible keys might be {Name, Address}
• Unlikely keys: {Name}, {Age}

There is a key that is chosen by the database designer used as an identifying mechanism for the whole entity set: primary key. This key is indicated by underlying attributes in the ER model.

### Relationship, Relationship Set and Relationship Type

• Relationship is an association among several entities

Eg. Employee (John) joins Project (Netlife)

• The set of similar associations at a point of time is called the Relationship Set

Eg. the following “Joins in ” is a relationship set

Employee (John) joins in Project (Netlife)

Employee (Mark) joins in Project (GreenNet)

Employee (Sara) joins in Project (mFORM)

Employee (Steve) joins in Project (Netlife)

• Relationship type is a collection of relationships among entities from a certain set of Entity Types

We can define the relationship type more formally.

Definition: Consider n ≥ 2 possibly non-distinct entity types E1, E2, …, En.

A relationship is a tuple(e1, e2,…, en) is a subset of E1 x E2 x … x En.

A relationship type R is a subset of the Cartesian Product E1 x E2 x … x En.

R has degree n

In the ER diagram, relationship type is represented by a diamond with a name inside and connects by straight line to the rectangles representing entity type.

#### Degree of Relationship Type

The degree of a relationship type is the number of participating entyties types.

• Unary (Recursive) relationship type is the relationship that involve only one entity type. However, the same entity type participates in a relationship type in different roles. For example, figure below shows the Supervise relationship type which relates an Employee and a Supervisor who is also an employee. So in this relationship, one employee has the role of supervisor, another has the role of supervisee.

In ER model, we have the diagram

• Binary relationship type: This relationship type has two entity types link together. This is the most common relationship.

For example the “Joins in” relationship between EMPLOYEE and PROJECT

The ER diagram for this relationship type is :

• Ternary relationship type: If there are three entity types link together, the relationship is called ternary relationship.

For example: The Supply relationship associates a SUPPLIER, a PART and a PROJECT.

The ER diagram for this relationship type is:

#### Relationship Type Constraints

The constraints of relationship types limit the possible combination of entities that participate in the relationship set. Two main types of constraints is mapping cardinality and participation

##### Mapping Cardinality

Mapping Cardinality describes the maximum number of entities that a given entity can be associated with via a relationship. In this section, we consider only the cardinality constraint for the binary relationship. The possible cardinality for binary relationship types are : One - to- One (1-1) , One – to – Many (1 – N) and Many – to – Many (M-N).

• One to one relationship: Given two entity sets A and B, there is a one-to-one relationship between A and B if each entity in the set A is associated with at most one entity in the set B and vice versa each entity in the set B is associated with at most one entity in the set A.

Example: An employee if is a manager then can manage one department and a department can have only one manager.

• One to many relationship: There is a one to many relationship sets associates two entities sets A and B if each entity in A is associated with several entities in B however, each entity in B is associated with at most one entity in A.

Example: There are many employees work in a department however, an employee can work for only one department.

• Many to many relationship : There is a many to many relationship sets associates two entities sets A and B if each entity in A is associated with several entities in B however, each entity in B is associated with several entities in A.

Example: An employee can join in several projects and a project can have several employees

##### Participation

The participation constraints specifiy whether the existence of an entity depends on its being related to another entity via the relationship type.

Participation in relationship set R by entity set A may be

• Total : It means every entity a in A participates in at least 1 relationship in R
• Partial: It means only some a in A participate in relationships in R

Example Every project have at least 1 employee joined in it.

Not every employee in the company join in a project

#### Attributes of Relationship Types

The relationship types can have attributes like entity types. For 1-1 and 1-N relationship types, attributes of the relationship can be migrated to one of the participating entity types. However, for M-N relationship types, attributes must be specified as the relationship attribute.

Example: The StartDate attribute records the date on which an employee joins in a project

### Weak Entity Type

Weak entity types are those whose existence depends on the existence of other entity type. Entities belongs to weak entity type are identified by being related to specific entities from another entity type which is called strong entity types.

The weak entity type does not have key attributes of their own. The keys of this type os partially or totally derived from strong entity types.

In ER diagram, weak entity and strong/weak relationship are denoted by double box/diamond

Example: Entity type DEPENDENT related to EMPLOYEE. This entity type is used to keep track of the dependents of each employees via 1-N relationship. DEPENDENT entity has Name, Birthdate and Relationship. Two dependents of two distinct employees might have the same {Name, Birthdate, Relationship} values but they are totally different. They are identified as distinct only after consider the association with particular employee entities to which the dependents relates. So the key of the DEPENDENT might be {EID, Name} in which EID is the attribute from the strong entity type EMPLOYEE.

### Entity Relationship Diagram for COMPANY database

Modeling Entities:

Modeling Relationships

## Content actions

PDF | EPUB (?)

### What is an EPUB file?

EPUB is an electronic book format that can be read on a variety of mobile devices.

My Favorites (?)

'My Favorites' is a special kind of lens which you can use to bookmark modules and collections. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need an account to use 'My Favorites'.

| A lens I own (?)

#### Definition of a lens

##### Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

##### What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

##### Who can create a lens?

Any individual member, a community, or a respected organization.

##### What are tags?

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

| External bookmarks