When studying data sets, we defined a database as one or more
lists. A list in a database is called a table. The idea is that a table is an
arrangement of the categories of information stored in a list and a table makes
it easy to locate and manage the records of a list. To better explore lists, you
should know how a table organizes its value.

A table is made of one or more categories divided as
columns. Consider the following example of a list of teachers of a high school:

Last Name

First Name

Main Subject

Alternate Subject

Pastore

Albert

Math

Physics

Andong

Gertrude

Chemistry

Static

Missiano

Helena

Physical Ed

Jones

Celestine

Comp Sciences

Math

Notice that the first names are grouped in a common
category, so are the last names and so on. This makes it easy to locate a
category and possibly a value.

Table Creation

To create a table, you start an expression with CREATE TABLE
followed by the name of the table:

CREATE TABLE Name;

The CREATE and TABLE keywords must be used to create a table. The Name factor specifies the name of the new
table.

The Name of a Table

After the CREATE TABLE expression, you must enter a
name for the table. The name of a table can be very flexible. This flexibility can
be overwhelming and confusing. To
avoid these, there are suggestions and conventions we will apply when naming our
tables:

The name of a table will start with a letter. In most cases, the name will
start in uppercase

Because we believe that a table represents a list of items, its name will
be in plural. Examples are Students, Employees, Products

When a name is a combination of words, each part will start in uppercase.
Examples are Student Names or Sport Activities

In most cases, we will avoid including space in a name; but if we do, the
name of a table will be included between [ and ]

Besides the CREATE TABLE expression followed by a
name, there are other issues related to creating a table. We will review more
details in future lessons.

Table Maintenance

The Tables Collection

The tables of an ADO database are stored
in a collection represented in the Connection class by the Tables
property. To locate this collection, you can access the Tables property
of the Catalog class of the ADOX namespace.

Deleting a Table

To remove a table from a database, create a DROP
TABLE expression followed by the name of the table. The formula to
use is:

DROP TABLE TableName;

Replace the TableName factor of our formula
with the name of the table you want to delete. Here is an example:

We saw that a table was used to
organize the values of a list by using categories of information. Here is an
example:

Last Name

First Name

Main Class

Years of Experience

Pastore

Albert

Math

4

Andong

Gertrude

Chemistry

8

Missiano

Helena

Physical Ed

5

Jones

Celestine

Comp Sciences

10

With this type of arrangement, each column holds a
particular category of information. A table must have at least one column. This
means that, to create a table using the CREATE TABLETableName
formula, you must specify a (at least one) column.

Column Creation

To create the columns of a table, on the right side of the
name, type an opening and a closing parentheses. In the parentheses of the CREATE TABLETableName() expression, the formula of creating a column is:

ColumnNameDataTypeOptions

Notice that there is only space that separates the sections
of the formula. This formula is for creating one column. If you want the table
to have more than one column, follow this formula as many times as possible but
separate the sections with colons. This would be done as follows:

In the next sections, we will review the factors of this
formula. To create a
table in ADO, you can pass the whole statement to the Execute() method of the
Connection class.

Characteristics of a Column

Introduction

Like a table of a database, a column must have a name. As mentioned for a table,
the name of a column is very flexible. Because of this, we
will adopt the same types of naming conventions we reviewed for tables:

The name of a column will start with a letter. In most cases, the name
will start in uppercase

When a name is a combination of words, each part will start in uppercase.
Examples are First Name or Date Hired

In most cases, we will avoid including space in a name but if we do, the
name of the column will be included between [ and ]

When creating the table, set the name of
the column in the ColumnName placeholder of our formula. Here is an example:

CREATE TABLE Students(FullName, DataTypeOptions)

Notice that the name of the column is not included in quotes.

The Types of Columns

To exercise a good level of control over the values that can
be entered or stored in a database, you can configure each column to allow
some types of value and/or to exclude some other types. This is done by
specifying an appropriate type of data for the column. To specify the data type of a
column, pass the
name of the data type as the second factor of the column.

