数据库编译第二章课后练习

use master   
go  
if exists(select * from sysdatabases where name='library')  
drop database library 
create database library  
on primary  
(name='library_data',filename='D:\\project\\library_data.mdf',size=5,filegrowth=15%)  
log on(name='library_log',filename='D:\\project\\library_log.ldf',size=1,filegrowth=15%)  
use library 
go  
create table book(  
bid varchar(20) not null,  
bname varchar(50) not null,  
author varchar(20),  
pubcomp varchar(50),  
pubdate datetime,  
bcount int,  
price money  
)  
create table reader(  
rid varchar(20) not null,  
rname varchar(50) not null,  
lendnum int,  
raddress varchar(255)  
)  
create table borrow(  
rid varchar(20) not null,  
bid varchar(20) not null,  
lenddate datetime not null,  
willdate datetime,  
returndate datetime  
)  
create table penalty(  
rid varchar(20) not null,  
bid varchar(20) not null,  
pdate datetime not null,  
ptype int,  
amount money  
)  
alter table book  
add constraint pk_bid1 primary key(bid)  
alter table book  
add constraint ck_bid check(bid like 'ISBN%')  
alter table book  
add constraint ck_pubdate check(pubdate<=getdate())  
alter table book  
add constraint ck_bcount check(bcount>=1)  
alter table book  
add constraint ck_price check(price>0)  
alter table reader  
add constraint pk_rid1 primary key(rid)  
alter table reader  
add constraint ck_lendnum check(lendnum>=0)  
alter table borrow  
add constraint pk_rid2 primary key(rid,bid,lenddate)  
alter table borrow  
add constraint fk_rid1 foreign key(rid) references reader(rid)  
alter table borrow  
add constraint fk_bid1 foreign key(bid) references book(bid)  
alter table borrow  
add constraint df_lenddate default(getdate()) for lenddate  
alter table borrow  
add constraint ck_willdate check(willdate>=getdate())  
alter table borrow  
add constraint df_willdate default(dateadd(month,1,getdate())) for willdate  
alter table borrow  
add constraint df_returndate default(null) for returndate  
alter table penalty  
add constraint pk_rid3 primary key(rid,bid,pdate)  
alter table penalty  
add constraint fk_rid2 foreign key(rid) references reader(rid)  
alter table penalty  
add constraint fk_bid2 foreign key(bid) references book(bid)  
alter table penalty  
add constraint df_pdate default(getdate()) for pdate  
alter table penalty  
add constraint ck_ptype check(ptype=1 or ptype=2 or ptype=3)  
alter table penalty  
add constraint ck_amount check(amount>0)  
alter table book add btotal int