北大青鸟 SQL第二学期第二章课后题（Library数据库）

--建数据库
use master
if exists(select * from sysdatabases where name='Library')
drop database Library
go


create database Library
on primary
(
name='Library_data',
filename='D:\sqltwosjk\Library_data.mdf',
size=5mb,
filegrowth=15%
)
log on
(
name='Library_log',
filename='D:\sqltwosjk\Library_log.ldf',
size=1mb,
filegrowth=15%
)
go


--建表
use Library
if exists(select * from sysobjects where name='Book')
drop table Book
go


create table Book
(
BID int not null,
BName varchar(20) not null,
Author varchar(20) not null,
PubComp varchar(20) not null,
PubDate datetime not null,
BCount int not null,
Price money not null
)


use Library
if exists(select * from sysobjects where name='Reader')
drop table Reader
go


create table Reader
(
RID int not null,
RName nvarchar(20) not null,
LendNum int not null,
RAddress nvarchar(50) not null
)


use Library
if exists(select * from sysobjects where name='Borrow')
drop table Borrow
go


create table Borrow
(
RID int not null,
BID int not null,
LendDate datetime not null,
WillDate datetime not null,
ReturnDate datetime not null
)




use Library
if exists(select * from sysobjects where name='Penalty')
drop table Penalty
go


create table Penalty
(
RID int not null,
BID int not null,
PDate datetime not null,
PType int not null,
Amount money not null
)




--建约束
--表1
--主键
alter table Book
Add constraint PK_BID primary key(BID)


--BID必须以ISBN开头
alter table Book
Add constraint CK_BID check(BID like 'ISBN%')


--出版日期小于当前日期
alter table Book
Add constraint CK_PubDate check(PubDate<getdate()) 


--现存数量必须大于等于1
alter table Book
Add constraint CK_BCount check(BCount>=1) 


--单价必须大于0
alter table Book
Add constraint CK_Price check(Price>0) 


--表2
alter table Reader
Add constraint PK_RID  primary key(RID),      --主键
       constraint CK_LendNum check(LendNum>=0)      --已借书数量必须大于等于0


--表3
alter table Borrow
Add constraint PK_RID_BID_LendDate primary key(RID,BID,LendDate) --符合主键


alter table Borrow
Add  constraint DF_LendDate default(getdate()) for LendDate  --借阅日期默认值为当前日期


alter table Borrow
Add constraint CK_WillDate check(WillDate>LendDate)   ---应归还日期 必须大于等于借阅日期 


alter table Borrow
Add constraint DF_WillDate default(DateAdd(mm,1,getdate())) for WillDate  --默认值为借阅日期+1个月


alter table Borrow
Add constraint DF_Return default(null) for ReturnDate  --实际归还日期默认值为空


alter table Borrow
Add constraint FK_RID foreign key(RID) references Reader(RID) --外键


alter table Borrow
Add constraint FK_BID foreign key(BID) references Book(BID)--外键


--表4
alter table Penalty
Add constraint PK_RID_BID_PDate primary key(RID,BID,PDate)


alter table Penalty
Add  constraint DF_PDate default(getdate()) for PDate  --默认值为当前日期


alter table Penalty
Add constraint CK_PType  check (PType in(1,2,3))   --罚款类型1-延期  2-损坏  3-丢失


alter table Penalty
Add constraint CK_Amount check(Amount>0)   --罚款金额必须大于0


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)--外键