Home

Introduction to the Tables of a Database

   

Tables Fundamentals

 

Introduction

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:

Judie
Ernest
Bill
David
Hermine

If the table is made of more than one list, each list is made in a distinguishable category. 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

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

Practical LearningPractical Learning: Introducing Database Tables

  1. Start Microsoft Visual Studio
  2. If the Server Explorer is not visible, on the main menu, click View -> Server Explorer.
    In the Server Explorer, right-click Data Connections and click Create New SQL Server Database
  3. In the Server Name combo box, select the server or type (local)
  4. Accept or specify your authentication mode
  5. Set the name of the database to CPAR1
     
    Create New SQL Server Database
  6. Click OK

Visual Creation of a Table

The information of a table is organized in categories called columns or fields. To visually create a table in Microsoft Visual Studio, in the Server Explorer, expand the connection to the database that will own the table. Right-click the Tables node and click Add New Table:

Add New Table

After clicking Add New Table, a new empty table would come up and expect you to customize it:

New Table

Practical LearningPractical Learning: Starting a Table

  1. In the Server Explorer, expand server.CPAR1.dbo
  2. Under it, right-click Tables and click Add New Table

Tables Names

To complete the creation of a table, you must save it. If you are freshly creating a table and decide to save it, you would be prompted to name it. 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

Creating a Table With SQL

 

Introduction

In SQL, 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.

Temporary Tables

After creating a table, it becomes part of its database and you can use that table over and over again. In some cases, you may want to create a table to test something and you would not need to use that table the next time you connect to the server. Such a table is referred to as a temporary table.

To create a temporary table, start its name with #, followed by the desired name. Once the table has been created, it would be available as long as you are using the same connection to the server. If you close Microsoft SQL Server, the table would be automatically deleted.

Tables Maintenance

 

Viewing the Properties of a Table

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.

Like every other object of a database or of the computer, a table possesses some characteristics that are proper to it. To view these characteristics in Microsoft SQL Server Management Studio, in the Object Explorer, you can right-click the table and click Properties.

Opening a Table

Most operations require that you open a table before using it. There are various ways a table displays, depending on how you want to examine it. In Microsoft Visual Studio, in the Server Explorer, expand the connection to the desired database and expand its Tables node:

  • To view the structure of a table, right-click the table and click Open Table Definition
  • To open a table to view its data, right-click the table and click Show Table Data

Tables Review

In Microsoft Visual Studio, to see the list of tables of a database, in the Server Explorer, expand the connection to the desired database and expand the Tables node. Here is an example:

Tables Review

Using Microsoft SQL Server Management Studio, to see the list of tables of a database using SQL, in a Query window, specify the database (using a USE statement), and execute sp_help (it is a stored procedure). Here is an example:

List of Tables

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();
	    }
    	}
    }
} 

sp_help

Renaming a Table

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.

Deleting a Table

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.

Referring to a Table

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
     

Tables and Permissions

The table as an object doesn't have many own rights. Its permissions are controlled partly by its database owner and by its contents. The primary right to exercise on a database is the ability to create a table.

To allow a user to create tables on a database, type GRANT CREATE TABLE TO followed by the name of the user. Here is an example:

USE Exercise1;
GO
GRANT CREATE TABLE
TO [Raymond Kouma];
GO
 

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:

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

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. Two aspects are particularly important: a name and the type of data that a column should/must/can hold.

The Name of a Column

To be able to recognize the categories of information that a column holds, the column must have a name. In the Microsoft SQL Server Management Studio, the name of a column displays in the top, the header part, of the column. The name of a column allows the database as a file to identify the column. The name of a column also will help you, the database developer, to identify that column. There are rules and suggestions you must or should follow when naming the columns of a table.

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

Practical LearningPractical Learning: Setting Columns Names

  • Click the empty box under Column Name (the caret should be blinking in it already) and type ReceiptNumber

The Types of Data

After deciding on the name of a column, the database needs to know what kind of information the column would hold. Since there are various kinds of information a database can deal with, we saw in Lesson 20 the types of data that Microsoft SQL Server supported. Therefore, you must specify the data type that is necessary for a particular column.

Practical LearningPractical Learning: Setting Data Types

  1. Press Tab and press Alt + down arrow key to display the list of data types
  2. Scroll up and select int from the list
     
    Setting Data Types
  3. Click the first empty field under ReceiptNumber
  4. Create the following columns:
     
    Column Name Data Type
    ReceiptNumber int
    OrderDate date
    OrderTime date
  5. To save the table, on the Standard toolbar, click the Save button
  6. In the Choose Name dialog box, type RepairOrders
  7. Click OK

Using User-Defined Data-Types

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). To do this visually, after displaying the table in design view, click the column name, click the arrow of the Data Type combo box to display a mix of Transact-SQL types and your own defined types:

Table

The Length of Data

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.

In some circumstances, you will need to change or specify the length as it applies to a particular field. For example, since you should use the varchar data type for a string field whose content will change from one record to another, not all varchar columns need to have the same length. Although a First Name and a Book Title columns should use the varchar type, both columns would not have the same length of entries. As it happens, people hardly have a first name that is beyond 20 characters and many book titles go beyond 32 characters. In this case, both fields would use the same data type but different lengths. On the other hand, for columns of datetime2 and money data types, you should accept the default length suggested by the database.

