-
Notifications
You must be signed in to change notification settings - Fork 0
/
DDL for HMS.sql
526 lines (437 loc) · 12.9 KB
/
DDL for HMS.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
/*
SQL Project Name : Hotel Booking System(HBS)
Trainee Name : Md. Masum Kazi
Trainee ID : 1279032
Batch ID : WADA/PNTL-A/56/1
__________________________________________________________________________________________________________________________
Table of Contents: DDL
----------------------
SECTION 01 -> Created a Database [HBS]
SECTION 02 -> Created Appropriate Tables with column definition related to the project
SECTION 03 -> ALTER, DROP AND MODIFY TABLES & COLUMNS
SECTION 04 -> CREATE CLUSTERED AND NONCLUSTERED INDEX
SECTION 05 -> CREATE SEQUENCE & ALTER SEQUENCE
SECTION 06 -> CREATE A VIEW & ALTER VIEW
SECTION 07 -> CREATE STORED PROCEDURE & ALTER STORED PROCEDURE
SECTION 08 -> CREATE FUNCTION(SCALAR, SIMPLE TABLE VALUED, MULTISTATEMENT TABLE VALUED) & ALTER FUNCTION
SECTION 09 -> CREATE TRIGGER (FOR/AFTER TRIGGER)
SECTION 10 -> CREATE TRIGGER (INSTEAD OF TRIGGER)
------------------------------------------------------------------------------------------------------------
______________________________ SECTION 01
CREATE DATABASE WITH ATTRIBUTES
______________________________*/
CREATE DATABASE HBS
ON
(
name = 'hbs_data',
filename = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA\hbs_data',
size = 5MB,
maxsize = 50MB,
filegrowth = 5%
)
LOG ON
(
name = 'hbs_log',
filename = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA\hbs_log',
size = 8MB,
maxsize = 40MB,
filegrowth = 5MB
)
GO
USE HBS
GO
/*______________________________ SECTION 02
CREATE TABLES WITH COLUMN DEFINITION
______________________________*/
----------------- Table with IDENTITY, PRIMARY KEY & nullability CONSTRAINT...
CREATE TABLE Room_Types
(
Room_Type_ID INT PRIMARY KEY,
Room_Type_Name NVARCHAR(50) NOT NULL,
Room_Type_Description NVARCHAR(100),
Room_Type_Capacity INT NOT NULL,
Room_Type_Price MONEY NOT NULL
);
----------------- Table with PRIMARY KEY & FOREIGN KEY & DEFAULT CONSTRAINT...
CREATE TABLE Rooms
(
Room_Number INT,
Room_Type_ID INT,
Floor_ID INT,
Room_Status NVARCHAR(50),
PRIMARY KEY (Room_Number, Room_Type_ID),
FOREIGN KEY (Room_Type_ID) REFERENCES Room_Types (Room_Type_ID)
);
GO
----------------- Table with CHECK CONSTRAINT & set CONSTRAINT name
CREATE TABLE Guests
(
[Guest ID] INT IDENTITY PRIMARY KEY,
[First Name] NVARCHAR(50),
[Last Name] NVARCHAR(50),
Guest_Address NVARCHAR(50),
Guest_City NVARCHAR(50),
Guest_Country NVARCHAR(50),
DOB DATE NOT NULL,
Email NVARCHAR(50) NOT NULL,
[Mobile No.] NVARCHAR(50) NOT NULL,
CONSTRAINT CHK_Guests_ValidDOB CHECK (DOB <= GETDATE()) -- Constraint to check valid DOB
);
GO
CREATE TABLE Bookings
(
Booking_ID INT PRIMARY KEY,
Guest_ID INT,
Room_Number INT,
Room_Type_ID INT,
Booking_Date DATETIME DEFAULT GETDATE(),
Check_In_Date DATE,
Check_Out_Date DATE,
Kids_Number INT,
Booking_Status NVARCHAR(50),
FOREIGN KEY (Guest_ID) REFERENCES Guests ([Guest ID]),
FOREIGN KEY (Room_Number, Room_Type_ID) REFERENCES Rooms (Room_Number, Room_Type_ID)
);
GO
CREATE TABLE Payments
(
Payment_ID INT PRIMARY KEY NOT NULL,
Booking_ID INT NOT NULL,
Payment_Date DATETIME DEFAULT GETDATE(),
Payment_Method NVARCHAR(50) NOT NULL,
Amount MONEY,
FOREIGN KEY (Booking_ID) REFERENCES Bookings (Booking_ID)
);
GO
CREATE TABLE Countries
(
Country NVARCHAR(50) PRIMARY KEY
);
GO
----------------- Table with composite PRIMARY KEY
CREATE TABLE Cities
(
City NVARCHAR(50) NOT NULL,
Country NVARCHAR(50) NOT NULL,
PRIMARY KEY (City, Country),
FOREIGN KEY (Country) REFERENCES Countries (Country)
);
GO
----------------- CREATE A SCHEMA
CREATE SCHEMA hbs
GO
----------------- USE SCHEMA IN A TABLE
CREATE TABLE hbs.tblCommentsInfo
(
commentId INT,
comment NVARCHAR(100) NULL,
commenterAge INT NULL
)
GO
/*______________________________ SECTION 03
ALTER, DROP AND MODIFY TABLES & COLUMNS
______________________________*/
CREATE TABLE SampleTable
(
ID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Age INT,
Email NVARCHAR(100),
IsActive BIT
);
GO
----------------- Update column definition
ALTER TABLE Payments
ALTER COLUMN Payment_Method NVARCHAR(40) NOT NULL
GO
----------------- ADD column with DEFAULT CONSTRAINT
ALTER TABLE Guests
ADD Age INT DEFAULT 18
GO
----------------- ADD CHECK CONSTRAINT with defining name
ALTER TABLE Guests
ADD
CONSTRAINT CK_emailValidate CHECK(Email LIKE '%@%' )
GO
----------------- DROP COLUMN
ALTER TABLE SampleTable
DROP COLUMN IsActive
GO
----------------- DROP TABLE
DROP TABLE SampleTable
GO
----------------- DROP SCHEMA
--DROP SCHEMA hbs
--GO
/*______________________________ SECTION 04
CREATE CLUSTERED AND NONCLUSTERED INDEX
______________________________*/
----------------- NON Clustered Index
CREATE UNIQUE NONCLUSTERED INDEX cityIndex
ON Cities
(
City
)
GO
/*______________________________ SECTION 05
CREATE SEQUENCE
______________________________*/
CREATE SEQUENCE seqNum
AS INT
START WITH 10
INCREMENT BY 15
MINVALUE 0
MAXVALUE 500
CYCLE
CACHE 5
GO
--============== ALTER SEQUENCE ============--
ALTER SEQUENCE seqNum
MAXVALUE 300
CYCLE
CACHE 7
GO
/*______________________________ SECTION 06
CREATE A VIEW
______________________________*/
CREATE VIEW VW_GuestsInfo
AS
SELECT [Guest ID], [First Name], Guest_Address
FROM Guests
GO
SELECT * FROM VW_GuestsInfo
GO
----------------- VIEW WITH ENCRYPTION, SCHEMABINDING & WITH CHECK OPTION
----------------- A VIEW to get today Bookings information
CREATE VIEW VW_TodayBookings
WITH SCHEMABINDING, ENCRYPTION
AS
SELECT Booking_ID, Guest_ID, Room_Number, Room_Type_ID
FROM dbo.Bookings
WHERE CONVERT(DATE, Booking_Date) = CONVERT(DATE, GETDATE())
WITH CHECK OPTION
GO
----------------- ALTER VIEW
ALTER VIEW VW_GuestsInfo
AS
SELECT [Guest ID], [First Name], Guest_Address, DOB, Guest_City, Guest_Country
FROM Guests
GO
/*______________________________ SECTION 07
STORED PROCEDURE
______________________________*/
----------------- STORED PROCEDURE for insert data using parameter
CREATE PROCEDURE AddGuest
@FirstName NVARCHAR(50),
@LastName NVARCHAR(50),
@DOB DATE,
@Email NVARCHAR(50),
@MobileNo NVARCHAR(50),
@GuestAddress NVARCHAR(50),
@GuestCity NVARCHAR(50),
@GuestCountry NVARCHAR(50)
AS
BEGIN
-- Insert the new guest record into the Guests table
INSERT INTO Guests ([First Name], [Last Name], DOB, Email, [Mobile No.], Guest_Address, Guest_City, Guest_Country)
VALUES (@FirstName, @LastName, @DOB, @Email, @MobileNo, @GuestAddress, @GuestCity, @GuestCountry);
SELECT 'Guest added successfully.' AS Result;
END;
GO
----------------- STORED PROCEDURE for insert data with OUTPUT parameter
CREATE PROCEDURE InsertRoomType
@Room_Type_Name NVARCHAR(50),
@Room_Type_Description NVARCHAR(100),
@Room_Type_Capacity INT,
@Room_Type_Price MONEY,
@New_Room_Type_ID INT OUTPUT
AS
BEGIN
-- Insert the new room type record into the Room_Types table
INSERT INTO Room_Types (Room_Type_Name, Room_Type_Description, Room_Type_Capacity, Room_Type_Price)
VALUES (@Room_Type_Name, @Room_Type_Description, @Room_Type_Capacity, @Room_Type_Price);
-- Get the newly generated Room_Type_ID
SET @New_Room_Type_ID = SCOPE_IDENTITY();
-- Return the newly generated Room_Type_ID as the output parameter
SELECT @New_Room_Type_ID AS NewRoomTypeID;
END;
GO
----------------- STORED PROCEDURE for UPDATE data
CREATE PROCEDURE UpdateRoom
@Room_Number INT,
@Room_Type_ID INT,
@Floor_ID INT,
@Room_Status NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
-- Check if the specified room exists
IF EXISTS (
SELECT 1
FROM Rooms
WHERE Room_Number = @Room_Number AND Room_Type_ID = @Room_Type_ID
)
BEGIN
-- Update the room details
UPDATE Rooms
SET
Floor_ID = @Floor_ID,
Room_Status = @Room_Status
WHERE Room_Number = @Room_Number AND Room_Type_ID = @Room_Type_ID;
SELECT 'Room updated successfully.' AS Result;
END
ELSE
BEGIN
SELECT 'Room not found. Update failed.' AS Result;
END
END;
GO
----------------- STORED PROCEDURE for DELETE Table data
CREATE PROCEDURE DeleteRoom
@Room_Number INT,
@Room_Type_ID INT
AS
BEGIN
-- Check if the specified room exists
IF EXISTS (
SELECT 1
FROM Rooms
WHERE Room_Number = @Room_Number AND Room_Type_ID = @Room_Type_ID
)
BEGIN
-- Delete the room
DELETE FROM Rooms
WHERE Room_Number = @Room_Number AND Room_Type_ID = @Room_Type_ID;
SELECT 'Room deleted successfully.' AS Result;
END
ELSE
BEGIN
SELECT 'Room not found. Deletion failed.' AS Result;
END
END;
GO
----------------- TRY CATCH IN A STORED PROCEDURE & RAISERROR WITH ERROR NUMBER AND ERROR MESSAGE
CREATE PROCEDURE DeleteRoomType
@Room_Type_ID INT
AS
BEGIN
BEGIN TRY
-- Delete the room type
DELETE FROM Room_Types
WHERE Room_Type_ID = @Room_Type_ID;
PRINT 'Room type deleted successfully.';
END TRY
BEGIN CATCH
-- Handle the error and print a simple error message
PRINT 'An error occurred while deleting the room type.';
PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH
END;
Go
----------------- ALTER STORED PROCEDURE
ALTER PROCEDURE DeleteRoom
@Room_Number INT,
@R_Type_ID INT
AS
BEGIN
-- Check if the specified room exists
IF EXISTS (
SELECT 1
FROM Rooms
WHERE Room_Number = @Room_Number AND Room_Type_ID = @R_Type_ID
)
BEGIN
-- Delete the room
DELETE FROM Rooms
WHERE Room_Number = @Room_Number AND Room_Type_ID = @R_Type_ID;
SELECT 'Room deleted successfully.' AS Result;
END
ELSE
BEGIN
SELECT 'Room not found. Deletion failed.' AS Result;
END
END;
GO
/*______________________________ SECTION 08
FUNCTION
______________________________*/
----------------- A SCALAR FUNCTION
-- A Scalar Function to get Current Year Total Net Sales
CREATE FUNCTION fnCurrentYearPayments()
RETURNS MONEY
AS
BEGIN
DECLARE @totalPayments MONEY
SELECT @totalPayments = SUM((Amount))
FROM Payments
WHERE YEAR(Payments.Amount) = YEAR(GETDATE())
RETURN @totalPayments
END
GO
----------------- A SIMPLE TABLE VALUED FUNCTION
----------------- A Inline Table Valued Function to get monthly total sales, discount & net sales using two parameter @year & @month
CREATE FUNCTION fnMonthlyPayments(@year INT, @month INT)
RETURNS TABLE
AS
RETURN
(
SELECT
SUM(Amount) AS 'Total Payments',
SUM(Amount) AS 'Net Amonu'
FROM Payments
WHERE YEAR(Payments.Payment_Date) = @year AND MONTH(Payments.Payment_Date) = @month
)
GO
----------------- ALTER FUNCTION
ALTER FUNCTION fnMonthlyPayments(@year INT, @month INT)
RETURNS TABLE
AS
RETURN
(
SELECT
SUM(Payment_ID) AS 'Total Payments',
SUM(Amount) AS 'Net Amonu'
FROM Payments
WHERE YEAR(Payments.Payment_Date) = @year AND MONTH(Payments.Payment_Date) = @month
)
GO
/*______________________________ SECTION 09
FOR/AFTER TRIGGER
______________________________*/
CREATE TRIGGER tr_RoomTypes_UpdateDescription
ON Room_Types
AFTER INSERT, UPDATE
AS
BEGIN
-- Update Room_Type_Description based on Room_Type_Name, Room_Type_Capacity, and Room_Type_Price
UPDATE Room_Types
SET
Room_Type_Description = rt.Room_Type_Name + ' - ' + CAST(rt.Room_Type_Capacity AS NVARCHAR(10)) + ' people, $' + CAST(rt.Room_Type_Price AS NVARCHAR(20))
FROM Room_Types rt
INNER JOIN inserted I ON rt.Room_Type_ID = I.Room_Type_ID;
END;
GO
/*______________________________ SECTION 10
INSTEAD OF TRIGGER
______________________________*/
----------------- AN INSTEAD OF TRIGGER ON VIEW
CREATE TRIGGER trVW_GuestsInfo
ON VW_GuestsInfo
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO VW_GuestsInfo([Guest ID], Guest_Address)
SELECT i.[Guest ID], i.Guest_City FROM inserted i
END
GO
----------------- ALTER TRIGGER
ALTER TRIGGER trVW_GuestsInfo
ON VW_GuestsInfo
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO VW_GuestsInfo(Guest_Country,[Guest ID], Guest_Address)
SELECT i.Guest_Country, i.[Guest ID], i.Guest_City FROM inserted i
END
GO