Creating the Resume Table

One of the functions of the Librarian is to maintain a resume database for Personnel, for potentially qualified staff for jobs as they open up. Therefore one of the tables in the Acme Online Corporate Library System is the RESUME table. This table is created by issuing the CREATE TABLE command.

If you enter the following:

CREATE TABLE  RESUME
       ( RES_ID  CHAR(5),
         RNAME   CHAR(15),
         JOB     CHAR(15),
         EDUC    CHAR(60),
         EXP     VARCHAR(2000)
       );

SQL statements as passed to Texis can be entered on one or more lines. Indenting is recommended to improve readability, but it is not required.

The CREATE TABLE command is entered interactively at a terminal, or as embedded in an application program. Note that the list of column definitions is enclosed in parentheses and that each column definition is separated from the next column definition by a comma. In all examples in this text, each SQL statement is shown in uppercase letters to help you identify what is to be entered. However, in most cases you actually can enter the statement in either upper or lowercase.

The first line in the CREATE TABLE statement identifies the name of the table: RESUME. The next five lines define the five columns that make up the RESUME table. The data types chosen to define each column are explained further on in this chapter.

1. The first column, named RES_ID, stores the resume's identification number (ID). Five characters are allowed for a Resume ID, following Acme internal naming conventions of a letter followed by up to 4 other characters; e.g., `R243' or `R-376'.

2. The second column, named RNAME, stores the name of the resume's job applicant. No name longer than 15 characters can be stored in this column.

3. The third column, named JOB, stores the job or jobs the person is applying for. A maximum of 15 characters is allowed for this column.

4. The fourth column, named EDUC, stores a brief description of the applicant's education. A maximum of 60 characters is allowed for this column. Note: One could choose to define EDUC with VARCHAR rather than CHAR, so that a full educational description could be entered without regard to waste of allocated space.

5. The fifth column, named EXP, stores the full text description of the applicant's job experience as included in the resume. You have two choices for the text field:

   1. You can store the entire description in the Texis table. This is useful for short descriptive lines, for abstracts of one or more paragraphs, or for short reports of one to two pages as depicts the usual resume. Data type would be defined as a variable length character VARCHAR(x) where X indicates the suggested number of characters.

   2. You can store filenames in the Texis table. In this case Texis would use the filename to direct it to the text of the actual file. Data type would be defined as INDIRECT.

   In our EXP text column for the RESUME table we have chosen to store the full text in the Texis table, as concept searches of this column are part of almost every resume search request. If we only occasionally referred to the full text content, we might prefer to store filenames which would point to the full text only when necessary.

Tables defined with the CREATE TABLE command are referred to as base tables. The table definition is automatically stored in a data dictionary referred to as the system catalog. This catalog is made up of various tables that store descriptive and statistical information related to the database. The catalog can be accessed to retrieve information about the contents and structure of the database. The system catalog is discussed in more detail in Chapter here.

As shown in Figure here, the CREATE TABLE command results in an empty table.

RES_ID RNAME            JOB            EDUC         EXP


  (No data is stored in the table at the time it is created.)