Introduction to the Tables of a Database

Tables Fundamentals

Starting a Table

As we saw when studying data sets, a table is one or more lists of items. If the table is only one list of items, these items are identical in nature. Here is an example:

If the table is made of more than one list, each list is made in a distinguishable category. Here is an example:

Based on this, a list is simply an arrangement of information and this information, also called data, is stored in tables.

To visually create a table in the Server Explorer (Microsoft Visual Studio 2013 Professional or Ultimate) or in the Database Explorer (Microsoft Visual Studio Express 2013), expand the connection, right-click Tables and click Add New Table. If you are writing SQL code, to create a table, you start with the following statement:

CREATE TABLE TableName;

The CREATE TABLE expression is required. The TableName factor specifies the name of the new table. The TableName can use the rules and suggestions we reviewed for the tables.

To complete the creation of a table, you must give it a name. The name of a table:

• Can be made of digits only. For example you can have a table named 148
• Can start with a digit, a letter, or an underscore
• Can be made of letters, digits, and spaces

Besides these rules, you can make up yours. To avoid confusion, here are the rules we will use to name our tables:

• A name will start with a letter. Examples are act or Second
• After the first character, the name will have combinations of underscores, letters, and digits. Examples are _n24, act_52_t
• Unless stated 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 Staff Members or Video Titles

To get the list of tables of a database, execute sp_help (it is a stored procedure). Here is an example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Exercise
{
    public partial class Exercise : Form
    {
	public Exercise()
	{
	    InitializeComponent();
    	}

    	private void Exercise_Load(object sender, EventArgs e)
    	{
	    using (SqlConnection connection =
		new SqlConnection("Data Source=(local);" +
			"Database='AltairRealtors1';" +
			"Integrated Security=yes;"))
	    {
	    	SqlCommand command =
			new SqlCommand("sp_help", connection);
	        connection.Open();
	    	SqlDataReader rdr = command.ExecuteReader();

	    	while (rdr.Read())
	    	{
		    lbxTables.Items.Add(rdr[0].ToString());
	    	}

	        rdr.Close();
	    }
    	}
    }
}

To specify the schema of a table using code, precede its name with the name of the schema followed by a period. The formula to use is:

CREATE TABLE SchemaName.TableName....

An example would be:

CREATE SCHEMA Registration;
GO
CREATE TABLE Registration.Students . . .

If you don't specify a particular schema, the default dbo schema takes ownership of the table. After creating a table, you can change its schema. To do this visually, open the table in Design view. In the Properties window, click the arrow of the Schema combo box and select the desired schema. You will receive a message box based on the permissions:

If you find out that the name of a table is not appropriate, you can change it. To change the name of a table in the SQL Server Management Studio, in the Object Explorer, right-click the table and click Rename. Type the desired name and press Enter.

To change the name of a table with code, execute sp_rename, followed by the current name of the table, a comma, and the new desired name of the table. The formula to use is:

sp_rename ExistingTableName, TableNewName;

The names of tables should be included in single-quotes. Here is an example:

sp_rename 'StaffMembers', 'Employees';
GO

In this case, the interpreter would look for a table named StaffMembers in the current or selected database. If it finds it, it would rename it Employees. If the table does not exist, you would receive an error.

If you have an undesired table in a database, you can remove it. To delete a table

•  In the SQL Server Management Studio, in the Object Explorer, right-click the table under its database node and click Delete
• In Microsoft Visual Studio, in the Server Explorer, expand the connection to the database that owns the table and expand its Tables node. Right-click the undesired table and click Delete

You will receive a warning giving you a chance to confirm your intentions. If you still want to remove the table, click OK.

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. When you execute the statement, you will not receive a warning before the table is deleted.

You can also use sample code in Microsoft SQL Server Management Studio that can generate code for you. First display an empty query window. Also display the Templates Explorer and expand the Table node. Under Table, drag Drop Table and drop it in the empty query window. Sample code would be generated for you. You can then simply modify it and execute the statement.

In future lessons, we will write various expressions that involve the names of tables. In those expressions, you will need to specify a particular table you want to use. There are three main ways you can do this. To refer to, or to indicate, a table:

• You can simply type its name. An example would be Students
• You can type dbo, followed by the period operator, followed by the name of the table. An example would be dbo.Students
• You can type the name of the database to which the table belongs, followed by the period operator, followed by dbo, followed by the period operator, and followed by the name of the table. An example would be RedOakHighSchool.dbo.Students

The Columns of a Table

Introduction

In our study of data sets and in our introduction to tables, we saw that a list could be organized in categories called columns. Here is an example:

As you can see from this arrangement, a column is used to particularly classify one type of data. For example, one column can be used to list some names. Another column can be used to list numbers. Yet another column can be used for a select list of items that keep repeating.

To organize the information that a column holds, a table needs a series of details about each column. If you are visually creating a table, you can create the columns in the top section or write SQL code in the bottom section.

Two aspects are particularly important: a name and the type of data that a column should/must/can hold. We saw that the primary formula to create a table was:

CREATE TABLE TableName

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 ")". Each column must be separated from the next with a comma, except for the last column. You can include all columns on the same line if possible as follows:

CREATE TABLE [SchemaName.]Country(Column1, Column2, Column3)

Alternatively, to make your statement easier to read, you should create each column on its own line as follows:

CREATE TABLE [SchemaName.]Country(
Column1,
Column2,
Column3);

There are two primary pieces of information you must specify for each column: its name and its type. Therefore, the syntax of creating a column is:

