房屋出租系统数据库代码
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1.创建数据库
CREATE DATABASE RentManage
GO
2．创建基本表
(1) Renter表
CREATE TABLE Renter (
RenterID char (18) PRIMARY KEY NOT NULL,
RenterName varchar (20)NULL,
Contact varchar (50)NULL,
ContractID char (8)NOT NULL,
RenterRental money NOT NULL,
Remark varchar (50)NULL,
)
GO
(2) RoomInfo表
CREATE TABLE RoomInfo (
RoomID char(18)PRIMARY KEY NOT NULL,
RenterID char(18)NOT NULL,
RoomType varchar(20) NULL,
Location varchar(50)NULL,
Floor varchar(2)NULL,
RatingNum smallint NULL,
TrueNum smallint NULL,
Area smallint NULL,
Price money NOT NULL,
AirCondition bit NULL,
Telephone bit NULL,
TV bit NULL,
WashRoom bit NULL,
Kitchen bit NULL,
Internet bit NULL,
Remark varchar(50)NULL,
FOREIGN KEY (RenterID) REFERENCES Renter (RenterID) )
GO
(3) Customer 表
CREATE TABLE Customer(
CustomerID char(18) PRIMARY KEY NOT NULL,
CustomerName varchar(20)NULL,
Sex char(2) NULL,
NativePlace varchar(50)NULL,
IDCard char(18)NULL,
RoomID char(18)NOT NULL,
InDate datetime NOT NULL,
RentalDate datetime NULL,
ContractID char(8)NOT NULL,
CustomerRental money NOT NULL,
Remark varchar(50)NULL,
FOREIGN KEY(RoomID) REFERENCES RoomInfo (RoomID)
)
GO
2．视图
（1）v_empty视图
create view v_empty
as
select RoomType as'类型',Location as'位置',Floor as'楼层',RatingNum as '额定人数',TrueNum as'实住人数',Area as'面积',Price as'价格',
case AirCondition when 1 then'有'when 0 then'无'end as'空调',
case Telephone when 1 then'有'when 0 then'无'end as'电话',
case TV when 1 then'有'when 0 then'无'end as'电视',
case Washroom when 1 then'有'when 0 then'无'end as'卫生间',
case Kitchen when 1 then'有'when 0 then'无'end as'厨房',
case Internet when 1 then'有'when 0 then'无'end as'宽带',
Remark as'备注',RoomID as'房屋编号',RenterID as'出租人编号'from RoomInfo where TrueNum=0;
（2）v_notfull视图
create view v_notfull
as
select RoomType as'类型',Location as'位置',Floor as'楼层',RatingNum as '额定人数',TrueNum as'实住人数',Area as'面积',Price as'价格',
case AirCondition when 1 then'有'when 0 then'无'end as'空调',
case Telephone when 1 then'有'when 0 then'无'end as'电话',
case TV when 1 then'有'when 0 then'无'end as'电视',
case Washroom when 1 then'有'when 0 then'无'end as'卫生间',
case Kitchen when 1 then'有'when 0 then'无'end as'厨房',
case Internet when 1 then'有'when 0 then'无'end as'宽带',
Remark as'备注',RoomID as'房屋编号',RenterID as'出租人编号'from RoomInfo where RatingNum>TrueNum;
3．存储过程
CREATE procedure AddRenter
@RenterName varchar(20),
@Contact varchar(50),
@ContractID char(8),
@RenterRental money,
@Remark varchar(50),
@RenterID char(18)
as insert into Renter values
(@RenterID,@RenterName,@Contact,@ContractID,@RenterRental,@Remark)