How to Create a Database Diagram from a DDL

A database diagram, aka ER diagram, is a certain type of flowchart used in database design. It describes the entities and how they relate to one another. Vertabelo lets you create an ER diagram for your database by importing a DDL file. Read on to find out more.

This article introduces what a DDL file is and why ER diagrams are an essential tool in the database development process. In the end, I’ll show you how to import a DDL file into Vertabelo to get an ER diagram of your database.

Vertabelo is a database modeling tool. If you are new to Vertabelo, we explain how it organizes the documents and models here. Also, you can have a look at this article to learn what an ER diagram is.

Let’s get started.

What Are DDL Files?

All SQL commands are grouped into one of five categories:

• DDL stands for Data Definition Language.
• DML stands for Data Manipulation Language.
• DQL stands for Data Query Language.
• DCL stands for Data Control Language.
• TCL stands for Transaction Control Language.

Here, we focus on the DDL part of SQL, which includes such commands as CREATE, DROP, ALTER, TRUNCATE, RENAME, and COMMENT.

A DDL file contains SQL DDL code that creates, removes, or alters database tables, views, constraints, or other objects.

Creating a DDL File

Let’s create a DDL file. We’ll design a database for the local zoo to keep track of all the animals, their caretakers, and the food that the animals eat.

We’ll start by creating tables that store all the zoo animals, employees, food types, food providers, etc. Here is the SQL DDL code for each table.

The Animal_Species table stores all the animal species present in the zoo.

CREATE TABLE Animal_Species (
    Species_Id int NOT NULL,
    Species_Name varchar(30) NOT NULL,
    CONSTRAINT Animal_Species_pk PRIMARY KEY (Species_Id)
);

The Food_Provider table stores all the food providers that supply the zoo with various food types.

CREATE TABLE Food_Provider (
    Food_Provider_Id int NOT NULL,
    Food_Provider_Name varchar(50) NOT NULL,
    Food_Provider_Email varchar(70) NOT NULL,
    CONSTRAINT Food_Provider_pk PRIMARY KEY (Food_Provider_Id)
);

The Food_Type table stores all the food types that the zoo animals eat.

CREATE TABLE Food_Type (
    Food_Type_Id int NOT NULL,
    Food_Type_Name varchar(30) NOT NULL,
    CONSTRAINT Food_Type_pk PRIMARY KEY (Food_Type_Id)
);

The Food_Type_Provider table stores information on which food provider supplies what types of food.

CREATE TABLE Food_Type_Provider (
    Food_Type_Id int NOT NULL,
    Food_Provider_Id int NOT NULL,
    CONSTRAINT Food_Type_Provider_pk PRIMARY KEY (Food_Type_Id,Food_Provider_Id)
);

The Zoo_Animals table stores records for all the animals in the zoo.

CREATE TABLE Zoo_Animals (
    Animal_Id int NOT NULL,
    Caretaker_Id int NOT NULL,
    Species_Id int NOT NULL,
    Food_Type_Id int NOT NULL,
    Birth_Date date NOT NULL,
    CONSTRAINT Zoo_Animals_pk PRIMARY KEY (Animal_Id)
);

The Zoo_Employees table stores records for all zoo employees.

CREATE TABLE Zoo_Employees (
    Employee_Id int NOT NULL,
    First_Name varchar(30) NOT NULL,
    Last_Name varchar(50) NOT NULL,
    Email varchar(70) NOT NULL,
    Phone varchar(15) NOT NULL,
    Position varchar(20) NOT NULL,
    Salary decimal(10,2) NOT NULL,
    CONSTRAINT Zoo_Employees_pk PRIMARY KEY (Employee_Id)
);

Now, let’s create the references between the tables, i.e. the foreign keys.

The Food_Type_Provider_Food_Provider constraint supplies the ID of a food provider to the Food_Type_Provider table and assigns the food provider to the food type:

ALTER TABLE Food_Type_Provider ADD CONSTRAINT Food_Type_Provider_Food_Provider
    FOREIGN KEY (Food_Provider_Id)
    REFERENCES Food_Provider (Food_Provider_Id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE;

The Food_Type_Provider_Food_Type constraint supplies the ID of a food type to the Food_Type_Provider table and also links the food provider to the food type.

ALTER TABLE Food_Type_Provider ADD CONSTRAINT Food_Type_Provider_Food_Type
    FOREIGN KEY (Food_Type_Id)
    REFERENCES Food_Type (Food_Type_Id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE;

The Zoo_Animals_Animal_Species constraint assigns a species to each animal:

ALTER TABLE Zoo_Animals ADD CONSTRAINT Zoo_Animals_Animal_Species
    FOREIGN KEY (Species_Id)
    REFERENCES Animal_Species (Species_Id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE;

The Zoo_Animals_Food_Type constraint assigns a food type to each animal:

ALTER TABLE Zoo_Animals ADD CONSTRAINT Zoo_Animals_Food_Type
    FOREIGN KEY (Food_Type_Id)
    REFERENCES Food_Type (Food_Type_Id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE;

The Zoo_Animals_Zoo_Employees constraint assigns a caretaker to each animal:

ALTER TABLE Zoo_Animals ADD CONSTRAINT Zoo_Animals_Zoo_Employees
    FOREIGN KEY (Caretaker_Id)
    REFERENCES Zoo_Employees (Employee_Id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE;

Now, we are ready to import the DDL file of our zoo database into Vertabelo. This will help us get the ER diagram.

How to Create a Database Diagram from a DDL in Vertabelo

First, we need to create a physical data model in Vertabelo. The following steps guide you through the process.

Step 1. Create a new document.

Step 2. Create a physical data model.

You can  find out more about other document types available in Vertabelo.

Step 3. Name your database and choose a database engine. Also, choose the content type From SQL and upload your DDL file.

Step 4. Press the Import SQL button and wait until the Successfully imported message shows up.

If your import failed, check out this article to learn how to troubleshoot DDL imports in Vertabelo.

Step 5. Press the Start Modeling button to see your ER diagram.

Note: The entity relationship diagram presents the structure of your database. All the tables and connections among them are visualized in this document. Such diagrams are used in database design and development, as they provide a detailed blueprint of the database. To learn more about ER diagrams and data modeling, check out this article on data modeling basics.

Now, you might need to adjust the link multiplicities after importing your DDL file. By default, these multiplicities have a value of either one or zero or more, as shown above.

Let’s look at the ER diagram of our zoo database after making the adjustments. Don’t hesitate to manipulate the placement of tables to achieve the best visualization of your database!

Getting  an ERD from a DDL or a DDL from an ERD

It works both ways! You can either prepare a SQL DDL code and create an ER diagram from it or prepare an ER diagram and generate a SQL DDL code.

Make sure to practice both the options using Vertabelo.

Good luck!