Chapter 4. Designing the Database

Data. Databases. It just kind of makes sense. If you have data, you need to put it somewhere. And what better place to put it than in a “data” base?

Just to make sure I had all the “bases” covered, I did a quick search on the Internet for a useful definition. What a shock. According to virtually every web site I found, a database is “a collection of data organized for easy retrieval by a computer.” With a definition like that, pretty much everything I put on my system is stored in a database. All of my disk files are organized for easy access. My saved emails can be sorted by subject or date received or sender, so they must be in a database, too. Even this document can be searched and sorted in any manner I wish. Is it a database?

Relational Databases

Perhaps that definition is too broad. These days, when we think of “database,” it’s generally a relational database system. Such databases are built on the “relational model” designed by Edgar Codd of IBM. In 1970, he issued “A Relational Model of Data for Large Shared Data Banks,” the seminal paper on relational modeling, and later expanded on the basic concepts with C. J. Date, another “real programmer.” Upon reading that 1970 paper—and if you have a free afternoon, you would really benefit from spending time with your family or friends rather than reading that paper—you will enter a world of n-tuples, domains, and expressible sets. Fortunately, you don’t need to know anything about these terms to use relational database systems.

The relational databases that most programmers use collect data in tables, each of which stores of specific set of unordered records. For convenience, tables are presented as a grid of data values, with each row representing a single record, and each column representing a consistent field that appears in each record. Table 4-1 presents a table of orders, with a separate record for each line item of the order.

Table 4-1. Boy, a Lot of People Drink Coffee and Tea

Putting all of your information in a table is really convenient. The important data appears at a glance in a nice and orderly arrangement, and it’s easy to sort the results based on a particular column. Unfortunately, this table of orders has a lot of repetition. Customer names and product names both repeat multiple times. Also, although the product ID “BEV01COF” indicates coffee, one of the lines lists it as “Tea.” There are a few other problems inherent in data that’s placed in a single flat file database table.

Mr. Codd, the brilliant computer scientist that he was, saw these problems, too. But instead of just sitting around and complaining about them like I do, he came up with a solution: normalization. By breaking the data into separate tables with data subsets, assigning a unique identifier to each record/row in every table (a primary key), and making a few other adjustments, the data could be “normalized” for both processing efficiency and data integrity. For the sample orders in Table 4-1, the data could be normalized into three separate tables: one for order line items, one for customers, and one for products (see Table 4-2, Table 4-3, and Table 4-4, respectively). In each table, I’ve put an asterisk next to the column title that acts as the primary key column.

Table 4-2. The Table of Customers

Table 4-3. The Table of Products

Table 4-4. The Table of Order Line Items

To get combined results from multiple tables at once, join (or link) their matching fields. For instance, you can link the Customer ID field in the table of line items with the matching Customer ID primary key field in the table of customers. Once joined, the details for a single combined line item record can be presented with the matching full customer name. It’s the same for joins with any two tables that have linkable fields. Figure 4-1 shows the relationships between the customer, product, and order line tables.

Figure 4-1. Three tables, and yet they work as one

To join tables together, relational databases implement query languages that allow you to manipulate the data using relational algebra (from which the term “relational database” derives). The most popular of these languages, SQL, uses simple English-like sentences to join, order, summarize, and retrieve just the data values you need. The primary statement, SELECT, provides basic data selection and retrieval features. Three other common statements, INSERT, UPDATE, and DELETE, let you manipulate the records stored in each table. Together, these four statements make up the primary data manipulation language (DML) commands of SQL. SQL also includes data definition language (DDL) statements that let you design the tables used to hold the data, as well as other database features. I’ll show examples of various SQL statements later in this chapter.

Vendor-specific systems, such as Microsoft’s SQL Server, Oracle’s “Oracle,” Microsoft’s Access, and IBM’s DB2, extend these core DDL and DML features through additional data analysis and management tools. They also battle each other over important features such as data replication, crash-proof data integrity, the speed at which complex queries return the requested results, and who has the biggest private jet.

SQL Server 2005

Microsoft’s primary business-level database tool is SQL Server. Although it began its life as a derivative of Sybase (another relational database), it has been given the Microsoft touch. Unlike Access (Microsoft’s other relational database product), SQL Server includes advanced data management and analysis features, and a nifty price tag to go along with those features. Although Microsoft was somewhat late in joining the relational database game, it has done a pretty good job at playing catch-up. Oracle still gets high marks for at least its perception of being the most robust, the most stable, and the most platform-independent of the various players. But SQL Server scores big as well, especially with its somewhat lower costs, and its more intuitive visual tools.

