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.

    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.

    Comments

    Post a Comment

    Popular posts from this blog

    KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

     About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
    Read more

    Procedures in PL/SQL

    Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
    Read more

    Cursors in PL/SQL

    In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
    Read more