Home

The Tables of a Database

 

Table Creation

 

Introduction

In the first lesson, 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 named 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.

Here is an example:

System::Void btnCreateTable_Click(System::Object^ sender, System::EventArgs^ e)
{
	 ADODB::ConnectionClass ^ conADO = gcnew ADODB::ConnectionClass;
	 String ^ strCreate; 

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
	          L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
	 strCreate = L"CREATE TABLE Students . . .";
	 conADO->Close();
}

After formulating the expression that creates the table, you can pass it to the Execute() method of a Connection variable. This would be done as follows:

System::Void btnCreateTable_Click(System::Object^ sender, System::EventArgs^ e)
{
	 ADODB::ConnectionClass ^ conADO = gcnew ADODB::ConnectionClass;
	 String ^ strCreate; 
	 Object ^obj = gcnew Object;

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
	          L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
	 strCreate = L"CREATE TABLE Students . . .";
	 conADO->Execute(strCreate, obj, 0);
	 conADO->Close();
}

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. 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:

System::Void btnDeleteTable_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::Connection ^ conADO = gcnew ADODB::Connection;
	 Object ^obj = gcnew Object;

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
                    L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
	 conADO->Execute(L"DROP TABLE Persons;", obj, 0);
 MessageBox::Show(L"The Persons table of the Exercise database has been deleted.");
	 conADO->Close();
}

 

 
 

Previous Copyright © 2005-2012 FunctionX, Inc. Next