酒店管理系统数据库代码

酒店管理系统数据库代码

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,

Notes ntext,

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

insert into Customer values('刘德华',,'m',,null)

insert into Customer values('张更硕',,'m',,null)