O'Reilly logo

Programming Visual Basic 2008 by Tim Patrick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Project

To assist in my development of Visual Basic database projects, I always write a "Technical Resource Kit" document before I begin the 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 datafiles, 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.

Activity

This table defines the features of the application that can be secured using group rights. These activities are linked with security groups (from the GroupName table) to establish the rights for a particular group.

Field

Type

Description

ID

bigint

Primary key. This key is not auto-generated; the value supplied matches internal values used within the Library application. Required.

FullName

varchar(50)

Descriptive name of this activity. Required.

The following activities are defined at this time:

  • 1—Manage authors and names

  • 2—Manage author and name types

  • 3—Manage copy status codes

  • 4—Manage media types

  • 5—Manage series

  • 6—Manage security groups

  • 7—Manage library materials

  • 8—Manage patrons

  • 9—Manage publishers

  • 10—Manage system values

  • 11—Manage administrative users

  • 12—Process and accept fees

  • 13—Manage locations

  • 14—Check out library items

  • 15—Check in library items

  • 16—Access administrative features

  • 17—Perform daily processing

  • 18—Run system reports

  • 19—Access patrons without patron password

  • 20—Manage bar codes

  • 21—Manage holidays

  • 22—Manage patron groups

  • 23—View administrative patron messages

GroupName

Each record in this table defines a single security group. Librarians and other administrators each belong to a single security group.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this group. Required.

GroupActivity

This table connects records in the Activity table to records in the GroupName table (a many-to-many relationship) to establish the activities a security group can perform.

Field

Type

Description

GroupID

bigint

Primary key. The associated security group. Foreign reference to GroupName.ID. Required.

ActivityID

bigint

Primary key. The activity that members of the associated security group can perform. Foreign reference to Activity.ID. Required.

UserName

This table contains the actual records for each librarian or administrator. Each record includes the user's password and security group setting.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this user, administrator, or librarian. Required.

LoginID

varchar(20)

User ID that gives this user access to the system. It is entered into the Library program's "login" form, along with the password, to gain access to enhanced features. Required.

Password

varchar(20)

The password for this user, in an encrypted format. Optional.

Active

bit

Is this user allowed to access the system? 0 for False, 1 for True. Required.

GroupID

bigint

To which security group does this user belong? Foreign reference to GroupName.ID. Required.

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") control.

CodeAuthorType

In the Library program, the word author is a generic term used for authors, illustrators, editors, and any other similar contributor to an item in the library's inventory. This table lets you define those roles.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this type of author or contributor. Required.

CodeCopyStatus

Copy status codes include things like "circulating," "being repaired," and any other primary status the library wishes to set. The checked-in or checked-out status is handled through other features, as is the flag that indicates whether an item is a reference item.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this status entry. Required.

CodeLocation

Physical locations where library items are stored. This could be separate sites, or rooms or areas within a common location.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this location. Required.

LastProcessing

datetime

The date when daily processing was last done for this location. If NULL, processing has not yet been done. Optional.

CodeMediaType

Types of media, such as books, magazines, videos, CDs, etc.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this media type. Required.

CheckoutDays

smallint

Number of days for which items in this type can be checked out, before renewal. Required.

RenewDays

smallint

Number of days to add to the original checkout period for a renewal of items within this type. Required.

RenewTimes

smallint

Maximum number of times the item can be renewed by a patron before it must be returned. Required.

DailyFine

money

Amount charged per day for an overdue item of this type. Required.

CodePatronGroup

Categories of groups into which patrons are placed. These are not security groups, but general groups for reporting purposes. This was added to support grouping of patrons by units within a company, or by class/grade within a school library setting.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this patron group. Required.

CodeSeries

Some items appear as part of a larger series or collection. This table defines the collection and series names.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this series or collection. Required.

Library items

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

NamedItem

A library item, such as a book, CD, or magazine. This table represents a general item, and not the actual copy of the item.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

Title

varchar(150)

Title of this item. Required.

Subtitle

varchar(150)

Subtitle of this item. Optional.

Description

varchar(max)

Full description of this item. Optional.

Edition

varchar(10)

Edition number for this item. Optional.

Publisher

bigint

