1. USE [master]
2. GO
3.  
4. IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MyTour')
5. BEGIN
6.         ALTER DATABASE MyTour SET OFFLINE WITH ROLLBACK IMMEDIATE;
7.         ALTER DATABASE MyTour SET ONLINE;
8.         DROP DATABASE MyTour ;
9. END
10. GO
11.  
12. CREATE DATABASE MyTour
13. GO
14.  
15. USE MyTour
16. GO
17.  
18. CREATE TABLE Membership( --2
19.         MembershipID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
20.         MembershipStatus nvarchar(50) NOT NULL,
21.         MembershipDiscount FLOAT,
22. )
23.  
24. CREATE TABLE Customer( --1
25.         ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
26.         [name] nvarchar(50) NOT NULL,
27.         [address] nvarchar(80) NOT NULL,
28.         email CHAR(50) NOT NULL,
29.         phone INT NOT NULL,
30.         MembershipID INT FOREIGN KEY REFERENCES Membership(MembershipID)
31. )
32.  
33. CREATE TABLE Restaurant( --3
34.         BookingID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
35.         [name] nvarchar(50) NOT NULL,
36.         [Address] nvarchar(50) NOT NULL,
37.         pricePerPeople FLOAT NOT NULL,
38.         NumberOfPeople INT NOT NULL
39. )
40.  
41. CREATE TABLE Tour( --4
42.         BookingID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
43.         [name] nvarchar(50) NOT NULL,
44.         [Location] nvarchar(50) NOT NULL,
45.         Duration nvarchar(50) NOT NULL,
46.         DepartureTime nvarchar(50) NOT NULL,
47.         Vehicle nvarchar(50) NOT NULL,
48.         NumberOfPeople INT NOT NULL,
49.         [TYPE] nvarchar(50) NOT NULL,
50.         PricePerPeople FLOAT NOT NULL
51. )
52.  
53. CREATE TABLE Flight( --5
54.         FlightID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
55.         FromLocation nvarchar(50) NOT NULL,
56.         ToLocation nvarchar(50) NOT NULL,
57.         AirlineName nvarchar(50) NOT NULL,
58.         ArrivalDate nvarchar(50) NOT NULL,
59.         DepartureDate nvarchar(50) NOT NULL,
60.         SeatClass nvarchar(50) NOT NULL
61. )
62.  
63. CREATE TABLE Hotel( --6
64.         BookingID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
65.         [Name] nvarchar(50) NOT NULL,
66.         [Address] nvarchar(50) NOT NULL,
67.         NumberOfRoom INT NOT NULL,
68.         NumberOfPeople INT NOT NULL,
69.         ArrivalDate nvarchar(50) NOT NULL,
70.         DepartureDate nvarchar(50) NOT NULL,
71.         PricePerNight FLOAT NOT NULL,
72.         AdditionalRequest nvarchar(50) NOT NULL,
73.         BillingMethod nvarchar(50) NOT NULL
74. )
75.  
76. CREATE TABLE Homestay( --7
77.         BookingID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
78.         [Name] nvarchar(50) NOT NULL,
79.         [Address] nvarchar(50) NOT NULL,
80.         NumberOfPeople INT NOT NULL,
81.         ArrivalDate nvarchar(50) NOT NULL,     
82.         DepartureDate nvarchar(50) NOT NULL,
83.         NumberOfNight INT NOT NULL,
84.         PricePerNight FLOAT NOT NULL
85. )
86.  
87. CREATE TABLE [ORDER]( --8
88.         OrderID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
89.         BookingDate nvarchar(50) NOT NULL,
90.         --ID int foreign key references Customer(ID),
91.         --BookingID int foreign key references OrderDetails(BookingID)
92. )
93.  
94. CREATE TABLE OrderDetails( --9
95.         BookingID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
96.         [Name] nvarchar(50) NOT NULL,
97.         Category nvarchar(50) NOT NULL,
98.         TotalPrice FLOAT NOT NULL,
99.         Discount FLOAT NOT NULL,
100.         PaymentMethod nvarchar(50) NOT NULL,
101.         OrderID INT FOREIGN KEY REFERENCES [ORDER](OrderID),
102.         BookingID1 INT FOREIGN KEY REFERENCES Restaurant(BookingID),
103.         BookingID2 INT FOREIGN KEY REFERENCES Tour(BookingID),
104.         BookingID3 INT FOREIGN KEY REFERENCES Hotel(BookingID),
105.         BookingID4 INT FOREIGN KEY REFERENCES Homestay(BookingID),
106.         FlightID INT FOREIGN KEY REFERENCES Flight(FlightID)
107. )
108.  
109. CREATE TABLE RestaurantSize( --10
110.         BookingID INT FOREIGN KEY REFERENCES Restaurant(BookingID),
111.         NumberOfChildren INT NOT NULL,
112.         NumberOfAdult INT NOT NULL
113. )
114.  
115. CREATE TABLE HomestaySize( --11
116.         BookingID INT FOREIGN KEY REFERENCES HomeStay(BookingID),
117.         NumberOfChildren INT NOT NULL,
118.         NumberOfAdult INT NOT NULL
119. )
120.  
121. CREATE TABLE HotelSize( --12
122.         BookingID INT FOREIGN KEY REFERENCES Hotel(BookingID),
123.         NumberOfChildren INT NOT NULL,
124.         NumberOfAdult INT NOT NULL
125. )
126.  
127. ALTER TABLE Flight ADD ticketPrice FLOAT NOT NULL
128.  
129. CREATE TABLE SeatClass( --13
130.         FlightID INT FOREIGN KEY REFERENCES Flight(FlightID),
131.         NumberOfPeople INT NOT NULL,
132.         TicketPrice FLOAT NOT NULL
133. )