Originally, Microsoft touted SQL Server as a business-minded tool for business-minded people with their business-minded agendas and their business-minded three-piece poly-knit double-breasted suits, and it is still viewed in this way. But Microsoft is increasingly identifying the database as a development tool, especially with the 2005 release. It was no coincidence that Microsoft chose to debut its newest version of SQL Server on November 7, 2005, the same day as the release of Visual Studio 2005. All flavors of Visual Studio now include some version of SQL Server—even the low-end Visual Studio 2005 Express Edition products have access to a SQL Server 2005 Express Edition complement. (As of this writing, it was available at no cost from Microsoft’s web site.) And it’s a two-way relationship between the products: You could always use SQL Server data in your .NET applications, but SQL Server 2005 now allows you to craft embedded stored procedures using .NET code, along with the native and more traditional T-SQL scripting language.

SQL Server, as the name implies, is a “server” product. It runs in the background on a system and communicates with you, the user, by having you first establish a standard network connection with the server engine. This is true even if the SQL Server engine runs on your own workstation. Watching a server product is about as exciting as reading some of those other Visual Basic 2005 tutorial books that you wisely avoided, so Microsoft provides various client tools that let you manage databases, tables, and other relational database properties. SQL Server Management Studio is the standard enterprise-level client tool for managing SQL Server databases. For SQL Server 2005 Express Edition, Microsoft supplies a reduced yet friendlier tool, the SQL Server Management Studio Express (see Figure 4-2). This tool lets you manage databases and process DDL and DML statements. Management Studio Express is not included in SQL Server 2005 Express Edition; you must download or obtain it separately from Microsoft. As of this writing, it is available at no cost from Microsoft’s web site.

Figure 4-2. SQL Server Management Studio Express

Although Microsoft continues to update and sell Microsoft Access, it is recommending more and more that professional developers use and distribute databases in SQL Server format. Microsoft will even permit you to redistribute SQL Server 2005 Express Edition with your application. To do this, you must first obtain a “SQL Server 2005 Express Edition redistribution license” from Microsoft. Fortunately, it’s free and can be had for the asking from the SQL Server 2005 Express Edition web site, http://www.microsoft.com/sql/express.

SQL: Structured Query Language

Conducting business in Japan is pretty easy—once you know the language. The same is true of SQL Server: It’s pretty easy to manipulate and access data, once you know the language. In this case, the language is SQL, or Structured Query Language. Originally developed by IBM, SQL has since become a standard across the database industry. Well, kind of. As with America and England, Microsoft’s SQL Server and Oracle’s “Oracle” are two relational databases divided by a common language. The core parts of the SQL language are pretty consistent between vendors, but each supplier adds a lot of extra features and syntax variations designed by Edgar Codd wannabes.

This section describes those DDL and DML statements that will be most useful in our development of the Library program. You’ll be glad to know that SQL isn’t too picky about the formatting of the various statements. Upper- and lowercase distinctions are ignored; SELECT is the same as select is the same as SeLeCt. (Traditional SQL code is mostly uppercase. I use uppercase for all keywords, and mixed case for tables, fields, and other custom items. Whatever you choose, consistency is important.) Also, employ whitespace as you see fit. You can put statements on one gigantic line, or put every word on a separate line. The only time whitespace and case matters is in the actual data text strings; whatever you type, that’s how it stays.

SQL statements normally end with a semicolon, but some tools do not require you to include the semicolon, and other tools require that you exclude it. When using the SQL Server visual client tools (Management Studio and Management Studio Express), semicolons are optional, but it’s a good idea to include them when you are using multiple statements together, one after another. SQL statements used in Visual Basic code never include semicolons.

Later, when you look at a SQL script I wrote, you will see the word “GO” from time to time. In SQL Server, this command says, “For all of the other statements that appeared so far, go ahead and process them now.”

DDL Statements

This may come as a shock to you, but before you can store any data in a table, you have to create the table. SQL has just the tool to do this: the CREATE TABLE statement. It’s one of the many DDL statements. The basic syntax is pretty straightforward.