This item's publisher. Foreign reference to Publisher.ID. Optional.

Dewey

varchar(20)

Dewey decimal number. Use / for line breaks. Optional.

LC

varchar(25)

Library of Congress number. Use / for line breaks. Optional.

ISxN

varchar(20)

ISBN, ISSN, or other standardized number of this item. Optional.

LCCN

varchar(12)

Library of Congress control number. Optional.

Copyright

smallint

Year of original copyright, or of believed original copyright. Optional.

Series

bigint

The series or collection in which this item appears. Foreign reference to CodeSeries.ID. Optional.

MediaType

bigint

The media classification of this item. Foreign reference to CodeMediaType.ID. Required.

OutOfPrint

bit

Is this title out of print? 0 for False, 1 for True. Required.

ItemCopy

A single copy of a named item. Separate copies of the same item will appear as separate records in this table.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

ItemID

bigint

The related named item record. Foreign reference to NamedItem.ID. Required.

CopyNumber

smallint

Numbered position of this item within the set of copies for a named item. Required, and unique among items with the same ItemID field value.

Description

varchar(max)

Comments specific to this copy of the item. Optional.

Available

bit

Is this copy available for checkout or circulation? 0 for False, 1 for True. Required.

Missing

bit

Has this copy been reported missing? 0 for False, 1 for True. Required.

Reference

bit

Is this a reference copy? 0 for False, 1 for True. Required.

Condition

varchar(30)

Any comments relevant to the condition of this copy. Optional.

Acquired

datetime

Date this copy was acquired by the library. Optional.

Cost

money

Value of this item, either original or replacement value. Optional.

Status

bigint

The general status of this copy. Foreign reference to CodeCopyStatus.ID. Required.

Barcode

varchar(20)

Bar code found on the copy. At this time, only numeric bar codes are supported. Optional.

Location

bigint

The site or room location of this item. Foreign reference to CodeLocation.ID. Optional.

Publisher

An organization that publishes books or some other type of media.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(100)

Name of the publisher. Required.

WebSite

varchar(255)

URL for this publisher's web site. Optional.

Author

Someone who writes, edits, illustrates, or in some other way contributes to a book or media item. In all cases, when the term author appears in this table, it refers to anyone who contributes to the item.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

LastName

varchar(50)

Last name of this author. Required.

FirstName

varchar(30)

First name of this author. Optional.

MiddleName

varchar(30)

Middle name or initial of this author. Optional.

Suffix

varchar(10)

Name suffix, such as "Jr." Optional.

BirthYear

smallint

Year of birth. Use negative numbers for BC. Optional.

DeathYear

smallint

Year of death. Use negative numbers for BC. Optional.

Comments

varchar(250)

Miscellaneous comments about this author. Optional.

ItemAuthor

An author, editor, and so on, for a specific named item. This table establishes a many-to-many relationship between the NamedItem and Author tables.

Field

Type

Description

ItemID

bigint

Primary key. The associated named item. Foreign reference to NamedItem.ID. Required.

AuthorID

bigint

Primary key. The author associated with the named item. Foreign reference to Author.ID. Required.

Sequence

smallint

Relative order of this author among the authors for this named item. Authors with smaller numbers appear first. Required.

AuthorType

bigint

The specific type of contribution given by this author for this named item. Foreign reference to CodeAuthorType.ID. Required.

Keyword

Custom words that can be applied to named items to make searching easier.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this keyword. Required.

ItemKeyword

Connects a keyword with a named item through a many-to-many relationship between the NamedItem and Keyword tables.

Field

Type

Description

ItemID

bigint

Primary key. The associated named item. Foreign reference to NamedItem.ID. Required.

KeywordID

bigint

Primary key. The keyword to associate with the named item. Foreign reference to Keyword.ID. Required.

Subject

Subject headings used to classify named items.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(150)

Name of this subject. Required.

ItemSubject

Connects a subject with a named item through a many-to-many relationship between the NamedItem and Subject tables.

Field

Type

Description

ItemID

bigint

Primary key. The associated named item. Foreign reference to NamedItem.ID. Required.

SubjectID

bigint

Primary key. The subject to associate with the named item. Foreign reference to Subject.ID. Required.

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).

