| 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. 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 barcodes 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") list 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 or not. 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 book, magazine, video, CD, 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. Because 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) | Barcode found on the copy. At this time, only numeric barcodes 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 check-out 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) | Barcode found on this patron's library card. At this time, only numeric barcodes 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 have been 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 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. |
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. BarcodeSheet Describes the template for a single page of barcode 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 barcode sheet. There may be any number of labels 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 barcode label. Items include static and generated text, lines, rectangles, and generated barcodes.
[Pages 149 - 150]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 = Barcode N = Barcode 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 end point. 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 end point. 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, barcode, 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 barcode number. Set to zero (0) to ignore padding. Ranges from 0 to 20. If the barcode length is less than the specified number of digits, it is padded on the left with zeros. Only applies 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) because 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 barcode in "code 39" or "code 3 of 9" format? If so, an asterisk will be placed before and after the barcode number before it is printed on a label. Use a value of 0 for False, or any non-zero value for True (1 is preferred). If missing or NULL, False is assumed. BarcodeFont. The name of the font used to print barcodes. This font must be installed on any workstation that displays or prints barcodes. It is not needed to scan barcodes. 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 barcodes. NextBarcodeMisc. The next starting value to use when printing miscellaneous barcodes. NextBarcodePatron. The next starting value to use when printing patron barcodes. 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 check-out privileges, or any non-zero value to allow patron check out (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 check-out 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 check-out 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 by Library of Congress (LC) call numbers. Use a value of 0 (zero) to indicate Dewey, or any non-zero 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'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. 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 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. 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. 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: ALTER TABLE GroupActivity ADD FOREIGN KEY (GroupID) REFERENCES GroupName (ID);
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. |