Just fill in the parts, and you’re ready to populate (data, that is). Table and field names are built from letters and digits; you can include spaces and some other special characters, but it makes for difficult coding later on. Each vendor has its own collection of data types; I’ll stick with the SQL Server versions here. The options let you specify things such as whether the field requires data or not, whether it represents the table’s primary key, and other similar constraints. Extensions to the syntax let you set up constraints that apply to the entire table, indexes (which let you sort or search a specific column more quickly), and data storage specifics.

Here’s a sample CREATE TABLE statement that could be used for the table of order line items (refer to Table 4-4).

The IDENTITY keyword lets SQL Server take charge of filling the RecordID field with data; it will use a sequential counter to supply a unique RecordID value with each new record. The PRIMARY KEY clause identifies the RecordID field as the unique identifying value for each record in the table. The bigint and smallint data types indicate appropriately sized integer fields, while the varchar type provides space for text, up to the maximum length specified in the parentheses (20 characters). The REFERENCES option clause identifies a relationship between this LineItems table and another table named Customers; values in the LineItems.CustomerID field match the key values from the Customers.CustomerID field. (Note the “dot” syntax to separate table and field names. It shows up everywhere in SQL.) References between tables are also known as foreign references.

If you need to make structure or option changes to a table or its fields after it is created, SQL includes an ALTER TABLE statement that can change almost everything in the table. Additionally, there is a related DROP TABLE statement used to get rid of a table and all of its data. You might want to avoid this statement on live production data as the users tend to get a bit irritable when their data suddenly disappears off the surface of the earth.

Table 4-5 summarizes the available data types used in SQL Server.

Table 4-5. SQL Server Data Types

DML Statements

Although DDL statements are powerful, they aren’t used that much. Once you create your database objects, there’s not much call for tinkering. The DML (Data Manipulation Language) statements are more useful for everyday data surfing.

The INSERT statement adds data records to a table. Data is added to a table one record at a time. (A variation of INSERT lets you insert multiple records, but those records must come from another existing table source.) To use the INSERT statement, specify the destination table and fields, and then the individual values to put into each field. One data value corresponds to each specified data column name.

Assuming this statement goes with the CREATE TABLE statement written earlier, this insert action will add a new record to the LineItems table with five new fields—four specified fields, plus the primary key automatically added to the RecordID field (because it was marked as IDENTITY). SQL Server also does a variety of data integrity checks on your behalf. Each data field you add must be of the right data type, but you already expected that. Because we designed the CustomerID field to be a reference to the Customer table, the insert will fail if customer ’BW3’ does not already exist in the Customer table.

Numeric literals can be included in your SQL statements as needed without any additional qualification. String literals are always surrounded by single quotes, as is done for the customer and product IDs in this INSERT statement. If you need to include single quotes in the literal, enter them twice.

'John O''Sullivan'

Surround literal date and time values with single quotes.

'7-Nov-2005'

Such date and time values accept any recognized format, although you should use a format that is not easy for SQL Server to misinterpret.

Many field types support an “unassigned” value, a value that indicates that the field contains no data at all. Such a value is known as the “null” value, and is specified in SQL Server using the “NULL” keyword. You cannot assign NULL to primary key fields, or to any field marked with the “NOT NULL” option.

To remove a previously added record, use the DELETE statement.

DELETE FROM LineItems WHERE RecordID = 92231

The DELETE statement includes a “WHERE” clause (the “WHERE RecordID = 92231” part). WHERE clauses let you indicate one or more records in a table by making comparisons with data fields. Your WHERE clauses can include AND and OR keywords to join multiple conditions, and parentheses for grouping.

DELETE FROM LineItems WHERE OrderID = 10001
   AND ProductID = 'BRD05RYE'

Such a DELETE statement may delete zero, one, or 1,000 records, so precision in the WHERE clause is important. To delete all records in the table, exclude the WHERE clause altogether.

DELETE FROM LineItems

The UPDATE statement also uses a WHERE clause to modify values in existing table records.

UPDATE LineItems SET Quantity = 4
   WHERE RecordID = 92231

Assignments are made to fields with the SET clause; put the field name (Quantity) on the left side of the equals sign, and the new value on the right (4). To assign multiple values at once, separate each assignment with a comma. You can also include formulas and calculations.

As with the DELETE statement, the UPDATE statement may update zero, one, or many records based on which records match the WHERE clause.

The final DML statement, and the one most often used, is SELECT.

SELECT ProductID, Quantity FROM LineItems
   WHERE RecordID = 92231

