酒店管理系统数据库设计

酒店管理系统第1页，共15页酒店管理系统一．酒店管理系统的需求分析用户的需求具体体现在各种信息的提供、保存、更新和查询等方面，这就要求数据库结构能充分满足各种信息的输出和输入。收集基本数据、数据结构以及数据处理的流程，得出本系统所处理的数据流程。针对一般酒店客房信息管理系统的需求，内容和流程分析，设计下面的数据项和数据结构：员工标准信息，包括的数据项有：员工编号，姓名，性别，出生日期，工龄，密码，电话，备注等。客户标准信息，包括的数据项有：客户编号，性名，性别，出生日期，身份证号，手机，备注等。客房标准信息，包括的数据项有：客房编号，类型编号，状态，备注等。类型标准信息，包括的数据项有：类型编号，类型名称，费用，总量，剩余量，备注等。订单标准信息，包括的数据项有：订单编号，员工编号，客户编号，客房编号，时间，押金，备注等。退房标准信息，包括的数据项有：员工编号，客户编号，客房编号，时间，备注等。二.概念结构设计：经过对酒店一般形式的调查，了解到了该系统包括的实体类型有：员工、客户、客房等。经过分析画出了“酒店管理系统”数据库的E-R图，分别如图1~图6所示：图-1员工实体E-R图员工员工编号姓名性别出生日期工龄密码电话备注酒店管理系统第2页，共15页图-2客户实体E-R图图-3客房实体E-R图客户客户编号姓名性别出生日期身份证号手机备注客房客房编号类型编号状态备注酒店管理系统第3页，共15页图-4类型实体E-R图图-5订单实体E-R图图-6退房实体E-R图通过对个实体之间联系的分析，将局部E-T图综合成全局E-R图，类型类型编号类型名称费用总量剩余量备注订单订单编号员工编号客户编号客房编号时间押金备注退房员工编号客房编号客户编号时间备注酒店管理系统第4页，共15页如图-7所示：图-7全局E-R图三.逻辑结构设计：根据概念设计的结果,将其转换为关系模型：员工（员工编号，姓名，性别，出生日期，工龄，密码，电话，备注）客户（客户编号，性名，性别，出生日期，身份证号，手机，备注）客房（客房编号，类型编号，状态，备注）类型（类型编号，类型名称，费用，总量，剩余量，备注）订单（订单编号，员工编号，客户编号，客房编号，时间，押金，备注）退房（员工编号，客户编号，客房编号，时间，备注）四.物理结构设计：1．员工表列名数据类型是否允许为空默认值是否为主键员工编号Char(10)否是姓名Char(10)否性别Char(2)否男出生日期Smalldatetime工龄Int密码Char(6)否电话Char(20)客户员工客房退房住宿n离开m登记管理分类类型预定登记订单nmmnnmmmnnmn酒店管理系统第5页，共15页备注Text2.客户表列名数据类型是否允许为空默认值是否为主键客户编号Char(10)否是姓名Char(10)否性别Char(2)否男出生日期Smalldatetime身份证号Char(18)否手机Char(20)备注Text3.客房表列名数据类型是否允许为空默认值是否为主键客房编号Char(6)否是类型编号Char(2)否状态Char(4)备注Text4.类型表列名数据类型是否允许为空默认值是否为主键类型编号Char(2)否是类型名称Char(16)否费用Int总量Int剩余量Int备注Text5.订单表列名数据类型是否允许为空默认值是否为主键订单编号Char(10)否是员工编号Char(10)客户编号Char(10)客房编号Char(6)酒店管理系统第6页，共15页时间SmalldatetimeGETDATE()押金Int备注Text6.退房表列名数据类型是否允许为空默认值是否为主键员工编号Char(10)客户编号Char(10)客房编号Char(5)时间SmalldatetimeGETDATE()备注Text题目：（一）1．用T-SQL语句创建数据库CREATEDATABASE酒店管理系统ONPRIMARY(NAME='酒店管理系统_data1',FILENAME='E:\SQL\酒店管理系统\酒店管理系统_data1.mdf',SIZE=5,MAXSIZE=UNLIMITED,FILEGROWTH=2),FILEGROUPFGROUP(NAME='酒店管理系统_data2',FILENAME='E:\SQL\酒店管理系统\酒店管理系统_data2.ndf',SIZE=5,MAXSIZE=UNLIMITED,FILEGROWTH=2)LOGON(NAME='酒店管理系统_log1',FILENAME='E:\SQL\酒店管理系统\酒店管理系统_log1.ldf',SIZE=10,MAXSIZE=UNLIMITED,FILEGROWTH=4)GO2．用T-SQL语句创建所有的表及设置主键酒店管理系统第7页，共15页CREATETABLE员工表(员工编号CHAR(10)NOTNULLPRIMARYKEY,姓名CHAR(10)NOTNULL,性别CHAR(2)NOTNULL,出生日期SMALLDATETIME,工龄INT,密码CHAR(6)NOTNULL,电话CHAR(20),备注TEXT)GOCREATETABLE客户表(客户编号CHAR(10)NOTNULLPRIMARYKEY,姓名CHAR(10)NOTNULL,性别CHAR(2)NOTNULL,出生日期SMALLDATETIME,身份证号CHAR(18)NOTNULL,手机CHAR(20),备注TEXT)GOCREATETABLE客房表(客房编号CHAR(6)NOTNULLPRIMARYKEY,类型编号CHAR(2)NOTNULL,状态CHAR(4),备注TEXT)GOCREATETABLE类型表(类型编号CHAR(2)NOTNULLPRIMARYKEY,类型名称CHAR(16)NOTNULL,费用INT,总量INT,剩余量INT,备注TEXT)GOCREATETABLE订单表(订单编号CHAR(10)NOTNULLPRIMARYKEY,员工编号CHAR(10),客户编号CHAR(10),客房编号CHAR(6),时间SMALLDATETIME,押金INT,酒店管理系统第8页，共15页备注TEXT)GOCREATETABLE退房表(员工编号CHAR(10),客户编号CHAR(10),客房编号CHAR(6),时间SMALLDATETIME,备注TEXT)GO3．用T-SQL语句给需要设外键的表设置外键ALTERTABLE客房表ADDCONSTRAINTFK_类型表_客房表FOREIGNKEY(类型编号)REFERENCES类型表(类型编号)GOALTERTABLE订单表ADDCONSTRAINTFK_员工表_订单表FOREIGNKEY(员工编号)REFERENCES员工表(员工编号)GOALTERTABLE订单表ADDCONSTRAINTFK_客户表_订单表FOREIGNKEY(客户编号)REFERENCES客户表(客户编号)GOALTERTABLE订单表ADDCONSTRAINTFK_客房表_订单表FOREIGNKEY(客房编号)REFERENCES客房表(客房编号)GOALTERTABLE退房表ADDCONSTRAINTFK_员工表_退房表FOREIGNKEY(员工编号)REFERENCES员工表(员工编号)GOALTERTABLE退房表ADDCONSTRAINTFK_客户表_退房表FOREIGNKEY(客户编号)REFERENCES客户表(客户编号)GOALTERTABLE退房表ADDCONSTRAINTFK_客房表_退房表FOREIGNKEY(客房编号)REFERENCES客房表(客酒店管理系统第9页，共15页房编号)GO4．用T-SQL语句给表加上check约束、UNIQUE约束、DEFAULT约束ALTERTABLE员工表ADDCONSTRAINTCK_员工性别CHECK(性别IN('男','女'))GOALTERTABLE员工表ADDCONSTRAINTDF_员工性别DEFAULT('男')FOR性别GOALTERTABLE客户表ADDCONSTRAINTCK_客户性别CHECK(性别IN('男','女'))GOALTERTABLE客户表ADDCONSTRAINTDF_客户性别DEFAULT('男')FOR性别GOALTERTABLE客户表ADDCONSTRAINTUQ_身份证号UNIQUE(身份证号)GOALTERTABLE订单表ADDCONSTRAINTDF_订房时间DEFAULT(GETDATE())FOR时间GOALTERTABLE退房表ADDCONSTRAINTDF_退房时间DEFAULT(GETDATE())FOR时间GO5．使用insert语句初始化数据库INSERTINTO员工表(员工编号,姓名,性别,出生日期,工龄,密码,电话)VALUES('0109090101','张三','男','1982-2-1',2,'123401','0758-2333401')INSERTINTO客户表(客户编号,姓名,性别,出生日期,身份证号,手机)VALUES('1009010001','王恒','女','1987-8-16',461324198708164456,'13623455776')INSERTINTO客房表(客房编号,类型编号,状态)VALUES('120501','12','空')INSERTINTO类型表(类型编号,类型名称,费用,总量,剩余量)VALUES('12','豪华双人房','480','20','20')酒店管理系统第10页，共15页INSERTINTO订单表(订单编号,员工编号,客户编号,客房编号,押金)VALUES('1005010001','0109090101','1009010001','120506','50')INSERTINTO退房表(员工编号,客户编号,客房编号)VALUES('0109090102','1009010001','210301')（二）1、流程控制语句与函数（1）、用上while语句根据入住天数，查询客户应付费用declare@yintdeclare@xintset@x=0set@y=0while@xdatediff(day,'2010-12-10',getdate())beginset@x=@x+1set@y=@x*300endselectfare=@y（2）、用上if…else语句查出普通房中，单人房多少钱。declare@xcharset@x='单人房'if@x='单人房'select'普通单人间100元'elseselect'普通双人间280元'go（3）、用上case函数查询客户的年龄信息select姓名,年龄=casewhenyear(getdate())-year(出生日期)=50then'老年'whenyear(getdate())-year(出生日期)between40and50then'中年'else'青年'endfrom客户表go（4）、用上substring函数截取“酒店管理系统”中“管理系统”这四个字。酒店管理系统第11页，共15页selectsubstring('酒店管理系统',3,4)（5）、用上datediff函数查询员工年龄等级select姓名,年龄=casewhendatediff(year,出生日期,getdate())=50then'老年'whendatediff(year,出生日期,getdate())between40and50then'中年'else'青年'endfrom员工表go（6）、自定义一个标量函数并调用它定义一个标量函数查询类型名称为“豪华单人房”的客房数createfunctionkfs()returnsintasbegindeclare@yintselect@y=count(*)from客房表a,类型表bwherea.类型编号=b.类型编号groupby类型名称having类型名称='豪华单人房'return@yend调用