Microsoft Visual C# 2005 Express Edition: Build a Program Now! (30 page)

integer

Not Null

ProductID (FK)

integer

Not Null

Quantity

integer

Not Null

LineTotal

numeric(38,6)

Not Null

Table 8-3

OrderDetail Table

Your Product, OrderHeader, and OrderDetail tables could also be represented graphically, as shown in Figure 8-1. This is a common way of looking at databases.

Figure 8-1

Partial database diagram of a small online company

Chapter 8: Managing the Data

131

C08622299.indd 131

C08622299.indd 131

10/24/05 4:02:29 PM

10/24/05 4:02:29 PM

What Is Null?

One of your first observations is that there’s a column in the tables titled Allow Nulls?, which is also reflected in Figure 8-1. When designing a table, you need to consider what’s absolutely necessary (Not Null) and what’s not (Null). For instance, when you insert a new row into the Product table, it may not matter whether the product has a photo, but it might be a problem to have a product without a product number. Now let’s correlate how allowing null is related to data integrity. Whenever a table is designed with a column that doesn’t allow null, the RDBMS will reject any insertion of a new row that has a column set to null when it is not supposed to. When you pay attention to those columns that cannot contain null when designing your tables, you automatically add another data integrity layer by making sure that all necessary data is present before the record is inserted into the database.
What Are Primary Keys and Foreign Keys?

You’ll see in Figure 8-1 that some columns have the letters (PK) for primary key in the tables or a yellow key for primary keys. Some columns also contain (FK) for foreign key. Let’s start by talking about the primary key.

Primary Key

A primary key is a value that is used to uniquely identify a specific row in a table. A primary key:

■ Can be composed of one or more column names. When it’s composed of more than one column, it’s called a composite key.

■ Is often a numeric field.

■ Is often generated by the RDBMS, in which case it’s called a surrogate key. A surrogate key is frequently (but not always) a sequential number. A surrogate key is also called an identity in SQL Server 2005. An identity starts at a set number, called the identity seed, and increments by another set number, called the identity increment. For example, if you create a table named Product, you can have a column named ProductID that is set as an identity, and you can set the identity seed to 1 with an identity increment to 1. When the first row is created in the Product table, the ProductID will be generated by the RDBMS

and set to 1. The following row will have a ProductID that is set to 2 and so forth.
132

Microsoft Visual C# 2005 Express Edition: Build a Program Now!

C08622299.indd 132

C08622299.indd 132

10/24/05 4:02:30 PM

10/24/05 4:02:30 PM

■ Should be as short as possible, but long enough to support the number of rows it will represent.

■ Is immutable, meaning its value should never change.

■ Is also a natural key when the key has a logical relationship with the rest of the columns in the table. For example, if you had a book table, the ISBN number could be used as a primary key because it uniquely identifies only one book. It would be an advantage compared with a generated key because it would take less space and has to exist anyway!

■ Is also used to relate two tables together.

In our Product table example, the ProductID is the primary key. At design time, it will also be an identity. You can claim that the product number could be a primary key and you could be right, but in certain scenarios a product number could be used twice. For example, suppose you have product #FG-001 with a revision 1.0. In time, you change the product because of customer complaints and give it a revision 2.0. You want your customers to continue to order the same product number for many business reasons. In your database, you would retire the product revision 1.0 by perhaps changing a column named Active, then add another row in your table with the new product details, including revision 2.0, and set it to Active. Why can’t you use the same row? Let’s assume that six months after creating the new product revision, you want to create a graph to determine whether your changes to the product meant that you had fewer returns from your customers. It would be difficult to come up with good data if you had only one row for the product, but it would be fairly easy to do if you have two rows because they would be unique in the database, with each one having a different ProductID.

In the OrderDetail table, you have a composite primary key that is a combination of the OrderID and OrderDetailID. This means that these two columns would ensure the uniqueness of a row in the OrderDetail table.

In the OrderHeader table, the OrderID is the primary key.

Chapter 8: Managing the Data

133

C08622299.indd 133

C08622299.indd 133

10/24/05 4:02:30 PM

10/24/05 4:02:30 PM

Foreign Key

A foreign key is a column in a table that relates to a column in another table. It also enables you to create relations between tables. A foreign key in a table is always a primary key in another table. Foreign keys are used to enforce data integrity by being part of foreign key constraints. Foreign key constraints are created to make sure referential integrity is preserved and not violated. There are two foreign keys in the order details. The first is the ProductID foreign key in the OrderDetail table, and it’s related to the primary key named ProductID in the Product table. The second is the OrderID foreign key in the OrderDetail table, and it’s related to the primary key named OrderID in the OrderHeader table. Concerning the naming of foreign keys, it’s a good practice to define them using the same name as their primary key counterpart; otherwise, it may lead to problems for those looking at your logical data model.

I introduced you to data integrity at the beginning of this chapter. In doing so, I cited an example that could create similar problems to the one in the Product and OrderDetail table example. Adding a foreign key constraint between these two tables would prevent a user from deleting a product in the Product table that could potentially create a large number of orphaned rows in the OrderDetail table. If you look at Figure 8-1, the foreign key constraint between Product and OrderDetail is shown as a line between the two tables that can be found by looking at the name FK_Product_OrderDetail. Naming constraints is an easy way to understand what they are for. We only have three tables in our example, but you can imagine that constraints without names that exist between numerous tables would quickly become unclear.

