I am uploading the TotalFlight database to the Wrox site. You can also download a copy at ftp://www.softconcepts.com. Its a big file, about 126 megabytes. You can log on with an anonymous user and your email address for the password. Here is the script for the database if you want to create an empty version of the database.
/****** Object: StoredProcedure [dbo].[InsertPerson] Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertPerson]
GO
/****** Object: View [dbo].[PassengerManifest] Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PassengerManifest]'))
DROP VIEW [dbo].[PassengerManifest]
GO
/****** Object: StoredProcedure [dbo].[DeletePerson] Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeletePerson]
GO
/****** Object: StoredProcedure [dbo].[UpdatePerson] Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdatePerson]
GO
/****** Object: Table [dbo].[Cities] Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cities]') AND type in (N'U'))
DROP TABLE [dbo].[Cities]
GO
/****** Object: Table [dbo].[Flight] Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Flight]') AND type in (N'U'))
DROP TABLE [dbo].[Flight]
GO
/****** Object: Table [dbo].[Person] Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
DROP TABLE [dbo].[Person]
GO
/****** Object: Table [dbo].[Leg] Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Leg]') AND type in (N'U'))
DROP TABLE [dbo].[Leg]
GO
/****** Object: Table [dbo].[LegManifest] Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LegManifest]') AND type in (N'U'))
DROP TABLE [dbo].[LegManifest]
GO
/****** Object: Table [dbo].[LegManifest] Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LegManifest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[LegManifest](
[LegManifestID] [int] IDENTITY(1,1) NOT NULL,
[LegID] [int] NOT NULL,
[PersonID] [int] NOT NULL,
[Seat] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_LegManifest] PRIMARY KEY CLUSTERED
(
[LegManifestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
/****** Object: Table [dbo].[Leg] Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Leg]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Leg](
[LegID] [int] IDENTITY(1,1) NOT NULL,
[FlightID] [int] NOT NULL,
[OriginID] [int] NOT NULL,
[DestinationID] [int] NOT NULL,
[Start] [datetime] NOT NULL,
[EstimatedEnd] [datetime] NOT NULL,
[ActualEnd] [datetime] NOT NULL,
[PassengersBooked] [int] NOT NULL,
[PassengersPresent] [int] NOT NULL,
[Notes] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Leg] PRIMARY KEY CLUSTERED
(
[LegID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
/****** Object: Table [dbo].[Person] Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Title] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Height] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Weight] [int] NULL,
[SeatPreference] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginCity] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginState] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DestinationCity] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DestinationState] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[State] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PostalCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'Title'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Customer preferred form of address' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'Title'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'Height'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Height used for smart-seat (TM) option' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'Height'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'Weight'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Weight used for smart-seat (TM) option' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'Weight'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'OriginCity'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default origin city' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'OriginCity'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'OriginState'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default origin state' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'OriginState'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'DestinationCity'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default destination city' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'DestinationCity'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'DestinationState'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default destination state' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'DestinationState'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'Address1'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default location for sending lost luggage and promotional items' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'Address1'
GO
/****** Object: Table [dbo].[Flight] Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Flight]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Flight](
[FlightID] [int] IDENTITY(1,1) NOT NULL,
[OriginID] [int] NOT NULL,
[DestinationID] [int] NOT NULL,
[FlightNumber] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PlaneID] [int] NOT NULL,
[Status] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PlannedStart] [datetime] NOT NULL,
[ActualStart] [datetime] NULL,
[PlannedCompletion] [datetime] NOT NULL,
[ActualCompletion] [datetime] NULL,
[Latitude] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Longitude] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Altitude] [int] NULL,
[EstimatedAirspeed] [int] NULL,
CONSTRAINT [PK_Flight] PRIMARY KEY CLUSTERED
(
[FlightID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Flight', N'COLUMN',N'Status'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'InProgress, Cancelled, Completed, Delayed, Late, OnTime' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Flight', @level2type=N'COLUMN',@level2name=N'Status'
GO
/****** Object: Table [dbo].[Cities] Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cities]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Cities](
[HubID] [int] IDENTITY(1,1) NOT NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[State] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Country] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED
(
[HubID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
/****** Object: StoredProcedure [dbo].[UpdatePerson] Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[UpdatePerson]
@PersonID int,
@FirstName varchar(25),
@LastName varchar(25),
@Title varchar(50) = NULL,
@Height varchar(8) = NULL,
@Weight int = NULL,
@SeatPreference varchar(10) = NULL,
@OriginCity varchar(25) = NULL,
@OriginState varchar(25) = NULL,
@DestinationCity varchar(25) = NULL,
@DestinationState varchar(25) = NULL,
@Address1 varchar(30),
@Address2 varchar(30) = NULL,
@City varchar(30),
@State varchar(25),
@PostalCode varchar(50)
AS
BEGIN
UPDATE
Person
SET
FirstName = @FirstName,
LastName = @LastName,
Title = @Title,
Height = @Height,
Weight = @Weight,
SeatPreference = @SeatPreference,
OriginCity = @OriginCity,
OriginState = @OriginState,
DestinationCity = @DestinationCity,
DestinationState = @DestinationState,
Address1 = @Address1,
Address2 = @Address2,
City = @City,
State = @State,
PostalCode = @PostalCode
WHERE PersonID = @PersonID
END
'
END
GO
/****** Object: StoredProcedure [dbo].[DeletePerson] Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[DeletePerson]
@PersonID int
AS
BEGIN
DELETE FROM Person WHERE PersonID = @PersonID
END
'
END
GO
/****** Object: View [dbo].[PassengerManifest] Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PassengerManifest]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[PassengerManifest]
AS
SELECT dbo.Flight.FlightNumber, dbo.Person.LastName, dbo.Person.FirstName, dbo.Person.Title, dbo.LegManifest.Seat
FROM dbo.LegManifest INNER JOIN
dbo.Person ON dbo.LegManifest.PersonID = dbo.Person.PersonID INNER JOIN
dbo.Leg ON dbo.LegManifest.LegID = dbo.Leg.LegID INNER JOIN
dbo.Flight ON dbo.Leg.FlightID = dbo.Flight.FlightID
'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'PassengerManifest', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[33] 4[21] 2[17] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = -192
Left = 0
End
Begin Tables =
Begin Table = "Person"
Begin Extent =
Top = 70
Left = 9
Bottom = 187
Right = 178
End
DisplayFlags = 280
TopColumn = 12
End
Begin Table = "Leg"
Begin Extent =
Top = 36
Left = 466
Bottom = 153
Right = 647
End
DisplayFlags = 280
TopColumn = 6
End
Begin Table = "Flight"
Begin Extent =
Top = 252
Left = 220
Bottom = 369
Right = 400
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "LegManifest"
Begin Extent =
Top = 9
Left = 211
Bottom = 112
Right = 405
End
DisplayFlags = 280
TopColumn = 1
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PassengerManifest'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane2' , N'SCHEMA',N'dbo', N'VIEW',N'PassengerManifest', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PassengerManifest'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'PassengerManifest', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PassengerManifest'
GO
/****** Object: StoredProcedure [dbo].[InsertPerson] Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[InsertPerson]
@PersonID int,
@FirstName varchar(25),
@LastName varchar(25),
@Title varchar(50) = NULL,
@Height varchar(8) = NULL,
@Weight int = NULL,
@SeatPreference varchar(10) = NULL,
@OriginCity varchar(25) = NULL,
@OriginState varchar(25) = NULL,
@DestinationCity varchar(25) = NULL,
@DestinationState varchar(25) = NULL,
@Address1 varchar(30),
@Address2 varchar(30) = NULL,
@City varchar(30),
@State varchar(25),
@PostalCode varchar(50)
AS
BEGIN
INSERT INTO Person
(
FirstName,
LastName,
Title,
Height,
Weight,
SeatPreference,
OriginCity,
OriginState,
DestinationCity,
DestinationState,
Address1,
Address2,
City,
[State],
PostalCode
)
VALUES
(
@FirstName,
@LastName,
@Title,
@Height,
@Weight,
@SeatPreference,
@OriginCity,
@OriginState,
@DestinationCity,
@DestinationState,
@Address1,
@Address2,
@City,
@State,
@PostalCode
)
END
'
END
GO