Patron

An identified library user. Patrons usually have checkout privileges.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

LastName

varchar(30)

Last name of this patron. Required.

FirstName

varchar(30)

First name of this patron. Required.

LastActivity

datetime

Date of last checkout, renewal, or return. Optional.

Active

bit

Is this an active patron? 0 for False, 1 for True. Required.

Comments

varchar(max)

Any comments associated with this patron. Optional.

AdminMessage

varchar(500)

Comments that are displayed to administrative users when the patron's record is accessed. Optional.

Barcode

varchar(20)

Bar code found on this patron's library card. At this time, only numeric bar codes are supported. Optional.

Password

varchar(20)

Patron's password, in an encrypted format. Required.

Email

varchar(100)

Patron's email address. Optional.

Phone

varchar(20)

Patron's phone number. Optional.

Address

varchar(50)

Patron's street address. Optional.

City

varchar(20)

Patron's city. Optional.

State

varchar(2)

Patron's state abbreviation. Optional.

Postal

varchar(10)

Patron's postal code. Optional.

PatronGroup

bigint

The group in which this patron appears. Foreign reference to CodePatronGroup.ID. Optional.

PatronCopy

This table manages item copies currently checked out by a patron, or item copies that were previously checked out and have since been returned.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

Patron

bigint

The associated patron. Foreign reference to Patron.ID. Required.

ItemCopy

bigint

The item copy currently or previously checked out by the patron. Foreign reference to ItemCopy.ID. Required.

CheckOut

datetime

The date when this item copy was initially checked out. Required.

Renewal

smallint

The number of times this item copy has been renewed. Set to 0 when the item copy is first checked out. Required.

DueDate

datetime

Current due date for this item copy. Required.

CheckIn

datetime

The date when this item copy was returned. Optional.

Returned

bit

Has the item copy been returned? 0 for False, 1 for True. Required.

Missing

bit

Is the item copy missing and considered lost? 0 for False, 1 for True. Required.

Fine

money

Total fine accumulated for this item copy. Defaults to 0.00. An administrator may reduce an accumulated fine. Required.

Paid

money

Total amount paid (in fees) for this item copy. Required.

ProcessDate

datetime

When an item copy is processed for overdue fines, this field contains the last date for which processing was done. Optional.

PatronPayment

Fines, payments, and dismissals on a patron copy record. Overdue fines are not recorded in this table, but administrator-initiated fines due to charges for missing items are recorded here.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

PatronCopy

bigint

The associated item checked out by the patron. Foreign reference to PatronCopy.ID. Required.

EntryDate

datetime

Date and time when this entry was recorded. Required.

EntryType

varchar(1)

The type of payment entry. Required. The possible values are:

  • P = The patron made a payment.

  • F = A fine (other than a standard overdue fine) was imposed by an administrator.

  • D = A portion (or all) of the fine was dismissed.

  • R = A refund was given to the patron due to overpayment.

Amount

money

The amount associated with this entry. The value is always positive. Required.

Comment

varchar(50)

A short comment about this entry. Optional.

UserID

bigint

The user who added this payment event. Foreign reference to UserName.ID. Optional.

Bar code-related tables

