Creating a Database

How You Create a Database

A database can be created using Visual DBA, if available in your environment, or by using Ingres commands and SQL statements.

The process for creating a database is as follows:

1. Create a database.
2. Create base tables for the database.
3. Populate the tables with data.

Note: Examples used in this guide are from the Ingres demonstration database, demodb.

How You Create a Database Using Visual Tools

The Ingres Visual DBA suite of tools is available on Windows.

The process for creating a database using the Ingres visual tools is as follows:

1. Connect to Ingres using Visual DBA
2. Create a database using the Create Database dialog in Visual DBA.
3. Create a table using the Create Table dialog in Visual DBA
4. Load data into the table using the Import Assistant.

Connect to Ingres Using Visual DBA

Before you can create database objects, you must connect to Ingres.

To connect to Ingres using Visual DBA

1. Start Visual DBA (either from Ingres Visual Manager or from the menu accessed by right-clicking the Ingres icon on the notification area of the Windows taskbar).

   The Ingres Visual DBA window is displayed.

2. Expand the Nodes branch in the left pane.

   All your virtual nodes for the Ingres instance are displayed.

3. Right-click the (local) node and select Connect.

   You are connected to the node. The Database Object Manager (DOM) is displayed, which shows all database objects for the node.

Create a Database Using Visual DBA

To create a database using Visual DBA, use the Create Database dialog.

To create a database

1. Select the Databases object category branch in the Database Object Manager window, and then click the Add Object toolbar button .

   The Create Database dialog appears.

   The fields in this sample dialog are filled in as if creating the demodb database.

   

2. Fill in the necessary information (for details, see the online help), and click OK.

   You are notified that the database has been created successfully.

Create a Table Using Visual DBA

After you create a database, you can create its tables, indexes, and other database objects.

To create a base table

1. Expand the Databases object category branch in the Database Object Manager window, and then expand the branch of the database in which the table will be created.
2. Select the Tables object category branch and click the Add Object toolbar button .

   The Create Table dialog appears.

   

3. Enter a unique, valid name for the table in the Table Name field.

   In the sample dialog below, the airport table in the demodb database is being created.

   Note: You can create a table from another table by selecting the Create Table As Select check box. For detailed information about each option, see the Visual DBA online help.

4. Enter a column name in the Name edit control in the column layout box (for example, ap_id) and click Add.

   A column is added to the column layout box.

5. Define the specifications of the column, such as data type, length, and primary key. For example:

   

6. Repeat steps 4 and 5 for each column you want to add to the table (for example, add an ap_name column), and click OK.

   The table is created and appears under the Tables object category branch, as shown in this example:

   

After a table and its columns are defined, you can then populate the table with data from a file. You can also create an index to improve query processing and define a view, or virtual table, to limit access to specific columns.

Populate a Table Using the Import Assistant

You can populate a table with data by importing the data from a file. The Ingres Import Assistant can import data from various file types.

In this example, we use a CSV file located in %II_SYSTEM%\ingres\demo\data\airport.csv.

Note: The airport table in the demodb is already populated. This sample procedure creates a new table called temp, which you can delete (drop) after this exercise.

To import data from a file using the Import Assistant

1. Click the Ingres Import Assistant button on the toolbar in Visual Manager.

   Step 1 of 4 of the Import Assistant is displayed.

2. Use the browse button in the File to be imported control to locate the file.
3. Expand the (local) node in the Into Ingres Table control, and then expand the demodb database. Select the <new table> option and enter temp into the Ingres Table to be Created field. Press Next.

   Step 2 of 3 of the Import Assistant (File Format Identification) is displayed.

   The Import Assistant displays each of its interpretations of the data as a tab.

4. Select the Csv1 tab, which shows that there are five columns of data.

   Check the Tab corresponds to expected results box and press Next.

   Step 3 of 4 of the Import Assistant (Column Properties) is displayed.

   The utility assigns default values to column properties.

5. Double-click the "with null" field in the first column and select "not null" from the drop-down list.

   

   Change the properties of the other columns, as needed.

   Note: When importing into Unicode columns, use the data type char or varchar. Ingres will coerce the values to Unicode using the appropriate Unicode mapping, indicated by the value of the Ingres environment variable II_CHARSET.

   Press Next.

   Step 4 of 4 of the Import Assistant (Import Preview) is displayed.

   Note: If the file was large, you could commit every 1000 rows to avoid filling the transaction log file.

6. Press the Finish Button

   The new table is populated.

   You are asked if you want to import another file.

7. Press No.

   The Import Assistant closes.