SELECT scans a table (LineItems), looking for all records matching a given criteria (RecordID = 92231), and returns a smaller table that contains just the indicated fields (ProductID and Quantity) for the matching records. The most basic query returns all rows and columns.

SELECT * FROM LineItems

This returns all records from the table in no particular order. The asterisk (*) means “include all fields.”

The optional ORDER BY clause returns the results in a specific order.

This query returns all records that have a Quantity field value of more than five, and sorts the results first by the ProductID column (in ascending order), and then by the numeric quantity (in descending order, specified with DESC).

Aggregate functions and grouping features let you summarize results from the larger set of data. The following query documents the total ordered quantity for each product in the table.

SELECT ProductID, SUM(Quantity) FROM LineItems
   GROUP BY ProductID

You can use joins to link the data from two or more distinct tables. The following query joins the LineItems and Customer tables on their matching CustomerID columns. This SELECT statement also demonstrates the use of table abbreviations (the LI and CU prefixes) added through the AS clauses; they aren’t usually necessary, but they can help make a complex query more readable.

This table uses an “inner join,” one of the five main types of joins, each of which returns different sets of records based on the relationship between the first (left) and second (right) table in the join.

• Inner Join. Returns only those records where there is a match in the linked fields. This type of join uses the “INNER JOIN” keywords.
• Left Outer Join. Returns every record from the left table, and only those records from the right table where there is a match in the linked fields. If a left table record doesn’t have a match, it acts as if all the fields in the right table for that record contain NULL values. This type of join uses the “LEFT JOIN” keywords. One use might be to join the Product and LineItems tables. You could return a list of the full product name for all available products, plus the total quantity ordered for each one. By putting the Product table on the left of a left outer join, the query would return all product names, even if that product had never been ordered (and didn’t appear in the LineItems table).
• Right Outer Join. This works just like a left outer join, but all records from the right table are returned, and just the left table records that have a match. This type of join uses the “RIGHT JOIN” keywords.
• Full Outer Join. Returns all records from the left and right tables, whether they have matches or not. When there is a match, it is reflected in the results. This type of join uses the “FULL JOIN” keywords.
• Cross Join. Also called a Cartesian Join. Returns every possible combination of left and right records. This type of join uses the “CROSS JOIN” keywords.

Joining focuses on the relationship that two tables have. (This use of “relationship,” by the way, is not the basis for the term “relational database.”) Some tables exist in a “parent-child” relationship; one “parent” record has one or more dependent “child” records in another table. This is often true of orders; a single “order header” has multiple “line items.” This type of relationship is known as one-to-many, because one record is tied to many records in the other table. And the relationship is unidirectional; a given child record does not tie to multiple parent records.

A one-to-one relationship ties a single record in one table to a single record in another table. It’s pretty straightforward, and is often used to enhance the values found in the original record through a supplementary record in a second table.

In a many-to-many relationship, a single record in one table is associated with multiple records in a second table, and a single record in that second table is also associated with multiple records in the first table. A real-world example would be the relationship between teachers and students in a college setting. One teacher has multiple students in the classroom, but each student also has multiple teachers each semester. Practical implementations of many-to-many relationships actually require three tables: the two related tables, and a “go between” table that links them together. I will show you a sample of such a table in the “Project” section of this chapter.

Beyond Basic SQL

The sample statements I listed here scratch only the surface of the data manipulation possibilities available through SQL. But by now you should have noticed that SQL is remarkably English-like in syntax, much more than even Visual Basic. In fact, the original name for the language—SEQUEL—was an acronym for “Structured English Query Language.” As the SQL statements get more complex, they will look less and less like an eighth-grade essay, and more like random collections of English words.

The goal here is to introduce you to the basic structure of SQL statements. Most of the statements we will encounter in the Library Project will be no more complex than the samples included here. If you’re hungry for more, the “Books Online” component installed with SQL Server (a separate download for the Express Edition) has some pretty good usage documentation. There are also several good books available on the ins and outs of SQL, including vendor-specific dialects.

Using Databases in Visual Basic

There are a few different ways that Visual Basic can interact with data stored in a database.

