How to calculate cost per every day(cost hotel + cost flight) depending on flight date ?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  June 23, 2017 at 6:04 am

  #326848

  I need to make database design relations   to  calculate cost per tour for tourists in Egypt
  Flight  cost + hotel cost per every day for person based on flight date
  Suppose I have flight on date 23/06/2017 from amesterdam to egypt
  And from cairo to amesterdam in 26/06/2017 will return back
  So that calculation will be as following  :
  Table tour
  1         Tour1
  2         Tour2
  3         Tour3
  Table Tour Duration
  Tour1  3 days
  Tour2  6 days

  Table Tour Duration Details
  Tour1 data
  Day          flight                                              Hotel
  Day1      amesterdam to cairo                 Hilton
  Day2   Cairo to aswan                              Hilton
  Day3  Cairo to amsterdam                    
  From 23/06/2017 to 26/06/2017
  Table cost Per day
  Tour1 data
  Day1     500       25   
  Day2   300      25
  Day3  500 
  
  Table flight data
  Tour1   23/06/2017  26/06/2017  1350
  Tour2  01/07/2017   07/07/2017 1425 (5 * 25 + 1300)   
  
  25 represent  cost accommodation per night in Hilton hotel

  500 represent cost  flight from Cairo Amsterdam ,Amsterdam to Cairo

  300  represent cost per flight from Cairo to Aswan
  How to design table relations to calculate cost per every day(cost hotel + cost flight) depending on flight date ?

• Thom A

  SSC Guru

  Points: 99133

  More actions

  June 23, 2017 at 6:40 am

  #1947934

  We really need a bit more in depth detail on your set up, however, I would hazard a guess that a hotel can be used in many tours, and also, a flight may be used by many tours.

  Thus, perhaps, something like this...?
  CREATE TABLE #Hotel
  (HotelID int IDENTITY(1,1), --Joe Celko will likely see this and come tell me off for this shortly ;)
  HotelName varchar(50),
  CostPerNight decimal(12,2));
GO

  CREATE TABLE #Flight
  (FlightID int IDENTITY(1,1),
  DepartDate date,
  DepartsFrom varchar(50),
  ArrivesAt varchar(50),
  Cost decimal(12,2));
GO

  CREATE TABLE #Tour
  (TourID int IDENTITY(1,1),
  TourDuration int,
  TourName varchar(50));
GO

  CREATE TABLE #TourFlight (TourID int, FlightID int);
CREATE TABLE #TourHotel
  (TourID int, HotelID int,
  CheckIn date,
  CheckOut date);
GO

  INSERT INTO #Hotel (HotelName, CostPerNight)
VALUES
  ('Cairo Hilton',25.00),
  ('Aswan Hilton',28.00); --Intentionally different

  INSERT INTO #Flight (DepartDate, DepartsFrom, ArrivesAt, Cost)
VALUES
  ('20170601','Amsterdam','Cairo',500),
  ('20170603','Cairo','Aswan',500),
  ('20170604','Aswan','Amsterdam',500);

  INSERT INTO #Tour (TourDuration, TourName)
VALUES (4,'Amsterdam: Cairo and Aswan');
GO

  INSERT INTO #TourFlight
VALUES (1,1),(1,2),(1,3);

  INSERT INTO #TourHotel
VALUES (1,1,'20170601','20170603'),
   (1,2,'20170603','20170604');
GO

  WITH Hotels AS (
  SELECT T.TourID, T.TourName, H.CostPerNight, H.HotelName, TH.CheckIn, TH.CheckOut, DD.[Date]
  FROM #Tour T
   JOIN #TourHotel TH on T.TourID = TH.TourID
   JOIN #Hotel H ON TH.HotelID = H.HotelID
   JOIN DimDate DD ON DD.[Date] BETWEEN TH.CheckIn AND DATEADD(DAY,-1,TH.CheckOut) --Because you pay by night you stay
   --DimDAte is a table I have already, see http://www.sqlservercentral.com/articles/calendar/145206/ for details on Calendar tables
  ),
