酒店管理系统数据库代码
酒店管理系统代码(C语言版)
＃include <stdio.h〉#include 〈stdlib。
h>＃include <string。
h>//房间信息结构体struct Node｛int Count; //指示该房间有多少个房客char nameOne［20］; //房客1的名字char nameTwo［20］; //房客2的名字int sexOne；//房客1的性别—1代表女，0代表没有，1代表男int sexTwo; //房客2的性别int roomNumber；//房间号｝roomArray［5];//初始化房间数组void InitArray（）｛int i；for（i=0；i<5；i++){roomArray[i］.roomNumber = 301+i;memset（roomArray［i]。
nameOne,0,20);memset(roomArray［i］。
nameTwo,0,20);roomArray[i].sexOne = 0；roomArray[i］.sexTwo = 0；roomArray［i］.Count = 0;}}void fun1（) //旅客入住的操作{char name[20］；int sex;int i；printf（”\n输入入住旅客姓名和性别(空格隔开,1为男，—1为女）:”）；scanf(”%s %d",name，＆sex);for(i=0；i<5;i++)｛if(roomArray[i]。
Count == 2）｛continue;｝else if（roomArray［i].Count == 1){if(roomArray［i]。
sexOne != sex）{continue；}strcpy(roomArray［i].nameTwo，name）；roomArray［i].sexTwo = sex;roomArray[i］。
Count++；system（"cls”)；printf（"客人已经成功入住,在房间％d”，roomArray[i］.roomNumber)；return；;}else{strcpy(roomArray[i]。
酒店管理系统实现代码
//入住import java。
awt。
＊；import java。
awt.event.*;import javax.swing.＊；import javax.swing.border。
Border;import javax。
swing。
text。
*；public class A1 extends JFrame implements WindowListener {//定义各个组件JFrame frame = new JFrame （"入住系统”)；JLabel nameLabel = new JLabel(”客户姓名”)；JLabel idtifyLabel = new JLabel（”身份证号”);JLabel roomLabel = new JLabel("入住房号")；JLabel moneyLabel = new JLabel（"应付金额"）;JTextField nameField = new JTextField（）;JTextField idtifyField= new JTextField(）;JTextField roomField= new JTextField（);JTextField moneyField= new JTextField（）；JButton button1，button2；/*public static void main(String args［]){A1 a1 = new A1（）;a1.go(）；｝＊/public void go（）｛UpperCaseDocument ucDocument = new UpperCaseDocument(）；nameField。
setDocument(ucDocument）;nameField.setForeground（Color。
black）；//frame.addWindowListener(this）；nameField.addActionListener(new ActionListener()｛public void actionPerformed（ActionEvent e)｛String username = nameField.getText(）；}｝）；JComboBox jcb1；//组合框String［］itemList = { "现金"，”刷卡”｝；jcb1 = new JComboBox(itemList）；jcb1.setSelectedIndex(0）；button1=new JButton("确认支付”）;button2=new JButton（”取消支付”）;final JPanel p1 = new JPanel();p1。
(完整版)C语言酒店管理系统
for(y=0;y<5;y++)
{
if(room[x][y]==0)
{
printf("空%d-%d |",x+1,y+1);
}else
{
printf("住%d-%d |",x+1,y+1);
}
if(y+1==5)
{
if(x+1==1||x+1==2)
printf("该层标间均50元");
else
printf("该层标间均100元");
printf("\t2.查询房间的入住情况\n");
printf("\t3.查询当前费用\n");
printf("\t4.结账退房\n");
printf("\t5.退出程序\n");
}
void hotel_execute();
void hotel_select();
void hotel_outhotel();
{
printf("|-------|--------|--------|--------|--------|--------|\n|第%d层|",x+|-------|--------|--------|--------|--------|--------|\n|第%d层|",x+1);
}
}
}
printf("\n|-------|--------|--------|--------|--------|--------|\n");
酒店管理系统数据库表和SQL
酒店管理系统数据库表和SQL010.tydis扩展:物价赔偿表采购表员工表数据库的表分别为:管理员信息表(operator)，房间类型表(roomType), 房间信息表(roomInfo),客户类型表(customerType)，客户信息表(customer)，入住信息表(liveIn)，预订信息表(engage)，结账表(account)，酒店简介表(companyIntroduce)，酒店新闻表(news)，天气信息表(temp)，特惠信息表(tehui)。
create table roomType(PK integer primary key, roomTypeID char(1) check(roomTypeIDin('A','B','C','D','E','F','G','H')) uniquenot null,roomTypeName varchar2(15) not null, roomPrice float not null, roomBedNum int,foregift float,cl_room int,cl_price float,remark varchar2(40),delmark int);/create table operator(PK integer primary key,userID varchar2(10) unique not null, pwd varchar2(10) not null,puis int,in_time date,login_num int);/create table customer(PK integer primary key,c_ID varchar2(20) unique not null, pwd varchar2(15) not null,c_name varchar2(15) not null, c_sex char(2) check(c_sex in('男','女')) not null, zj_type varchar2(15) not null, zj_no varchar2(20) not null, c_addr varchar2(40),c_tel varchar2(20) not null, remark varchar2(40),delmark int,c_type_id char(1),jifen int,foreign key(c_type_id) references customerType(customerID));/create table customerType(PK integer primary key,customerID char(1) check(customerID in('A','B','C','D','E','F','G')) unique notnull,c_type_name varchar2(15) not null, dis_attr varchar2(15),discount float,remark varchar2(40),delmark int);/create table roomInfo(PK integer primary key,roomID char(3) not null unique,roomTypeID char(1) not null, roomState char(1) check(RoomStatein('0','1')) not null,roomTel varchar2(10) not null, roomLoc varchar2(10),remark varchar2(40),delmark int,foreign key (roomTypeID) references roomType(roomTypeID));/create table liveIn(PK integer primary key,in_no varchar2(10) not null unique, roomID char(3) not null,c_ID varchar2(10),c_name varchar2(15) not null, c_sex char(2) check(c_sex in('男','女')) not null, zj_type varchar2(15) not null, zj_no varchar2(25) not null, addr varchar2(50),renshu int,in_time date,leave_time date,delmark int,--客户离开c_tel varchar2(20),foreign key (roomID) references roomInfo(roomID));/create table engage(PK integer primary key,c_ID varchar2(10) not null, roomID char(3) not null,engage_time date,in_time date,leave_time date,engage_mark int,delmark int,foreign key (roomID) references roomInfo(roomID),foreign key (c_ID) references customer(c_ID));/create table account(chk_ID varchar2(10) primary key, in_no varchar2(10) not null, chk_time date,days int,money float,userID varchar2(15),delmark int,foreign key (in_no) references liveIn(in_no),foreign key(userID) references operator(userID));/create table record(PK integer primary key,userID varchar2(15) not null, brief varchar2(20),in_no varchar2(10) not null, delmark int,foreign key (userID) references operator(userID),foreign key (in_no) references liveIn(in_no));/create table companyIntroduce( PK integer primary key, brief varchar2(2000));create table news(PK integer primary key, type varchar2(40) not null, title varchar2(40) not null, in_time date,brief varchar2(500));create table temp(PK integer primary key, day date,tempa int,tianqi varchar2(30));create table tehui(PK integer primary key, title varchar2(50) not null, brif varchar2(500),in_time date);--select deptno,max(sal) as max1,max(decode(t,2,sal)) asmax2,min(sal) as max3 from--(select empno,ename,sal,t,deptno from--(select empno,ename,sal,row_number() over (partition by deptno order by sal desc)t,deptno--from emp) e1--where e1.t<=3)--group by deptno每个房间类型住的人次数 num_per_roomtypeselect r.*,t.roomprice,t.roombednum from(select t.roomTypeID,t.roomTypeName,count(*) as num from roomType t,roomInfo r,liveIn iwhere i.roomID=r.roomID and r.roomTypeID=t.roomTypeID andi.in_time>=to_date('2012-1-1','yyyy-mm-dd') andi.leave_time<=to_date('2012-11-1','yyyy-mm-dd') group byt.roomTypeID,roomTypeNameorder by roomTypeID,roomTypeName) r,roomType t wherer.roomTypeID=t.roomTypeIDselect count(*) as num from livein iwhere i.in_time>=to_date('2012-1-1','yyyy-mm-dd') andi.leave_time<=to_date('2012-6-1','yyyy-mm-dd')--select count(a.O_LOG_ID) from por_log a whereto_char(a.OPER_TIME,'yyyymm')>='201110'一年的每个月收入总额 money_per_yearselect sum(money) as moneytotle,to_char(chk_time,'yyyymm') month from accountwhere to_char(chk_time,'yyyy')='2008'group by to_char(chk_time,'yyyymm')order by to_char(chk_time,'yyyymm')那个个月的收入详细信息 money_per_monthselecta.chk_ID,i.c_name,i.c_sex,i.c_tel,i.in_time,i.leave_time,a.days,a.mo ney,r.roomID,t.roomTypeNamefrom account a,liveIn i,roomInfo r,roomType twhere to_char(a.chk_time,'yyyymm')=? and a.in_no=i.in_noand i.roomID=r.roomID and r.roomTypeID=t.roomTypeID某个时间段内消费前N高的客户 high_pay_customerselect distinct(l.c_sex),l.c_tel,l.zj_no,l.addr,e.* from (select t.* from (select i.c_name,sum(a.money) as xiaofei from account a,liveIniwhere a.in_no=i.in_no andto_char(a.chk_time,'yyyymm')>='201201' andto_char(a.chk_time,'yyyymm')<='201211'group by i.c_nameorder by sum(a.money) desc) twhere rownum<=4) e,livein lwhere e.c_name=l.c_nameorder by e.xiaofei desc某个时间段内住宿次数前N高的客户select distinct(l.c_sex),l.c_tel,l.zj_no,l.addr,e.* from (select * from(select count(*) as cishu,i.c_namefrom account a,liveIn iwhere a.in_no=i.in_no andto_char(a.chk_time,'yyyymm')>='200801' andto_char(a.chk_time,'yyyymm')<='201211'group by i.c_nameorder by count(*) desc)where rownum<=1) e,livein l where e.c_name=l.c_name order by cishu desc日结账统计表当日开房报表。
酒店管理系统JAVA代码
import java。
util。
Scanner;import org。
omg。
CORBA。
PUBLIC_MEMBER;public class jiudian {static String[］[］ rooms ;public static void main(String［］ args） {rooms = new String［8]［8];for （int i = 0； i < rooms。
length; i++) {for (int j = 0； j 〈 rooms［i］.length; j++） {rooms［i][j］= "NULL”;}}String command = ”"；Scanner scanner = new Scanner(System.in)；while （true) {System。
out。
println("ÇëÊäÈëÃüÁ”)；command = scanner。
nextLine(）;if(command。
equals（"search”）)｛searchRoom()；}else if （command.equals（”in"）) ｛System.out.println("ÇëÊäÈëÄúÐèÒªÈëסµÄ·¿¼äºÅ£º”）；String roomIDString =scanner.nextLine（）;int roomID = Integer。
酒店管理系统数据库建表
身份证号码
Oadmittype
char(8)
权限类型
Otel
varchar(20)
电话
OEmail
varchar(50)
Emall地址
Oon
varchar(10)
在职/离职状态
16、权限表（Admit）
字段名
数据类型
主键
是否空
说明
id
int(4)
否
数据库id,自动增长
adid
char(8)
负责人
Aintime
datetime(8)
签定日期
Aouttime
datetime(8)
截止日期
Amemory
varchar(500)
备注
7、客房信息表（RoomInfo）
字段名
数据类型
主键
是否空
说明
id
int(4)
否
数据库id,自动增长
Rid
varchar (20)
PK
否
客房编号
Xrank
varchar(20)
客单号
Scustomername
varchar(50)
客人姓名 可多填
Smemory
varchar(2000)
留言内容
Stime
datetime(8)
留言时间
Oid
char(8)
FK（操作员表）
操作员编号
Stime2
datetime(8)
录入时间
13、帐目表(Account)
字段名
数据类型
主键
是否空
Xcoffer
varchar(50)
酒店管理系统代码
}
}
}
}
}
}
return (x);
}
/////////////////////////////09030317///////李俊杰/////////////////////
int dengji()
{
if(strcmp(zj,fvip[i].id)==0)
{
j=1;break;
}
}
if(j==0)
{
printf("会员信息有误！\n");
printf("%d",j);
return(flag);
}
if(j==1)
if(flat1[i].norms==1)
printf("单间 100元\n");
if(flat1[i].norms==2)
printf("标间 200元\n");
if(flat1[i].norms==3)
printf("VIP 300元\n");
}
}
for(i=0;!feof(fp);i++)
fread(&f[i],sizeof(struct flat),1,fp);
fclose(fp);
len=i;
fp=fopen("hy.txt","rb");
for(i=0;!feof(fp);i++)
fread(&fvip[i],sizeof(struct hy),1,fp);
{
int x,len,vlen,p=1,i,j=0,flag=1;
酒店管理系统代码
#include <windows.h>#include <dos.h>#include<stdio.h>#include<stdlib.h>#include<time.h>//--------------------------------------------------结构定义------------------------------------------typedef struct CheckinInformation{char name[10]; //姓名int id; //证件号int roomType; //房型int countType; //计费方式}CheckinInfo;typedef struct HotelRoom{int roomType; //房型int roomNum; //房号int checked; //入住情况int price; //房价}Room;typedef struct RoomOrder{CheckinInfo *checkinInfo; //入住信息long date; //入住时间Room * room; //房间信息}Order;typedef struct HotelInfomation{int checkinAmount; //已入住房数int singleRemainAmount; //单人房剩余房数int doubleRemainAmount; //双人房剩余房数int bigRemainAmount; //大床房剩余房数}HotelInfo;//--------------------------------枚举类型---------------------------enum {MainUI,HotelInfoUI,CheckinUI,CheckinResultUI,OrderUI,CheckOutUI,Exit};//GUI enum {Single,Double,Big};//Room Typeenum {Hour,Day};//countType//--------------------------------全局变量--------------------------int GUI = MainUI;Order* orderList[100]; //订单数组Room* roomList[100]; //房间数组HotelInfo * hotelInfo = NULL;//酒店房间信息//-------------------------------函数声明----------------------------void initiallizeRoomList();void insertToOrderList(Order * order);Room* getRoomByType(int roomType);Order* getOrderByRoomNum(int roomNum);void showMainUI();void showHotelInfoUI();void showCheckinUI();void showCheckinResultUI();void showOrderUI();void showCheckOutUI();//-------------------------------Main函数----------------------------void main() //主函数{//初始化酒店房间信息hotelInfo = (HotelInfo *)malloc(sizeof(HotelInfo));hotelInfo -> singleRemainAmount = 20;hotelInfo -> doubleRemainAmount=40;hotelInfo -> bigRemainAmount=40;hotelInfo -> checkinAmount=0;//初始化房间列表initiallizeRoomList();//界面显示while(GUI != Exit){switch(GUI){case MainUI:showMainUI();break;case HotelInfoUI:showHotelInfoUI();break;case CheckinUI:showCheckinUI();break;case CheckinResultUI:showCheckinResultUI();break;case OrderUI:showOrderUI();break;case CheckOutUI:showCheckOutUI();break;default:break;}}}//-------------------------------函数定义----------------------------void initiallizeRoomList(){//房间数组初始化，初始化的结果是让roomList的数组有100个room指针，而且设置了相应的值int i;Room*newRoom=NULL;for(i=0;i<20;i++) //单人房房间信息初始化{newRoom = ( Room* )malloc(sizeof(Room));roomList[i] = newRoom;roomList[i]->checked=0;roomList[i]->price=110;roomList[i]->roomNum=i+1;roomList[i]->roomType=Single;}for(i=20;i<60;i++) //双人房房间信息初始化{newRoom = ( Room* )malloc(sizeof(Room));roomList[i] = newRoom;roomList[i]->checked=0;roomList[i]->price=180;roomList[i]->roomNum=i+1;roomList[i]->roomType=Double;}for(i=60;i<100;i++) //大床房房间信息初始化{newRoom = ( Room* )malloc(sizeof(Room));roomList[i] = newRoom;roomList[i]->checked=0;roomList[i]->price=180;roomList[i]->roomNum=i+1;roomList[i]->roomType=Big;}}//通过所选择的房型获取空房间，获取房间后将房间信息改为已入住，并减少相应房型的剩余房间数Room* getRoomByType(int roomType){int i;switch(roomType){case Single:for(i=0;i<20;i++){if( roomList[i]->checked == 0){roomList[i]->checked=1;hotelInfo->singleRemainAmount -- ;hotelInfo->checkinAmount++;return roomList[i];}}break;case Double:for(i=20;i<60;i++){if( roomList[i]->checked == 0){roomList[i]->checked=1;hotelInfo->doubleRemainAmount -- ;hotelInfo->checkinAmount++;return roomList[i];}}break;case Big:for(i=60;i<100;i++){if( roomList[i]->checked == 0){roomList[i]->checked=1;hotelInfo->bigRemainAmount --;hotelInfo->checkinAmount++;return roomList[i];}}break;}//将订单放入订单列表void insertToOrderList(Order * order){int i;for( i = 0;i<100;i++){if( orderList[i] ==NULL ){orderList[i] = order;break;}}}//通过房号查询订单Order* getOrderByRoomNum(int roomNum){int i;for(i=0;i<100;i++){if( orderList[i]->room->roomNum == roomNum){return orderList[i];}}void showMainUI(){//显示主界面，并接受输入int chooseNum;system("cls");printf("\n\n==========================酒店房间登记与计费管理管理系统=======================\t\n\n\n");printf("*\t\t\t\t1. 入住登记\t\t\t\t*\n");printf("*\t\t\t\t2. 查询入住情况\t\t\t*\n");printf("*\t\t\t\t3. 查询当前费用\t\t\t*\n");printf("*\t\t\t\t4. 结账退房\t\t\t\t*\n");printf("*\t\t\t\t5. 退出程序\t\t\t\t*\n\n\n");printf("\n\n==========================酒店房间登记与计费管理管理系统=======================\t\n\n\n");printf("请输入相应编号进入菜单\t");//接受输入scanf("%d",&chooseNum);switch(chooseNum){case 1:GUI = HotelInfoUI;break;case 2:GUI = HotelInfoUI;break;case 3:GUI = OrderUI;break;case 4:GUI = OrderUI;break;case 5:Sleep(3000);GUI = Exit;break;default:break;}}void showHotelInfoUI(){int chooseNum;system("cls");printf("\n\n=========================酒店入住情况查询菜单=======================\t\n\n\n\n");printf("*\t\t\t入住房间数: %d\t\t\t\t*\n", hotelInfo->checkinAmount);printf("*\t\t\t剩余房间数: \t");printf("单人房：%d\t\t*\n",hotelInfo->singleRemainAmount);printf("*\t\t\t\t\t双人房：%d\t\t*\n",hotelInfo->doubleRemainAmount);printf("*\t\t\t\t\t大床房：%d\t\t*\n\n",hotelInfo->bigRemainAmount);printf("\n\n=========================酒店入住情况查询菜单=======================\t\n\n\n");printf("按0 ：返回\n");printf("按1 : 登记入住\n");scanf("%d",&chooseNum);switch(chooseNum){case 0:GUI = MainUI;break;case 1:GUI = CheckinUI;break;default:GUI = HotelInfoUI;break;}}void showCheckinUI(){Order * newOrder;Room* newRoom = NULL;//填写一个新的入住信息CheckinInfo * newCheckinInfo = NULL;int roomTypeNum;int countTypeNum;time_t timep;system("cls");printf("\n\n===========================酒店入住登记菜单=========================\t\n\n\n");newCheckinInfo = ( CheckinInfo * )malloc(sizeof(CheckinInfo));printf("*\t\t请输入姓名:");scanf("%s", &(newCheckinInfo->name) );printf("*\t\t请输入证件号:");scanf("%d", &(newCheckinInfo->id) );printf("*\t\t请选择入住房型:\n");printf("\t\t\t1.单人房\n\t\t\t2.双人房\n\t\t\t3.大床房\n");scanf("%d",&(roomTypeNum));switch(roomTypeNum) //通过输入的数字对应房型{case 1:newCheckinInfo->roomType = Single;break;case 2:newCheckinInfo->roomType = Double;break;case 3:newCheckinInfo->roomType = Big;break;default:newCheckinInfo->roomType = Single;break;}printf("*\t\t请选择计费方式:\n");printf("\t\t\t1.按小时计费; \n\t\t\t2.按天数计费\n"); //通过输入的数字对应计费方式scanf("%d",&countTypeNum);switch(countTypeNum){case 1:newCheckinInfo->countType = Hour;break;case 2:newCheckinInfo->countType = Day;break;}printf("\n\n===========================酒店入住登记菜单=========================\t\n\n\n");//生成一个新的订单newOrder = ( Order* )malloc(sizeof(Order));newOrder -> checkinInfo = newCheckinInfo;newOrder -> date = time(0);switch(newCheckinInfo->roomType) //通过房型获取房间{case Single:newRoom = getRoomByType(Single);break;case Double:newRoom=getRoomByType(Double);break;case Big :newRoom=getRoomByType(Big);break;}newOrder->room = newRoom;insertToOrderList(newOrder);printf("房间号为:%d\n",newOrder->room->roomNum);GUI = CheckinResultUI;}void showCheckinResultUI(){int chooseNum;printf("\n\n=========================酒店入住登记确认菜单=======================\t\n\n\n");printf("\t\t\t************\t\t\t\t\n");printf("\t\t\t* 登记成功*\t\t\t\t\n");printf("\t\t\t************\t\t\t\t\n\n");printf("\n\n=========================酒店入住登记确认菜单=======================\t\n\n\n");printf("按0 ：返回\n");scanf("%d",&chooseNum);switch(chooseNum){case 0:GUI = MainUI;break;default:GUI = CheckinResultUI;break;}}void showOrderUI(){int roomNum;int chooseNum;int amount;Order * theOrder = NULL;system("cls");printf("\n\n=========================酒店房间信息查询菜单=======================\t\n\n\n");printf("请输入房间号:");scanf("%d",&roomNum);if (roomNum<0 || roomNum>100){printf("\n 输入有误请重新输入") ;GUI = OrderUI;}else{theOrder = getOrderByRoomNum(roomNum);printf("房型：");switch(theOrder->room->roomType){case Single:printf("单人房\n");break;case Double:printf("双人房\n");break;case Big:printf("大床房\n");break;}printf("计费方式：");switch(theOrder->checkinInfo->countType ){case Hour:printf("小时计费\n");amount = (time(0) - theOrder->date) / 3600 +1;printf("已入住时间：%d小时\n",amount);break;case Day:printf("天计费\n");amount = (time(0) - theOrder->date) / (3600*24) +1;printf("已入住时间：%d天\n",amount);break;}printf("房价：%d\n",theOrder->room->price);printf("应支付：%d\n\n",amount * theOrder->room->price);printf("\n\n=========================酒店房间信息查询菜单=======================\t\n\n\n");printf("按0：返回\n");printf("按1：结账退房\n");scanf("%d",&chooseNum);switch(chooseNum){case 0:GUI = MainUI;break;case 1:GUI = CheckOutUI;break;default:break;}}}void showCheckOutUI(){int chooseNum;printf("\n\n=========================酒店结账退房确认菜单=======================\t\n\n\n");printf("\t\t\t\t************\t\t\t\t\n");printf("\t\t\t\t* 结账成功*\t\t\t\t\n");printf("\t\t\t\t************\t\t\t\t\n\n");printf("\n\n=========================酒店结账退房确认菜单=======================\t\n\n\n");printf("按0：返回");scanf("%d",&chooseNum);switch(chooseNum){case 0:GUI = MainUI;break;default:GUI = CheckOutUI;break;}}。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
酒店管理系统数据库代码use Hotel_Management1select*from Customerselect*from Employeeselect*from RoomTypeselect*from Roomselect*from OrderInfoselect*from Checkoutdrop database Hotel_Management1------------------------创建数据库Hotel_Management---------------------------------------------------------------------- create database Hotel_MDBonprimary(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 OrderInfocreate 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-outcreate 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,Notes ntext,FOREIGN KEY(EmployeeID)REFERENCES Employee(EmployeeID),FOREIGN KEY(CustomerID)REFERENCES Customer(CustomerID),)--drop table Checkoutsp_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 RoomTypeinsert 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 Roominsert 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 Customerselect*from Roominsert into Customer values('刘德华',,'m',,null)insert into Customer values('张更硕',,'m',,null)insert into Customer values('周辉',,'m',,null)insert into Customer values('刘美美',,'f',,null)insert into Customer values('范冰冰',,'f',,null)insert into Customer values('佟大为',,'m',,null)insert into Customer values('范玮琪',,'f',,null)insert into Customer values('陈小春',,'m',,null)insert into Customer values('kenim',,'m',,null)--OrderInfo 表select*from OrderInfoinsert into OrderInfo values(9001,'1011',1,1,'2013-09-03 9:00PM',250.00,'use',null)insert into OrderInfo values(9002,'1021',2,2,'2013-09-05 7:00PM',300.00,'use',null)insert into OrderInfo values(9003,'1031',3,2,'2013-09-04 8:00PM',600.00,'use',null)insert into OrderInfo values(9004,'1041',4,2,'2013-09-12 2:00PM',1000.00,'use',null)insert into OrderInfo values(9005,'1021',9,2,'2013-09-04 7:00PM',300.00,'use',null)insert into OrderInfo values(9006,'1031',10,2,'2013-09-04 8:00PM',600.00,'use',null)--insert into OrderInfo values(9007,'1041',11,2,'2013-09-4 2:00PM',1000.00,'use',null)exec proc_find_stu 1041---库存-1--insert into OrderInfo values(9005,'1012',1,1,'2013-09-03 9:00PM',250.00,'use' ,null)--delete OrderInfo where OrderID in(9005)--drop table OrderInfo---Checkout表insert into Checkout values(13001,'1011',1,2,'2013-09-03 9:00PM','2013-09-04',200,NULL)insert into Checkout values(13002,'1021',2,2,'2013-09-03 3:00PM','2013-09-04',200,NULL)insert into Checkout values(13003,'1031',3,2,'2013-09-03 10:00PM','2013-09-04',200,NULL)--insert into Checkout values(13004,'1041',4,2,'2013-09-03 8:00PM','2013-09-04',200,NULL )insert into Checkout values(13003,'1021',9,2,'2013-09-03 10:00PM','2013-09-04',880,NULL)delete Checkout where CheckoutID in(13001,13002)--drop table Checkoutselect*from Checkoutselect*from OrderInfoselect*from RoomTypeselect*from Roomexec proc_find_stu 1041---库存-1exec proc_find_stu2 1011---库存+1--insert into Checkout values('O2001','R003',1002,2,'2013-09-06',570,NULL )--insert into Checkout values('O2002','R001',1003,2,'2013-09-04',570,NULL )----------------------------------------创建触发器---------------------------------------------------------------------- --1\创建客房使用状态触发器(插入)create trigger RoomState_1on OrderInfofor insertasupdate Roomset RoomState='Y'where RoomID=any(select RoomID from INSERTED )--drop trigger RoomState_1--2-创建客房使用状态触发器（删除）create trigger RoomState_2on Checkoutfor insertasupdate Roomset RoomState='N'where RoomID in(select RoomID from INSERTED )--drop trigger RoomState_2---3--创建修改订单状态触发器create trigger ORstatic_1on Checkoutfor insertasupdate OrderInfoset ORstatic='NO'where RoomID=any(select RoomID from INSERTED )--drop trigger ORstatic_1---------------------------------------存储过程------------------------------------------------------------------------------------------------------------------------------------1--创建修改客房库存触发器（减少）create proc proc_find_stu(@startId int)asupdate RoomType set Surplus=(Surplus-1)where RoomTypeID in(select RoomTypeID from Room where RoomID=@startId) goexec proc_find_stu 1011--2--创建修改客房库存触发器（增长）create proc proc_find_stu2(@startId int)asupdate RoomType set Surplus=(Surplus+1)where RoomTypeID in(select RoomTypeID from Room where RoomID=@startId) goexec proc_find_stu2 1011---系统功能流程use Hotel_Management1select*from Customerselect*from Employeeselect*from RoomTypeselect*from Roomselect*from OrderInfoselect*from Checkout--（）顾客入住员工查询闲置房间select*from Room where RoomState='N'select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType where Room.RoomTypeID=RoomType.RoomTypeID and RoomState='N'---A。