• Use ADO.NET, the primary data access technology included in the .NET Framework, to interact with database-stored content. This is the method used throughout the Library program to interact with its database. ADO.NET is discussed in Chapter 10, “ADO.NET,” with examples of its use. I will also introduce ADO.NET-specific code into the Library Project in that chapter.
• Use the “data binding” features available in Visual Basic and Visual Studio. Binding establishes a connection between an on-screen data control or similar data-enabled object and content from a database. Code written for you by Microsoft takes care of all the communication work; you can even drag-and-drop these types of interactions. Although I will discuss data binding in Chapter 10 (because binding is based on ADO.NET), I tend to avoid it because it reduces the amount of control the programmer can exert on user data management. Data binding will not be used in the Library program.
• Extract the data from the database into a standard file, and use file manipulation features in Visual Basic to process the data. Hmm, that doesn’t seem very useful, but I have actually had to do it, especially in the old days when some proprietary databases could not interact easily with Visual Basic code.
• Each time you need some of the data, tell the user that somehow the data has been lost, and that it must be reentered immediately. If you have ever been curious to know what the inside of an unemployment office looks like, this could be your chance.

If you are a former Visual Basic 6.0 (or earlier) programmer, you may think that your knowledge of ADO will translate directly into ADO.NET development. Ha! You couldn’t be more wrong. Although the two data technologies share a partial name, the code written to use each method varies considerably. I will not discuss the older ADO technology at all in this book.

Documenting the Database

Technical content that describes the tables and fields in your application’s database represents the most important piece of documentation generated during your application’s lifetime. In fact, the need for good documentation is the basis for one of my core programming beliefs: Project documentation is as important, and sometimes more important, than source code.

You may think I’m joking about this. Although you will (hopefully) find a lot of humor in the pages of this book, this is something I don’t joke about. If you are developing an application that centers on database-stored user content, complete and accurate documentation of every table and field used in the database is a must. Any lack in this area will—not might, not perhaps, but will—lead to data integrity issues and a longer-than-necessary development timeline. Figure 4-3 puts it another way.

Figure 4-3. Any questions?

Why do I think that database documentation is even more important than user documentation or functional specifications? It’s because of the impact the document will have on the user’s data. If you have a documented database, you can make guesses about the functional specification, and probably come pretty close. If you lack user documentation, you can always write it when the program is done (as if there was any other way?). But if you lack database documentation, you are in for a world of hurt.

If you haven’t worked on large database projects before, you might not believe me. But I have. I once inherited an existing enterprise-wide database system written in Visual Basic 3.0. The source code was bad enough, but the associated undocumented 100-table database was a mish-mash of inconsistently stored data values. The confusing stored procedure code wasn’t much better. Because there wasn’t a clear set of documentation on each field, the six programmers who originally developed the system had each made their own decisions about what range of data would be allowed in each field, or about which fields were required or not.

Tracing back through the uncommented 100,000 lines of source code to determine what every field did was not fun, and it took a few months to complete it with accuracy. Because the customer had paid for and expected a stable and coherent system, most of the extra cost involved in replacing the documentation that should have been there in the first place was borne by my development group. Don’t let this happen to you!

Summary

Most Visual Basic applications target the business world, and are designed to interact with some sort of database. Understanding the database system used with your application is important; even more important is documenting the specific database features you incorporate into your application.

Because of the influence of relational databases and the SQL language on the database industry, it won’t be hard to find a lot of resources to assist you in crafting SQL statements and complex data analysis queries. The Library Project in this book uses SQL Server 2005, but because of the generally consistent use of the core SQL language features, the application could just as easily have used Oracle, Access, or any of a number of other relational databases.

Project

To assist in my development of Visual Basic database projects, I always write a “Technical Resource Kit” document before I begin actual coding of the application. The bulk of this word-processing document consists of the table- and field-level documentation for the application’s associated database. Also included are the formats for all configuration and custom data files, a map of the online help pages, and information about third-party products used in the application. Depending on the type of application, my expectations for the user, and the terms of any contract, I may supply none, some, or all of the Resource Kit’s content to the user community.

Let’s begin the Technical Resource Kit for the Library Project by designing and documenting the database tables to be used by the application. This Resource Kit appears in the book’s installation directory, in the Chapter 4 subdirectory, and contains the following three files.

• ACME Library Resource Kit.doc. A Microsoft Word version of the technical documentation for the project.
• ACME Library Resource Kit.pdf. A second copy of the Technical Resource Kit, this time in Adobe Acrobat (PDF) format.
• Database Creation Script.sql. A SQL Server database script used to build the actual tables and fields in the database.

Technical Resource Kit Content

This section includes a listing of the tables included in the Library database. Each table includes a general description to assist you in your understanding of the database structure. You will encounter all of these tables in successive chapters, along with associated source code, so don’t freak out if some table or field seems unknowable right now.

