--建数据库
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)--外键
北大青鸟 SQL第二学期第二章课后题(Library数据库)
最新推荐文章于 2021-02-11 21:59:59 发布