There are two ways you can change the length of a string-based column:

  • In the top section of the window, to change the length of the field, in the parentheses of the data type, enter the desired value
  • In the top section of the window, click the name of the column. In the bottom section, click the Length field and type the desired value

Practical LearningPractical Learning: Setting Data Types

  1. Click the empty box under OrderTime, type CustomerName and press Enter
  2. Click the arrow of the data type combo box and select nvarchar(50)
  3. In the bottom section of the table, click Length and on the right side, replace 50 with 40
  4. Complete the table with following columns:
     
    Column Name Data Type
    ReceiptNumber int
    OrderDate date
    OrderTime date
    CustomerName nvarchar(80)
    CarMake nvarchar(50)
    CarModel nvarchar(50)
    CarYear smallint
    ProblemDescription ntext
    TotalParts money
    TotalLabor money
    TaxRate decimal(6,2)
    TaxAmount money
    TotalOrder money
    Recommendations ntext
  5. To save the table, on the Standard toolbar, click the Save button Save
  6. Close the table by clicking its Close button Close

Column Creation with SQL

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

The name of the column follows the rules and suggestions we listed. After typing the name of the column, type the desired or appropriate data type for the column. For this example, use one of the (appropriate) data types we reviewed.

Remember that 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). As it happens, the table design specifies different default values for text-based columns. Therefore, when using SQL to create your columns, you should (strongly) specify your own default length for text-based columns.

To create a table in your Windows application, you can pass the CREATE TABLE expression to a command object. 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("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.");
    }
}

Referring to a Column

 

Introduction

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 must type 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 type dbo, followed by the period operator, 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 dbo, followed by the period operator, 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 RedOakHighSchool.dbo.Employees.LastName

Using the Alias Name of a Table

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 operator, 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.

The Properties of a Column

 

Introduction

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.

Column Review

To see the structure of a table in Microsoft Visual, in the Server Explorer, expand the connection to the desired database, expand the Tables node, and expand the table whose columns you want to see. Here is an example:

Column Review

The Column Properties 

A column on a table controls the type of data appropriate for that particular column. The characteristics that identify or describe such a table are defined as its properties. As we have seen previously, three primary properties are particularly important and required for each column: the name, the data type, and the length. Besides these, some other properties can be used to further control the behavior of a particular field.

Besides the name, the data type, and the length of a column, you can control the columns of a table using the Column Properties tab in the lower section of the table design view. These properties sometimes depend on the data type of the column. Therefore, to specify the properties of a column, you must first select it in the upper section of the table. This selection can be done by just clicking either the name, the data type, or the length of the column. Then you can either press F6 or click the first field in the lower section, select the desired property and type the necessary value:

Column Properties

Collation

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 to apply a certain language mechanism to the field by changing the Collation property.

To visually specify the language collation of a column, after selecting the column in the top section, in the lower section, click Collation and click the ellipsis button of its right field. This would open a Collation dialog box. You have two categorical options. To use a collation defined by the SQL, you can accept the SQL Collation radio button, and click the arrow of the top combo box to select the desired option:

Collation 

To specify these options using SQL, 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
);

As you can see, the SQL's collation can be hard to figure out. If you are using the Collation dialog box, you can click the Windows Collation radio button and click the arrow of the second combo box. This time, you would receive a list of familiar names of languages.

Besides the language of your choice, you can also specify the types of sort algorithms that you want the SQL interpreter to follow. The options are under the second combo box.

After making your selections, you can click OK.

Columns Maintenance

 

Modifying a Column

When making a change on a column, you are also said to alter the table. To visually modify a column, from the Server Explorer, right-click its parent table and click Open Table Definition. Locate the column and change any characteristic you want: name, data type, nullity, or properties.

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.

Adding a New Column

After a table has already been created, you can still add a new column to it.

To add a new column in Microsoft Visual Studio, in the Server Explorer, expand the connection to the desired database, expand the Tables node, and expand the table. Here Right-click the table and click Open Table Definition. To add a new column to the end of the table, click the first empty field under Column Name, type a name, and specify the other options.

To insert a new column between two existing one, right-click the column that will succeed it and click Insert Column:

Adding a New Column

This would create a new empty field. Type the desired name and specify the other options.

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.

Renaming a Column

If you find out that the name of a column is not appropriate, you can change it.

To visually rename a column in Microsoft Visual Studio, in the Server Explorer, right-click the table that the column belongs to and click Open Table Definition. In the design view, highlight the name of the desired column to put it into edit mode and edit it.

In Microsoft SQL Server Management Studio, to change the name of a column, first open an empty query window. In a query window, execute sp_rename using the following formula:

sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

The sp_rename factor and the 'COLUMN' string are required. The TableName factor is the name of the table that the column belongs to. The ColumnName is the current name of the column. The NewColumnName is the desired name you want to give to the column.

Here is an example:

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();
}

When this code is executed, the interpreter will look for a column named FullName in the StaffMembers table of the current or selected database. If it finds that column in the table, then it renames it EmployeeName.

Deleting a Column

If you have an undesired column that you don't want anymore in a table, you can remove it.

To visually delete a column, in the Server Explorer, right-click the table and click Open Table Definition. In the table, right-click the undesired column and click Delete Column:

Deleting a Column

You would not receive a warning.

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:

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.");
    }
}

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.

Practical LearningPractical Learning: Setting Data Types

  1. In the Server Explorer, right-click Server.CPAR1.dbo and click Delete
  2. When asked for a confirmation, click Yes
 

Previous Copyright © 2010-2011 C#Key Next