Visual Paradigm logo
Jump to Menu

How to Generate Redshift Database from ERD?

You can design database with Entity Relationship Diagram (ERD), and then generate a physical database from the design. In this tutorial, you will draw a simple ERD for an online bike shop, enter sample data for your database and then generate an Amazon Redshift database.

Compatible edition(s): Enterprise, Professional, Standard

  • April 08, 2016
  • Views: 4,342
  • PDF

Create database in Redshift

In order to walk through this tutorial, please setup a new database in Redshift first. In this tutorial we will interact with a database named Tutorial01. You may use any name you like.

Configure default database for your project

Visual Paradigm supports database modeling for multiple DBMS. As you know, different DBMS support different sets of data type. Some of them are compatible with other DBMS, while some are DBMS specific. Before you start, it is important to select Redshift to be your default database so that you can use its data types when designing database. To configure the default database:

  1. Select Tools > DB > Database Configuration from the application toolbar.
  2. In the Database Configuration window, select Redshift from the list of databases on the left hand side.
    Select Redshift database
  3. Provide the JDBC Driver File. You may click here to download the Amazon Redshift JDBC driver.
    JDBC driver specified
  4. Fill in the hostname, port, username and password of your Redshift database. Again, in this tutorial we are using a database named tutorial01.
    Connection settings filled
  5. Click Test Connection to make sure your settings are all correct and Visual Paradigm can connect with your database. Now, we are ready for database design.

Designing your Redshift database with ERD

Let's design a 'bike store' database with ERD.

  1. Create an ERD by selecting Diagram > New from the application toolbar. In the New Diagram window, select Entity Relationship Diagram and click Next.
    New diagram window
  2. Enter BikeShop as diagram name and then click OK to create the diagram.
    Entered diagram name
  3. On the right hand side of the diagram you are prompted to select a model type. Just keep Physical selected. Only entities under physical model will be processed in database exporting.
  4. Select Entity from the diagram toolbar. Then click on the diagram to create an entity. Name it Item and press Enter to confirm editing.
    Created entity
  5. Right click on the Item entity and select New Column from popup menu.
    New column
  6. Enter +ID : int4 and press Enter to create a primary key column ID with int4 a type.
    Creating column
  7. By default a new column will be created upon the confirmation of the previous column. Now enter name : varchar(255) and price : float4 for the next two columns.
    Columns created
  8. Press Esc to stop adding further columns.
  9. An Item may have different styles, i.e. a bike jersey will have different sizes and colors. Let's create an ItemVariant entity from Item entity with one-to-many-relationship. Move the mouse pointer over the Item entity. Press on the Resource Catalog icon at the top right of shape and drag it out.
    Dragging Resource Catalog
  10. Release the mouse button. Select One-to-Many Relationship -> Entity in Resource Catalog.
    Creating one to many
  11. Enter ItemVariant as entity name.
    Item variant created
  12. Right click on ItemVariant entity and select New Column from popup menu, then enter the following columns.
    Column Name Type
    +ID int4
    detail varchar(255)
    color varchar(30)
    size varchar(50)
    qty int4
  13. Repeat the steps above to create the ERD as below.
    Updated ERD
  14. Finally, we have to store the items purchased on each order. We should relate the Order with ItemVariant instead of Item since ItemVariant is the entity storing the actual item. As each Order can have multiple ItemVariant, and each ItemVariant can be involved in multiple Order, therefore it is a many-to-many relationship. Move the mouse pointer over the Order entity. Press on the Resource Catalog icon, drag to ItemVariant and release your button. In Resource Catalog, select Many-to-Many Relationship -> Entity.
    Connect entities
  15. A link entity is created between Order and ItemVariant. Rename it to OrderLine.
    Link entity created
  16. Right click on OrderLine and select New Column from popup menu, then enter qty : int.
    Updated ERD
    Now our ERD is ready and we can start defining the sample data for our database.

Entering sample data

Sample data enables your team to have a basic idea of the kind of data that will be stored. Sample data can also be generated to database in database generation, thus saving your time to prepare the sample in order to trial run your database. To enter sample data for your database design:

  1. Right click on the blank area of your ERD and select Show Table Record Editor or View Editor.
  2. Select the entity Customer in diagram. Now you will see the Table Record Editor listing the columns of the Customer entity.
    Table record editor presented
  3. Enter the following details of the customer into Table Record Editor.
    ID (PK) firstName lastName email address phone
    1 John Doe john.doe@demo-vp.com 1205, river side 12345678
    2 Peter Pan peter.pan@demo-vp.com 306, cox road 87654321
    3 Mary Jane mary.jane@demo-vp.com 52, wolf hill 12358764
  4. Let's move on to the Order entity. Since an Order must be placed by someone, we can pick the Customer record when filling in the sample data for Order. Press the ... button in the FK cell in Table Record Editor.
    Edit FK
  5. This will bring up the sample data we entered for Customer. Choose Peter from the list. The FK value for Peter will be filled in for you in the Order record.
    Select FK
  6. Repeat the above steps to enter the following sample data.
    Order
    ID (PK) date CustomerID (FK)
    1 2016-04-10 17:30:15 2
    2 2016-04-10 18:20:22 1

    Brand
    ID (PK) name
    1 3R
    2 Red Line

    Category
    ID (PK) name
    1 Components
    2 Cloths

    Item
    ID (PK) name price CategoryID (FK) BrandID (FK)
    1 Handle Bar 799 1 1
    2 Head Set 999 1 2
    3 Jersey 299 2 1
    4 Shpes 1599 2 1

    ItemVariant
    ID (PK) detail color size qty ItemID (FK)
    1 full carbon black NA 50 1
    2 NA black NA 40 2
    3 NA pink NA 40 2
    4 short sleeve white M 150 3
    5 short sleeve white L 150 3
    6 short sleeve white XL 50 3
    7 short sleeve white S 100 3
    8 short sleeve blue M 150 3
    9 short sleeve blue L 150 3
    10 short sleeve blue XL 50 3
    11 short sleeve blue 5 80 3
    12 short sleeve blue XS 20 3
    13 road black 39 40 4
    14 road white 39 20 4

    OrderLine
    OrderID (PK) ItemVariantID(PK) qty
    1 1 1
    1 4 1
    2 13 1
    2 9 1
    2 3 1

    Once everything is ready, we can then move on to generating the database.

Generate database

The database design is done. Let's generate a Redshift database from it. To generate database:

  1. Select Tools > DB > Generate Database... from the application toolbar.
  2. Specify the Output Path if you wish to keep the DDL file for your database.
    Specify output path
  3. In the Generate Database field, keep Create Database selected.
  4. Select Export to database to let Visual Paradigm directly execute the DDL script to your database. In practice, if you want to execute changes manually, uncheck this, but for this tutorial, check it first.
    Select export to database
  5. In Generate Sample Data field, select Yes (Without Auto Generated PK).
    Generate sample data
  6. Click OK to proceed. When finished generation, you can check Redshift and you should find the database tables created and with sample records inserted.
    Export result


Trademark Disclaimer

Amazon Redshift is a trademark of Amazon Web Services


Turn every software project into a successful one.