IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Skyshark')
	DROP DATABASE [Skyshark]
GO

CREATE DATABASE [Skyshark]  ON (NAME = N'Skyshark_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Skyshark_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'Skyshark_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Skyshark_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'Skyshark', N'autoclose', N'false'
GO

exec sp_dboption N'Skyshark', N'bulkcopy', N'false'
GO

exec sp_dboption N'Skyshark', N'trunc. log', N'false'
GO

exec sp_dboption N'Skyshark', N'torn page detection', N'true'
GO

exec sp_dboption N'Skyshark', N'read only', N'false'
GO

exec sp_dboption N'Skyshark', N'dbo use', N'false'
GO

exec sp_dboption N'Skyshark', N'single', N'false'
GO

exec sp_dboption N'Skyshark', N'autoshrink', N'false'
GO

exec sp_dboption N'Skyshark', N'ANSI null default', N'false'
GO

exec sp_dboption N'Skyshark', N'recursive triggers', N'false'
GO

exec sp_dboption N'Skyshark', N'ANSI nulls', N'false'
GO

exec sp_dboption N'Skyshark', N'concat null yields null', N'false'
GO

exec sp_dboption N'Skyshark', N'cursor close on commit', N'false'
GO

exec sp_dboption N'Skyshark', N'default to local cursor', N'false'
GO

exec sp_dboption N'Skyshark', N'quoted identifier', N'false'
GO

exec sp_dboption N'Skyshark', N'ANSI warnings', N'false'
GO

exec sp_dboption N'Skyshark', N'auto create statistics', N'true'
GO

exec sp_dboption N'Skyshark', N'auto update statistics', N'true'
GO

use [Skyshark]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_dtFlightStatus_dtFltDetails]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[dtFltStatus] DROP CONSTRAINT FK_dtFlightStatus_dtFltDetails
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_dtReservations_dtFltDetails]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[dtReservations] DROP CONSTRAINT FK_dtReservations_dtFltDetails
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_dtDepartedFlights_dtPassengerDetails]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[dtDepartedFlights] DROP CONSTRAINT FK_dtDepartedFlights_dtPassengerDetails
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_dtFrequentFlier_dtAllCustomers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[dtFrequentFliers] DROP CONSTRAINT FK_dtFrequentFlier_dtAllCustomers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_dtCancellation_dtUsers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[dtCancellations] DROP CONSTRAINT FK_dtCancellation_dtUsers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_dtReservations_dtUsers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[dtReservations] DROP CONSTRAINT FK_dtReservations_dtUsers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteReservations]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteReservations]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FrequentFlier]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FrequentFlier]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateReservations]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateReservations]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dtCancellations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dtCancellations]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dtDepartedFlights]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dtDepartedFlights]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dtFltDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dtFltDetails]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dtFltStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dtFltStatus]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dtFrequentFliers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dtFrequentFliers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dtPassengerDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dtPassengerDetails]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dtReservations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dtReservations]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dtUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dtUsers]
GO