Text-Based Columns: If the fields under a column would be used to hold any
type of value, including regular text, such a column is treated as
string-based. There are various data types you can apply to such a column.
You can specify the data type as Char, Text, String or Varchar.
Here are examples of three columns created with these types:

Each one of the char, string, or varchar
data types would produce the same effect. A column with the string,
the char, or the varchar data
type allows any type of value made of any character up to 255 symbols. If
you want the column to hold longer text, specify its data type as Memo, NOTE, or LONGTEXT. Such a column can hold any
type of text, any combination of characters, and symbols, up to 64000 characters.
Here are examples:

Byte and Integer1: If you want a column to hold
natural numbers, you can specify its data type as Byte or Integer1.
This is suited for a column that will hold small numeric values
not to exceed 255. Here are examples:

Binary: The binary data type can let a
column accept any type of data but it is equipped to interpret the value.
For example, it can be used to receive hexadecimal numbers.
To specify this when creating a column, set its data type to either
BINARY or VARBINARY.

Image:
If you are creating a column that will hold external
documents, such as pictures, formatted (from Microsoft Word for example),
or spreadsheet, etc, specify its data type to one of the following:
IMAGE, OLEOBJECT, LONGBINARY, or GENERAL.

Column Maintenance

Introduction

Column maintenance consists of adding a new column or deleting an
existing column. Because the columns belong to a table, their maintenance is
related to it. To perform this maintenance, you start with the ALTER TABLE
expression followed by the name of the table.

Adding a New Column

After a table with one or more columns has been
created, you can add a new column to it. To add a new column, after the ALTER
TABLE statement and the name of the table, include an ADD COLUMN expression using the following
formula:

ALTER TABLE TableName
ADD COLUMN ColumnNameDataType

The ColumnName factor must be a valid name for
the new column and you must follow the rules for naming columns. The data
type must be one of those we reviewed. Here is an example that adds a new
string-based column named CellPhone to a table named Employees:

This creates a Microsoft JET database named People, followed
by a table named Persons equipped with three columns.

After creating a table and its column(s),
you can populate the
database with data. Data entry consists of filling a table with the necessary
values. A series of values that corresponds to same levels of columns is
called a row or a record.

New Record Creation

Before performing data entry on a table, you must know
how the table is structured, the sequence of its columns, the type
of data that each column is made of. To enter data in a table, you start with the INSERT
combined with the VALUES keywords. The statement uses the
following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n)

Alternatively, or to be more precise, you can
specify that you are entering data in the table using the INTO
keyword between the INSERT keyword and the TableName
factor. This is done with the following syntax:

INSERT INTOTableNameVALUES(Column1, Column2, Column_n)

The TableName factor must be a valid name
of an existing table in the currently selected database. If the name
is wrong, the SQL interpreter would simply consider that the table
you are referring to doesn't exist. Consequently, you would receive
an error.

The VALUES keyword indicates that you are
ready to list the values of the columns. The values of the columns
must be included in parentheses. Specify the value of each column in
the parentheses that follow the VALUES keyword:

Boolean Values: If the column is Boolean-based, you
must specify its value as 0 or 1.

Numeric Values: If the column is a numeric
type and if the number is an integer, you
should provide a valid natural number without the decimal separator.
If the column is for a decimal number, you can type the value with
its character separator (the period for US English).

Character and String Values: If the data type of a column is a string type,
you should include its value between double-quotes. For example, a
shelf number can be specified as 'HHR-604' and a middle initial can
be given as 'D'.

Date and Time Values: If the column was created for a date or a time data
type, you should/must use an appropriate formula with the year
represented by 2 or 4 digits. You should also include the date in
single-quotes. If you want to specify the year with 2 digits, use
the formula:

'yy-mm-dd'

Or

'yy/mm/dd'