ColumnName DataType Options

To be able to recognize the categories of information that a column holds, the column must have a name. The name of a column:

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

After respecting these rules, you can add your own rules. In our lessons, here are the rules we will use to name our columns:

• 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

After deciding on the name of a column, the database needs to know what kind of information the column would hold. This means that you must specify the data type that is necessary for a particular column. We saw that you can create user-defined data types for existing Transact-SQL data types. If you are working on a database, you can create and store your user-defined types in it. After creating the UDT(s), you can use it(them) for your column(s).

A database deals with various types of data, appropriate or not for certain fields. This means that you should take care of jobs behind the scenes as much as you can. One way you can do this is by controlling the amount of information that can be stored in a particular field. As various columns can hold different types of data, so can the same data type control its own mechanism of internal data entry. The length of data means different things to different fields. Columns that carry the same data type can have different lengths.

Bit Fields: We saw already that a bit column type is meant for one of two answers. The user is supposed to simply let the database know that the answer is yes or no, true or false, on or off, 1 or 0. Therefore, the only length of this field is 1.

Integers: The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.

Decimal and Floating-Point Numbers: The Length specifies how many bytes the field can store.

Strings: The length of a character or string column specifies the maximum number of characters that the field can hold.

Some of the data types need to have a length. This is certainly true for all string or text-based columns (char, text, varchar, etc). In the case of text-based columns, when using SQL to create your columns, because it is less visual than the table design, you cannot rely on the default length of strings suggested by SQL (in fact, in MySQL, you must specify a length for varchar). Here are examples:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
		 new SqlCommand("CREATE TABLE Customers (" +
				"DrvLicNbr NVarChar(50), " +
				"DateIssued Date," +
				"DateExpired Date," +
				"FullName nvarchar(120)," +
				"Address NVARCHAR(120)," +
				"City nvarchar(50)," +
				"State nvarchar(100)," +
				"PostalCode nvarchar(20)," +
				"HomePhone nvarchar(20)," +
				"OrganDonor bit);",
				connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show("A new table named \"Customers\" has been created.");
    }
}

If a column was already created, to get its length, you can call the COL_LENGTH() function. Its syntax is:

COL_LENGTH('table' , 'column') RETURNS smallint;

This function takes two required arguments. The first argument is the name of the table that owns the column. The second argument is the name of the column. The result is as follows:

• If the column is of type char or varchar, the function returns the number of characters that were specified at its creation
• If the column is of type nchar or nvarchar, the function returns twice the number of characters that were specified at its creation
• If the column is another type, the function returns the length based on its data type definition in Transact-SQL

Here is an example:

SELECT COL_LENGTH(N'Employees', N'HourlySalary') AS [Size of Salary];

We will write many expressions that include the names of columns. In such expressions, you will need to indicate the particular column you are referring to. There are various ways you can do this. To refer to, or to indicate, a table:

• You can use the name of the table to which the column belongs, followed by the period operator, followed by the name of the column. An example would be Employees.LastName
• You can use the name of the schema that owns the table to which the column belongs, followed by a period, followed by the name of the table to which the column belongs, followed by the period operator, followed by the name of the column. An example would be dbo.Employees.LastName
• You can type the name of the database that owns the table's column, followed by the period operator, followed by the name of the schema, followed by a period, followed by the name of the table to which the column belongs, followed by a period, followed by the name of the column. An example would be RedOakHighSchool.dbo.Employees.LastName

You can create an alias name of a table to use in an expression that involves a column. To do this, type a letter or a word that will represent the table to which the column belongs. The letter or the word is followed by a period, and followed by the name of the column. An example would be empl.LastName. At the end of the statement, you must type the name of the table, followed by space, and followed by the letter or the word. An example would be Employee empl. You can also type AS between the name of the table and its alias. An example is Employee AS empl (we will see various examples in future lessons.

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.

Because different languages use different mechanisms in their alphabetical characters, this can affect the way some sort algorithms or queries are performed on data, you can ask the database engine to apply a certain language mechanism to the field by changing the Collation property.

To specify the collation option, when programmatically creating the table, type COLLATE, followed by the desired collation code. Here is an example:

CREATE TABLE Customers(
    FullName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
);

When making a change on a column, you are also said to alter the table. To programmatically support this operation, 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.

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:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
		 new SqlCommand("ALTER TABLE Customers " +
				"ADD EmaillAddress varchar(100);",
				connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show(
		"A new column named \"EmailAddress\" has been added.");
    }
}

When this code is executed, a new column named Address, of type varchar, with a limit of 100 characters, that allows empty entries, will be added to a table named StaffMembers in the current database.

If you find out that the name of a column is not appropriate, you can change it. To do this, execute sp_rename using the following formula:

sp_rename/font> 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

private void btnDatabase_Click(object sender, EventArgs e)
{
    string strConnection = "sp_rename 'StaffMembers.FullName', 'EmployeeName', 'COLUMN';";
    SqlConnection conDatabase = new 
	SqlConnection("Data Source=(local);" +
		      "Database='Countries2';" +
		      "Integrated Security=yes");
    SqlCommand cmdDatabase = new SqlCommand(strConnection, conDatabase);

    conDatabase.Open();

    cmdDatabase.ExecuteNonQuery();
    conDatabase.Close();
}

ALTER TABLE TableName
DROP COLUMN ColumnName

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
		new SqlCommand("ALTER TABLE Customers " +
				"DROP Column DateIssued;",
				connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show(
		"The column named \"DateIssued\" has been deleted.");
    }
}