Saturday, July 14, 2012

Good day students! I know you have a hard time constructing an ERD (Entity Relationship Diagram or Model) of your project.  Well, it is indeed a bit difficult to construct an ERD out from a scratch, but it can be easily done without wasting so many symbols and time.  You see our books are telling us the standard procedures on making ERDs but they haven't thought us some guidelines on how to start and that's where we make mistakes in the process.

So here are the requirements before I would start my tutorial:

1. Basic knowledge on ERD symbols and usage.
2. Knowledge on relationship cardinalities.
3. Basic knowledge on Normalization at least 1st or 2nd normal forms.
4. Business rules of the project.  -- this is important!

 I presumed that you have those knowledge I mentioned above, because you need those things to practice ERD or ERM.  Before I proceed let me talk first the importance of ERD.  There are many students who proceed right away on their Database software making tables without analyzing the quality of their database.  A database which is not properly done is dangerous to the system, a system could experience  data redundancy, inconsistency, loss of data, etc.  and if you are the one managing this kind of database the work is very stressful.  Why?  Because this not programming that error messages will just simply pop-up on your screen, database bugs are not felt by means of error messages but the way it produces output.   

Ok! Now you know the importance of ERD let's start!

Step 1 - Write the business rules of your project (This will serve us processes on the development of the system)

Let's say the Project is Ordering System  and you have identified the business rules:

1.  Customer can order only one item at a time
2.  Customer pays the item in installment basis
3.  Supplier sends stocks by request
4.  one request has many stocks on it
5.  The system can monitor orders by customer
6.  The system can monitor payments done customer
7.  The system can monitor stocks delivered  in every request 

 Note:  When you write your business rules you should group related processes. example: start with the processes pertaining to the customer, then supplier and lastly what the system can do.   Do not jumble the processes.

Step 2  - Identify all possible data that need to be stored.  Write anything on the board or scratch paper anything that you think is worth to store.

Fig 1.0

In the example we have identified Customer, Product, Supplier etc.

If you have the business rules, identifying the data need to be stored is not a problem.

Fig 2.0

Step 3 - Identify the Strong Entities

Strong entity is some sort of data containers which are required  for the system to sustain or function.
In figure 1.0  Strong entities are checked.  Why they are strong entities?  Because Ordering system cannot work or function without them, in other words those entities are required for the system to work.    The ordered items, date ordered, down payment, etc. those are just data that would come out during the process of strong entities.  "You cannot order, if you have no customer and product, right?"

Note: most likely strong entities have no foreign keys.

Step 4 - Make a relationship of your Strong entities

Fig 3.0

Fig 3.0  I simply connect strong entities.

How to add relationship?

When you connect two strong entities simply think of an event or action that causes the two or more entities to meet.
In Fig 3.0 Customer and Product would meet when there is order and, Supplier and Product when there is request.

Step 5 - Identify Sprouting Data

We all know that the only way a relationship will become a table is when there are attributes sprouting in the process.   We call that relationship as Associative Entity, some books they call it the Bridge.

In our example the sprouting Data during ordering are the following:
Date Ordered
Invoice Number,etc.

 And for the request:

Date requested
Date Delivered, etc.

Now let's go to our business rules and try to check if we have answered some of it.

  * Customer can order only one item at a time
  * Supplier sends stocks by request
  * The system can monitor orders by customer
  * The system can monitor stocks delivered  in every request

Good, if we have met four processes from our business rules but  there are still things that we need to solve:

1. Multiple Payments by the customer
2. Multiple Items by one request

Step 6 - Make a relationship to hold the multiple records

Fig. 4.0
In fig 4.0  we added two more associative entities the Payment and the Selected Items.  These were relationships which we turned to associative entities because they have sprouting data.

The Payment entity holds all payments made by the customer, while the Selected items entity holds all the items belong to one request.

Let's Review our business Rules:

1.  Customer can order only one item at a time   - checked
2.  Customer pays the item in installment basis  - checked
3.  Supplier sends stocks by request    - checked

4.  one request has many stocks on it  - checked
5.  The system can monitor orders by customer   - checked

6.  The system can monitor payments done customer   - checked
7.  The system can monitor stocks delivered  in every request   - checked

Step 7 - Convert ERD to Database Relationship
To make your ERD ready for your DBMS then convert it.

Fig 5.0
Fig 5.0 shows only the connection of primary and foreign keys.

Note: When you convert your ERD to Database relationship always remember that Associative entities hold the foreign keys of the entities connected to it.

I hope the tutorial helps you in creating simplify ERD - Thank you!
(c)2012 the scanhead