Creating a Database for our site - Part 2

When we want to work with data stored in a database, we need to create the database as well as establish a connection with the database. This connection is the medium of communication with the data stored in the database.

• Clicking on the Add Connection... option will bring about this dialog box:

In this dialog box, we need to fill some information about our database. The first thing to consider is the Data Source. Since we are using SQL Server 2005 Express, we will leave it as default - Microsoft SQL Server (SqlClient). However, we have the option to change the data source and select Oracle, Access or any other data source.

We also need to identify the name of the server where our database is stored. Since we have installed the SQL Server on the same PC, our computer name is the Server name. In bigger organizations, the database may be stored on a different computer and we need to select that server name.

The Log on to the server part asks us how we connect to the SQL Server. Use Windows Authentication option is used to connect using windows username/password. Use SQL Server Authentication option is used when we need a separate username/password for connection to SQL Server. These options are generally set at the time of SQL Server Setup and Installation.

Connect to a database section allows us to connect to a previously created database or create a new database and connect to it. Select the `Select or enter a database name' radio button. If we expand the drop down list now, we can see a list of previously created databases (if any). However, since we want to create a new database, enter its name (`Library', in our example) in the drop down list. Click OK to continue.

• As soon as we click on the OK button, we will get a message:

Since the database name provided by us (Library) does not exist, VWD is asking us if we want to create it. Of course we do. Click on the Yes button.

• That is it! We have created a database named Library. We can see it in the Database Explorer. But we cannot store our data yet. First, we will need to create a table in our database.

In the Database Explorer, you can expand our database by clicking on the + symbol. This will display a list of folders. Find the folder named `Tables'. Right click on it. From the menu, select `Add New Table' option.

• A new tab will open up in the VWD IDE. This is the design view of the table:

As we can see from the figure above, we need to specify the Column Name, Data Type and Allow Null information here.

The Column Name is the name of the columns in our table. For example, Title, Author, Publisher etc. Data Type will specify the type of data being stored in these columns. Allow Null is used to specify whether we want to allow empty (null) values in the columns.

Let us design our table using the following guideline:

Column Name	Data Type	Allow Null
BookISBN	nchar(13)	Yes (check)
BookTitle	varchar(120)	No (uncheck)
BookAuthor	varchar(60)	Yes (check)
BookPublisher	varchar(50)	Yes (check)
BookCatagory	varchar(20)	No (uncheck)
BookSerialNo	int	No (uncheck)

Let us talk about the last column - BookSerialNo. We have created this column to be used as the primary key for the table. It does not have any other purpose. We could have used the BookISBN column as the primary key as no two ISBNs are same but there are two reasons for not doing so. First, the BookISBN column is of character type. Primary keys works best for integer types (although not a strict requirement). Second and more important reason is that many useful texts such as whitepapers, thesis and other such documents do not have ISBN. We may want to include such items in our library and that is why we have allowed null values in the BookISBN column. Since the primary key column never allows null values, we cannot use BookISBN column as the primary key.

We will now make the BookSerialNo column as the primary key. To do this, click on the column. In the lower pane, the properties of the column are displayed.

Look for the property `Identity Specification'. Expand it and set the (IsIdentity) property to Yes. Leave the other properties as they are. This will make the values in the column start with 1 and increase with 1.

Next, right click on the column name in the upper pane and from the menu, select Set Primary Key option. This will make the BookSerialNo column the primary key of our table.

The complete table design should look like this:

• Now that the table design is complete, we will save the table. Click on the save button in the toolbar. A dialog box will appear:

Enter a name for the table and click on the OK button.