优化MySchool 第二章课后

--创建图书管理系统数据库  
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_S2\Library_data.mdf',  
size=5mb,  
filegrowth=15%  
)  
log on   
(  
name='Library_log',  
filename='D:\Project_S2\Libraary_log.ldf',  
size=1mb,  
filegrowth=15%  
)  
go  
--创建Book表  
use Library   
go  
create table  Book  
(  
BID int not null,  
BName nvarchar(20) not null,  
Author nvarchar(20) not null,  
PubComp nvarchar(50) null,  
PubDate datetime not  null,  
BCount int not null,  
Price money not null  
 )  
go  
  
--创建Reader表  
use Library  
go  
create table Reader  
(  
RID int not null,  
RName nvarchar(20) not null,  
LendNum int null,  
RAddress nvarchar(50) null  
 )  
 go  
  
  
--创建Borrow表  
use Library  
go   
create table Borrow  
(  
RID int not null,  
BID　int not null,  
LendDate datetime  not null,  
WillDate datetime null,  
ReturnDate datetime null  
)  
go  
  
  
--创建Penalty表  
use Library   
go   
create table Penalty  
(  
RID int not null,  
BID int not null,  
PDate datetime not null,  
PType int null,  
Amount money null  
)  
go  
  
  
--添加约束  
  
--1.Book表中  
     --添加主键  
alter table Book  
add constraint PK_BID primary key (BID)  
  
  
    --检查约束  
    --1.（必须以ISBN 开头）  
alter table Book  
add constraint CK_BID check (BID like 'ISDN%')  
  
    --2.(必须小于当前日期)  
alter table Book   
add constraint CK_PubDate check (PubDate<getdate())  
  
   --3.(必须大于等于1)  
alter table Book  
add constraint CK_BCount check (BCount>='1')  
  
   --4.(必须大于0)  
alter table Book   
add constraint CK_Price check (Price>'0')  
  
--2.Reader表中  
    --添加主键  
alter table Reader  
add constraint PK_RID primary key (RID)  
  
    --检查约束  
alter table Reader  
add constraint CK_LendNum check (LendNum>='0')  
  
--3.Borrow表中  
  
      --1.添加主键  
alter table Borrow  
add constraint PK_RID1 primary key (RID)  
   
   --外键约束  
   --1.  
alter table Reader  
add constraint FK_RID  
foreign key(RID)  
references Borrow(RID)  
  
--------删除约束  
------alter table Borrow  
------drop constraint PK_LendDate  
  
------alter table Reader  
------drop constraint FK_RID  
  --2.  
alter table Borrow  
add constraint FK_BID  
foreign key (BID) references Book (BID)  
  
  
  --默认约束  
alter table Borrow  
add constraint DB_LendDate default (getDate()) for LendDate  
  
  --(必须大于等于借阅日期，默认为借阅日+1)  
  
alter table Borrow  
add constraint CK_WillDate check (WillDate>=LendDate)  
  
alter table Borrow  
add constraint DB_WillDate default (dateAdd(mm,1,getdate())) for WillDate  
--删除约束  
alter table Borrow  
drop constraint DB_WillDate  
  
  
    --默认null  
alter table Borrow  
add constraint DB_ReturnDate default (null) for ReturnDate  
  
--4.Penalty表中  
   --1.添加主键  
alter table Penalty  
add constraint PK_RID2 primary key (RID)  
   --2.添加主键(有错误！)  
alter table Penalty  
add constraint PK_BID2 primary key (BID)  
---------------删除约束  
--------------alter table Penalty  
--------------drop constraint PK_RID2  
 --外键约束  
   --1.  
alter table Reader  
add constraint FK_RID1  
foreign key(RID)  
references Penalty(RID)  
  
  
  --2.  
alter table Penalty  
add constraint FK_BID1  
foreign key (BID) references Book (BID)  
    --默认约束  
alter table Penalty  
add constraint DB_PDate default (getDate()) for PDate    
  
    --检查约束  
alter table Penalty  
add constraint CK_Amount check (Amount>0)  
  
  
--Book中添加BTotal列  
   --先删除表 再添加数据  
drop table Book  
  
use Library   
go  
create table  Book  
(  
BID int not null,  
BName nvarchar(20) not null,  
Author nvarchar(20) not null,  
PubComp nvarchar(50) null,  
PubDate datetime not  null,  
BCount int not null,  
Price money not null,  
BTotal int null  
 )  
go  
--像各表中添加至少2条数据  
    --Book  
insert into Book(BID,BName,Author,PubComp,PubDate,BCount,Price,BTotal)  
select 1,'','','','',1,100,1000000  union  
select 1,'','','','',1,100,1000000  union  
select 1,'','','','',1,100,1000000    
   --Reader  
insert into Reader(RID,RName,LendNum,RAddress)  
select 1,'',2,'' union  
select 1,'',2,'' union  
select 1,'',2,''   
   --Borrow  
insert into Borrow (RID,BID,LendDate,WillDate,ReturnDate)  
select 1,1,'','','' union  
select 1,1,'','','' union  
select 1,1,'','',''   
   --Penalty  
insert into Penalty(RID,BID,PDate,PType,Amount)  
select 1,1,'',1,20 union  
select 1,1,'',3,20 union  
select 1,1,'',2,20