CREATE DATABASE RentManage
GO
2．创建基本表
(1) Renter表
CREATE TABLE Renter (
RenterID char (18) PRIMARY KEY RenterName varchar (20) NULL,
Contact varchar (50) NULL,
ContractID char (8) NOT NULL,
RenterRental money NOT NULL,
Remark varchar (50) NULL,
)
NOT NULL,
GO
(2) RoomInfo表
CREATE TABLE RoomInfo (
RoomID char(18) PRIMARY KEYRenterID 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,
NULL,
NOT
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)
4．触发器
create trigger insert_trigger
on Customer
after insert
as
declare @RoomID varchar,@TrueNum int
select @RoomID=RoomID
from inserted
update RoomInfo
set TrueNum=TrueNum+1
where RoomID=@RoomID
create trigger delete_trigger
on Customer
after delete
as
declare @RoomID varchar,@TrueNum int
select @RoomID=RoomID
from deleted
update RoomInfo
set TrueNum=TrueNum-1
where RoomID=@RoomID
因篇幅问题不能全部显示，请点此查看更多更全内容