ERD – Entity Relationship Diagrams

ERD is Entity Relationship Diagrams in long terms, which we use to represent the data storage in a well-organized graphical way. First of all we should have a clear idea about the system we are going to create. And it is recommended to draw an ERD in the design stage before starting to develop the system.

An ERD contains following parts.

Entity

A real world object which have an independence existence in the database. (This should not be a user or an output of the system.)

Entity types:

Strong – Can exist without a dependency on other entities.

Example: – Student, Teacher

image

Weak – Exists on the existence of another entity.

Example: – Invoice (This depends on the order which it is been issued for.)

image

Attribute

Characteristics of entities which are only needed to be stored in the database.

Attribute types:

Simple – Attributes which cannot be broken into further more simple parts.

image

Composite – Attributes which can be broken into further more simple attributes.

Example: Address (no, street, city)

image

Multivalued – Attributes which may hold more than one value for that in an entity.

image

Derived – Attributes whose values can be calculated from related attribute values.

image

Key Attribute – The attributes (or set of attributes) which uniquely identifies individual instances of an entity type.

image

Relationship

A connection between two or more entities which is created when one entity referring other entities.

image

A customer places an order.

Degree of a relationship

This is noted considering the number of entities participating for the relationship.

Unary – A relationship contains only one entity connected to it.

image

Binary – A relationship contains only two entities connected to it.

image

Ternary – A relationship contains more than two entities connected to it.

image

Cardinality Ratio of a relationship

Specify the number of instances of one entity that can (or must) be associated with each instance of another entity.

The possible cardinality ratios for binary relationship types are

– 1:1 (one to one)

An order has only one invoice.

image

An order has only one invoice.

– 1:M (one to many)

image

A customer can place any amount of orders while an order can only have one customer.

– M:N (many to many)

image

An order may consist many items, and an item may be included in many orders.

Participation Constrains on a relationship

1. Total Participation

Contribution of an entity to the relationship, is essential to have the relationship.

Example: Order must have a customer. So, participation of the Order in the relationship is total.

2. Partial Participation

Contribution of an entity to the relationship is not essential to have the relationship.

Example: A Customer may not own any of orders. So, participation of the Customer in the relationship is partial.

image

Attributes on a relationship

The attribute which needs to be recorded when an instance of the relationship takes place.

image

Look at the following simple example.

“ABC Stores is a retail shop where day to day items are sold. Each item has an itemCode, name, description, category, unit, unitPrice, qtyOnHand and reOrderLevel. Each category contains categoryCode and name, while one category may contain any amount of distinct items, but one item can only be categorized under one category. Items are sold to customers, ONLY through an order. customerID, customerName, address and tel of each customer have to be stored. An order has orderNo, customerID who ordered it, date and amount of the order, and an order may contain at least one item in it. Each order may be needed to be delivered sometimes and if so, an invoice should be provided including invoiceNo, orderNo and deliveryDate.”

First of all, we have to identify the entities.

Entities Attributes
Customer customerID, customerName, address, tel
Category categoryCode, name
Item itemCode, name, description, unit, unitPrice, qtyOnHand, reOrderLevel
Order orderNo, date, amount(derived)
Invoice (weak) invoiceNo, deliveryDate

The relevant ERD is as follows.

image

Then we have to map this ERD into Relations.

To be continued….

11 comments on “ERD – Entity Relationship Diagrams

  • Dinuka , Direct link to comment

    need ER
    The company employee administrative database stores information about the employees, the department and the projects of a company. The following data have been identified in the requirements collection and analysis phase and they are to be represented in the enterprise. The company is organized into departments. Each department has a unique name, a unique number and a particular employee who manages the department. The database keeps track of the start date when an employee began managing the department. A department controls number of projects. Each project has a unique name, unique number and a single location. The database also stores the number of work hours budgeted for each project. The database stores each employees name, pin number, address, salary, sex, birth date, date hired and date he/she terminates employment. An employee is assigned to one department but he/she may work on several projects, which are not necessarily controlled by the same department. The database also keeps tracks of the number of hours an employee already worked on a project and the direct supervisor of each employee if he/she has one. (Note that an employee need not have and assigned supervisor).

  • osama , Direct link to comment

    please,,how to set cardinality and participation on ternary relation?

  • Praneeth Nilanga Peiris , Direct link to comment

    @osama : That’s very tricky. It’s mostly one-to-many. Because, in many cases when there is a ternary relationship, it generates a new table. Then it mostly one-to-many or many-to-many.

Leave a Reply