Exploring the Northwind Database

A database may have many tables, some of which are related to each other. For example, the North-wind database contains many tables, four of which are named Customers, Orders, Order Details, and Products. Figure 2.11 is a repeat of the diagram shown earlier that illustrates how these tables are related.

click to expand
Figure 2.11: Relationships between the Customers, Orders, Order Details, and Products tables

The columns for each table are shown within each box. For example, the Customers table contains 11 columns:

  • CustomerID

  • CompanyName

  • ContactName

  • ContactTitle

  • Address

  • City

  • Region

  • PostalCode

  • Country

  • Phone

  • Fax

In the next few sections, you'll learn some database theory, and then you'll learn how each of the previous columns is defined in the Customers table. You'll also explore the Orders, Order Details, and Products tables.

Primary Keys

Typically, each table in a database has one or more columns that uniquely identify each row in the table. This column is known as the primary key for the table. A primary key can be composed of more than one column. In such cases, it is known as a composite key.

Note 

The value for the primary key in each row of a table must be unique.

In the case of the Customers table, the primary key is the CustomerID column. The key icon shown to the left of the CustomerID column in Figure 2.11 indicates that this column is the primary key for the Customers table. Similarly, the primary key for the Orders table is OrderID. The primary key for the Order Details table is composed of two columns: OrderID and ProductID. The primary key for the Products table is ProductID.

Table Relationships and Foreign Keys

The lines that connect the tables in Figure 2.11, shown earlier, display the relationships between the tables. The infinity sign () at the end of each line indicates a one-to-many relationship between two tables, meaning that a row in one table can be related to one or more rows in the other table.

For example, the Customers table has a one-to-many relationship with the Orders table. Each customer can place many orders. Similarly, the one-to-many relationship between the Orders and Order Details table means that each order can be made up of many order details (you can think of an order detail as a line in a purchase order list, with each line referring to a specific product that is ordered). Finally, the one-to-many relationship between the Products and Order Details table means that each product can appear in many order details.

One-to-many relationships are modeled using foreign keys. For example, the Orders table has a column named CustomerID. This column is related to the CustomerID column in the Customers table through a foreign key. This means that every row in the Orders table must have a corresponding row in the Customers table with a matching value for the CustomerID column. For example, if a row in the Orders table has a CustomerID of ALFKI, then there must also be a row in the Customers table with a CustomerID of ALFKI. Since the relationship between the Customers and Orders table is one-to-many, this means that there can be many rows in the Orders table with the same CustomerID column. Conceptually, you can think of the foreign key as a pointer from the Orders table to the Customers table.

Often, the table containing the foreign key is known as the child table, and the table with the column referenced by the foreign key is known as the parent table. For example, the Orders table is the child table, and the Customers table is the parent table. Foreign key relationships are often known as parent-child relationships.

Note 

The relational term from "relational database" comes from the fact that tables can be related to each other through foreign keys.

You can manage the relationships for a table from Enterprise Manager by selecting the table from the Tables node, clicking the right mouse button, and selecting Design Table. You then click the Manage Relationships button on the toolbar of the table designer. For example, Figure 2.12 shows the relationship between the Customers and Orders tables.

click to expand
Figure 2.12: Relationship between the Customers and Orders table

The Customers and Orders tables are related through the CustomerID column. The CustomerID column in the Orders table is the foreign key. The relationship between the two tables is named FK_Orders_Customers.

Null Values

Databases must also provide the ability to handle values that are not set, or are otherwise unknown. Unknown values are called null values, and a column is defined as allowing or disallowing null values. When a column allows null values, that column is defined as null; otherwise it is defined as not-null. A not-null column in a row must always have value stored in it. If you tried to add a row but didn't supply a value to a not-null column, then the database would display an error and wouldn't add your new row.

Indexes

When looking for a particular topic in a book, you can either scan the whole book looking for your topic, or you can use the book's index to find the exact location of the topic directly. An index for a database table is similar in concept to a book index, except that database indexes are used to find specific rows in a table. The downside of indexes is that when a row is added to the table, additional time is required to update the index for the new row.

Generally, you should only create an index on a column when you find that you are retrieving a small number of rows from a table containing many rows. A good rule of thumb is that an index is useful when you expect any single query to retrieve 10 percent or less of the total rows in a table. This means that the candidate column for an index should be used to store a wide range of values. A good candidate for indexing would be a column containing a unique number for each record, while a poor candidate for indexing would be a column that contains only a small range of numeric codes such as 1, 2, 3, or 4. This consideration applies to all database types, not just numbers.

Note 

SQL Server automatically creates an index for the primary key column of a table.

