Understanding the Rules of ER Diagrams
Entity-Relationship Diagrams (ER Diagrams or ERDs) are one of the most popular tools for database design. They visually represent the entities (objects or concepts) within a system and the relationships between them. ER Diagrams are essential for database engineers and developers because they provide a blueprint for how data is structured and connected.
In this article, we’ll cover the rules that govern ER Diagrams, ensuring you understand how to build accurate, scalable, and effective database structures. These rules are essential for anyone working with databases and data models.
Rules of ER Diagrams
1. Entity Representation
- Entity as Rectangle: Entities are depicted as rectangles in an ER Diagram. An entity can be a physical object (e.g., a person, product) or an abstract concept (e.g., order, transaction).
-
Naming: The entity name should be a singular noun and be descriptive enough to represent the concept clearly (e.g.,
Customer
,Order
,Product
).
2. Attributes of Entities
-
Attributes as Ovals: Attributes represent properties or characteristics of an entity and are represented by ovals connected to their entity. For instance, an entity
Customer
could have attributes likeCustomer_ID
,Name
, andAddress
. - Key Attributes (Primary Key): The primary key attribute uniquely identifies each instance of the entity. It is typically underlined in the ERD.
-
Composite Attributes: These attributes can be broken down into more meaningful sub-parts. For example, a
FullName
attribute might be split intoFirstName
andLastName
. -
Multivalued Attributes: Attributes that can hold multiple values are represented by double ovals. For example, a
PhoneNumber
attribute for a customer might allow storing multiple phone numbers. -
Derived Attributes: These attributes are values that can be calculated from other attributes. They are represented by dashed ovals (e.g.,
Age
can be derived from theDateOfBirth
).
3. Relationships Between Entities
-
Relationship as Diamond: Relationships between entities are shown as diamonds, connected to the related entities. The name of the relationship should clearly describe its meaning (e.g.,
Works_For
,Places
). -
Binary Relationships: The most common type, it involves two entities. For example, a
Customer
places anOrder
. -
Ternary Relationships: Involves three entities. For instance, an entity set with
Project
,Employee
, andRole
might be part of a ternary relationship. -
Recursive Relationships: An entity can have a relationship with itself. For instance, an
Employee
may be aManager
of anotherEmployee
.
4. Cardinality of Relationships
-
1:1 (One-to-One): In a one-to-one relationship, a single instance of an entity is related to a single instance of another entity. Example: A
Person
has onePassport
. -
1:N (One-to-Many): In a one-to-many relationship, a single instance of an entity is related to multiple instances of another entity. Example: A
Customer
can place manyOrders
. -
M:N (Many-to-Many): In a many-to-many relationship, multiple instances of one entity can relate to multiple instances of another. Example: A
Student
can enroll in multipleCourses
, and aCourse
can have multipleStudents
.
5. Participation Constraints
-
Total Participation: If all instances of an entity must participate in a relationship, the participation is total. This is represented by a double line connecting the entity to the relationship. Example: All
Students
must be enrolled in at least oneCourse
. -
Partial Participation: If only some instances of an entity participate in a relationship, the participation is partial, represented by a single line. Example: Not all
Employees
may beManagers
.
6. Generalization and Specialization
-
Generalization: It is the process of extracting shared characteristics from multiple entities and creating a generalized entity. For example,
Vehicle
could be a generalized entity forCar
,Bike
, andTruck
. -
Specialization: This is the reverse process of generalization where a generic entity is divided into more specific entities based on some characteristic. For example,
Employee
can be specialized intoManager
,Developer
, andDesigner
. -
Inheritance: Attributes of the higher-level entity (parent) are inherited by the lower-level entities (child). For example, if
Employee
has aName
attribute,Manager
will also haveName
.
7. Weak Entities
- Weak Entity Representation: Weak entities cannot be uniquely identified by their own attributes alone and depend on a "strong" or "owner" entity. They are depicted with double rectangles.
- Partial Key: Weak entities have a partial key, which when combined with the primary key of the owner entity, can uniquely identify its instances. Partial keys are underlined with a dashed line.
- Identifying Relationship: A weak entity is always associated with an identifying relationship, depicted by a double diamond, connecting it to the strong entity.
8. Aggregation
-
Aggregation: This is used when we need to model a relationship between a relationship and an entity. For example, if you want to model the fact that an
Employee
works on aProject
through aDepartment
, you would use aggregation to simplify this complex relationship.
9. ER Diagram Conventions
- Naming Conventions: Entities should have meaningful, concise, and unique names. Attributes and relationships should also be named appropriately to avoid ambiguity.
- Consistency: The diagram should be consistent in the use of symbols, naming, and structure. Always follow the same convention throughout to ensure clarity.
Building a Full ER Diagram Project and Implementing It in MySQL
Creating an ER (Entity-Relationship) diagram and implementing the corresponding database tables in MySQL is an excellent way to deepen your understanding of database design. This guide will walk you through the process step-by-step, from creating the ER diagram to building and interacting with the database in MySQL. Let’s dive in!
Project Overview
Start by defining the scope of your project. Choose a domain that interests you, whether it's an online bookstore, a hotel management system, or a school database. For this guide, we’ll use an Online Shopping System as an example project.
Steps to Create the Project
Step 1: Define the Requirements
To begin, determine the core functionalities your project should have. For an online shopping system, you’ll want to manage:
- Customers
- Products
- Orders
- Payments
- Shipping information
Step 2: Identify Entities and Attributes
Identify the key entities you’ll need for the project, along with their respective attributes. Here are the major entities for the Online Shopping System:
-
Customer
-
CustomerID
(Primary Key) FirstName
LastName
Email
Phone
Address
-
-
Product
-
ProductID
(Primary Key) ProductName
Description
Price
StockQuantity
-
-
Order
-
OrderID
(Primary Key) OrderDate
TotalAmount
-
CustomerID
(Foreign Key referencing Customer)
-
-
OrderItem
-
OrderItemID
(Primary Key) -
OrderID
(Foreign Key referencing Order) -
ProductID
(Foreign Key referencing Product) Quantity
-
-
Payment
-
PaymentID
(Primary Key) -
OrderID
(Foreign Key referencing Order) PaymentDate
Amount
PaymentMethod
-
-
Shipping
-
ShippingID
(Primary Key) -
OrderID
(Foreign Key referencing Order) ShippingAddress
ShippingDate
DeliveryDate
-
Step 3: How to Create an ER Diagram from Scratch – A Step-by-Step Guide
Creating an ER (Entity-Relationship) Diagram from scratch may seem daunting, but by following a clear step-by-step process, it becomes simple. Here is a guide for absolute beginners that outlines exactly how to create an ER diagram and what shapes to use for each element.
Step 1: Choose Your Tool
Before you start designing, choose an ER diagram tool. Here are a few popular ones:
- Lucidchart
- Draw.io (now called diagrams.net)
- MySQL Workbench (if you're planning to directly work with databases)
- SmartDraw
All of these tools offer a variety of shapes that can help you model your database visually.
Step 2: Identify Entities
Entities are the objects or concepts that store data. Examples include Customer
, Order
, Product
, etc. Each entity will eventually be turned into a table in your database.
Shape to Use: Rectangle
- In ER diagrams, entities are represented by rectangles.
- Each rectangle will contain the name of the entity.
How to Do It:
- Start by dragging and dropping a rectangle onto your workspace for each entity.
- Label each rectangle with the entity’s name, e.g.,
Customer
,Order
,Product
, etc.
Step 3: Define Attributes for Each Entity
Attributes are the pieces of information that describe an entity. For example, a Customer
entity may have attributes like CustomerID
, FirstName
, LastName
, Email
, and Phone
.
Shape to Use: Oval (Ellipses)
- Attributes are represented by ovals connected to their respective entities with a line.
How to Do It:
- For each entity, draw ovals for every attribute you’ve identified (like
FirstName
,Email
, etc.). - Use lines to connect each oval to the respective entity.
- If an attribute is a primary key (like
CustomerID
), you can underline it to show that it’s unique for the entity.
Step 4: Identify Relationships Between Entities
Once you’ve identified your entities and their attributes, the next step is to define how these entities interact with one another. Relationships between entities help define how data is structured and how the database will function. In this step, we'll use diamonds to represent these relationships and clearly specify the cardinality (1:1, 1:M, or M:N). Below are the specific relationships and their descriptions for an Online Shopping System.
Relationships:
- places: A Customer can place multiple Orders (1:M relationship).
- contains: An Order can contain multiple OrderItems (1:M relationship).
- is a: Each OrderItem refers to one Product (M:1 relationship).
- has (for payment): An Order has one Payment (1:1 relationship).
- has (for shipping): An Order can have one Shipping record (1:1 relationship).
Example Steps to Create the Relationships
1. Customer places Order (1:M)
- Relationship Name: places
- Cardinality: One Customer can place multiple Orders. But each Order is placed by only one Customer.
How to Do It:
- Draw a diamond labeled places.
- Draw a line connecting Customer (rectangle) to places (diamond). Label the connection on the Customer side as 1.
- Draw a line connecting places (diamond) to Order (rectangle). Label the connection on the Order side as M (many).
This step defines the Customer places Order relationship, with the cardinality 1:M (one-to-many).
2. Order contains OrderItems (1:M)
- Relationship Name: contains
- Cardinality: One Order can contain multiple OrderItems, but each OrderItem belongs to a specific Order.
How to Do It:
- Draw a diamond labeled contains.
- Draw a line connecting Order (rectangle) to contains (diamond). Label the connection on the Order side as 1.
- Draw a line connecting contains (diamond) to OrderItem (rectangle). Label the connection on the OrderItem side as M (many).
This defines the Order contains OrderItems relationship, specifying the 1:M (one-to-many) nature of the relationship.
3. OrderItem is a Product (M:1)
- Relationship Name: is a
- Cardinality: Each OrderItem refers to exactly one Product, but one Product can appear in multiple OrderItems.
How to Do It:
- Draw a diamond labeled is a.
- Draw a line connecting OrderItem (rectangle) to is a (diamond). Label the connection on the OrderItem side as M (many).
- Draw a line connecting is a (diamond) to Product (rectangle). Label the connection on the Product side as 1 (one).
This step defines the OrderItem is a Product relationship, which is M:1 (many-to-one).
4. Order has Payment (1:1)
- Relationship Name: has
- Cardinality: Each Order has only one Payment record, and each Payment refers to only one Order.
How to Do It:
- Draw a diamond labeled has (for Payment).
- Draw a line connecting Order (rectangle) to has (diamond). Label the connection on both sides as 1 (one).
- Draw a line connecting has (diamond) to Payment (rectangle). Label the connection as 1 on both ends.
This defines the Order has Payment relationship, which is 1:1 (one-to-one).
5. Order has Shipping (1:1)
- Relationship Name: has
- Cardinality: Each Order can have only one Shipping record, and each Shipping record is tied to one Order.
How to Do It:
- Draw a diamond labeled has (for Shipping).
- Draw a line connecting Order (rectangle) to has (diamond). Label the connection on both sides as 1 (one).
- Draw a line connecting has (diamond) to Shipping (rectangle). Label the connection on both ends as 1 (one).
This defines the Order has Shipping relationship, also 1:1 (one-to-one).
Step 5: Connect Entities to Relationships
Now that we have defined all the relationships, let's connect everything:
-
Customer places Order:
- Use 1:M to connect Customer to places and Order.
-
Order contains OrderItems:
- Use 1:M to connect Order to contains and OrderItem.
-
OrderItem is a Product:
- Use M:1 to connect OrderItem to is a and Product.
-
Order has Payment:
- Use 1:1 to connect Order to has and Payment.
-
Order has Shipping:
- Use 1:1 to connect Order to has and Shipping.
The ER-Diagram will look something like this:
By this step, you should have all your entities, relationships, and cardinality mapped out clearly in the ER diagram, forming the core structure of your database. This diagram will act as a blueprint when you move on to implementing your tables in SQL.
Step 4: Translate the ER Diagram into MySQL Tables
Once you have your ER diagram, it’s time to implement it in MySQL by creating tables based on your design.
Here are some SQL table creation scripts for the Online Shopping System:
Customer Table
CREATE TABLE Customer (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(15),
Address VARCHAR(255)
);
Product Table
CREATE TABLE Product (
ProductID INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL,
Description TEXT,
Price DECIMAL(10, 2) NOT NULL,
StockQuantity INT NOT NULL
);
Order Table
CREATE TABLE `Order` (
OrderID INT AUTO_INCREMENT PRIMARY KEY,
OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP,
TotalAmount DECIMAL(10, 2) NOT NULL,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
OrderItem Table
CREATE TABLE OrderItem (
OrderItemID INT AUTO_INCREMENT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT NOT NULL,
FOREIGN KEY (OrderID) REFERENCES `Order`(OrderID),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);
Payment Table
CREATE TABLE Payment (
PaymentID INT AUTO_INCREMENT PRIMARY KEY,
OrderID INT,
PaymentDate DATETIME DEFAULT CURRENT_TIMESTAMP,
Amount DECIMAL(10, 2) NOT NULL,
PaymentMethod VARCHAR(50),
FOREIGN KEY (OrderID) REFERENCES `Order`(OrderID)
);
Shipping Table
CREATE TABLE Shipping (
ShippingID INT AUTO_INCREMENT PRIMARY KEY,
OrderID INT,
ShippingAddress VARCHAR(255),
ShippingDate DATETIME,
DeliveryDate DATETIME,
FOREIGN KEY (OrderID) REFERENCES `Order`(OrderID)
);
Step 5: Implement in MySQL
After defining the structure, it’s time to create the database and implement these tables in MySQL.
Set Up MySQL and Create the Database
CREATE DATABASE OnlineShoppingDB;
USE OnlineShoppingDB;
Create the Tables
Run the SQL scripts you wrote in Step 4 to create the tables.
Step 6: Populate the Tables
Now, insert some sample data to test how your design works with actual records. Here are some example insert statements:
Insert Sample Data into Customer Table
INSERT INTO Customer (FirstName, LastName, Email, Phone, Address)
VALUES ('John', 'Doe', 'john@example.com', '1234567890', '123 Main St');
Insert Sample Data into Product Table
INSERT INTO Product (ProductName, Description, Price, StockQuantity)
VALUES ('Laptop', 'A powerful gaming laptop', 1200.00, 10);
Insert Sample Data into Order Table
INSERT INTO `Order` (TotalAmount, CustomerID)
VALUES (1200.00, 1);
Insert Sample Data into OrderItem Table
INSERT INTO OrderItem (OrderID, ProductID, Quantity)
VALUES (1, 1, 1);
Insert Sample Data into Payment Table
INSERT INTO Payment (OrderID, Amount, PaymentMethod)
VALUES (1, 1200.00, 'Credit Card');
Insert Sample Data into Shipping Table
INSERT INTO Shipping (OrderID, ShippingAddress, ShippingDate)
VALUES (1, '123 Main St', NOW());
Step 7: Querying the Database
Now that your database is set up and populated, you can write queries to retrieve information. For example, to retrieve all orders placed by a customer, you could use the following query:
SELECT o.OrderID, o.OrderDate, o.TotalAmount
FROM `Order` o
JOIN Customer c ON o.CustomerID = c.CustomerID
WHERE c.Email = 'john@example.com';
Benefits of This Project
- Hands-On Experience: You’ll gain practical experience with ERD design and SQL coding.
- Concept Reinforcement: Working through real-world scenarios helps reinforce your understanding of database concepts.
- Problem-Solving Skills: You’ll learn to troubleshoot issues and refine your design based on the challenges you encounter.
- Portfolio Development: A complete database project can be a valuable addition to your portfolio, showcasing your skills to potential employers.
Conclusion
Building an ER diagram project and implementing it in MySQL is a fantastic way to solidify your database knowledge. You’ll gain hands-on experience and a better understanding of how relational databases function in real-world applications. This project will serve as a strong foundation for more advanced database work in the future.