FLights AS (
  SELECT T.TourID, T.TourName, F.DepartsFrom, F.ArrivesAt, F.DepartDate, F.Cost
  FROM #Tour T
   JOIN #TourFlight TF ON T.TourID = TF.TourID
   JOIN #Flight F ON TF.FlightID = F.FlightID
)
SELECT COALESCE(H.TourID, F.TourID) As TourID,
   COALESCE(H.TourName, F.TourName) As TourName,
   COALESCE(H.Date, F.DepartDate) AS [Date],
   H.HotelName AS Hotel,
   F.DepartsFrom + '->' + F.ArrivesAt AS Flight,
   ISNULL(H.CostPerNight,0.00) + ISNULL(F.Cost,0.00) AS DailyCost
FROM Hotels H
  FULL OUTER JOIN Flights F ON H.TourID = F.TourID
          AND H.Date = F.DepartDate
ORDER BY COALESCE(H.TourID, F.TourID), COALESCE(H.Date, F.DepartDate);

  GO
DROP TABLE #TourHotel;
DROP TABLE #TourFlight;
DROP TABLE #Tour;
DROP TABLE #Flight;
DROP TABLE #Hotel;
GO

  The SELECT statement then returns the below (which I haven't reformated, as SSC just hates pasting, and I can't be bothered to add all the spaces back in it was so "kind" enough to remove... -_-)
  TourID  TourName               Date   Hotel                Flight                                 DailyCost
----------- -------------------------------------------------- ---------- -------------------------------------------------- ------------------------------------------------------------------------------------------------------ ---------------------------------------
1    Amsterdam: Cairo and Aswan         2017-06-01 Cairo Hilton             Amsterdam->Cairo                             525.00
1    Amsterdam: Cairo and Aswan         2017-06-02 Cairo Hilton             NULL                                 25.00
1    Amsterdam: Cairo and Aswan         2017-06-03 Aswan Hilton             Cairo->Aswan                               528.00
1    Amsterdam: Cairo and Aswan         2017-06-04 NULL                Aswan->Amsterdam                             500.00
  

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  June 23, 2017 at 8:06 am

  #1947959

  thank you for reply
  i can give you any details you need 
  Calculation of hotel cost and flight cost depend on fixed program tour as image below
  in left picture program for 15 days and right increase day by day depend on flight date starting
  

• Thom A

  SSC Guru

  Points: 99133

  More actions

  June 23, 2017 at 8:35 am

  #1947965

  That does seem to be similar to the structure i provided above. Does that not give you something to start off on?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  June 23, 2017 at 10:07 am

  #1947988

  thank you for reply
  I have designed my database as following
  my problem How to join between days and costs like screen shoot above
  day1 
  day2
  day3
  cost per day1
  cost per day2
  cost per day3
  USE [NileTravel]
