酒店管理系统数据库代码
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
酒店管理系统数据库代码
use Hotel_Management1
select*from Customer
select*from Employee
select*from RoomType
select*from Room
select*from OrderInfo
select*from Checkout
drop database Hotel_Management1
------------------------创建数据库Hotel_Management----------------------------------------------------------------------create database Hotel_MDB
on
primary
(name=Hotel_Management1,
filename='F:\Hotel_Management\Hotel_Management.MDF',
size=10MB,
filegrowth=20%
)
log on
(name=Hotel_Management1,
filename='F:\Hotel_Management\Hotel_Management1.LDF',
size=10MB,
filegrowth=2MB)
--使用数据库
USE Hotel_Management1
--------------------------------------------创建表---------------------------------------------------------------
--1顾客表
create table Customer
(CustomerID int primary key,
CustomerName nvarchar(40)not null,
CustomerInfo nvarchar(18)not null,
Csex nvarchar(1),
CPhone nvarchar(11)not null,
Notes ntext
)
--drop table Customer
--2员工表
create table Employee
(EmployeeID int primary key,
UserName nvarchar(40)not null,
Password nvarchar(40)not null,
EmployeeName nvarchar(40)not null,
Esex nvarchar(1),
EPhone nvarchar(11)not null,
Notes ntext
)
--3客房表(有外键)
create table Room
(RoomID int primary key,
RoomTypeID int not null,
RoomState nvarchar(1)not null,
Notes ntext,
FOREIGN KEY(RoomTypeID)REFERENCES RoomType(RoomTypeID), )
--drop table Room
--4客房类型表(有外键)
create table RoomType
(RoomTypeID int primary key,
RoomTypeName nchar(20)not null,
Cost float,
Total int,
Surplus int,
Notes ntext,
)
--drop table RoomType
--5订房表
select*from OrderInfo
create table OrderInfo
(OrderID int not null primary key,
RoomID int not null,
CustomerID int,
EmployeeID int,
Entertime datetime not null,
Deposit float,
ORstatic nvarchar(10)not null,
Notes ntext,
FOREIGN KEY(CustomerID)REFERENCES Customer(CustomerID), FOREIGN KEY(RoomID)REFERENCES Room(RoomID),
FOREIGN KEY(EmployeeID)REFERENCES Employee(EmployeeID), )
--alter table add constraint OI_D ORstatic default 'use'
--drop table OrderInfo
--6退房表check-out
create table Checkout
(CheckoutID int primary key,
RoomID int not null,
CustomerID int,
EmployeeID int,
Entertime datetime not null,
Endtime datetime not null,
Total_consumption float,
,
ntext Notes
FOREIGN KEY(EmployeeID)REFERENCES Employee(EmployeeID),
FOREIGN KEY(CustomerID)REFERENCES Customer(CustomerID),
)
--drop table Checkout
sp_help Checkout
----------------------------------------表插入信息------------------------------------------------------------------------Employee表
insert into Employee values('zhoutonglu',123456,'董洁','f',,null)
insert into Employee values('liminghao',123456,'李明浩','m',,null)
insert into Employee values('yuxian',123456,'余香','f',,null)
select*from Employee
---RoomType表
select*from RoomType
insert into RoomType values(1,'单间',200,20,19,null)
insert into RoomType values(2,'标准间',260,20,19,null)
insert into RoomType values(3,'豪华单间',580,20,19,null)
insert into RoomType values(4,'行政套房',880,20,19,null)
----Room表
select*from Room
insert into Room values('1011',1,'Y',null)
insert into Room values('1012',1,'N',null)
insert into Room values('1021',2,'Y',null)
insert into Room values('1022',2,'N',null)
insert into Room values('1031',3,'Y',null)
insert into Room values('1032',3,'N',null)
insert into Room values('1041',4,'Y',null)
insert into Room values('1042',4,'N',null)
insert into Room values('1013',1,'Y',null)
insert into Room values('1014',1,'N',null)
insert into Room values('1023',2,'Y',null)
insert into Room values('1024',2,'N',null)
insert into Room values('1033',3,'Y',null)
insert into Room values('1034',3,'N',null)
insert into Room values('1051',4,'Y',null)
insert into Room values('1052',4,'N',null)
---Customer 表
select*from Customer
select*from Room