-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathHotelManagementDatabase.sql
157 lines (129 loc) · 5.5 KB
/
HotelManagementDatabase.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
CREATE DATABASE HotelManagement
USE HotelManagement
GO
-- DROP DATABASE FOR TESTING
DROP DATABASE HotelManagement
--CREATE TABLE ACCOUNT FOR LOGIN
CREATE TABLE Account (
id INT IDENTITY (1, 1) PRIMARY KEY,
username VARCHAR(15) NOT NULL UNIQUE,
passwordHash BINARY(64) NOT NULL,
position VARCHAR(30)
)
CREATE TABLE RoomType(
roomTypeID INT IDENTITY (1,1) PRIMARY KEY,
roomTypeName VARCHAR(120) NOT NULL,
)
CREATE TABLE Room (
roomID INT IDENTITY (1,1) PRIMARY KEY,
roomName VARCHAR(15) NOT NULL,
roomTypeID INT FOREIGN KEY REFERENCES RoomType(roomTypeID),
--Room static Available, Rented, Dirty
roomStatus VARCHAR(120)
)
CREATE TABLE Customer (
cusID INT IDENTITY (1,1) PRIMARY KEY,
cusIdentityNumber VARCHAR (20) UNIQUE NOT NULL,
cusName NVARCHAR (200) NOT NULL,
cusGender VARCHAR(10),
cusDOB DATE,
cusPhone VARCHAR (12),
cusAddress NVARCHAR (250),
)
CREATE TABLE RoomBooking(
roomBookingID INT IDENTITY (1, 1) PRIMARY KEY,
roomID INT FOREIGN KEY REFERENCES Room(roomID),
employeeName VARCHAR(120) NOT NULL,
customerName VARCHAR(120) NOT NULL,
customerIdentityNumber VARCHAR(20) NOT NULL,
customerPhoneNumber VARCHAR(14) NOT NULL,
customerQuantity INT NOT NULL,
checkinTime DATETIME NOT NULL,
checkoutTime DATETIME NOT NULL,
prepaid MONEY NOT NULL
)
CREATE TABLE Checkin(
checkinID INT IDENTITY (1,1) PRIMARY KEY,
cusIdentityNumber VARCHAR(20) FOREIGN KEY REFERENCES Customer(cusIdentityNumber),
roomNumber VARCHAR(15) FOREIGN KEY REFERENCES Room(roomName),
checkinDate DATETIME,
checkoutDate DATE,
prepaid MONEY,
discount MONEY,
)
--Phuc
CREATE TABLE EmployeeInformation(
userID int foreign key references Account(id),
fullName varchar(30),
numberId varchar(30),
startWork date,
birthday date,
userEmail varchar(30),
userPhone varchar(20),
userAddress varchar(50),
deleted char(1)
)
INSERT INTO Account VALUES ('admin', HASHBYTES('SHA2_512', '123456'), 'Manager');
INSERT INTO Account VALUES ('ppdien', HASHBYTES('SHA2_512', '123'), 'Employee');
INSERT INTO Account VALUES ('nsan', HASHBYTES('SHA2_512', '456'), 'Employee');
INSERT INTO RoomType VALUES ('Single')
INSERT INTO RoomType VALUES ('Double')
INSERT INTO RoomType VALUES ('Single VIP')
INSERT INTO RoomType VALUES ('Double VIP')
INSERT INTO Room VALUES ('101', 1, 'Available')
INSERT INTO Room VALUES ('102', 2, 'Rented')
INSERT INTO Room VALUES ('103', 3, 'Dirty')
INSERT INTO Room VALUES ('104', 4, 'Available')
INSERT INTO Room VALUES ('201', 1, 'Available')
INSERT INTO Room VALUES ('202', 2, 'Rented')
INSERT INTO Room VALUES ('203', 3, 'Available')
INSERT INTO Room VALUES ('204', 4, 'Available')
INSERT INTO Room VALUES ('301', 1, 'Available')
INSERT INTO Room VALUES ('302', 2, 'Rented')
INSERT INTO Room VALUES ('303', 3, 'Dirty')
INSERT INTO Room VALUES ('304', 4, 'Available')
INSERT INTO Customer VALUES ('0123456789', N'Nguyễn Văn Tèo', 'Male', '05/28/1995', '0905115448', N'Hải Châu, Đà Nẵng');
INSERT INTO Customer VALUES ('1112223334', N'Nguyễn Văn Tí', 'Male', '03/18/1999', '0905253664', N'Sơn Trà, Đà Nẵng');
INSERT INTO Customer VALUES ('2223334445', N'Quách Thị Tĩnh', 'Female', '08/21/1994', '0984557998', N'Ngũ Hành Sơn, Đà Nẵng');
INSERT INTO Customer VALUES ('5556667778', N'Trần Dần', 'Male', '05/16/1986', '0903558115', N'Thăng Bình, Quảng Nam');
INSERT INTO Customer VALUES ('8889991110', N'Lý Kim Thoa', 'Female', '09/22/1996', '0964889223', N'Hải Châu, Đà Nẵng');
--Procedure check account
CREATE PROC checkLogin @username VARCHAR(15), @pass VARCHAR(60) AS
SELECT * FROM Account AD WHERE AD.username = @username AND AD.passwordHash = HASHBYTES('SHA2_512', @pass)
GO
--Procudure check is manager or not
CREATE PROC checkIsManager @username VARCHAR(15), @position VARCHAR(30) AS
SELECT * FROM Account AC WHERE AC.username = @username AND AC.position = @position
GO
UPDATE Room SET roomStatus = 'Dirty' WHERE roomName = 101
UPDATE Room SET roomStatus = 'Dirty' WHERE roomName = 104
UPDATE Room SET roomStatus = 'Dirty' WHERE roomName = 202
UPDATE Room SET roomStatus = 'Dirty' WHERE roomName = 304
GO
-- Procudure confirm cleaned and change status room
CREATE PROC confirmCleanedRoom @roomName VARCHAR(15) AS
UPDATE Room SET roomStatus = 'Available' WHERE roomName = @roomName
GO
-- Procedure check and get customer name from customer Identiry number
CREATE PROC getCusNameFromIDNumber @idNum VARCHAR(20) AS
SELECT CUS.cusName FROM Customer CUS WHERE CUS.cusIdentityNumber = @idNum
GO
--Procedure get room type from room name
CREATE PROC getRoomType @roomName VARCHAR(15) AS
SELECT RT.roomTypeName FROM Room R JOIN RoomType RT ON R.roomTypeID = RT.roomTypeID WHERE R.roomName = @roomName
GO
-- Procudure add checkin
CREATE PROC addCheckin @cusIndentityNumber VARCHAR(20), @roomNumber VARCHAR(15), @checkinDate DATETIME, @checkoutDate DATE, @prepaid MONEY, @discount MONEY AS
INSERT INTO Checkin VALUES(@cusIndentityNumber, @roomNumber, @checkinDate, @checkoutDate, @prepaid, @discount)
GO
--Procudure add customer (only name and id)
CREATE PROC addNameAndIDCustomer @cusIdentityNumber VARCHAR(20), @cusName NVARCHAR(200) AS
INSERT INTO Customer(cusIdentityNumber, cusName) VALUES (@cusIdentityNumber, @cusName)
SELECT Room.roomName FROM Room
cusID INT IDENTITY (1,1) PRIMARY KEY,
cusIdentityNumber VARCHAR (20) UNIQUE NOT NULL,
cusName NVARCHAR (200) NOT NULL,
cusGender VARCHAR(10),
cusDOB DATE,
cusPhone VARCHAR (12),
cusAddress NVARCHAR (250),