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 Ingres Commands

The process for creating a database using Ingres commands and SQL statements is as follows:

1. Create a database using the createdb command.
2. Create tables for the database using the CREATE TABLE statement.
3. Populate the tables with data using the COPY statement.

Create a Database Using Createdb Command

Use the createdb command to create a database. The user who creates a database becomes its database administrator. For a complete description of the createdb command, see the Command Reference Guide.

Note: You must have the createdb privilege to create a database. For details, see the Database Administrator Guide.

To create the demodb database using the createdb command

Issue the following command:

createdb -i demodb

This command creates a public database named demodb on the local node in the default locations. The database is Unicode-enabled using Normalization Form C.

Create a Table Using the CREATE TABLE Statement

The CREATE TABLE statement creates a new base table that is owned by the user issuing the statement. The CREATE TABLE statement creates entries in the system catalogs for the table created.

You can create tables using:

• The Ingres/Terminal Monitor
• Interactive SQL
• An Embedded SQL program
• Ingres/Applications-By-Forms (ABF) and Ingres/4GL (referred to as ABF/4GL)
• Tables utility

For a complete description of the CREATE TABLE statement, refer to the SQL Reference Guide.

To create the airport table using the CREATE TABLE statement

The following statement creates the airport table with columns ap_id, ap_iatacode, ap_place, ap_name, and ap_ccode:

CREATE TABLE airport(

       ap_id integer not null not default,

       ap_iatacode nchar(3) not null not default,

       ap_place nvarchar(30),

       ap_name nvarchar(50),

       ap_ccode nchar(2)

);

Populate a Table Using the COPY Statement

The SQL statement COPY can be used to copy the contents of a file into a table.

Note: The input file for the following COPY statement is a binary file. To create this file, use this statement:

COPY TABLE airport () INTO 'airport.in';

To load the airport table from a binary file

Issue the following statement:

COPY TABLE airport (

    ap_id=integer,

    ap_iatacode=nchar(3) ,

    ap_place=nvarchar(30),

    ap_name=nvarchar(50),

    ap_ccode=nchar(2))

FROM 'airport.in';

To load the airport table from a CVS file

Alternatively, if the data file is in comma-separated value (.csv) format, you must specify the character types and the delimiter on the COPY statement. This method allows more flexibility and lets you skip fields. The COPY statement would look like this:

COPY airport (

    ap_id=char(0) comma,

    ap_iatacode=char(0) comma,

    ap_place=char(0) comma,

    ap_name=char(0) comma,

    ap_ccode=char(0) nl)

FROM 'airport.csv';

For more information on the COPY statement, see the SQL Reference Guide and the Database Administrator Guide.