Creating a Database and Two Tables

Last modified: October 18, 2023

The following walkthrough describes how to add a database connection, create a database and a table. To complete the walkthrough you must have a MySQL server running.

To get acquainted with dbForge Studio, let us perform some common operations and see how a database and data within it can be managed. In order to interact with MySQL, a client must first establish a connection with the server.

Note

In this topic as well as in the other topics, clicking with the right mouse button is referred to as right-click. Clicking with the left mouse button is referred to as click. Double-click means that you should click quickly with the left mouse button twice.

Prerequisites

In order to complete this walkthrough, you will need access to MySQL 5.0 server version 5.0.30 or higher, or to MySQL server 5.1.13 or higher.

To create database and tables you should have the CREATE privilege on the created database or the global CREATE privilege.

Adding a Database Connection

1. To open the Database Explorer window, click Database Explorer on the View.
2. In the Database Explorer window, click the New Connection button.
3. Set up connection parameters in the dialog window that appears.
4. To navigate through the connection created, click on the plus mark to the left of the tree nodes.

Now, suppose you do not have any databases on this server. You have to create one to continue working and proceed in step-by-step learning of dbForge Studio.

Creating a Database

1. In Database Explorer, right-click the active connection node and select New Database.
2. Input the name of the database: Demobase, and specify database character set and collation.
3. Click the OK button, and, if specified name is unique, the database will appear in Database Explorer.

Creating a database in MySQL is not very difficult as it has only one property: its name. Now to go on you have to create several tables inside this database.

Creating Tables

Defining Table Structure

Suppose you need to manage an organization employees. To keep track of their status, you might want to create two tables, one named Emp with data on every employee and the other named Dept with data on departments that employees work in.

Let table Emp have the following structure:

Column name	Type	Description
EmpNo	INT	Internal employee’s number
EName	VARCHAR (10)	Name of the employee
Job	VARCHAR (9)	Occupation
MGR	INT	Internal number of employee’s manager
HireDate	DATETIME	Date when the employee was hired
Sal	FLOAT	Salary of the employee
Comm	FLOAT	Commission
DeptNo	INT	Internal number of a department where the employee works

Primary index should be created for column EmpNo.

Table Dept may be described as follows:

Column Name	Type	Description
DeptNo	INT	Internal number of the department
DName	VARCHAR (14)	Name of the department
Loc	VARCHAR (14)	Location of the department

Primary index should be created for column “DeptNo”.

Creating the Tables

1. In Database Explorer, navigate to database you want to create table in and expand it. In our example it should be Demobase.
2. Right-click the Tables node and choose New Table on the shortcut menu.
3. In the opened dialog, type the name of the table (it should be Emp) and click Create.

Table editor appears with a blank document in it.

1. Right-click in the table editor (the General tab must be active), and choose New Column… on the shortcut menu or press the INSERT key. The Column Properties dialog box appears.
2. Type in column name: EmpNo.
3. Select the Primary option below.
4. Set type for the column to INT.
5. Click OK to create the column.
6. Repeat steps 4, 5, 7 and 8 to set up all other columns with their respective options.
7. After you have finished with the table, click the Save button to confirm changes made to the table.

Note

Length of fields with variable size can be changed in the field “Length” in the middle-right of the column editor.

Adding an Index to the Table

1. Open the table Emp from Database Explorer using double-click if it is not open yet.
2. Switch to the Indexes tab.
3. Right-click the document, and choose New Index on the shortcut menu or press the INSERT key. The Index Properties dialog box appears.
4. In the dialog box, type in a name for the index.
5. Set it to be unique by checking an appropriate check box.
6. There are two lists in the dialog: Index columns and Available columns. In the Available columns list, select field EmpNo and click the Add Field button.

Field EmpNo is removed from the original list and added to the Index columns list.

1. Click OK to create an index.
2. Click the Save button to confirm changes made to the table.

Now, the first table is ready. Repeat these steps for table Dept to create it.