ACCP7.0S2优化myschool数据库设计第二章课后简答

use master 
go
if exists(select * from sysdatabases where name='labrary')
drop database labrary
create database labrary
on primary
(name='labrary_data',filename='d:\labrary.mdf',size=5,filegrowth=15%)
log on(name='labrary_log',filename='d:\labrary.ldf',size=1,filegrowth=15%)
use labrary
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