Build Your Own ASP.NET 3.5 Website Using C# & VB (54 page)

and the DepartmentID column in the Employees table, are perfect candidates for

the application of a foreign key constraint. Take a look at the examples shown in

Table 7.13 and Table 7.14
.

The DepartmentID column in the Employees table references the DepartmentID

primary key in the Departments table. Notice that the DepartmentID primary key

in the Departments table is unique, but the DepartmentID foreign key within the

Employees table may repeat.

As they stand, these tables already have an established relationship, and all the data

in the DepartmentID column of the Employees table correctly matches existing departments in the Department table. However, as with primary keys, just having the correct fields in place doesn’t mean that our data is guaranteed to be correct.

For example, try setting the DepartmentID field for one of the employees to 123.

SQL Server won’t mind making the change for you, so if you tried this in practice,

you’d end up storing invalid data. However, after we set the foreign keys correctly,

SQL Server will be able to ensure the integrity of our data—specifically, it will forbid

us to assign employees to nonexistent departments, or to delete departments with

which employees are associated.

The easiest way to create foreign keys using Visual Web Developer or SQL Server

Management Studio is through database diagrams, so let’s learn about them.

Licensed to [email protected]

Database Design and Development

293

Table 7.13. The Departments table’s primary key

DepartmentID (Primary Key)

Department

1

Accounting

2

Administration

3

Business Development

4

Customer Support

5

Executive

6

Engineering

7

Facilities

8

IT

9

Marketing

10

Operations

Table 7.14. The Employees table referencing records from the Departments

table

Emp’ID

Dep’tID

Name

U’name

P’word

City

State

M’Phone

(Primary

Key)

1

5

Zak

zak

zak

San

CA

555-555-5551

Ruvalcaba

Diego

2

9

Jessica

jessica

jessica

San

CA

555-555-5552

Ruvalcaba

Diego

3

6

Ted

ted

ted

San

CA

555-555-5555

Lindsey

Diego

4

6

Shane

shane

shane

San

CA

555-555-5554

Weebe

Diego

5

9

David

david

david

San

CA

555-555-5553

Levinson

Diego

6

1

Geoff Kim

geoff

geoff

San

CA

555-555-5556

Diego

Licensed to [email protected]

294

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Using Database Diagrams

To keep the data consistent, the Dorknozzle database really should contain quite a

few foreign keys. The good news is that you have access to a great feature called

database diagrams
, which makes it a cinch to create foreign keys. You can define

the table relationships visually using the database diagrams tool in Visual Web

Developer or SQL Server Management Studio, and have the foreign keys generated

for you.

Database diagrams weren’t created specifically for the purpose of adding foreign

keys. The primary use of diagrams is to offer a visual representation of the tables

in your database and the relationships that exist between them, to help you to design

the structure of your database. However, the diagrams editor included in Visual

Web Developer and SQL Server Management Studio is very powerful, so you can

use the diagrams to create new tables, modify the structure of existing tables, or add

foreign keys.

Let’s start by creating a diagram for the Dorknozzle database. To create a database

diagram in Visual Web Developer, right-click the
Database Diagrams
node, and select

Add New Diagram
, as shown in Figure 7.15.

The process is similar in SQL Server Management Studio, which, as Figure 7.16 il-

lustrates, has a similar menu.

The first time you try to create a diagram, you’ll be asked to confirm the creation

of the database structures that support diagrams. Select
Yes
from the dialog, which

should look like the one shown in Figure 7.17.

Figure 7.17. Adding support for database diagrams

Licensed to [email protected]

Database Design and Development

295

Figure 7.15. Creating a database diagram with Visual Web Developer

Figure 7.16. Creating a database diagram with SQL Server Management Studio

Figure 7.18. Adding tables to the diagram

Licensed to [email protected]

296

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Next, a dialog like the one in Figure 7.18
will ask you which of your database tables you want included in the diagram. If you’re working with a database that comprises

many tables, you may want to have diagrams built to represent specific pieces of

functionality, but we want to create a diagram that includes all the tables in our

database.

Click
Add
until all the tables are added to the diagram. As you click
Add
, the tables will be removed from the list and will appear in the diagram. Once you’ve added

all the tables, click
Close
. You’ll see a window in which all the tables are clearly

displayed—
something like Figure 7.19.

You’ll probably need to tweak their positioning and dimensions so they fit nicely

into the window. The zooming feature may prove useful here! Select
File
>
Save

Diagram1
(or similar) to save your new diagram. Enter
Dorknozzle
for the diagram’s name.

Now, if you right-click any table in the diagram, you’ll gain access to a plethora of

possibilities, as
Figure 7.20 reveals. This menu, along with the other diagramming

features, are identical in Visual Web Developer and SQL Server Management Studio.

Figure 7.20. The many features of the diagram editor

Expanding the
Table View
submenu gives you more options for displaying your table.

If you choose
Standard
, you’ll see a full-blown version of the table definition; as

Figure 7.21
shows, you can change the table structure directly in the diagram! The diagramming features provided for free are extremely powerful and useful.

Licensed to [email protected]