怎样为租车网专门设计数据库系统

1. 租车公司有多个租车门店，分布于多个不同的地区，并有各自的租车电话。

2. 每个租车门店有多辆汽车可供租赁。

3. 供租赁的车辆需要登记车辆识别代号（VIN），购入时间，所属门店，车辆型号，车辆状态（可租Ready，维修中Repair，租出Inuse，无效Inactive）

4. 车辆的租用费用基本由车辆型号和日期类型（平日，周末，还是节假日）来决定。

5. 顾客在订车前需先进行注册，包括姓名，身份z号，驾照号，性别，手机号，固定电话，家庭住址，Email。

6. 注册顾客可通过系统下租车单，预约某车型，若干天的租赁（预约期最远为6个月）。

7. 租车单需记录顾客编号，车辆编号，租赁起始日期，租赁结束日期，提车门店，还车门店，租赁费用，预付款金额，订单状态（输入Entered，提交Booked，预约Reserved，使用中Inuse，交还Returned，取消Cancelled）。注：暂不提供送车上门和上门取车服务。

对于上述需求，比较明显的需创建的表有：车辆（Table_Car），门店（Table_Store），顾客（Table_Customer），订单（Table_Order）。

除此之外，车辆型号，车辆状态，日期类型和订单状态分别创建成四张枚举表Table_CarCategory，Table_CarStatus，Table_DateType，Table_OrderStatus。

还应有一张租车价位对照表（Table_BasePrice），其中会包含两个外键分别指向Table_CarCategory，Table_DateType。

简单表关系图如下：

大部分字段的含义大家可以从命名中猜测到。其中需要注意的有两点：

1. 这一设计中有4张枚举表（Table_DateType,Table_CarCategory,Table_OrderStatus,Table_CarStatus），在实际的信息系统或业务系统中这样的枚举表可能非常多。把这些枚举表整合到一张配置表中会带来哪些好处与哪些坏处？是否还有其他解决方案？大家可以进行思考。

2. 租车价位对照表在图中被设计成Table_BasePrice。其主键为一联合键，包括CarCategory_ID（表明车型，如：乐风 1.6 MT），DateType_ID（表明是平日，周末或节日），BasePrice_StartDate（表明从哪个时间点开始顾客在系统页面看到新的价格），其中CarCategory_ID，DateType_ID同时为外键。这是一种设计方式。

另有2种可选的设计方式：

待选方案1. 把Table_BasePrice中的DateType_ID去除，Table_BasePrice只存某种车型的初始租价。在Table_DateType中多加一列DateType_AdjustRate，存放一个大于等于1的比率，如：平日比率为1.0，双休日为1.1。某一日的基本租价为：比率×初始租价。

待选方案2. 在待选方案1的基础上，直接去除Table_BasePrice表。把BasePrice_Price放到Table_CarCategory中（可改名为CarCategory_Price）。其他修改和方案1相同。

这些方案会影响到系统使用的灵活性，易用性和可追溯性。大家可以对这些方案的优点和缺点进行思考和讨论。

用access建立一个数据库例子如下：

单击“开始”→”所有程序“→”Microsoft

Office“→”Microsoft

Access

2010“，打开Microsoft

Access

2010软件

在打开的Microsoft

Access

2010软件中选中“空数据库”双击即可创建。或者打击“创建”命令按钮创建，其中文件名处可以选择指定具体的文件名和路径。

创建空白数据库后可以看到一个新建了一个名为“表1”的数据表，界面右侧显示了它的字段。若要添加字段，可以单击“单击以添加”旁的倒三角箭头，选择要添加的字段类型。

此时光标会定位在字段名称上，可以对字段名称进行重命名，重命名字段名称后按下回车键，将继续下一个字段的添加 *** 作。

通过以上的步骤就可以创建一个包含单个数据表的简易的空白数据库了。

--------------创建数据库--------------------------

create database bankDB

on primary

(

name = 'bankDB',

filename = 'e:\bank\bankDB.mdf',

size = 3MB,

maxsize = 100MB,

filegrowth = 15%

)

log on

(

name = 'bankDB_log',

filename = 'e:\bank\bankDB_log.ldf',

size = 2MB,

filegrowth = 1MB

)

go

--------------------创建数据表---------------------

use bankDB

gocreate table userInfo

(

customerID int identity(1,1) not null,

customerName varchar(10) not null,

PID char(18) not null,

telphone char(13) not null,

adress varchar(100)

)alter table userInfo

