ER Diagrams

Learn about ER diagrams

Design a normalized database schema for the following requirement.

The requirement: A library wants to maintain the record of books, members, book issue, book return, and fines collected for late returns, in a database. The database can be loaded with book information. Students can register with the library to be a member. Books can be issued to students with a valid library membership. A student can keep an issued book with him/her for a maximum period of two weeks from the date of issue, beyond which a fine will be charged. Fine is calculated based on the delay in days of return. For 0-7 days: Rs 10, For 7 – 30 days: Rs 100, and for days above 30 days: Rs 10 will be charged per day.

Sample Database Design

BOOK (Book_Id, Title, Language_Id, MRP, Publisher_Id, Published_Date, Volume, Status)
//Language_Id, Publisher_Id are FK (Foreign Key)

AUTHOR(Author_Id, Name, Email, Phone_Number, Status)

BOOK_AUTHOR(Book_Id, Author_Id)
// many-to-many relationship, both columns are PKFK
(Primary Key and Foreign Key)

PUBLISHER(Publisher_id, Name, Address)

MEMBER(Member_Id, Name, Branch_Code, Roll_Number, Phone_Number, Email_Id,Date_of_Join, Status)

BOOK_ISSUE(Issue_Id, Date_Of_Issue, Book_Id, Member_Id, Expected_Date_Of_Return,Status)
// Book+Id and Member_Id are FKs

BOOK_RETURN(Issue_Id, Actual_Date_Of_Return, LateDays, LateFee)
// Issue_Id is PK and FK

LANGUAGE(Language_id, Name) //Static Table for storing permanent data

LATE_FEE_RULE(FromDays, ToDays, Amount)
// Composite Key

Draw the ER diagram of the above problem using the tools 

Software Tools:

• draw.io: Online diagramming tool.
• Lucidchart: Online diagramming application.
• Microsoft Visio: Diagramming and vector graphics application.
• MySQL Workbench: Tool for designing and modeling databases

MySQL Workbench.

MySQL provides several tools and features to represent and create ER diagrams, most notably through MySQL Workbench. MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. It includes data modeling (ER diagrams), SQL development, and comprehensive administration tools.

Here’s how you can represent ER diagrams in MySQL using MySQL Workbench:

Steps to Create an ER Diagram in MySQL Workbench

Install MySQL Workbench:

• Download and install MySQL Workbench from the official MySQL website.

                sudo apt update

                sudo apt install mysql-workbench

Once installed, you can start MySQL Workbench from the command line or from the desktop environment.

             

From Command Line:

Open a terminal and type:           mysql-workbench

Create a New Model:

• Open MySQL Workbench.
• Go to File > New Model to create a new model.

Add a Diagram:

• In the Model overview page, click on the Add Diagram icon.
• This will open a new EER (Enhanced Entity-Relationship) Diagram tab.

Add Entities (Tables):

• Use the table tool on the left-hand side to add tables.
• Click on the Table icon and then click on the canvas to place a new table.
• Double-click the table to open the table editor, where you can define columns, data types, and set primary keys.

Define Relationships:

• Use the Foreign Key tools to define relationships between tables.
• Click the 1:n (one-to-many) or n:m (many-to-many) icons on the left, then click on the parent table and the child table to create the relationship.
• Set the foreign key constraints and specify how they link between tables.

Add Attributes:

• For each table, add the required attributes (columns) and specify their data types.
• Set primary keys by marking the appropriate column as PK.
• Add other constraints like NOT NULL, UNIQUE, and AUTO_INCREMENT as needed.

Save and Export the Diagram:

• Save your model frequently.
• You can export the diagram to various formats like PNG, PDF, or SVG by going to File > Export > Export as PNG/PDF/SVG.

Example:

Let's walk through a simple example to create an ER diagram for an online bookstore.

1. Open MySQL Workbench and create a new model.

2. Add Entities:

   • Add tables for Book, Author, Customer, Order, BooksAuthors, and OrderDetails.
   • Define the columns for each table:
     • Book: ISBN (PK), Title, Publication Year, Price
     • Author: AuthorID (PK), FirstName, LastName
     • Customer: CustomerID (PK), Name, Email, Address
     • Order: OrderID (PK), OrderDate, CustomerID (FK)
     • BooksAuthors: ISBN (FK), AuthorID (FK)
     • OrderDetails: OrderID (FK), ISBN (FK), Quantity

3. Define Relationships:

• Create a many-to-many relationship between Book and Author using the BooksAuthors table.
• Create a one-to-many relationship between Customer and Order.
• Create a many-to-many relationship between Order and Book using the OrderDetails table.

MySQL Workbench Features:

• Forward Engineering: Once your ER diagram is complete, you can forward engineer the diagram to create the corresponding database schema in MySQL. Go to Database > Forward Engineer to generate the SQL scripts and execute them on your MySQL server.
• Reverse Engineering: You can also create an ER diagram from an existing database by reverse engineering. Go to Database > Reverse Engineer and connect to your database to import its schema into a model.

https://medium.com/@tushar0618/how-to-create-er-diagram-of-a-database-in-mysql-workbench-209fbf63fd03

Using MySQL Workbench, you can visually design your database schema, manage relationships, and generate the necessary SQL scripts to create and maintain your database, making it a powerful tool for database design and management.