GO
/****** Object: Table [dbo].[ActivityType]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ActivityType](
    [ActivityTypeID] [int] NOT NULL,
    [ActivityType] [nvarchar](50) NULL,
    [ProductID] [int] NULL,
CONSTRAINT [PK_ActivityType] PRIMARY KEY CLUSTERED
(
    [ActivityTypeID] 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].[AirLine]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AirLine](
    [AirLineID] [int] NOT NULL,
    [AirLineName] [nvarchar](50) NULL,
CONSTRAINT [PK_AirLine] PRIMARY KEY CLUSTERED
(
    [AirLineID] 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].[ChildrenDiscount]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChildrenDiscount](
    [ChildrenDiscountID] [int] NOT NULL,
    [ProductID] [int] NULL,
    [MinAge] [int] NULL,
    [MaxAge] [int] NULL,
    [Discount] [money] NULL,
CONSTRAINT [PK_ChildrenDiscount] PRIMARY KEY CLUSTERED
(
    [ChildrenDiscountID] 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].[Destination]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Destination](
    [BestimID] [int] NOT NULL,
    [BestimName] [nvarchar](50) NULL,
    [ParentID] [int] NULL,
    [Image] [varbinary](max) NULL,
CONSTRAINT [PK_Bestiming] PRIMARY KEY CLUSTERED
(
    [BestimID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[DurationDetails]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DurationDetails](
    [DetailsDurationID] [int] NOT NULL,
    [PackageDurationsID] [int] NULL,
    [Days] [nvarchar](50) NULL,
    [Flight] [nvarchar](50) NULL,
    [Accomodate] [nvarchar](50) NULL,
    [Excursion] [nvarchar](50) NULL,
    [Transfer] [nvarchar](50) NULL,
CONSTRAINT [PK_DurationDetails] PRIMARY KEY CLUSTERED
(
    [DetailsDurationID] 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].[FlightData]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FlightData](
    [FlighID] [nvarchar](50) NOT NULL,
    [FlightNo] [nvarchar](50) NOT NULL,
    [FlightDate] [datetime] NULL,
    [FlightTypeID] [int] NULL,
    [AirLineID] [int] NULL,
    [Arrival] [time](7) NULL,
    [Departure] [time](7) NULL,
    [AdultPrice] [money] NULL,
    [ChildPrice] [money] NULL,
    [Stock] [int] NULL,
    [TotalPrice] [money] NULL,
    [Active] [bit] NULL,
CONSTRAINT [PK_FlightData_1] PRIMARY KEY CLUSTERED
(
    [FlighID] 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].[FlightRoute]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FlightRoute](
    [FlightTypeID] [int] NOT NULL,
    [FlightFrom] [nvarchar](50) NULL,
    [FlightTo] [nvarchar](50) NULL,
    [Active] [bit] NULL,
CONSTRAINT [PK_FlightType] PRIMARY KEY CLUSTERED
(
    [FlightTypeID] 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].[Hotel]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hotel](
    [ProductID] [int] NOT NULL,
    [ProductName] [nvarchar](50) NULL,
    [BestimmingID] [int] NULL,
    [TypeID] [int] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
    [ProductID] 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].[HotelPrice]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HotelPrice](
    [ProductPriceID] [nchar](10) NOT NULL,
    [ProductID] [int] NULL,
    [FromDate] [datetime] NULL,
    [ToDate] [datetime] NULL,
CONSTRAINT [PK_ProductPrice] PRIMARY KEY CLUSTERED
(
    [ProductPriceID] 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].[Luggage]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Luggage](
    [LuggageID] [int] NOT NULL,
    [AirLineID] [int] NULL,
    [KG] [float] NULL,
    [CostPrice] [int] NULL,
    [SalesPrice] [int] NULL,
    [LuggageName] [nvarchar](50) NULL,
CONSTRAINT [PK_Luggage] PRIMARY KEY CLUSTERED
(
    [LuggageID] 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].[Parent]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Parent](
    [ParentID] [int] NOT NULL,
    [Subject] [nvarchar](50) NULL,
    [Text] [nvarchar](max) NULL,
    [Slug] [nvarchar](50) NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
    [ParentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO
/****** Object: Table [dbo].[RoomOccupation]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomOccupation](
    [RoomOccupationID] [int] NOT NULL,
    [OccupationNo] [int] NULL,
    [MaxAdult] [int] NULL,
    [MaxOccupation] [int] NULL,
    [Price] [money] NULL,
    [ProductID] [int] NULL,
CONSTRAINT [PK_RoomOccupation] PRIMARY KEY CLUSTERED
(
    [RoomOccupationID] 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].[RoomService]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomService](
    [RoomServiceID] [int] NOT NULL,
    [RoomService] [nvarchar](50) NULL,
    [ProductID] [int] NULL,
CONSTRAINT [PK_RoomService] PRIMARY KEY CLUSTERED
(
    [RoomServiceID] 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].[RoomType]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomType](
    [RoomTypeID] [int] NOT NULL,
    [RoomType] [nvarchar](50) NULL,
    [ProductID] [int] NULL,
    [RoomPrice] [money] NULL,
CONSTRAINT [PK_RoomType] PRIMARY KEY CLUSTERED
(
    [RoomTypeID] 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].[Services]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Services](
    [ServiceID] [int] NOT NULL,
    [ServiceType] [nvarchar](50) NULL,
    [ProductID] [int] NULL,
    [ServicePrice] [money] NULL,
CONSTRAINT [PK_Services] PRIMARY KEY CLUSTERED
(
    [ServiceID] 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].[Tour]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tour](
    [PackageID] [int] NOT NULL,
    [PackageName] [nvarchar](100) NULL,
    [BestimID] [int] NULL,
    [Duration] [nvarchar](50) NULL,
CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED
(
    [PackageID] 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].[TourDestination]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TourDestination](
    [PackageID] [int] NOT NULL,
    [BestomingID] [int] NOT NULL
) ON [PRIMARY]

  GO
/****** Object: Table [dbo].[TourDuration]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TourDuration](
    [PackageDurationsID] [int] NOT NULL,
    [PackageID] [int] NULL,
    [PackageDuration] [int] NULL,
    [NightCounts] [int] NULL,
CONSTRAINT [PK_PackageDuration] PRIMARY KEY CLUSTERED
(
    [PackageDurationsID] 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].[TourType]  Script Date: 23/06/2017 1:21:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TourType](
    [PackageTypeID] [int] NOT NULL,
    [PackageType] [nvarchar](50) NULL,
CONSTRAINT [PK_PackageType] PRIMARY KEY CLUSTERED
(
    [PackageTypeID] 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
ALTER TABLE [dbo].[ActivityType] WITH CHECK ADD CONSTRAINT [FK_ActivityType_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[ActivityType] CHECK CONSTRAINT [FK_ActivityType_Product]
GO
ALTER TABLE [dbo].[ChildrenDiscount] WITH CHECK ADD CONSTRAINT [FK_ChildrenDiscount_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[ChildrenDiscount] CHECK CONSTRAINT [FK_ChildrenDiscount_Product]
GO
ALTER TABLE [dbo].[DurationDetails] WITH CHECK ADD CONSTRAINT [FK_DurationDetails_PackageDuration] FOREIGN KEY([PackageDurationsID])
REFERENCES [dbo].[TourDuration] ([PackageDurationsID])
GO
ALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_PackageDuration]
GO
ALTER TABLE [dbo].[FlightData] WITH CHECK ADD CONSTRAINT [FK_FlightData_AirLine] FOREIGN KEY([AirLineID])
REFERENCES [dbo].[AirLine] ([AirLineID])
GO
ALTER TABLE [dbo].[FlightData] CHECK CONSTRAINT [FK_FlightData_AirLine]
GO
ALTER TABLE [dbo].[HotelPrice] WITH CHECK ADD CONSTRAINT [FK_ProductPrice_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_ProductPrice_Product]
GO
ALTER TABLE [dbo].[Luggage] WITH CHECK ADD CONSTRAINT [FK_Luggage_AirLine] FOREIGN KEY([AirLineID])
REFERENCES [dbo].[AirLine] ([AirLineID])
GO
ALTER TABLE [dbo].[Luggage] CHECK CONSTRAINT [FK_Luggage_AirLine]
GO
ALTER TABLE [dbo].[RoomOccupation] WITH CHECK ADD CONSTRAINT [FK_RoomOccupation_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[RoomOccupation] CHECK CONSTRAINT [FK_RoomOccupation_Product]
GO
ALTER TABLE [dbo].[RoomService] WITH CHECK ADD CONSTRAINT [FK_RoomService_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[RoomService] CHECK CONSTRAINT [FK_RoomService_Product]
GO
ALTER TABLE [dbo].[RoomType] WITH CHECK ADD CONSTRAINT [FK_RoomType_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[RoomType] CHECK CONSTRAINT [FK_RoomType_Product]
GO
ALTER TABLE [dbo].[TourDestination] WITH CHECK ADD CONSTRAINT [FK_PackageBestim_Bestiming] FOREIGN KEY([BestomingID])
REFERENCES [dbo].[Destination] ([BestimID])
GO
ALTER TABLE [dbo].[TourDestination] CHECK CONSTRAINT [FK_PackageBestim_Bestiming]
GO
ALTER TABLE [dbo].[TourDestination] WITH CHECK ADD CONSTRAINT [FK_PackageBestim_Package] FOREIGN KEY([PackageID])
REFERENCES [dbo].[Tour] ([PackageID])
GO
ALTER TABLE [dbo].[TourDestination] CHECK CONSTRAINT [FK_PackageBestim_Package]
GO
ALTER TABLE [dbo].[TourDuration] WITH CHECK ADD CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([PackageID])
REFERENCES [dbo].[Tour] ([PackageID])
GO
ALTER TABLE [dbo].[TourDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]
GO

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  July 5, 2017 at 12:41 pm

  #1949557

  Closing: post replies here: https://www.sqlservercentral.com/Forums/1884715/How-to-make-relation-between-tables-to-calculate-cost-of-flight-and-transfer-and-hotel-and-excursion?Update=1#bm1884820

• June 19, 2019 at 2:01 pm

  This was removed by the editor as SPAM