add constraint PK_customerID primary key(customerID)

alter table userInfo

add constraint CK_PID check(PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or

PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]X' or

PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

alter table userInfo

add constraint CK_telphone check(telphone like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telphone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

alter table userInfo

add constraint DF_telphone default('地址不详') for adress

alter table userInfo

add constraint UQ_PID unique(PID)create table cardInfo

(

cardID char(19) not null,

curType varchar(5) not null,

savingType varchar(6) not null,

openDate datetime not null,

openMoney money not null,

balance money not null,

pass char(6) not null,

IsReportLoss char(2) not null,

customerID int not null

)alter table cardInfo

add constraint FK_customerID foreign key(customerID) references userInfo(customerID)

alter table cardInfo

add constraint PK_cardID primary key(cardID)

alter table cardInfo

add constraint DF_openDate default(getdate()) for openDate

alter table cardInfo

add constraint DF_pass default('888888') for pass

alter table cardInfo

add constraint DF_curType default('RMB') for curType

alter table cardInfo

add constraint CK_openMoney check(openMoney >1)

alter table cardInfo

add constraint CK_balance check(balance >1)

alter table cardInfo

add constraint CK_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]')

alter table cardInfo

add constraint DF_cardID default('1010 3576 ' + convert(varchar(4),convert(int,rand()*10000)) + ' ' +convert(varchar(4),convert(int,rand()*10000))) for cardID

alter table cardInfo

add constraint DF_IsReportLoss default('否') for IsReportLoss

alter table cardInfo

add constraint CK_IsReportLoss check(IsReportLoss like '是' or IsReportLoss like '否')

alter table cardInfo

add constraint CK_savingType check(savingType like '定期' or savingType like '活期' or savingType like '定活期')create table transInfo

(

transDate datetime not null,

cardID char(19) not null,

transType char(4) not null,

transMoney money not null,

remark varchar(100)

)alter table transInfo

add constraint DF_transDate default(getdate()) for transDate

alter table transInfo

add constraint FK_cardID foreign key(cardID) references cardInfo(cardID)

alter table transInfo

add constraint CK_stransType check(transType like '存入' or transType like '支取')

alter table transInfo

add constraint CK_transMoney check(transMoney >0)go

--------------------创建开户用户卡信息视图---------

create view v_cus

as

select customerName, cardID,curType,savingType,telphone,adress

from cardInfo inner join userInfo on cardInfo.customerID = userInfo.customerID

where datediff(wk,openDate,getdate()) = 0-----------------------修改密码--------------------

create proc changePWD

@cardID char(19),

@newpwd char(6),

@oldpwd char(6) = '888888'

as

declare @temppwd int

if((select count(*) from cardInfo where cardID = @cardID) = 0)

begin

print '您输入的卡号不存在！'

return

end

select @temppwd = pass from cardInfo where cardID = @cardID

if(@temppwd = @oldpwd)

begin

update cardInfo set pass = @newpwd where cardID = @cardID

if(@@error = 0)

print '密码修改成功！'

else

print '密码修改失败！'

end

else

print '您输入的原始密码不对，不能修改密码！'----------------------yhk挂失-------------------

create proc ReportLoss

@cardID char(19),

@PID char(18)

as

if((select count(*) from cardInfo where cardID = @cardID) = 0)

begin

print '您输入的卡号不存在！'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')

begin

print '该卡已挂失！'

return

end

if((select PID from userInfo where customerID = (select customerID from cardInfo where cardID = @cardID)) = @PID)

begin

update cardInfo set IsReportLoss = '是' where cardID = @cardID

if(@@error = 0)

print '挂失成功！'

else

print '挂失失败！'

end

else

begin

print '您输入的身份z错误！不能挂失！'

return

end----------------------取消yhk挂失-------------------

create proc CancelReportLoss

@cardID char(19),

@PID char(18)

as

if((select count(*) from cardInfo where cardID = @cardID) = 0)

begin

print '您输入的卡号不存在！'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID) = '否')

begin

print '该卡没有挂失！不能进行此项 *** 作！'

return

end

if((select PID from userInfo where customerID = (select customerID from cardInfo where cardID = @cardID)) = @PID)

begin

update cardInfo set IsReportLoss = '否' where cardID = @cardID

if(@@error = 0)

print '取消挂失成功！'

else

print '取消挂失失败！'

end

else

begin

