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.
A real world object which have an independence existence in the database. (This should not be a user or an output of the system.)
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.)
Characteristics of entities which are only needed to be stored in the database.
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.
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.
|Customer||customerID, customerName, address, tel|
|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….