Normally, the DBA is responsible for creating indexes, but as an application developer, you probably know more about your application than the DBA and will be able to recommend which columns are good candidates for indexing.

You can manage the indexes for a table from Enterprise Manager by selecting the table from the Tables node, clicking the right mouse button, and selecting All Tasks Manage Indexes. For example, Figure 2.13 shows the indexes for the Customers table. You can also manage indexes from the table designer by clicking the Manage Indexes/Keys button.

click to expand
Figure 2.13: Indexes for the Customers table

The Customers table has five indexes: one each on the CustomerID, City, CompanyName, PostalCode, and Region columns.

You'll learn how to add an index to a table in the "Creating an Index" section later.

Column Types

Each column in a table has a specific database type. This type is similar to the type of a variable in C#, except that a database type applies to the kind of value you can store in a table column. Table 2.3 lists the SQL Server database types.

Table 2.3: SQL SERVER DATABASE TYPES

TYPE

DESCRIPTION

bigint

Integer value from -263 (-9,223,372,036,854,775,808) to 263-1 (9,223,372,036,854,775,807).

int

Integer value from -231 (-2,147,483,648) to 231-1 (2,147,483,647).

smallint

Integer value from 215 (-32,768) to 215-1 (32,767).

tinyint

Integer value from 0 to 255.

bit

Integer value with either a 1 or 0 value.

decimal

Fixed precision and scale numeric value from -1038+1 to 1038-1.

numeric

Same as decimal.

money

Monetary data value from -263 (-922,337,203,685,477.5808) to 263-1 (922,337,203,685,477.5807), with an accuracy to one ten-thousandth of a monetary unit.

smallmoney

Monetary data value from -214,748.3648 to 214,748.3647, with an accuracy to one ten-thousandth of a monetary unit.

float

Floating-point value from -1.79E+308 to 1.79E+308.

real

Floating-point value from -3.40E + 38 to 3.40E + 38.

datetime

Date and time value from January 1, 1753, to December 31, 9999, with an accuracy of three-hundredths of a second (3.33 milliseconds).

smalldatetime

Date and time value from January 1, 1900 to June 6, 2079 with an accuracy of one minute.

char

Fixed-length non-Unicode characters with a maximum length of 8,000 characters.

varchar

Variable-length non-Unicode characters with a maximum of 8,000 characters.

text

Variable-length non-Unicode characters with a maximum length of 231-1 (2,147,483,647) characters.

nchar

Fixed-length Unicode characters with a maximum length of 4,000 characters.

nvarchar

Variable-length Unicode characters with a maximum length of 4,000 characters.

ntext

Variable-length Unicode characters with a maximum length of 230-1 (1,073,741,823) characters.

binary

Fixed-length binary data with a maximum length of 8,000 bytes.

varbinary

Variable-length binary data with a maximum length of 8,000 bytes.

image

Variable-length binary data with a maximum length of 231-1 (2,147,483,647) bytes.

cursor

Reference to a cursor, which is a set of rows.

sql_variant

Can store values of various SQL Server types except text, ntext, timestamp, and sql_variant.

table

Stores a set of rows.

timestamp

Unique binary number that is updated every time you modify a row. You can only define one timestamp column in a table.

uniqueidentifier

Globally unique identifier (GUID).

Okay, enough theory! Let's take a closer look at the Customers, Orders, Order Details, and Products tables.

The Customers Table

The Customers table contains rows that store the details of a company that might place orders with the Northwind Company. Figure 2.14 shows some of the rows and columns stored in the Customers table.

click to expand
Figure 2.14: Rows from the Customers table

As you can see, the first row displayed is for a customer with the name Alfreds Futterkiste; this name is stored in the CompanyName column of the Customers table.

The CustomerID for the first row is ALFKI, and as you can see, the CustomerID is unique for each row. As mentioned earlier, the primary key for the Customers table is the CustomerID column. If you tried to add a row with a primary key already used by a row, then the database would reject your new row. For example, if you tried to add a row to the Customers table with a CompanyID of ALFKI, then that row would be rejected because ALFKI is already used by the first row in the table.

Tip 

You can view the rows from a table yourself by selecting the table in Enterprise Manager, clicking the right mouse button, and selecting Open Table Return all rows. You'll learn more about viewing rows from tables later in the "Building Queries" section.

Definition of the Customers Table

Table 2.4 shows the definition for the columns of the Customers table. This table shows the column name, database type, length, and whether the column allows null values.

Table 2.4: DEFINITION FOR THE COLUMNS OF THE Customers TABLE

COLUMN NAME

DATABASE TYPE

LENGTH

ALLOWS NULL VALUES?

