Skip to content Skip to navigation

Connexions

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

Navigation

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? tag icon

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

This content is ...

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.

Figure 1: The main phases of the database application development process
Figure 1 (graphics1.png)

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

Figure 2: Entity type in ER diagram
Figure 2 (graphics2.png)

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.

Figure 3: Attribute diagram
Figure 3 (graphics3.png)

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.

Figure 4: Notation of Composite attribute in ER Diagram
Figure 4 (graphics4.png)
Figure 5: Notation of Multivalued attribute in ER Diagram
Figure 5 (graphics5.png)
Figure 6: Notation of Derived attribute in ER Diargam
Figure 6 (graphics6.png)

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.

Figure 7: Key Notation
Figure 7 (graphics7.png)

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.

Figure 8: Relationship type notation
Figure 8 (graphics8.png)

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.
Figure 9: Supervise Relationship
Figure 9 (graphics9.png)

In ER model, we have the diagram

Figure 10: Supervisor Notation
Figure 10 (graphics10.png)
  • 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

Figure 11: "Joins in" Relationship
Figure 11 (graphics11.png)

The ER diagram for this relationship type is :

Figure 12: "Joins in" relationship notation
Figure 12 (graphics12.png)
  • 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.

Figure 13: Ternary relationship example
Figure 13 (graphics13.png)

The ER diagram for this relationship type is:

Figure 14: Ternary relationship notation
Figure 14 (graphics14.png)

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.
Figure 15: One-to-one relationship example
Figure 15 (graphics15.png)

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

Figure 16: One-to-one relationship notation
Figure 16 (graphics16.png)
  • 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.
Figure 17: One-to-many relationship example
Figure 17 (graphics17.png)

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

Figure 18: One-to-many relationship notation
Figure 18 (graphics18.png)
  • 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.
Figure 19: Many-to-many relationship example
Figure 19 (graphics19.png)

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

Figure 20: Many-to-many relationship notation
Figure 20 (graphics20.png)
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

Figure 21: Participation in relationship notation
Figure 21 (graphics21.png)

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

Figure 22: Attribute in relationship example
Figure 22 (graphics22.png)

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.

Figure 23: Weak entity type notation
Figure 23 (graphics23.png)

Entity Relationship Diagram for COMPANY database

Modeling Entities:

Figure 24: Entities of COMPANY database
Figure 24 (graphics24.png)
Figure 25: Entities of COMPANY database
Figure 25 (graphics25.png)

Modeling Relationships

Figure 26: Relationships of COMPANY database
Figure 26 (graphics26.png)

Content actions

Download module as:

PDF | EPUB (?)

What is an EPUB file?

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

Downloading to a reading device

For detailed instructions on how to download this content's EPUB to your specific device, click the "(?)" link.

| More downloads ...

Add module to:

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? tag icon

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