Jump to Menu

How to Insert Sample Records into ERD?

Visual Paradigm allows you to design your database with entity relationship diagram (ERD), and finally generate the design to database as database schema. On top of the schema that will be generated, you can also specify default data to insert into database upon database generation. In this tutorial, we will draw a simple ERD with three entities, specify default data and generate database.
You are expected to have a fundamental knowledge on database modeling with ERD and database engineering.

Compatible edition(s): Enterprise, Professional, Standard, Modeler

  • August 16, 2010
  • Views: 50,983
  • PDF

Setting up your database

MySQL will be used as database software throughout this tutorial. You can, however, use any other types of database products that we support. When you are familiar with the database software you are using, it won't be hard for you to complete this tutorial.

Create a database and name it as myshop in advance.


create db

Creating new project

Create a new project by selecting Project > New from the application toolbar. In the New Project window, enter My Shop Model as project name and click Create Blank Project.


new project

Configuring your database

  1. Select Tools > Object-Relational Mapping (ORM) > Database Configuration... from the main menu.
  2. In the Database Configuration dialog box, check MySQL to select it as the database software.
    select db
  3. In the Database Setting panel, press the upside down arrow button next to Driver file field. If your database providers does not support downloading driver file, scroll down to Database Driver Description to deal with the problem.
    download driver
  4. Fill in the connection information to connect to database. Enter myshop as database name. Select InnoDB as engine to keep the relationships among entities when generating database. Click on Test Connection to verify the connection. Click OK to close the configuration dialog box.
    connection info

Drawing an ERD

  1. Create an ERD. Right-click on Entity Relationship Diagram in Diagram Navigator and select New Entity Relationship Diagram from the pop-up menu.
    new erd
  2. Immediately, name the diagram as MyShop ERD at the top left corner. Keep Physical Model selected at the top right corner.
    diagram name
  3. Next, create an entity. Press on Entity on diagram toolbar and drag to the diagram. Name it as PurchaseOrder and press Enter to confirm.
    create purchase order
  4. A purchase order consists of many products, while a product can appear in many purchase order. This is a typical example of many-to-many relationship. Now, create the product entity. Move the mouse pointer to the entity PurchaseOrder, press on the resource icon Many-to-Many Relationship -> Entity, drag it out and then release the mouse button to confirm. Name the entity as Product.
    create product
  5. Add columns to the entities. Right-click on PurchaseOrder and select New Column from the pop-up menu. Enter +id : int as name (note: The plus sign indicates that this is a Primary Key column). Press Enter.
    new col
  6. Entity Column
    PurchaseOrder +id : int(10),
    staff : varchar(255)
    Product qty : int(11)
    PurchaseOrder_Product +id : int(10),
    name : varchar(255)

    The result of diagram is shown as below:
    completed erd

Entering default data

  1. Select PurchaseOrder. You can see the table record editor appears at the bottom of diagram lists the columns for you to add default data. If you do not see the editor, right-click on the ERD's background and select Show Table Record Editor from the pop-up menu.
    record editor
  2. Double click on the id cell and enter 1. Then, double click on the staff cell and enter Mary. Press Enter.
    created purchase order
  3. Follow the table below to add records in PurchaseOrder entity.
    Id (PK) staff
    1 Mary
    2 David
    3 Paul
  4. Repeat the previous steps to add records in Product entity. Here are the records to add:
    Id (PK) name
    1 Shampoo (500 ml)
    2 Battery (AAA)
  5. Select PurchaseOrder_Product entity.
  6. In the editor, select 1 in PurchaseOrderid and 2 in Productid, and then enter 5 in qty.
    select po id
  7. Add records to the PurchaseOrder_Product entity. Here are the records to add:
    PurchaseOrderid (PK+FK) Productid (PK+FK) qty
    1 2 5
    2 1 2
    3 1 1
    3 2 1

Generating database

  1. Select Tools > Object-Relational Mapping (ORM) > Generate Database... from the main menu.
  2. In the Database Code Generation dialog box, check Export to database and Generate sample data. If you do not select them, both database and sample data will not be created in database.
    check option in db gen
  3. Click OK button to start generation.

Checking your database

You can now check your database to see if the schema and default data are both generated. Here is a screenshot captured under MySQL, for checking the generated schema.


check schema
This is another screen that shows the default records added to database.
check data


Turn every software project into a successful one.

We use cookies to offer you a better experience. By visiting our website, you agree to the use of cookies as described in our Cookie Policy.

OK