S2第二章

最新推荐文章于 2024-10-24 09:54:52 发布

IT的雨天

最新推荐文章于 2024-10-24 09:54:52 发布

阅读量329

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_36074163/article/details/54178662

本文介绍了一个图书管理系统的数据库设计过程，包括创建数据库、定义表结构及添加各种约束条件。涉及的主要表包括Book（书籍）、Reader（读者）、Borrow（借阅记录）和Penalty（罚金记录），并为这些表添加了主键、外键、默认值和检查约束。

use master
if exists(select * from  sysdatabases where name='MySchool')
drop database MySchool
create database MySchool
on primary
(
name='MySchool_data',
filename='D:\MySchool_data.mdf',
size=5,
maxsize=100,
filegrowth=12%
)
log on
(
name='MySchool_log',
filename='D:\MySchool_data.ldf',
size=5,
maxsize=100,
filegrowth=12%
)
use MySchool
if exists(select * from Sysobjects where name='Book')
drop table Book
create table Book
(
BID varchar(10) not null,
BName varchar(10) not null,
AUThor varchar(10) not null,
PubComp varchar(10) not null,
Pubdate datetime not null,
Bcount int not null,
Price  float  not null
)
use MySchool
if exists(select * from sysobjects where name='Reader')
drop table Reader
create table Reader
(
RID varchar(10) not null,
BID varchar(10)  not null,
LendName int not null,
Raddress varchar(10) not null
)
use MySchool
if exists(select * from Sysobjects where name='Borrow')
drop table Borrow 
create table Borrow 
(
RID varchar(10) not null,
BID varchar(10) not null,
LendDate datetime not null,
WillDate datetime not null,
ReturnDate datetime not null
)
use MySchool
if exists(select * from Sysobjects where name='Penalty')
drop table Penalty
create table Penalty
(
RID varchar(10) not null,
BID varchar(10) not null,
PData datetime not null,
PType  int not null,
Amount float not null
)
--约束
-- 1
alter table Book
add constraint PK_BID primary key(BID)
alter table Book
add constraint CK_BID check(BID='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)

-- 2
alter table Reader
add constraint PK_RID primary key (RID)
alter table Reader
add constraint CK_LendName check(LendName>0)
--3
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)
alter table Borrow 
add  constraint DF_LendDate default(getdate()) for LendDate
alter table Borrow
add constraint DT_WillDate default(DateAdd(mm,1, getdate())) for WillDate
alter table Borrow
add constraint CK_WillDate check(WillDate>getdate()) 
--4



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 DF_PData default(getdate()) for PData

alter table  Penalty
add constraint CK_PType check(PType like '[1-3]')
alter table  Penalty
add constraint CK_Amount check(Amount>0)