An Entity Relationship Diagram (ERD) is a visual representation of different entities within a system and how they relate to each other.
Agenda: To create ER diagrams for the tables in the database.
Consider a hypothetical scenario in which Restaurant data is stored in a database. The database contains the following tables:
Following are the tables: Food_Item, List_Recipies, Food_Menu, Order_Details, My_Customers, My_Promo, My_Orders, My_Employees, My_Promo
In the diagrams above the Column names in orange are called ‘Entities’ and the keys in green are called ‘Attributes’. The following diagram describes this further.
Follow the steps to create ER diagram for the above tables:
Go to phpMyAdmin and create a database named ‘Restaurant’.
To populate the tables in the database run the following SQL commands.
CREATE TABLE IF NOT EXISTS Food_Item ( id INTEGER NOT NULL PRIMARY KEY , menu_id INTEGER , item_name VARCHAR(40) , Item_price VARCHAR(20) , Stock VARCHAR(20))
CREATE TABLE IF NOT EXISTS Customer( id INTEGER NOT NULL PRIMARY KEY , food_item_id INTEGER , name VARCHAR(40) , contact INTEGER(30) , member_id VARCHAR(20))
CREATE TABLE IF NOT EXISTS Employees( emp_id INTEGER NOT NULL PRIMARY KEY , customer_id VARCHAR(30) , name VARCHAR(40) , address Varchar(20) , salary VARCHAR(20))
CREATE TABLE IF NOT EXISTS Promo( id INTEGER NOT NULL PRIMARY KEY , order_id VARCHAR(30), promo_desc VARCHAR(40) , discount VARCHAR (20) , expiry_date VARCHAR(20))
CREATE TABLE IF NOT EXISTS Food_Menu( id INTEGER NOT NULL PRIMARY KEY, employee_id VARCHAR(20), menu_name VARCHAR(40), price Varchar(20), cooking_time VARCHAR(20))
CREATE TABLE IF NOT EXISTS Orders( id INTEGER NOT NULL PRIMARY KEY , employee_id INTEGER , order_date VARCHAR(40) , order_type Varchar(20) , total_amount VARCHAR(20))
Now your database is populated with all the tables.
Note: You do not need to insert data in the tables to create an Entity Relationship Diagram. You just need the field names and their data types.
In the top menu select More → Designer option as shown in the image below.
This will open the designing mode. You can see all the tables scattered on the designing sheet something like this:
Now you have to create relationships between the different tables. The following chart will help you to understand the relationships between the two tables better.
Now create relationships by clicking on the relationship icon on the left menu bar. Then select the primary key and foreign key in a relation and click on create relationship option. Perform the same for all the rest of the tables.
The result will look something like this:
Now you had create ER Diagrams for your database.
The last page of the report will be as shown below:
Hence these are the basic steps of creating an Entity-Relationship (ER) Diagrams [create ER diagrams] for a database.
Thanks For Reading
Read More on Aelum Blogs
Author: Abhikhya Ashi
Designation: Senior Php Developer