When a company asks you to make them a working, functional DBMS which they can work with, there are certain steps to follow. Let us summarize them here:
- Gathering information: This could be a written document that describes the system in question with reasonable amount of details.
- Producing ERD: ERD or Entity Relationship Diagram is a diagrammatic representation of the description we have gathered about the system.
- Designing the database: Out of the ERD we have created, it is very easy to determine the tables, the attributes which the tables must contain and the relationship among these tables.
- Normalization: This is a process of removing different kinds of impurities from the tables we have just created in the above step.
How to Prepare an ERD
Step 1
Let us take a very simple example and we try to reach a fully organized database from it. Let us look at the following simple statement:
A boy eats an ice cream.
This is a description of a real word activity, and we may consider the above statement as a written document (very short, of course).
Step 2
Now we have to prepare the ERD. Before doing that we have to process the statement a little. We can see that the sentence contains a subject (boy), an object (ice cream) and a verb (eats) that defines the relationship between the subject and the object. Consider the nouns as entities (boy and ice cream) and the verb (eats) as a relationship. To plot them in the diagram, put the nouns within rectangles and the relationship within a diamond. Also, show the relationship with a directed arrow, starting from the subject entity (boy) towards the object entity (ice cream).
Now we have to prepare the ERD. Before doing that we have to process the statement a little. We can see that the sentence contains a subject (boy), an object (ice cream) and a verb (eats) that defines the relationship between the subject and the object. Consider the nouns as entities (boy and ice cream) and the verb (eats) as a relationship. To plot them in the diagram, put the nouns within rectangles and the relationship within a diamond. Also, show the relationship with a directed arrow, starting from the subject entity (boy) towards the object entity (ice cream).
Well, fine. Up to this point the ERD shows how boy and ice cream are related. Now, every boy must have a name, address, phone number etc. and every ice cream has a manufacturer, flavor, price etc. Without these the diagram is not complete. These items which we mentioned here are known as attributes, and they must be incorporated in the ERD as connected ovals.
But can only entities have attributes? Certainly not. If we want then the relationship must have their attributes too. These attribute do not inform anything more either about theboy or the ice cream, but they provide additional information about the relationships between the boy and the ice cream.
Step 3
We are almost complete now. If you look carefully, we now have defined structures for at least three tables like the following:
We are almost complete now. If you look carefully, we now have defined structures for at least three tables like the following:
| Name |
Address
|
Phone
|
| Manufacturer |
Flavor
|
Price
|
| Date |
Time
|
However, this is still not a working database, because by definition, database should be “collection of related tables.” To make them connected, the tables must have some common attributes. If we chose the attribute Name of the Boy table to play the role of the common attribute, then the revised structure of the above tables become something like the following.
| Name |
Address
|
Phone
|
| Manufacturer |
Flavor
|
Price
|
Name
|
| Date |
Time
|
Name
|
This is as complete as it can be. We now have information about the boy, about the ice cream he has eaten and about the date and time when the eating was done.