优化MySchool第二章课后

 /**创建数据库*/USE masterGOIF EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE NAME='Library')  DROP DATABASE LibraryGOCREATE DATABASE  LibraryON( NAME='Librarydata',    FILENAME='D:\project\Librarydata.mdf',size=5,FILEGROWTH=15%)Log ON (NAME='Librarylog',FILENAME='D:\project\Librarylog.ldf',SIZE=1,FILEGROWTH=15%)GO/**创建四个表*/Use LibraryGOIF EXISTS(SELECT * FROM sysobjects WHERE NAME=' Book')DROP TABLE BookGOCREATE TABLE Book(BID  varchar(50)  not null,BNAME  varchar(50)  not null,Author  varchar(20)  not null,PubComp  varchar(20)  not null,PubDate  datetime  not null,BCount  int  not null,Price  float  not null,)GOIF EXISTS(SELECT * FROM sysobjects WHERE NAME='Reader')DROP TABLE ReaderGOCREATE TABLE Reader(RID  varchar(50)  not null,RNAME  varchar(50)  not null,LendNum  int not null)GOIF EXISTS(SELECT * FROM sysobjects WHERE NAME='Borrow')Drop TABLE BorrowGOCREATE TABLE  Borrow (RID varchar(50) not null,BID varchar(50) not null,LendDate datetime not null,WillDate datetime not null,ReturnDate datetime  )GOIF EXISTS(SELECT * FROM sysobjects WHERE NAME='Penalty')Drop TABLE PenaltyGOCREATE TABLE Penalty(RID varchar(50) not null,BID varchar(50)  not null,PDate datetime not null,PType  int not null,Amount float not null)GO/**分别给四个表添加约束*/ALTER TABLE Book ADD CONSTRAINT pk_bid 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_rid PRIMARY KEY(RID)ALTER TABLE Reader ADD CONSTRAINT ck_lendnum CHECK(LendNum>=0) ALTER TABLE Borrow ADD CONSTRAINT fk_borrowrid FOReign KEY(RID) REFERENCES Reader(RID)ALTER TABLE Borrow ADD CONSTRAINT fk_borrowbid FOReign KEY(BID) REFERENCES Book(BID)ALTER TABLE Borrow ADD CONSTRAINT dk_lenddate DEFAULT (getDate()) FOR LendDate ALTER TABLE Borrow ADD CONSTRAINT dk_willdate DEFAULT(DateADD(mm,1,getDate())) FOR  willdateALTER TABLE Borrow ADD CONSTRAINT ck_willdate CHECK(WillDate >=getDate())ALTER TABLE  Penalty ADD CONSTRAINT fk_penaltyrid FOReign KEY(RID) REFERENCES Reader(RID)ALTER TABLE Penalty ADD CONSTRAINT fk_penaltybid FOReign KEY(BID) REFERENCES Book(BID)ALTER TABLE Penalty ADD CONSTRAINT dk_pdate DEFAULT(getDate()) FOR PDate ALTER TABLE Penalty ADD CONSTRAINT ck_ptype CHECK(PType  LIKE '[1-3]')ALTER TABLE Penalty ADD CONSTRAINT ck_amount CHECK(Amount >0)/**向表中插入信息*/INSERT INTO Book VALUES('ISBN001','java','cay s.horstmann','jixiegongye',02-02-90, 100,200.0)INSERT INTO Book VALUES('ISBN002','.net','cay s.horstmann','jixiegongye',03-03-90, 200,240.0)INSERT INTO Reader VALUES('001','zhangYongwei','1')INSERT INTO Reader VALUES('002','zhangDawei','2')INSERT INTO Borrow(RID,BID) VALUES('001','ISBN001')INSERT INTO Borrow(RID,BID) VALUES ('002','ISBN002')INSERT INTO Penalty(RID ,BID,PType,Amount) VALUES('001','ISBN001',1,2000)INSERT INTO Penalty(RID,BID ,PType,Amount)VALUES('002','ISBN002',3,500)