Sample Database and Other Resources

You may have noticed that this book does not include a CD. SQL Server development is a dynamic field, as you will see if you compare the first, second, and third editions of the book. Rather than increase the cost of the book by adding a CD, which would be out of date almost before it hit the bookstore, the publisher and I have chosen to make additional resources available for download via the Web. In addition to the sample database (more information on that in just a bit) that I have created and will use through most of this book, other resources available include these:

• Several tools for source code management and database deployment Set of T-SQL, VBScript, and .NET tools for generating, managing, and deploying code of database objects.

• Sample SQLCLR code Visual Studio .NET sample projects for demonstrating development of database objects in Visual Basic .NET and C#.

• Periodic updates As noted earlier, SQL Server development is a dynamic field, and thus a book on SQL Server needs to be dynamic to meet the evolving needs of its audience. Reader feedback is important to me. Check my web site (http://www.trigonblue.com) for periodic updates on issues raised by readers.

• Author's web site Aside from being the source of the sample database and periodic update downloads, the Trigon Blue web site provides a wealth of excellent reference materials and links. Visit the site often for SQL Server and e-business news. While you're there, have a look at the many articles and white papers, and check out Trigon Blue's many product and service offerings.

Sample Database

The subject of the Asset 5 sample database created for this book is an asset-management system within a fictional organization. Although the database is based on real-world experience within financial institutions, it is also applicable in many other environments.

The main purpose of the database is to track assets. Assets are defined as equipment, and all variations in their content, attributes, and shape are recorded as values of properties. The Inventory table tracks location, status, leasing information, and who is currently using each asset. To transfer an asset from one location to another, to assign assets to a different owner or department, to request maintenance, or to request upgrades or new assets, users of the database use orders and order items. Activities performed to complete the order are recorded in the charge log and interdepartmental invoices are generated. There are lookup tables used to track provinces, lease frequencies, statuses, and other details.

Sample Database Installation

You should download this database and install it on your server before you begin to read the rest of this book. To download and install the sample Asset5 database:

1. Visit http://www.trigonblue.com/stored_procedures_2005

2. Click the Download Sample DB link.

3. Click the Asset 5 sample database link to start the download. When prompted, opt to save the file to disk. Remember the location where you saved the file.

4. Unzip the contents of the Zip file into the Data folder of the machine on which SQL Server is installed (usually \Program FilesMicrosoft SQL Server\ MSSQL. l\MSSQL\Data).

5. Make sure that SQL Server is running. If necessary, go to Control Panel | Administrative Tools | Services, find the SQL Server service in the list, and make sure that its status is Started.

6. Run SQL Server Management Studio (Start | Programs | Microsoft SQL Server 2005).

7. You will be prompted to connect to SQL Server. Make sure that Server Type is set to Database Engine and that Server Name is set to the name of the machine (network) name on which you installed SQL Server. If you have specified the name of an instance of SQL Server 2005 during the installation, you must specify the instance name in the field as well with backslash (\) as a delimiter. In the screen shown here, the machine name is LG and the instance name is RC.

   

8. If your Windows login is a member of the Administrators local group, you can leave Authentication set to Windows Authentication and click OK. If you are one of the local administrators, you can ask an administrator to give you such access or ask a database administrator to create a SQL server login for you. In that case you would need to change Authentication to SQL Server Authentication and then specify a login and password to connect to the server.

9. Management Studio will appear on the screen with Object Browser connected to the server in the left side of the window.

10. Click the New Query button on the toolbar to open a new query window. Management Studio opens a query window pointing to the master database.

    

11. Type the following text in the query window:

          EXEC sp_attach_db 'Asset5',      'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Asset5.mdf',      'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Asset5_log.ldf' 

12. If the location of the folder containing the Asset5 database file is different from the one shown in the command, change the command.

13. To attach the database, select Query | Execute from the menu bar. SQL Server attaches the database. The database is now ready for use.

Purpose and Design of the Sample Database

The Asset5 database is designed to track and manage assets within an organization. This database allows users to

• Track features of assets

• Search for assets with specific features

• Record the current location and status of an asset

• Track the person and organizational unit to which the asset is assigned

• Note how an asset is acquired and the cost of the acquisition

• Keep parameters concerning leases (for example, lease payments, lease schedules, and lease vendors used to obtain assets)

• Identify assets for which lease schedules have expired

• Record orders to departments in charge of services such as acquisition, disposal, servicing, and technical support

• Monitor the processing of orders

• Manage the costs associated with actions taken on order items

Database Diagram

Figure 1-1 shows the physical implementation of the Assets entity relationship diagram.

Figure 1-1: A database diagram of the Asset5 database

Description of Assets

The following illustration shows the tables involved in the description of each asset. Detailed information about deployed equipment and their features is essential for the proper management of current inventory as well as future upgrades and acquisitions.

Information in these asset description tables allows users to

• Manage a list of standard equipment deployed within the organization

• Manage a list of attributes (properties) that can be used to describe assets

• Manage a list of attributes for each asset

• Obtain a summary of equipment deployed within the organization

• Make decisions about the deployment of a software package based on the capabilities of existing equipment in the field

• Find obsolete pieces of equipment that need to be disposed of and replaced with new equipment

Inventory The central table in the Asset5 database is the Inventory table. It is designed to track the assets currently deployed within an organization. The most important information about an asset indicates what kind of equipment it is. This table also stores information about the asset's current location and its status, as well as the way in which the asset was acquired and the cost of acquisition.

Equipment The Equipment table stores the make and model of each type of asset. Each piece of equipment with a unique make and model has a separate record in this table. It groups equipment by equipment type.

EqType This table lists types of equipment. For example, equipment types include notebook, printer, monitor, keyboard, mouse, scanner, and network hub.

Property Each asset in the database can be described with a set of attributes listed in the Properties table. This table also records a unit used to store the value of the property. For example, the properties (and units of measure) of a monitor are size (inch), resolution (pixel), and type, while an external hard disk has properties (and units) such as capacity (GB), size (inch), and adapter.

InventoryProperty Each asset in the Inventory table has a set of properties. The InventoryProperty table stores the values of each property (except for make and model, which are recorded in the Equipment table).

For example, a Toshiba (Make) Protégé 7020 (Model) notebook (EqType) assigned to an employee has 64 (value) MB (unit) of RAM (property), 4.3 (value) GB (unit) of HDD capacity (property), a Pentium II 333 (value) processor (property), and so on. Another employee is using an upgraded version of the same equipment with 128 (value) MB (unit) of RAM (property), 6.4 (value) GB (unit) of HDD capacity (property), a Pentium II 366 (value) processor (property), and so on.

Deployment of Assets

This following set of tables keeps track of the location in which an asset is deployed and the person and organizational unit to which the asset is assigned.

The information in these asset deployment tables allows users to

• Manage a list of locations within an organization

• Manage a list of persons working within an organization

• Retrieve contact information about persons to whom assets are assigned

• Generate reports about assets deployed by province and organizational unit

• Retrieve a list of assets assigned to a particular person

• Manage relationships between organizational units

• Assign person(s) to organizational units

Location The Location table stores information about the physical location of the deployed asset. Each location has a name and an address as attributes.

Province This table contains a list of provinces and states. The primary key is the abbreviation of the province/state. The presence of this table is essential for reports, which will aggregate asset deployment by location, province/state, and country.

Contact This table contains a list of persons involved in the asset management process. It includes persons with assets assigned to them, persons completing and approving orders, and persons performing maintenance and support.

OrgUnit Each contact is assigned to some organizational unit within the organization. The OrgUnit table records relationships among companies, cost centers, departments, and the like. This table is designed as a recursive table: an organizational unit can be part of some other organizational unit. This quality also reflects the need for rapid changes in today's work environment due to change of ownership, restructuring, and so on.

Leasing Tables

An important aspect of asset management is the tracking of lease information. It helps management avoid payment of penalties associated with late returns or the failure to return leased assets to the leasing vendor:

The information in the lease tables allows users to

• Keep track of the assets associated with each lease

• Manage lease schedules to keep track of the start, end, and duration of lease periods

• Identify assets that need to be returned to a lease vendor

• Generate reports on assets deployed by lease schedule and lease contract

• Retrieve a list of assets obtained from a particular lease vendor

• Retrieve the total value of lease payments, lease schedules, and lease contracts

Lease The Lease table contains information about lease contracts. It records the name of the lease vendor, the number of the lease that the vendor is using to track the contract, the date the contract was signed, and the total value of assets assigned to the lease.

LeaseSchedule Assets obtained through one lease contract might not be received on the same date. An asset might also be under a different payment regime and lease duration. Therefore, each lease contains a set of lease schedules. Each schedule is recorded in the LeaseSchedule table and is described with a start date, an end date, and the frequency of payments. This table also tracks the total value of payments per lease term.

LeaseFrequency LeaseFrequency is a lookup table that contains all possible values for lease frequency including monthly, semimonthly, biweekly, and weekly.

AcquisitionType AcquisitionType is a lookup table that lists possible acquisition types including lease, purchase, and rent.

Order Tables

Orders are the primary means of managing assets within the organization. Users can request new assets and the disposal of obsolete assets. They can request maintenance and technical support. Authorized personnel can monitor orders and react to them, associate a cost with their execution, and generate invoices. The following tables are used to store information about orders:

The information in these order tables allows users to

• Request new equipment

• Request technical support

• Request maintenance

• Execute scheduled maintenance

• Track the status of orders

• Assign a staff member to execute the order

• Approve the execution of orders

• Manage a list of actions and the default costs associated with them

• Track costs associated with each activity

• Generate interdepartmental invoices

• Request the transfer of assets

• Request the disposal of obsolete assets

• Generate summaries and reports on performed activities

Order Users can record requests in the Order table. At that time, the order date and target date are recorded. General request requirements are recorded as an order type, and special requirements are recorded as a note. The person making the request is recorded, as well as the person approving the request and assigning the order for execution. If the order is a transfer request, the table also records a destination for the asset. Users can track the status of the order, and once it is completed, its completion date is set. At that point, one organizational unit is billed for performed actions, and once the order is paid, the payment is noted on the order and funds are assigned to the organizational unit completing the order.

Orderltem The OrderItem table records assets that need the intervention of authorized personnel or new equipment that needs to be purchased. Special requests are recorded in the Note field.

Action The Action table manages the list of activities needed to complete a request as well as the default cost associated with each.

ChargeLog Actions performed on an orderItem to complete an order will be recorded in the ChargeLog table. This table will be used to generate an invoice after completion of the order.

OrderStatus The OrderStatus table is used as a lookup table to manage the status of orders. It contains statuses such as

• Ordered

• In-process

• Canceled

• Deferred

• Completed

OrderType The OrderType table is used as a lookup table to store the general requirements of the order. It contains values such as

• Requisition

• Transfer

• Support

• Scrap

• Repair

ActivityLog This table is not related specifically to the recording of orders. Rather, it is a repository for audit trail information. Most of the time it is populated by a trigger associated with some specific database change.