14 June 2022

How to Create a Database Diagram from a DDL

Using a SQL DDL code to create your database? You can also use it to get an ER diagram. This article shows how Redgate Data Modeler does it for you.

Guest post

This is a guest post from Redgate Data Modeler.

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. Redgate Data Modeler 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 Redgate Data Modeler to get an ER diagram of your database.

Redgate Data Modeler is a database modeling tool. If you are new to Redgate Data Modeler, 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 Redgate Data Modeler. This will help us get the ER diagram.

How to Create a Database Diagram from a DDL in Redgate Data Modeler

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

Step 1. Create a new document.