Visual Paradigm logo
Jump to Menu

Design and Generate SQL Server Database

You can design database with ERD, and construct database by generating from the resulting ERD. In this tutorial, you will draw a simple ERD of an online bike shop, define sample data for your database and generate database to Microsoft SQL Server from it. Visual Paradigm and SQL Server 2014 will be used in this tutorial.

Compatible edition(s): Enterprise, Professional, Standard

  • June 19, 2015
  • Views: 37,807
  • PDF

Create database in SQL Server

The first thing we need to do is ready brand new database in SQL Server. To create a new database in SQL Server:

  1. Open the SQL Server Management Studio.
    01 launch sql management studio
  2. Login to your SQL Server.
    02 login to sql server
  3. Right click on Databases root node in Object Explorer, and select New Database.
    03 create new db
  4. Name the database as BikeShopDB, and press OK to create the database.
    04 name the db

Once the database was created, we can then prepare our Visual Paradigm software for model your database.

Configure default database for your project

Visual Paradigm supports database modeling for multiple DBMS. Since each database do have their own data type which may not compatible with other database, it is important to specify the SQL Server as our default database before we start modeling. The data type for SQL Server will available in our ER diagram once we specified it as our default database. To configure the default database in Visual Paradigm:

  1. Go to Tools > DB > Database Configuration.
    05 configure default db
  2. Select MS SQL Server in the database list.
    06 select sql server
  3. Select 2008 or higher in Version field (as we are using SQL Server 2014).
    07 select 2008 up
  4. In this tutorial we will use the jDTS driver for establish connection with our database. Press the green arrow button next to Driver file field to let VP download the driver for you.
    08 download driver
    If you wish to use other driver you can then simply select the appropriate driver in the Driver field, and press the ... button to locate your driver file from your local file system.
  5. Fill in the hostname or IP address of the machine which hosting your SQL Server in the Hostname field. And fill in the post number of your SQL Server instance in the Port number field. After that enter BikeShopDB in the Database name field, as well as your name and password for accessing the database.
    11 specify connection details
  6. When finish enter the connection details, press Test Connection button to check everything alright.
    12 test connection
    Now we are ready to start model our database in ERD.

Model your database with ERD

Let's start model our bike store database with ERD.

  1. Go to Diagram > New to open the New Diagram dialog.
    15 new diagram
  2. Enter erd in the search box to locate the Entity Relationship Diagram. Press Next to proceed.
    16 new diagram dialog
  3. Name the ERD as BikeShop, and press OK to create the blank ER diagram.
    17 new diagram dialog2
  4. Select Entity from the diagram toolbar. Then click on the diagram to create an entity. Name it as Item and press Enter to confirm editing.
    19 create entity
  5. Right click on the Item entity and select New Column from popup menu.
    21 new column
  6. Enter +ID : int and press Enter to create a primary key column ID with type as int.
    22 define column
  7. By default a new column will be created and in editing mode upon you confirm the previous column. Now enter name : varchar(255) and price : float(2) for the next 2 columns.
    23 entity
  8. Press Esc key on keyboard to stop editing of the Item entity.
  9. Every Item may contains various variations, i.e. a bike jersey will have different size and color. Let's create ItemVariant entity from Item entity with one-to-many-relationship. Move the mouse pointer over the Item entity. Press on One-to-Many Relationship -> Entity and drag it out.
    24 create 1m from item
  10. Release the mouse button and name it as ItemVariant.
    25 create item variant
  11. Right click on ItemVariant entity and select New Column from popup menu, then enter the following columns.
    Column Name Type
    +ID int
    detail varchar(255)
    color varchar(30)
    size varchar(50)
    qty int
  12. Repeat the steps above to create the ERD as below.
    27 partial erd
  13. Finally we have to store the items purchased on each order. We should relate the Order with ItemVariant instead of Item since the ItemVariant is the entity storing the actual item. As each Order could have multiple ItemVariant, and each ItemVariant could be involved in multiple Order, therefore it should be created as many-to-many relationship. Move the mouse pointer over the Order entity. Press on the Many-to-Many Relationship -> Entity and drag it out, then drop it on ItemVariant30 create mn relationship
  14. Now a link entity between Order and ItemVariant is being created. Rename the link entity to OrderLine.
    31 rename orderline
  15. Right click on OrderLine and select New Column from popup menu, then enter qty : int.
    32 order line

Now our ERD is ready and we can start define the sample data for our database.

Define sample data

Defining sample data for your ERD will helps you have more understanding about how your database works. The sample data will also being generated into database thus saving your time to prepare the sample in order to trial run your database. To define sample data for your ERD:

  1. Right click on the blank area of your ERD and select Show Table Record Editor or View Editor.
    33 open table record editor
  2. Select the entity Customer in diagram. Now you will see the Table Record Editor showing the columns of the Customer entity.
    34 open table record editor
  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 the Order must be ordered by someone, we can pick the Customer record when fill in the sample data for Order. Press the ... button in the FK cell in Table Record Editor.
    35 select value
  5. This will bring up the sample data we defined for Customer. Choose Peter form the list and FK value for Peter fill in for you in the Order record.
    36 select from list
  6. Repeat the above steps to define the following sample data.
    Order
    ID (PK) datetime CustomerID (FK)
    1 2015-06-02 17:30:15 2
    2 2015-06-02 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 generate the database.

Generate database

Now everything is ready, let's generate it into database. To generate database:

  1. Go to Tools > DB > Generate Database...
    37 gen db
  2. Select Database only in Generate.
    38 gen db only
  3. Specify the Output Path if you wish to keep the DDL file for your database.
    39 ddl path
  4. In Generate Database field, select Create Database.
    41 select gen db
  5. Select Export to database to let Visual Paradigm directly execute the DDL script to your database.
    42 export to db
  6. In Generate Sample Data field, select Yes (With Auto Generated PK).
    43 gen sample
  7. Press OK to proceed.
    44 proceed to gen db

If everything is OK then we will see the progress dialog showing 100% complete.


45 gen finish

Now let's go to SQL Server Management Studio to review our database.


46 tables created

Let's try to see what's inside the ItemVariant table. Right click on it and choose Select Top 1000 Rows from popup menu.


47 show records

And you can see the sample data are there.


48 records showing


Turn every software project into a successful one.