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

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