-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathHotel_Project_data.sql
97 lines (97 loc) · 26.5 KB
/
Hotel_Project_data.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
USE [Hotel_Project]
GO
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_Adam', N'adam123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_Alice', N'alice123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_David', N'david123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_Jack', N'jack123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_James', N'james123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_John', N'john123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_Kevin', N'kevin123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_Mansour', N'mansour123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_Maria', N'maria123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_Mark', N'mark123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_Nancy', N'nancy123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'EMP_Sofia', N'sofia123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'USER_Anna', N'anna123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'USER_Carol', N'carol123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'USER_Daniel', N'daniel123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'USER_Jerry', N'jerry123')
INSERT [dbo].[LoginAccount] ([UserName], [Password]) VALUES (N'USER_Roy', N'roy123')
GO
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [DefaultSalary]) VALUES (N'CEO', N'Head Manager', N'Controls everything in the system', 200000)
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [DefaultSalary]) VALUES (N'HouEMP', N'HouseKeeping', N'Responsible for cleaning rooms', 2000)
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [DefaultSalary]) VALUES (N'MGR', N'Manager', N'Controls all employees', 100000)
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [DefaultSalary]) VALUES (N'OrgEMP', N'Organizer', N'Responsible for organizing events', 7500)
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [DefaultSalary]) VALUES (N'RecEMP', N'Receptionist', N'Responsible for reserving and checking up the rooms', 10000)
GO
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'David', N'M', N'Bryan', 12222, N'MGR', N'M', CAST(N'1955-12-08' AS Date), N'638 Voss, Houston, TX', N'14844493857', 12345, 100000, N'EMP_David')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'Mark', N'F', N'Jerry', 12345, N'CEO', N'M', CAST(N'1965-09-11' AS Date), N'731 Fondren, Houston, TX', N'14844493863', NULL, 250000, N'EMP_Mark')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'John', N'L', N'Tom', 13333, N'MGR', N'M', CAST(N'1937-11-10' AS Date), N'3321,Castle, Spring, TX', N'14844493500', 12345, 22000, N'EMP_John')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'Sofia', N'Z', N'Labib', 14444, N'MGR', N'F', CAST(N'1979-03-29' AS Date), N'975 Fire Oak, Humble, TX', N'15852135945', 12345, 45000, N'EMP_Sofia')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'Maria', N'F', N'Habib', 15555, N'RecEMP', N'F', CAST(N'1972-07-31' AS Date), N'980 Dallas, Houston, TX', N'15852135888', 12222, 10000, N'EMP_Maria')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'Jack', N'S', N'Peter', 16666, N'RecEMP', N'M', CAST(N'1972-07-11' AS Date), N'523 Rice, Houston, T', N'158521357231', 12222, 11000, N'EMP_Jack')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'Alice', N'M', N'Alex', 17777, N'OrgEMP', N'F', CAST(N'1965-09-08' AS Date), N'291 Rice, Bellaire, TX', N'15811223945', 14444, 7500, N'EMP_Alice')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'Nancy', N'K', N'David', 18888, N'OrgEMP', N'F', CAST(N'1985-03-29' AS Date), N'975 Fire Oak, Ohio, TX', N'14822293863', 14444, 7500, N'EMP_Nancy')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'Mansour', N'M', N'Ibrahim', 19999, N'HouEMP', N'M', CAST(N'1957-05-10' AS Date), N'3321,Castle, Summer, TX', N'14844421863', 13333, 2500, N'EMP_Mansour')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'James', N'Z', N'Lebron', 20000, N'HouEMP', N'M', CAST(N'1969-07-04' AS Date), N'5631 Rice, Alaska, T', N'15852132345', 13333, 2000, N'EMP_James')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'Kevin', N'A', N'Green', 21111, N'HouEMP', N'M', CAST(N'1967-03-11' AS Date), N'451 Dallas, Houston, TX', N'15898765945', 13333, 2000, N'EMP_Kevin')
INSERT [dbo].[Employee] ([FName], [Minit], [LName], [SSN], [RoleID], [Gender], [BirthDate], [Address], [MobileNO], [SuperSSN], [Salary], [UserName]) VALUES (N'Adam', N'J', N'Bran', 22222, N'HouEMP', N'M', CAST(N'1975-12-31' AS Date), N'975 Fire Oak, California, TX', N'15823137785', 13333, 2200, N'EMP_Adam')
GO
INSERT [dbo].[Dependents] ([FName], [LName], [ESSN], [Gender], [Relationship]) VALUES (N'Akron', N'Lebron', 20000, N'M', N'Brother')
INSERT [dbo].[Dependents] ([FName], [LName], [ESSN], [Gender], [Relationship]) VALUES (N'Fatma', N'Ibrahim', 19999, N'F', N'Sister')
INSERT [dbo].[Dependents] ([FName], [LName], [ESSN], [Gender], [Relationship]) VALUES (N'Jason', N'Jerry', 12345, N'M', N'Son')
INSERT [dbo].[Dependents] ([FName], [LName], [ESSN], [Gender], [Relationship]) VALUES (N'Kevin', N'Peter', 16666, N'M', N'Brother')
INSERT [dbo].[Dependents] ([FName], [LName], [ESSN], [Gender], [Relationship]) VALUES (N'Mark', N'John', 15555, N'M', N'Son')
INSERT [dbo].[Dependents] ([FName], [LName], [ESSN], [Gender], [Relationship]) VALUES (N'Savannah', N'James', 20000, N'F', N'Wife')
GO
INSERT [dbo].[Events] ([EventNO], [StartDate], [EndDate], [EventCost], [Description]) VALUES (1, CAST(N'2022-01-05' AS Date), CAST(N'2022-01-07' AS Date), 1000, N'Camping event. The Journey will start with us going to the desert and doing safari, then we will camp near the cliff.')
INSERT [dbo].[Events] ([EventNO], [StartDate], [EndDate], [EventCost], [Description]) VALUES (2, CAST(N'2022-01-08' AS Date), CAST(N'2022-01-09' AS Date), 500, N'Disco party featuring diplo DJ.')
INSERT [dbo].[Events] ([EventNO], [StartDate], [EndDate], [EventCost], [Description]) VALUES (3, CAST(N'2022-02-10' AS Date), CAST(N'2022-02-12' AS Date), 3000, N'Daily trips around California and special breakfast and lunch. With access to the water park near the beach.')
INSERT [dbo].[Events] ([EventNO], [StartDate], [EndDate], [EventCost], [Description]) VALUES (4, CAST(N'2022-03-10' AS Date), CAST(N'2022-03-11' AS Date), 300, N'Coldplay concert.')
GO
INSERT [dbo].[Organize] ([ESSN], [EventNO]) VALUES (17777, 1)
INSERT [dbo].[Organize] ([ESSN], [EventNO]) VALUES (17777, 4)
INSERT [dbo].[Organize] ([ESSN], [EventNO]) VALUES (18888, 2)
INSERT [dbo].[Organize] ([ESSN], [EventNO]) VALUES (18888, 3)
GO
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (1, N'Single', N'City', N'A single bedroom with city view', 1000)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (2, N'Single', N'Pool', N'A single bedroom with pool view', 1500)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (3, N'Double', N'Pool', N'A double bedroom with a pool view', 2500)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (4, N'Double', N'Sea', N'A double bedroom with sea view', 3000)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (5, N'Double', N'Mountain', N'A double bedroom with a mountain view', 3000)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (6, N'King', N'Pool', N'A room with king bed and a pool view', 4000)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (7, N'King', N'Sea', N'A room with a king bed and a sea view', 4500)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (8, N'King', N'Mountain', N'A room with king bed and a mountain view', 5000)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (9, N'Suite', N'Sea', N'A room with one or more bedrooms and a separate living space with sea view', 6000)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (10, N'Suite', N'Mountain', N'A room with one or more bedrooms and a separate living space with mountain view', 6500)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType], [RoomView], [Description], [Price]) VALUES (11, N'Super Suite', N'Sea', N'A super suite has more than one bedroom and a living space with a strong emphasis on grand in-room decoration, high-quality amenities and supplies, and tailor-made services with sea view', 50000)
GO
INSERT [dbo].[Room] ([RoomNO], [RoomType], [Occupancy], [Cleaned], [ESSN]) VALUES (1, 1, 1, N'F', 19999)
INSERT [dbo].[Room] ([RoomNO], [RoomType], [Occupancy], [Cleaned], [ESSN]) VALUES (2, 8, 0, N'T', 20000)
INSERT [dbo].[Room] ([RoomNO], [RoomType], [Occupancy], [Cleaned], [ESSN]) VALUES (3, 3, 1, N'F', 21111)
INSERT [dbo].[Room] ([RoomNO], [RoomType], [Occupancy], [Cleaned], [ESSN]) VALUES (4, 9, 1, N'F', 22222)
INSERT [dbo].[Room] ([RoomNO], [RoomType], [Occupancy], [Cleaned], [ESSN]) VALUES (5, 11, 0, N'T', 19999)
GO
INSERT [dbo].[User] ([FName], [Minit], [LName], [SSN], [Gender], [BirthDate], [Address], [MobileNO], [UserName]) VALUES (N'Roy', N'S', N'Yasser', 55555, N'M', CAST(N'2001-09-12' AS Date), N'980 Manhutten, Newyork TX', N'15223335945', N'USER_Roy')
INSERT [dbo].[User] ([FName], [Minit], [LName], [SSN], [Gender], [BirthDate], [Address], [MobileNO], [UserName]) VALUES (N'Jerry', N'M', N'Jack', 56666, N'M', CAST(N'1986-01-01' AS Date), N'5631 Rice, California,T', N'15852137891', N'USER_Jerry')
INSERT [dbo].[User] ([FName], [Minit], [LName], [SSN], [Gender], [BirthDate], [Address], [MobileNO], [UserName]) VALUES (N'Anna', N'N', N'Rodri', 57777, N'F', CAST(N'1990-02-10' AS Date), N'3321,Castle, Winter, TX', N'14844255863', N'USER_Anna')
INSERT [dbo].[User] ([FName], [Minit], [LName], [SSN], [Gender], [BirthDate], [Address], [MobileNO], [UserName]) VALUES (N'Carol', N'K', N'Mark', 58888, N'F', CAST(N'1980-03-14' AS Date), N'234 Berry, California, TX', N'14844494456', N'USER_Carol')
INSERT [dbo].[User] ([FName], [Minit], [LName], [SSN], [Gender], [BirthDate], [Address], [MobileNO], [UserName]) VALUES (N'Daniel', N'F', N'Williams', 59999, N'M', CAST(N'1987-10-05' AS Date), N'975 Fire Oak, NewYork, TX', N'14844777763', N'USER_Daniel')
GO
INSERT [dbo].[Attend] ([USSN], [EventNO]) VALUES (55555, 2)
INSERT [dbo].[Attend] ([USSN], [EventNO]) VALUES (57777, 1)
INSERT [dbo].[Attend] ([USSN], [EventNO]) VALUES (59999, 4)
GO
INSERT [dbo].[Reservation] ([ReservationNO], [StartDate], [EndDate], [USSN], [RoomNO], [BillNO]) VALUES (1, CAST(N'2022-01-04' AS Date), CAST(N'2022-01-07' AS Date), 55555, 1, 1)
INSERT [dbo].[Reservation] ([ReservationNO], [StartDate], [EndDate], [USSN], [RoomNO], [BillNO]) VALUES (2, CAST(N'2022-01-05' AS Date), CAST(N'2022-01-07' AS Date), 56666, 3, 2)
INSERT [dbo].[Reservation] ([ReservationNO], [StartDate], [EndDate], [USSN], [RoomNO], [BillNO]) VALUES (3, CAST(N'2022-02-09' AS Date), CAST(N'2022-02-12' AS Date), 57777, 4, 3)
GO
INSERT [dbo].[Companion] ([FName], [Minit], [LName], [USSN], [Gender], [BirthDate], [Relationship]) VALUES (N'Antoni', N'M', N'Gerrard', 57777, N'M', CAST(N'1990-07-14' AS Date), N'Husband')
INSERT [dbo].[Companion] ([FName], [Minit], [LName], [USSN], [Gender], [BirthDate], [Relationship]) VALUES (N'Frank', N'W', N'Brown', 59999, N'M', CAST(N'1950-11-24' AS Date), N'Father')
INSERT [dbo].[Companion] ([FName], [Minit], [LName], [USSN], [Gender], [BirthDate], [Relationship]) VALUES (N'Maria', N'S', N'Alex', 59999, N'F', CAST(N'1955-09-12' AS Date), N'Mother')
INSERT [dbo].[Companion] ([FName], [Minit], [LName], [USSN], [Gender], [BirthDate], [Relationship]) VALUES (N'Sally', N'A', N'Josef', 56666, N'F', CAST(N'1969-12-05' AS Date), N'Sister')
GO
INSERT [dbo].[Bill] ([BillNO], [EventCost], [RoomCost]) VALUES (1, 500, 3000)
INSERT [dbo].[Bill] ([BillNO], [EventCost], [RoomCost]) VALUES (2, NULL, 5000)
INSERT [dbo].[Bill] ([BillNO], [EventCost], [RoomCost]) VALUES (3, 3000, 18000)
GO