CREATE TABLE [dbo].[dtCancellations] (
	[TicketNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Refund] [int] NOT NULL ,
	[ProcessedBy] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CancellationDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[dtDepartedFlights] (
	[TicketNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FltNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DateOfJourney] [datetime] NOT NULL ,
	[ClassOfRes] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EMail] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fare] [int] NOT NULL ,
	[Status] [int] NOT NULL ,
	[ReservedBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DateOfRes] [datetime] NOT NULL ,
	[TicketConfirmed] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[dtFltDetails] (
	[FltNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Origin] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Destination] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Deptime] [datetime] NOT NULL ,
	[Arrtime] [datetime] NOT NULL ,
	[AircraftType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SeatsExec] [int] NOT NULL ,
	[SeatsBn] [int] NOT NULL ,
	[FareExec] [int] NOT NULL ,
	[FareBn] [int] NOT NULL ,
	[LaunchDate] [datetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[dtFltStatus] (
	[FltNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[StatusDate] [datetime] NOT NULL ,
	[StatusClass] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Status] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[dtFrequentFliers] (
	[EMail] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Discount] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[dtPassengerDetails] (
	[EMail] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FareCollected] [int] NOT NULL ,
	[TotalTimesFlown] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[dtReservations] (
	[TicketNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FltNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DateOfJourney] [datetime] NOT NULL ,
	[ClassOfRes] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EMail] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fare] [int] NOT NULL ,
	[Status] [int] NOT NULL ,
	[ReservedBy] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DateOfRes] [datetime] NOT NULL ,
	[TicketConfirmed] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[dtUsers] (
	[Username] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Password] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Role] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PasswordChanged] [bit] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dtCancellations] WITH NOCHECK ADD 
	CONSTRAINT [PK_dtCancellation] PRIMARY KEY  CLUSTERED 
	(
		[TicketNo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dtDepartedFlights] WITH NOCHECK ADD 
	CONSTRAINT [PK_dtDepartedFlights] PRIMARY KEY  CLUSTERED 
	(
		[TicketNo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dtFltDetails] WITH NOCHECK ADD 
	CONSTRAINT [PK_dtFltDetails] PRIMARY KEY  CLUSTERED 
	(
		[FltNo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dtFrequentFliers] WITH NOCHECK ADD 
	CONSTRAINT [PK_dtFrequentFlier] PRIMARY KEY  CLUSTERED 
	(
		[EMail]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dtPassengerDetails] WITH NOCHECK ADD 
	CONSTRAINT [PK_dtAllCustomers] PRIMARY KEY  CLUSTERED 
	(
		[EMail]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dtReservations] WITH NOCHECK ADD 
	CONSTRAINT [PK_dtReservations] PRIMARY KEY  CLUSTERED 
	(
		[TicketNo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dtUsers] WITH NOCHECK ADD 
	CONSTRAINT [PK_dtUsers] PRIMARY KEY  CLUSTERED 
	(
		[Username]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[dtCancellations] ADD 
	CONSTRAINT [FK_dtCancellation_dtUsers] FOREIGN KEY 
	(
		[ProcessedBy]
	) REFERENCES [dbo].[dtUsers] (
		[Username]
	)
GO

ALTER TABLE [dbo].[dtDepartedFlights] ADD 
	CONSTRAINT [FK_dtDepartedFlights_dtPassengerDetails] FOREIGN KEY 
	(
		[EMail]
	) REFERENCES [dbo].[dtPassengerDetails] (
		[EMail]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[dtDepartedFlights] nocheck constraint [FK_dtDepartedFlights_dtPassengerDetails]
GO

ALTER TABLE [dbo].[dtFltStatus] ADD 
	CONSTRAINT [FK_dtFlightStatus_dtFltDetails] FOREIGN KEY 
	(
		[FltNo]
	) REFERENCES [dbo].[dtFltDetails] (
		[FltNo]
	)
GO

ALTER TABLE [dbo].[dtFrequentFliers] ADD 
	CONSTRAINT [FK_dtFrequentFlier_dtAllCustomers] FOREIGN KEY 
	(
		[EMail]
	) REFERENCES [dbo].[dtPassengerDetails] (
		[EMail]
	)
GO

ALTER TABLE [dbo].[dtReservations] ADD 
	CONSTRAINT [FK_dtReservations_dtFltDetails] FOREIGN KEY 
	(
		[FltNo]
	) REFERENCES [dbo].[dtFltDetails] (
		[FltNo]
	),
	CONSTRAINT [FK_dtReservations_dtUsers] FOREIGN KEY 
	(
		[ReservedBy]
	) REFERENCES [dbo].[dtUsers] (
		[Username]
	)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE DeleteReservations 
@ticketno char(10), @user char(15), @cancdate datetime, @refund int
AS
Declare @fltno char(10)
Declare @date datetime
Declare @class char(10)
Declare @status int
select @fltno=FltNo, @date=DateOfJourney, @class=ClassOfRes , @status=Status
from dtReservations where TicketNo=@ticketno
Update dtReservations
set Status=Status+1 where FltNo=@fltno and DateOfJourney=@date 
and ClassOfRes=@class and Status<@status
Update dtFltStatus
set Status=Status+1 where FltNo=@fltno and StatusDate=@date 
and StatusClass=@class
INSERT into dtCancellations
values (@ticketno, @refund, @user, @cancdate)
Delete dtReservations where TicketNo=@ticketno
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE FrequentFlier
AS
DELETE dtFrequentFliers
INSERT INTO dtPassengerDetails
SELECT EMail, Sum(Fare), Count(EMail) from dtDepartedFlights
where EMAIL!='NotSpecified' group by EMail
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE UpdateReservations 
@date datetime 
AS
INSERT INTO dtDepartedFlights
SELECT * from dtReservations
WHERE (DateOfJourney < @date) AND (TicketConfirmed=1)
DELETE from dtReservations
WHERE (DateOfJourney < @date)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

