图书管理系统数据库创建

图书管理系统在SQL Server 2005 数据库中的创建代码: 1. 创建数据库
create database BookSys
on
(
name='BookSys',
filename='D:\BookSysDB\BookSys.mdf',
size= 3,
filegrowth= 10%
)
log on
(
name='BookSys_log',
filename='D:\BookSysDB\BookSys_log.ldf', size= 1,
filegrowth= 1
)
Go
2.创建表
（1）创建管理员(admin)信息表
use BookSys
go
create table admin
(
adminId varchar(10)not null, adminName varchar(4)not null, adminSex varchar(2)not null, adminAge int not null, adminPass varchar(10)not null
)
Go
（2）创建读者(reader)信息表
use BookSys
go
create table reader
(
readerId varchar(10)not null, readerPass varchar(10)not null, readerName varchar(8)not null, readerSex varchar(2)not null, readerAge int not null, readerDept varchar(20)not null,
readerzhuanye varchar(20)not null, maxnumber int,
islost varchar(2)
)
Go
（3）创建图书类别(booktype)信息表
use BookSys
go
create table booktype
(
bookTypeId int not null, bookTypeName varchar(10)not null,
flow varchar(4)not null
)
go
（4）创建出版社(publisher)信息表
use BookSys
go
create table publisher
(
publisherId varchar(10)not null, publisherName varchar(20)not null, publisherAddress varchar(20)not null, publisherTel varchar(11)
)
Go
（5）创建图书(book)信息表
use BookSys
go
create table book
(
bookId varchar(10)not null, bookName varchar(10)not null, bookWritter varchar(10)not null, bookTypeId int not null, publisherId varchar(10)not null, bookPrice float not null, ifborrow varchar(2)
)
Go
（6）创建借阅(borrow)信息表
use BookSys
go
create table borrow
(
borrowId int not null,
bookId varchar(10)not null,
readerId varchar(10)not null,
borrowTime datetime not null,
returnTime datetime,
outTime varchar(6)
)
Go
3.创建表的约束
（1）创建管理员(admin)信息表的约束
use BookSys
go
alter table admin
add constraint PK_adminId primary key(adminId),
constraint CK_adminSex check(adminSex in('男','女')),
constraint CK_adminAge check(adminAge>(0)AND adminAge<(100))
go
（2）创建读者(reader)信息表的约束
use BookSys
go
alter table reader
add constraint PK_readerId primary key(readerId),
constraint CK_readerAge check(readerAge>(0)and readerAge<(100)), constraint CK_readerSex check(readerSex in('男','女')),
constraint DF_islost default('否')for islost
go
（3）创建图书类别(booktype)信息表的约束
use BookSys
go
alter table bookType
add constraint PK_bookTypeId primary key(bookTypeId)
go
（4）创建出版社(publisher)信息表的约束
use BookSys
go
alter table publisher
add constraint PK_publisherId primary key(publisherId)
go
（5）创建图书(book)信息表的约束
use BookSys
go
alter table book
add constraint PK_bookId primary key(bookId),
constraint FK_bookTypeId foreign key(bookTypeId)references booktype(bookTypeId),
constraint FK_publisherId foreign key(publisherId)references publisher(publisherId),
constraint CK_bookPrice check(bookPrice>0),
constraint DF_ifborrow default('否')for ifborrow
go
（6）创建借阅(borrow)信息表的约束
use BookSys
go
alter table borrow
add constraint PK_borrowId primary key(borrowId),
constraint FK_bookId foreign key(bookId)references book(bookId), constraint FK_readerId foreign key(readerId)references
reader(readerId),
constraint DF_borrowTime default(getdate())FOR borrowTime,
constraint DF_outTime default('否')for outTime
go
本文由牙膏 ,花都网站建设发表。