第二章:数据库的实现 (课后简答题二)

 --设置当前数据库为master,以便访问sysdatabase表use master if exists (select * from sysdatabases where name='Library')drop database Library  --删除数据库create database Libraryon primary(--数据文件name='Library_data',               --逻辑名称filename='D:\\Library_data.mdf',   --物理名称size=5mb,                          --初始大小  maxsize=100mb,                     --最大值  filegrowth=15%                     --增长率)log on(--日志文件name='Library_log',filename='D:\\Library_data.ldf',size=2mb,filegrowth=1mb)--创建表use  Librarycreate table Book       --图书信息表(Bid int not null,Bname varchar(20) not null,Author varchar(20) not null,Pubcomp varchar(50) not null,Pubdate datetime not null,Bcount int not null,Price money not null )create table Reader     --读者信息表  (Rid int not null,Rname varchar(20) not null,Lendnum int not null,Raddress varchar(50) not null)create table Borre      --图书借阅表 (Rid int not null,Bid int not null,Lenddate datetime not null,Willdate datetime not null,Retrundate datetime not null)create  table Penalty   --罚款记录表(Rid int not null,Bid int not null,Pdate datetime not null,ptype int not null,Amount money not null)--图书主键,编号alter table Bookadd constraint pk_Bid primary key (Bid)--必须以ISBN开头alter table Bookadd constraint ck_Bid check (Bid like 'ISBN%')--出版日期必须小于当前日期alter table Book add constraint Ck_Pubdate check (Pubdate<getdate())--现存数量,必须大于等于0alter table Bookadd constraint ck_Bcount check (Bcount>=0)--单价必须大于零alter table Bookadd constraint ck_Price check (Price>0)--主键(读者编号)alter table Readeradd constraint pk_Rid primary key (Rid)--已借数量必须大于等于0alter table Readeradd constraint ck_Lendnum check (Lendnum>=0)----读者编号,复合主键alter table Borreadd constraint fk_Rid foreign key(Rid) references Reader(Rid)--图书编号,复合主键alter table Borreadd constraint fk_Bid foreign key(Bid) references Book(Bid)--借阅日期,复合主键alter table Borreadd constraint pk_Lenddate primary key (Lenddate)--归还日期必须大于借阅日期alter table Borreadd constraint ck_Willdate check (Willdate>Lenddate)--默认值为借阅日期+1个月alter table Borreadd constraint df_Willdate default (dateadd(mm,1,getdate())) for Willdate--实际归还日期,默认为空alter table Borreadd constraint df_Retrindate default ('') for Retrundate--读者编号,复合主键alter table Penaltyadd constraint fk_Rid11 foreign key(Rid) references Reader(Rid)--图书编号,复合主键alter table Penaltyadd constraint fk_Bid22 foreign key(Bid) references Book(Bid)--罚款日期,复合主键alter table Penalty add constraint pk_Pdate primary key (Pdate)   --罚款日期,默认为当前日期 alter table Penalty add constraint df_Pdate default (getdate()) for Pdate   --罚款类型,1--延期 ,2--损坏 ,3--丢失 alter table Penalty add constraint ck_ptype check (ptype=1 or ptype=2 or ptype=3)   --罚款金额,必须大于0 alter table Penalty add constraint ck_Amount check (Amount>0)