· 8 years ago · Apr 30, 2018, 01:36 PM
1DROP DATABASE IF EXISTS 0408982209_test;
2CREATE DATABASE 0408982209_test;
3USE 0408982209_test;
4
5
6DROP TABLE IF EXISTS `Hotel`;
7CREATE TABLE `Hotel` (
8 `HotelID` int NOT NULL AUTO_INCREMENT,
9 `AddressID` int NOT NULL,
10 `HotelName` varchar(40) NOT NULL,
11 `TotalRooms` int NOT NULL,
12 PRIMARY KEY (
13 `HotelID`
14 )
15);
16
17DROP TABLE IF EXISTS `Employee`;
18CREATE TABLE `Employee` (
19 `EmpID` int NOT NULL AUTO_INCREMENT,
20 `EmpSSN` varchar(20),
21 `AddressID` int,
22 `Title` varchar(20) NOT NULL,
23 `LName` varchar(20),
24 `FName` varchar(20),
25 `User` varchar(20),
26 `Pass` varchar(20),
27 PRIMARY KEY (
28 `EmpID`
29 )
30);
31
32DROP TABLE IF EXISTS `Customer`;
33CREATE TABLE `Customer` (
34 `CusID` int NOT NULL AUTO_INCREMENT,
35 `CusSSN` varchar(20) NOT NULL,
36 `AddressID` int NOT NULL,
37 `LName` varchar(20) NOT NULL,
38 `FName` varchar(20) NOT NULL,
39 `Email` varchar(60) NOT NULL,
40 `Phone` varchar(15) NOT NULL,
41 `User` varchar(20) NOT NULL,
42 `Pass` varchar(20) NOT NULL,
43 PRIMARY KEY (
44 `CusID`
45 )
46);
47
48DROP TABLE IF EXISTS `Order`;
49CREATE TABLE `Order` (
50 `OrderID` int NOT NULL AUTO_INCREMENT,
51 `EmpID` int NOT NULL,
52 `CusID` int NOT NULL,
53 `OrderDate` datetime NOT NULL,
54 PRIMARY KEY (
55 `OrderID`
56 )
57);
58
59DROP TABLE IF EXISTS `Room`;
60CREATE TABLE `Room` (
61 `RoomID` int NOT NULL AUTO_INCREMENT,
62 `HotelID` int NOT NULL,
63 `TypeID` int NOT NULL,
64 `DoorNum` int NOT NULL,
65 PRIMARY KEY (
66 `RoomID`
67 )
68);
69
70DROP TABLE IF EXISTS `RoomType`;
71CREATE TABLE `RoomType` (
72 `TypeID` int NOT NULL AUTO_INCREMENT,
73 `RoomType` varchar(20) NOT NULL,
74 `Desc` varchar(200) NOT NULL,
75 `Cost` int NOT NULL,
76 PRIMARY KEY (
77 `TypeID`
78 )
79);
80
81DROP TABLE IF EXISTS `Staff`;
82CREATE TABLE `Staff` (
83 `EmpID` int NOT NULL,
84 `HotelID` int NOT NULL
85);
86
87DROP TABLE IF EXISTS `Address`;
88CREATE TABLE `Address` (
89 `AddressID` int NOT NULL AUTO_INCREMENT,
90 `Zip` varchar(20) NOT NULL,
91 `CityName` varchar(20) NOT NULL,
92 `CountryName` varchar(20) NOT NULL,
93 `StreetName` varchar(20) NOT NULL,
94 `BuildingNum` varchar(5) NOT NULL,
95 `ApartNum` varchar(5) NULL,
96 PRIMARY KEY (
97 `AddressID`
98 )
99);
100
101DROP TABLE IF EXISTS `Service_Order`;
102CREATE TABLE `Service_Order` (
103 `ServiceID` int NOT NULL AUTO_INCREMENT,
104 `RoomID` int NOT NULL,
105 `OrderID` int NOT NULL,
106 `EmpID` int NOT NULL,
107 `DateTimeOfService` datetime NOT NULL,
108 PRIMARY KEY (
109 `ServiceID`
110 )
111);
112
113DROP TABLE IF EXISTS `Item`;
114CREATE TABLE `Item` (
115 `ItemID` int NOT NULL AUTO_INCREMENT,
116 `ItemName` varchar(70) NOT NULL,
117 `ItemDetails` varchar(200) DEFAULT NULL,
118 `Cost` int,
119 PRIMARY KEY (
120 `ItemID`
121 )
122);
123
124DROP TABLE IF EXISTS `Order_Has_Items`;
125CREATE TABLE `Order_Has_Items` (
126 `ServiceID` int NOT NULL,
127 `ItemID` int NOT NULL
128);
129
130DROP TABLE IF EXISTS `Order_Has_Rooms`;
131CREATE TABLE `Order_Has_Rooms` (
132 `OrderID` int NOT NULL,
133 `RoomID` int NOT NULL,
134 `CheckInDate` datetime NOT NULL,
135 `CheckoutDate` datetime NOT NULL
136);
137
138ALTER TABLE `Hotel` ADD CONSTRAINT `fk_Hotel_AddressID` FOREIGN KEY(`AddressID`)
139REFERENCES `Address` (`AddressID`);
140
141ALTER TABLE `Employee` ADD CONSTRAINT `fk_Employee_AddressID` FOREIGN KEY(`AddressID`)
142REFERENCES `Address` (`AddressID`);
143
144ALTER TABLE `Customer` ADD CONSTRAINT `fk_Customer_AddressID` FOREIGN KEY(`AddressID`)
145REFERENCES `Address` (`AddressID`);
146
147ALTER TABLE `Order` ADD CONSTRAINT `fk_Order_EmpID` FOREIGN KEY(`EmpID`)
148REFERENCES `Employee` (`EmpID`);
149
150ALTER TABLE `Order` ADD CONSTRAINT `fk_Order_CusID` FOREIGN KEY(`CusID`)
151REFERENCES `Customer` (`CusID`);
152
153ALTER TABLE `Room` ADD CONSTRAINT `fk_Room_HotelID` FOREIGN KEY(`HotelID`)
154REFERENCES `Hotel` (`HotelID`);
155
156ALTER TABLE `Room` ADD CONSTRAINT `fk_Room_TypeID` FOREIGN KEY(`TypeID`)
157REFERENCES `RoomType` (`TypeID`);
158
159ALTER TABLE `Staff` ADD CONSTRAINT `fk_Staff_EmpID` FOREIGN KEY(`EmpID`)
160REFERENCES `Employee` (`EmpID`);
161
162ALTER TABLE `Staff` ADD CONSTRAINT `fk_Staff_HotelID` FOREIGN KEY(`HotelID`)
163REFERENCES `Hotel` (`HotelID`);
164
165ALTER TABLE `Service_Order` ADD CONSTRAINT `fk_Service_RoomID` FOREIGN KEY(`RoomID`)
166REFERENCES `Room` (`RoomID`);
167
168ALTER TABLE `Order_Has_Rooms` ADD CONSTRAINT `fk_Order_Has_Rooms_OrderID` FOREIGN KEY(`OrderID`)
169REFERENCES `Order` (`OrderID`);
170
171ALTER TABLE `Order_Has_Rooms` ADD CONSTRAINT `fk_Order_Has_Rooms_RoomID` FOREIGN KEY(`RoomID`)
172REFERENCES `Room` (`RoomID`);
173
174ALTER TABLE `Order_Has_Items` ADD CONSTRAINT `fk_Order_Has_Items_ServiceID` FOREIGN KEY(`ServiceID`)
175REFERENCES `Service_Order`(`ServiceID`);
176
177ALTER TABLE `Order_Has_Items` ADD CONSTRAINT `fk_Order_Has_Items_ItemID` FOREIGN KEY(`ItemID`)
178REFERENCES `Item`(`ItemID`);
179
180ALTER TABLE `Service_Order` ADD CONSTRAINT `fk_OrderID` FOREIGN KEY(`OrderID`)
181REFERENCES `Order`(`OrderID`);
182
183ALTER TABLE `Service_Order` ADD CONSTRAINT `fk_EmpID` FOREIGN KEY(`EmpID`)
184REFERENCES `Employee`(`EmpID`);