ÿþUSE [TotalFlight]

GO

/****** Object:  Table [dbo].[Flight]    Script Date: 12/14/2009 11:58:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Flight](

	[FlightID] [int] IDENTITY(1,1) NOT NULL,

	[OriginID] [int] NOT NULL,

	[DestinationID] [int] NOT NULL,

	[FlightNumber] [varchar](10) NOT NULL,

	[PlaneID] [int] NOT NULL,

	[Status] [varchar](15) NOT NULL,

	[PlannedStart] [datetime] NOT NULL,

	[ActualStart] [datetime] NULL,

	[PlannedCompletion] [datetime] NOT NULL,

	[ActualCompletion] [datetime] NULL,

	[Latitude] [varchar](15) NULL,

	[Longitude] [varchar](15) 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) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

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].[LegManifest]    Script Date: 12/14/2009 11:58:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[LegManifest](

	[LegManifestID] [int] IDENTITY(1,1) NOT NULL,

	[LegID] [int] NOT NULL,

	[PersonID] [int] NOT NULL,

	[Seat] [nvarchar](5) 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) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Leg]    Script Date: 12/14/2009 11:58:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

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) NULL,

	[PricePaid] [decimal](18, 0) 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) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[Person]    Script Date: 12/14/2009 11:58:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Person](

	[PersonID] [int] IDENTITY(1,1) NOT NULL,

	[FirstName] [varchar](25) NOT NULL,

	[LastName] [varchar](25) NOT NULL,

	[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) NOT NULL,

	[Address2] [varchar](30) NULL,

	[City] [varchar](30) NOT NULL,

	[State] [varchar](25) NOT NULL,

	[PostalCode] [varchar](50) 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) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

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

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

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

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

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

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

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

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].[Cities]    Script Date: 12/14/2009 11:58:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Cities](

	[HubID] [int] IDENTITY(1,1) NOT NULL,

	[City] [varchar](30) NOT NULL,

	[State] [varchar](30) NOT NULL,

	[Country] [varchar](30) 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) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  StoredProcedure [dbo].[UpdatePerson]    Script Date: 12/14/2009 11:58:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

GO

/****** Object:  View [dbo].[PricedPassengerManifest]    Script Date: 12/14/2009 11:58:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[PricedPassengerManifest]

AS

SELECT        dbo.Flight.FlightNumber, dbo.LegManifest.LegManifestID, dbo.Person.FirstName, dbo.Person.LastName, dbo.LegManifest.Seat, dbo.Leg.PricePaid

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

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[40] 4[20] 2[20] 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[57] 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 = 10

   End

   Begin DiagramPane = 

      Begin Origin = 

         Top = 0

         Left = 0

      End

      Begin Tables = 

         Begin Table = "LegManifest"

            Begin Extent = 

               Top = 6

               Left = 38

               Bottom = 135

               Right = 208

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "Person"

            Begin Extent = 

               Top = 6

               Left = 246

               Bottom = 135

               Right = 421

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "Flight"

            Begin Extent = 

               Top = 138

               Left = 38

               Bottom = 267

               Right = 233

            End

            DisplayFlags = 280

            TopColumn = 10

         End

         Begin Table = "Leg"

            Begin Extent = 

               Top = 6

               Left = 459

               Bottom = 135

               Right = 646

            End

            DisplayFlags = 280

            TopColumn = 7

         End

      End

   End

   Begin SQLPane = 

   End

   Begin DataPane = 

      PaneHidden = 

      Begin ParameterDefaults = ""

      End

   End

   Begin CriteriaPane = 

      PaneHidden = 

      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

   End

End

' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PricedPassengerManifest'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PricedPassengerManifest'

GO

/****** Object:  View [dbo].[PassengerManifest]    Script Date: 12/14/2009 11:58:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

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 = "LegManifest"

            Begin Extent = 

               Top = 9

               Left = 211

               Bottom = 112

               Right = 405

            End

            DisplayFlags = 280

            TopColumn = 1

         End

         Begin Table = "Person"

            Begin Extent = 

               Top = 70

               Left = 9

               Bottom = 187

               Right = 178

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "Flight"

            Begin Extent = 

               Top = 252

               Left = 220

               Bottom = 369

               Right = 400

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "Leg"

            Begin Extent = 

               Top = 36

               Left = 466

               Bottom = 153

               Right = 647

            End

            DisplayFlags = 280

            TopColumn = 0

         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 = 3090

         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

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

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: 12/14/2009 11:58:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

GO

/****** Object:  StoredProcedure [dbo].[DeletePerson]    Script Date: 12/14/2009 11:58:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[DeletePerson]

	@PersonID int

AS

BEGIN

	DELETE FROM Person WHERE PersonID = @PersonID

END

GO