CustomerID

nchar

5

No

CompanyName

nvarchar

40

No

ContactName

nvarchar

30

Yes

ContactTitle

nvarchar

30

Yes

Address

nvarchar

60

Yes

City

nvarchar

15

Yes

Region

nvarchar

15

Yes

PostalCode

nvarchar

10

Yes

Country

nvarchar

15

Yes

Phone

nvarchar

24

Yes

Fax

nvarchar

24

Yes

In the next section, you'll learn about the Orders table.

The Orders Table

The Orders table contains rows that store the orders placed by customer. Figure 2.15 shows some of the rows and columns stored in the Orders table.

click to expand
Figure 2.15: Rows from the Orders table

The primary key for the Orders table is the OrderID column, meaning that the value for this column must be unique for each row. If you look closely at the first six rows in the Orders table, you'll see that the CustomerID column is equal to ALFKI, which is the same as the CustomerID column for the first row in the Customers table shown earlier in Figure 2.12.

You can now see how foreign keys relate information. The CustomerID column of the Orders table is a foreign key that references the CustomerID column of the Customers table. In this example, the Orders table is the child table, and the Customers table is the parent table. You can think of the foreign key as a pointer from the Orders table to the Customers table. Table 2.5 shows the definition for the columns of the Orders table.

Table 2.5: DEFINITION FOR THE COLUMNS OF THE Orders TABLE

COLUMN NAME

DATABASE TYPE

LENGTH

ALLOWS NULL VALUES?

OrderID

int

4

No

CustomerID

nchar

5

Yes

EmployeeID

int

4

Yes

OrderDate

datetime

8

Yes

RequiredDate

datetime

8

Yes

ShippedDate

datetime

8

Yes

ShipVia

int

4

Yes

Freight

money

8

Yes

ShipName

nvarchar

40

Yes

ShipAddress

nvarchar

60

Yes

ShipCity

nvarchar

15

Yes

ShipRegion

nvarchar

15

Yes

ShipPostalCode

nvarchar

10

Yes

ShipCountry

nvarchar

15

Yes

In the next section, you'll learn about the Order Details table.

The Order Details Table

The Order Details table contains rows that store the details of each order. In Figure 2.16, I've restricted the rows retrieved from the Order Details table to those where the OrderID column is equal to 10643 (this is the same as the OrderID column for the first row in the Orders table shown earlier in Figure 2.15).

click to expand
Figure 2.16: Restricted rows from the Order Details table

The primary key for the Order Details table is the combination of the OrderID and CustomerID columns, meaning that the combination of the values in these two columns must be unique for each row.

Also, the OrderID column of the Order Details table is a foreign key that references the OrderID column of the Orders table. The ProductID column of the Order Details table is a foreign key that references the ProductID column of the Products table. Table 2.6 shows the definition for the columns of the Order Details table. You'll learn about the Products table next.

Table 2.6: DEFINITION FOR THE COLUMNS OF THE Order Details TABLE

COLUMN NAME

DATABASE TYPE

LENGTH

ALLOWS NULL VALUES?

OrderID

int

4

Yes

ProductID

int

4

Yes

UnitPrice

money

8

Yes

Quantity

smallint

2

Yes

Discount

real

4

Yes

The Products Table

The Products table contains rows that store the details of each product sold by the Northwind Company. In Figure 2.17, I've restricted the rows retrieved from the Products table to those where the ProductID column is equal to 22, 39, and 46 (these are the same as the values for the ProductID column for the rows in the Order Details table shown earlier in Figure 2.16).

click to expand
Figure 2.17: Restricted rows from the Products table

The primary key for the Products table is the ProductID column. The CategoryID column of the Products table is a foreign key that references the CategoryID column of the Categories table. The Categories table contains the various categories of products.

The SupplierID column of the Products table is a foreign key that references the SupplierID column of the Suppliers table. The Suppliers table contains the suppliers of products to the Northwind Company. Table 2.7 shows the definition for the columns of the Products table.

Table 2.7: DEFINITION FOR THE COLUMNS OF THE Products TABLE

COLUMN NAME

DATABASE TYPE

LENGTH

ALLOWS NULL VALUES?

ProductID

int

4

No

ProductName

nvarchar

40

No

SupplierID

int

4

Yes

CategoryID

int

4

Yes

QuantityPerUnit

nvarchar

20

Yes

UnitPrice

money

8

Yes

UnitsInStock

smallint

2

Yes

UnitsOnOrder

smallint

2

Yes

ReorderLevel

smallint

2

Yes

Discontinued

bit

1

Yes

In the next section, you'll learn how to build queries to retrieve rows from tables.




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net