if exists (
select 1
2
3 from sys.sysreferences r
join sys.sysobjects o
on (o.id
= r.constid
and o.type
= ‘F‘)
4
5 where r.fkeyid
= object_id(
‘"Order"‘)
and o.name
= ‘FK_ORDER_ORDER_CUSTOMER‘)
6
7 alter table "
Order"
8
9 drop constraint FK_ORDER_ORDER_CUSTOMER
10
11 go
12
13
14
15 if exists (
select 1
16
17 from sys.sysreferences r
join sys.sysobjects o
on (o.id
= r.constid
and o.type
= ‘F‘)
18
19 where r.fkeyid
= object_id(
‘"Order"‘)
and o.name
= ‘FK_ORDER_ORDER2_ROOM‘)
20
21 alter table "
Order"
22
23 drop constraint FK_ORDER_ORDER2_ROOM
24
25 go
26
27
28
29 if exists (
select 1
30
31 from sys.sysreferences r
join sys.sysobjects o
on (o.id
= r.constid
and o.type
= ‘F‘)
32
33 where r.fkeyid
= object_id(
‘Room‘)
and o.name
= ‘FK_ROOM_CONTAIN_ROOMCATE‘)
34
35 alter table Room
36
37 drop constraint FK_ROOM_CONTAIN_ROOMCATE
38
39 go
40
41
42
43 if exists (
select 1
44
45 from sysobjects
46
47 where id
= object_id(
‘Customer‘)
48
49 and type
= ‘U‘)
50
51 drop table Customer
52
53 go
54
55
56
57 if exists (
select 1
58
59 from sysindexes
60
61 where id
= object_id(
‘"Order"‘)
62
63 and name
= ‘Order2_FK‘
64
65 and indid
> 0
66
67 and indid
< 255)
68
69 drop index "
Order".Order2_FK
70
71 go
72
73
74
75 if exists (
select 1
76
77 from sysindexes
78
79 where id
= object_id(
‘"Order"‘)
80
81 and name
= ‘Order_FK‘
82
83 and indid
> 0
84
85 and indid
< 255)
86
87 drop index "
Order".Order_FK
88
89 go
90
91
92
93 if exists (
select 1
94
95 from sysobjects
96
97 where id
= object_id(
‘"Order"‘)
98
99 and type
= ‘U‘)
100
101 drop table "
Order"
102
103 go
104
105
106
107 if exists (
select 1
108
109 from sysindexes
110
111 where id
= object_id(
‘Room‘)
112
113 and name
= ‘Contain_FK‘
114
115 and indid
> 0
116
117 and indid
< 255)
118
119 drop index Room.Contain_FK
120
121 go
122
123
124
125 if exists (
select 1
126
127 from sysobjects
128
129 where id
= object_id(
‘Room‘)
130
131 and type
= ‘U‘)
132
133 drop table Room
134
135 go
136
137
138
139 if exists (
select 1
140
141 from sysobjects
142
143 where id
= object_id(
‘RoomCategory‘)
144
145 and type
= ‘U‘)
146
147 drop table RoomCategory
148
149 go
150
151
152
153 /*==============================================================*/
154
155 /* Table: Customer */
156
157 /*==============================================================*/
158
159 create table Customer (
160
161 ID
varchar(
18)
not null,
162
163 name
varchar(
20)
null,
164
165 password
varchar(
20)
null,
166
167 isVIP
tinyint null,
168
169 constraint PK_CUSTOMER
primary key nonclustered (ID)
170
171 )
172
173 go
174
175
176
177 /*==============================================================*/
178
179 /* Table: "Order" */
180
181 /*==============================================================*/
182
183 create table "
Order" (
184
185 ID
varchar(
18)
not null,
186
187 roomNum
int not null,
188
189 beginDate
datetime null,
190
191 endDate
datetime null,
192
193 price
money null,
194
195 constraint PK_ORDER
primary key (ID, roomNum)
196
197 )
198
199 go
200
201
202
203 /*==============================================================*/
204
205 /* Index: Order_FK */
206
207 /*==============================================================*/
208
209 create index Order_FK
on "
Order" (
210
211 ID
ASC
212
213 )
214
215 go
216
217
218
219 /*==============================================================*/
220
221 /* Index: Order2_FK */
222
223 /*==============================================================*/
224
225 create index Order2_FK
on "
Order" (
226
227 roomNum
ASC
228
229 )
230
231 go
232
233
234
235 /*==============================================================*/
236
237 /* Table: Room */
238
239 /*==============================================================*/
240
241 create table Room (
242
243 roomNum
int not null,
244
245 Category
varchar(
18)
null,
246
247 constraint PK_ROOM
primary key nonclustered (roomNum)
248
249 )
250
251 go
252
253
254
255 /*==============================================================*/
256
257 /* Index: Contain_FK */
258
259 /*==============================================================*/
260
261 create index Contain_FK
on Room (
262
263 Category
ASC
264
265 )
266
267 go
268
269
270
271 /*==============================================================*/
272
273 /* Table: RoomCategory */
274
275 /*==============================================================*/
276
277 create table RoomCategory (
278
279 Category
varchar(
18)
not null,
280
281 cnt
int null,
282
283 price
money null,
284
285 constraint PK_ROOMCATEGORY
primary key nonclustered (Category)
286
287 )
288
289 go
290
291
292
293 alter table "
Order"
294
295 add constraint FK_ORDER_ORDER_CUSTOMER
foreign key (ID)
296
297 references Customer (ID)
298
299 go
300
301
302
303 alter table "
Order"
304
305 add constraint FK_ORDER_ORDER2_ROOM
foreign key (roomNum)
306
307 references Room (roomNum)
308
309 go
310
311
312
313 alter table Room
314
315 add constraint FK_ROOM_CONTAIN_ROOMCATE
foreign key (Category)
316
317 references RoomCategory (Category)
318
319 go
View Code
4.5其他设计
4.5.1存储过程设计
查询是否有该用户
1 if exists (select name from sysobjects where name = ‘select_exp‘ and type = ‘P‘)
2
3 drop procedure select_exp
4
5 GO
6
7 create procedure select_exp @id varchar(18),@passwords varchar(20)
8
9 as
10
11 select *
12
13 from Customer
14
15 where Customer.ID = id and Customer.password = @passwords
16
17 Go
View Code
查询该用户的订房清单
1 if exists (select name from sysobjects where name = ‘order_exp‘ and type = ‘P‘)
2
3 drop procedure order_exp
4
5 GO
6
7 create procedure order_exp @the_id varchar(18)
8
9 as
10
11 select Customer.name,[Order].beginDate,[Order].endDate,Room.Category,[Order].price
12
13 from Customer left join [Order] on Customer.ID = [Order].ID
14
15 left join Room on [Order].roomNum = Room.roomNum
16
17 where Customer.ID = [Order].ID and [Order].ID = @the_id
18
19 GO
View Code
查询房间的所有用房时间
1 if exists (select name from sysobjects where name = ‘orderbynum_exp‘ and type = ‘P‘)
2
3 drop procedure orderbynum_exp
4
5 GO
6
7 create procedure orderbynum_exp @type int
8
9 as
10
11 select [Order].beginDate,[Order].endDate
12
13 from [Order]
14
15 where [Order].roomNum = @type
16
17 Go
View Code
查询某类房间具体有哪些房间
1 if exists (select name from sysobjects where name = ‘room_exp‘ and type = ‘P‘)
2
3 drop procedure room_exp
4
5 GO
6
7 create procedure room_exp @type varchar(18)
8
9 as
10
11 select roomNum,price
12
13 from Room left join RoomCategory on Room.Category = RoomCategory.Category
14
15 where Room.Category = @type
16
17 Go
View Code
插入一条订房信息
1 if exists (select name from sysobjects where name = ‘insert_exp‘ and type = ‘P‘)
2
3 drop procedure insert_exp
4
5 GO
6
7 create procedure insert_exp @id varchar(18),@room int,@begin DateTime,@end DateTime,@money money
8
9 as
10
11 insert into [Order](ID,roomNum,beginDate,endDate,price)
12
13 values (@id,@room,@begin,@end,@money)
14
15 GO
16
17
View Code
4.5.2 触发器设计
--统计房间触发器
1 alter trigger cnt
2
3 on Room for insert
4
5 as
6
7 declare @type varchar(20),@tmp int
8
9 select @type = Category
10
11 from inserted
12
13 select @tmp = COUNT(*)
14
15 from Room
16
17 where Room.Category = @type
18
19 update RoomCategory
20
21 set cnt = @tmp
22
23 where RoomCategory.Category = @type
24
25 go
26
27
View Code
5 应用程序设计
应用程序采用C#制作，数据库连接使用ADO.NET
考虑到客户定房间只会给定一个房间种类，和入住时段，如何充分利用房间资源，给客户提供订房服务。这里的解决方案是，遍历所有该种类的房间，找出每个房间的入住时间，如果客户需求的时间不与任何一个时间段冲突，则将这间房间给客户，否则继续找下一个房间。
具体核心程序：
登录数据库：
//登录数据库
1 private void button1_Click(object sender, EventArgs e)
2
3 {
4
5 if(textBox1.Text=="")
6
7 {
8
9 MessageBox.Show("请输入要连接的数据库名");
10
11 }
12
13 else
14
15 {
16
17 try
18
19 {
20
21 //datastr = "Server=.;Database="+textBox1.Text.Trim()+";Trusted_Connection=SSPI";
22
23 datastr = "Data Source=.;" +"Persist Security Info=True;" + "Initial Catalog=Hotel;" + "Integrated Security=false;" + "User ID=sa;" + "Password=yinjian..m;";
24
25 conn = new SqlConnection(datastr);
26
27 conn.Open();
28
29 if (conn.State==ConnectionState.Open)
30
31 {
32
33 label2.Text = "数据库【" + textBox1.Text.Trim() + "】连接成功";
34
35 }
36
37 }
38
39 catch
40
41 {
42
43 }
44
45 }
46
47 }
View Code
用户身份验证及用户订房清单查询：
//登录身份
1 private void button3_Click(object sender, EventArgs e)
2
3 {
4
5 conn = new SqlConnection(datastr);
6
7 conn.Open();
8
9 id = textBox2.Text.Trim();
10
11 string password = textBox5.Text.Trim();
12