Security-Related Tables

Although patrons do not need to log in to the application to look up items in the database, administrators must log in before they can access enhanced features of the program. The following four tables manage the security credentials of each administrator. The application uses SQL Server or Windows-based security credentials only to access the database initially, not to restrict features.

Support Code Tables

Several tables exist simply to provide a list of values to other tables. In an application, these list tables often appear as the choices in a drop-down (“combo box”) list control.

Library Items

The tables in this section manage the actual inventory of items. Because a library may own more than one copy of a single item, these tables manage the “named item” and its individual “copies” separately.

Patron-Related Tables

The tables in this section define the actual patron records, and their relationship to item copies (when such copies are checked out by the patron).

Barcode-Related Tables

There are three levels of definition to create a barcode: (1) the sheet on which a grid of labels prints, (2) a single label on the sheet, and (3) the individual items that appear on each label. The three tables in this section define those three levels.

Other Miscellaneous Tables

Two additional tables provide support for features not handled through other tables.

Creating the Database

Adding the database to SQL Server is almost as easy as documenting it; in fact, it’s less typing. The CREATE TABLE statements are straightforward, and they all pretty much look the same. I’m going to show only a few of them here. The Database Creation Script.sql file in this book’s installation directory includes the full script content.

The instructions listed here are for SQL Server 2005 Management Studio Express. You can perform all of these tasks using SQL Server 2005 Management Studio, or even the command-line tools supplied with SQL Server, but the details of each step will vary. The same CREATE TABLE statements work with whichever tool you choose.

If you haven’t done so already, install SQL Server 2005 Express Edition (or whichever version of the database you will be using). SQL Server 2005 Management Studio Express is a separate product from SQL Server itself, so you must install that as well.

Most of the tables in the library project are simple data tables with a single primary key. Their code is straightforward. The Author table is a good example.

The fields included in each CREATE TABLE statement appear as a comma-delimited list, all enclosed in parentheses. Each field includes either a NULL or NOT NULL option that indicates whether NULL values may be used in that field or not. The PRIMARY KEY option automatically specifies NOT NULL.

Some fields create tables that link two other tables in a many-to-many relationship. One example is the GroupActivity table, which connects the GroupName table with the Activity table.

The Author table had a single primary key, so the PRIMARY KEY option could be attached directly to its ID field. Because the GroupActivity table has a two-field primary key (which is common in relational databases), the PRIMARY KEY option is specified as an entry all its own, with the key fields specified as a parentheses-enclosed comma-delimited list.

Earlier in the chapter, I showed how you could establish a reference to a field in another table by using the REFERENCES constraint as part of the CREATE TABLE statement. You can also establish them after the tables are already in place, as I do in the script. Here is the statement that establishes the link between the GroupActivity and GroupName tables:

Because I’ve already written the entire SQL script for you, I’ll just have you process it directly using Microsoft SQL Server 2005 Management Studio Express. (If you will be using the full version of SQL Server or some other management tool, the provided script will still work, although the step-by-step instructions will differ.) Before adding the tables, we need to create a database specific to the Library project. Start up Microsoft SQL Server 2005 Management Studio Express.

Figure 4-4. SQL Server 2005 Management Studio Express main form

To add a new database for the Library Project, right-click on the Database folder in the Object Explorer, and select New Database from the shortcut menu. On the New Database form that appears, enter “Library” in the Database Name field, and then click OK.

The Library database is a shell of a database; it doesn’t contain any tables or data yet. Let’s use the Database Creation Script.sql file from the book’s installation directory to generate the tables and initial data. In Management Studio Express, select the File Open File menu command, and locate the Database Creation Script.sql file. (You may be prompted to log in to SQL Server again.) Opening this file places its content in a new panel within Management Studio Express.

All that’s left to do is to process the script. In the toolbar area, make sure that “Library” is the selected database (see Figure 4-5). Then click the Execute toolbar button, or press the F5 key. It’s a small script with not a lot going on (at least from SQL Server’s point of view), so it should finish in just a few seconds.

Figure 4-5. If you don’t select “Library,” your tables will go somewhere else

That’s it! Close the script panel. Then, back in the Object Explorer, right-click on the Library database folder, and select Refresh from the menu. If you then expand the Library database branch and its Tables subbranch, you will see all of the tables created by the script (see Figure 4-6).

Figure 4-6. Partial list of database tables

With the database done, it’s time to start programming.