There are three levels of definition to create a bar code: (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.

BarcodeSheet

Describes the template for a single page of bar code labels.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this sheet template. Required.

UnitType

varchar(1)

Units used in the various measurements found in most fields in this record. Required.

  • I = Inches

  • C = Centimeters

  • P = Points

  • T = Twips

PageWidth

decimal(10,4)

Width of the entire page. Required.

PageHeight

decimal(10,4)

Height of the entire page. Required.

MarginLeft

decimal(10,4)

Left border, up to the edge of the printable label area. Required.

MarginRight

decimal(10,4)

Right border, up to the edge of the printable label area. Required.

MarginTop

decimal(10,4)

Top border, up to the edge of the printable label area. Required.

MarginBottom

decimal(10,4)

Bottom border, up to the edge of the printable label area. Required.

IntraColumn

decimal(10,4)

The width of the blank area between label columns. Required.

IntraRow

decimal(10,4)

The height of the blank area between label rows. Required.

ColumnsCount

smallint

The number of label columns on this template. Required.

RowsCount

smallint

The number of label rows on this template. Required.

BarcodeLabel

Describes the template for a single label on a bar code sheet. Any number of labels may be on a single sheet, but they all have the same shape and format.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

FullName

varchar(50)

Name of this label template. Required.

BarcodeSheet

bigint

The sheet template on which this label template appears. Foreign reference to BarcodeSheet.ID. Required.

UnitType

varchar(1)

Units used in the various measurements found in most fields in this record. Required.

  • I = Inches

  • C = Centimeters

  • P = Points

  • T = Twips

BarcodeLabelItem

Describes a single item as found on a bar code label. Items include static and generated text, lines, rectangles, and generated bar codes.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

Priority

smallint

Identifies the order in which items on the label are printed. Lower numbers are printed first. Required.

BarcodeLabel

bigint

The label template on which this item appears. Foreign reference to BarcodeLabel.ID. Required.

ItemType

varchar(1)

What type of item does this record represent? Required.

  • T = Static text

  • B = Bar code

  • N = Bar code number

  • L = Line

  • R = Rectangle

PosLeft

decimal(10,4)

Left edge of the item relative to the left edge of the label. Measured according to the related BarcodeLabel.UnitType field. Required.

PosTop

decimal(10,4)

Top edge of the item relative to the top edge of the label. Measured according to the related BarcodeLabel.UnitType field. Required.

PosWidth

decimal(10,4)

Width of the item, or of the box in which the item is drawn. For lines, this is the x coordinate of the endpoint. Measured according to the related BarcodeLabel.UnitType field. Required.

PosHeight

decimal(10,4)

Height of the item, or of the box in which the item is drawn. For lines, this is the y coordinate of the endpoint. Measured according to the related BarcodeLabel.UnitType field. Required.

Rotation

smallint

Rotation angle, in degrees, of the box in which the item is drawn. Zero (0) equals no angle, and increasing angles proceed clockwise. Ranges from 0 to 359. Only used when ItemType is T, B, N, or R. Optional.

FontName

varchar(50)

The name of the font used to write the text. Valid only when ItemType is T or N. Optional.

FontSize

decimal(10,4)

The size of the font used to write the text. Valid only when ItemType is T, B, or N. Optional.

StaticText

varchar(100)

The static text to display on the label. Valid only when ItemType is T. Optional.

FontStyle

varchar(4)

The style of the font text. May be any combination of the following four codes:

  • B = Bold

  • I = Italic

  • U = Underline

  • K = Strikeout

Leave this field NULL to use the normal style. Valid only when ItemType is T or N. Optional.

Color1

bigint

The main color of the text, bar code, or line. When printing a rectangle, this is the border color. If NULL, black is used. A standard Windows 32-bit RGB color value. Optional.

Color2

bigint

The fill color when printing a rectangle. If NULL, white is used. A standard Windows 32-bit RGB color value. Optional.

Alignment

smallint

The alignment of the text within the bounding box. Valid only when ItemType is T, B, or N.

  • 1 = Align in top-left corner of box

  • 2 = Align in top-center area of box

  • 4 = Align in top-right corner of box

  • 16 = Align in middle-left area of box

  • 32 = Align in middle-center area of box

  • 64 = Align in middle-right area of box

  • 256 = Align in bottom-left corner of box

  • 512 = Align in bottom-center area of box

  • 1024 = Align in bottom-right corner of box

PadDigits

smallint

The number of digits in which to pad the bar code number. Set to zero (0) to ignore padding. Ranges from 0 to 20. If the bar code length is less than the specified number of digits, it is padded on the left with zeros. Applies only to ItemTypes of B and N.

Other miscellaneous tables

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

Holiday

When checking out an item to a patron, the return date should not fall on a holiday (or any day that the library is closed) since the patron might not have a way to return the book on the day it's due. This table defines one-time and recurring holidays.

Field

Type

Description

ID

bigint

Primary key, automatically assigned. Required.

FullName

varchar(50)

Name of this holiday. Not necessarily unique. Required.

EntryType

varchar(1)

The type of entry. Required. From the following list:

  • A = Annual (as in "every December 25")

  • E = Weekly (as in "every Sunday")

  • O = One-time (as in "2/16/2004 is President's Day")

EntryDetail

varchar(10)

Entry-type-specific detail. Required. Differs for each entry type.

Entry type Detail value

A Month and day in "mm/dd" format

E Single digit: 1 = Sunday through 7 = Saturday

O Date in "yyyy/mm/dd" format

SystemValue

This table stores miscellaneous enterprise-wide settings that apply to every workstation. Local workstation-specific settings are stored on each machine, not in the database.

Field

Type

Description

ID

bigint

Primary key; automatically assigned. Required.

ValueName

varchar(50)

Name of this value. Required.

ValueData

varchar(100)

Information associated with this entry. Optional.

The following system values are defined at this time. The name of the code appears in the ValueName field. The corresponding value appears in the ValueData field.

BarcodeCode39

Is the specified bar code in "code 39" or "code 3 of 9" format? If so, an asterisk will be placed before and after the bar code number before it is printed on a label. Use a value of 0 for False or any nonzero value for True (−1 is preferred). If missing or NULL, False is assumed.

BarcodeFont

The name of the font used to print bar codes. This font must be installed on any workstation that displays or prints bar codes. It is not needed to scan bar codes.

DatabaseVersion

Which structural version of the database is currently in use? Right now, it is set to "1," and is reserved for future enhancement.

DefaultLocation

CodeLocation.ID value for the location that is set as the default.

FineGrace

Number of days that an item can be overdue without incurring a fine.

NextBarcodeItem

The next starting value to use when printing item bar codes.

NextBarcodeMisc

The next starting value to use when printing miscellaneous bar codes.

NextBarcodePatron

The next starting value to use when printing patron bar codes.

PatronCheckOut

Indicates whether patrons can check out items without being logged in as an administrative user. Use a value of 0 (zero) to indicate no checkout privileges, or any nonzero value to allow patron checkout (−1 is preferred). If this value is missing or empty, patrons will not be allowed to check out items without administrator assistance.

SearchLimit

Indicates the maximum number of results returned in any search or lookup. If this value is missing or invalid, a default of 250 is used. The allowed range is between 25 and 5,000, inclusive.

TicketHeading

Display text to be printed at the top of checkout tickets. All lines are centered on the ticket. Include the vertical bar character (|) to break the text into multiple lines.

TicketFooting

Display text to be printed at the bottom of checkout tickets. All lines are centered on the ticket. Include the vertical bar character (|) to break the text into multiple lines.

UseLC

Indicates whether books are categorized by Dewey or Library of Congress (LC) call numbers. Use a value of 0 (zero) to indicate Dewey, or any nonzero value for LC (−1 is preferred). If this value is missing or empty, Dewey is assumed.

Creating the Database

Adding the database to SQL Server is almost as easy as documenting it; in fact, it requires 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.

CREATE TABLE Author
(
   ID           bigint         IDENTITY PRIMARY KEY,
   LastName     varchar(50)    NOT NULL,
   FirstName    varchar(30)    NULL,
   MiddleName   varchar(30)    NULL,
   Suffix       varchar(10)    NULL,
   BirthYear    smallint       NULL,
   DeathYear    smallint       NULL,
   Comments     varchar(250)   NULL
);

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 a NOT NULL option that indicates whether NULL values may be used in that field. The PRIMARY KEY option automatically specifies NOT NULL.

Some statements 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.

CREATE TABLE GroupActivity
(
   GroupID      bigint   NOT NULL,
   ActivityID   bigint   NOT NULL,
   PRIMARY KEY (GroupID, ActivityID)
);

The Author table had a single primary key, so the PRIMARY KEY option could be attached directly to its ID field. Since 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 this 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:

ALTER TABLE GroupActivity
   ADD FOREIGN KEY (GroupID)
   REFERENCES GroupName (ID);

Since 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 (see Figure 4-4).

SQL Server 2005 Management Studio Express main form

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.

If you don't select "Library," your tables will go somewhere else

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 sub-branch, you will see all the tables created by the script (see Figure 4-6).

Partial list of database tables

Figure 4-6. Partial list of database tables

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required