Lesson 4: Table Tutorial  Lesson 4: Table Tutorial

Create the database table

Note

To create the database table, you:

Define the columns in the table

  1. Click the Database button in the PowerBar.

    Shown is the Power Bar with the Database button circled.

    The Database painter workspace displays. It contains many views for working with database information. If your workspace does not look like this, you may want to select View>Layouts>(Default).

    Shown is the default Database painter work space. Across the top are menu and power bars, then at left is a system tree. In the middle are Object Layout and Extended Attributes tabs. Object Layout is shown, and to its right is an Object Details area. Across the bottom of is a set of tabs labeled I Sequel Session, Columns, and Results.

  2. Position the pointer on the Tables folder in the EAS Demo DB V125 IM database and select New Table from the pop-up menu.

    Shown is the default Database painter work space. Superimposed over the tables folder in the system tree at left is a pop up menu with options for New Table, Show System Tables, Table Security, Refresh, and Properties.

    The view in which you define the columns in the table displays. The cursor is in the Column Name box for the first column.

    Shown at bottom right is the columns tab in which you define the columns in the table. It has a column for Column Name, a drop down for Data Type, then columns for Width and Dess, and drop downs for Null and Default.

  3. Type id and press Tab.

    The cursor moves to the Data Type box. The default datatype is highlighted. The datatype for the id column needs to be integer.

  4. Click the arrow in the Data Type box and select integer from the drop-down list of available datatypes.

    This changes the datatype.

  5. Press Tab three times.

    This moves you past the setting of No for the column labeled Null. No means you do not want to allow the column you are defining to be empty (to have a null value). Since you always want a value for id, you leave the value No. You also move past the column labeled Default. You are not going to specify a default.

    Shown is the columns tab in which you define the columns in the table. It displays the entries ID in Column Name, integer for Data Type, no for Null, and ( none ) for Default.

  6. Type last_name and press Tab.

    The cursor moves to the Type column. The most recently used datatype (integer) is highlighted. The last_name column should be type char, so you change the datatype.

  7. Type c and press Tab.

    Typing c is a shortcut for choosing char quickly. The cursor moves to the Width column.

  8. Type 15.

    This allows 15 characters for the last name instead of 10.

  9. Press Tab three times.

    This moves you past the setting of No for the column labeled Null to the Column Name box for the next column. No is what you want (name cannot be empty). You move past the column labeled Default again.

    Shown is the columns tab in which you define the columns in the table. Row 1 displays the entries ID in Column Name, integer for Data Type, no for Null, and ( none ) for Default. Row 2 displays the entries last _ name in Column Name, char for Data Type, 15 for Width, no for Null, and ( none ) for Default

  10. Finish entering information about the table columns.

    The following table shows all the values needed to define the columns in the contact table. You have already entered values for the first two columns (id and last_name).

    Name

    Type

    Width

    Dec

    Null

    Default

    id

    integer

    No

    (None)

    last_name

    char

    15

    No

    (None)

    first_name

    char

    15

    No

    (None)

    title

    char

    2

    No

    (None)

    street

    char

    30

    No

    (None)

    city

    char

    20

    No

    (None)

    state

    char

    2

    No

    (None)

    zip

    char

    5

    No

    (None)

    phone

    char

    10

    Yes

    (None)

    fax

    char

    10

    Yes

    (None)

    When you have finished, the Columns view should look like this.

    Shown is the Columns view with entries matching those in the table of values that precedes this illustration.

Save the table

You have now entered all of the column information needed. Now you finish creating the table.

  1. Select File>Save from the menu bar.

    The Create New Table dialog box displays.

    You leave the owner as dba (database administrator) and enter a name for the table.

  2. Type contact1 in the Table Name box.

    Click OK.

    Shown is the Create New Table dialog box. It has a box for Owner Name with the entry d b a and a box for Table Name with the entry contact 1.

    InfoMaker passes the SQL needed to create the table to the SQL Anywhere DBMS, which creates the table.

    InfoMaker displays a representation of the table in the Database painter Object Layout view.

    Shown is a representation of the table in the Database painter Object Layout view. It displays a scrollable list of columns such as i d, last _ name, and first _ name.

    The list of columns displays. The scroll bar on the right side means that there are more columns than can be displayed at once. To see the other columns, you can click the down arrow at the bottom of the scroll bar.

Define a primary key

A primary key uniquely identifies each row. In your contact table, id is the unique identifier of each row of data.

  1. Select Object>Insert>Primary Key from the menu bar. (If the menu item is grayed out, click the Object Layout view to make it current and then select the menu item from the menu bar.)

    The Primary Key properties dialog box displays in the Object Details view. Here you can pick one or more columns as the primary key.

    Shown is the Primary Key property sheet in the Object Details view. It displays the Table name contact 1 at the top, then a scrollable list of columns with check boxes you can select, and below that a box for displaying the Order of the selected columns.

  2. Click id in the list of columns.

    Shown is the Primary Key property sheet in the Object Details view. It displays the Table name contact 1 at the top, then a scrollable list of columns with check boxes you can select, and below that a box for displaying the Order of the selected columns. In this property sheet, the ID column checkbox is selected.

  3. Select File>Save from the menu bar.

    This defines id as the primary key for your table. Notice that the representation of the table now includes a symbol for the primary key of the table.

    Shown is a representation of the table in the Database painter Object Layout view. It displays a scrollable list of columns such as i d, last _ name, and first _ name. Next to the i d column is a symbol designating it as a primary key.

Next you define a comment for the table.

Enter comments to document the table

Although not required, comments can be helpful in identifying tables.

  1. Position the pointer on the title bar of the representation of the contact1 table, display the pop-up menu, and select Properties.

    The Table Properties properties dialog box displays in the Object Details view.

  2. Click in the Comments box and type This is the sample table for the tutorial created by [your name].

    Shown is the General tab of the Table Properties property sheet in the Object Details view. It has a Comments box at the bottom with the sample text " This is the sample table for the tutorial created by Jane Sterling. "

    This associates the comments with the table you are creating.

  3. Select File>Save from the menu bar.




Copyright © 2011. Sybase Inc. All rights reserved. Lesson 4: Table Tutorial

View this document as PDF