Home

The Tables of a Database

 

Tables Fundamentals

 

Introduction

A table is one or more lists of items. Here is an example:

Name Age Gender Relationship
Judie 18 Female Sister
Ernest 24 Male Cousin
Bill 52 Unknown Uncle
David 36 Male Brother
Hermine 12 Unknown Niece

The information of a table is organized in categories called columns.

Creating a Table

The primary formula to create a table is:

CREATE TABLE TableName(Column1, Column2, Column3)

You start with the CREATE TABLE expression followed by its name. After specifying the name of the table, you must list the columns of the table. The list of columns starts with an opening parenthesis "(". The list ends with a closing parenthesis ")".

Creating a Column

Each column uses the following formula:

ColumnName DataType Options

The name of a column:

  • Can start with a letter, a digit, or an underscore
  • Can include letters, digits, and spaces in any combination

In our databases:

  • A name will start with a letter. Examples are n, act, or Second
  • After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are n24 or col_52_t
  • Unless specified otherwise, a name will not include special characters such as !, @, #, $, %, ^, &, or *
  • If the name is a combination of words, each word will start in uppercase. Examples are Date Hired, LastName, Drivers License Number, or EmailAddress

The Type of Value of a Column

After the name of a column, type its data type:

  • Boolean Columns: If you want to create a column that will hold only values as being true or being false, specify its data type as YESNO, BIT, or LOGICAL
  • Characters and Strings: If the fields under a column would be used to hold any type of value, including regular text, specify its as Char, String or Varchar. 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.
    To specify the number of characters, add the parentheses to the data type and, in the parentheses, add a number between 1 and 255
  • Long Strings:. If you want the column to hold longer text, specify its data type as Text, Memo, NOTE, or LONGTEXT. Such a column can hold any type of text, any combination of characters, and symbols, up to 64000 characters
  • Small Integers: 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. 
  • Short Integers: If you want the column to hold larger numbers that can exceed 255, specify its data type as SHORT or INTEGER2
  • Long Integers: If the column will hold small to very large numbers, specify its data type as INT, INTEGER, INTEGER4 or Long
  • Floating-Point Value With Single Precision: If you want to create a column that will hold regular decimal values without regards to precision on its value, specify its data type as Single
  • Floating-Point Value With Double Precision: If the values of a column will require a good level of precision, specify its data type as Double or as Numeric
  • Monetary fields: If you want the values of a column to hold monetary values, specify its data type as Money or Currency
  • Date and Time: If you are creating a column whose values would consist of date, time, or both date and time, specify its data type as DATE or DATETIME
  • 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, specify its data type as 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.

Here is an example:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String
        Dim connection As OleDbConnection = New OleDbConnection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & Server.MapPath("App_Data/exercise1.mdb")
        connection.ConnectionString = strConnection

        Dim CommandToExecute As OleDbCommand = _
            New OleDbCommand("CREATE TABLE Contractors (" & _
                             "DrvLicNbr VarChar(50), " & _
                             "DateIssued DateTime, " & _
                             "DateExpired DateTime, " & _
                             "FullName varchar(120), " & _
                             "Address VARCHAR(120), " & _
                             "City varchar(50), " & _
                             "State varchar(100), " & _
                             "PostalCode varchar(20), " & _
                             "HomePhone varchar(20), " & _
                             "OrganDonor bit);", connection)

        connection.Open()
        CommandToExecute.Connection = connection
        CommandToExecute.ExecuteNonQuery()
        connection.Close()
    End Sub
End Class
 

 

 

Table and Column Maintenance

 

Introduction

Table maintenance consists of reviewing or changing its aspects. This includes reviewing the list of tables of a database, renaming a table, or deleting it.

When making a change on a column, you are also said to alter the table. To programmatically change a column, the SQL starts with the following formula:

ALTER TABLE TableName

When using this statement, the ALTER TABLE expression is required and it is followed by the name of the table.

Column maintenance consists of reviewing or changing any of its aspects. This includes reviewing the structure of columns of a table, renaming a column, deleting a column, changing the data type or the nullity of a column, etc.

Adding a New Column

After a table has already been created, you can still add a new column to it. In SQL, the basic formula to add a new column to an existing table is:

ALTER TABLE TableName
ADD ColumnName Properties

The ColumnName factor is required. In fact, on the right side of the ADD operator, define the column by its name and use all the options we reviewed for columns.

Here is an example:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String
        Dim connection As OleDbConnection = New OleDbConnection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & Server.MapPath("App_Data/exercise1.mdb")
        connection.ConnectionString = strConnection

        Dim CommandToExecute As OleDbCommand = _
            New OleDbCommand("ALTER TABLE Customers " & _
                             "ADD EmailAddress nvarchar(50);", connection)

        connection.Open()
        CommandToExecute.Connection = connection
        CommandToExecute.ExecuteNonQuery()
        connection.Close()
    End Sub
End Class

Deleting a Column

If you have an undesired column that you don't want anymore in a table, you can remove it. To programmatically delete a column, use the following formula:

ALTER TABLE TableName
DROP COLUMN ColumnName

On the right side of the ALTER TABLE expression, type the name of the table. On the right side of the DROP COLUMN expression, enter the name of the undesired column. Here is an example:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String
        Dim connection As OleDbConnection = New OleDbConnection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & Server.MapPath("App_Data/exercise1.mdb")
        connection.ConnectionString = strConnection

        Dim CommandToExecute As OleDbCommand = _
            New OleDbCommand("ALTER TABLE Customers " & _
                             "DROP COLUMN EmaillAddress;", connection)

        connection.Open()
        CommandToExecute.Connection = connection
        CommandToExecute.ExecuteNonQuery()
        connection.Close()
    End Sub
End Class

When this code is executed, the interpreter will look for a column named CurrentResidence in a table named StaffMembers of the current. If it finds that column, it will remove it from the table.

Deleting a Table

If you have an undesired table in a database, you can remove it. To delete a table using SQL, use the following formula:

DROP TABLE TableName

The DROP TABLE expression is required and it is followed by the name of the undesired table. 

 
 
   
 

Home Copyright 2009 FunctionX, Inc.