ER Diagram + SQL: Online Shopping System Project

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 like Customer_ID, Name, and Address.
• 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 into FirstName and LastName.
• 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 the DateOfBirth).

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 an Order.
• Ternary Relationships: Involves three entities. For instance, an entity set with Project, Employee, and Role might be part of a ternary relationship.
• Recursive Relationships: An entity can have a relationship with itself. For instance, an Employee may be a Manager of another Employee.

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 one Passport.
• 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 many Orders.
• 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 multiple Courses, and a Course can have multiple Students.

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 one Course.
• 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 be Managers.

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 for Car, Bike, and Truck.
• 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 into Manager, Developer, and Designer.
• Inheritance: Attributes of the higher-level entity (parent) are inherited by the lower-level entities (child). For example, if Employee has a Name attribute, Manager will also have Name.

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 a Project through a Department, 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:

1. Customer

   • CustomerID (Primary Key)
   • FirstName
   • LastName
   • Email
   • Phone
   • Address

2. Product

   • ProductID (Primary Key)
   • ProductName
   • Description
   • Price
   • StockQuantity

3. Order

   • OrderID (Primary Key)
   • OrderDate
   • TotalAmount
   • CustomerID (Foreign Key referencing Customer)

4. OrderItem

   • OrderItemID (Primary Key)
   • OrderID (Foreign Key referencing Order)
   • ProductID (Foreign Key referencing Product)
   • Quantity

5. Payment

   • PaymentID (Primary Key)
   • OrderID (Foreign Key referencing Order)
   • PaymentDate
   • Amount
   • PaymentMethod

6. 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:

1. Start by dragging and dropping a rectangle onto your workspace for each entity.
2. 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:

1. For each entity, draw ovals for every attribute you’ve identified (like FirstName, Email, etc.).
2. Use lines to connect each oval to the respective entity.
3. 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:

1. Draw a diamond labeled places.
2. Draw a line connecting Customer (rectangle) to places (diamond). Label the connection on the Customer side as 1.
3. 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:

1. Draw a diamond labeled contains.
2. Draw a line connecting Order (rectangle) to contains (diamond). Label the connection on the Order side as 1.
3. 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:

1. Draw a diamond labeled is a.
2. Draw a line connecting OrderItem (rectangle) to is a (diamond). Label the connection on the OrderItem side as M (many).
3. 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:

1. Draw a diamond labeled has (for Payment).
2. Draw a line connecting Order (rectangle) to has (diamond). Label the connection on both sides as 1 (one).
3. 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:

1. Draw a diamond labeled has (for Shipping).
2. Draw a line connecting Order (rectangle) to has (diamond). Label the connection on both sides as 1 (one).
3. 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:

1. Customer places Order:

   • Use 1:M to connect Customer to places and Order.

2. Order contains OrderItems:

   • Use 1:M to connect Order to contains and OrderItem.

3. OrderItem is a Product:

   • Use M:1 to connect OrderItem to is a and Product.

4. Order has Payment:

   • Use 1:1 to connect Order to has and Payment.

5. 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', '[e-mail]', '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 = '[e-mail]';

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.