You can use the dash symbol "-" or the
forward slash "/" as the date separator. An alternative to representing a year is with 4
digits. In this case, you would use the formulas:

'yyyy-mm-dd'

Or

'yyyy/mm/dd'

The year with 4 digits is more precise as it
properly expresses a complete year.

A month from January to September can be
represented as 1, 2, 3, 4, 5, 6, 7, 8, or 9. Day numbers follow the
same logic.

Adjacent Data entry

The most common technique of performing data entry
requires that you know the sequence of columns of the table in which you
want to enter data. With this subsequent list in mind, enter the value of
each field in its correct position.

During data entry on adjacent fields, if you don't
have a value for a numeric field, you should type 0 as its value. For a
string field whose data you don't have and cannot provide, type two
single-quotes to specify an empty field. Here is an example:

The adjacent data entry requires
that you know the position of each column. The SQL provides an alternative
that allows you to perform data entry using the name of a column instead
of its position. This allows you to provide the values of fields in any
order of your choice.

To perform data entry at random, you must provide a
list of the columns of the table in the order of your choice. You can
either use all columns or provide a list of the same columns but in your
own order. Here is an example:

You don't have to provide data for all columns,
just those you want, in the order you want. To do this, enter the names of
the desired columns on the right side of the name of the table, in
parentheses. The syntax used would be:

When performing data entry, you can
expect the user to skip any column whose value is not available and move
to the next. In some cases, you may require that the value of a column be
specified before the user can move on. If you are creating a column and if you want to let the
user add or not add a value for the column, type the NULL keyword
on the right side of the data type. If you want to require a value for the
column, type NOT NULL. Here are examples:

In this case, when performing data entry, the user
must always provide a value for the VideoTitle column in order to create a
record. If you omit to specify the nullity of a field, it is assumed NULL;
that's the case for the YearReleased column of the above Videos table.

Auto-Increment

When we study relationships, we will see that, on a
table, each record should be uniquely identified. This should be the case
even if many records seem to have the same values for each column. We saw
already that you can require that the user provide a value for each record
of a certain column. In some cases, the user may not have the right value
for a column but at the time, the record would need to be created, even if
it is temporary. To solve this type of problem and many others, you can
create a column that provides its own value. On the other hand, to create
a special column that can be used to uniquely identify each record, you
can apply an integer data type to it but ask the database engine to automatically
provide a numeric value for the column.

If you are creating the column, you can specify its data type
as either COUNTER or AUTOINCREMENT. Only one column of a table
can have one of these data types. Here is an example:

By default, when you apply the COUNTER or the
AUTOINCREMENT data type, when the user creates the first record, the field
int the auto-incrementing column receives a number of 1. If the user
creates a second record, the auto-incrementing value receives a number of
2, and so on. If you want, you can make the first record receive a number
other than 1. You can also make it increment to a value other than 1. To
apply this feature, the COUNTER and the AUTOINCREMENT types use a seed as
their parentheses:

COUNTER(x,y)

or

AUTOINCREMENT(x,y)

The x value represents the starting value of the records. The
y value
specifies how much would be added to a value to get the next.

Fields Sizes

When reviewing the data types available for columns, we saw that
some of them could use a string-based data type, namely TEXT, CHAR,
or VARCHAR. By default, if you create a table and you set a column's
data type to TEXT, CHAR, or VARCHAR, it is made to hold 255 characters.
If you want, you can control the maximum number of characters that would
be allowed in a column during data entry.

To specify
the number of characters of the string-based column, add the parentheses
to the TEXT, the CHAR, or the VARCHAR data types, and in the parentheses,
enter the desired number. Here are examples:

A default value allows a column to use a value that is
supposed to be common to most cells of a particular column. The default
value can be set as a constant value or it can use a function that would
adapt to the time the value is needed.

To specify a default value, after the name and the
data type of a column, type DEFAULT and assign it the desired value, based
on the data type. Here is an example: