SQL 2005+

SQL Server Programming Tutorial Database

by Richard Carr, published at http://www.blackwasp.co.uk/SQLTutorialDatabase.aspx

The second part of the SQL Server Programming Fundamentals tutorial describes the database that will be created throughout the rest of the tutorial. In each instalment, this database will be improved or used to demonstrate SQL Server development.

The Tutorial

Throughout this tutorial we will create and use a database to demonstrate a possible real-world application of the Microsoft SQL Server RDBMS. This database will support a job booking system that permits the storage and processing of engineer visits for "DisasterFix", a company that provides services to businesses and homeowners who have experienced a gas, electricity or plumbing emergency requiring urgent repair. NB: DisasterFix is a fictitious company. Any resemblance to any real company is completely coincidental.

Although a user interface will not be created, the entire data structure and some business logic elements will be created within the new database. This could be extended to provide a complete software package using Microsoft .NET technologies or any other development environment that permits communication with SQL Server databases.

The following sections describe the company requiring the database and the basic requirements for their new software.

DisasterFix

DisasterFix are a fictitious company based in the United Kingdom. They provide services to businesses and homeowners who have experienced an emergency with a utility in their property. This includes gas, electricity, plumbing, heating, etc. When an emergency occurs, DisasterFix sends a suitably skilled engineer to examine the problem and perform any required repairs.

DisasterFix's customers pay for services in one of two ways. For new customers with an immediate problem, jobs are charged individually. A set fee is applied dependant upon the type of job to be undertaken. Customers may also purchase an insurance policy from DisasterFix. This is an annual contract that allows any number of jobs to be provided to an address with no additional charges.

DisasterFix does not employ its own engineers. Instead, any service-providing company or individual may apply to become a DisasterFix partner. Following suitable training, engineers are approved to provide services on behalf of DisasterFix and will automatically be assigned work in their designated areas.

All approved engineers are provided with a stock of standard parts that they may use for jobs assigned by DisasterFix. When a job is completed, the engineer sends a report back to the DisasterFix head office indicating the parts used and the time taken. The parts can then be replenished and the cost of the job calculated.

In addition to emergency responses, DisasterFix provides non-emergency work. Such jobs are handled in exactly the same way as emergencies except that they are always charged individually and are given a lower priority.

The Job Booking System

The new Job Booking System (JoBS) will replace the existing systems and improve the efficiency of the DisasterFix operations team by meeting the following requirements:

• The system will permit the creation of customers with multiple addresses.
• It will be possible to create contracts for annual renewal or for single jobs. Each contract will be linked to a customer address.
• It will be necessary to hold standard pricing for emergency and non-emergency work.
• The system will hold a list of approved engineers and the skills that they have. Skills include "gas", "electricity", "plumbing", etc. Each engineer will be assigned one of more geographical areas in which they operate.
• When a customer requires work to be undertaken, the system will generate a job and automatically assign it to an appropriate engineer. The engineer selection will be dependent upon the customer's address, the skill set and workload of the engineer, the hourly rate charged by the engineer and the number of complaints the engineer has received in the previous six months.
• If a customer complains about a job and an engineer is deemed to be at fault, this will be recorded against the engineer.
• The system must integrate with the existing stock system that controls the replenishment of the engineers' stock. This requires a daily export of used parts to the external system.
• The system must integrate with the existing billing system that controls invoicing and payments.