Chapter 7: Database Design and Development

the line, we include the Employee ID column, the function of which is to assign a unique number to each employee in the database. This gives us an easy way to refer to each person, and let us keep track of which employee is which. We’ll discuss such database design issues in greater depth shortly.

So, to review, Figure 7.1 shows a four-column table with four rows, or entries. Each row in the table contains four fields, one for each column in the table: the employee’s ID, name, username, and telephone number.

Now, with this basic terminology under your belt, you’re ready to roll up your sleeves and build your first database!

Creating your First Database

The SQL Server 2005 engine does a great job of storing and managing your databases, but in order to be able to do anything meaningful with the data, we first need to connect to SQL Server. There are many ways to interact with SQL Server, but for starters, we’re just interested in using it as a visual tool to facilitate basic administrative tasks.

Because you’re using SQL Server 2005, your key tools will be either:

Visual Web Developer 2005 Express Edition

SQL Server Management Studio Express Edition

That’s right, Visual Web Developer has everything you need to get started with

SQL Server! However, we’ll use SQL Server Management Studio for most database tasks—most tasks are easier in SQL Server Management Studio than they are in Visual Web Developer, as SQL Server Management Studio’s interface has been designed specifically for working with databases.

We’ll call the database that will store the data for our sample project “Dorknozzle.” In this chapter, you’ll learn how to create its structure, and in the next chapter, we’ll begin to work with the database. You can use either Visual Web Developer or SQL Server Management Studio to create the Dorknozzle database. I’ll show you both approaches, so you’re comfortable with both options.

254

Creating a New Database Using Visual Web Developer

Creating a New Database Using Visual Web

Developer

Visual Web Developer’s Database Explorer window gives you access to most database-related features. You can make this window appear by selecting View

> Database Explorer. Right-click the Data Connections node and select Add Connection… from the context menu, as shown in Figure 7.2.

Figure 7.2. Adding a new database connection

If you’ve installed SQL Server using the instructions we stepped through in

Chapter 1, select Microsoft SQL Server from the Choose Data Source dialog that appears, and click Continue. You’ll then be asked to enter the details for your data connection. Enter the following data:

Set Server name to localhost\SqlExpress.

Leave the Use Windows Authentication option selected.

Click Test Connection to ensure you can successfully connect to SQL Server using the data you’ve provided.

Enter Dorknozzle in the Select or enter a database name field. Click OK.

You’ll be asked to confirm the creation of a new database called Dorknozzle. Click Yes.

255

Chapter 7: Database Design and Development

Figure 7.3. Exploring the Dorknozzle database

Once you click Yes, the new database will be created, and a link to it will be added to the Data Connections node in Database Explorer. You can expand it to view its contents, as Figure 7.3 illustrates.

Creating a New Database Using SQL Server

Management Studio

To start SQL Server Management Studio, which we learned to install in Chapter 1, select Start > All Programs > Microsoft SQL Server > SQL Server Management Studio Express. In the dialog that appears, enter localhost\SqlExpress into the Server Name box, and leave Authentication mode to Windows Authentication, as Figure 7.4 illustrates.

After you connect to SQL Server, expand the Databases node to see the current databases. If you’ve just installed SQL Server, you’ll only have installed the system databases, which are grouped under a System Databases node. In Figure 7.5 below, you can see that I have another database, named BalloonShop, on my SQL Server.

256

Creating a New Database Using SQL Server Management Studio

Figure 7.4. Connecting to a SQL Server instance

Figure 7.5. Inspecting your SQL server instance

To create a new database, right-click the Databases node, and select New Database… from the context menu. In the dialog that appears, enter Dorknozzle into the Database name field, then click OK.

Congratulations, you have a brand new database to play with!

257