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.