EXERCISE - 5

Objectives:

1. Explore how changes can be made to the internal schema of an Ingres database;
2. Find out how to unload database tables into back-up ASCII files;
3. Find out how to load data from a back-up ASCII file of a suitable format into a table.

You may find that during this exercise you lose the 'Publisher' table. Remember to have an up-to-date copy of your database Tables in ASCII form. If you lose the 'Publisher data you can then restore your database.

1. Copy the Publisher Table data INTO a suitably named ASCII file.
   1. Go into the Queries, SQL function under Ingres/Menu.
   2. Invoke the Edit option.
   3. Paste in the following command, using the left mouse button to select and the middle mouse button to copy:
      COPY TABLE publisher (<column-defs>)
      INTO 'publisher.dat'
      Example.

      Now open the 'tabledefs.sql' file using a different ved editor.

      i.e.Go to a unix command line and issue the unix command ved tabledefs.sql &

      Replace the <column-defs> string with the column definitions you used to create the table.
      Example.

      Change the scape between the column name and datatype to space 'equals' space & add the text WITH NULL(<value>) before the comma for each column.
      Example.

      Replace the <value> string with appropriate substitute values for nulls (do this even though you may have specified NOT NULLs for the columns).

      This tells Ingres to replace null values with the specified values. Note: If the column width was narrower that the null substitution string you specified you would need to specify a larger column size in the COPY command to fit the null substitue. This would not affect your original table definitions.
      Example.

      Go f(8) the COPY command. This will copy the information in the publisher table to the specified file, using the specified null substitutions where appropriate.

      Save the COPY TABLE command in a file.

      Use the File, WriteFile option to specify a file to save the command in.
2. You now need to drop(delete) the Publisher table and then create a new version of it that contains an attribute that will hold the contact name (i.e. contact column for each publisher. Remember to Modify this new table to the required storage structure, and to recreate any necessary Secondary indexes.
   1. In the Queries, SQL function under Ingres/Menu enter the command DROP TABLE publisher and Go f(8) it.
   2. Modify your CREATE TABLE publisher command to include a new column called contact_name.
      Example.
   3. Re-run the CREATE TABLE command to re-create the table according to the new definition.
3. Use the COPY command to populate your 'new' Publisher table FROM the data in your ASCII file. (Hint - remember that the table structure has changed !).
   1. Invoke the Edit option.
   2. Reload the COPY TABLE command you used earlier.
   3. Change the keyword INTO to FROM.
   4. Go f(8) the COPY command. This will copy the information in the the specified file to the publisher table, using the specified null substitutions where appropriate. All contact_name values will be null.
      We could have given a default value for contact_name by specifying a value for contact_name in a WITH NULL clause.

      Save the COPY TABLE command in a file.

      Use the File, WriteFile option to specify a file to save the command in.
4. The PUBLISHER table will now contain NULL values for the contact_name column. You must now update this in every row to some suitable value. This can be done through the QBF default Form. If you have created a customised Form in VIFRED for this table, then it will have to be edited to include the new attribute.