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.
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
Weak – Exists on the existence of another entity.
Example: – Invoice (This depends on the order which it is been issued for.)
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.
Composite – Attributes which can be broken into further more simple attributes.
Example: Address (no, street, city)
Multivalued – Attributes which may hold more than one value for that in an entity.
Derived – Attributes whose values can be calculated from related attribute values.
Key Attribute – The attributes (or set of attributes) which uniquely identifies individual instances of an entity type.
Relationship
A connection between two or more entities which is created when one entity referring other entities.
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.
Binary – A relationship contains only two entities connected to it.
Ternary – A relationship contains more than two entities connected to it.
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.
An order has only one invoice.
– 1:M (one to many)
A customer can place any amount of orders while an order can only have one customer.
– M:N (many to many)
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.
Attributes on a relationship
The attribute which needs to be recorded when an instance of the relationship takes place.
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.
Then we have to map this ERD into Relations.
To be continued….
wow … nice work bro !! rely hlp full !!
good work machng……. keep it up !!!!!!
wonderfully described.thanks bro!!
thanks buddy, it helps a lot….
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).
[…] Lets take the example from my first post on ERD. […]
Thanks for the info! I have also found a great website that teaches How to Draw ERD (Entity Relationship Diagram) using Lucidchart and it is very helpful! Check it out!
please,,how to set cardinality and participation on ternary relation?
@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.
I have used Creately for ER diagramming and its an amazing tool with lot of system examples. You can check some of the er diagram templates here.
Thank you!!!!