SQL语句数据库的实现课后题

--新建数据库

use master
if exists(select * from sysdatabases where name ='Library')
drop database Library
create database Library
on primary(
name='Library_data',
filename='D:\project\library_data.mdf',
size=5mb,
filegrowth=15%
)
log on(
name='Library_log',
filename='D:\project\library_log.ldf',
size=1mb,
filegrowth=15%
)



---创建表
use Library
create TABLE Book(
BID nvarchar(20) NOT NULL,
BName nvarchar(20) NOT NULL,
Author nvarchar(20) NOT NULL,
PubComp nvarchar(50) NOT NULL,
PubDate datetime NOT NULL,
BCount int NOT NULL,
Price money NOT NULL
)
use Library
create TABLE Reader(
RID nvarchar(20) NOT NULL,
RName nvarchar(20) NOT NULL,
LendNum int NOT NULL,
RAddress nvarchar(20) NOT NULL
)
use Library
create TABLE Borrow(
RID nvarchar(20) NOT NULL,
BID nvarchar(20) NOT NULL,
LendDate datetime NOT NULL,
WillDate datetime NOT NULL,
ReturnDate datetime NOT NULL
)
use Library
create TABLE Penalty(
RID nvarchar(20) NOT NULL,
BID nvarchar(20) NOT NULL,
PDate datetime NOT NULL,
PType  int NOT NULL,
Amount money NOT NULL
)
--删除约束
alter table Borrow 
drop constraint PK_RID
--添加主键
ALTER TABLE BOOK
ADD CONSTRAINT PK_BID PRIMARY KEY(BID)

ALTER TABLE Reader
ADD CONSTRAINT PK_RID PRIMARY KEY(RID)

ALTER TABLE Borrow
ADD CONSTRAINT PK_BID1 PRIMARY KEY(BID)

ALTER TABLE Penalty
ADD CONSTRAINT PK_BID PRIMARY KEY(BID)

---检查约束BID的开头
ALTER TABLE Book 
ADD CONSTRAINT CK_BIDname CHECK (BID like 'ISBN%')
---检查约束日期
ALTER TABLE Book 
ADD CONSTRAINT CK_PubDate CHECK (PubDate<getdate())
--检查约束存货
ALTER TABLE Book 
ADD CONSTRAINT CK_BCount CHECK (PubDate>1)
--检查约束货币
ALTER TABLE Book 
ADD CONSTRAINT CK_Price CHECK (Price>0)
--检查约束借书量
ALTER TABLE Reader
ADD CONSTRAINT CK_LendNum CHECK (LendNum>0)
--默认约束日期
ALTER TABLE Borrow 
ADD CONSTRAINT DF_WillDate DEFAULT(dateAdd(mm,1,getdate())) FOR WillDate
--检查约束货币
ALTER TABLE Penalty
ADD CONSTRAINT CK_Amount CHECK (Amount>0)    
--检查约束罚款
ALTER TABLE Penalty
ADD CONSTRAINT CK_PType CHECK (1='延期'or 2='损坏' or 3='丢失')    
--复合主键
alter table Penalty add constraint PK_RIDduo1 primary key(BID,RID,PDate)
alter table Borrow add constraint PK_RIDduo  primary key(BID,RID,LendDate)
--增加列
alter  table Book   add  Total int