Another foreign key constraint exists here, which is the one between the OrderHeader and OrderDetail tables that would prevent an order from being deleted before all of its matching OrderDetails have been deleted. You can see in Figure 8-1 that the OrderHeader table has another foreign key called CustomerID. Therefore, another foreign key constraint would exist between the Customer and OrderHeader tables. Following the same principles found with other foreign key constraints, this would prevent a customer from the Customer table from being deleted before all of its matching orders in the OrderHeader table and all detail rows in the OrderDetails table that match the orders have been deleted. If there were no foreign key constraints in this database, data integrity would be easily violated. The database would be left with a big problem: a time bomb of orphaned rows
134

Microsoft Visual C# 2005 Express Edition: Build a Program Now!

C08622299.indd 134

C08622299.indd 134

10/24/05 4:02:31 PM

10/24/05 4:02:31 PM

that take up space and slow down all queries. By adding this foreign key constraint, the RDBMS would ensure that all rows in the OrderDetail table that reference this product have been deleted before the product row could be deleted in the Product table.
How Do You Interact with a Relational Database?

So far, I’ve talked about tables in which you can update, add, or delete rows or query the database to get particular results. Perhaps you’ve been asking yourself: But how do I talk or interact with the database? How does it return the answers to my queries? And how do you create those tables? I’m sure you’ve been asking yourself many other questions as well. The answer to all of these questions is SQL Server 2005 Express Edition.

SQL stands for
S
tructured
Q
uery
L
anguage and was invented in the 1970s. The acronym is pronounced SEQUEL and was also introduced using that same spelling, but because of a trademark dispute in the UK in the 1970s, the name was shortened to the now well-known SQL acronym. Back then, the SEQUEL acronym meant
S
tructured
E
nglish
Qu
ery
L
anguage. SQL is an English-based language and is very similar to human language questions. That’s why it’s easy and fast to learn basic SQL programming. Let’s look at two examples:
1.
SELECT * FROM CUSTOMER

2.
SELECT COUNT(*) FROM PRODUCT

The first example can be translated in English to give me all (*) rows in the Customer table or give me the list of customers in English. The second example can be translated as a request to give me the total of all rows contained in the Product table or to count how many products this company has.

When you issue an SQL query to a relational database, the database returns a result set that simply contains the rows with the answers to your query. Using SQL, you can also group or aggregate the results of a query. You also use SQL to create tables or delete (drop) tables. You’ve learned about primary keys, foreign keys, and constraints, but you probably didn’t know that they’re also created using SQL.

It’s also good to know that SQL is an ANSI/ISO standard; therefore, any RDBMS producer needs to obey a set of rules. Basic SQL is a base programming language and as such is usually not sufficient to solve all possible problems or analysis needs that an application may demand. It has a rather limited set of keywords. Because its first goal is to query data from a
Chapter 8: Managing the Data

135

C08622299.indd 135

C08622299.indd 135

10/24/05 4:02:31 PM

10/24/05 4:02:31 PM

database, the most popular RDBMSs on the market have added extensions to SQL to permit the addition of procedural code. These additions turn SQL into a full-fledged programming language that helps solve more complex problems. The following is a list of popular extensions and their manufacturers: Microsoft Transact-SQL (or T-SQL for short), Oracle PL/

SQL, and IBM SQL PL. Recently, in addition to these extensions, RDBMS manufacturers have added the support of other programming languages. Microsoft is adding .NET language support into the database with all SQL Server 2005 Editions, while Oracle and IBM have added Java support.

There are more database concepts and theories than those listed and explained here, but we have covered the immediate database needs of this book. You’ll now apply those concepts concretely in a Windows Forms application that will use a SQL Server Express 2005

database.

SQL Server 2005 Express in Visual C# 2005 Express Edition

In this section, you will develop a Windows Forms application. This will be a car tracker application that will enable the user to track the prices of cars over time and determine where the listing was observed. You will first use Visual Studio to create the database and the tables, then add some data and validate some of the concepts you’ve learned in the first part of this chapter. You will then create a Windows application that will use your data and build a data-centric application that will allow the user to store any amount of data. Refer to Figure 8-2 for the database diagram pertaining to this section’s example.
136

Microsoft Visual C# 2005 Express Edition: Build a Program Now!

C08622299.indd 136

C08622299.indd 136

10/24/05 4:02:31 PM

10/24/05 4:02:31 PM

Figure 8-2

Car tracker application database diagram

Creating a Database Using Visual C# 2005 Express Edition

Before using data, we need a place to store the data. You’ll learn how to create a database in

SQL Server is well integrated

Visual C# 2005 Express Edition. You’ll also see how easy it is for you to create all of the
because Visual Studio provides a

tables we need to satisfy the needs of our car tracker application because the SQL Server
great SDK for other components

to plug into the IDE.

team did a wonderful job of integrating the tools into Visual Studio.