print '您输入的身份z错误！不能取消挂失！'

return

end----------------------统计银行的资金流通余额和盈利结算--------------------create proc StatMoney

as

select ((select sum(transMoney) as 资金流通余额 from transInfo where transType like '存入') - (select sum(transMoney) as 资金流通余额 from transInfo where transType like '支取'))

as 资金流通余额 select ((select sum(transMoney) as 资金流通余额 from transInfo where transType like '存入')*0.008

- (select sum(transMoney) as 资金流通余额 from transInfo where transType like '支取')*0.003)

as 盈利结算 ---------------------查询本周开户的卡号，显示该卡的信息-------------------

create proc SearchNewCus

as

print '本周开户卡号信息'

select * from v_cus -----------------------查询本月交易金额最高的卡号-------------------------

create proc SearchHigh

as

select top 1 cardID, sum(transMoney) as 交易金额 from transInfo

group by cardID------------------------------查询挂失帐号的客户信息----------------------

create proc SearchLossCus

as

print '挂失帐号客户信息'

select * from userInfo where customerID = (select customerID from cardInfo where IsReportLoss = '是')--------------------------催款提醒业务------------------------------------

create proc Awoke

as

select telphone, balance, customerName, cardID

from cardInfo inner join userInfo on cardInfo.customerID = userInfo.customerID

where balance <200-------------------------银行开户-----------------------------------------

create proc OpenUser

@curType varchar(5),

@savingType varchar(6),

@openMoney money,

@PID char(18),

@telphone char(13),

@customerName varchar(10)

as

begin tran OpenU

declare @tag int

declare @tempID varchar(10)

set @tag = 0 insert into userInfo (customerName,PID,telphone) values (@customerName, @PID, @telphone)

set @tag = @tag + @@error

if(@tag = 0)

set @tempID = (select customerID from userInfo where PID = @PID)

insert into cardInfo (curType, savingType, openMoney, balance, customerID) values (@curType, @savingType, @openMoney, @openMoney, @tempID)

set @tag = @tag + @@error

if(@tag = 0)

begin

print '开户成功！'

commit tran

end

else

begin

print '开户失败！'

rollback tran

end-----------------------------客户存款-------------------------------------

create proc SaveMoney

@cardID char(19),

@transMoney money,

@transType char(4) = '存入'

as

if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')

begin

print '该卡已挂失!无法对其进行 *** 作!'

return

end

begin tran Saving

declare @tag int

set @tag = 0 insert into transInfo (cardID, transType, transMoney) values (@cardID, @transType, @transMoney)

set @tag = @tag + @@error

update cardInfo set balance = balance + @transMoney where cardID = @cardID

set @tag = @tag + @@error

if(@tag = 0)

begin

print '存款成功！'

commit tran

end

else

begin

print '存款失败!'

rollback tran

end ------------------------------客户取款------------------------------------

create proc DrawMoney

@cardID char(19),

@transMoney money,

@transType char(4) = '支取'

as

if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')

begin

print '该卡已挂失!无法对其进行 *** 作!'

return

end

begin tran Drawing

declare @tag int

set @tag = 0 insert into transInfo (cardID, transType, transMoney) values (@cardID, @transType, @transMoney)

set @tag = @tag + @@error

update cardInfo set balance = balance - @transMoney where cardID = @cardID

set @tag = @tag + @@error

if(@tag = 0)

begin

print '取款成功！'

commit tran

end

else

begin

print '取款失败!'

rollback tran

end----------------------------转帐------------------------------------------

create proc Transfer

@cardID_out char(19),

@cardID_in char(19),

@tranMoney money

as

if((select count(*) from cardInfo where cardID = @cardID_out or cardID = @cardID_in) <>2)

begin

print '请确认两张卡是否都存在！'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID_out) = '是')

begin

print @cardID_out + '该卡已挂失!无法对其进行 *** 作!'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID_in) = '是')

begin

print @cardID_in + '该卡已挂失!无法对其进行 *** 作!'

return

end

begin tran TransMoney

declare @tag int

set @tag = 0 update cardInfo set balance = balance - @tranMoney where cardID = @cardID_out

set @tag = @tag + @@error

update cardInfo set balance = balance + @tranMoney where cardID = @cardID_in

set @tag = @tag + @@error

if(@tag = 0)

begin

print '转帐成功！'

commit tran

end

else

begin

print '转帐失败!'

rollback tran

end

---